索引是存储引擎用于快速找到记录的一种数据结构. MYSQL先在索引上按值进行查找, 然后返回所有包含该值得数据行. 在MYSQL中, 索引是在存储引擎层, 而不是在服务器层实现的.

索引类型

Tree索引

  • B-Tree索引的查询类型

image.png
B-Tree索引适用于全键值, 键值范围或键前缀查找
image.png

  1. # 全值匹配
  2. SELECT * FROM `gh_activity` WHERE name='Jone' and age=18;
  3. # 匹配最左前缀
  4. SELECT * FROM `gh_activity` WHERE name='Jone';
  5. # 匹配列前缀
  6. SELECT * FROM `gh_activity` WHERE name like 'Jo%';
  7. # 匹配范围值, 例子略
  8. # 精确匹配某一列, 并范围匹配另一列
  9. SELECT * FROM `gh_activity` WHERE name like 'Jo%' and age BETWEEN 18 and 20;
  10. # 只访问索引的查询, 例子略

image.png

  • B-Tree索引的限制
    • 如果不是按照索引的最左列进行查找, 则无法使用索引
    • 不能跳过索引中的列
    • 如果查询中有某个列的范围查询, 则其右边所有列都无法使用索引优化查找(右边的所有列都不走索引)

哈希索引

哈希索引基于哈希表实现, 只有精确匹配索引所有列的查询才有效.
对于每一行数据, 存储引擎都会对所有的索引列计算一个哈希码, 哈希码是一个较小的值, 并且不同键值的行计算出来的哈希码也不一样, 哈希索引将所有的哈希码存储在索引中, 同时在哈希表中保存指向每个数据行的指针. 在查找时, MYSQL会根据计算的查找的索引列的哈希值., 找到对应行的指针.

在MYSQL中只有MEMORY引擎显式支持哈希索引.

哈希索引的限制:

  • 哈希索引只包含哈希值和行指针, 而不存储字段值, 所以不能使用索引中的值来避免读取行, 不过, 访问内存中的行的速度很快, 大部分情况下对性能的影响并不明显
  • 哈希索引并不是按照索引值顺序进行排序的, 是按计算后的哈希值进行排的, 因此不能用于排序
  • 哈希索引页不支持部分索引列匹配查找, 因为哈希索引是利用索引列的全部内容来计算哈希值的
  • 哈希索引只支持等值比较查询, 不支持任何范围查询
  • 访问哈希索引的速度非常快, 除非有很多的哈希冲突
  • 如果哈希冲突很多的话, 一些维护操作的代码也会很高

InnoDB引擎有”自适应哈希索引”, 当注意到某些索引值被使用的非常频繁时, 它会在内存中基于B-Tree索引之上再建一个哈希索引, 这是一个完全的自动的内部的行为, 用户无法配置或控制.

创建自定义哈希索引
若存储引擎不支持哈希索引, 可以模拟创建, 比如存储大量的URL, 并需要根据URL进行搜索查找, 若使用B-TREE来存储URL, 存储的内容会非常大

  1. - 在url列上有B-TREE索引
  2. select id from url where url="www.baidu.com"
  3. - 优化方案, 删除原B-TREE索引, 新增一个被索引的url_crc列, 使用CRC32做哈希, 查询优化如下
  4. - 注意一旦出现哈希冲突会查到多条记录, 因此不能只查url_crc, 要带上原url的条件
  5. select id from url where url="www.baidu.com" and url_crc=CRC32("www.baidu.com")

哈希值得维护可以用触发器
image.png

空间数据索引(R-Tree)

MYISAM表支持空间索引, 可以用作地理数据存储

全文索引

索引的优点

  1. 索引大大减少了服务器需要扫描的数据量
  2. 索引可以帮助服务器避免排序和临时表
  3. 索引可以将随机I/O变成顺序I/O

高性能的索引策略

独立的列

查询的列不能是表达式的一部分, 也不能是函数的参数, 必须为一个独立的列, 否则MYSQL就不会使用索引

  1. - 以下俩例子不走索引
  2. select id from actor where id+1=5
  3. select ... where TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<10

前缀索引和索引选择性

