索引

2019-02-14 本文已影响0人阿长_一个程序员

索引类型

常用的索引类型有2种，B-Tree和Hash

B-Tree

InnoDB存储引擎就是用B+Tree实现其索引结构,B+数原理网上自己搜

B-Tree索引使用场景

全值匹配的查询SQL，如 where act_id= '1111_act'
联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE
只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
匹配模糊查询的前匹配，如where act_name like '11_act%'
匹配范围值的SQL查询，如where act_date > '9865123547215'（not in和<>无法使用索引）
覆盖索引的SQL查询，就是说select出来的字段都建立了索引

B-Tree索引的限制
现在有一个商品表，有cat_id类别字段，price价格字段。
假设我们给cat_id和price分别加上各自的索引，

那么当我们使用sql：

select * from goods where cat_id = 3 and price > 100;

这句sql只能用上cat_id或price索引，因为它们两是独立的索引，同时只能用上1个。

在使用联合索引时(以 index(a,b,c) 为例，(注意和顺序有关))，满足最左匹配原则

如果不是按照索引的最左列开始查找，则无法使用索引

where a=2 可以用到索引
where a=1 and b=2 可以用到索引
where a=1 and b=2 and c=3 可以用到索引
where a in(1,2,3) and b in(4,5,6) and c=3 虽然type是range，但可以用到索引
where b=1 / c=1 不能用到索引

不能跳过索引中的列

where a=1 and c=1
只有a使用了索引，c没有使用索引

a可以发挥索引，c不能使用到索引

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

where a=1 and b>10 and c=1
a可以发挥索引，b也可以发挥索引，c不能发挥索引
where a=1 and b like 'xxx%' and c=1
a可以发挥索引，b可以发挥索引，c不能发挥索引

Hash

挖坑待填

索引优点

减少需要扫描的row
避免排序和临时表
将随机I/O变为顺序I/O

索引策略

前缀索引

需要索引很长的字符串时，可以通过索引开始的部分字符来提高性能。但这样也会降低索引的选择性。
有如下数据

使用前缀索引需要指定前缀索引的长度，如下图索引长度为7

索引长度为7
计算合适的前缀长度
前缀的选择性应接近完整列的选择性，下图显示如何计算完整列的选择性以及前缀的选择性

计算完整列的选择性

计算前缀的选择性
找到合适的前缀长度后，创建前缀索引

mysql > ALTER TABLE sakila.city_demo ADD KEY(city(7))

前缀索引能使索引能小，更快，但MYSQL无法使用前缀索引order by、group by以及覆盖扫描

索引合并

单列索引在大部分情况下不能提高MYSQL的查询性能。MYSQL5.0后使用了"索引合并"(index merge)策略，查询能够同时使用多个单列索引进行扫描，并将结果合并。有三种情况会遇到index merge：or、and、or和and都有。

type:index_merge

index_merge的出现说明你的索引建得很糟糕！

联合索引的顺序

一般来说，将选择性高的列放到索引的最前列
考虑以下查询

计算列的选择性

customer_id的选择性更高，所以把它放到索引列的第一列

覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。非常有用，可以极大提高查询性能。
只扫描索引而无需回表的优点：
1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题。
4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，所以**MYSQL只能使用BTree索引做覆盖索引

使用索引来排序

使用索引来排序的目的是避免Extra出现Using filesort
使用索引来排序必须满足

索引的顺序和order by子句的顺序完全一致
order by子句中列的排序方向一致(都是正序或都是倒序)
如果查询需要关联多张表，order by子句的列必须为同一张表的列

有如下联合索引

ALTER TABLE table_name ADD INDEX index_name ( a, b, c )

以下查询不会出现Using filesort

where a = 1 order by b

where a > 1 order by b ,c
这个查询也没问题，因为order by使用的两列是索引的最左前缀

以下查询出现Using filesort

where a = 1 order by b desc,c asc
//排序方向不一致
where a = 1 order by b,f
//f不是索引列
where a = 1 order by c
// where 和order by中的列无法组合成索引的最左前缀
where a > 1 order by b,c
索引列的第一列是范围条件，MYSQL无法使用索引的其余列
where a = 1 and b in(1,2) order by c
这个查询在b列上有多个等于条件，这也是一种范围查询

压缩索引(了解)

MyISAM使用压缩索引减少索引大小。默认只压缩字符串，通过参数设置也可以压缩整数。
具体压缩方法是，保存索引块中第一个值，将其他值和第一个值比较得到相同前缀的字节数和剩余的不同后綴部分。比如第一个值是“perform”，第二个值是“performance”，第三个值是“performancer”。那么第二个值的前缀压缩后存储的是类似"7,ance"，第三个值类似"7,ancer"这样的形式
压缩块使用更少的空间，代价是order by desc等操作会更慢

冗余和重复索引

重复索引

重复索引指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免创建这样的索引
例如下面代

CREATE TABLE test (
    ID INT NOT NULL PRIMARY KEY,
    A  INT NOT NULL,
    B  INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID)
) ENGINE=InnoDB

MYSQL的唯一限制和主键限制都是通过索引实现的，因此上面在ID列上创建了三个重复的索引。

冗余索引

冗余索引不同于重复索引，如果先创建了index(A,B)，再创建index(A)就是冗余索引，因为index(A,B)可以当作index(A)来使用。但是如果再创建index(B)这就不是冗余索引，因为B不是index(A,B)的最左前缀
冗余索引通常发生在覆盖索引时。比如将一个索引扩展为(A,主键)，对于InnoDB来说主键包含在二级索引中，所以是冗余索引。
大多数情况下都不需要冗余索引，如果出现可以删除，但删除索引时要小心。例如index(A) 在 where A = 5 order by ID这样的查询会很有用，因为index(A)相当于index(A,ID)。如果将索引扩展成(A,B),则实际会变成(A,B,ID)。

未使用的索引

直接删除

索引和锁

索引可以让查询锁定更少的行，因为索引能减少InnoDB访问的行数，InnoDB在访问行时会加锁。

索引包含的列过多会导致数据插入变慢

image.png

索引