MySQL篇-索引应该怎么创建

索引的类型

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列,所以agebirth无法使用索引列

  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

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

对于BLOBTEXTVARCHAR等大类型的列,都要建立前缀索引,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%