第五章—创建高性能的索引
索引基础
- 索引,又叫key(键)
- 在mysql中,存储引擎先在索引中找到检索的内容,然后根据索引结果找到对应的
数据行
- 索引可以包含一个或多个列的值,如果索引包含多个列,那么
列的顺序十分重要
,因为mysql只能高效的使用索引的最左前缀列
最左前缀列
就是KEY(id, name, sex)
,id
在id、name、sex
里面是写在左边的,这就叫最左前缀
索引的类型
- 索引是
在存储引擎层
而不是服务器层实现的
B-Tree索引
- 使用B-Tree数据结构来存储数据
- innoDB使用B+Tree
- B-Tree意味着所有的数据都是按顺序存储,适合查找某一范围内的数据,对数据排序
- 可加快访问数据的速度,存储引擎不再需要扫描全表,而是遍历索引搜索数据
- 既然索引按顺序存储,也就意味着它对数据排序了,索引对数据排序的依据是按照定义索引的顺序来排序,例如
KEY(id, name, sex)
,先对id排序,再是name,最后是sex
B+Tree索引
是B-Tree的改进版本,同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上,并且增加了顺序访问指针,每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说,进行范围查找时只需要查找两个节点,进行遍历即可。而B-Tree需要获取所有节点,相比之下B+Tree效率更高
为什么索引结构默认使用B-Tree,而不是hash,二叉树,红黑树?
- hash:虽然可以快速定位,但是没有顺序,IO复杂度高。
- 二叉树:树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度),并且IO代价高。
- 红黑树:树的高度随着数据量增加而增加,IO代价高。
为什么官方建议使用自增长主键作为索引
- 结合B+Tree的特点,自增主键是连续的,在插入过程中尽量减少页分裂,即使要进行页分裂,也只会分裂很少一部分。
- 并且能减少数据的移动,每次插入都是插入到最后。总之就是减少分裂和移动的频率。
可以用B-Tree索引的查询类型
当使用一下查询类型是可以使用B-Tree索引 假设有以下索引:KEY(id, name, sex)
这个id不一定是唯一标识,例如班级编号等
- 全值匹配:例如查找id为5,name为张三,sex为女的人,匹配全部的列
- 匹配最左前缀:例如只匹配id=5的人
- 匹配列前缀:匹配某一列的开头部分,例如查找name中姓为“李”的人
- 匹配范围值:例如查找id在5到100之间的人
- 精确匹配某一列并范围匹配另外一列:例如查找id=5,name匹配“李”开头的人,即第一列全部匹配,第二列部分匹配
- 只访问索引的查询:只需要查询索引,不需要查询数据行
刚才说了索引中的节点都是有序的,所以索引除了用于按值查找,还可以用于order by等排序操作
- 如果order by子句满足前面列出的查询类型,就可以用来排序
B-Tree索引的限制
- 如果不是按照
最左前缀
原则来查询的话,则无法使用索引:例如无法查找name=张三
的人,也无法查找sex=女
的人,因为他们都不是key
里面最左边的。同理如果第一个索引不是id是name,无法查找名字中以“三”结尾的人
,因为以**结尾不是最左原则
- 不能跳过中间索引的列:不可以跳过name来查找id=5,sex=女的人,如果你这样干了,那mysql只会使用第一个索引:id=5这个条件,放弃其他的。
- 如果查询中
有某个列的范围查询
,则其右边的所有列都无法使用索引优化查询:例如查询id=5 and name like ‘李%’ and sex=女的人,因为name列使用了范围查询(like),所以sex无法使用,只会使用前两列索引
因此索引的顺序很重要,一个经验是将索引选择性最高的放到前面
哈希索引
- 哈希索引基于哈希表实现,
只有精确匹配索引所有列的查询才有效
。 - 解释一下上面的话,假如有哈希索引:
KEY USING HASH(name)
,哈希索引会计算所有name列的值(是值整体,不是值的某一部分),得到一个hash码存储在索引中,同时在哈希表中保存每个hash码指向的数据行的指针
hash码指针指向1235第一行5623第十行5984第五行
- 当你查询
name=张三
的时候,哈希索引会计算张三
这个值,然后得到一个hash码
,再根据这个hash码
在哈希表中找到指定的行的指针,然后找到这行,取这行name的值计算hash看看是否等于“张三”,确保就是要查找的行。 - 如果不是精确查找,也就是你要找张三,但是输入成了张三三,由于张三三计算出来的hash码不等于张三的hash码,于是查找失败。
- 哈希索引的特点是速度快
- 哈希索引存储的是hash码与指针,不是实际数据,无法用于排序
- 不支持
部分
索引列匹配查找,因为哈希要计算全部
值得hash码 只支持精确查找,无法用于部分查找和范围查找 无法用于排序与分组 - 只支持等值比较:
=
、IN
- 不支持
>
一类的查询 - innoDB有自适应哈希索引:他会把频繁使用的索引,
自动
创建哈希索引 - 场景:对于
URL
,有时候URL会很大,不适合B-Tree索引,可以使用哈希索引,通过计算URL的hash码,是比较快的方式
全文索引
- MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。
- 查找条件使用 MATCH AGAINST,而不是普通的 WHERE。
- 全文索引使用倒排索引实现,它记录着关键词到其所在文档的映射。
- InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
索引的优点
- 索引让服务器可以快速定位到表的指定位置
- 大大减少了服务器需要扫描的数量
- 帮助服务器避免排序和临时表
- 将随机I/O变为顺序I/O
什么时候使用索引
- 当表数据量大的时候,要使用索引 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效 对于中到大型的表,索引就非常有效
- 但是对于特大型的表,建立和维护索引的代价将会随之增长。这种情况下,需要用到一种技术可以直接区分出需要查询的一组数据,而不是一条记录一条记录地匹配,例如可以使用分区技术。
- 还有一点就是,对于超大型数据,B-Tree索引会失效!除非是索引覆盖查询
高性能的索引策略
先讲一下有哪些策略,再讲一下,一些索引的特点
- 独立的列:索引列不能是表达式的一部分,也不能是函数的参数,例如:where id + 1 = 5 这个id + 1 = 5就不是独立的列,直接写成id=4多好 虽然你写一个表达式也可以查询出结果,但是无法使用索引
- 前缀索引和索引选择性 对于特别长的字符列,一般只索引这段长字符的前面一部分(当然哈希索引也很好),从而提高效率 对与bolb和text或者很长的varchar,必须使用前缀索引,mysql不允许全部索引他们
索引的选择性是指:不重复的索引值和记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,查询效率也越高。
对于数据量比较小的索引可以放到最左,例如dog在数据库中有100条记录,cat在数据库中有56231条记录,可以把dog放到最左。因为更小意味着速度更快(前提是你会查它)key
最好是有顺序
的,例如int
型自增的id
- 多列索引: (把频繁查询的列都设置为索引) 在需要使用多个列作为条件进行查询时,使用多列索引比使用多个单列索引性能更好。例如下面的语句中,最好把 actor_id 和 film_id 设置为多列索引。
-
SELECT film_id, actor_ id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;
- 覆盖索引: 索引包含所有需要查询的字段的值。(就是你要查询的数据都是索引) 具有以下优点:
- 索引通常远小于数据行的大小,只读取索引能大大减少数据访问量。
- 一些存储引擎(例如 MyISAM)在内存中只缓存索引,而数据依赖于操作系统来缓存。因此, 只访问索引可以不使用系统调用(通常比较费时)。
- 对于 InnoDB 引擎,若辅助索引能够覆盖查询,则无需访问主索引。
聚簇索引
- 一种数据存储方式;在同一个结构中保存了B-Tree索引和数据行;数据行放在索引的叶子页中
- 一个表只能有一个聚簇索引
- 可以理解聚簇索引“就是”表
- mysql中InnoDB引擎的主键索引为聚簇索引,MyISAM存储引擎采用非聚集索引
- InnoDB通过主键聚集数据,如果没有定义主键,InnoDB将选择一个没有空值的列创建聚簇索引。
优点
- 相关数据保存在一起,不必每次都导致磁盘I/O
- 数据访问速度更快:因为数据和索引都在一个B-Tree中
缺点
- 导致全表扫描变慢
- 二级索引(非聚簇索引)可能会很大,或者访问需要两次索引查找:因为二级索引中保存的行指针,指向的是
主键值
,还需要再根据主键值去聚簇索引中查找数据行 - 更新聚簇索引列的代价很高
非聚簇索引
- 索引数据和存储数据是分离的
二级索引
- 二级索引存储的是记录的主键,而不是数据存储的地址
- 需要两次查询,效率不高
覆盖索引
- 指一个索引覆盖了所有需要查询的字段的值(也就是扫描索引就可获取数据,无需再回去查表)
- 是非常有用的工具,
可以极大地提高性能
- MySQL只能使用B-Tree来做覆盖索引
- 因为索引是按照列值顺序存储的,所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多
聚簇索引与覆盖索引的区别
- 聚簇索引不是索引,是一种数据存储方式;而覆盖索引是索引
- 聚簇索引会将数据存储在索引的叶子节点中,例如 InnoDB 的主索引(primary key)就是聚簇索引,而辅助索引(key)不是聚簇的,这是因为只能将数据存储在一个索引上。MyISAM 的主索引不是聚簇的。
- 而覆盖索引是指,使用索引来
覆盖查询到的所有字段
,比如查询涉及到的字段为 a,b,c,那么就在这三个字段上设置索引
,因为索引的本身包含了这些字段的值,因此也不需要进行磁盘操作。 - mysql会同时使用到这两种索引
压缩前缀索引
- 对较长的字符串压缩,从而让更多的索引可以放入内存中
索引和锁
- 索引可以让查询锁定更少的行
- innoDB在访问行的时候会对其加锁,索引可以减少innoDB访问的行数,从而减少锁的数量
- 如果做一个查询:
select id from user where id < 5;
返回如下
|------| | id | |------| | 2 | | 3 | | 4 | --------
虽然只返回了2、3、4行的数据,但是实际上是获得了1~4行的排它锁(X锁),只有在查询结束后X锁才会被释放
- 把需要做范围查询的列放到索引的最后