MySQL篇-索引应该怎么创建

发表于 | 分类于
字数统计 3,605字 | 阅读时长 14分钟

索引的类型

B-Tree索引

在MySQL中，B-Tree索引一般都是采用B+树结构进行存储。该结构拥有以下特点：

  1. 父节点中包含所有子节点的指针
  2. 只有叶子节点包含数据，非叶子节点均不保存数据
  3. B+树也是一种BST树
  4. 从左往右的叶子节点之间拥有单向指针连接
image-20200505112503173

可以使用B-Tree索引查找的类型

索引查找类型 含义
全值匹配 查找的条件精确匹配索引列
最左前缀匹配 查找的条件从最左边的索引列开始匹配
索引的范围查询 对索引列进行某一范围内的查询，无法匹配后面的其它索引列
前缀部分匹配 查找的条件满足某一列索引的前缀部分匹配，例如”like A%”，这也属性范围匹配
索引排序 对索引列进行排序，无法匹配后面的其它索引列
精确匹配某一列而范围匹配另外一列 对某一列索引进行全值匹配，后面对另一列索引进行范围匹配
只查询索引 只需要查询索引列，这种索引称为“覆盖索引”

索引查询失效的场景：

  1. 对某一列索引进行范围查询后，后面的其它索引会失效

例如具有索引（age, birth），查询语句：

1
SELECT * FROM USER WHERE age >= 12 AND birth='1998-10-30'

birth索引列会失效，因为age使用了范围查询

  1. 索引不是按照最左前缀匹配

例如具有索引（name, age, birth），查询语句为：

1
SELECT * FROM USER WHERE age = 12 AND birth='1998-10-30'

不匹配最左前缀索引name列，所以age和birth无法使用索引列

  1. 索引没有按照前缀部分匹配，索引和2相同
1
SELECT * FROM USER WHERE name LIKE "%A"

由于name不符合部分前缀匹配，所以无法使用name索引

  1. 跳过了索引的某些中间列

例如具有索引（name, age, birth），查询语句为：

1
SELECT * FROM USER WHERE name = '张三' AND birth = '1998-10-30'

跳过了age列，所以只能使用name索引列进行查询。

判断B-Tree索引能否正常工作的技巧：

  1. 判断某个索引条件能否在B-Tree索引中按照BST的方式进行查询，如果可以工作，那么该索引列就是有效的
1
SELECT * FROM USER WHERE name LIKE 'A%' AND age = 12

name是可以工作的，可以根据前缀部分匹配进行二分查找，找到所有的记录，但是age不能工作，因为在name是部分匹配，不能按照age进行全值匹配的BST查找。

Hash索引

哈希索引用于精确匹配所有索引列的查询中，基本结构如下：

哈希值 数据指针
3294 指向第1行记录
4293 指向第3行记录
8523 指向第2行记录
9867 指向第5行记录
10724 指向第4行记录

可以看到，哈希索引保存的数据指针不是顺序排列，所以：哈希索引不能排序查询

假如有索引(name, sex, birth)，数据如下表所示

name sex birth
张三 1 1998-11-11
李四 0 2000-11-11
李狗子 1 1999-10-30
二狗蛋 0 2005-10-27
令狐冲 1 2008-07-30

建立的哈希索引方式如下：

1
2
3
4
5
hash('张三','1','1998-11-11') = 3294
hash('李四','0','2000-11-11') = 8523
hash('李狗子','1','1999-1030') = 4293
hash('二狗蛋','0','2005-10-27') = 10724
hash('令狐冲','1','2008-07-30') = 9867

如果出现哈希冲突，会形成一条链表，并比较匹配的条件是否成立，例如有第六行记录：

1
hash('王五','1','1999-01-24') = 3294
image-20200505142251559

查找（’王五’,’1’,’1999-01-24’）这条记录时，会与（’张三’,’1’,’1998-11-11’）冲突，冲突后会比较待查找的值与对应的值是否相同，相同就返回记录。

哈希索引可以应用在B-Tree中（伪哈希索引）：为某一个特别长的列建立哈希值，并用这个哈希值建立索引，可以避免索引占用空间过大

例如：一个列是存储URL地址（http://smallpineapp1e.github.io），使用该列做索引就会使索引占用太大的磁盘空间了。

所以额外建立一列：hash_url

URL hash_url
http://smallpineapp1e.github.io hash(‘http://smallpineapp1e.github.io') = 23942

对hash_url建立索引列，可以使索引长度大大缩短。

但是查询的时候要注意加上全值匹配查询，例如：

1
2
3
SELECT * FROM USER 
WHERE hash_url = hash('http://smallpineapp1e.github.io') 
AND URL = 'http://smallpineapp1e.github.io';

因为在发生哈希冲突的情况下，不带上URL的全值匹配条件，会返回多条数据，B-Tree索引不会自动比较每条数据是否匹配待查找的值，所以称为伪哈希索引。

