索引的类型
B-Tree索引
在MySQL中,B-Tree索引一般都是采用B+
树结构进行存储。该结构拥有以下特点:
- 父节点中包含所有子节点的指针
- 只有叶子节点包含数据,非叶子节点均不保存数据
- B+树也是一种BST树
- 从左往右的叶子节点之间拥有单向指针连接

可以使用B-Tree索引查找的类型
索引查找类型 | 含义 |
---|---|
全值匹配 | 查找的条件精确匹配索引列 |
最左前缀匹配 | 查找的条件从最左边的索引列开始匹配 |
索引的范围查询 | 对索引列进行某一范围内的查询,无法匹配后面的其它索引列 |
前缀部分匹配 | 查找的条件满足某一列索引的前缀部分匹配,例如”like A%”,这也属性范围匹配 |
索引排序 | 对索引列进行排序,无法匹配后面的其它索引列 |
精确匹配某一列而范围匹配另外一列 | 对某一列索引进行全值匹配,后面对另一列索引进行范围匹配 |
只查询索引 | 只需要查询索引列,这种索引称为“覆盖索引” |
索引查询失效的场景:
- 对某一列索引进行范围查询后,后面的其它索引会失效
例如具有索引(age, birth)
,查询语句:
1 | SELECT * FROM USER WHERE age >= 12 AND birth='1998-10-30' |
birth
索引列会失效,因为age
使用了范围查询
- 索引不是按照最左前缀匹配
例如具有索引(name, age, birth)
,查询语句为:
1 | SELECT * FROM USER WHERE age = 12 AND birth='1998-10-30' |
不匹配最左前缀索引name
列,所以age
和birth
无法使用索引列
- 索引没有按照前缀部分匹配,索引和2相同
1 | SELECT * FROM USER WHERE name LIKE "%A" |
由于name
不符合部分前缀匹配,所以无法使用name
索引
- 跳过了索引的某些中间列
例如具有索引(name, age, birth)
,查询语句为:
1 | SELECT * FROM USER WHERE name = '张三' AND birth = '1998-10-30' |
跳过了age
列,所以只能使用name
索引列进行查询。
判断B-Tree
索引能否正常工作的技巧:
- 判断某个索引条件能否在
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 | hash('张三','1','1998-11-11') = 3294 |
如果出现哈希冲突,会形成一条链表,并比较匹配的条件是否成立,例如有第六行记录:
1 | hash('王五','1','1999-01-24') = 3294 |

