mysql索引总结

mysql索引总结

几个你需要思考的问题

如果使用了ORM是否还需要关心索引?

ORM工具是能够生成符合逻辑的、合法的查询的。但是ORM工具能够生成的也一般只是简单的查询而已(eg:根据表的主键查询)。而如果是关于利用到索引的查询,如果设计到很多的条件与规则以及顺序,ORM工具是很难生成合理利用索引的查询的。所以即使是用了对象关系映射工具,还是需要理解索引。

索引总是最好的解决方案吗?

索引并不一定是最好的解决方案,只有当索引需要的额外工作与压力小于其好处时,才应该使用索引。比如:很小的表,可能索引带来的效果根本就不明显或者还没有全表扫描来的更高效。或者很大的表。索引所占的空间以及动态维护的成本都成倍增长,而且定位单条记录的意义根本就不大。这个时候更应该考虑别的技术(比如分区)。

使用索引应该注意什么?

理解索引是如何工作的是很重要的。这样就可以根据你的理解来创建出合适的索引。而不是根据一些经验法则”where、group by、order by后的所有列创建索引”。一定要积极的创建一个合适的索引,如果有些地方有取舍,就要先看能不能有替代策略(比如,允余字段,提前计算,转换sql等)

何为索引?

索引的定义

索引在mysql中也叫做键(”key”),是数据库的存储引擎用于快速找到记录的一种数据结构

索引的类型

注:不同存储引擎的索引工作方式不一样,也不是所有的存储引擎都支撑所有类型的索引。即使不同的存储引擎实现了同一种类型的索引他们的底层实现也可能不一样

B-Tree索引

定义:B-Tree意味着所有的值都是按顺序存储的,并且每个叶子页到根的距离相同。存储引擎不需要全表扫描就能获取需要的数据,而是从索引的根节点开始进行搜索,因此能够加快访问数据的速度。假设有个人员表,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
create table tb_people(
family_name varchar(12) not null comment '姓',
name varchar(12) not null '名',
birthdate date not null '生日',
key(family_name,name,birthdate)
);
那么在如下的查询中 索引是有效的:
1.全部值都匹配
select * from tb_people where family_name = 'li' and name = 'san' and birthdate = '1994-11-23';
说明:就是跟所有列都匹配,用于查找姓名为li san、出生于1994-11-23的人

2.匹配最左前缀
select * from tb_people where family_name = 'li';
说明:就是只使用到了索引的第一列,查找姓为li的人

3.匹配列前缀
select * from tb_people where family_name like 'l%';
说明:用于查找姓以‘l’开头的人

4.匹配范围值
select * from tb_people where family_name in ('li','wang') ;
说明:用于查找姓为li,wang的人

5.精确匹配某一列并范围匹配另一列
select * from tb_people where family_name = 'li' and name like 'san%' ;
说明:查询所有姓为‘li’并且名为‘三开头的’的人

6.只访问索引的查询
select family_name,name,birthdate from tb_people;
说明:即所查询的列都是索引中的列

那么在如下的查询中 索引是无效的:
1.如果不是按照索引的最左列开始查找,则无法使用索引。eg:
select * from tb_people where name = 'san';

select * from tb_people where name = 'san' and birthdate = '1994-11-23';

select * from tb_people where family_name like '%i';

2.不能跳过索引中的列
select * from tb_people where family_name = 'li' and birthdate = '1994-11-23';
说明:如果在where条件中没有指定name而跳过它,则实际上只用到了索引的第一列

3.如果查询中有某个列的范围查询,则该列后面的所有列都将无法使用索引,eg:
select * from tb_people where family_name = 'li' and name like 'san%' and birthdate = '1994-11-23';

哈希索引

定义:哈希索引基于哈希表实现,只有精确匹配索引的所有列索引才有效,对于每一行数据,存储引擎都会对所有的列计算一个哈希码。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。
哈希索引的工作原理:
假设上表的fimaly_name建立了哈希索引,有如下查询:

1
2
3
4
5
6
7
8
9
select * from tb_people where fimaly_name = 'li';
那么工作原理就是:mysql先计算‘li’的哈希值,并使用该哈希值去索引结构中寻找对应的记录指针,假设‘li’根据函数生成的哈希值为123,那么mysql就会去索引中找123,然后找到123的指针指向第一行,然后就会判断第一行的值是否为‘li’。</br>
因为哈希码一般都是一个较小的值,而索引自身只需要存储对应的哈希码,而不是存储字段值。所以哈希索引的查找速度是非常快的。但是哈希索引的限制也有很多
* 哈希索引因为存储的是哈希码,本身就无顺序可言,所以哈希所以无法用于排序
* 因为哈希码是根据字段的全部值来生成的,所以哈希索引也不支持列的部分匹配来查找 eg:
select * from tb_people where fimaly_name like 'l%';
select * from tb_people where fimaly_name = 'li';
说明:假设第一条是在列A上建立的哈希索引,第二条是在列(A,B)上建立的索引;
* 哈希索引只支持等值查询,包括=,in(),<=>。而不支持范围查询,比如 where birthdate > '1994-11-23'

其他索引

空间索引(R-Tree)、全文索引(一种特殊类型的索引,它不是通过比较索引中的值来查找)、其他索引类别等。

优缺点

