1 Insert 优化

1.1 批量插入

推荐500 - 1000条，不要太多

1.2 手动提交事务

因为每次执行单条语句时，mysql会自动开启并结束，耗性能

1.3 主键顺序插入

B+Tree你懂的

1.4 使用load指令大批量插入数据

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

-- 客户端连接服务端时，加上参数--local-infile，代表需要加载本地的文件
mysql --local-infile -u root -p 

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- 执行load指令将准备好的数据，加载到表结构中
-- fields terminated by 每个字段分隔， lines terminated by 每行分隔
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

2. 主键优化

2.1 一些问题

数据组织方式

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

页分裂

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

主键乱序插入，页位置不够时，会将一部分分离到新页，新数据再加入，然后通过指针重新连接

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

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

当删除时，一页的空闲部分达到一半页大小，就会发生页合并

2.2 主键设计原则

满足业务需求的情况下，尽量降低主键的长度。

如果存在大量二级索引，那么索引节点叶子的主键存储空间加起来也很大

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

能好好利用数据页

尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

无序会乱序插入，插入难，搜索难

业务操作时，避免对主键的修改

不用多说了吧

3 Order By 优化

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

3.1 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

3.2 尽量使用覆盖索引。

3.3 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

3.4 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）。

3.5 案例

有索引，满足最左，同顺序，则Extra显示为Using Index。但都反向索引扫描（索引默认升序排）

先orderby phone违背最左前缀法则，不走索引，到age时还是会走索引

age是走索引的，但是phone desc不走，因为索引默认升序

根据orderby的顺序创建索引

create index idx_user_age_pho_ad on tb_user(age asc, phone desc);

ORDER BY的索引优化。如果一个SQL语句形如：

在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];

WHERE + ORDER BY的索引优化，形如：

建立一个联合索引(columnX,sort)来实现order by 优化。

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

4 Group By 优化

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

4.2 分组操作时，索引的使用也是满足最左前缀法则的。

4.3 案例：

满足最左前缀法则那一定会走索引，如果不满足最左前缀但满足覆盖索引会同时走索引和临时表（性能降低）

where和group by前后的字段顺序满足最左，走索引

5 Limit 优化

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

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询的形式进行优化。

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

6 Count 优化

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；

InnoDB引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路：自己计数（比如使用redis，插入一条redis的记录加 1）。

count的几种用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。

用法：count(* ) 、 count（主键)、count(字段)、count ( 1)

count(主键)

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

count(字段)

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

count(1)

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

count(*)

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

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

7 Update 优化（避免行锁升级为表锁）

复习一下：InnoDB的三大特性：事务，外键，行级锁

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

-- 都手动提交事务，name没有索引
update course set name = 'Golang' where name = 'Java'
update course set name = '垃圾' where id = 1

第二条阻塞，因为没有索引，所以会全表扫描 --> 行锁升级为表锁

加了索引，问题解决

SQL优化笔记 ｜ 青训营