高性能MySQL —— SQL优化

插入优化

1. 批量操作
   数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率【批量插入】。
   超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作，否则会造成严重的主从延迟。或者使用load进行插入操作，即将本地文件直接加载到数据库（需要设置一下local_infile配置）。
2. 手动控制事务
   自动提交每条操作都会提交一次，并对磁盘中的日志进行同步，IO次数多；
   自动提交没法保证一批数据的数据完整性。（只能回滚一条操作）；
   可以实现更精确的锁管理。
3. 尽量按主键顺序插入

主键优化

表数据都是根据主键顺序组织存放的【主键索引是聚集索引】
页分裂：

页的节点可以为空，也可以占一半空间，也可以占满空间。非顺序插入会造成页分裂。

页删除：
innoDB采取逻辑删除的方式。当一个页中被标记删除的节点达到阈值（默认50%）。会尝试页合并，即如果逻辑上相邻的页能与当前页合并成一整个页就会合并。

1. 尽量按主键顺序插入
2. 尽量降低主键的长度【故尽量不适用uuid】
3. 避免对主键进行修改

OrderBy优化

优化原则：order by的内容应尽量符合索引顺序的正序或倒序【注要么一起倒序要么一起正序】。
否则将会 using filesort：指利用内存中的缓冲区/或磁盘对查询结果进行重排序。
因此应根据应用场景建立更细粒度的索引 xxx asc, xxx desc.

Group By优化

核心思路：尽量避免建立临时表进行group by。

以下是会建立临时表的情况，应避免：

• 如果同时使用GROUP BY和ORDER BY，但引用的列不同，那么MySQL可能会使用一个临时表。
• 查询中Select的列和GROUP BY子句中的列不完全相同，MySQL可能也会使用临时表。【取决于索引的排序情况】
• 聚合函数可能需要MySQL使用临时表来计算结果。
• 没有适当的索引。索引的排序顺序需要与group by期望的分组情况能对应上。如果顺序不能按序分类为组，则需要构建临时表。

Limit 优化

limit是实现page分页的底层原理。limit startIndex, pageSize;
当startIndex越大，效率将锐减。
优化思路：避免全文检索。或进行数据分区。
故采取覆盖索引查找相应id再去回表查询出所需信息。具体而言就是采用子查询+覆盖索引的方式。

Count 优化

 MyISAM引擎将表的总数存储在磁盘上，故无条件count(*)时效率高。
 InnoDB则是一行一行读出来，累计计数。
Count(target)统计的是所有非NULL的target

核心思想：1. 需要判断是否为null的最慢。2. 需要取数据的其次。

Update 优化

核心：对于没有索引的字段会造成锁升级为表锁；
通过begin和start transaction开始事务进行更新时，一定要注意这点。