MySQL优化实战

Mysql如何选择合适的索引

trace使用

开启trace工具会影响mysql性能，所以只能临时分析sql使用，用
完之后立即关闭

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
mysql> SELECT * from user WHERE name like '张三%' ORDER BY code limit 100;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
mysql> set session optimizer_trace="enabled=off"; ‐‐关闭trace

分析trace中的执行计划。可以有效分析建的索引为什么没有走。查看优化器如何选择执行计划，获取每个可能的索引选择的代价。

TRACE 字段中整个文本大致分为三个过程。

• 准备阶段：对应文本中的 join_preparation
• 优化阶段：对应文本中的 join_optimization
• 执行阶段：对应文本中的 join_execution
  使用时，重点关注优化阶段和执行阶段。
  索引字段会作用在查询和排序时，有排序字段时考虑跟查询一块建立组合索引;

常见sql深入优化

Order by与Group by优化

mysql有两种排序算法：单路排序和双路排序

解释这两种算法之前，要了解的是mysql有个专门的内存区域是sort buffer,用来排序。MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式；

单路排序

mysql会一次性取出所有字段进行，然后放入到sortbuffer中，排完序之后返回给客户端

双路排序

mysql会取出要排序的字段和主键值，排完序之后，进行回表查询，再把数据返回给客户端

从这个地方来说：进行不要用 select *，需要什么字段就查询什么字段，减少双路排序。
如果一次性需要排序的数据比较到怎么办？也就是排序的数据大小超过了sortbuffer的大小，还要用到文件排序。
但是如果要排序的字段是有索引的，也就是有序的，就不需要排序了，这也就是为什么要求排序的字段要有索引
注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

• 总结
  • MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
  • order by满足两种情况会使用Using index。
    • order by语句使用索引最左前列。
    • 使用where子句与order by子句条件列组合满足索引最左前列。
  • 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
  • 如果order by的条件不在索引列上，就会产生Using filesort。
  • 能用覆盖索引尽量用覆盖索引
  • group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

索引设计原则

• 代码先行，索引后上不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？
  这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。
• 联合索引尽量覆盖条件比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
• 不要在小基数字段上建立索引,索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。
  • 如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。
  • 一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
• 长字符串我们可以采用前缀索引,尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
  • 当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
  • 对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个
  • 字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY
    index(name(20),age,position)。
  • 此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
  • 但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排
    序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
• where与order by冲突时优先where,在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?
  • 一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
  • 因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可
    能会小很多。
• 基于慢sql查询做优化
  可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

核心思想就是，尽量利用一两个复杂的多字段联合索引，抗下
你80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询，保证这种大数据量表的查询尽
可能多的都能充分利用索引，这样就能保证你的查询速度和性能了！

分页查询优化

很多时候我们业务系统实现分页功能可能会用如下sql实现
select * from table limit 10000,10;
表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010
条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率
是非常低的。

常见的分页场景优化技巧

• 根据自增且连续的主键排序的分页查询

select * from employees limit 90000,5;
改为:
select * from table where id > 90000 limit 5;
这种改写得满足以下两个条件
主键自增且连续
结果是按照主键排序的

• 根据非主键字段排序的分页查询

select * from employees ORDER BY name limit 90000,5;
扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引
关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录
select * from table inner join (select id from table order by name limit 90000,5) ed on e.id = ed.id;

Join关联查询优化

mysql的表关联常见有两种算法

• Nested-Loop Join 算法 (嵌套循环连接 Nested-Loop Join(NLJ) 算法)

  一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

• Block Nested-Loop Join 算法 (基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法)

  把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

  • 被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

    如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
    很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
    因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有 索引的情况下 NLJ 算法比 BNL算法性能更高

• 对于关联sql的优化

  • 关联字段加索引，让mysql做join操作时尽量选择NLJ算法
  • 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

    对于小表定义的明确
    在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数
    量，数据量小的那个表，就是“小表”，应该作为驱动表。

• in和exsits优化

  原则：小表驱动大表，即小的数据集驱动大的数据集

  • in：当B表的数据集小于A表的数据集时，in优于exists
  • exists：当A表的数据集小于B表的数据集时，exists优于in

• count(*)查询优化

  • EXPLAIN select count(1) from employees;
  • EXPLAIN select count(id) from employees;
  • EXPLAIN select count(name) from employees;
  • EXPLAIN select count(*) from employees;

  注意：以上4条sql只有根据某个字段count不会统计字段为null值的数据行

  • 字段有索引：count(*) ≈ count(1) > count(字段) > count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
  • 字段无索引：count(*) ≈ count(1) > count(主键 id) > count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

常见优化方法

• 查询mysql自己维护的总行数

  对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算

• show table status

  如果只需要知道表总行数的估计值可以用如下sql查询，性能很高

• 将总数维护到Redis里

  插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

• 增加数据库计数表

  插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

阿里巴巴Mysql规范解读

补充：MySQL数据类型选择

在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：

• 确定合适的大类型：数字、字符串、时间、二进制；
• 确定具体的类型：有无符号、取值范围、变长定长等。

在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量
把字段定义为NOT NULL，避免使用NULL。

数值类型

• 优化建议
  • 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
  • 建议使用TINYINT代替ENUM、BITENUM、SET。
  • 避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。
  • DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
  • 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
  • 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

日期和时间

• 优化建议
  • MySQL能存储的最小时间粒度为秒。
  • 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
  • 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
  • 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
  • TIMESTAMP是UTC时间戳，与时区相关。
  • DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
  • 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
  • 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。

字符串

• 优化建议
  • 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
  • CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
  • 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
  • BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
  • BLOB和TEXT都不能有默认值。

PS：INT显示宽度

CREATE TABLE `user`(
`id` TINYINT(2) UNSIGNED
);

• 这里表示user表的id字段的类型是TINYINT，可以存储的最大数值是255。
• 所以，在存储数据时，如果存入值小于等于255，如200，虽然超过2位，但是没有超出TINYINT类型长度，所以可以正常保存；
• 如果存入值大于255，如500，那么MySQL会自动保存为TINYINT类型的最大值255。