Mysql 进阶-SQL优化

Ruiven

2017 年 07 月 22 日

97次浏览

暂无评论

4773字数

进阶数据库

插入数据

INSERT 优化

批量插入
手动事务提交
主键顺序插入

大批量插入数据

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

#客户端连接服务器时加上参数 --local-infile

mysql --local-infile -u root -p

# 设置全局参数local_infile 为1 开启从本地加载文件导入数据的开关

set global local_infile = 1;

# 执行load 指令将准备好的数据，加载到表结构中

load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

导入前数据：

1,jdTmmKQlwu1,jdTmmKQlwu,jdTmmKQlwu,2020-10-13,1
2,BTJOeWjRiw2,BTJOeWjRiw,BTJOeWjRiw,2020-6-12,2
3,waQTJIIlHI3,waQTJIIlHI,waQTJIIlHI,2020-6-2,0
4,XmeFHwozIo4,XmeFHwozIo,XmeFHwozIo,2020-1-11,1
5,xRrvQSHcZn5,xRrvQSHcZn,xRrvQSHcZn,2020-10-18,2
6,gTDfGFNLEj6,gTDfGFNLEj,gTDfGFNLEj,2020-1-13,0
7,nBETIlVCle7,nBETIlVCle,nBETIlVCle,2020-9-27,1
8,vmePKKZjJU8,vmePKKZjJU,vmePKKZjJU,2020-10-20,2
9,pWjaLhJVaB9,pWjaLhJVaB,pWjaLhJVaB,2020-5-7,0
10,zimgGFPEQe10,zimgGFPEQe,zimgGFPEQe,2020-8-1,1

导入后的数据：

+----+--------------+------------+------------+------------+------+
| id | username     | password   | name       | birthday   | sex  |
+----+--------------+------------+------------+------------+------+
|  1 | jdTmmKQlwu1  | jdTmmKQlwu | jdTmmKQlwu | 2020-10-13 | 1    |
|  2 | BTJOeWjRiw2  | BTJOeWjRiw | BTJOeWjRiw | 2020-06-12 | 2    |
|  3 | waQTJIIlHI3  | waQTJIIlHI | waQTJIIlHI | 2020-06-02 | 0    |
|  4 | XmeFHwozIo4  | XmeFHwozIo | XmeFHwozIo | 2020-01-11 | 1    |
|  5 | xRrvQSHcZn5  | xRrvQSHcZn | xRrvQSHcZn | 2020-10-18 | 2    |
|  6 | gTDfGFNLEj6  | gTDfGFNLEj | gTDfGFNLEj | 2020-01-13 | 0    |
|  7 | nBETIlVCle7  | nBETIlVCle | nBETIlVCle | 2020-09-27 | 1    |
|  8 | vmePKKZjJU8  | vmePKKZjJU | vmePKKZjJU | 2020-10-20 | 2    |
|  9 | pWjaLhJVaB9  | pWjaLhJVaB | pWjaLhJVaB | 2020-05-07 | 0    |
| 10 | zimgGFPEQe10 | zimgGFPEQe | zimgGFPEQe | 2020-08-01 | 1    |
+----+--------------+------------+------------+------------+------+

mysql> load data local infile '~/Downloads/less/Mysql/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected (9.70 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

主键优化

数据组织方式

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

页分裂

页可以为空，也可以填充一半，也可以填充100% ，每页包含2-N行数据(如果一行数据多大，会行溢出)，根据主键排列

页合并

当删除一行记录时，实际上记录并没有被无力删除，只是记录被标记（flaged）为删除并且它的恐惧变得允许被其他记录声明使用。当页中删除的记录达MERGE_THRESHOLD（默认为页的50%），InnoDB开始寻找最靠近的页(前或后) 看看是否可以将两个页合并以优化空间使用。MERGE_THRESHOLD合并页的法治，可以自己设置，在创建表或者创建索引时指定。

主键的设计原则

满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
尽量不要使用UUID做主键或者是其他自然主键，如身份证
业务操作室，避免对主键的修改

