【MySQL学习-黑马】SQL优化

插入数据

insert优化

使用insert插入多条数据时，尽量使用批量插入，不要一条一条insert语句执行，比如：

insert into table (col1, col2, col3) values (val1, val2, val3), (val4, va5, val6) ....

手动事务提交

执行sql时，都是mysql负责自动开启事务和关闭事务。如果执行的insert语句太多，就会频繁地开启和关闭事务，导致性能下降。

因此，我们可以手动开启事务来提高insert的性能，如下面举例所示：

start transaction;
insert into tb_test values(1,"Tom"),(2,"Cat"),(3,"Jerry");
insert into tb_test values(4,"Tom"),(5,"Cat"),(6,"Jerry");
insert into tb_test values(7,"Tom"),(8,"Cat"),(9,"Jerry");
commit;

主键顺序插入

执行插入操作时，按照主键的顺序插入比乱序插入，性能更高。原因会在页分裂时讲到。

大批量插入数据

如果一次性需要插入大批量数据（超过1w），使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

load data local infile '[data_file_path]' into table '[table_name]' fields terminated by '[字段分隔符]' lines terminated by '[行数据分隔符]' ;

操作如下：

首先准备一份待插入的数据文本，待插入的数据需要有一定的格式。如下图所示，逗号隔开每个字段的数据，换行代表一行的数据：

然后客户端连接服务端，加上参数--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' ;

主键优化

数据组织方式

页分裂

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

主键顺序插入图解：

主键按序插入时，当叶子节点的数据也会按顺序插入到页里，填满了一页之后，就会开辟新的一页继续按顺序存储叶子节点。

主键乱序插入图解：

现在需要插入一条id=50的数据，想插入到1号页里，但是空间明显不足。

此时就会先开辟一个新的页空间3号，然后再找出1号页中的叶子节点一半的位置。

然后再把1号页空间后一半的叶子节点，移动到新的3号页空间，再把新插入的50也放到3号页空间。

最后重新建立页空间的链表，将1号页空间的后继指针，改为指向新建的3号页空间，再将3号页空间的后继指针指向2号页面。像这样插入一个数据，因为页空间不足，需要开辟一个新的页空间来存储新的数据，称之为页分裂。

