索引、sql优化

Posted on Oct 03, 2019 By xqw

索引

  • 覆盖索引 优先从辅助索引里找，数据量小(叶子只有id)，如 select id/count(*) from table where other_index = xxx;

  • 不使用索引的情况， 除了一般说的is null, !=, like %xxx，外
    使用辅助索引查时，若查询结果为id外的且结果占整张表过大（一般20%）的会全表扫描
    因为使用辅助索引查到id后，要将这些id去聚集索引里找，这时为随机读，磁盘随机读一般较慢(固态会高一些)，mysql优化器会选择全表扫(顺序读) 对索引进行函数操作、数据类型转换（如字符串转数字）、数据编码转换（utf8转utf8mb4）

  • Index Hint(索引提示) use/force/ignore index等

  • Multi-Range Read (MRR)
    为了减少随机访问 （Explain中Extra字段显示 Using MRR），适用于range、ref、eq_ref类型的查询
    1 使得数据访问变得较为顺序，查询辅助索引时，先依据辅助索引查找的的主键重新排序，然后用排序好的的主键进行书签查找

  • Index Collection Pushdown
    在存储引擎层就对数据进行过滤，减少sql层对数据的读取 （Explain中Extra字段显示 Using Index Condition）

索引类别及存储引擎区别

建立索引注意事项
主索引建议自增（顺序存储，否则需要移动后面的数据），辅助索引不建议同方向(高并发更新情况引起最后页的竞争)

mysql高性能建议

MySQL EXPLAIN详解

sql优化

什么情况下设置了索引但无法使用，索引无效

  1. 以”%”开头的LIKE语句，模糊匹配：红色标识位置的百分号会导致相关列的索引无法使用
  2. Or语句前后没有同时使用索引
  3. 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型，会使索引无效，产生全表扫描。）
  4. 在索引列上使用IS NULL 或IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可
  5. 在索引字段上使用not，<>，!=，eg<> 操作符（不等于）：不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 用其它相同功能的操作运算代替，如 a<>0 改为 a>0 or a<0
  6. 对索引字段进行计算操作
  7. 在索引字段上使用函数 sql优化

不走索引的情况

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
条件字段函数操作
隐式类型转换
隐式字符编码转换
MySQL实战45讲/19

基本操作

Mysql创建、删除用户

  • 查看表的存储引擎
    1 查看table_name show create table table_name; 2 查看MyDB的所有表引擎

      select table_catalog
        ,table_schema
        ,table_name
        ,engine
  from information_schema.tables
  where table_schema='MyDB';
  //and engine='MyISAM';

优化

innodb内存管理

在redo log工作时，更新的数据若在内存，则会直接更新内存，写入redo log、bin log提交事物；下次读取是直接读取内存数据。
这个内存就是innodb查询结果后在内存中的缓存，由innodb_buffer_pool_size控制。

如何淘汰旧数据：改进的最近最少使用算法 (Least Recently Used, LRU)
普通LRU算法：队列头是最新的数据，刚查询出来或者再次访问的数据，队尾为即将淘汰的数据
问题：一个大量读取的操作会将所有之前的淘汰，而新数据又不常用也马上淘汰，那么这段时间缓存命中率很低
改进后：分段5:3的young:old，刚查询出的数据在old的队列头(6/8位置)，
再次访问时
young的数据到队列头，old的数据若存在时间小于innodb_old_blocks_time(默认1秒)则不变，超过才会到队列头

join 是如何工作的

select * from t1 join t2 on (t1.a=t2.a);

  • 被驱动表有索引 Index Nested-Loop Join

    1.优化器选择其中一个为驱动表(t1)，一般原则：驱动表记录少，被驱动表有索引等

    2.读入t1一条数据R，到t2中查询，有索引走索引

    3.取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；

    4.重复执行步骤2、3，直到表t1的末尾循环结束。

    为什么选表记录少的做驱动表？
    若t1:N行,t2:M 行，
    那么扫描行数：N + N*logM，判断次数 N *logM

    type:ref

    MRR优化：上面第二步会变成：将数据读入join buffer，排序后顺序读，
    扫描t2一次，N次比较，扫描行数：N + logM，判断次数还是 N*logM

    Extra:Using MRR

  • 被驱动表无索引 Block Nested-Loop Join

    上面第二步会变成：将数据读入join buffer，排序后顺序读，
    扫描行数：N + M，判断次数 N*M

    若join buffer不够，则分多次读取，多次扫描t2，由join_buffer_size参数控制，
    扫描行数：N + n *M（n为 size(t1)/join_buffer_size），判断次数 N *M

    Extra:Using join_buffer(Block Nested Loop)

  • 写法区别

      select * from a straight_join b on(a.f1=b.f1); /*Q1*/
  select * from a join b on(a.f1=b.f1); /*Q2*/
  select * from a left join b on(a.f1=b.f1); /*Q3*/
  select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2);/*Q4*/
  select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q5*/

    Q1 Q2 straight_join为指定驱动表，这里a一定为驱动表，而join可能会被优化为b

    Q2 Q3 left join左连接，a的数据一定会出现，匹配不到的b表的列名为null，同样会被优化

    Q4 Q5 Q4匹配不到的b表的列名会出现null，Q5不会

    下面四句其实一样：

      select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); 
  select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);
  select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);
  select * from a ,b where (a.f1=b.f1) and (a.f2=b.f2);

  • 优化

  • 在被驱动表上建索引，驱动表数据量少
  • 若在被驱动表上建索引很浪费资源，使用临时表或者查询出来在客户端hash join 
    讲义例子：
