MySQL优化

MySQL优化三大方向:

1. 服务器的内核优化
2. MySQL 的参数优化
3. SQL语句及表的优化

MySQL参数优化：

1. 默认的最大连接数为100，可以使用show variables like ‘max_connections’;
2. 修改最大连接数，最大可设置16384个，超过了没有用。
3. 增大缓存的连接数量，back_log
4. 减少交互连接在被服务器关闭前的等待时间，interactive_timeout,默认28800，可优化为7200，一个客户端连接默认连接8小时
5. 开启查询缓存时，设置缓存区大小，（分析碎片多少）设置缓存区的最小单位
6. 修改连接缓存：
   1. 扫描表的缓存，默认128K,可改为16M
   2. 随机读缓存 可设置16M
   3. 排序缓冲区，增加这值加速ORDER BY或GROUP BY操作，默认2M,可改为16M
   4. 联合查询的缓冲区
   5. 表高速缓存的大小

MySQL语句的优化：

1. 对于字典表或是很少修改的表使用查询缓存。（函数的可变性会导致查询缓存失效）MySQL 8.0删除了查询缓存功能

2. 使用EXPLAIN分析sql。

   id:选择标识符
   select_type:表示查询的类型。
   table:输出结果集的表
   partitions:匹配的分区
   type:表示表的连接类型
   possible_keys:表示查询时，可能使用的索引
   key:表示实际使用的索引
   key_len:索引字段的长度
   ref:列与索引的比较
   rows:扫描出的行数(估算的行数)
   filtered:按表条件过滤的行百分比
   Extra:执行情况的描述和说明

3. 当只要一行数据时用limit 1，找到一条数据后会停止搜索，而不是继续查询下一条符合条件的数据记录。

4. 为搜索字段建立索引。

   MySQL自带的全文索引只对myisam数据表有效，只能对英文进行全文检索，能找到默认最小长度为4的字符。使用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

   联合索引和位置没有关系,mysql会自动优化。（a,b,c）下面两条时一样的。都可以使用

   a=2 and b=3 and c=4

   c=4 and b=3 and a=2

   普通索引和唯一索引的，普通索引适用于写多读少的表，唯一索引无法使用change buffer.

5. 使用join时使用相同类型的列，并将其索引，不用类型的列无法使用索引。

6. 不要order by rand()

7. 避免select *

8. 永远为每个表设置一个ID

9. 可以使用ENUM 而不要使用varchar，保存的时TINYINT,外表显示为字符串

10. 尽可能使用not null

11. 固定长度的表会更快，避免使用varchar,text,blob类型

12. 垂直分割，一表分多表，降低表的复杂度

13. 拆分大的delete和insert

14. 越小的列会越快

15. 使用正确的存储引擎

16. 小心“永久链接”

17. 区分度越高的列索引效果越好

18. 列中含有NULL值将不会被包含在索引中，如含有NULL值会导致索引失效，is null 和 is not null

19. MySQL 只能使用一个索引，所以在排序的时候尽量使用复合索引

20. like操作前模糊也会导致索引失效

21. 不要再索引列上进行运算，函数，类型转换(隐式类型转化，字符串不加引号)操作会导致索引失效

22. 使用not exists代替not in

23. 查询数据量过大也会导致索引失效

24. 使用复合索引注意最左前缀原则

25. 使用！=或是<>会导致索引失效

26. 少用or

27. 27.InnoDB 为什么用B+树 MySQL索引-B+树