MySQL进阶 - 索引
MySQL进阶 - 索引
索引概述
概念
索引是帮助MySQL的数据结构
(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式饮用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
优缺点
- 优点
- 提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗
- 缺点
- 索引列也是要占用空间的
- 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。
注意
索引是一种典型的使用空间换时间的例子。
索引结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种:
索引结构 | 描述 |
---|---|
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree空间索引 | 空间索引是MyISAM引擎的一个特殊索引,主要用于地理空间数据类型,通常使用较少 |
Full-text全文索引 | 是一种通过建立倒排索引,快速匹配文档的方式,类似于Lucene,Solr,Es |
二叉树
缺点
使用二叉树做索引结构,顺序插入时,会退化成为一个链表,查询性能大大降低。大量数据的情况下,层级较深,检索速度慢。
红黑树
缺点
红黑树虽然解决了退化为单链表的这种现象,但是其本质上仍然是二叉树,在大量数据的情况下,层级较深,检索速度还是很慢。
B树
B树(B-Tree)又称为多路平衡查找树,一般是以阶树来衡量B树,以其最大度数作为B树的阶数,一棵n阶B树的节点最多能存储n-1个数据,n个指针。
小知识
树的度数指的是一个节点的子节点个数。
B树随着数据的插入或者删除会自动进行节点的裂变和合并,这里的具体细节可以去学习数据结构中的B树。
B树知识点
// todo 待更新
B树
B+树
B+树是B树的一个变种,基本的结构和节点的变化规律不变,仅在非叶子结点中存储索引,在叶子结点中存储数据。并且在叶子结点中,相邻的叶子结点会有一个单向的指针连接,形成一个单向链表。
优化的B+树
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上增加了一个指向相邻叶子结点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
Hash
哈希索引就是采用一定的Hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
是哈希结构,那么就不可避免的会出现哈希碰撞(哈希冲突),即多个键值对映射到了同一个槽位上,此时就产生了哈希碰撞,可以通过构建链表来解决。
知识点
解决哈希碰撞的方法有很多,如:拉链法、开放寻址法、再哈希法、建立公共溢出区等方法进行解决。
哈希索引的特点:
- hash索引只能用于对等比较(== , in) ,不支持范围查找(between , > , < , ...)
- 无法利用索引完成排序操作。
- 查询效率高,通常只需要一次检索就可以了,效率通常要高于B+Tree索引。
存储引擎支持
在MySQL中,支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。其他引擎不支持。
自适应hash就是InnoDB存储引擎根据我们的查询条件,在指定的条件下会自动的将B+树索引构建成hash索引。
思考
思考题
为什么InnoDB存储引擎选择使用B+树索引结构?
- 相对于二叉树,在相同数据规模的情况下,B+树具有更少的层级,查询速度更快
- 对于B树,无论是叶子结点还是非叶子结点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要保存大量数据,只能增加树的高度,导致性能降低。
- 对于Hash索引,B+树支持范围匹配和排序操作。
存储引擎支持
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+Tree索引 | ✅ | ✅ | ✅ |
Hash索引 | ❌ | ❌ | ✅ |
R-Tree索引 | ❌ | ✅ | ❌ |
Full-text索引 | 5.6版本以后✅ | ✅ | ❌ |
索引分类
索引主要分为4个类型:主键索引、唯一索引、常规索引、全文索引
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引Clustered Index | 将数据和索引的存储放到了一块,索引结构的叶子结点保存了行数据 | 必须有,而且只能有一个 |
二级索引Secoundary Index | 将数据和索引分开存储,索引结构的叶子结点关联的是对应的主键 | 可以存在多个 |
聚集索引的选取规则:
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一UNIQUE索引作为聚集索引。
- 如果没有主键,也没有合适的唯一索引,那么InnoDB引擎就会生成一个rowid作为隐藏的聚集索引。
回表查询
我们在对数据的查询时,通常会给定查询条件,而当我们的查询条件并不是聚集索引中的值,而是二级索引中的值,这时候的查询过程为:
- 先通过筛选条件在二级索引中查找对应的记录值,找到记录值的id;
- 使用记录值的id再在聚集索引中查询,得到数据的记录值。
这个过程我们就叫做回表查询,虽然只执行了一条查询语句,但是实际的底层在B+树内查询了两次。
思考题
- 以下SQL语句中,那个执行效率更高?为什么?
select * from user where id = 10;
select * from user where name = 'Mast';
备注:id为主键,name字段创建的有索引。
答案
第一条SQL使用id查询的效率更高,因为id是主键,而主键会建立聚集索引,聚集索引中查询直接就能够得到数据的记录值。而使用name字段查询,虽然建立有索引,但是其为二级索引,查询后的值为该字段对应的id值,此时还需要通过id值在聚集索引中查询,有回表查询,查询了两次B+树,而第一条SQL只查询了一次B+树。
- InnoDB主键索引的B+Tree高度为多高呢?
答案
假设:
一行数据大小为1K=1024B=1024字节,一页中能存储16行这样的数据,InnoDB的指针占用6个字节的空间,主键即使为bigint,占用8个字节。高度为2时:
n * 8 + (n + 1) * 6 = 16 * 1024
解得:n = 1170所以一共能存储 1171 * 16 = 18736 个数据
高度为3时:
1171 * 1171 * 16 = 21939856 个数据
索引语法
- 创建索引
CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name(index_col_name,...);
在表 table_name 中创建名为index_name 的索引。
注意
[UNIQUE | FULLTEXT]是可选项,分别为创建唯一索引和全文索引,如果都不选的话,则创建的是常规索引。一个索引可以关联多个字段。
根据索引关联字段的个数,可以讲索引分为两种类型:
- 单列索引:索引列表只有一个字段。
- 联合索引:又叫组合索引,即索引列表有多个字段。
- 查看索引
SHOW INDEX FROM table_name;
查看指定表的索引。
- 删除索引
DROP INDEX index_name ON table_name;
删除表 table_name 名为 index_name 的索引。
索引使用
索引失效场景
- 最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
举个简单的例子,如为 mail(5)、tel(6)、username(7)三个字段建立联合索引,括号内为长度,则在使用的时候根据 mail -> tel -> username 这个顺序来判断是否走索引。
- 查询条件的顺序为:mail - tel - username,则走索引,长度为 5 + 6 + 7 = 18
- 查询条件的顺序为:mail - username - tel,则走索引,但长度为 5
- 查询条件的顺序为:tel - username - mail,则不走索引,长度为NULL
......以此类推,满足最左前缀法则,哪里不满足,则从哪里断开,可以根据索引长度来判断
范围查询
联合索引中,出现范围查(>
,<
),范围查询右侧的列索引失效。索引列运算
不要在索引列上进行运算操作,索引将失效。字符串类型不加引号
在字符串类型字段上使用时,不加引号,索引将失效。模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是开头模糊匹配,则索引失效。or连接的条件
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被使用到。数据分布影响
如果MySQL评估使用索引比全表更慢,则不会使用索引。
SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化的目的。
- use index 建议使用索引
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
- ignore index 忽略索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
- force index 强制索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select *
即尽量保证所需要的数据在使用索引内包含,保证所需要的所有数据都能够通过索引查询到。即保证返回的字段,能通过索引直接查到,不回表查询。
小贴士
using index condition:查找使用了索引,但是需要回表查询数据。
using where;using index:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据。
前缀索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
- 语法
create index idx_xxxx on table_name(column(n));
- 前缀长度
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
select count(distinct email) / count(*) from tb_user;
select count(distanct substring((email,1,5) / count(*) from tb_user;
单列/联合索引
- 单列索引:即一个索引值包含了单个列
- 联合索引:即一个索引值包含了多个列
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。
小知识
有时候即便我们创建了联合索引,MySQL也不一定就会使用,在多联合查询时,MySQL优化器会评估哪个字段的索引效率更高,会选择该索引完成本次查询。
索引设计原则
- 针对于数据量较大,且查询比较频繁的表建立索引。
- 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
- 尽量选择区分度较高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
- 如果是字符串类型和字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改茶的效率。
- 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引更有效地用于查询。