132.【MySQL_进阶】（四）-阿里云开发者社区

(三)、SQL优化

1.SQL优化_插入数据

(1).建议批量插入

相对于单条SQL语句地插入，我们每次添加地时候都需要连接初始化等操作，这些操作也是消耗时间的。所以我们建议使用批量插入的语句可以降低这些消耗，建议批量插入的数据不超过1000条。

(2).建议手动提交事务

因为相对于自动提交事务而言，我们每次编写一个数据库语句都要进行数据的提交，这样来回的事务提交也是消耗时间的，所以我们建议手动提交事务。

start transaction';
insert into tb_user values(1,'Tom');
...
commit;

(3).建议主键顺序插入

相对于主键乱序插入，主键顺序插入我们进行遍历的时候顺序查找效率更高。

(4).大批量插入数据(三部曲)

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

将磁盘文件的信息加载到我们的MySQL中，每个字段用逗号分割且每行的最后一个字段不用加逗号但要换行。

第一步: 客户端连接服务端时

加上参数: --local-linfile

mysql --local-linfile -u root -p

第二部:设置全局local_linfile为1

设置全局参数开启从本地加载文件都如数据的开关

set global local_infile=1;

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

linux系统下需要使用 '/'。window下需要使用 '//' 指向路径

load data local infile '路径/文件名.log' into table 表名 fields terminated by ';' lines terminated by '\n';

(5).测试大批量插入数据

1.表的框架结构

create table tb_user(
  id int(4) not null auto_increment,
  username varchar(50) not null,
  password varchar(50) not null,
  name varchar(20) not null,
  birthday date default null,
  sex char(1) default null,
  primary key(id),
  unique key `unique_user_username` (username)
);

# 1. 服务端连接
mysql --local-infile -u root -p
# 2. 查看我们是否自动开启
select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
-- 3.加载test_load.sql文件的数据，每个字段,分割，每一条用换行
load data local infile '/home/test_load.sql' into table tb_user fields terminated by ',' lines terminated by '\n';
-- 4.查询是否插入成功 ⭐
select *from tb_user;
+----+-------------+-------------+----------+------------+------+
| id | username    | password    | name     | birthday   | sex  |
+----+-------------+-------------+----------+------------+------+
|  1 | sdsdsdsdsd  | sddsdsdsds  | sdsdsds  | 2001-12-17 |      |
|  2 | sdsdsdsdsd2 | sddsdsdsd2s | sdsdsds2 | 2001-12-17 |      |
+----+-------------+-------------+----------+------------+------+

中文插入不进去:

2.SQL优化_主键优化

(1).数据组织方式

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

(2).页分裂_乱序插入

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

主键乱序插入的时候，会出现页分裂。

(3).叶合并_顺序插入

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。(比如说我们常见的主键递增的时候，删除某一行之后，主键不会替换原来的主键值，而是继续累加)。

当页中删除的记录达到 50%，innodb会开始寻找最靠近的页(前或后)看看是否可以将两个页合并并以优化空间使用。

(4).主键设计原则

满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用Auto_increment自增主键。
尽量不要使用UUID做主键或者其他自然主键，如身份证号等。

3.SQL优化_order by优化

(1). order by优化

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

create index idx_user_age_phone on tb_user(age,phone);
-- 1.符合最左前缀法则: 两面都升序:Using index  ⭐(除了这个效率高)
explain select id,age,phone from tb_user order by age,phone;
-- 2.符合最左前缀法则: 前面升序,后面降序->Using index; Using filesor
explain select id,age,phone from tb_user order by age ,phone desc;
-- 3.符合最左前缀法则: 前面降序后面升序 ->Using index; Using filesor
explain select id,age,phone from tb_user order by age desc,phone;
-- 4.符合最左前缀法则: 前面和后面都降序->Using index; Using filesor
explain select id,age,phone from tb_user order by age desc,phone desc;
-- 违背最左前缀法则4种情况全是 :Using index; Using file
explain select id,age,phone from tb_user order by phone,age;
-- 再次创建排序索引
create index idx_user_age_phone_ad on tb_user(age asc,phone desc);
-- 假如再次查询，那么发现变成  Using index
explain select id,age,phone from tb_user order by age ,phone desc;

(2).order by 优化总结

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

4.SQL优化_group by优化

(1).group by介绍

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

(2).group by示列

-- 1.先把所有的索引全部删除，留一个主键索引
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_user |          0 | PRIMARY  |            1 | id          | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-- 2.查看无索引状态下 执行计划  (效率偏低Using temporary)
explain select profession,count(*) from tb_user group by profession;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
-- 3.创建联合索引 执行计划  (效率偏高⭐)
create index idx_user_pro_age_sta on tb_user(profession,age,status);
explain select profession,count(*) from tb_user group by profession;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
-- 4.未使用最左前缀法则(执行计划效率偏低)
explain select age,count(*) from tb_user group by age;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | NULL |   24 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+
-- 5. 使用最左前缀法则 (执行效率偏高⭐)
explain select age,count(*) from tb_user where profession ='软件工程' group by age;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | const |    4 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+

5.SQL优化_limit 优化

(1).limit介绍

首页=(页码-1)*10。

一个常见又非常头疼的问题就是 limit 200000,10; 此时需要MySQL排序前2000010记录，仅仅返回 2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化方案: 主键排序索引+子查询

(2).具体优化

--1.当查询大数据的时候，排序会占用很长的时间 耗时: 19.86s
 select *from tb_user limit 100000,10;
优化方案: 子查询+索引
(1)首先查询出id,根据我们的主键索引,
select id from tb_user order by id limit 10000,10;
(2).然后进行子查询的操作
select t_u.* from tb_user t_u,(select id from tb_user order by id limit 10000,10) t2_u2 where t_u.id=t2_u2.id;

6.SQL优化_count 优化

(1).count介绍

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

优化思路: redis计数器。

(2).count地几种用法

count()是一个聚合函数，对于返回地结果集，一行行地判断，如果count函数地参数不是NULL,累计值就加1，否则不加，最后返回累计值。
count(*): innodb引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
count(主键):innodb引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加。
count(字段):

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

count(1): innodb引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

效率排序: count(字段)<count(主键 id)<count(1)≈count(*),所以尽量使用count(*)。

7.SQL优化_update优化

(1).update优化_介绍

当我们更新的时候，where后面的判断条件不是索引字段，那么innodb会转变为表锁。如果判断条件是索引字段那么innodb会转化为行锁。

suoy

(2).update优化_示列

innodb支持行锁，所以能够同时对一张表的不同行进行修改。
假如我们更新的时候的判断条件是非索引字段，那么会变成表锁，锁整张表。只要当这个表锁释放后其他数据才能继续改变。

注意:我们在更新数据的时候，所以条件一定要是索引字段，并且该索引字段不能失效，如果失效行锁就会升级为表锁。

132.【MySQL_进阶】（四）