常用的哈希函数有：MD5()、CRC32()、FNV64()······

索引的优点

  1. 将随机I/O变为顺序I/O，减少磁盘寻道的时间
  2. 大大减少了扫描表和访问磁盘的次数
  3. 可以帮助服务器避免排序和产生临时表

索引的三星系统：

  1. 一星：索引把相关的记录都放在了一起；
  2. 二星：索引中的排列顺序和查找中的排列顺序一致
  3. 三星：索引的列全部包括了查找中的列

要正确的使索引生效

索引列应该处于独立的位置

使用索引列进行运算时会使索引失效，例如索引（actor_id）

1
SELECT * FROM sakila.actor WHERE actor_id + 1 = 5;

actor_id + 1 是一个致命的错误，MySQL无法解析这个表达式，使索引列失效。

正确的做法是：actor_id = 5 - 1，或者直接点：actor_id = 4

创建前缀索引和选择合适的前缀长度

对于BLOB、TEXT、VARCHAR等大类型的列，都要建立前缀索引，MySQL不允许对整列建立索引。

要选择一个合适的前缀索引，利用前缀索引查找到的数据必须和预期的数据非常接近，否则会产生大量的冲突值。

创建一个city_demo表，并返回前10位出现次数最多的城市：

image-20200505151102485

尝试用前缀长度为3查询前10个出现最多次数的城市：

image-20200505151216670

结果远大于真实出现的最多次数，代表重复值特别多，无法真正查找出出现次数最多的前10个城市，增加前缀长度到7时：

image-20200505151351341

发现和真实的数据非常接近，可以认为7是最合适的前缀长度，还要利用标准的参考方式进行计算：

计算出每个城市在所有数据中的可选择性，计算出是3%，通过取不同的前缀长度使得选择性接近这个值：

image-20200505151516540 image-20200505151906659

当前缀长度达到7时，已经不再增长了，同时也非常靠近真实值的选择性，所以可以选择前缀为7的长度建立前缀索引列：

1
ALTER TABLE city_demo ADD KEY(city(7));

选择合适的索引列顺序

使用sakila数据库的payment表作例子：

1
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

判断应该创建（staff_id，customer_id）还是创建（customer_id，staff_id）可以根据选择性来确定，优先将选择性高的列作为索引的最左前缀。

两个列的选择性计算：

customer_id的选择性为0.0373，大于staff_id，所以可以认为customer_id放在最左前缀

聚簇索引

聚簇索引中保存了B-Tree节点和数据行，常见的聚簇索引是主键索引。

image-20200505171214664

非叶子节点包含了指向孩子节点的指针，而叶子节点包含了主键值和数据行。

使用聚簇索引的好处：

  1. 减少磁盘I/O次数
  2. 数据访问速度更快，因为一个B-Tree中包含了索引列及所有行数据
  3. 使用覆盖索引查询时可以直接返回聚簇索引的值

使用聚簇索引的缺点：

  1. 如果数据存放于内存中，那么聚簇索引没有任何作用，因为它是减少磁盘I/O次数的一种存储方式
  2. 数据插入速度严重依赖于数据的顺序，如果用AUTO_INCREMENT主键生成策略，那么插入速度很快；如果用UUID生成策略，插入速度很慢
  3. 非聚簇索引的叶子节点保存聚簇索引的值，如果聚簇索引非常大，那么非聚簇索引的B-Tree也会占用很大的磁盘空间

InnoDB和MyISAM的索引分布区别

两种存储引擎存储索引的数据结构均是B-Tree，在内部有差别：

聚簇索引

InnoDB：

节点 存储内容
非叶子节点 存储索引列以及指向下一个节点的指针
叶子节点 存储该数据页内的所有行记录

MyISAM：

节点 存储内容
非叶子节点 与InnoDB存储的内容相同
叶子节点 存储指向该数据行记录的指针

不同点总结：

  1. InnoDB的叶子节点存储的是行记录本身，而MyISAM的叶子节点存储行记录指针，所以MyISAM需要比InnoDB多访问一次磁盘

非聚簇索引

InnoDB：

节点 存储内容
非叶子节点 存储索引列以及指向下一个节点的指针
叶子节点 存储该记录对应的聚簇索引值（主键值）

MyISAM：

节点 存储内容
非叶子节点 存储索引列以及指向下一个节点的指针
叶子节点 存储指向该数据行记录的指针

可以看到InnoDB的叶子节点存储的是主键的值，所以使用非聚簇索引查找数据时会发生回表，回到主键的B-Tree上查找（不包括覆盖索引查询情况），而MyISAM的叶子节点还是存储行记录指针，所以查找过程不需要回表。

image-20200505232559728

InnoDB自增和UUID主键生成策略的差距

自增采用AUTO_INCREMENT生成主键，UUID采用随机产生字符串形式生成主键，两者的速度和占用空间有天壤之别：

