Mysql优化相关小结
一、查看执行计划
用explain查看（explain extends可以进一步查看有没有发生隐式转换），结果含义如下：
* type，ALL表明是全表扫描；
* rows，表明扫描的总行数有多少；
二、常见导致索引失效原因
* 1.数据类型问题（比如应用传入字段类型和数据库字段类型不一致）或校验规则不匹配（比如A标的字段区分大小写，B表不区分，这样做关联的时候会不匹配）（因为mysql默认情况下字段内容是不区分大小写的，也即大小写不敏感，可以使用BINARY使搜索区分大小写）
* 2.应用的sql加了data等函数，由于索引是对原始字段加的，因此使用函数会索引失效；
三、索引优化
1.分页（翻页的优化写法）
普通写法：
select * from buyer where sellerid = 100 limit 100000, 5000;
普通limit的翻页写法，越往后翻页速率越满，会加载前M+N条数据；
优化写法（注意：表上一定要有sellerid的索引，即子查询一定要在索引里面去查）：
select t1.* from buyer t1 (select id from buyer where sellerid = 100 limit 100000, 5000) t2 where t1.id = t2.id;
相当于普通写法扫面原表，优化写法扫面索引；
2.子查询优化
典型子查询：
``SELECT first_name FROM employees WHERE emp_no IN (SELECT emp_no FROM salaries_2000 WHERE salary = 5000);``
MySQL的处理逻辑是遍历employees表中的每一条记录，代入到子查询中中去(比如emplyee表有10w行，salaries表有20w行，那么关联查询是10w*20w级别)。
优化子查询（改写为关联查询）：
``SELECT first_name FROM employees emp, (SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal WHERE emp.emp_no = sal.emp_no;``
（这个关联级别是10w* 子查询结果集行数）
3.索引优化最佳实践
* 利用高效的limit查询，避免分页查询给数据库带来性能影响；
* 子查询优化，子查询在5.1，5.5版本中都存在较大风险，将子查询改为关联 使用Mysql 5.6的版本，可以避免麻烦的子查询改写；
* 只查询需要的字段，避免用 SELECT * 查询所有字段数据，只查询需要的字段数据；
四、锁问题最佳实践
设计开发阶段：
* 1.避免使用myisam存储引擎，改用innodb引擎
* 2.避免大事务，长事务导致事务在数据库中的运行时间加长
* 3.选择升级到MySQL5.6版本，支持online ddl
管理运维阶段：
* 1.在业务低峰期执行上述操作，比如创建索引，添加字段；
* 2.在结构变更前，观察数据库中是否存在长SQL，大事务；
* 3.结构变更期间，监控数据库的线程状态是否存在lock wait；
* 4.ApsaraDB支持在DDL变更中加入 wait timeout；
五、数据库延迟问题最佳实践
排查思路：
* 1.一看资源是否达到瓶颈；
* 2.二看线程状态是否有锁；
* 3.三判断是否存在大事务；
最佳实践：
* 1.使用innodb存储引擎；
* 2.只读实例的规格不低于主实例；
* 3.大事务拆分为小事务；
* 4.DDL变更期间观察是否有大查询；
六、参数优化最佳实践
排查思路：
* 1.查看SQL执行计划；
* 2.查看数据库版本和优化器规则；
* 3.对比参数设置；
* 4.对比硬件配置；
最佳实践：
* 1.Query_cache_size；
* 2.Temp_table_size
* 3.Back_log
七、连接数最佳实践
* 看下有没有慢sql
* 连接数配置是否超过了最大连接数，是的话就需要扩规格
* 能否加一层db-gateway来复用数据库连接
八、io 100%
排查思路：
* 1、慢sql问题
* 2、ddl， create index等；
* 3、配置问题，检查配置，不够则扩规格
九、磁盘100%
排查思路：
磁盘空间组成：数据文件、临时文件、日志文件
tips：数据删除，表空间不会回收，除非truncate表或者表重建（optimize table ）
最佳实践：
数据空间问题：
* 采用optimize table收缩表空间；删除不必要的索引；
日志空间问题：
* 1、减少大字段的使用；
* 2、使用truncate替代delete from；
临时空间问题：
* 1、适当调大sort_buffer_size;
* 2、创建合适索引避免排序；
十、内存问题
内存组成：buffer pool size + dictionary memory + thread cost memory
最佳实践：
buffer pool size：
* 1、创建合适的索引，避免大量的数据扫描；
* 2、去除不必要的索引，降低内存的消耗；
thread cost memory：
* 1、创建合适的索引避免排序；
* 2、只查询应用所需要的数据；
dictionary memory：
1、不要过度分表；