浅谈MySQL的优化

　　平时在开发中大多在写业务逻辑，很少关注于底层sql的执行效率，大多能交给batis的mapper做的就交给它去做。

　　然而这些天越来越发现，大家还是很愿意手写sql的，往往一段业务逻辑，可以用稍微复杂一些的sql直接查询到，避免了代码中多次访问数据库（当然，我觉得如果sql太过复杂也不是很好，也许性能更好，但对于逻辑的更改和扩展都是不小的负担，这个还是要权衡一下），通过手写sql，可以提高一些查询性能也是不错的。

　　今天在开发过程中遇到了一个情景，对于多个团队id的一个Set传给Mysql（sql中用的in的方式）作为查询条件。之前仅仅用了tk-mybatis的Example的inAnd方法，后来想自己写一下看看性能如何，于是改了mapper中的sql，使用了in标签实现。

　　但是在explain的时候发现，mysql居然没有用到in的列所建立的索引，这就让我很迷惑了，印象中并没有哪里说到过in可以使索引失效的。

　　后来通过交流和测试才发现，原来是mysql底层做了优化，在他认为in中数据量不大，使用索引并不会带来更小开销的情况下，便不会使用索引。这一点其实还是很智能的，我也想借此机会总结一下之前看到过，学习过的一些mysql优化知识，希望平时能学以致用吧。

一.优化的策略

1. 引擎选择（主要针对于MyISAM和InnoDB）

首先是区别：
MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用 MyISAM。
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用 MyISAM存储会省去不少的麻烦。
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename
type=innodb）。
然后是使用场景：
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那
么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操
作，则应该使用 InnoDB，这样可以提高多用户并发操作的性能。

2. 正确使用索引

适合建立的：where子句，连接子句，order by，group by， distinct 后，不要再select列加。
如果数据很少更新，并且查询字段不多，可以考虑索引覆盖。
索引值应该不相同，唯一值时效果最好，大量重复效果很差
使用短索引，指定前缀长度char(50)的前20，30值唯一例：文件名；索引缓存一定（小）时，存的索引多，消耗IO更小，能提高查找速度
最左前缀n列索引，最左列的值匹配，更快。
like查询，索引会失效，尽量少用like；or，计算操作，函数，数据类型转换等都会使索引失效。
多用简单句，避免临时表过多。
能用union all就不用union（union会多排序和去重的花销）
索引本身占空间，并且维护代价高，不是越多越好。

3. 避免使用`SELECT *`

返回结果过多，降低查询的速度
过多的返回结果，会增大服务器返回给端的数据传输量。例：网络传输速度面，弱网络环境下，容易造成请求失效

二、其他硬件优化

1. Linux内核用内存开缓存存放数据

写文件：文件延迟写入机制，先把文件存放到缓存，达到一定程度写进硬盘
读文件：同时读文件到缓存，下次需要相同文件直接从缓存中取，而不是从硬盘取

2. 增加应用缓存

本地缓存：数据防盗服务器内存的文件中
分布式缓存：Redis, Mencache 读写性能非常高，QPS（每秒查询请求数）每秒达到1W以上；数据持久化用Redis，不持久化两者都可以

三、架构优化

　这个我听了一下EP的分享，也看到很多种架构模型，但是还是由于自己经验太少，很多还是听不太明白，总结一下看到过的方法吧：

1. 分表

水平拆分：数据分成多个表拆分后的每张表的表头相同
垂直拆分：字段分成多个表
插入数据、更新数据、删除数据、查询数据时：MyISAM
MERGE存储引擎，多个表合成一个表 InnoDB用alter table，变成MyISAM存储引擎，然后MEGRE
表更大的话就需要分库了

2. 读写分离

读是一些机器，写是一些机器，二进制文件的主从复制，延迟解决方案。
数据库压力大了，可以把读和写拆开，对应主从服务器，主服务器写操作、从服务器是读操作。
主服务器写操作的同时，同步到从服务器，保持数据完整性——主从复制
主从复制原理：基于主服务器的二进制日志（binlog）跟踪所有的对数据库的完整更改实现。要实现主从复制，必须在主服务器上启动二进制日志，主从复制是异步复制，三个线程参与：主服务器一个线程（IO线程）、从服务器两个（IO线程和SQL线程）
主从复制过程：
a. 从数据库，执行start slave开启主从复制；
b.
从数据库IO线程会通过主数据库授权的用户请求连接主数据库，并请求主数据库的binlog日志的指定位置，change master命令指定日志文件位置
c.
主数据库收到IO请求，负责复制的IO线程跟据请求读取的指定binlog文件返回给从数据库的IO线程，返回的信息除了日志文件，还有本次返回的日志内容在binlog文件名称和位置
d.
从数据库获取的内容和位置（binlog），写入到（从数据库）relaylog中继日志的最末端，并将新的binlog文件名和位置记录到master-info文件，方便下次读取主数据库的binlog日志，指定位置，方便定位
e. 从数据库SQL线程，实时检测本地relaylog新增内容，解析为SQL语句，执行。
弊端：延迟
主从复制延迟解决方案：
a. 定位问题：延迟瓶颈，IO压力大，升级硬件，换成SSD
b.
单线程从relaylog执行MySQL语句延迟，换成MySQL5.6以上版本多线程，或者Tungsten第三方并行复制工具
c. 都不行，直接分库

3. 分库

这一部分网上的资料也很多，但是由于自己确实经验缺乏，先不做整理了。（现在觉得，复制却不能消化的是没有意义的）

四、其他

　　我能想到的就是：慢查询的记录（当然包括一些参数的设定）；explain语句进行分析（分析出的结果表要详细了解每一列的含义）；show profile查看每一个小环节的性能消耗，可以定位到具体的一步。

写在最后：其实平时开发过程中还是应该多关注一下底层实现，虽然现在的开发框架很多，许多工具都帮我们做了底层的封装和优化，但是我们并不能因此丧失了这部分能力。如同我们可以借助单车、汽车多样出行，但当堵车的时候，还是要有徒步的能力。真正理解，才能做得更完善，我真的还有很长的路要走。