慢查询优化

  1. 根据 where 条件，组建联合索引『每一次查询只能用上一个索引，所以需要好好利用联合索引』
  2. 使用强制索引，使用 explain 分析查询 sql 时，需要关注 key、rows、filtered 这三个字段，rows 越小表示扫描记录数越少，filtered 越大表示过滤效果越好。

限制 SQL 的最大执行时间

```sh
# 查看数据库默认的最大SQL执行时间，默认是0，即没限制
show global variables like 'max_execution_time';

# 大表需要用 like 全表扫描获取计数时，可以改为查询列表并限制最大获取计数，同时限制单条sql的执行时间，避免数据库出现大量慢sql导致数据库崩溃
select /*+ MAX_EXECUTION_TIME(1000) */ id from material limit 20
```

实用的表优化方案

  1. 定期分析表： analyze table tablename; // 本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。
  2. 定期检查表【处理很慢】：check table tablename; // 本语句用于检查一个或多个表是否有错误。
  3. 定期优化表【处理很慢】：ALTER TABLE table_name ENGINE=InnoDB; 对应innodb表，不支持optimize table tablename;

操作前提：短时间出现大量数据删除、碎片占用足够大时才需要手动执行 optimize 命令。

优点：进行碎片整理，减少磁盘占用，提高索引效率

缺点：锁表，锁表会导致 insert，delete，update 语句堵住

```sql
-- 分析数据表大小
select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where data_free !=0 order by table_rows desc;
```

索引

通过索引通常可以帮助用户解决大多数的 SQL 性能问题。

索引分类

  • B+树 索引：B+树索引中的 B 不是代表二叉树（binary），而是代表平衡树（balanced）【最左匹配原则是使用索引中的最左边进行查找，是索引使用的首要原则】
  • HASH 索引： 只有 Memory 引擎支持，使用场景简单
  • Full-text(全文索引)：是 MyISAM 的一个特殊索引类型，InnoDB 从 MySQL 5.6 版本开始提供对全文索引的支持

存在索引但不能使用索引的典型场景

  • 以 % 开头的 like 查询不能够利用索引，执行计划中key的值为 NULL 表示没有使用索引
  • 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串时，那么一定记得在where条件中把字符常量值用引号引起来
  • 复合索引的情况下，查询条件要满足最左匹配原则
  • 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。如返回记录数很多
  • 用 or 分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会用到
  • 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

MySQL强制索引和禁止某个索引

  1. mysql强制使用索引: force index(索引名或者主键PRI)

    1
2

    select * from tablei index(PRI)
    left join table2 force index(table2_index) on table.id=table2.id;

  2. mysql禁止某个索引：ignore index(索引名或者主键PRI)

    1
2

    select * from table ignore index(PRI) limit 2; -- 禁止使用主键
select * from table ignore index(PRI,ziduan1_index) limit 2; -- 禁止使用索引"PRI,ziduan1_index"

  3. 组合索引是有序的，遵循最左前缀原理

配置优化

  1. group_concat_max_len: 聚合函数，默认1024，可以设置字符创连接允许的最大长度（eg: 使用group_concat()函数，对查询的数据进行字符串连接操作）
  2. max_length_for_sort_data: 增大该值，能够让MySQL选择更优化的Filesort排序算法
  3. sort_buffer_size: 适当增大该值，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行

书写高质量 SQL 建议

  1. 不要有超过 5 个以上的表连接

    • 连表越多，编译的时间和开销也就越大。
    • 把连接表拆开成较小的几个执行，可读性更高。
    • 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

  2. 索引不宜太多，一般 5 个以内。

    • 索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。
    • insert 或 update 时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。
    • 一个表的索引数最好不要超过 5 个，若太多需要考虑一些索引是否没有存在的必要。

  3. 索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。

    因为 SQL 优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql 查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

  4. 尽量用 union all 替换 union

    如果使用 union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用 union all 代替 union，这样会提高效率。

  5. 尽可能使用 varchar/nvarchar 代替 char/nchar

    因为首先变长字段存储空间小，可以节省存储空间。其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

  6. 优化 limit 分页

    我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下。

    反例：select id，name，age from employee limit 10000，10

    正例：

     ```sql
 //方案一 ：返回上次查询的最大记录(偏移量)
 select id，name from employee where id>10000 limit 10.

 //方案二：order by + 索引
 select id，name from employee order by id  limit 10000，10

 //方案三：在业务允许的情况下限制页数：
 ```

    理由：

    • 当偏移量最大的时候，查询效率就会越低，因为 Mysql 并非是跳过偏移量直接去取后面的数据，而是先把偏移量 + 要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。
    • 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。
    • 方案二使用 order by + 索引，也是可以提高查询效率的。
    • 方案三的话，建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

其他

  1. 通过 EXPLAIN/DESC 分析低效 SQL 的执行计划，主要是了解 SELECT 语句执行的情况。
  2. 如何解决 MySQL server has gone away 问题？

最好的方法，即检查 MySQL 的连接状态，使其重新链接。Mysql 官方提供了 mysql_ping() 方法，可用于定时检查 mysql 的连接状态。