t1数据量一般，t2很大，但是只要2000行（感觉优化器会字段选择t2当驱动表）
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
使用：
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

    参考MySQL实战45讲/34 35 44讲

order by是如何工作的

select city,name,age from t where city='杭州' order by name limit 1000;

  • 无覆盖索引，有city索引
  1. 初始化sort_buffer，确定放入name、city、age这三个字段；
  2. 从索引city找到第一个满足city='杭州'条件的主键id；
  3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
  4. 从索引city取下一个记录的主键id；
  5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；
  6. 对sort_buffer中的数据按照字段name做快速排序；
  7. 按照排序结果取前1000行返回给客户端。

相关参数 sort_buffer_size 排序内存最大值 max_length_for_sort_data 排序每行最大值
若行数过多，超过了sort_buffer_size，会利用磁盘临时文件辅助排序，先排好一部分放入磁盘归并排序
若列数过长，超过了max_length_for_sort_data，第一步只取出name id字段，排序好后第七步再检索一遍聚集索引

  • 有覆盖索引

有复合索引(city,name) 或者(city,name,age)

(city, name):

  1. 从索引(city,name)找到第一个满足city='杭州'条件的主键id；
  2. 到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
  3. 从索引(city,name)取下一个记录主键id；
  4. 重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州'条件时循环结束。

(city,name,age)：

  1. 从索引(city,name,age)找到第一个满足city='杭州'条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；
  2. 从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；

  3. 重复执行步骤2，直到查到第1000条记录，或者是不满足city='杭州'条件时循环结束。

    参考MySQL实战45讲/16讲

group by是如何工作的

create table t1(id int primary key, a int, b int, index(a));
select id%10 as m, count(*) as c from t1 group by m;

explain一下： Using index，表示这个语句使用了覆盖索引，选择了索引a，不需要回表；
Using temporary，表示使用了临时表；
Using filesort，表示需要排序。
这个语句的执行流程是这样的：

  1. 创建内存临时表，表里有两个字段m和c，主键是m；
  2. 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；
    如果临时表中没有主键为x的行，就插入一个记录(x,1);
    如果表中有主键为x的行，就将x这一行的c值加1；
  3. 遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

**优化 ***添加索引 alter table t1 add column z int generated always as(id % 100), add index(z);

explain后只有using index

***不适合添加索引的情况

若数据量很大，添加索引太耗资源，而且内存临时表肯定放不下，则可用SQL_BIG_RESULT，直接使用磁盘，避免了先使用内存表发现内存不够，再用磁盘

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
explain后只有Using index，Using filesort

*** 优化小结

  1. 如果对group by语句的结果没有排序要求，要在语句后面加 order by null；

  2. 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；

  3. 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；

  4. 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。

参考MySQL实战45讲/37讲

其他

前缀索引，前缀区分度高-email

使用前几位即可，减小存储    
给表email字段前六位添加索引`alter table SUser add index index2(email(6));`   
分析区分度 可以用5%标准
```
select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;
```

后缀的区分度高-身份证

1反转存储   
`select field_list from t where id_card = reverse('input_id_card_string');`   
2hash存储   
`alter table t add id_card_crc int unsigned, add index(id_card_crc);`   
`select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'`

首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。同样地，hash字段的方式也只能支持等值查询。

它们的区别，主要体现在以下三个方面：

从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。

在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的话，reverse函数额外消耗的CPU资源会更小些。

从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

[参考MySQL实战45讲/11讲]()

查询结果只有一条仍然慢的几种情况

等MDL锁
等flush（flush tables操作，关闭对应的表，确保数据刷新至磁盘）
等行锁
回滚至一致性读的概念，一致性读时若该数据被其他线程操作了很多次，需要回滚至当前事务开始时期

自增主键不连续

唯一键冲突是导致自增主键id不连续 事务回滚