假设有两张表：userinfo、userinfo_uuid，分别插入100万行数据和300万行数据，比较插入花费的总时间和索引的占用空间：

表 插入总行数 花费时间（秒） 索引空间大小（MB）
userinfo 1000000 137 342
userinfo_uuid 1000000 180 544
userinfo 3000000 1233 1036
userinfo_uuid 3000000 4525 1707

随着行数增加，差距越来越大，AUTO_INCREMENT的优势越来越明显，产生差距的原因在于B-Tree这种存储结构：

AUTO_INCREMENT：

image-20200505234106030

UUID：

image-20200505234123578

UUID的缺点在于：

  1. 插入的顺序并不是符合B-Tree的存储顺序，每次都要重新查找插入位置，大量的磁盘I/O，插入时间长；
  2. 主键列的长度过长导致容易产生页分裂，页分裂又会产生内存碎片，占用的空间大

所以自增策略一般使用AUTO_INCREMENT会比较好。

覆盖索引

覆盖索引是指索引覆盖了查询中的所有列

例如：

1
EXPLAIN SELECT actor_id, last_name FROM sakila.actor WHERE last_name = 'HOPPER';

覆盖索引的好处：

  1. 减少磁盘I/O的次数。在二级索引上执行覆盖索引的查询不需要回表操作
  2. 减少数据访问量。索引的数据大小比行数据要小得多，使用覆盖索引查询可以节省很多内存消耗。

覆盖索引的应用：延迟关联，延迟关联用于优化无法使用覆盖索引的查询语句，在子句中使用覆盖索引

MySQL5.6优化操作：索引条件下推

使用索引扫描做排序

MySQL的排序分为两种：文件排序（Using filesort）和 索引排序（type：inedx）

索引排序的优点：

  1. 效率高，使用索引本身完成排序工作，而文件排序则需要先读出来，再做整体排序；
  2. 索引本身的排列顺序就是有序的，排序过程中可以根据升序还是降序来确定初始节点是哪一个

索引列最好覆盖查询需要用到的所有列，否则需要一条条数据回表到主键列中查询再进行排序，效率极低。

索引排序的例子：包含索引列（rental_date, inventory_id, customer_id）

1
2
3
EXPLAIN SELECT rental_id FROM rental 
WHERE rental_date = '2005-05-25 20:48:50' 
ORDER BY inventory_id ASC, customer_id ASC;

rental_id包含在索引列（rental_date, inventory_id, customer_id）里，满足索引排序的结果type : ref

关于type中的几个类型说明：type类型讲解文章

可以使用索引排序的场景

  1. 所有索引列的排序顺序一致（ASC或者DESC），且索引列覆盖查询中的所有列
1
2
3
EXPLAIN SELECT rental_date, inventory_id, customer_id, rental_id 
FROM rental 
ORDER BY rental_date ASC, inventory_id ASC, customer_id ASC;
  1. 满足最左前缀匹配
1
EXPLAIN SELECT rental_id FROM rental ORDER BY rental_date ASC, rental_date ASC, inventory_id ASC;
  1. 索引列的第一列是常量条件，后面跟上索引列排序
1
2
3
EXPLAIN SELECT rental_id 
FROM rental 
WHERE rental_date = '2005-05-25 20:48:50' ORDER BY inventory_id ASC, customer_id ASC;

无法使用索引排序的场景

  1. 索引中的列无法覆盖查询列
1
EXPLAIN SELECT *  FROM rental ORDER BY rental_date ASC, inventory_id ASC, customer_id ASC;
  1. 索引排序顺序不相同
1
2
3
EXPLAIN SELECT rental_date, inventory_id, customer_id, rental_id  
FROM rental 
ORDER BY rental_date ASC, inventory_id ASC, customer_id DESC;
  1. 不满足最左前缀匹配
1
EXPLAIN SELECT rental_id FROM rental ORDER BY inventory_id ASC, customer_id ASC;
  1. 跳过了某些索引列进行排序
1
EXPLAIN SELECT rental_id FROM rental WHERE rental_date = '2005-05-25 20:48:50' ORDER BY customer_id DESC;
  1. 某个索引列是范围查询，后面的索引列不能够再使用

应用场景：ORDER BY + LIMIT 子句

冗余和重复索引

重复索引：重复建立已存在的索引列

例如：索引（A）再创建一个索引（A）

冗余索引：为了提高查询性能和满足需求而创建的包含在其它索引列中的索引

例如：索引（A，B），创建一个索引（A），索引（A）是（A，B）的前缀索引

重复索引没有存在的意义，应该避免。

冗余索引的好处：

  1. 可以提高某些查询的性能

冗余索引的缺点：

  1. 索引占用的存储空间变大
  2. 给表的INSERT、UPDATE、DELETE的速度变慢，因为需要插入更多的索引列
0%