此小结与索引其实没有太多的关联,但是为了便于理解索引的内容,添加此小结作为铺垫知识。
1.1 InnoDB逻辑存储结构
MySQL表中的所有数据被存储在一个空间内,称之为表空间,表空间内部又可以分为段(segment)、区(extent)、页(page)、行(row),逻辑结构如下图:
表空间是由不同的段组成的,常见的段有:数据段,索引段,回滚段等等,在 MySQL中,数据是按照B+树来存储,因此数据即索引,因此数据段即为B+树的叶子节点,索引段为B+树的非叶子节点,回滚段用于存储undo日志,用于事务失败后数据回滚以及在事务未提交之前通过undo日志获取之前版本的数据,在InnoDB1.1版本之前一个InnoDB,只支持一个回滚段,支持1023个并发修改事务同时进行,在InnoDB1.2版本,将回滚段数量提高到了128个,也就是说可以同时进行128*1023个并发修改事务。
区是由连续页组成的空间,每个区的固定大小为1MB,为保证区中页的连续性,InnoDB会一次从磁盘中申请4~5个区,在默认不压缩的情况下,一个区可以容纳64个连续的页。但是在开始新建表的时候,空表的默认大小为96KB,是由于为了高效的利用磁盘空间,在开始插入数据时表会先利用32个页大小的碎片页来存储数据,当这些碎片使用完后,表大小才会按照MB倍数来增加。
页是InnoDB存储引擎的最小管理单位,每页大小默认是16KB,从InnoDB 1.2.x版本开始,可以利用innodb_page_size来改变页size,但是改变只能在初始化InnoDB实例前进行修改,之后便无法进行修改,除非mysqldump导出创建新库,常见的页类型有:数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。
行对应的是表中的行记录,每页存储最多的行记录也是有硬性规定的最多16KB/2-200,即7992行(16KB是页大小,我也不明白为什么要这么算,据说是内核定义)
B树与B+树通常用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。B+ 树的特点是能够保持数据稳定有序,其插入与修改拥有较稳定的对数时间复杂度。
2.1 B树
定义:
B树(B-TREE)满足如下条件,即可称之为m阶B树:
2.2 B+树
定义:
B+树满足如下条件,即可称之为m阶B+树:
B树与B+树区别:
以m阶树为例:
3.1 聚簇索引
每个InnoDB的表都拥有一个索引,称之为聚簇索引,此索引中存储着行记录,一般来说,聚簇索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作,理解InnoDB聚簇索引是很有必要的。
聚簇索引按照如下规则创建:
Note: 对于选择唯一索引的顺序是按照定义唯一索引的顺序,而非表中列的顺序, 同时选中的唯一索引字段会充当为主键,或者InnoDB隐式创建的自增列也可以看做主键。
聚簇索引整体是一个b+树,非叶子节点存放的是键值,叶子节点存放的是行数据,称之为数据页,这就决定了表中的数据也是聚簇索引中的一部分,数据页之间是通过一个双向链表来链接的,上文说到B+树是一棵平衡查找树,也就是聚簇索引的数据存储是有序的,但是这个是逻辑上的有序,但是在实际在数据的物理存储上是,因为数据页之间是通过双向链表来连接,假如物理存储是顺序的话,那维护聚簇索引的成本非常的高。
3.2 辅助索引
除了聚簇索引之外的索引都可以称之为辅助索引,与聚簇索引的区别在于辅助索引的叶子节点中存放的是主键的键值。一张表可以存在多个辅助索引,但是只能有一个聚簇索引,通过辅助索引来查找对应的航记录的话,需要进行两步,第一步通过辅助索引来确定对应的主键,第二步通过相应的主键值在聚簇索引中查询到对应的行记录,也就是进行两次B+树搜索。相反通过辅助索引来查询主键的话,遍历一次辅助索引就可以确定主键了,也就是所谓的索引覆盖,不用回表(查询聚簇索引)。
创建辅助索引,可以创建单列的索引,也就是用一个字段来创建索引,也可以用多个字段来创建副主索引称为联合索引,创建联合索引后,B+树的节点存储的键值数量不是1个,而是多个,如下图:
order by
对某个字段进行排序时,可以减少复杂度,加速进行查询;select * from table where a=? and ?
可以使用索引(a,b)来加速查询,但是在查询时有一个原则,sql的where条件的顺序必须和二级索引一致,而且还遵循索引最左原则,select * from table where b=?
则无法利用(a,b)索引来加速查询。以下的每一步操作都会生成一个虚拟表,作为下一个处理的输入,在这个过程中,这些虚拟表对于用户都是透明的,只用最后一步执行完的虚拟表返回给用户,在处理过程中,没有的步骤会直接跳过。
以下为逻辑上的执行顺序:
(1) from:对左表left-table和右表right-table执行笛卡尔积(a*b),形成虚拟表VT1;
(2) on: 对虚拟表VT1进行on条件进行筛选,只有符合条件的记录才会插入到虚拟表VT2中;
(3) join: 指定out join会将未匹配行添加到VT2产生VT3,若有多张表,则会重复(1)~(3);
(4) where: 对VT3进行条件过滤,形成VT4, where条件是从左向右执行的;
(5) group by: 对VT4进行分组操作得到VT5;
(6) cube | rollup: 对VT5进行cube | rollup操作得到VT6;
(7) having: 对VT6进行过滤得到VT7;
(8) select: 执行选择操作得到VT8,本人看来VT7和VT8应该是一样的;
(9) distinct: 对VT8进行去重,得到VT9;
(10) order by: 对VT9进行排序,得到VT10;
(11) limit: 对记录进行截取,得到VT11返回给用户。
Note: on条件应用于连表过滤,where应用于on过滤后的结果(有on的话),having应用于分组过滤
索引有如下有点:减少服务器扫描的数据量、避免排序和临时表、将随机I/O变为顺序I/O。
可使用B+树索引的查询方式
范围查询后的条件不会走索引,具体原因会在下一节进行介绍。
列的选择性(区分度)
选择性(区分度)是指不重复的列值个数/列值的总个数,一般意义上建索引的字段要区分度高,而且在建联合索引的时候区分度高的列字段要放在前边,这样可以在第一个条件就过滤掉大量的数据,有利用性能的提升,对于如何计算列的区分度,有如下两种方法:
show index from <table_name>
来查看,解释一下,此处的carlinality并不是准确值,而且 MySQL在B+树种选择了8个数据页来抽样统计的值,也就是说carlinality=每个数据页记录总和/8*所有的数据页,因此也说明这个值是不准确的,因为在插入/更新记录时,实时的去更新carlinality对于 MySQL的负载是很高的,如果数据量很大的话,触发 MySQL重新统计该值得条件是当表中的1/16数据发生变化时。但是选择区分度高的列作为索引也不是百试百灵的,某些情况还是不合适的,下节会进行介绍。
MySQL查询过程
当希望 MySQL能够高性能运行的时候,最好的办法就是明白 MySQL是如何优化和执行的,一旦理解了这一点,很多查询优化工作实际上就是遵循了一些原则让优化器能够按照预想的合理的方式运行————《引用自高性能 MySQL 》
当想 MySQL实例发送一个请求时, MySQL按照如下图的方式进行查询:
注意&建议
这个部分是我在学习过程中产生的一些疑问,以及在工作中碰到的或者同事提起的一些问题,对此我做了些调研,总结了一下并添加了些自己的理解,如有错误还请指正。
索引分裂
此处提一下索引分裂,就我个人理解,在 MySQL插入记录的同时会更新配置的相应索引文件,根据以上的了解,在插入索引时,可能会存在索引的页的分裂,因此会导致磁盘数据的移动。当插入的主键是随机字符串时,每次插入不会是在B+树的最后插入,每次插入位置都是随机的,每次都可能导致数据页的移动,而且字符串的存储空间占用也很大,这样重建索引不仅仅效率低而且 MySQL的负载也会很高,同时还会导致大量的磁盘碎片,磁盘碎片多了也会对查询造成一定的性能开销,因为存储位置不连续导致更多的磁盘I/O,这就是为什么推荐定义主键为递增整型的一个原因, MySQL索引页默认大小是16KB,当有新纪录插入的时候, MySQL会留下每页空间的1/16用于未来索引记录增长,避免过多的磁盘数据移动。
自增主键的弊端
对于高并发的场景,在InnoDB中按照主键的顺序插入可能会造成明显的争用,主键的上界会成为“热点”,因为所有的插入都发生在此处,索引并发的插入可能会造成间隙锁竞争,何为间隙锁竞争,下个会详细介绍;另外一个原因可能是Auto_increment的锁机制,在 MySQL处理自增主键时,当innodb_autoinc_lock_mode
为0或1时,在不知道插入有多少行时,比如insert t1 xx select xx from t2
,对于这个statement的执行会进行锁表,只有这个statement执行完以后才会释放锁,然后别的插入才能够继续执行,但是在innodb_autoinc_lock_mode=2
时,这种情况不会存在表锁,但是只能保证所有并发执行的statement插入的记录是唯一并且自增的,但是每个statement做的多行插入之间是不连接的。
优化器不使用索引选择全表扫描
比如一张order表中有联合索引(order_id, goods_id),在此例子上来说明这个问题是从两个方面来说:
select order_id from order where order_id > 1000
,如果查看其执行计划的话,发现是用use index condition,走的是索引覆盖。
select * from order where order_id > 1000
, 此条语句查询的是该表所有字段,有一部分字段并未在此联合索引中,因此走联合索引查询会走两步,首先通过联合索引确定符合条件的主键id,然后利用这些主键id再去聚簇索引中去查询,然后得到所有记录,利用主键id在聚簇索引中查询记录的过程是无序的,在磁盘上就变成了离散读取的操作,假如当读取的记录很多时(一般是整个表的20%左右),这个时候优化器会选择直接使用聚簇索引,也就是扫全表,因为顺序读取要快于离散读取,这也就是为何一般不用区分度不大的字段单独做索引,注意是单独因为利用此字段查出来的数据会很多,有很大概率走全表扫描。
范围查询之后的条件不走索引
根据 MySQL的查询原理的话,当处理到where的范围查询条件后,会将查询到的行全部返回到服务器端(查询执行引擎),接下来的条件操作在服务器端进行处理,这也就是为什么范围条件不走索引的原因了,因为之后的条件过滤已经不在存储引擎完成了。但是在 MySQL 5.6以后假如了一个新的功能index condition pushdown(ICP),这个功能允许范围查询条件之后的条件继续走索引,但是需要有几个前提条件:
select * from xx where c1=x and c2>x and c3<x
,这样c3是可以走到索引的;set @@optimizer_switch = "index_condition_pushdown=on" 开启ICP set @@optimizer_switch = "index_condition_pushdown=off" 关闭ICP
范围查询统计函数不遵循 MySQL索引最左原则
比如创建一个表:
create table `person`( `id` int not null auto_increment primary key, `uid` int not null, `name` varchar(60) not null, `time` date not null, key `idx_uid_date` (uid, time) )engine=innodb default charset=utf8mb4;
当执行select count(*) from person where time > '2018-03-11' and time < '2018-03-16'
时,time是可以用到idx_uid_date`的索引的,看如下的执行计划:
其中extra标识use index说明是走索引覆盖的,一般意义来说是 MySQL是无法支持松散索引的,但是对于统计函数,是可以使用索引覆盖的,因此 MySQL的优化器选择利用该索引。
分页offset值很大性能问题
在 MySQL中,分页当offset值很大的时候,性能会非常的差,比如limit 100000, 20,需要查询100020条数据,然后取20条,抛弃前100000条,在这个过程中产生了大量的随机I/O,这是性能很差的原因,为了解决这个问题,切入点便是减少无用数据的查询,减少随机I/O。 解决的方法是利用索引覆盖,也就是扫描索引得到id然后再从聚簇索引中查询行记录,我知道有两种方式:
比如从表t1中分页查询limit 1000000,5
select * from t1 inner join (select id from t1 where xxx order by xx limit 1000000,5) as t2 using(id)
,子查询先走索引覆盖查得id,然后根据得到的id直接取5条得数据。
select * from t1 where id > 1000000 order by id limit 0, 5
,即利用条件id > 1000000
在扫描索引是跳过1000000条记录,然后取5条即可,这种处理方式的offset值便成为0了,但此种方式通常分页不能用,但是可以用来分批取数据。
索引合并
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
对于如上的sql在 MySQL 5.0版本之前,假如没有建立相应的联合索引,是要走全表扫描的,但是在 MySQL 5.1后引入了一种优化策略为索引合并,可以在一定程度上利用表上的多个单列索引来定位指定行,其原理是将对每个索引的扫描结果做运算,总共有:交集、并集以及他们的组合,但是索引合并并非是一种合适的选择,因为在做索引合并时可能会消耗大量的CPU和内存资源,一般用到索引合并的情况也从侧面反映了该表的索引需要优化。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。