MySQL优化指南

介绍

本文帮助你从系统到使用去优化MySQL。

字段设计

1.保证业务的前题下，数据库字段设置为最小的类型。

除了varchar和char以外，字段类型定义的储存空间。举个例子，你使用int类型就会占用4个字节的储存空间，int(2)只是限制数字的最大值是两位。

2.MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。

三大范式设计

1. 第一范式（原子性）：一个字段不能有两个意思，比如地址要把省市区拆分出去，不能放一个字段。
2. 第二范式（唯一性）：商品表里的每个商品记录里不能都有供应商的电话、地址信息，供应商的应该单独放一个表。
3. 第三范式（冗余性）：数据库不同的表，字段有重复。例如：订单表里有商品名称和价格，订单商品表也有商品名称和价格。

反范式化

用空间换时间，比如 订单表 里增加 订单商品表 的商品名称字段，就可以避免 订单表 和 订单商品表 联合查询了。

SQL查询分析

1.查询SQL语句各阶段消耗的时间，session级别的配置。

1. 开启查询时间记录：set profiling = 1;
2. 查看所有SQL语句的查询时间：show profiles;
3. 查询某个SQL语句的详细时间：show profiles for query ID

2.根据慢日志定位慢查询SQL，持久修改需更改配置文件，set global重启无效。

1. 查看慢日志是否开启，show variables like ‘%quer%’;
2. 将慢日志记录开启，set global slow_query_log = on；
3. 查看慢日志文件地址，show query_log_file；
4. 设置执行时间多少秒为慢日志，set global long_query_time = 1；（生效需重连数据库）
5. 慢查询的数量：show status like ‘%slow_queries%’;

3.使用explain放在执行SQL前分析，例：explain select…

• type类型越靠后越不慢：system>const>eq_ref>…>range>index>all
• extra出现以下两条代表没有使用索引
  1. using filesort：使用的是外部索引排序，MySQL无法利用索引完成排序称为“文件排序”
  2. using temporary：表示MySQL查询结果排序时使用临时表，常见order by或分组查询group by

SQL索引

1.索引的列上不要用函数，否则无法进行索引查询
2.字符串前缀索引，由于字符串太长不可能全部建立索引，所以create index要限制数量
3.联合索引最左前缀匹配原则

• 遇到范围查询（>、<、between、like）就停止匹配
  • 联合索引最左边的字段必须使用
    • 例1：index…（‘a’,‘b’,‘c’），只用b或bc将不会使用索引
    • 列2：index…（‘a’,‘b’,‘c’），只用a、c的查询只会用到a的索引
  • 联合索引适用于多个字段要同时被使用的组合查询，可以减少储存的空间
  • 索引文件具有 B-Tree 的最左前缀匹配特性，某个列如果左边的值未确定，那么无法使用此索引。
    • 查询时，不要使用全模糊搜素
    • 查询时，不要使用做模糊搜素

4.索引扫描优化排序

1. 索引的列表顺序和Order by子句的顺序完全一致
2. 索引中所有列的方向（升序，降序）和Order by子句完全一致
3. Order by中的字段全部在关联表中的第一张表中

5.索引优化锁

• 查询的字段上若是有索引，可以避免其他事务在查询的同一个字段数据时被锁住

6.索引的冗余

1. 主键索引创建了就不需要再创建唯一索引、单列索引，这个是冗余的
2. 单列创建了索引，就不需要增加到在复合索引了
3. 可以找工具检查多余的索引：pt-duplicate-key-checker h=127.0.0.1