【MySQL笔记】sql优化

SQL执行频率

SHOW GLOBAL STATUS LIKE 'Com_______'

结果如下：

Com_insert：插入sql执行次数
Com_delete：删除sql执行次数
Com_update：更新sql执行次数
Com_select：查询sql执行次数

insert优化

批量插入

插入多条记录，建议批量插入（不要超过1000条，大批量可以使用load指令）

insert into tb1 values(..),(..),(..)

原因分析：主要原因是多条insert合并后日志量减少了，降低了日志刷盘的数据量和频率从而提高效率；同时也能减少sql解析次数；减少了网络传输IO

手动提交事务

每条sql都会开启一个事务

begin;
insert into tb1 values(..),(..),(..)
insert into tb1 values(..),(..),(..)
commit;

主键顺序插入

如果乱序插入，可能发生页分裂

order by优化

排序字段应该创建索引
如果按多个字段排序，应该创建联合索引：create index ix_name_money on account(name,money)
如果多个字段排序，有升序、有降序，创建联合索引：create index ix_name_moneydesc on account(name asc,money desc)

group by 优化

分组字段应该创建索引

update优化

一定要根据索引字段进行更新，也就是where条件中的字段一定要加索引，否则会锁表，如下：

-- name字段没加索引，会锁表。如果name字段加了索引，只会锁一条记录
update account set money=1000 where name='fan'

InnoDB的行锁是针对索引加的锁，而不是针对记录加的锁，如果没有索引会升级成表锁

count优化

使用count(*),不要使用count(id)、count(字段)、count(1)
InnoDB对count(*)做了优化，不取值，服务层按行累加

limit优化

分页查询常规直接使用LIMIT，仅适用数据量少的情况。越往后速度越慢。
比如limit 1000000,10,MySQL需要对前100w记录进行排序，仅返回1000000-1000010十条记录，其他记录丢弃，查询排序的代价非常大。

Demo:测试了千万级数据，分页查询1000万以后的分页，用时十几秒

select * from order_shopcart order by id limit 10000000,10

利用BETWEEN..AND..

最快的方式，用时0.18秒！执行计划的type是range（部分索引）
缺点：查询字段的数据必须要连续的，如果数据不是连续的话，只能用上面的方式

select * from order_shopcart where id BETWEEN 10000000 AND 10000010 order by id;

利用覆盖索引

子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即使是比较大的 offset 查询速度也不会太差。
利用子查询的方式，把原来的基于order_shopcart的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。
方式1:

select * from order_shopcart where id>(
select ID from order_shopcart order by id limit 10000000,1) limit 10

方式2:

SELECT *
FROM (SELECT `ID` AS `ID_1` FROM `order_shopcart`  ORDER BY ID LIMIT 10000000,10) AS `t`
JOIN `order_shopcart`   ON `t`.`ID_1`=`order_shopcart`.`ID`

用时3秒多，已经快了很多