MySQL学习整理-码农进阶之路(三)

四：优化

4.1 索引使用常见注意点

1.独立的列，即索引列不能是表达式的一部分，也不能是函数的参数。如index(a)；where a + 1 > xx；to_days(col)。

2.尽量全值匹配，联合索引最左前缀原则，使用覆盖索引查询，范围查询条件放最后。

3.不等于类的语法慎用，会导致扫描区间近似全表扫描，如not in(...)。

4.like查询前缀：'abc%'，如果是获取后缀比如xxx.qq.com，可以新增一列存储反转字符串[moc.qq.xxx]再使用前缀'moc.qq%'。

5.索引列字段类型要一致；字符类型查询加引号'13333333333'；联表查询的关联字段，类型和字符集类型要统一。

这个是真实业务场景中碰到的索引失效问题，我有一个朋友经常查手机号不加引号^_^。联表查询关联字段在两张表里类型分别是bigint和varchar，还有一种常见的情况两个字段都是varchar，但字符集是unicode_ci和general_ci，字符集不一样同样导致索引失效。解决方式如下：

        select x from a join b on a.sid = concat(b.sid,'');

        select x from a join b on a.sid = b.sid COLLATE utf8mb4_general_ci;

4.2 ICP、MRR和回表查询

每次执行回表查询都相当于对聚簇索引的一次随机IO，ICP和MRR都是mysql6以后出现的用来优化回表查询的技术。

MRR(Disk-Sweep Multi-Range Read)多范围读取

       二级索引回表时，先读取一部分二级索引记录，将他们的主键排好序后再执行回表操作，减少了回表的次数，降低了回表的随机IO消耗。

ICP(index condition pushdown) 索引条件下推

        顾名思义就是索引的判断条件可以放到存储引擎层。在没有使用ICP技术的回表查询时，存储引擎获取索引扫描区间的全部记录并回表，server层对回表的结果使用where条件判断，不符合就丢弃。使用ICP后，可以在索引遍历过程中，由存储引擎过滤掉不满足索引条件的记录，减少了返回给server层回表的记录条数。[extra: using index condition]

4.3 EXPLAIN

用法：explain [format=json] select xxx from tb...

一个很重要的查询分析器，主要关注下面4个列的部分值即可，其他的一看就知道是啥。

type: 查询方式 
        const：[id=1, 唯一确定值常数级时间，二级索引列不能为null(or key is null InnoDB把null当做不确定值)] 
        eq_ref：[主键或者唯一二级索引列等值访问 t1 inner join t2 on t1.id = t2.id]
        ref：[普通二级索引常量等值匹配，常用作索引访问]
        index_merge：[索引合并，两个索引列的常量等值匹配，col1=1 or col2 = '1']
        range：[范围查询between > %lt; in]
        index：[可以索引覆盖但需要查询全部的列，索引的全表扫描，index(a,b) select a from tb where b=xx。可以使用索引覆盖但无法使用过滤条件只能索引树全扫描]
        All：全表扫描
rows：预估扫描记录数，mysql使用的是贪婪算法取的近似值，并没有真正统计
filtered：满足搜索条件记录的百分比，如果为10，则查询结果占扫描行数的10%
extra：剩余其他有效信息 
    using index：[索引覆盖] 
    using index condition：[用到了索引但无法覆盖，使用了ICP]
    using where：[每找到一条记录都要通过where条件判定，不符合丢弃，符合返回给客户端。和全表扫描回表没关系，仅仅代表server层使用where对结果过滤]
    using join buffer：[使用join buffer内存块加速被驱动表无索引查询速度]
    using filesort：[不能使用索引排序，将记录放到内存或者磁盘进行单次传输排序]
    using temporary：[使用临时表来去重排序，distinct, group by, order by]

4.4 SHOW PROFILES

一个可以查询sql执行时间的工具

        show processlist;        //查看mysql的线程状态

        select @@have_profiling;        //查看是否支持profile

        select @@profiling;                //查看状态

        set profiling = 1;        //开启

        show profiles;        //最近执行sql列表，根据第一列的query_id可以进一步查询

        show profile [all/source] for query 2;        //此处的2就是上面的query_id

4.5 查询成本

mysql会自动采样统计各个表的信息，查询成本是mysql根据采样信息计算的用来选择查询策略的依据。通过查询成本的计算我们能更了解mysql关心的系统开销在哪些方面，我们以后也能依据真实的表数据通过查询成本的计算，写出更加高性能的sql语句。

4.5.1 查询成本计算

成本系数：IO[1]        CPU[0.2]

全表扫描成本(顺序IO)：

        show table status like 'users'; //查看表users统计信息，此处数据使用我测试库某表的数据

        Rows:64479325        //数据行数。

        Data_length:8598323200 //数据大小，单位字节，用于成本计算需要转换成页[8598323200/16/1024=524800]

IO成本[524800*1+1.1(常数)=524801.1]+CPU成本[64479325*0.2+1=12895866]=1342066701

索引查询(需回表)成本(随机IO)：

        explain select * from users where a > 10 and a < 20;//使用explain获取索引的扫描行数。

        rows:30542

IO成本[1*1]+CPU成本[30542*0.2+0.01=6108.41]+回表IO成本[30542*1]+回表CPU成本[30542*0.2=6108.4] = 42759.81