查找(’王五’,’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 | SELECT * FROM USER |
因为在发生哈希冲突的情况下,不带上URL
的全值匹配条件,会返回多条数据,B-Tree
索引不会自动比较每条数据是否匹配待查找的值,所以称为伪哈希索引。
常用的哈希函数有:MD5()、CRC32()、FNV64()······
索引的优点
- 将随机I/O变为顺序I/O,减少磁盘寻道的时间
- 大大减少了扫描表和访问磁盘的次数
- 可以帮助服务器避免排序和产生临时表
索引的三星系统:
- 一星:索引把相关的记录都放在了一起;
- 二星:索引中的排列顺序和查找中的排列顺序一致
- 三星:索引的列全部包括了查找中的列
要正确的使索引生效
索引列应该处于独立的位置
使用索引列进行运算时会使索引失效,例如索引(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位出现次数最多的城市:

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

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

发现和真实的数据非常接近,可以认为7是最合适的前缀长度,还要利用标准的参考方式进行计算:
计算出每个城市在所有数据中的可选择性,计算出是3%
,通过取不同的前缀长度使得选择性接近这个值:



当前缀长度达到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
节点和数据行,常见的聚簇索引是主键索引。

非叶子节点包含了指向孩子节点的指针,而叶子节点包含了主键值和数据行。
使用聚簇索引的好处:
- 减少磁盘I/O次数
- 数据访问速度更快,因为一个B-Tree中包含了索引列及所有行数据
- 使用覆盖索引查询时可以直接返回聚簇索引的值
使用聚簇索引的缺点:
- 如果数据存放于内存中,那么聚簇索引没有任何作用,因为它是减少磁盘I/O次数的一种存储方式
- 数据插入速度严重依赖于数据的顺序,如果用
AUTO_INCREMENT
主键生成策略,那么插入速度很快;如果用UUID
生成策略,插入速度很慢 - 非聚簇索引的叶子节点保存聚簇索引的值,如果聚簇索引非常大,那么非聚簇索引的B-Tree也会占用很大的磁盘空间
InnoDB和MyISAM的索引分布区别
两种存储引擎存储索引的数据结构均是B-Tree
,在内部有差别:
聚簇索引
InnoDB:
节点 | 存储内容 |
---|---|
非叶子节点 | 存储索引列以及指向下一个节点的指针 |
叶子节点 | 存储该数据页内的所有行记录 |
MyISAM:
节点 | 存储内容 |
---|---|
非叶子节点 | 与InnoDB存储的内容相同 |
叶子节点 | 存储指向该数据行记录的指针 |
不同点总结:
- InnoDB的叶子节点存储的是行记录本身,而MyISAM的叶子节点存储行记录指针,所以MyISAM需要比InnoDB多访问一次磁盘
非聚簇索引
InnoDB:
节点 | 存储内容 |
---|---|
非叶子节点 | 存储索引列以及指向下一个节点的指针 |
叶子节点 | 存储该记录对应的聚簇索引值(主键值) |
MyISAM:
节点 | 存储内容 |
---|---|
非叶子节点 | 存储索引列以及指向下一个节点的指针 |
叶子节点 | 存储指向该数据行记录的指针 |
可以看到InnoDB的叶子节点存储的是主键的值,所以使用非聚簇索引查找数据时会发生回表,回到主键的B-Tree
上查找(不包括覆盖索引查询情况),而MyISAM的叶子节点还是存储行记录指针,所以查找过程不需要回表。

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:

UUID:

UUID
的缺点在于:
- 插入的顺序并不是符合
B-Tree
的存储顺序,每次都要重新查找插入位置,大量的磁盘I/O,插入时间长; - 主键列的长度过长导致容易产生页分裂,页分裂又会产生内存碎片,占用的空间大
所以自增策略一般使用AUTO_INCREMENT
会比较好。
覆盖索引
覆盖索引是指索引覆盖了查询中的所有列
例如:
1 | EXPLAIN SELECT actor_id, last_name FROM sakila.actor WHERE last_name = 'HOPPER'; |

覆盖索引的好处:
- 减少磁盘I/O的次数。在二级索引上执行覆盖索引的查询不需要回表操作
- 减少数据访问量。索引的数据大小比行数据要小得多,使用覆盖索引查询可以节省很多内存消耗。
覆盖索引的应用:延迟关联,延迟关联用于优化无法使用覆盖索引的查询语句,在子句中使用覆盖索引
MySQL5.6优化操作:索引条件下推
使用索引扫描做排序
MySQL的排序分为两种:文件排序(Using filesort)和 索引排序(type:inedx)
索引排序的优点:
- 效率高,使用索引本身完成排序工作,而文件排序则需要先读出来,再做整体排序;
- 索引本身的排列顺序就是有序的,排序过程中可以根据升序还是降序来确定初始节点是哪一个
索引列最好覆盖查询需要用到的所有列,否则需要一条条数据回表到主键列中查询再进行排序,效率极低。
索引排序的例子:包含索引列(rental_date, inventory_id, customer_id)
1 | EXPLAIN SELECT rental_id FROM rental |

rental_id
包含在索引列(rental_date, inventory_id, customer_id)里,满足索引排序的结果type : ref
关于type中的几个类型说明:type类型讲解文章
可以使用索引排序的场景
- 所有索引列的排序顺序一致(ASC或者DESC),且索引列覆盖查询中的所有列
1 | EXPLAIN SELECT rental_date, inventory_id, customer_id, rental_id |

- 满足最左前缀匹配
1 | EXPLAIN SELECT rental_id FROM rental ORDER BY rental_date ASC, rental_date ASC, inventory_id ASC; |

- 索引列的第一列是常量条件,后面跟上索引列排序
1 | EXPLAIN SELECT rental_id |

无法使用索引排序的场景
- 索引中的列无法覆盖查询列
1 | EXPLAIN SELECT * FROM rental ORDER BY rental_date ASC, inventory_id ASC, customer_id ASC; |

- 索引排序顺序不相同
1 | EXPLAIN SELECT rental_date, inventory_id, customer_id, rental_id |

- 不满足最左前缀匹配
1 | EXPLAIN SELECT rental_id FROM rental ORDER BY inventory_id ASC, customer_id ASC; |

- 跳过了某些索引列进行排序
1 | EXPLAIN SELECT rental_id FROM rental WHERE rental_date = '2005-05-25 20:48:50' ORDER BY customer_id DESC; |

- 某个索引列是范围查询,后面的索引列不能够再使用
应用场景:ORDER BY + LIMIT 子句
冗余和重复索引
重复索引:重复建立已存在的索引列
例如:索引(A)再创建一个索引(A)
冗余索引:为了提高查询性能和满足需求而创建的包含在其它索引列中的索引
例如:索引(A,B),创建一个索引(A),索引(A)是(A,B)的前缀索引
重复索引没有存在的意义,应该避免。
冗余索引的好处:
- 可以提高某些查询的性能
冗余索引的缺点:
- 索引占用的存储空间变大
- 给表的INSERT、UPDATE、DELETE的速度变慢,因为需要插入更多的索引列