SQL优化涉及到多个方面，我们接下来主要从以下几个方面介绍：
- 插入数据时的优化。
- 主键优化。
- order by 排序的优化。
- group by 分组的优化。
- limit 分页的优化。
- count 统计的优化。
- update更新数据的优化。
- 插入数据的优化
即insert优化，我们可以从批量插入数据、手动事务提交，以及主键顺序插入等实现。
1）批量插入
# 注意此批量插入一次最多不超过1000条数据insert into tb_user values (1, "Tom"), (2, "Cat"), (3, "Jerry"),...;
2）手动事务提交
start transaction;insert into tb_user values (1, "Tom"), (2, "Cat"), (3, "Jerry");insert into tb_user values (4, "Tom"), (5, "Cat"), (6, "Jerry");insert into tb_user values (7, "Tom"), (8, "Cat"), (9, "Jerry");commit;
3）主键顺序插入
# 主键乱序插入8，1，9，10，22，81，2，4，15，89，5，7，3# 主键顺序插入，基于MySQL的组织结构，顺序插入性能更高1，2，3，4，5，7，8，9，10，15，22，81，89
4）大批量插入数据
如果一次性需要插入大批量数据，使用insert语句性能较低，此时可以使用MySQL数据库提供的load指令进行插入数据操作。操作如下：
例如，将文件a.text中的数据，通过load转为数据库存储：
# a.text1, sdfgh, qwert, qwert, 2011-01-01, 02, sdfgh, qwert, qwert, 2011-11-01, 03, sdfgh, qwert, qwert, 2011-10-01, 14, sdfgh, qwert, qwert, 2011-01-11, 25, sdfgh, qwert, qwert, 2011-09-01, 06, sdfgh, qwert, qwert, 2011-01-01, 07, sdfgh, qwert, qwert, 2011-11-01, 08, sdfgh, qwert, qwert, 2011-10-01, 19, sdfgh, qwert, qwert, 2011-01-11, 210, sdfgh, qwert, qwert, 2011-09-01, 0# 数据库存储结构id, name, password, message, date, status
具体实现语法如下：
# 客户端连接服务器时，加上参数 --local-infilemysql --local-infile -u root -p# 创建一个数据库create database ithero;# 查看是否参加成功show databases;# 进入ithero数据库use ithero;# 查看当前数据库名称select database();# 查看参数 local_infile的值select @@local_infile;# 设置全局参数 local_infile = 1, 开启从本地加载文件导入数据的开关set global local_infile=1;# 创建测试表# 注意，MySQL中，为了区分MySQL关键字与普通字符，MySQL引入了反引号CREATE TABLE `tb_user` (`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT "主键",`username` VARCHAR(50) NOT NULL COMMENT "用户名",`password` VARCHAR(50) NOT NULL COMMENT "密码",`name` VARCHAR(20) NOT NULL COMMENT "姓名",`birthday` DATE DEFAULT NULL COMMENT "生日",`sex` CHAR(1) DEFAULT NULL COMMENT "性别",PRIMARY KEY (`id`),UNIQUE KEY `unique_user_username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="测试表";# 执行load指令将准备好的数据，加载到表结构中load data local infile "/root/sql.log" into table "tb_user" fields terminated by ", " lines terminated by "\n";# 注意，主键顺序插入的性能高于乱序插入
2. 主键的优化
1）数据的组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index organized table, IOT）。
2）页分裂
页可以为空，也可以填充一半，也可以填充100%；每个页包含了2～N行数据（如果一行数据太大，会行溢出），根据主键排列。
当主键乱序插入时，会出现页分裂的现象。
即当已经存在2页（页a, 页b）数据填充满的情况下，又出现了一个中间主键(key)；那么第一页(页a)将从中间断开，后1/2数据与该中间主键(key)组合成第3个页（c），然后指针变向：由a<->b，改成 a <-> c <-> b。
3）页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除，且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE_THRESHOLD（阈值，默认为50%），InnoDB会开始寻找最近的页（前或后），看看是否可以将两个页合并以优化空间使用。
注意：MERGE_THRESHOLD即合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
4）主键的设计原则
- 满足业务需求的情况下，尽量降低主键的长度；
- 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键；
- 尽量不要使用UUID做主键，或者其他自然主键，如身份证号。(因为其大部分是无序的，且较长)；
- 业务操作时，避免对主键的修改。（修改主键代价大）
3. order by 优化
1）Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作；所有不是通过索引直接返回排序结果的排序都叫 Filesort 排序。
2）Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况叫 using index，不需要额外排序，操作效率高。
# 1. 没有创建索引时，根据age，phone进行排序explain select id, age, phone from tb_user order by age, phone;# 此时，由于没有索引，extra内展示的，即为Using filesort# 2. 创建索引create index idx_user_age_phone on tb_user(age, phone);# 根据age， phone 升序排explain select id, age, phone from tb_user order by age asc, phone asc;# 根据age， phone 将序排explain select id, age, phone from tb_user order by age desc, phone desc;# 上述2种排序，都能使用索引完成，extra内展示的，即为Using index# 3. 若根据age 升序， phone 将序排explain select id, age, phone from tb_user order by age asc, phone desc;# 此时，extra内展示的，既有Using index，也有Using filesort# 表示即使用了索引，也使用了全表扫描# 4. 我们创建age 升序 + phone 将序的索引create index idx_user_age_pho_ad on tb_user(age asc, phone desc);# 此时再次根据age 升序， phone 将序排explain select id, age, phone from tb_user order by age asc, phone desc;# 则extra内展示的，只有Using index，表示只使用了索引。
使用order by 优化的原则：
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则；
- 尽量使用覆盖索引
- 多字段排序，一个升序、一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
# 查看filesort排序时的，缓冲区大小show variables like "sort_buffer_size";
后续的优化内容，我们在下一篇中继续学习。
原创文章，作者：guozi，如若转载，请注明出处：https://www.sudun.com/ask/90803.html
