MySQL数据库部分——SQL优化

前言：此章节是关于关系型数据库MySQL的SQL优化部分讲解，可能不如其他文章讲解的SQL优化详尽，但博主会竭尽所能讲优化部分讲全并且讲的通俗易懂一些；

SQL优化

（一）、Insert优化

1. 插入优化：使用批量Insert建议500-1000条插入，如果超过了建议分割为多条Insert。

2. 建议手动提交事务

3. 如果主键不自增建议进行顺序插入

4. 如果出现一次性大批量数据插入此时不建议使用Insert，改为使用load插入，指令如下：

1. 客户端连接服务端时加上参数--local-infile

mysql --local-infile -uxxx -p

2. 设置全局参数local-infile为1，开启从本地加载文件导入数据的开关。

select @@local_infile
set global local_infile=1

3. 执行load命令将准备好的数据加载到表结构中

load data local infile '/root/xxxx.sql' into table '表名' fields terminated by ',' lines terminated by '\n'

注意：这里的SQL内结构为表的一一对应首先创建好表然后使用load导入数据，数据结构如下。

张三    zhangsan@163.com    xxx
李四    lisi@163.com        xxx

（二）、主键优化

1. 数据组织方式：在Innodb存储引擎中，表数据是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）

1. 页分裂：页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出）根据主键顺序排列。如何出现页分裂情况？主键乱序插入，页指针变动形成页分裂

2. 页合并：当删除一行记录时，实际上并没被物理删除，只是被记录标记（flaged）为删除并且它的空间变的允许被其他记录声明使用。当页中删除的记录达到MEGER_THRESHOLD（默认为页的50%），Innodb会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用，这里MEGER_THRESHOLD合并页的阈值可以自己设置，在创建表活着创建索引时指定。

2. 主键设计原则：

1. 满足业务需求的情况下，尽量降低主键的长度。

2. 插入数据时尽量选择顺序插入，或者选择自增主键。

3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

4. 业务操作时，避免对主键的修改。

（三）、order by优化

1. using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序。

2. using index： 通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

3. 优化内容：

1. 根据排序字段建立合适的索引，多字段排序的时候，也遵循最左前缀法则。

2. 尽量使用覆盖索引

3. 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）

4. 如果不可避免的出现filesort，大量数据排序时，可以适当增加排序缓冲区大小sort_buffer_size（默认256k）使用show variables like 'sort_buffer_size'查看。如果超过默认大小则进行磁盘排序，效率就会降低

（四）、group by优化

1. 在分组操作时可以通过索引提高效率，而且索引的使用也是满足最左前缀法则的。

（五）、limit 优化

1. 常见问题：limit 20000000，10 此时MySQL需要排序前面的20000010记录，仅返回20000000-20000010的记录，其他记录丢弃，查询排序的代价非常大。

2. 优化思路：在不进行分库分表操作情况下，一般分页查询时，通过创建覆盖索引就能比较好的提高性能，可以通过覆盖索引加子查询进行优化。例如：

select s.* from edu s ,(select id from edu order by limit 20000000, 10) a where s.id = a.id

（六）、count 优化

1. MyISAM引擎把一个表的总行数放在了磁盘上，因此执行count（*）的时候会直接返回这个数，效率很高；但前提为无where条件

2. Innodb引擎需要把数据一行行从引擎取出，累积计数

3. 优化思路：自己计数，例如依赖redis等

（七）、update优化

1. Innodb的行锁是根据索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行所升级为表锁

结语：以上是总结的部分SQL优化的部分，因为知识点较少所以暂且写到这里，如果您读到这里，发现文章中的一些问题，希望您能通过评论区进行指证。