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值时，它可以更好地确定哪个索引更有效地用于查询。