SQL优化

插入数据

insert语句优化：建议批量插入数据（500-1000），手动控制提交事务，主键顺序插入。

大批量数据插入使用 load指令。

顺序插入优于乱序插入。

mysql --local-infile -u root -p
# 开启从本地加载文件的开关
SET GLOBAL local_infile = 1
# 每个字段通过什么分割，每行数据通过什么分割
LOAD DATA LOCAL INFILE '文件路径' INTO TABLE tb_user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'SELECT itheima;

主键优化

数据组织方式： 在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table IOT) 。

页分裂和页合并现象

页分裂：是mysql处理插入数据的一种机制，当向一个已经满的页中插入数据时，会创建一个新的页，然后将原页中一半的数据移动到新页中，插入数据，调整树的结构。

页合并：当一个页中的数据由于删除操作，使当前页中的数据量低于某个阈值（默认为50%）时，会进行页的合并，释放空间。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

1. 满足业务的条件下，尽量降低主键的长度。因为二级索引中存储的是对应的id值，id越大占用的空间就会较多。
2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。（顺序插入效率较高）
3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
4. 业务操作时，避免对主键的修改。

order by 优化

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

using index的性能较高，尽量优化为using index。

# age 和 phone没有索引，就回出现using filesort
explain select  id,age,phone from tb_user order by age, phone;

-- 创建联合索引
create  index  idx_user_age_phone_aa  on  tb_user(age,phone);
explain select  id,age,phone from tb_user order by age; # using indexexplain select  id,age,phone from tb_user order by age , phone; 3 using index
# 会出现using index和Backward index scan
explain select  id,age,phone from tb_user order by age desc , phone desc ;

Backward index scan反向扫描索引 ：因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序 时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。

在mysql8中支持降序索引，我们也可以创建降序索引。

不遵循最左前缀法则出现using filesort.

 explain select  id,age,phone from tb_user order by phone , age; 

创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时 就会出现Using filesort。

 explain select  id,age,phone from tb_user order by age asc , phone  desc ;

升序/降序联合索引

create  index  idx_user_age_phone_ad  on  tb_user(age asc ,phone desc);explain select  id,age,phone from tb_user order by age asc , phone  desc ;

总结：

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
2. 尽量使用覆盖索引。
3. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小

# 排序缓冲区的大小，默认是256k
SHOW VARIABLES LIKE 'sort_buffer_size';

group by优化

1. 建立合适的索引提高分组的效率
2. group by满足前缀法则

# using temporary : 使用到了临时表，效率较低
explain  select  profession , count(*)  from  tb_user   group  by  profession ;
# 创建联合索引
CREATE INDEX idx_pro_age_name ON tb_user(profession,age,name);
#using index : 效率较高
explain  select  profession , count(*)  from  tb_user   group  by  profession ;
# using index,using temporary ： 不满足最左前缀法则
explain  select  age ,count(*)  from  tb_user   group  by  age ;
# using index : 满足最左前缀法则
explain  select  profession , count(*)  from  tb_user WHERE profession = '软件工程'  group  by   age ;

limit优化

limit查询令人头疼的问题就是 limit 200000,10 ，要先排序前200000的数据，但是返回200000-200010的数据，效率非常的低。

优化思路：创建覆盖索引，通过覆盖索引+子查询的形式进行优化。

# mysql8不支持这种写法(mysql8不支持子查询)
SELECT * FROM tb_user WHERE id in(SELECT id FROM tb_user ORDER BY id LIMIT 10,20);
# 使用连表查询
EXPLAIN SELECT * FROM tb_user tu , (SELECT id FROM tb_user ORDER BY id LIMIT 10,20) a WHERE a.id = tu.id;

count优化

MyISAM 引擎 ： 把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。

InnoDB 引擎：就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数 。

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数 据库进行,但是如果是带条件的count又比较麻烦了)。

用法：

count(*) ：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接 按行进行累加 。

count（字段）:如果字段为null，计数不会加1。

没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出 来，返回给服务层，服务层判断是否为null，不为null，计数累加。

有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返 回给服务层，直接按行进行累加。

count（数字m）： InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字m进去，直接按行进行累加 。

count（主键）： InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。 服务层拿到主键后，直接按行进行累加(主键不可能为null)

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽 量使用 count()。

update优化

uodate语句执行时：

更新条件如果是索引字段，那么会是行锁。

update tb_user set profession = '软件工程' where id = 1;

更新条件如果不是索引字段，那么会是表锁。

update tb_user set profession = '软件工程' where name = '张三';

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能 大大降低 ，降低了并发执行的效率。

尽量使用id去更新数据。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。