面试官:聊一聊索引吧

记录一下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;

优化方法1
select * from table inner join (select id from table where sex=1 order by age limit 10000,10) as tmp using(id);

说明:此方法利用关联查询中的覆盖索引查询,返回数据的主键ID,然后根据主键ID获取对应行数据。比直接limit查询后再回表查询速度要快。

优化方法2
select * 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;

这么做的好处:

  1. 让缓存效率更高。在客户端应用中可以对不同表做单独缓存,而不必每次都缓存所有结果。对于mysql缓存来说,关联查询中如果其中一个表发生了变化,那么就会使缓存失效,但如果是拆分成多个简单查询,那么即使某个表缓存失效,但其他的查询仍然是有缓存的。
  2. 将查询拆分后,可以减少锁的竞争。
  3. 在应用层做关联,可以更方便的对数据进行扩展和拆分。
  4. 单表查询可以更好的应用索引查询。
  5. 减少冗余记录的查询。在应用层做关联,对于某条记录可能只需要访问一次,但在数据库做关联查询,则可能会重复查询一部分数据。


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;
image

2.依次递增前缀数量,最终达到和完整列的选择性相接近的程度即可。

select count(*) as cnt, LEFT(city,3) as pref  from table group by pref order by cnt desc limit 10;    

image

select count(*) as cnt, LEFT(city,7) as  pref from table group by pref order by cnt desc limit 10;

image

第二种方式:计算完整列的选择性
1.首先计算完整列值的选择性

select count(distinct(city))/count(*) from table; # 最终前缀索引的选择性值应与这里得到的越接近越好
image
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;

image

索引的选择性:指不重复的索引值与数据表的记录总数的比值。索引的选择性越高,查询效率就越高。因为高选择性的索引,可以在查询时过滤更多的行。

本作品采用《CC 协议》,转载必须注明作者和本文链接
本帖由系统于 1天前 自动加精
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
讨论数量: 2

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!