ORDER BY 优化

对于mysql中的排序有两种：

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_aa 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;

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个建旭，此时需要注意联合所有在创建时的规则（ASC / DESC）
如果不可避免的出现filesort, 大数据量时，可以试单增大排序缓冲区大小sort_buffer_size（默认256k）.

 // 查看缓冲区
 show variables like 'sort_buffer_size';

GROUP BY 优化

分组操作室，可以通过索引来提供效率
分组操作室索引的使用也是满足最左前缀法则

LIMIT 优化

优化思路：一般分页查询书，通过创建覆盖索引能够比较好的提供性能，可以通过覆盖索引加子查询形式进行优化
例如：查询1900000 - 1900010 10条数据

SELECT s.* from tb_sku s, (select id from tb_sku order by id limit 1900000, 10) b where s.id = b.id

COUNT 优化

Explain SELECT COUNT(*) FROM tb_user;

MyISAM 引擎把衣柜表的总行数存在磁盘上，因此执行count(*)的时候会直接返回这个书，效率很高
InnoDB 引擎就很麻烦了，它执行count(*)的时候，需要把一行一行的从引擎里面读出来，然后累积计数；

优化思路 : 自己计数

count 的集中用法

count(主键) : InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（逐渐不可能为null）
count(字段): 没有not null 约束，InnoDB会遍历整张表把每一行字段值都取出来，返回给服务层，服务层判断是否为null, 不为null,记录累加
有not null 约束，遍历完表把每一个字段都取出来返回给服务层，直接按行进行累加
count(1)：InnoDB引擎会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字”1“进去，直接按行进行累加
count(*)：InnoDB不会吧全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

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

UPDATE优化

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

 UPDATE STUDENT SET no= '2000100100' WHERE id =1 ; // 此时update是行锁
 UPDATE STUDENT SET no= '2000100100' WHERE name='韦一笑' // 此时name没有索引所以会加表锁

Mysql 进阶-SQL优化

Ruiven • 2017 年 07 月 22 日

插入数据

INSERT 优化

批量插入
手动事务提交
主键顺序插入

大批量插入数据

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

#客户端连接服务器时加上参数 --local-infile

mysql --local-infile -u root -p

# 设置全局参数local_infile 为1 开启从本地加载文件导入数据的开关

set global local_infile = 1;

# 执行load 指令将准备好的数据，加载到表结构中

load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

导入前数据：

1,jdTmmKQlwu1,jdTmmKQlwu,jdTmmKQlwu,2020-10-13,1
2,BTJOeWjRiw2,BTJOeWjRiw,BTJOeWjRiw,2020-6-12,2
3,waQTJIIlHI3,waQTJIIlHI,waQTJIIlHI,2020-6-2,0
4,XmeFHwozIo4,XmeFHwozIo,XmeFHwozIo,2020-1-11,1
5,xRrvQSHcZn5,xRrvQSHcZn,xRrvQSHcZn,2020-10-18,2
6,gTDfGFNLEj6,gTDfGFNLEj,gTDfGFNLEj,2020-1-13,0
7,nBETIlVCle7,nBETIlVCle,nBETIlVCle,2020-9-27,1
8,vmePKKZjJU8,vmePKKZjJU,vmePKKZjJU,2020-10-20,2
9,pWjaLhJVaB9,pWjaLhJVaB,pWjaLhJVaB,2020-5-7,0
10,zimgGFPEQe10,zimgGFPEQe,zimgGFPEQe,2020-8-1,1

导入后的数据：

