面试官:聊一聊索引吧
记录一下Mysql索引相关材料,随时更新,有不正确的欢迎评论区指出。
一、索引的三星规则
- 一星:索引将相关记录放到一起;
- 二星:索引中的数据顺序和查找中的排序一致;
- 三星:索引中的列包含了查询中所需要的全部列。
星级越高,代表索引应用效率越高。
二、哈希索引
哈希索引的特点:
1.只支持等值查找
2.查询速度更快
3.索引列是根据哈希值进行排序的,而不是索引列的值,所以无法应用排序
4.哈希索引只存储哈希值和行指针,然后根据行指针来获取对应行数据,不过内存中这一过程很快,所以对性能的影响很小
5.可能会存在哈希冲突的情况,即两个不同的值生成的哈希值一样,出现这种情况后存储引擎会为匹配到的哈希值创建链表,然后遍历链表中的所有指针,逐行比较行数据,以此来获取符合条件的行。
三、BTREE索引
索引生效条件:针对联合索引,查询时需要满足最左匹配原则
建立索引: idx_first_sec(‘first’, ‘second’, ‘third’)
1.查询条件必须包含first字段才会生效
示例:WHERE first='xxx' AND second = 'xxx'
;
2.如果从second开始查,则不会应用索引
示例:WHERE second='xxx'
3.如果中间跳了一列,则只会应用到部分索引
示例:WHERE first='xxx' and third='xxx';
#只会应用first索引
4.如果某一列使用了范围查找,则右边所有列就不会应用索引了。
示例:WHERE first='xxx' and second like 'xxx%' and third='xxx';
#最终只会应用first和second索引
索引失效的情况
1.索引应该是一个独立的列,不能是表达式的一部分或者函数的参数。
select * from table where active_id + 1 = 5;
select * from table where to_days(current_day) - to_days(date_col) <= 10;
2.范围查找
select * from table where age != 18;
select * from table where name like '%张三%';
多列索引(联合索引)
当查询语句的where条件中,出现了多个索引的相交操作(通常有多个AND条件),那么就应该考虑使用多列索引了。
例如:select * from table where col1='xx' and col2='xx';
col1 和 col2 是两个独立的单列索引(5.0版本之前,这种查询会导致全表扫描,解决方式是用 union all 两个列的单独查询来代替 AND)
此时,可以为 col1 和 col2 建立多列索引。alter table table add index idx_c1_c2(col1,col2);
InnoDB中,为什么要按主键顺序插入行?(主键自增)
因为按照顺序插入,只需要将每一条记录存储在上一条记录的后面即可,无论是执行速度还是存储空间都比随机主键性能要好,主要在3个方面:
1.写入的目标页是随机的,有可能已经已经刷到磁盘并从缓存中清除,或还没有加载到缓存中。在插入之前不得不从磁盘中读取目标页到缓存中,会带来大量的随机IO;
2.因为写入是乱序的,InnoDB不得不做大量的页分裂,以便为新的行分配空间,页分裂会导致大量数据移动。
3.因为频繁的页分裂,页会变得稀疏不规则,所以最终数据会有碎片。
什么是覆盖索引?什么情况下无法使用?
根据索引查询的叶子节点,不需要进行二次查询即可满足本次查询的过程,称为覆盖索引查询。
正常的一次查询:根据 where 条件中的索引列,找到对应主键ID,然后再根据主键ID查找到对应行数据。(二次查询/回表)
覆盖索引查询:根据 where 条件中的索引列,可以直接查出本次结果,不需要再根据主键ID查找对应行数据。
注意事项:
1.禁止使用
select *
,因为没有索引会覆盖全字段,使用 * 将势必会造成回表查询。
2.where 条件中禁止使用会导致索引失效的情况(例如:like ‘%xx%’, !=等操作),否则即便是查询指定列,也会导致全表扫描。
索引排序
当一个查询及排序语句,完全符合表索引顺序时,也是可以利用索引进行排序。
比如:表 table 的索引:idx_date_user(date,user_id);
下面的查询则可以利用到索引排序:
select user_id,user_name from table where date='2021-06-12' order by user_id;
select user_id,user_name from table order by date,user_id;
所以,不管是where还是order by 语句,或两者同时使用,对于单表来说只要查询顺序符合索引顺序,则可以利用索引。
有一种情况,如果order by 语句中两个排序字段,排序方向不一致,就无法使用索引排序。
比如:select user_id from table order by date desc, user_id asc;
四、优化技巧
1. 使用范围查找的索引列,最好放在联合索引的最后一个。
比如:age字段,往往是根据范围来进行查询的(18-25)这样,所以放在最后一列比较合理,否则会导致其他索引无法使用。
其中的一个解决方案是可以使用 in(18,19,20,21,22,23,24,25) 这样来代替,但并不是所有条件都适用。而且每增加一个 in 条件,优化器需要做的组合都将以指数级增长,最终也会导致性能问题。
2. 大分页查询优化
例如limit 10000,10
示例:select * from table where sex=1 order by age limit 10000,10;
优化方法1select * from table inner join (select id from table where sex=1 order by age limit 10000,10) as tmp using(id);
说明:此方法利用关联查询中的覆盖索引查询,返回数据的主键ID,然后根据主键ID获取对应行数据。比直接limit查询后再回表查询速度要快。
优化方法2select * from table where id>10000 and sex=1 order by age limit 10;
说明:此方法需要配合业务代码实现,在翻页过程中记录当前页面的最大ID,然后在查询时将ID作为查询条件传入,可以准确过滤不需要匹配的行数据,不过弊端就是无法实现页码跳转了。
3. 将一个复杂查询拆分成多个简单查询。
例如一个JOIN查询可以拆分成多个单表查询:
select * from table1 join table2 on table1.id=table2.tid join table3 on table2.id = table3.aid
where table1.name = 'xxx' and table2.age=32 and table3.nid=56;
#拆分后
select * from table1 where name='xxx';
select * from table2 where age=32;
select * from table3 where nid=56;
这么做的好处:
- 让缓存效率更高。在客户端应用中可以对不同表做单独缓存,而不必每次都缓存所有结果。对于mysql缓存来说,关联查询中如果其中一个表发生了变化,那么就会使缓存失效,但如果是拆分成多个简单查询,那么即使某个表缓存失效,但其他的查询仍然是有缓存的。
- 将查询拆分后,可以减少锁的竞争。
- 在应用层做关联,可以更方便的对数据进行扩展和拆分。
- 单表查询可以更好的应用索引查询。
- 减少冗余记录的查询。在应用层做关联,对于某条记录可能只需要访问一次,但在数据库做关联查询,则可能会重复查询一部分数据。
4. 为超长字段模拟哈希索引列(实际上还是b-tree索引),以加快查询速度。
如果表中存在url字段索引,可以为其设置一个哈希索引列,目的在于更快的匹配到对应的行,即使有部分冲突,也会比原本只查询url快很多。
比如:创建字段:url_crc字段,使用crc32为url生成哈希值。表的索引有两个:url_crc、url
在查询时就可以这样:SELECT id FROM table WHERE url_crc=CRC32('http://www.baidu.com') AND url = 'http://www.baidu.com';
这样查询会比以前只查url快很多。
注意:因为哈希可能会冲突,即同样的哈希值对应不同的值,所以在查询时必须带上查询的真实列。
5. 创建前缀索引
如果表结构不方便进行修改,那么可以在超长字段上建立前缀索引,以加快查询速度。
例如:alter table add key idx_city(city(7)); # 为city字段的前7个字符创建索引
缺点就是Mysql无法对前缀索引做ORDER BY 和GROUP BY。
如何判断出前缀的长度?
第一种方式:人工匹配
1.数据量不大的情况下,可以人工匹配,即首先统计该字段的次数
select count(*) as cnt, city from table group by city order by cnt desc limit 10;
2.依次递增前缀数量,最终达到和完整列的选择性相接近的程度即可。
select count(*) as cnt, LEFT(city,3) as pref from table group by pref order by cnt desc limit 10;
select count(*) as cnt, LEFT(city,7) as pref from table group by pref order by cnt desc limit 10;
第二种方式:计算完整列的选择性
1.首先计算完整列值的选择性
select count(distinct(city))/count(*) from table; # 最终前缀索引的选择性值应与这里得到的越接近越好
2.计算不同前缀的选择性,找出与完整列的选择性最接近的值。(示例中前缀长度为7的最接近)
select count(distinct left(city, 3)) / count(*) as sel3,
count(distinct left(city, 4)) / count(*) as sel4,
count(distinct left(city, 5)) / count(*) as sel5,
count(distinct left(city, 6)) / count(*) as sel6,
count(distinct left(city, 7)) / count(*) as sel7
from table;
索引的选择性:指不重复的索引值与数据表的记录总数的比值。索引的选择性越高,查询效率就越高。因为高选择性的索引,可以在查询时过滤更多的行。
本作品采用《CC 协议》,转载必须注明作者和本文链接
推荐文章: