MySQL进阶：SQL优化

SQL优化

插入数据

1. 插入多条数据，可以采用批量操作（不建议超过1000条， 500到1000是比较合适的）。超过的话，分成多条批量插入语句。

   INSERT INTO tabel_name VALUES(1, 'Tom'),(2, 'Jack'),(3, 'Bob');
   

2. 手动提交事物, MySQL默认自动提交事物，会频繁开启事物并提交。

3. 主键顺序插入。顺序插入的性能高于乱序插入。（主键优化）

大批量插入数据，不建议使用insert了，可以采用MySQL提供的load指令。
load: 本地文件---->数据库

# 客户端连接时，加上参数--local-infile
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地文件导入数据的开关
set local_infile=1;
# 执行load指令，将准备好的数据加载到表结构中
load data local infile '/data_path' into table table_name fields terminated by ',' lines terminated by '\n';

load指令插入100万条数据16s，而用insert十多分钟。

主键优化

• InnoDB引擎按照主键顺序存放数据（聚集索引：主键索引）。插入数据可以用AUTO_INCREMENT自增主键。

顺序插入，构建B+树代价小，而乱序插入，会发生很多额外的页分裂操作。

页分裂： 数据乱序插入的话，当前插入数据所应插入的位置空间不足，则需开辟一个新的空页，找到前一个数据页50%的位置，将后半的数据移到新的空数据页，然后插入新数据到新的数据页，然后调整指针，把新页放到正确的位置。
页合并： 删除数据行时，不会真正的从磁盘删除，而是标志位标志为删除，并且它空间可被其他记录使用。当页中删除的记录数达到MERGE_THRESHOLD(默认页的50%)时，InnoDB会从邻近的页查找是否可以合并的页，从而优化空间的使用。

• 满足业务需求情况下，尽量减小主键长度。（节省磁盘空间，提高磁盘IO效率）

• 尽量不使用UUID做主键或其他自然主键，如身份证号等（它们往往是无序的）。

• 业务操作时，尽量避免对主键的修改。

order by优化

using filesort: 需要通过遍历索引或全表扫描获取符合条件的数据，然后将数据放入sort buffer中进行排序，凡是没有通过索引获取排序结果的排序都叫filesort排序。
using index：直接读取有序索引，获取排序结果。

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

• 尽量使用覆盖索引。如果不是覆盖索引，会回表查询数据，然后在排序缓冲区对数据进行排序。

• 多字段排序，一个升序一个降序，此时需注意创建联合索引时的规则。

  create index idx_name on tb_name(col1 asc, col2 desc);
  

• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）。

group by优化

• 通过建立适当的索引来提升分组效率。
• 分组使用索引也满足最左前缀法则。

limit优化

当limit起始位置很大时是比较耗时的，例如：

select * from table limit(2000000, 10);

这条语句需要对前2000010条数据先进行排序，然后再返回倒数十行。

优化：
采用覆盖索引+子查询，比如先查id，然后将查询结果作为一个临时表，联结查询。（limit在子查询里不支持）。

count优化

select count(*) from table;

MyISAM引擎会存放一个表的总行数，所以执行count(*)时可以直接返回这个值，前提是没有where条件。
而InnoDB则必须一行一行地把数据读取出来，然后累计行数返回。

优化： 目前没有直接的优化策略，但是可以自行进行计数，比如利用哈希表进行记录。

count的几种用法：
count(*), count(主键), count(字段)，count(某一个数)。

count不计数NULL值。

count(主键)：InnoDB引擎会把每一行的主键id取出来，然后交给服务层进行累加计数，此时不用判断NULL值，主键不可能为NULL。
count(字段)：
取出相应字段值，交给服务层进行计数，如果该字段没有not NULL约束，则要判断是否为NULL，有not NULL约束，则不用判断。
count(1):遍历每一行，但不取值，把得到的行交给服务层，服务层往每一行里添加数字‘1’，然后进行计数。
count(*): InnoDB对count(*)进行了优化，它不会取出所有字段值，而是服务层直接按行进行累加。

因此效率排序
count(字段) < count(主键) < count(1) ≈ count(*)

update优化

InnoDB的行锁是针对索引加的锁，而不是对记录加锁，因此如果索引失效，则行锁升级为表锁。

优化：更新操作时，尽量根据索引字段来锁定要更新的行，这样InnoDB加的就是行级锁，如果不用索引字段，则会加表锁。