+----+--------------+------------+------------+------------+------+
| id | username     | password   | name       | birthday   | sex  |
+----+--------------+------------+------------+------------+------+
|  1 | jdTmmKQlwu1  | jdTmmKQlwu | jdTmmKQlwu | 2020-10-13 | 1    |
|  2 | BTJOeWjRiw2  | BTJOeWjRiw | BTJOeWjRiw | 2020-06-12 | 2    |
|  3 | waQTJIIlHI3  | waQTJIIlHI | waQTJIIlHI | 2020-06-02 | 0    |
|  4 | XmeFHwozIo4  | XmeFHwozIo | XmeFHwozIo | 2020-01-11 | 1    |
|  5 | xRrvQSHcZn5  | xRrvQSHcZn | xRrvQSHcZn | 2020-10-18 | 2    |
|  6 | gTDfGFNLEj6  | gTDfGFNLEj | gTDfGFNLEj | 2020-01-13 | 0    |
|  7 | nBETIlVCle7  | nBETIlVCle | nBETIlVCle | 2020-09-27 | 1    |
|  8 | vmePKKZjJU8  | vmePKKZjJU | vmePKKZjJU | 2020-10-20 | 2    |
|  9 | pWjaLhJVaB9  | pWjaLhJVaB | pWjaLhJVaB | 2020-05-07 | 0    |
| 10 | zimgGFPEQe10 | zimgGFPEQe | zimgGFPEQe | 2020-08-01 | 1    |
+----+--------------+------------+------------+------------+------+

mysql> load data local infile '~/Downloads/less/Mysql/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected (9.70 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

主键优化

数据组织方式

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

页分裂

页可以为空，也可以填充一半，也可以填充100% ，每页包含2-N行数据(如果一行数据多大，会行溢出)，根据主键排列

页合并

主键的设计原则

满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
尽量不要使用UUID做主键或者是其他自然主键，如身份证
业务操作室，避免对主键的修改

ORDER BY 优化

对于mysql中的排序有两种：

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_aa 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;

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个建旭，此时需要注意联合所有在创建时的规则（ASC / DESC）
如果不可避免的出现filesort, 大数据量时，可以试单增大排序缓冲区大小sort_buffer_size（默认256k）.

 // 查看缓冲区
 show variables like 'sort_buffer_size';

GROUP BY 优化

分组操作室，可以通过索引来提供效率
分组操作室索引的使用也是满足最左前缀法则

LIMIT 优化

优化思路：一般分页查询书，通过创建覆盖索引能够比较好的提供性能，可以通过覆盖索引加子查询形式进行优化
例如：查询1900000 - 1900010 10条数据

SELECT s.* from tb_sku s, (select id from tb_sku order by id limit 1900000, 10) b where s.id = b.id

COUNT 优化

Explain SELECT COUNT(*) FROM tb_user;

MyISAM 引擎把衣柜表的总行数存在磁盘上，因此执行count(*)的时候会直接返回这个书，效率很高
InnoDB 引擎就很麻烦了，它执行count(*)的时候，需要把一行一行的从引擎里面读出来，然后累积计数；

优化思路 : 自己计数

count 的集中用法

count(主键) : InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（逐渐不可能为null）
count(字段): 没有not null 约束，InnoDB会遍历整张表把每一行字段值都取出来，返回给服务层，服务层判断是否为null, 不为null,记录累加
有not null 约束，遍历完表把每一个字段都取出来返回给服务层，直接按行进行累加
count(1)：InnoDB引擎会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字”1“进去，直接按行进行累加
count(*)：InnoDB不会吧全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

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

UPDATE优化

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

 UPDATE STUDENT SET no= '2000100100' WHERE id =1 ; // 此时update是行锁
 UPDATE STUDENT SET no= '2000100100' WHERE name='韦一笑' // 此时name没有索引所以会加表锁

插入数据

INSERT 优化

大批量插入数据

主键优化

数据组织方式

页分裂

页合并

主键的设计原则

ORDER BY 优化

GROUP BY 优化

LIMIT 优化

COUNT 优化

优化思路 : 自己计数

count 的集中用法

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

UPDATE优化

发表评论 取消回复

Mysql 进阶-SQL优化

插入数据

INSERT 优化

大批量插入数据

主键优化

数据组织方式

页分裂

页合并

主键的设计原则

ORDER BY 优化

GROUP BY 优化

LIMIT 优化

COUNT 优化

优化思路 : 自己计数

count 的集中用法

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

UPDATE优化

发表评论取消回复