MySQL 性能优化

1. 使用EXPLAIN分析 SELECT 查询的结果

explain 可以帮助我们分析 select 语句，让我们知道查询效率低下的原因，从而改进我们的查询

EXPLAIN SELECT * FROM `region`;

select_type 有 simple，primary，subquery，derived，union，union_result。

• simple表示查询中不包含子查询或者union
• 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
• 在select或where列表中包含了子查询，则子查询被标记成subquery。
• 在from的列表中包含的子查询被标记成derived。
• 若第二个select出现在union后，则被标记成 union，若 union 在 from 子句的子查询中，外层的 select 被标记成 derived。
• 从union表获取结果的select被标记成union result。

type 叫访问类型，表示在表中找到所需行的方式，常见类型有 all，index，range，ref，eq_ref，const，system，NULL 性能从左至右由差变好。

• all，即full table scan，mysql将遍历全表来找到所需要的行
• index为full index scan，只遍历索引树
• range表示索引范围扫描 ，对索引的扫描开始于一点，返回匹配的值域的行，常见于between，<，>的查询
• ref为非唯一性索引扫描，返回匹配某个单独值的所有行，常见于非唯一索引即唯一索引的非唯一前缀进行的查找
• eq_ref表示唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描
• const，system表示当对查询部分进行优化，并转化成一个常量时，使用这些类型访问。比如将主键置于where列表中，mysql就能把该查询置成一个常量。system是const的一个特例，当查询表中只有一行的情况下使用的是system
• NULL表示在执行语句中，不用查表或索引

possible_keys 表示能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询使用。

key 表示查询时使用的索引。若查询中使用了覆盖索引，则该索引仅出现在key中
keylen 表示索引所使用的字节数，可以通过该列结算查询中使用的索引长度

ref 表示上述表的链接匹配条件，即哪些列或常量可被用于查找索引列上的值

rows 表示根据mysql表统计信息及索引选用情况，估算找到所需记录要读取的行数

extra 表示不在其他列并且也很重要的额外信息

using index 表示在相应的select中使用了覆盖索引

usingwhere 表示存储引擎搜到记录后进行了后过滤(POST-FILTER)，如果查询未能使用索引，usingwhere的作用只是提醒我们mysql要用where条件过滤z结果集

using temporay 表示用临时表来存储结果集，常见于排序和分组查询

usingfilesort，mysql 中无法用索引完成的排序成为文件排序

2. 合理使用索引

• 索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。

3. 不要使用 ORDER BY RAND()

• 这样使用只让你的数据库的性能呈指数级的下降。

SELECT * FROM table LIMIT m,n;

• 解释：从 m+1 条记录开始，取n条记录

4. 避免 SELECT *

• 需要什么字段就拿什么字段，不要每次都全部拿出来，这样会浪费掉好多资源

5. 为每张表设置一个ID

• 我们应该为数据库里的每张表都设置一个 ID 做为其主键，而且最好的是一个 INT 型的（推荐使用 UNSIGNED ），并设置上自动增加的 AUTO_INCREMENT 标志。

6. 拆分大的 DELETE 或 INSERT 语句

• 如果你需要在一个在线的网站上去执行一个大的DELETE或INSERT查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。可以使用 LIMIT

example：每次删除1000条记录

while(1) {
    mysql_query("DELETE FROM persons WHERE `created_at` < '2018-03-16 12:12:12' LIMIT 1000 ");
    if (mysql_affected_rows == 0) {
        //没有东西删除
        break;
    }
    sleep(5);
}

7. 选择正确的存储引擎

• Innodb 支持事务；
• 若系统崩溃了，MyISAM恢复比Innodb困难；
• 如果只执行大量的 SELECT，MyISAM 是更好的选择;
  

  到最后没啥特殊的话就使用Innodb 吧，现在Mysql默认存储引擎就是 Innodb

8. 添加缓存

• 当查询量越来越大的时候，请使用 memcached 或者 redis，具体选用哪个根据自身业务调整

9. 反范式化设计

• 反范式化设计,以空间换时间,避免join,有些join操作可以在用代码实现,没必要用数据库来实现；比如 Laravel 的 Eloquent 就是代码将 join 操作拆分成简单的单表查询，内存占用加大，速度快了一半；

10. 开启慢查询

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

# 开启慢查询
set global slow_query_log='ON';
# 设置日志路径
set global slow_query_log_file='/var/log/mysql/data/slow.log';
# 查询超过1秒就记录
set global long_query_time = 2;

11. 切分

• 包括垂直切分和水平切分，实现方式上又包括分库、分表

11. 列属性尽量为 NOT NULL

• 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在 MySQL 中，含有空值的列很难进行查询优化，而且对表索引时不会存储 NULL 值的，所以如果索引的字段可以为 NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。