MySQL数据库学习笔记 --- SQL优化

SQL优化

---

---

插入数据

• insert优化

  # 一条语言只插入一条数据
  insert into tb_test values (1,'Tom');
  insert into tb_test values (2,'Jerry');
  insert into tb_test values (3,'Spike');
  ...
  

  • 第一种优化方式：批量插入

  # 应用insert一般最多插入上万条数据。
  insert into tb_test values(1,'Tom'),(2,'Jerry'),(3,'Spike');
  

  • 第二种优化方式：手动提交事务

  start transaction;
  insert into tb_test values(1,'Tom'),(2,'Jerry'),(3,'Spike');
  insert into tb_test values(4,'Cat'),(2,'Mouse'),(3,'Dog');
  commit;
  

  • 主键顺序插入（性能更高）

  主键乱序插入：8 1 9 21 88 2 4 15 89
  主键顺序插入：1 2 4 8 9 15 21 88 89
  

• 大批量插入数据
     如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

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

主键优化

• 数据组织方式
    在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(Index Organized Table, IOT)。
• 页分裂
    页可以为空，可以填充一半，也可以填充100%。每个页至少包含2行数据（如果一行数据比较大，则会产生行溢出），根据主键排列。
  • 主键顺序插入
    
  • 主键乱序插入
    
• 页合并
    以InnoDB存储引擎为例，删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除，且它的空间允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%，在创建表或者创建索引时指定），InnoDB会寻找临近页将两个页进行合并以优化空间使用。
  
• 主键设计原则
  • 满足业务需求的情况下，尽量降低主键的长度。 — 二级索引不唯一，主键长度越小占用页空间越小。
  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  • 尽量不要使用UUID做主键或者其它自然主键，如身份证号。 — 产生页分裂且占用页空间较大。
  • 业务操作时，避免修改主键。

order by优化

• Using filesort：通过表的索引和全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
• Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

# 没有创建索引时，根据age, phone排序
explain select id,age,phone from tb_user order by age,phone;
# 创建索引
create index idx_user_age_phone on tb_user(age, phone);
# 创建索引后，根据age, phone进行升序排序
explain select id,age,phone from tb_user order by age, phone;
# 创建索引后，根据age, phone进行降序排序
explain select id,age,phone from tb_user order by age desc, phone desc;
# 创建索引，根据age升序排列，根据phone降序排列
create index idx_user_age_phone on tb_user(age asc, phone desc);

• 总结：
  • 根据排序字段建立合适的索引，多字段排序时，遵循最左前缀法则
  • 尽量使用覆盖索引
  • 多字段排序，一个升序一个降序，此时需要注意联合索引创建时的规则
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256KB）。

group by优化

# 删除目前的联合索引 idx_user_pro_age_sta
drop index idx_user_pro_age_sta on tb_user;
# 执行分组操作，根据profession字段分组
explain select profession, count(*) from tb_user group by profession;
# 创建索引
create index idx_user_pro_age_sta on tb_user(profession, age, status);
# 执行分组操作，根据profession字段分组
explain select profession, count(*) from tb_user group by profession;
# 执行分组操作，根据profession字段分组
explain select profession, count(*) from tb_user group by profession, age;

  在分组操作中，我们需要通过以下两点优化：

  1. 通过索引提高效率
  2. 索引的使用满足最左前缀法则

  思考：为什么这句SELECT会出现Using index？欢迎在评论区讨论

limit优化

  在limit操作中，一个常见的问题是limit 1000000,10，此时需要MySQL排序前1000010条记录，但是仅仅返回第1000001-1000010条记录，其他记录丢弃，查询排序的代价非常大。

  优化思路：一般分页查询时，可以通过“覆盖索引+子查询”的方式进行优化，提高性能。

count优化

explain select count(*) from tb_user;

  MyISAM引擎把一个表的总行数存在磁盘里，因此执行 count(*) 的时候会直接返回这个数，效率很高。
  InnoDB引擎执行 count(*) 的时候，需要把数据一行一行地从引擎里读出来，然后累计计数。

• count的几种用法
    count()是一个聚合函数，对于返回的结果集一行行进行判断，如果count函数的参数不是NULL则计数加1，否则不加，最后返回累计值。
  • count(主键）
      InnoDB引擎会遍历整张表，把每一行主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行计数（主键不可能为null）。
  • count(字段)
    • 没有not null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不是null则计数+1。
    • 有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行计数。
  • count(1)
      InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行计数。
  • count(*)
      InnoDB引擎并不会把全部字段取出来，而是专门做了优化——不取值，服务层直接按行进行计数。

按照效率排序：count(*)≈count(1)>count(主键id)>count(字段)，因此尽量使用count(*)。

update优化

# 建立事务测试
update student set no = '2000100100' where id = 1;
update student set no = '2000100105' where name = '韦一笑';

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