优点:
1.单行访问是很慢的。如果服务器从存储中读取一个数据库只是为了其中一行,那么就浪费了很多工作。使用索引可以创建位置以提升效率
2.按顺序访问范围数据是很快的。顺序I/O比随机I/O快很多,如果服务器需要顺序读取数据,那么就不需要额外的排序操作(B-Tree)。
3.如果一个索引包含了查询所需要的所有列,那么存储引擎就不会再回数据表里查找,极大提升查询效率
缺点:
1.索引总是意味着额外的维护工作与空间
2.哈希索引可能会出现哈希冲突,如果索引建立的不合理的话,代价会很大

高性能索引你必须get的几个点

索引的选择性

索引的选择性是指不重复的索引值和数据表的记录总数(#T)的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1。
对于BOLB、TEXT或者很长的VARCHAR的列,mysql不允许索引这些列的完整长度,所以必须使用前缀索引。前缀索引必须在选择性跟空间成本中做平衡。具体请查看《高性能mysql》

独立的列

索引列不能使表达式的一部分,也不能使函数的参数。eg:

1
2
1.select id from tb_test where id+1=5;
2. select * from tb_test where to_days(current_date) -to_days(birthdate) <= 10;

多列索引以及索引列的顺序

很多人都受所谓的经验法则“把where提交的列都建上索引”误导,导致对多列索引的理解不够,其实这样在很大程度上不会提高查询能,mysql5.0开始就加了“索引合并”的策略。这种策略是一种优化,但同时也说明了所建的索引基本上没有效果,有时候还有不可控的结果
索引列的顺序一般是将选择性高的放在前面

使用索引扫描来做排序

MySQL可以使用同一个索引既满足排序,又用于查找行。如果可能,设计索引时尽量满足这两种任务。
只有当索引的列顺序和order by子句的顺序完全一致,并且所有列的排序方向都一样时,MySQL才能使用索引来对结果做排序。如果是关联查询,则只有当order by自居引用的字段全部为第一个表时,才能使用索引做排序。

重复索引和未使用的索引

这些索引会导致成本变高,又没起到作用,应该发现并删除。可以通过查询information_schema表来查找。

索引的一些小技巧

支持多种过滤条件

1.在有更多不同值的列上创建索引的选择性会更好。
2.有些列比如sex,选择性不高,但是频率特别高,基本上所有查询都要带上它,那创建索引的时候就应该把他放在组合索引的前列。正因为它选择性不高,所以即使有些查询没带上这个条件,我们也依然可以让该查询能用到索引,比如sex in(‘m’,’f’)
3.对于多半会有范围查询的列,在组合索引时就应该把该列放在最后面,比如时间,年龄,工资这种列。

避免多个范围条件

1.什么是范围条件?
从explain的输出我们有时候很难区分出是查询范围值,还是列表值。比如:

1
2
1.EXPLAIN SELECT actor_id from tb_actor where actor_id > 45;
2.EXPLAIN SELECT actor_id from tb_actor where actor_id in (1,45,90);

这两条sql语句explain都会输出type:”range”;但事实上第二个其实就是三个等值条件查询。
2.避免的话,如果查询不需要精确数据,我们是有替代策略的。比如新加一个字段值,然后通过定时任务来维护这个字段值。索引中加上这个字段值来替代原本的列。比如超过20天的就不是新会员了,我们需要统计新会员跟老会员,本来是根据成为时间来判断,我们新加一个会员状态的字段值,通过定时任务去维护。如果需要精确的数据,那就只能根据时间放到where子句中,但不加入到索引中。

优化排序

场景:如果查询条件中只有个选择性非常低的列。但是数据量又非常大,而且需要翻页。这样的查询会很慢,即使有索引到最后因为limit偏移量的原因也会很慢。因为需要大量的时间去扫描丢弃的数据。
方案:比较好的一个方案就是使用延迟关联。通过使用覆盖索引查询返回需要的主键。再根据主键关联原来表获得需要的行。
假设有个索引为(sex,salary)。需要按照薪资排序,翻页。sex作为查询条件。eg:

1
select * from tb_person inner join ( select id from tb_person where x.sex = 'm' order by salary limit 100000,15) as x using(id);

参考文献

《高性能MySQL》

结语

第一次用GitHub跟hexo搭建独立博客。排版什么的也不太会用。能看到这里的你可能也很无聊吧,哈哈

文章目录
  1. 1. 几个你需要思考的问题
    1. 1.1. 如果使用了ORM是否还需要关心索引?
    2. 1.2. 索引总是最好的解决方案吗?
    3. 1.3. 使用索引应该注意什么?
  2. 2. 何为索引?
    1. 2.1. 索引的定义
    2. 2.2. 索引的类型
      1. 2.2.1. B-Tree索引
      2. 2.2.2. 哈希索引
      3. 2.2.3. 其他索引
    3. 2.3. 优缺点
  3. 3. 高性能索引你必须get的几个点
    1. 3.1. 索引的选择性
    2. 3.2. 独立的列
    3. 3.3. 多列索引以及索引列的顺序
    4. 3.4. 使用索引扫描来做排序
    5. 3.5. 重复索引和未使用的索引
    6. 3.6. 索引的一些小技巧
      1. 3.6.1. 支持多种过滤条件
      2. 3.6.2. 避免多个范围条件
      3. 3.6.3. 优化排序
  4. 4. 参考文献
  5. 5. 结语
,