2018-02-24
六、优化手段
主要以查询优化、索引使用和表结构设计方面进行讲解。
6.1 查询优化
1) 避免 SELECT *，需要什么数据，就查询对应的字段。
2) 小表驱动大表，即小的数据集驱动大的数据集。如：以 A，B 两表为例，两表通过 id 字段进行关联。
当 B 表的数据集小于 A 表时，用in优化 exist；使用in，两表执行顺序是先查 B 表，再查 A 表select * from Awhereidin(select id from B)当 A 表的数据集小于 B 表时，用 exist 优化in；使用 exists，两表执行顺序是先查 A 表，再查 B 表select * from Awhereexists (select 1 from BwhereB.id = A.id)
3) 一些情况下，可以使用连接代替子查询，因为使用 join，MySQL 不会在内存中创建临时表。
4) 适当添加冗余字段，减少表关联。
5) 合理使用索引（下文介绍）。如：为排序、分组字段建立索引，避免 filesort 的出现。
6.2 索引使用
6.2.1 适合使用索引的场景
1) 主键自动创建唯一索引
2) 频繁作为查询条件的字段
3) 查询中与其他表关联的字段
4) 查询中排序的字段
5) 查询中统计或分组字段
6.2.2 不适合使用索引的场景
1) 频繁更新的字段
2) where 条件中用不到的字段
3) 表记录太少
4) 经常增删改的表
5) 字段的值的差异性不大或重复性高
6.2.3 索引创建和使用原则
1) 单表查询：哪个列作查询条件，就在该列创建索引
2) 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段
3) 不要对索引列进行任何操作（计算、函数、类型转换）
4) 索引列中不要使用 !=，<> 非等于
5) 索引列不要为空，且不要使用 is null 或 is not null 判断
6) 索引字段是字符串类型，查询条件的值要加''单引号,避免底层类型自动转换
违背上述原则可能会导致索引失效，具体情况需要使用 explain 命令进行查看
6.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 失效
6.3 数据库表结构设计
6.3.1 选择合适的数据类型
1) 使用可以存下数据最小的数据类型
2) 使用简单的数据类型。int 要比 varchar 类型在mysql处理简单
3) 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
4) 尽可能使用 not null 定义字段，因为 null 占用4字节空间
5) 尽量少用 text 类型,非用不可时最好考虑分表
6) 尽量使用 timestamp 而非 datetime
7) 单表不要有太多字段，建议在 20 以内
6.3.2 表的拆分
当数据库中的数据非常大时，查询优化方案也不能解决查询速度慢的问题时，我们可以考虑拆分表，让每张表的数据量变小，从而提高查询效率。
1) 垂直拆分：将表中多个列分开放到不同的表中。例如用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中。
插入数据时，使用事务确保两张表的数据一致性。
2) 水平拆分：按照行进行拆分。例如用户表中，使用用户ID，对用户ID取10的余数，将用户数据均匀的分配到0~9的10个用户表中。查找时也按照这个规则查询数据。
6.3.3 读写分离
一般情况下对数据库而言都是“读多写少”。换言之，数据库的压力多数是因为大量的读取数据的操作造成的。我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。
七、服务器参数调优
7.1 内存相关
sort_buffer_size 排序缓冲区内存大小
join_buffer_size 使用连接缓冲区大小
read_buffer_size 全表扫描时分配的缓冲区大小
7.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 到磁盘
7.3 安全相关
expire_logs_days 指定自动清理 binlog 的天数
max_allowed_packet 控制 MySQL 可以接收的包的大小
skip_name_resolve 禁用 DNS 查找
read_only 禁止非 super 权限用户写权限
skip_slave_start 级你用 slave 自动恢复
7.4 其他
max_connections 控制允许的最大连接数
tmp_table_size 临时表大小
max_heap_table_size 最大内存表大小
笔者并没有使用这些参数对 MySQL 服务器进行调优，具体详情介绍和性能效果请参考文章末尾的资料或另行百度。
八、硬件选购和参数优化
硬件的性能直接决定 MySQL 数据库的性能。硬件的性能瓶颈，直接决定 MySQL 数据库的运行数据和效率。
作为软件开发程序员，我们主要关注软件方面的优化内容，以下硬件方面的优化作为了解即可
8.1 内存相关
内存的 IO 比硬盘的速度快很多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的 IO
8.2 磁盘 I/O 相关
1) 使用 SSD 或 PCle SSD 设备，至少获得数百倍甚至万倍的 IOPS 提升
2) 购置阵列卡同时配备 CACHE 及 BBU 模块，可以明显提升 IOPS
3) 尽可能选用 RAID-10，而非 RAID-5
8.3 配置 CUP 相关
在服务器的 BIOS 设置中，调整如下配置：
1) 选择 Performance Per Watt Optimized（DAPC）模式，发挥 CPU 最大性能
2) 关闭 C1E 和 C States 等选项，提升 CPU 效率
3) Memory Frequency（内存频率）选择 Maximum Performance
作者：moonlightL
链接：https://juejin.im/post/59d83f1651882545eb54fc7e
来源：掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
