无论是在日常的工作中还是在面试中都会遇到Mysql的优化问题，下面我将从Sql优化和Mysql的参数优化两个方面做整理。

1、Sql优化

（1）limit优化sql

Mysql在数据库的数据量非常大的时候会存在深度分页的问题，深度分页有多种优化方案，典型的方案是采用limit优化:

#未使用limit优化前的sql 
select * from student where limit 20000, 10; 
#使用limit优化后的深度分页查询 
select * from student where id > 20000 limit 10;

（2）like模糊查询

数据库字段使用like模糊查询的时候，不要将%放在前面，因为这样会使索引失效进而全表扫描。

#like的正确使用方法 
select * from student where name like "zhang%"

（3）union查询优化

union查询对两个结果集做并集操作的时候，自动地去除重复行（相当于distinct），同时进行默认规则的排序。建议采用union all替代union

union all查询对两个结果集做并集操作的时候，不会去除重复行并且不进行默认的排序；所以针对有特殊要求的业务需要在业务层做去重和排序。

#union all 
select * from student where name = "zhangsan" 
union all 
select * from student where age = 15;

（4）区分场景使用exists和in

（a）如果子查询得出的结果集数据较少，主查询中的表数据较多且查询字段又有索引时采用in

select * from student where teacher_id in 
 (select id from teacher where name = “张老师”);

（b）如果外层的主查询数据较少，子查询中的表大且又有索引时使用exists，

select * from teacher as t where exists  
(select 1 from user as u where u.id = t.id)

备注：exists和in的区别

exists以外层表为驱动表先被访问；in是先执行子查询。exists 是对外表做 loop 循环，每次 loop 循环再对内表进行查询； i n 把外表和内表做 hash 连接。

（5）删除表使用truncate

truncate student;

truncate是直接把表删除然后再重建表结构，性能更高；删除操作记录不记入日志所以不可以回滚。truncate执行后表和索引所占用的空间会恢复到初始大小。

常见的删除还有delete，delete语句执行删除的过程是每次从表中删除一行所以性能较低；删除操作会作为事务记录在日志中保存，以便进行回滚操作；delete只是将被删除的记录标记为已删除，不会立即减少表或索引所占用的空间。

（6）新增操作尽量使用批操作

insert into student(name, age) values ("zhangsan",1),("lisi",2);

批量操作（即一次数据库操作中插入多个数据行）相比于sql单条操作可减少大量的IO交互和SQL解析，从而提高了插入效率。

（7）过滤先行

select name from student where student_area = "北京" or  
student_area = "广州"  group by name;

无论是分组还是排序或者多表join，如果条件允许的情况下，首先把用不到的记录先过滤掉，减少数据量来提高查询效率。

（8）函数在等号的右侧

select * from student where area = left("中国北京",4);

众所周知在索引上使用函数会使得索引失效，所以针对需要使用函数的索引字段，我们将函数放在等号的右侧。

（9）字段类型最小化

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常会使SOL执行更快，因为它占用更少的磁盘、内存、CPU缓存，并且处理时需要的CPU周期也更少。我们常用的表示数据软删除字段（isDelete），本字段的数据值一般都是有限值，所以适合采用tinyint来定义其类型。

（10）正确使用char和varchar

char是定长的字段，适合存储长度固定的数据，如身份证号、、邮政编码手机号等等；varchar是存储不定长的数据，它会单独申请一块空间记录数据的长度，然后在长度后面记录真实的数据，适合存储不确定长度的数据。

char类型存取效率高；varchar更节省空间（因为char类型不够规定的长度会使用空字符串替代）把varchar的长度调整为最小可用，是可以优化sql排序性能的。

（11）添加索引的原则

（a）频繁作为查询条件的字段应该创建索引，频繁更新的字段不适合创建索引

（b）多表关联查询中的关联字段，查询中统计或者分组字段，查询中排序字段，应该创建索引

（c）尽量使用区分度高的字段创建索引

（d）多条件组合查询优先创建组合索引

（e）delete和update语句的where条件必须有索引，否则会导致锁表

（12）巧用force index

Mysql查询优化器在执行SQL语句时会选择它认为最合适的索引，但有时却并不准确，不是实际上最快的索引，此时可以用force index人为指定索引；

select * from student force index(idx_teacher_id) where teacher_id in 
 (123,124,154);

2、Mysql的参数优化

下面针对innodb的调优，先来看一下mysql的架构图：

（1）Buffer Pool调优

Mysql在启动的时候就向内存申请一块默认大小是128M的空间，次空间就是Buffer Pool ，其存在的主要目的是让尽可能多的操作在内存中。

#查询Mysql的buffer pool大小的 
show variables like 'innodb_buffer_pool_size%';

为了评估buffer pool的性能指标，通过命令查询多少的查询在buffer pool中执行的

show status like 'innodb_buffer_pool_read%';

命中率 = 内存缓冲页数据量请求 / （磁盘读取的数据量请求 + 内存缓冲页数据量请求） = 1398845854999 / （1398845854999 + 94952865）= 0.99。

如果当前的命中率的值低于90%，我们就需要考虑增加buffer pool的大小

#设置buffer pool的大小 
set global innodb_buffer_pool_size = 6442450944 
#监控调整的buffer pool大小是否生效了 
show status where variable_name = 'Innodb_buffer_pool_resize_status'

（2）redo log参数调优

redo log是一种基于磁盘的数据结构，用于在宕机恢复期间更正由不完整事务写入的数据。 r edo log缓存区的大小一般默认是16MB，如果事务中含有blog/text等大字段，这个缓冲区会被很快填满而引起额外的IO负载。配置更大的日志缓冲区就可以有效地提高Mysql的效率。

#查询Mysql的redo log文件的缓冲区大小 
show variables like 'innodb_log_buffer_size%';

#查询redo log文件大小 
show variables like 'innodb_log_file_size%';

redo log文件过大过小都存在问题，

文件过小就会导致刷新数据到Mysql的频率增加，可能出现处理大事务的时候由于存储空间不足导致事务处理失败的问题。

文件过大的时候，如果Mysql宕机的时候，做数据恢复的时候就非常的消耗时间。

如何设置redo log文件的大小呢？这个需要根据每个系统的自身业务情况来设定，可以使用预测法先预测1分钟记录的日志量，再通过1分钟的日志量来预测1小时内存储的日志大小，由于Mysql是最小支持1小时的业务redo log日志的，所以据此来设置 i nnodb_log_file_size的大小。