结论：通过上述的主键顺序插入和乱序插入的图解，可以看出，如果插入数据时主键顺序乱序，容易出现页分裂，导致插入数据的效率降低。

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged) 为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD (默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

页合并图解：

现在2号空间要删除后半部分连续的4个数据，这4个数据会被标记为删除，暂时并未真正的删除，而是声明可以被覆盖。

而3号页空间的数据未占整个页空间的一半，所以就会把3号页空间的数据，覆盖到2号页空间已经被标记删除的数据上，从而实现将3号的数据合并到2号上。3号空间即使为空，也不会被回收。

 后续有数据按顺序插入，就会添加到3号空间。

主键设计原则：

• 满足业务需求的情况下，尽量降低主键的长度。因为二级索引的叶子节点存储的是id，如果id过长，二级索引过多就会占用大量的磁盘空间。
• 插入数据时，尽量选择顺序插入，选择使用AUTO_ INCREMENT自增主键。有效保证数据是主键顺序插入，防止页分裂，保障了插入数据的效率。
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。因为字符串太长，建立二级索引会占用大量的磁盘空间，而且数据的随机性，也容易出现页分裂。

order by 优化

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

简单来说，就是Using index效率高，Using filesort效率低，优化时尽量避免Using filesort的出现。
 

情况1：没有建立age的索引，并且根据age升序排序查询数据，执行以下sql:

        select id, age from tb_user order by age;

分析：通过explain查看该sql的执行计划，发现Extra参数显示 Using filesort，说明是通过排序缓存区来排的。而且没有age索引，需要全表扫描再通过缓存区排序，导致sql的执行效率较低。

情况2：建立了age索引，并且根据age升序排序查询数据，执行以下sql:

         select id, age from tb_user order by age;

分析：通过explain查看该sql的执行计划，Extra参数显示 Using index。建立age索引之后，就可以从索引中直接取出符合条件的结果，sql的执行效率高。

情况3：建立age、phone联合索引，并且根据age、phone升序排序查询数据，执行以下sql:

        select id, age, phone from tb_user order by age, phone;

分析：通过explain查看该sql的执行计划，Extra参数显示 Using index。因为查询条件符合最左前缀法则，可以使用联合索引，查询效率高。

情况4：建立age、phone联合索引，并且根据phone、age升序排序查询数据，执行以下sql:

        select id, age, phone from tb_user order by phone, age;

分析：通过explain查看该sql的执行计划，Extra参数显示 Using index 和 Using filesort。因为查询条件是先phone排序再age排序，而联合索引的建立默认是先age排序再phone排序。只有age能够使用联合索引，phone还需要额外使用缓存进行排序，查询效率低。

情况5：建立age、phone联合索引，并且根据age、phone降序排序查询数据，执行以下sql:

        select id, age, phone from tb_user order by age desc, phone desc;

分析：通过explain查看该sql的执行计划，Extra参数显示 Backward index scan 和 Using index。该sql的查询条件排序，正好是联合索引的反转，可以通过反向扫描索引，再从索引取出数据，查询效率高。

情况6：建立age、phone联合索引，并且根据age升序、phone降序排序查询数据，执行以下sql:

        select id, age, phone from tb_user order by age, phone desc;

分析：通过explain查看该sql的执行计划，Extra参数显示 Using index 和 Using filesort。原因和情况4类似，因为查询条件是age升序、phone降序，而联合索引的建立默认是先age和phone都是升序，所以只有age可以使用索引，phone则需要缓存排序，执行效率低。这种情况可以通过建立一个适合的索引解决，比如 create index idx_name on tb_user(age asc, phone desc);

情况7：建立age、phone联合索引，并且根据age、phone升序排序，并查询所有的数据，执行以下sql:

        select * from tb_user order by age, phone ;

分析：通过explain查看该sql的执行计划，Extra参数显示Using filesort。之前的6种情况都是覆盖索引，所以都可以在索引获取到数据。而该sql是需要获取所有的数据，通过联合索引获取排序之后，还需要回表查询出所有数据，再进行缓存排序，执行效率低。

order by优化原则：

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

group by 优化

在分组操作时，可以通过索引来提高效率。
 

有索引idx(a,b)，

group by a，符合最左前缀法则，可走索引；

group by b，不符合最左前缀法则，走索引但会创建临时表；

where a = 'xxx' group by b，符合最左前缀法则，可走索引；

limit 优化

现有一张1kw数据量的表，查询第2000000之后的10条数据，我们通常会使用limit 200000,10。

        select * from tb_sku limit 2000000, 10;

此时需要MySQL排序前2000010记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

优化方案：

1. 先根据id排序，查询符合条件的id：

select id from tb_sku order by id limit 2000000, 10;

2. 然后将上面的sql作为子查询，连表查出所有的数据：

select t.* from tb_sku t, (select id from tb_sku order by id limit 2000000, 10) a where t.id = a.id;

count 优化

count (主键)：

InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。

count (字段)：

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

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

count (*)：
InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

结论：

count(字段) 和 count(主键id) 都需要先取值再累加，而 count(1) 和 count(*) 只需直接累加，所以前二者的效率不如后两者的高。
所以，按照效率排序的话，count(字段) < count(主键id) < count(1) ≈ count(*)，所以尽量使用count(*)。
 

update 优化

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

比如现在要修改where id = 45的记录，进行update操作的时候InnoDB会使用行锁，只会锁住id=45这条记录，其他事务还是可以操作当前表的其他记录。

如果使用非索引的字段作为查询条件，行锁就会升级为表锁，把整个表锁住，其他事务就无法在当前表进行操作，必须要等到表锁释放，其他事务才能执行操作，这样就降低了并发性能。