网站里面的超链接怎么做,wordpress 如何修改主题中元素,谈谈你在建设主题资源网站时,wordpress英文主题破解MySQL的引擎与体系结构
体系结构
连接层
最上层是一些客户端和链接服务#xff0c;主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限
服务层
第二层架构主要完成大多数的核心服务功能#xff0c;如SQL…MySQL的引擎与体系结构
体系结构
连接层
最上层是一些客户端和链接服务主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限
服务层
第二层架构主要完成大多数的核心服务功能如SQL接口并完成缓存的查询SQL的优化和分析部分内置函数的操作。所有跨存储引擎的功能也是在这一层实现的如过程、函数等
引擎层
存储引擎真正的负责MySQL中个数据的存储与提取服务器通过API和存储引擎进行通信不同的存储引擎具有不同的功能这样子我们也可以根据自己的需求来选取合适的存储引擎
存储层
主要是将数据存储在文件系统之上并完成与存储引擎的交互
存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式存储引擎是基于库的所以存储引擎也可以被称为表类型
默认引擎-InnoDB
查看所有的引擎
show engines;可以看到所有的引擎 存储引擎的特点
InnoDB
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎在MySQL5.5版本之后InnoDB是默认的MySQL的引擎。
特点
DML(增删改)操作支持ACID(原子性、一致性、隔离性、持久性)模型支持事务行级锁提高并发访问的性能支持外键Foreign Key约束保证数据的完整性和正确性
文件
xxx.ibd:xxx代表的是表明innoDB引擎的每张表都会对应着这样的一个表文件存储该表的表结构(frm、sdi)、数据和索引参数innodb_file_per_table 默认为no表示每张表各自占用一个表空间如果为yes表示多张表共用一个表空间
查看指令
-- 查看是共用多个表空间
show variables like innodb_file_per_table;查看ibd文件
我在本地系统中找到了自己库的文件 但是这个文件是一个二进制文件没法直接查看但是可以借助指令来进行一个查看
ibd2sdi 文件命.ibd查看结果 可以直接看到表的全部信息以及字段信息
逻辑空间
TableSpace 表空间Segment段Extent区 区的大小是固定的是1M 可以包含64个页Page页 页是操作的最小单元大小页是固定的一个页16KRow行 MyISAM
MyISAM是MySQL的早期默认存储引擎
特点
不支持事务不支持外键
支持表锁不支持行锁
访问速度快
文件
*.sdi存储表结构信息
.MYD存储数据
***.MYI **存储索引
Memory
Memory引擎的表数据是存储在内存中的由于受到硬件问题、断电问题的影响只能将这些表作为临时表或者缓存使用
特点
内存存放
hash索引
文件
*.sdi存储表结构信息
各个引擎的特点
特点InnoDBMyISAMMemory存储限制64TB有有事务安全支持--锁机制行锁表锁表锁Btree索引支持支持支持hash索引--支持全文索引5.6之后支持支持-空间使用高低内存使用高低中等批量插入速度低高高支持外键支持--
InnoDB与MyISAMInnoDB支持事务、外键和行级锁
执行引擎的选择 InnoDB是MySQL的默认引擎支持事务、外键如果应用对事物的**完整性**有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询外还包含很多更新、删除操作那么InnoDB存储引擎是比较合适的选择 MyISAM如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事物的完整性、并发性要求不是很高那么选择这个存储引擎比较合适 Memory将所有数据保存在内存中访问速度快通常用于临时表及缓存。Memory缺陷是对标的大小有限制太大的表无法缓存在内存中而却无法保证数据的安全性 索引
索引概述
什么是索引
索引index是帮助MySQL搞笑获取数据的数据结构有序在数据之外数据库系统还维护者满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引
优缺点
优点缺点提高数据检索的效率降低数据库的IO成本索引列也是要占用空间的通过索引列堆数据进行排序降低数据排序的成本索引大大提高了查询效率同时也降低更新表的速度如对表进行Insert、update、delete操作时效率降低但实际上查询操作更多而增改删操作较少
索引数据结构
MySQL的索引是在存储引擎曾实现的不同的存储引擎有不同的结构主要包括以下几种
索引结构描述BTree索引最常见的索引类型大部分引擎都支持B树索引Hash索引底层数据结构是通过哈希表实现的只有精确匹配索引列的查询才有效不支持范围查询R-Tree空间索引空间索引是MyISAM引擎的一个特殊索引类型主要用于地理空间数据类型比较少用Full-text全文索引是一种通过建立倒排索引快速匹配文档的方式类似于ES
索引支持
索引InnoDBMyISAMMemoryBTree索引支持支持支持Hash索引不支持不支持支持R-Tree索引不支持支持不支持Full-text5.6版本之后支持支持不支持
平时所说的索引如果没有特别的指明都是B树结构组织的索引
BTree 二叉树的缺点顺序插入时会形成链表查询性能大大降低。大数据量的情况下层级较深检索速度较慢
红黑树能够解决顺序插入形成链表的情况但是在大数据量的情况下层级较深索引速度较慢
B-Tree多路平和查找树:以一颗最大度数max-degree为5阶的b-tree为例每个节点最多存储4个key5个指针【树的度数指的是一个结点的子结点个数】 BTree与B-Tree的区别
所有的数据都会出现在叶子节点叶子节点形成一个单向链表 MySQL的B树相对于经典的B树进行了优化在原有的B树的基础上增加了一个指向相邻叶子节点的链表指针就形成了带有顺序指针的B树提高区间访问性能 Hash
哈希索引就是采用一定的Hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中
如果两个或多个键值映射到一个相同的槽位上他们就会产生hash冲突可以通过链表进行解决
特点
Hash索引只能用于对等比较in不支持范围查询无法利用索引完成排序操作查询效率高通常只需要一次检索就可以了效率通常高于BTree索引
存储结构支持
在MySQL中支持hash索引的是memory引擎而innodb中具有自适应hash功能hash索引是存储引擎根据B树索引在指定条件下自动构建的
InnoDB存储引擎为什么要选择BTree索引结构
相对于二叉树层级更少搜索效率更高相对于B-Tree无论是叶子节点还是非叶子节点都会存储数据这样导致一页中存储的键值减少指针跟着减少要同时保存大量数据只能增加树的高度导致性能降低相对于Hash索引BTree支持范围匹配和排序操作
索引分类
分类含义特点关键字主键索引针对于表中逐渐创建的索引默认自动创建只能有一个PRIMARY唯一索引避免同一个表中的某个数据列中的重复值可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引超找的是文本中的关键词而不是比较索引中的值可以有多个FUNNTEXT
在InnoDB存储索引中根据索引的存储形式又可以分为以下两种
分类含义特点聚集索引Clustered index将数据存储与索引放在一块必须有而且只能有一个非聚集索引/二级索引/辅助索引Secondary index将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个
聚集索引选取规则
如果存在主键主键索引就是聚集索引如果不存在逐渐将使用第一个唯一(UNIQUE)索引作为聚集索引如果表没有逐渐或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引
select * from user where name Arm查询条件是name那么就先走二级索引二级索引查找到之后就会拿到这一行的主键ID根据主键在聚集索引中进行查询找到这一行的数据。
回表查询指的是现在二级索引中找到主键值然后再到主键索引中找到对应的行这种查询被称为回表查询
索引的语法
创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,....)一次可以创建多个索引如果使用UNIQUE或者FULLTEXT 则表示创建的是唯一/全文索引反之则是普通索引
查看索引
SHOW INDEX FROM table_name;删除索引
DROP INDEX index_name ON table_nameSQL 性能分析
SQL执行频率
MySQL客户端连接成功后通过show [session|global] status命令可以提供服务器状态信息。通过下面指令可以看到当前数据库的insert update delete select 的访问频率
SHOW GLOBAL STATUS LIKE COM_______;
-- 7个下划线慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位秒默认为10s)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启需要在MySQL的配置文件(/etc/my.cnf)中配置
查询是否开启了慢查询日志
show variables like slow_query_log;修改配置文件
# 开启MySQL慢查询日志查询开关
slow_query_log1
# 设置慢日志的时间为2s如果语句执行时间超过了2s就会被视为慢查询记录慢查询日志
long_query_time2profile详情
show profiles能够在做SQL优化时帮助我们了解事件都消耗到哪里去了。通过have_profiling参数能够看到当前MySQL是否支持profile操作
查看是否支持该操作
select have_profiling;默认情况下profiling是关闭的可以通过set语句在session/global级别开启profiling
set profiling 1;先查看是否已经开启
select profiling;如果为1则表示已经打开这里为0表示没有打开需要再进行一次设置设置成功后再次查询 #这里我的建议是 不用看因为MySQL8.0之后要废弃这个方法
SHOW PROFILES is deprecated and will be removed in a future release. Please use Performance Schema insteadexplain执行计划
EXPLAIN或者DESC命令获取MySQL如何执行select语句的信息包括在select语句执行过程中如何连接和连接顺序
-- 直接在select 语句之前加上关键字 explain 或者desc即可
explain select 字段列表 from 表名 where 条件;查询语句
explain select * from shop.tb_newbee_mall_admin_user where admin_user_id 1;查询结果 含义介绍 Id select查询的序列号表示查询中执行select子句或者是操作表的顺序(Id相同执行顺序从上到下Id不同值越大越先执行)。 select_type 表示select的查询类型常见的取值有SIMPLE见到表及不使用表连接或者子查询、PRIMARY(主查询即外层的查询) UNIONUNION中的第二个或者后面的查询语句、SUBQUERYSelect/where之后包含了子查询等等 type 表示连接类型性能从好到差以此为NULL system CONST eq_ref ref range index all 根据唯一主键进行查询会返回CONST非唯一主键返回refnull几乎达不到除非不查询任何表 possible_key 显示可能应用在这张表上的索引一个或者多个 key 实际使用的索引如果为null则表示没有使用索引 key_len 表示索引中使用的字节数该职位为索引字段最大可能值并非实际使用长度在不损失精确性的前提下长度越长越好 rows MySQL认为必须执行的查询的行数在innodb引擎的表中是一个预估值可能并不总是准确。 filtered 表示返回结果的行数占需读取行书的百分比filtered越大越好
索引使用
失效原因
最小前缀法则
如果索引了多列联合索引要遵守最左前缀法则最左前缀法则指的是查询从索引最左列开始并且不能够跳过索引中的列如果条约某一列索引将部分失效后面的字段索引失效
举个例子现在存在一个联合索引分别为字段A B C顺序页是从左往右在查询过程中如果输入
select * from table where A 1 and B 2 and C 3;这样的话就会走联合索引查出来的key_len等于ABC三个字段的和
那么如果不带C
select * from table where A1 and B2;这样子仍然走联合索引但是key_len等于AB两个字段的长度
依此类推
那么此时不走A
select * from table where B 1 and C3 ;由于A是最左列这样子就不满足最左前缀法则所以索引为null
那么如果我们有A但是跳过B直接到C
select * from table where A 1 and C 3;这样子走索引但是key_len等于A的长度则表示C已经被丢失对应了后面的索引失效
那么我们走ABC但是顺序不一样
select * from table where b 1 and c 2 and a1;这样子和第一种情况相似字段长度仍为ABC的总和
范围查询
联合索引中出现了范围查询(,),范围查询右侧的索引失效但是 这种可以直接规避这种情况
索引列运算操作
不要再索引列上进行运算操作否则索引将失效
举个例子
自己创建了一个表和一个联合索引用nick_name和address 进行联合用nick_name进行一个直接查询
explain select * from shop.tb_newbee_mall_user where nick_name 十三;结果 那么我们进行计算进行一个字符串切割再查看结果
explain select *from shop.tb_newbee_mall_user where substr(nick_name,1,3) 198;在这里看出来索引直接失效
需要注意的是如果是模糊查询直接自己手动拼接 写成 字符串%不会出现任何问题还是会走索引如果是%字符串%则不会走索引CONCAT也相同
使用CONCAT函数
explain select *from shop.tb_newbee_mall_user where nick_name like CONCAT(%,198,%);运行结果 单个
explain select *from shop.tb_newbee_mall_user where nick_name like 198%;字符串不加引号
字符串不加引号会造成索引失效
例如加上引号
explain select * from shop.tb_newbee_mall_user where nick_name 1986565395;效果 不加引号
explain select * from shop.tb_newbee_mall_user where nick_name 1986565395;效果 可能会使用tb_wxk这个索引但是实际上并没有进行使用 发生了隐式转换 模糊查询 如果只是尾部模糊匹配索引不会失败如果是头部模糊匹配索引则会失效 or连接的条件 用or分隔开的条件如果or前的条件中的列有索引而后面的列中没有索引那么设计的索引都不会被用到 先查看索引
show index from shop.tb_newbee_mall_user;在这里使用or进行连接
连接两个都有索引的字段
explain select * from shop.tb_newbee_mall_user where nick_name1986565395 or user_id1;一个字段没有索引
explain select * from shop.tb_newbee_mall_user where nick_name1986565395 or is_deleted0;结果
数据分布影响 如果MySQL评估使用索引比全表更慢则不使用索引 比如说这里的nickname是0~10都比0大 那么我进行一个操作查询操作
explain select * from shop.tb_newbee_mall_user where nick_name 0;按照正常流程这个操作是要走索引的此外nick_name全都比0大
运行结果如下 到最后没有走索引而是选择了扫描全表
那么再次进行一个更换
explain select * from shop.tb_newbee_mall_user where nick_name 0;这样子又走索引了。
那么我们查询大于等于5的大于等于五的数据小于一半
explain select * from shop.tb_newbee_mall_user where nick_name 5;这时候还是老老实实的走了全局索引
说明MySQL在选择扫描全表还是走索引会进行评估这个评估和数据分布有关
索引使用原则
SQL提示
我们知道nick_name和address是一个联合索引在这里我将nick_name设置成为一个单列索引
create index tb_nick on shop.tb_newbee_mall_user(nick_name);查看所有的索引 可以看到此时的nickname已经存在两个索引那么执行下面SQL
explain select * from shop.tb_newbee_mall_user where nick_name 2;最后还是走了联合索引说明在这时MySQL自己做出了选择那么如何规避这个选择这时候就是用到了SQL提示 SQL提示是优化数据库的一个重要手段简单来说就是SQL语句中加入一些认为的提示来达到优化的作用 use index: explain select * from shop.tb_newbee_mall_user use INDEX (tb_nick) where nick_name 5在这里要求使用了tb_nick这个锁 ignore index: 忽略某个index explain select * from shop.tb_newbee_mall_user ignore INDEX (tb_nick) where nick_name 5;这里选择忽略tb_nick这个索引 直接不进行使用 force index: 强制使用某个索引 explain select * from shop.tb_newbee_mall_user force INDEX (tb_nick) where nick_name 5;覆盖索引
尽量使用覆盖索引查询使用了索引并需要返回的列在该列中已经全部能找到减少使用 select *
explain select nick_name,address from shop.tb_newbee_mall_user where nick_name 5;在这里我们查询nickname和address
查询结果 我们在查询is_deleted:
explain select is_deleted from shop.tb_newbee_mall_user where nick_name 5;using index condition 查找使用了索引但是需要回标查询 using where, using index 查找使用了索引但是需要的数据都在索引列中能够找到不需要进行回表查询操作 前缀索引 不建议使用可以考虑ES
当字段为字符串(varchar text等)时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO影响查询效率此时可以只讲字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率 语法 create index idx_xxx on table_name(column(n)) 前缀长度 可以根据索引的许安则醒来确定而选择性是指不重复的索引值基数和数据表的记录总数的比值索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的 select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user; 文章转载自： http://www.morning.ndpzm.cn.gov.cn.ndpzm.cn http://www.morning.xxiobql.cn.gov.cn.xxiobql.cn http://www.morning.nptls.cn.gov.cn.nptls.cn http://www.morning.mntxalcb.com.gov.cn.mntxalcb.com http://www.morning.kpcjl.cn.gov.cn.kpcjl.cn http://www.morning.ttaes.cn.gov.cn.ttaes.cn http://www.morning.dfrenti.com.gov.cn.dfrenti.com http://www.morning.wdply.cn.gov.cn.wdply.cn http://www.morning.bhdyr.cn.gov.cn.bhdyr.cn http://www.morning.rqbkc.cn.gov.cn.rqbkc.cn http://www.morning.zrdhd.cn.gov.cn.zrdhd.cn http://www.morning.gbfuy28.cn.gov.cn.gbfuy28.cn http://www.morning.wrcgy.cn.gov.cn.wrcgy.cn http://www.morning.ktcfl.cn.gov.cn.ktcfl.cn http://www.morning.nzwp.cn.gov.cn.nzwp.cn http://www.morning.fmswb.cn.gov.cn.fmswb.cn http://www.morning.sjwzl.cn.gov.cn.sjwzl.cn http://www.morning.wqnc.cn.gov.cn.wqnc.cn http://www.morning.gbyng.cn.gov.cn.gbyng.cn http://www.morning.ggnkt.cn.gov.cn.ggnkt.cn http://www.morning.ykmtz.cn.gov.cn.ykmtz.cn http://www.morning.lbhck.cn.gov.cn.lbhck.cn http://www.morning.nkkr.cn.gov.cn.nkkr.cn http://www.morning.yfwygl.cn.gov.cn.yfwygl.cn http://www.morning.jbblf.cn.gov.cn.jbblf.cn http://www.morning.kzbpx.cn.gov.cn.kzbpx.cn http://www.morning.nccyc.cn.gov.cn.nccyc.cn http://www.morning.dpbgw.cn.gov.cn.dpbgw.cn http://www.morning.xnfg.cn.gov.cn.xnfg.cn http://www.morning.hgsylxs.com.gov.cn.hgsylxs.com http://www.morning.vnuwdy.cn.gov.cn.vnuwdy.cn http://www.morning.cwgfq.cn.gov.cn.cwgfq.cn http://www.morning.hympq.cn.gov.cn.hympq.cn http://www.morning.kndst.cn.gov.cn.kndst.cn http://www.morning.mqbsm.cn.gov.cn.mqbsm.cn http://www.morning.rbkml.cn.gov.cn.rbkml.cn http://www.morning.langlaitech.cn.gov.cn.langlaitech.cn http://www.morning.mngyb.cn.gov.cn.mngyb.cn http://www.morning.znnsk.cn.gov.cn.znnsk.cn http://www.morning.qrlsy.cn.gov.cn.qrlsy.cn http://www.morning.jbpodhb.cn.gov.cn.jbpodhb.cn http://www.morning.sqqkr.cn.gov.cn.sqqkr.cn http://www.morning.ltqzq.cn.gov.cn.ltqzq.cn http://www.morning.nzhzt.cn.gov.cn.nzhzt.cn http://www.morning.xrwbc.cn.gov.cn.xrwbc.cn http://www.morning.khcpx.cn.gov.cn.khcpx.cn http://www.morning.qbksx.cn.gov.cn.qbksx.cn http://www.morning.nwzcf.cn.gov.cn.nwzcf.cn http://www.morning.wqtzs.cn.gov.cn.wqtzs.cn http://www.morning.mqzcn.cn.gov.cn.mqzcn.cn http://www.morning.bzkgn.cn.gov.cn.bzkgn.cn http://www.morning.fglxh.cn.gov.cn.fglxh.cn http://www.morning.rgxll.cn.gov.cn.rgxll.cn http://www.morning.qswws.cn.gov.cn.qswws.cn http://www.morning.wqpm.cn.gov.cn.wqpm.cn http://www.morning.prmbb.cn.gov.cn.prmbb.cn http://www.morning.cdrzw.cn.gov.cn.cdrzw.cn http://www.morning.sqxr.cn.gov.cn.sqxr.cn http://www.morning.fewhope.com.gov.cn.fewhope.com http://www.morning.cwnqd.cn.gov.cn.cwnqd.cn http://www.morning.gjzwj.cn.gov.cn.gjzwj.cn http://www.morning.dddcfr.cn.gov.cn.dddcfr.cn http://www.morning.fldsb.cn.gov.cn.fldsb.cn http://www.morning.gglhj.cn.gov.cn.gglhj.cn http://www.morning.rhph.cn.gov.cn.rhph.cn http://www.morning.dyxlm.cn.gov.cn.dyxlm.cn http://www.morning.bkylg.cn.gov.cn.bkylg.cn http://www.morning.kpcdc.cn.gov.cn.kpcdc.cn http://www.morning.yodajy.cn.gov.cn.yodajy.cn http://www.morning.yxkyl.cn.gov.cn.yxkyl.cn http://www.morning.rzbgn.cn.gov.cn.rzbgn.cn http://www.morning.mflqd.cn.gov.cn.mflqd.cn http://www.morning.datadragon-auh.cn.gov.cn.datadragon-auh.cn http://www.morning.pljdy.cn.gov.cn.pljdy.cn http://www.morning.iiunion.com.gov.cn.iiunion.com http://www.morning.fpqq.cn.gov.cn.fpqq.cn http://www.morning.qbdqc.cn.gov.cn.qbdqc.cn http://www.morning.ntffl.cn.gov.cn.ntffl.cn http://www.morning.brbnc.cn.gov.cn.brbnc.cn http://www.morning.mdmxf.cn.gov.cn.mdmxf.cn
