Mysql优化建议

承接上篇EXPLAIN学习在发现问题后可结合本篇内容寻找一个合适的解决方案（可优化的地方也就是容易出现问题的地方）

 

1 查询优化

 

1) 避免 SELECT *，需要什么数据，就查询对应的字段。

2) 小表驱动大表，即小的数据集驱动大的数据集。如：以 A，B 两表为例，两表通过 id 字段进行关联。

 

# 当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表
select * from A where id in (select id from B)

# 当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id = A.id)

 

3) 一些情况下，可以使用连接代替子查询，因为使用 join，MySQL 不会在内存中创建临时表。

4) 适当添加冗余字段，减少表关联。

5) 合理使用索引（下文介绍）。如：为排序、分组字段建立索引，避免 filesort 的出现。

 

2 索引使用

 

2.1 适合使用索引的场景

 

1) 主键自动创建唯一索引

2) 频繁作为查询条件的字段

3) 查询中与其他表关联的字段

4) 查询中排序的字段

5) 查询中统计或分组字段

 

2.2 不适合使用索引的场景

 

1) 频繁更新的字段

2) where 条件中用不到的字段

3) 表记录太少

4) 经常增删改的表

5) 字段的值的差异性不大或重复性高

 

2.3 索引创建和使用原则

 

1) 单表查询：哪个列作查询条件，就在该列创建索引

2) 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段

3) 不要对索引列进行任何操作（计算、函数、类型转换）

4) 索引列中不要使用 !=，<> 非等于

5) 索引列不要为空，且不要使用 is null 或 is not null 判断

6) 索引字段是字符串类型，查询条件的值要加''单引号,避免底层类型自动转换

违背上述原则可能会导致索引失效，具体情况需要使用 explain 命令进行查看

 

2.4 索引失效情况

 

除了违背索引创建和使用原则外，如下情况也会导致索引失效：

1) 模糊查询时，以 % 开头

2) 使用 or 时，如：字段1（非索引）or 字段2（索引）会导致索引失效。

3) 使用复合索引时，不使用第一个索引列。

 

index(a,b,c) ，以字段 a,b,c 作为复合索引为例：

语句	索引是否生效
where a = 1	是，字段 a 索引生效
where a = 1 and b = 2	是，字段 a 和 b 索引生效
where a = 1 and b = 2 and c = 3	是，全部生效
where b = 2 或 where c = 3	否
where a = 1 and c = 3	字段 a 生效，字段 c 失效
where a = 1 and b > 2 and c = 3	字段 a，b 生效，字段 c 失效
where a = 1 and b like 'xxx%' and c = 3	字段 a，b 生效，字段 c 失效

 

3 数据库表结构设计

 

3.1 选择合适的数据类型

 

1) 使用可以存下数据最小的数据类型

2) 使用简单的数据类型。int 要比 varchar 类型在mysql处理简单

3) 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int

4) 尽可能使用 not null 定义字段，因为 null 占用4字节空间

5) 尽量少用 text 类型,非用不可时最好考虑分表

6) 尽量使用 timestamp 而非 datetime

timestamp	datetime
4字节	8字节
默认值为当前时间	默认值为null
update语句时，会更新为当前时间 。把插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其转换为客户端当前时区进行返回	查询时是原样输出
能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’	能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

 

7) 单表不要有太多字段，建议在 20 以内

 

3.2 表的拆分

 

当数据库中的数据非常大时，查询优化方案也不能解决查询速度慢的问题时，我们可以考虑拆分表，让每张表的数据量变小，从而提高查询效率。

 

1) 垂直拆分：将表中多个列分开放到不同的表中。例如用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中。
插入数据时，使用事务确保两张表的数据一致性。

 

2) 水平拆分：按照行进行拆分。例如用户表中，使用用户ID，对用户ID取10的余数，将用户数据均匀的分配到0~9的10个用户表中。查找时也按照这个规则查询数据。

 

3.3 读写分离

 

一般情况下对数据库而言都是“读多写少”。换言之，数据库的压力多数是因为大量的读取数据的操作造成的。我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。

 

四、服务器参数调优

4.1 内存相关

 

sort_buffer_size 排序缓冲区内存大小

join_buffer_size 使用连接缓冲区大小

read_buffer_size 全表扫描时分配的缓冲区大小

 

4.2 IO 相关

 

Innodb_log_file_size 事务日志大小

Innodb_log_files_in_group 事务日志个数

Innodb_log_buffer_size 事务日志缓冲区大小

Innodb_flush_log_at_trx_commit 事务日志刷新策略 ，其值如下：

0：每秒进行一次 log 写入 cache，并 flush log 到磁盘

1：在每次事务提交执行 log 写入 cache，并 flush log 到磁盘

2：每次事务提交，执行 log 数据写到 cache，每秒执行一次 flush log 到磁盘

 

4.3 安全相关

 

expire_logs_days 指定自动清理 binlog 的天数

max_allowed_packet 控制 MySQL 可以接收的包的大小

skip_name_resolve 禁用 DNS 查找

read_only 禁止非 super 权限用户写权限

skip_slave_start 级你用 slave 自动恢复

 

4.4 其他

 

max_connections 控制允许的最大连接数

tmp_table_size 临时表大小

max_heap_table_size 最大内存表大小

 

参考博客：

https://blog.csdn.net/peter_zcs/article/details/88640649

https://mp.weixin.qq.com/s/MxW_P2ePd6Lqs4wfTuuVFA