插入数据

insert 优化

采用批量插入：尽量避免少量多次插入，每次插入都需要与数据库建立连接，消耗数据库网络资源。也要避免一次插入太多数据，建议一次插入最多不超过1000条，如果需要插入上万条记录，可以将其分割为多条insert语句。

insert into tb_test values(1,)(2,),(3,),...;

手动事务提交

MySQL会自动提交事务，如果同时执行多条SQL语句，事务的commit次数很多，可以手动提交事务，将多条SQL进行一次性commit。

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;

主键顺序插入

主键可以顺序插入和乱序插入，建议通过顺序插入（主键顺序插入速度高于乱序插入）。

# 主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
# 主键顺序插入：1 2 3 4 5 6 7 9 15 21 88 89

批量插入数据

如果一次性需要插入大批量数据（比如上百万数据），使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令直接加载表文件到数据库。

#客户端连接服务端时，加上参数--local-infile
mysql --local-infile  -u root -p

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

#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into tabletb_user fields terminated by ',' lines terminated by '\n';

主键优化

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

页分裂

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

主键顺序插入时

主键乱序插入时

• 插入50时，并不会直接插入到后面的页中，而是开辟一个新的页，重新进行页的组织，并重新设置链表指针，这就是页分裂现象。

页合并

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

主键设计原则

1. 满足业务需求的情况下，尽量降低主键的长度（一般我们有一个聚集索引和多个二级索引，如果主键过长，则二级索引中维护的数据将会占用大量的磁盘空间）。
2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键（避免出现页分裂现象）。
3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号（UUID是无序的，身份证号过长导致业务检索时耗费大量的磁盘空间）。
4. 业务操作时，避免对主键的修改（修改主键会修改表的索引结构，代价太大）。

order by 优化

MySQL中的排序有两种方式，分别是：

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

示例：

这里使用上一节我们创建的tbe_user表来进行举例说明。首先清理索引。

drop index idx_user_name on tbe_user;
drop index idx_user_phone on tbe_user;
drop index idx_user_phone_name on tbe_user;

没有索引时查询，通过filesort排序，效率是比较低的；

创建了联合索引之后查询，通过index排序，效率较高。

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

create index idx_user_age_phone on tbe_user(age,phone);
desc select id,age,phone from tbe_user order by age;
desc select id,age,phone from tbe_user order by age,phone;

反向扫描索引Backward index scan：在联合索引中，多个索引从左到右依次顺序排序，如果此时desc倒序查询，将触发反向扫描索引。

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

违背联合索引的最左前缀法则时，会导致出现using filesort。

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

联合索引在创建时，如果没有指定存储顺序，默认按照升序往后走（collation = A），先按照age进行升序排序，对于age相同的会按照phone进行升序排序。此时若按照升序查询age，再倒序查询phone，导致phone字段进行了额外的排序。

desc select id,age,phone from tbe_user order by age asc,phone desc;

此时我们再创建一个索引

create index idx_user_age_pho_ad on tbe_user(age asc,phone desc);
show index from tbe_user;

此时再执行SQL查询，可以看到走的是using index 的方式，从而规避了filesort。

desc select id,age,phone from tbe_user order by age asc,phone desc;

那么我们上面用到的两个联合索引具体是什么结构呢？

联合索引都为升序：先按照age进行升序排序，再按照phone升序排序。

联合索引为一升一降：先按照age进行升序排序，再按照phone降序排序。

索引里的数据已经有了一定的顺序，当我们进行排序查询时，如果从索引里查询的数据已经满足了排序要求，就会直接从索引里查询，当不满足要求时，则会再通过filesort进行排序。

这里还有一个前提，查询的字段覆盖索引，不需要进行额外的查询，如果没有覆盖索引，需要回表查询并在排序缓冲区中对数据进行排序。

总结

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
2. 尽量使用覆盖索引。
3. 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。如果超过排序缓冲区的大小，则会在磁盘文件中进行排序，效率大大降低，在大数据场景下，可以将这个参数上调以提升排序效率。

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

group by 优化

1、为了不干扰后面的操作，先删除掉目前的联合索引。

drop index idx_user_pro_age_sta on tbe_user;
drop index idx_user_age_phone on tbe_user;
drop index idx_user_age_pho_ad on tbe_user;
drop index idx_user_email on tbe_user;
show index from tbe_user;

对专业进行分组

