前言
日常工作中，sql语句执行效率经常成为程序的性能瓶颈，如何快速的执行数据库的增删改查则是一个合格的后端程序员必备技能，本文描述了从发现执行慢的sql到如何优化增删改查的sql语句，以及从架构本身优化查询速度的一些方向，本文是我个人的经验之谈，不一定完全正确，如有错误，还望指出
优化前先了解一些基本的MySQL存储结构和索引基础知识
在做性能优化之前，首先要对MySQL的数据存储结构要有一定的了解，只有当了解了结构，才能更清楚的知道为什么这样写sql执行会更快，为什么表结构这样设计，在同样的数据行的情况下，查询和写入效率都更高，由于MySQL常用的存储引擎是InnoDB，所以这里介绍的也是基于InnoDB的
InnoDB的存储结构
InnoDB存储引擎中，表记录都是根据主键顺序组织存放，这种存储方式的表称为索引组织表，InnoDB的逻辑结构从大到小是表空间->段->区->页->行->列。
所有数据都被逻辑的存放在表空间中，表空间由各个段组成，常见的段：数据段、索引段、回滚段。
区是由连续的页组成的，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InonoDB存储引擎一次从磁盘申请4-5个区。在默认情况下，InnoDB存储引擎的页的大小为16KB，即一个区中应有64个连续的页。从InnoDB1.0.x开始，可以通过参数KEY_BLOCK_SIZE设置页的大小。
页（block），是InnoDB磁盘管理的最小单元，其中常见的有数据页、undo页、系统页、事务数据页等。
行（ROW）InnoDB存储引擎是面向列的（row-oriented），则按行进行存放。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200。每页最少存储2行记录，用链表连接起来，否则会失去B+树的意义。数据大的行记录，如：大字符串、TEXT、BLOB对象，都是采用行溢出数据存储。不同的行格式，存储方式不同。
索引类型
索引有3个维度：数据结构纬度、物理存储纬度、逻辑维度
数据结构纬度
B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。（这个索引的结构需要重点了解一下）
哈希索引: 适合等值查询，检索效率高，一次到位。InnoDB中虽然有用到这个索引，但是是引擎内部自己用的，我们不能手动创建这个类型的索引
全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。
R-Tree索引: 用来对GIS数据类型创建SPATIAL索引
物理存储维度
聚集索引：用来确定表数据唯一性的索引，优先选择主键索引，如果没有主键索引会自动选择合适的唯一索引，如果唯一索引也没用，则会使用默认的内部表字段rowId,聚集索引的叶子节点存储的是行的数据
非聚集索引：不是聚集索引的都是非聚集索引（有点废话），在叶子节点存储的是索引本身的数据以及聚集索引的值
逻辑维度
主键索引：一种特殊的唯一索引，不允许有空值。
普通索引：MySQL中基本索引类型，允许空值和重复值
联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
唯一索引：索引列中的值必须是唯一的，但是允许为空值。
空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则
上面只是大概介绍了一下MySQL的存储引擎和索引的基本知识，要想学好sql优化，光看优化方式是不行的，还需要学习引擎和索引的相关结构、存储、执行逻辑，才能更清楚的了解优化过程中为什么要避免这么做，为什么要那么做，所以建议大家需要看看上面相关的书籍和文章。
发现慢SQL
发现慢SQL主要是通过慢查询日志和SHOW PROFILES命令，2种方法各有优劣，2种方法并不是互斥的，给我感觉反而是相互补充的，反正我实际使用中，是2种方法结合起来使用
慢查询日志
MySQL 的慢查询日志可以记录执行时间超过指定阈值的 SQL 语句，通过分析慢查询日志可以找出执行时间较长的 SQL 语句，进而进行优化。
要启用慢查询日志，需要在 MySQL 配置文件中设置相关参数，例如设置执行时间超过 3 秒的 SQL 语句会被记录到慢查询日志：
1 | slow_query_log = 1 |
这样我们定时的去数据库服务器获取慢查询日志就可以发现哪些sql执行的慢，需要优化了。但是这样并不智能，毕竟不同的表数据量不通，查询的性能指标也肯定不同，比如一个表只有10万条数据，虽然查询只花费了2秒，并没有记录到，但是明显也是需要优化的sql，而有些表数据上亿，查询用了3秒，虽然也需要优化，但是实际上本身才从sql语句上面优化的结果并不会太理想了，所以慢查询日志并不能完全的发现程序中需要优化的sql语句。注意开启慢查询日志也会因为需要记录日志而占用一定的CPU和硬盘资源，所以如果正式环境MySQL服务器资源已经紧张的情况下，需要谨慎开启
SHOW PROFILES
SHOW PROFILES是MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果，首先需要确认当前MySQL是支持的，首先在MySQL中执行
1 | SELECT @@have_profiling |
返回的是YES说明当前MySQL版本是支持的，执行
1 | SELECT @@profiling |
返回0是关闭的，返回1是开启了，如果返回0，则需要通过执行
1 | SET GLOBAL PROFILING =1 |
来开启SHOW PROFILES功能，开启后执行
1 | SHOW PROFILES; |
可以查看最近15条sql的执行情况，返回有Query_id(查询的id)，Duration（耗时），Query
(执行的sql语句)，如果想知道某个结果执行的具体耗时，确认id后，通过执行
1 | SHOW PROFILE FOR QUERY 指定id |
可以查看sql的具体执行耗时情况，借用此命令，我们可以程序中定时获取MySQL最近15条sql语句的执行情况，然后自己通过自定义规则来判定哪些sql需要优化（需要自行编程实现），通过这种方式，我们可以完全自定义每个表的执行效率规则，来找出需要优化的sql，但是这样也会有一个问题，那就是每次是获取的最近15条sql，有可能每次需要执行的sql语句都刚好没有抓取到，另外就是为了抓取更多的sql，频率太频繁本身也是对数据的一种压力
实际工作中需要将上面2种方式结合起来使用，一个根据表数据量和应该执行的效率来随机抓取需要优化的sql，一个是获取已经超过执行阀值的sql，这样能消灭系统中绝大部分sql语句性能瓶颈
查看SQL执行情况
explain （desc）
当遇到一条sql语句执行慢的缺又没有办法确定准确原因的时候，可以通过explain关键字来查看语句的执行计划，通过执行计划，我们可以确定语句命中了哪个索引，大概扫描了多少行数据，以及关联表的顺序等信息，通过desc关键字效果也是一致的
返回的结果中有id、select_type、table、partitions、type 、possible_keys、key、key_len、ref、rows、filtered、Extra
1.id ： 表示当前表的执行操作顺序，id相同，顺序从上往下，id不通，值越大越优先执行
2.select_type：查询的类型，常见的有SIMPLE、PRIMARY、UNION、SUBQUERY、DERIVED
3.table：表名（如果取了别名会是别名）
4.partitions：查询将匹配记录的分区。 对于非分区表，该值为 NULL。
5.type: 表示链接的类型，性能由好到差是顺序为：NULL->system->const->eq_ref->ref->rang->index->all
NULL：MySQL能在优化阶段分解查询语句，在执行阶段不用再去访问表或者索引。正常业务sql不会出现
system、const：MySQL对查询的某部分进行优化并把其转化成一个常量，system是const的一个特例，表示表里只有一条元组匹配时为system
eq_ref：主键或唯一键索引被连接使用，最多只会返回一条符合条件的记录
ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行
range：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。
index：扫描全索引拿到结果，一般是扫描某个二级索引，二级索引一般比较少，所以通常比ALL快一点
all：全表扫描
6.possible_keys：显示当前查询中可能用到的索引，如果为NULL则表示没有相关的索引
7.key：当前查询中实际用到的索引
8.key_len：当前命中索引里面使用的字节数（最左原则），值越接近索引本身的长度越好
9.ref：显示key列记录的索引中，表查找值时使用到的列或常量。常见的有const、字段名
10.rows：当前查询MySQL预计要读取的行数（不是实际的查询出来的结果行数，而是预计要扫描多少行才能得到正确数据，并且这个行数是预估值，实际上可能会有偏差）
11.filtered：表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比，其他条件相同的情况下，此值越接近100性能越好
12.Extra：额外信息列，常见的重要值如下：
Using index：使用覆盖索引
Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围
Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的
Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时
上面只是大概介绍了一下explain返回的各个值的基本说明，需要详细了解explain的可以查阅专门介绍explain的文章
查询语句优化的一些方向
1.尽可能的避免select *，首先如果一个字段业务根本用不到，刚好这个字段的又是一个很长的字符串，那么查询本身可能会跨页查询（不是我们业务的分页，而是MySQL底层的页），同时也增大了网络和内存开销，当然select * 并非没有好处，那就是如果加了字段只需要业务代码添加对应的属性即可使用，所以如果你的表结构小，字段又几乎是业务全部会用到的，用 *没有太大问题，如果你的表字段多，建议尽量避免
2.尽量命中索引，如果是联合索引，则尽可能的多命中字段，也就是explain中的key_len尽可能的长，同时也需要尽量避免条件中导致索引失效，关于哪些做法会导致索引失效和怎么高效的命中索引，可以查阅相关文章，内容太多，这里不做详细介绍
3.小表驱动大表，关联查询中，让优先执行的语句尽可能的是数据量小的表，并且出来的数据量也尽可能的小
4.能用union all就不用union，对于多次查询结果肯定不可能重复的情况下，尽可能的用union all,因为union会对结果去重，造成性能损失
5.提前limit，比如关联的表出来只是为了字段返回，本身不会影响主体结果的情况下，先对主体数据提前分页，分页之后再关联需要字段返回的表，而不是关联完了最后分页
6.尽可能的减少回表，非聚集索引存储的数据是聚集索引的值，也就是说如果命中了非聚集索引，并且查询的字段包含非索引字段，则需要进行回表操作，具体可以查看一些相关文章，回表优化在数据量大的时候能提升不少效率，很多时候甚至会使用手动回表来提升查询效率（通过非聚集索引获取到聚集索引的值，然后通过值查询具体数据，而不是通过MySQL自己的回表来查询）
7.避免太多表链接查询，适当的时候可以从业务代码中分多次查询，联表查询关联的表越多，性能越低下，有时候还不如多查一次数据库来的快，所以需要根据实际情况下对sql语句禁行拆分，不用非得要求一次查询返回所有数据
8.如果主键字段是没有规律的，也可以使用避开MySQL自动回表，用主键字段 IN （SELECT 主键字段 FROM 表名 条件 LIMIT 分页参数）的方式也可以提示部分性能（这个方法不一定有用，不同的表结构和数据库版本结果都可能不同，建议先实际测试过之后确认是否采用），另外这2种办法都不适合联合主键的情况
9.group by 和 order by 的字段如果在索引里面，也是能命中索引的
10.索引合并查询，当执行语句需要使用多个索引时MySQL可以使用索引合并(Index Merge)来优化查询性能，当explain中出现了Using index merge，则说明使用了索引合并来查询
11.指定索引查询，当有多个索引可以使用时，MySQL会自动估算并选择合适的索引来进行查询，但是估算结果不一定正确，如果能确定使用其他索引速度肯定比MySQL选择的快，那么也是可以再查询语句中指定索引查询的
优化索引
1.优先使用联合索引，并且如果能确定字段组合的结果是唯一的，那么可以把类型设置为唯一或者主键索引。实际业务场景中，where条件肯定不止一个字段（根据主键查询的情况除外），所以联合索引能大大的提高索引的key_len,并且时覆盖索引查询的概率增加
2.创建合适数量的索引，索引并不是越多越好，每增加一个索引，会增加写入时间损耗，并且查询时也是也会影响MySQL估算成本的
3.索引字段尽量跟业务强相关，比如高频出现的查询条件所在的字段，尽可能的在索引里面
4.索引字段顺序跟业务强相关，首先字段顺序要满足一个就是大部分的查询能尽可能的命中索引的字段，同时，索引的字段顺序又要值得到的结果越少越好，比如用户表里面的性别，虽然性别筛选频率较高，但是如果把性别放在第一个就不合适，毕竟除了男就是女或者还有个未知，这样过滤出来的数据体量还是非常大，命中索引意义也不大
插入的优化
- 主键字段最好是有顺序的，这个跟B+树的数据结构有关，如果是乱序的，在每次插入的时候页分裂的时候，耗费性能会比顺序插入更大，建议查阅一下相关资料深入了解一下
2.数据量大的情况下尽可能批量，MySQL原生支持批量插入，当我们业务数据量很大的时候，我们可以通过业务代码实现定时定量插入，比如当数据累积到500条的时候执行一次操作，当时间过了1秒还没有到500条的时候，也进行一次插入。这里需要自行控制好数量的尺度和时间尺度，当然不是所有数据都适合批量插入，并且批量插入会增加业务代码复杂性，毕竟需要除了数量和时间控制，还需要数据丢失的问题，但是如果你的数据量很大，批量插入也是无法避免的
更新和删除的优化
更新和删除的时，主要是需要尽可能的使用聚集索引的值来进行更新或者删除，也就是说最好通过主键来作为条件进行更新和删除
分页的优化
分页优化，大数据量情况下，使用limit分页会导致越到后面性能越低下，所以也需要对分页本身做一些优化，
对排序字段创建索引，每次查询传上一页的最后一条数据得对应的值回来，查询时先where对比上一页最后一条的值，再直接limit 每页大小，如果排序字段是非主键或者是可以重复的，那么还需要带上对应的主键值，排序的时候则先通过排序字段排序，再通过主键字段排序，条件也加上主键的值
表结构的优化
1.合适的字段类型：遵循能用数值用数值，字符串长度能小就尽量小
2.合适的字段：参考数据库三大范式（只是参考，并不是完全符合三大范式），如果有些字段业务使用量很小的情况下，可以考虑拆分成2个表，一个表保存常用字段，一个表保存非常用字段
3.适当的冗余:很多时候很多数据必须要关联其他表来获取数据，也可以在表中添加部分冗余字段，避免查询时的链表查询
实际数据库设计中，既要参考三大范式，又要跟三大范式冲突，要结合业务来设计，在数据库存储量、写入性能损耗和操作性能中平衡取舍，没有最好的设计，只有更合适业务的设计，所有设计都应该是为业务服务
架构的优化
1.读写分离：数据量大了之后，单机节点难免出现性能瓶颈，通过读写分离分散压力
2.分库分表：读写分离只能分摊读取压力，对于写没办法分摊（双主从也是没办法分摊的，因为最终所有数据都会写入2个主机），并且一个表数据量过于大之后，查询性能肯定直线下级，所以单库单表数据量已经达到性能临界值之后，就需要分库分表了
3.采用缓存：对于更新少，读取量很大的情况下，可以采用缓存的方式，降低对数据库的操作
4.引入搜索引擎（ES）:缓存没办法解决条件搜索，如果一个查询非常复杂，导致数据库搜索性能低下并且优化手段用尽效果也有限的情况下，可以引入ES等搜索引擎，将复杂条件的筛选放搜索引擎中执行从而加速查询速度
5.适当的归档：如果有些数据（比如一年前的数据），用的已经非常少了，那么可以考虑从数据库中移入其他表或者移入其他可以存储系统，这样可以保证对于热点数据可以快速的通过MySQL查询出来，而对于历史数据，也是可以查询的，只是速度稍微慢点