对于很长的字符串, 通常可以索引开始的部分字符, 以节约索引空间, 提交索引效率, 但也会降低索引的选择性.
索引的选择性=不重复的索引值/数据表的记录总数; 选择性高的索引可以让mysql在查找时过滤掉更多的行, 唯一索引的选择性是1, 是最好的索引选择性, 性能也是最好的.

诀窍在于选择足够长的前缀以保证较高的选择性, 同时又不能太长, 两种方法这里略, 具体用到了再百度

多列索引

MSQL在5.0和更新版本中引入了”索引合并”的策略, 一定程度上可以使用表上的多个单列索引来定位指定的行. 查询能同时使用两个单列索引进行扫描, 并将结果进行合并, 这种算法有三个变种, OR条件的联合, AND条件的相交, 组合前两种情况的联合和相交.
image.png

如果在explain中看到有索引合并, 应该好好检查下查询和表的结构, 看是不是最优的
**

选择合适的索引列顺序

适用于B-TREE索引, 哈希或其他索引类型不按数据顺序存储
经验法则, 将选择性最高的列放到索引的最前列.

聚簇索引

聚簇索引不是一种单独的索引类型, 而是一种数据存储方式, InnoDB的聚簇索引实际上在同一结构中保存了B-tree索引和数据行. 聚簇索引的数据行实际上存放在索引的叶子页中, “聚簇”表示数据行和相邻的键值紧凑的存储在一起, 因为无法将数据行存放在两个不同的地方, 因此一个表只能有一个聚簇索引
image.png
InnoDB通过主键聚集数据, 也就是说图中被索引的列就是主键列, 如果没有定义主键, InnoDB会选择一个唯一的非空索引代替, 如果没有这样的索引, 会隐式定义一个主键来作为聚簇索引.

image.png
image.png

覆盖索引

如果一个索引包含所有需要查询的字段的值, 我们就称之为覆盖索引, 覆盖索引使得查询只需要扫描索引而无需回表.
覆盖索引要存储列的值, 因此MYSQL只能用B-Tree索引来做覆盖索引.
好处:

  • 索引条目远小于数据行大小, 只读取索引, 极大减少数据访问量, 也能提高缓存的负载
  • 因为索引按照列值顺序存储, 所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多
  • 一些存储引擎比如MYISAM只存储索引, 数据则依赖操作系统进行缓存, 因此访问数据要进行一次系统调用, 会导致严重的性能问题, 而覆盖索引能避免这些开销
  • InnoDB有聚簇索引, 若二级索引在叶子节点中保存了行的主键值, 则可以避免对主键索引的二次查询

当发起一个被索引覆盖的查询, 在explain的extral列会有Using Index信息;
在大多数存储引擎中, 覆盖索引只能覆盖那些只访问索引中部分列的查询, 不过可以通过利用子查询做延迟关联来优化sql以使用覆盖索引
image.png
image.png

此外, InnoDB二级索引的叶子节点都包含主键的值, 下面例子只有last_name列有索引, 此查询依然会走覆盖索引.
image.png

使用索引扫描来做排序

MYSQL排序的两种方式

  • 通过排序操作
  • 按索引顺序扫描, EXPLAIN出来的TYPE值为index
  1. 只有当索引的列顺序和order by子句的顺序完全一致, 并且所有列的排序方向(倒序或正序)都一样时, MYSQL才能使用索引来对结果进行排序.
  2. 如果查询要关联多张表, 则只有当order by的子句引用的字段全部为第一个表时, 才能使用索引做排序. order by子句和查找型查询的限制是一样的, 需要满足最左前缀的要求.image.png

image.png

压缩(前缀压缩)索引

MYISAM使用前缀压缩来减少索引的大小, 从而让更多的索引可以放入内存中, 这在某些情况下可以极大的提高性能.

冗余和重复索引

MYSQL允许在相同列上创建多个索引, 无论是有意的还是无意的, MYSQL需要单独维护重复的索引, 并且优化器在优化查询的时候也需要逐个的进行考虑, 这会影响性能.

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引, 应该避免这样创建重复索引, 发现以后也应该立即移除

未使用的索引

查出来删就完事了

索引和锁

索引可以让查询锁定更少的行, InnoDB只有在访问行的时候才会对其加锁; 即索引可以减少锁的数量.

总结

image.png