联表查询成本计算更复杂，大体的的公式是：驱动表成本+驱动表扇出[查询结果数]*被驱动表成本

由此可以看出联表查询关联字段一定要用索引，否则驱动表扇出大的情况下被驱动表的总查询成本会很高。

4.5.2 Optimizer_trace

查询优化追踪器，mysql5.6以后的版本提供，可以跟踪查询语句的优化执行过程，我们可以用来查看mysql对我们sql语句的优化改写，索引选择及对应的查询成本，我们可以用来校验我们计算的查询成本。

使用方式：

查看:SHOW VARIABLES LIKE 'optimizer_trace';

开启:set optimizer_trace="enabled=on";        //off为关闭，消耗系统性能，平时应该关闭

执行SQL语句：

select * from users where a > 10 and a < 20;//假设这就是我们要跟踪和验证查询成本的sql语句

select * from information_schema.optimizer_trace;//两条语句一起执行

第二条语句的TRACE列，结果是一个json语句，大体有如下几个部分：

join_preparation        //重写mysql

join_optimization        //详细跟踪过程

        condition_processing        //查询条件优化改写

        rows_estimation                //索引列表 使用及分析

                analizing_range_alternatives        //索引查询成本分析

                chosen_range_access_summary //索引选择结果汇总

        considered_execution_plans        //联表查询分析，计算每种组合的查询成本。如果没有结果依赖的话，多表关联的可能性是n！，因此此处可能极其复杂，mysql内置了参数并不会完全分析每一种排列组合情况

        attaching_conditions_to_tables        //其他条件，比如ICP

join_excution                //结束

下面是截取的关于最后汇总的结果部分：

  {
    "considered_execution_plans": [
      {
        "plan_prefix": [
        ],
        "table": "`users`",
        "best_access_path": {
          "considered_access_paths": [
            {
              "access_type": "range",
              "rows": 30542,
              "cost": 42760,    //查询成本
              "chosen": true   //代表选择了这种方案，如果是false会有cause表示不选的原因。
            }
          ]
        },
        "cost_for_plan": 42760,
        "rows_for_plan": 30542,
        "chosen": true
      }
    ]
  },

 我们在上一小节中对这条查询语句计算的成本是42759.81！

4.6 实战

4.6.1 大数据表修改表结构、添加索引，alter table xxx

alter table会锁表，如果数据量很大的话会导致服务不可用，加个索引卡半个小时都是好的，那我要不要跑路！

1.冷备，在备机上完成表结构修改操作，再由备机提供服务完成主机的表结构修改

2.影子拷贝，表tb，拷贝为tb1，修改好表结构后，删除tb，将tb1重命名为tb。

3..frm，这个文件存的表结构，导出表结构，在其他地方创建并修改表结构，使用新的.frm覆盖原.frm文件

4.6.2 长字段的条件查询，如BLOB text

1.使用SUBSTRING(column, length)[mysql的LEFT()函数] 截取部分字段取得近似结果，参考前缀索引。
2.模拟hash索引，即新增一个列存原列hash。查询时根据hash值索引查询，但该方式不支持范围查找

4.6.3 需要mysql支持很高的并发

其实是一个热点分离的问题，参考JAVA并发包的LongAddr实现思想

因为单机器性能上限的问题，一般需要配合分库分表解决这类问题。可以对对热点数据表做横向分表，比如按日期分表可减少每天累计数据量，按地区分表可分散时间点数据。

除开分表，主键可以人为设计：数据中心id+时间戳+自增序列，这样可减少因锁竞争的等待时间，加快写入速度。[通过数据库或者分区分表的逻辑字段+自增列，既保证了主键的顺序性，又实现了锁分段]

4.6.4 分组查询和排序

group by/order by表达式涉及的字段来自一张表性能更好，group by会默认排序[using filesort]，工作中group by常被用作子查询语句一般不需要排序，可以使用 group by col order by null来取消默认排序以提高性能。

4.6.5 分页查询1000页后

分页查询通常使用limit或者offset这种偏移量的方式实现，但问题在于在偏移量非常大的时候，比如1001页(limit 10000,10)，MySQL需要查询10010条数据然后只返回后面10条，前面的10000条都会被抛掉，这样的代价非常高，分页查询越往后总是越慢。

1.使用覆盖索引查询id，再用id做联表查询。使用子查询：where id in(xx)也是一样的，mysql会改写成联表查询。

select xx from tb a inner join (select id from tb where xxx limit 10000,10) as b on a.id = b.id;

2.获取上一次分页查询最后一行数据的id值比如10000，需要前端配合修改。

select xx from tb where id > 10000 and xxx limit 10;

4.6.6 禁用或强制使用索引

这种方式会绕过优化器的决策，除非真的非常确定，不然不建议，优化器还是很智能的，而且当前数据下适合的sql可能随着数据量的变更或者mysql的版本升级就不适合了。

USE INDEX，IGNORE INDEX，FORCE INDEX

4.6.7 union和union all

or查询语句改写成union可以提升性能，mysql使用临时表处理此类查询语句。其中union可以消除重复的行，如不是确实需要去重应尽可能使用union all，union会导致给临时表加上distinct选项导致查询性能降低。
实际开发中应减少此类语法的使用，可以在服务层分别获取结果作处理，不应该把复杂的业务写到数据库层里，不仅加重了数据库的负担也不易于后期的扩展和维护。