• 这里using temporary，使用了临时表，效率比较低。

explain select profession,count(*) from tbe_user group by profession;

创建联合索引

• 因为分组字段建立了索引，此时group by使用的是index进行分组。

create index idx_user_pro_age_sta on tbe_user(profession,age, status);
explain select profession,count(*) from tbe_user group by profession;

根据年龄分组统计各年龄段学生数量：

• 因不满足最左前缀法则，所以通过temporary进行分组。

explain select age,count(*)from tbe_user group by age;

根据专业和年龄分组统计各专业的学生数量：

• 此时满足最左前缀法则，通过index进行分组。

explain select profession,count(*)from tb_user group by profession,age;

查询软件工程的学生并通过年龄分组：

• 这里where后跟了profession，group by后跟了age，两个条件都有，满足最左前缀法则，所以使用了索引分组。

explain select age,count(*)from tbe_user  where profession = '软件工程' group by age;

小结

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

limit 优化

limit命令的功能是分页，后面跟两个参数：起始位置,每页的数量

select count(*) from tbe_user;
select * from tbe_user limit 0,10;
select * from tbe_user limit 10,10;

在大数据的场景下进行分页查询，越靠后的页查询时间越长，比如 limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回 2000000-2000010
的记录，其他记录丢弃，查询排序的代价非常大。

优化方案

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

子查询的方式：

• 首先尝试了子查询，却发现子查询里不支持limit，所以考虑采用多表查询的方式，把子查询的结果当成一张表来进行联查。

select * from tbe_user where id in (select id from tbe_user limit 10,10);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

多表联查：

select a.* from tbe_user a,(select id from tbe_user order by id limit 10,10) b where a.id = b.id;

查看一下执行计划：

desc select a.* from tbe_user a,(select id from tbe_user order by id limit 10,10) b where a.id = b.id;

因为我这张表数据量小，看不出效果，实际在上百万行数据的表中，这样的优化可以节省将近一半的查询时间。

count 优化

count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。我们来看这样一个查询语句select count(*) from tbe_user;：

• 对于MyISAM存储引擎来说，MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*）的时候会直接返回这个数，效率很高，但在使用where条件时也会很慢；
• nnoDB引擎执行count(*）的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数，如果数据量非常庞大，这条查询语句执行会非常耗时。

对于count命令，并没有比较好的优化方案，只能通过自己计数的方式进行优化（redis）。虽然如此，count 在不同用法之间性能也有差异，所以我们对count的几种用法做一下介绍。

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

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

1. count（主键）

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

2. count（字段）

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

3. count(1)

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

4. count(*)

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

select count(*) from tbe_user;
select count(id) from tbe_user;
select count(profession) from tbe_user;
select count(1) from tbe_user;

update 优化(避免行锁升级为表锁)

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

行锁示例：两个窗口中开启事务，在窗口1中对id=1的记录做更新操作，此时在窗口2中测试发现，id=1这一行锁住了，其他行不受影响。只有窗口1中的事务commit之后，窗口2才可以操作id=1的记录。

mysql-1> begin;
mysql-2> begin;
mysql-1> update coures set name = 'javaEE' where id = 1;
mysql-2> update coures set name = 'kafka' where id = 4;
mysql-2> update coures set name = 'kafka2' where id = 1;
mysql-1> rollback;
mysql-2> rollback;

表锁示例：两个窗口中开启事务，在窗口1中对name=java的记录做更新操作，此时在窗口2中测试发现，id=4这一行锁住了，实际上整个表都锁住了，只有窗口1中的事务commit之后，窗口2才可以操作。

• 此时name这个字段没有索引，所以给整个表加了表锁；
• 在事务中where条件需要指定索引字段，这样才能添加行锁。

mysql-1> begin;
mysql-2> begin;
mysql-1> update coures set name = 'javaEE' where name = 'java';
mysql-2> update coures set name = 'kafka' where id = 4;
mysql-1> rollback;
mysql-2> rollback;

为name字段添加索引后再次测试，看到事务设置的是行锁了。

mysql> create index idx_course_name on coures;
mysql-1> begin;
mysql-2> begin;
mysql-1> update coures set name = 'javaEE' where name = 'java';
mysql-2> update coures set name = 'kafka' where id = 4;
mysql-1> rollback;
mysql-2> rollback;

我们在update数据时，where的条件要使用索引字段，避免行锁升级为表锁，降低数据库的效率。

---END