Mysql的索引在我学完后还是感觉比较模糊,这篇文章通过几个常见的问题,来对索引进行梳理,加深理解
1. 什么是索引?
索引从本质上就是一种用于加速数据查询的数据结构,比如说常见的数组,其下标就是某个数据的索引,常见的哈希表,其key就是value的索引,B+树,每个非叶子节点上建立了关于数据页的指针,通过这些手段,能够避免全表的查询,实现快速查找
2. 索引的分类有哪些?
我认为,索引的分类可以从这四个方面进行划分
- 首先第一个是索引的物理存储结构,在这方面可以分为聚簇索引和非聚簇索引,所谓聚簇索引的核心理念就是
索引即数据,索引与数据存储在一起,典型的,在B+树中有着叶子节点这样的数据,它的索引和数据是在一起存放的 - 第二个就是可以根据索引的数据组织结构进行分类,索引的实现一般可以分为三种,第一种是
Hash结构,它是基于Hash()函数实现的,第二种是B+树,通过压缩查找次数实现的,第三种是倒排索引,这种索引比较复杂,我仅仅了解过,它可以实现大文本的查询,但是目前对于这种数据的搜索可以基于es等搜索引擎实现 - 第三个是可以根据索引的特性进行分类,索引可以分为
主键索引,在InnoDB下,主键索引对应着一棵B+树,是真正存储数据的数据结构,第二种是唯一索引,它规定了索引值必须唯一,这里的值是指它的列值,列值可以为NULL。第三种是普通索引,普通索引如其名,它根据它的列名组织出来了一棵B+树,通过这棵B+树能够快速定位到一整条数据(相比于全表查询) - 第四个是可以根据组织起索引的字段个数,分别有单个字段索引和多个字段索引
3. 不同存储引擎的索引有什么区别?
这个问题比较细节,通过一张图来记忆
一般来说,目前实现的索引模型有B+Tree和Hash索引以及Full Text
首先先来讲讲B+ Tree,B+ Tree是三个存储引擎都支持的,但是有略微的不同
MyISAM中的索引和数据不是存放在一起的,而是两个不同的文件,索引文件的叶子节点存储的是主键+记录的物理地址位置,然后最终通过查找真实记录表,通过之前查找到的物理地址位置快速定位到记录
InnoDB中的索引和数据是存放在一起的,是一个文件.ibd。
Hash结构只有memory引擎支持,但是在InnoDB中存在自适应哈希表,这个哈希表会将大量查询的数据的主键值和物理地址存储到一个哈希表中,当下一次来查询的时候,不需要在树上进行搜索,而是直接通过哈希映射得到物理地址
Full-Text是倒排索引,它用来实现文本查询,Memory引擎不支持
4. 说说InnoDB的索引特点
目前来说InnoDB是最常用的存储引擎,因此它的索引使用也是最广泛的
首先先来说一下主键索引的生成,为什么呢,这是因为InnoDB中的特性是索引即数据,那么它必须要有一个可以作为主键的列来生成这棵索引树,否则的话无法存储数据,有三种情况
- 如果主动设置了主键,那么直接以该主键为聚簇索引的键
- 如果没有主动设置主键,但是有一个
NOT && UNIQUE的列,那么选取这个列作为聚簇索引的键 - 如果都没有,那么启用隐藏字段的
row_id来替代索引的键
那么在其他设置的索引就是二级索引或者叫非聚簇索引了,这类索引在数据的搜索的一个问题是存在回表的问题,但是这个问题可以通过索引覆盖来进行解决
下面来讲讲详细的B+树的数据结构
首先我们要从最基本的结构开始,每一个节点都是一个页,页里面有很多很多的记录
实际上,B+树中在2层以上起码存在着3级索引加速情况
首先第一级索引,页目录项:目录项记载着存储存储目标记录的页的目录的页号
断句:存储目标记录 的页 的目录
第二级索引:存储目标记录的页的页号
第三级索引:存储目标记录的页中存储的槽
槽
槽的设计我认为就是一种跳表,它将页内的记录再次分组,首先第一个槽是
infirmum,它是用来标记记录存储的开始位置,最后一个槽是结束位置,简单理解成虚拟头节点和虚拟尾结点即可,在这种情况下,槽会记录一个数值,这个数值就是这个分组中最大的记录的偏移地址。
那么槽是怎么组织的呢,为了加速查找,槽的存储形式通常是数组的形式,存储的值是最大的记录的偏移量
槽的管理的信息集中于页头部
简述一下索引的查找流程
首先会从存储中去查找对应的根节点的地址,然后开始查询,比如说要查找主键值是123,然后先查找第一级目录项,它给出了一个页号,比如说是1,然后它就去查找1号页,在查找1号页的时候,由于可能存在很多条记录,通过查找槽数组,然后定位到记录的分组,然后在这个分组里面去顺序查找即可
这时候有两种情况,如果聚簇索引,那么就直接返回记录即可,如果是非聚簇索引,那么还需要先判断索引是否覆盖,如果覆盖了,就不需要回表,否则还是需要回表的
5. 为什么要B+Tree呢?BST不行吗?AVL不行吗?Hash呢?
首先我们先来比较和他血缘关系最近的一个数据结构,叫做B-树,B+树就是由它演化而来的,B-树也是一种N叉树,这种N叉树和B+树有这样的区别
首先同一层的节点之间没有双向链表连接的,因此无法做到高效的连续IO和范围查询
其次,这种数据结构中,数据是存储在不同层的,因此查找的复杂度是不稳定的,不像B+树,B+树由于数据都存在叶子节点,因此查找的数据复杂度十分稳定
B树和B+树
对于B树而言,由于索引和数据都是存储在一起的,因此在页大小固定的情况下,一页之中所能够存储的数据就会更少,层级就会更深,最终导致IO的次数就会更多,那么对于B+树来说,由于非叶子节点都是数据,因此非叶子层级中能够存放的索引就会越多,最终带来的收益就是能够使得I/O的次数越少,同时会使得稳定的搜索复杂度
第二点是能够更快地进行范围查询
BST和B+
首先普通的BST具有一个不平衡的问题,在极端情况下退化为链表搜索的时间复杂度为O(n)
同时,如果要使用目录项的优化策略,在二叉树的情况下,每个节点只能够有两个指向,这样的话就会导致树过高
AVL和B+
AVL能够通过维护平衡性,使得搜索的时间复杂度为O(logn),但是缺点就还是每个节点只能有两个指向,浪费空间而且不灵活
Hash和B+
我们说Hash的特点是:如果知道了输入值,那么输出值就确定了,能够以接近O(1)的时间获取输出值,但是这种数据模型不适合用来做范围查询,比如说我想要找[1,n]区间内的数据,那么我就必须要对这里面的一个个数据进行哈希运算后再取值,而对于B+树这样的结构来说,我只需要通过主键的值,获取到那几页就可以了
6. 说一说联合索引和单字段的索引有什么不同
首先联合索引是由多个列组成的,它在InnoDB中的也是按照索引列的值在树上进行排序的
比如说创建了索引(c,a),那它就是先按照c排序,然后再按照a排序。最终就形成了有序的结果
联合索引存在失效的问题
这里的失效应该说是部分失效,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列,如果跳跃了某一列,索引就是部分失效了
首先,联合索引必须按照索引声明顺序进行条件的查询,否则索引失效
比如说,我必须使用
select c1 from tb where c = 1 and a =2这样的话索引是会生效的
而如果使用的是
select c1 from tb where a = 1 and c =2索引就会失效,转变为全表查询
其实这就是最左前缀匹配原则,用我自己的话来讲就是一个短路运算符,就是说当前面的不匹配索引的时候,就会直接取消使用索引
除了使用顺序的问题,还有一个使用范围查询和模糊查询的情况,我分别来说一下情况
首先是使用范围查询,比如说
select c1 from tb where a > 1 and c = 2在这种情况下,索引将会失效,a的取值是(1,+无穷),它的搜索算法是,在a>1 的第一条记录停下来,然后对后面的所有的记录进行查询
为什么要这样呢?
这是因为范围查询只能过滤掉区间以外的,而区间以内的有可能有很多答案符合要求,因此必须保留然后判断
这个查询还有一个特点,就是在查询的时候用到了索引下推技术,这个角度上,一定程度上利用了索引的结果,性能倒也不算太差,索引下推就是说,原本啊,它得到了相关的索引数据之后,就把这个索引拿去回表了
比如说我通过这个sql,可以找到3条匹配的记录
a = 2,c = 2
a = 2,c = 3
a = 2,c = 4然而我们可以根据c=2来过滤掉后面两条
在Mysql5.6之前就是直接把这三条记录拿回去到server做业务逻辑的
但是在5.6之后就是先看一下字段里面有没有索引,如果有索引的话再做一次过滤,那这样的话后面两条就过滤掉了
select * from t_table where a >= 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
这个>=1决定了(a,b)都用上了索引的B+Tree
如果要查看的话,可以使用explain指令
因此,我们也可以看出,不能够一棒子打死一船人,比如说像between和>= <=这些带有准确值的量,是可以用上索引的
第二种情况是模糊查询
看一个sql
SELECT * FROM t_user WHERE name like 'j%' and age = 22首先这个sql中,也有精确值的,比如说是name = j的时候,这时候索引就可以用上了,name = j and age = 22
因此这条语句是走索引的
如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是”30%”）很高的时候，它一般会忽略索引，进行全表扫描。
select * from order where status = 1 order by create_time asc有的同学会认为，单独给 status 建立一个索引就可以了。
但是更好的方式给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。
因为在查询时，如果只用到 status 的索引，但是这条语句还要对 create_time 排序，这时就要用文件排序 filesort，也就是在 SQL 执行计划中，Extra 列会出现 Using filesort。
所以，要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。
7. 索引操作的语法
- 创建索引
create [unique|fulltext] INDEX index_name on table_name (index_col_name,...)- 查看索引
SHOW INDEX FROM table_name;- 删除索引
DROP INDEX index_name on table_name;8. 如何查看SQL性能
优化通常是对查询的进行优化
- 查看执行的频次
MYSQL客户端连接成功后,可以通过show [session|global] status命令来提供服务器状态信息,通过如下指令,可以查看当前数据库的INSERT等操作
SHOW GLOBAL STATUS LIKE 'Com____'重要:查看慢查询
慢查询日志记录了所有执行时间超过指定参数long_query的所有SQL语句的日志
MySQL的慢查询日志默认是没有开启的,需要在/etc/my.cnf中配置信息
show variables like 'slow_query_log';show profiles
show profiles能够在SQL优化的时候了解时间都耗费在哪了,通过have_profiling变量进行查看
SELECT @@have_profiling;show profiles for query query_id;explain执行计划
explain select * from tb where id = xxx;- id:select查询的序列号,表示查询中执行select子句或者是操作表的顺序,id相同的时候,执行顺序从上到下,否则的就是值越大,越先执行
select_type:表示SELECT的类型,常见的有simple(简单表的查询)、primary(主查询,外层查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE后包含了子查询)type:表示连接的类型,依次有NULL、system、const、eq_ref、ref、range、index、allpossible:可能的索引key:实际使用的索引rows:执行的行数,只是一个预估值filtered:返回结果/读取结果的百分比
- All（全表扫描）；
- index（全索引扫描）；
当index为二级索引的时候,还要算上回表的开销,更加离谱
- range（索引范围扫描）；
range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
- ref（非唯一索引扫描）；
ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
foreach (rowA in A) { foreach (rowC in C) { if (rowA.id == rowC.id) { addToResult(rowA.text, rowC.text); } } }
- eq_ref（唯一索引扫描）；
eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
>foreach (rowA in A) >{ if (existsInBRowWithID(rowA.id) { addToResult(rowA.text, getRowInBWithID(rowA.id).text); break; } >}
- const（结果只有一条的主键或唯一索引扫描）。
const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。
需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。
- Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
- Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
- Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。
9. 如何验证你设计的索引的效率?
一般来说,验证索引的效率可以通过SQL的执行耗时来进行查看,具体的步骤如下
最简单的办法是这样的,首先可以通过执行SQL后的时间耗时进行记录
然后创建一个索引
然后再执行相关的SQL,进行时间的比较即可
一种比较通用的办法是这样的
SHOW @@have_profiliings;然后就可以查询到每一条SQL的执行时间,对于执行索引前后,可以使用\G的等无关紧要的标志进行区分
10. 索引失效的具体场景有哪些
- 在索引列上进行运算操作,将会导致索引失效
explain SELECT * FROM tb WHERE DATE(tt) = '2019-10-01'此时通过执行计划,可以看到它是一个ALL类型的查询
为什么?
这是我们如果要走索引,那么肯定是按照
索引的值进行查询, 然而你运算之后,这个运算结果就不是存储的索引的值了,因此最终无法按照这个索引的进行路径的搜索和二分,退化为全表查询
- 如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的并且没有加括号
explain SELECT * FROM tb WHERE name = 123456;此时也是一个ALL类型的查询,没有用到索引
某一个字段如果没有加单引号,那么就不会走索引
这种情况其实有一种专业的说法,叫做
索引隐式类型转换,这个机制的含义是MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。因此就相当于
explain SELECT * FROM tb WHERE CAST(name as signed int) = 123456;可以看到是对索引列做了一个计算,最终导致了失效
模糊匹配专题
单字段情况
如果仅仅是尾部进行模糊匹配,索引不会失效,如果是头部的话就会失效
explain SELECT * FROM tb WHERE name like 'hhh%';-- 尾部
explain SELECT * FROM tb WHERE name like '%hhh';-- 头部
explain SELECT * FROM tb WHERE name like '%hhh%';-- 头尾都加,肯定失效可以看到第一个语句是走索引的,第二个语句是不走索引的
为什么?
这是因为你组成的条件是叫做
请你给我找出那些以hhh结尾的所有的name,但是它并没有对开头进行限制,因此在这种情况下,索引在搜索的时候,它会按照最左前缀的原则,而不是根据后缀,所以它的搜索区间就是(-无穷,+无穷)但是如果是
a%的话,那么也就是说,它的搜索区间是[a,b)这时候的搜索区间是有效的,因此不是全表查询,索引是有效的
or连接造成的条件
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
