前言
之前已经简单介绍了 MySQL 的优化步骤, 那么接下来自然而是就是常用的 SQL 优化, 比如 inseer,group by 等常用 SQL 的优化, 会涉及 SQL 语句内部细节 (这正是我缺乏的). 最后希望自己能记录完成的一套 MySQL 优化博文!
注: 其中部分我并没有全部实验 (并不代表是错的), 这里只相当于记录下, 接下来会慢慢补充!
参考资料:深入浅出 MySQL
1, 大批量插入数据优化
(1) 对于 MyISAM 存储引擎的表, 可以使用: DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新.
- ALTER TABLE tbl_name DISABLE KEYS;
- loading the data
- ALTER TABLE tbl_name ENABLE KEYS;
(2) 对于 InnoDB 引擎, 有以下几种优化措施:
导入的数据按照主键的顺序保存: 这是因为 InnoDB 引擎表示按照主键顺序保存的, 如果能将插入的数据提前按照排序好自然能省去很多时间.
比如 bulk_insert.txt 文件是以表 user 主键的顺序存储的, 导入的时间为 15.23 秒
- mysql> load data infile 'mysql/bulk_insert.txt' into table user;
- Query OK, 126732 rows affected (15.23 sec)
- Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0
没有按照主键排序的话, 时间为: 26.54 秒
- mysql> load data infile 'mysql/bulk_insert.txt' into table user;
- Query OK, 126732 rows affected (26.54 sec)
- Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0
导入数据前执行 SET UNIQUE_CHECKS=0, 关闭唯一性校验, 带导入之后再打开设置为 1: 校验会消耗时间, 在数据量大的情况下需要考虑.
导入前设置 SET AUTOCOMMIT=0, 关闭自动提交, 导入后结束再设置为 1: 这是因为自动提交会消耗部分时间与资源, 虽然消耗不是很大, 但是在数据量大的情况下还是得考虑.
2,INSERT 的优化
(1) 尽量使用多个值表的 INSERT 语句, 这种方式将大大缩减客户端与数据库之间的连接, 关闭等消耗.(同一客户的情况下), 即:
INSERT INTO tablename values(1,2),(1,3),(1,4)
实验: 插入 8 条数据到 user 表中 (使用 navicat 客户端工具)
- insert into user values(1,'test',replace(uuid(),'-',''));
- insert into user values(2,'test',replace(uuid(),'-',''));
- insert into user values(3,'test',replace(uuid(),'-',''));
- insert into user values(4,'test',replace(uuid(),'-',''));
- insert into user values(5,'test',replace(uuid(),'-',''));
- insert into user values(6,'test',replace(uuid(),'-',''));
- insert into user values(7,'test',replace(uuid(),'-',''));
- insert into user values(8,'test',replace(uuid(),'-',''));
得到反馈:
[SQL] insert into user values(1,'test',replace(uuid(),'-',''));
受影响的行: 1
时间: 0.033s
- [SQL]
- insert into user values(2,'test',replace(uuid(),'-',''));
受影响的行: 1
时间: 0.034s
- [SQL]
- insert into user values(3,'test',replace(uuid(),'-',''));
受影响的行: 1
时间: 0.056s
- [SQL]
- insert into user values(4,'test',replace(uuid(),'-',''));
受影响的行: 1
时间: 0.008s
- [SQL]
- insert into user values(5,'test',replace(uuid(),'-',''));
受影响的行: 1
时间: 0.008s
- [SQL]
- insert into user values(6,'test',replace(uuid(),'-',''));
受影响的行: 1
时间: 0.024s
- [SQL]
- insert into user values(7,'test',replace(uuid(),'-',''));
受影响的行: 1
时间: 0.004s
- [SQL]
- insert into user values(8,'test',replace(uuid(),'-',''));
受影响的行: 1
时间: 0.004s
总共的时间为 0.171 秒, 接下来使用多值表形式:
- insert into user values
- (9,'test',replace(uuid(),'-','')),
- (10,'test',replace(uuid(),'-','')),
- (11,'test',replace(uuid(),'-','')),
- (12,'test',replace(uuid(),'-','')),
- (13,'test',replace(uuid(),'-','')),
- (14,'test',replace(uuid(),'-','')),
- (15,'test',replace(uuid(),'-','')),
- (16,'test',replace(uuid(),'-',''));
得到反馈:
- [SQL] insert into user values
- (9,'test',replace(uuid(),'-','')),
- (10,'test',replace(uuid(),'-','')),
- (11,'test',replace(uuid(),'-','')),
- (12,'test',replace(uuid(),'-','')),
- (13,'test',replace(uuid(),'-','')),
- (14,'test',replace(uuid(),'-','')),
- (15,'test',replace(uuid(),'-','')),
- (16,'test',replace(uuid(),'-',''));
受影响的行: 8
时间: 0.038s
得到时间为 0.038, 这样一来可以很明显节约时间优化 SQL
(2) 如果在不同客户端插入很多行, 可使用 INSERT DELAYED 语句得到更高的速度, DELLAYED 含义是让 INSERT 语句马上执行, 其实数据都被放在内存的队列中. 并没有真正写入磁盘. LOW_PRIORITY 刚好相反.
(3) 将索引文件和数据文件分在不同的磁盘上存放 (InnoDB 引擎是在同一个表空间的).
(4) 如果批量插入, 则可以增加 bluk_insert_buffer_size 变量值提供速度 (只对 MyISAM 有用)
(5) 当从一个文本文件装载一个表时, 使用 LOAD DATA INFILE, 通常比 INSERT 语句快 20 倍.
3,GROUP BY 的优化
在默认情况下, MySQL 中的 GROUP BY 语句会对其后出现的字段进行默认排序 (非主键情况), 就好比我们使用 ORDER BY col1,col2,col3... 所以我们在后面跟上具有相同列 (与 GROUP BY 后出现的 col1,col2,col3... 相同)ORDER BY 子句并没有影响该 SQL 的实际执行性能.
那么就会有这样的情况出现, 我们对查询到的结果是否已经排序不在乎时, 可以使用 ORDER BY NULL 禁止排序达到优化目的. 下面使用 EXPLAIN 命令分析 SQL.
在 user_1 中执行 select id, sum(money) form user_1 group by name 时, 会默认排序 (注意 group by 后的 column 是非 index 才会体现 group by 的排序, 如果是 primary key, 那之前说过了 InnoDB 默认是按照主键 index 排好序的)
- mysql> select*from user_1;
- +----+----------+-------+
- | id | name | money |
- +----+----------+-------+
- | 1 | Zhangsan | 32 |
- | 2 | Lisi | 65 |
- | 3 | Wangwu | 44 |
- | 4 | Lijian | 100 |
- +----+----------+-------+
- 4 rows in set
不禁止排序, 即不使用 ORDER BY NULL 时: 有明显的 Using filesort.
当使用 ORDER BY NULL 禁止排序后, Using filesort 不存在
未完待续....................................
来源: https://www.cnblogs.com/jian0110/p/9410981.html