一次性需要插入多条数据时，可以通过以下途径改善性能：
-
采用批量插入，单次插入500~1000条数据。
-
手动提交事务。
-
主键顺序插入。
如果有大量数据需要插入，可以使用 load 命令。
使用 load 命令时，mysql 客户端需要使用--local-infile参数进行连接：
mysql --local-infile -u root -p
需要查看是否开启了相应的参数：
select @@local_infile;
如果没有开启，开启：
set global local_infile = 1;
创建用于导入的数据库和表：
create database itheima;
use itheima;
CREATE TABLE `tb_user` (
`id` INT(11) 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`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
通过 load 命令导入的表数据，通常使用一定规则进行分隔，比如,分隔列，\n分隔行：
head load_user_100w_sort.sql
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
使用 load 命令进行导入：
load data local infile '/home/icexmoon/download/sql/load_user_100w_sort.sql' into table `tb_user` fields terminated by ','
lines terminated by '\n';
用于测试的导入数据可以从获取。
主键优化
在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表被称作索引组织表（index organized table IOT）。
在插入数据时，可能会产生页分裂，关于页分裂的演示，可以观看这个。
在删除数据时，可能会产生页合并，关于页合并的演示，可以观看这个。
主键设计原则：
-
满足业务需求的情况下，尽量降低主键的长度（单个页可以存储更多的主键）。
-
插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键（避免可能的页分页产生的性能开销）。
-
尽量不要使用 UUID 或其它自然主键（如身份证）作为主键（无序，会频繁导致页分裂）。
-
业务操作时，避免对主键进行修改。
排序优化
使用 SQL 通过 order by 进行排序时，会以两种方式实现：
-
Using filesort：通过表索引或全表扫描读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作。
-
Using index：利用索引直接返回有序数据，不需要额外排序，执行效率高。
看示例：
mysql> explain select id,age,phone from tb_user order by age,phone;
因为缺少相应的索引，所以这里排序的执行计划中 Extra 一栏是 Using filesort。
创建可以用于该排序的索引：
create index idx_age_phone on tb_user(age,phone);
再次查看执行计划就能发现将使用索引进行排序：
索引是有序的：
show index from tb_user;
这里的 A 代表 ASC，D 表示 DESC。默认情况下索引采用升序排列（体现为 B+tree 的叶子节点将按照升序排列）。
在这种情况下，排序字段都为升序或都为降序都是可以使用索引进行排序的，比如：
explain select id,age,phone from tb_user order by age desc,phone desc;
这里的额外信息显示Backward index scan; Using index，表示依然使用索引进行排序，不过是反向遍历（从叶子节点的尾部到头部）。
需要注意的是，当字段的排序方向不同时（比如一个升序一个降序），就无法使用这种索引：
explain select id,age,phone from tb_user order by age asc,phone desc;
额外信息中的Using filesort表示此时将使用内存缓冲区进行排序。
可以创建一个符合排序要求的索引进行优化：
create index idx_age_phone_ad on tb_user(age asc,phone desc);
再次查看执行计划就能发现已经可以使用索引进行排序。
最后，需要注意的是，使用索引进行排序的前提是覆盖索引，即查询的字段（除主键外）都包含在索引字段内，否则 MySQL 就不会使用索引排序，直接使用缓冲区进行排序。
比如：
explain select id,age,phone,name from tb_user order by age asc,phone desc;
这里 name 并不包含在索引idx_age_phone_ad 中，即使使用索引进行排序，也需要通过回表查询获取 name 列的信息，所以 MySQL 在这种情况下不会使用索引排序，直接查询出结果后在缓冲区中进行排序。
就像前面说的，如果排序没有使用索引，就会使用排序缓冲区（sort buffer）进行排序，默认的排序缓冲区大小是 256 k：
mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
如果排序时数据量过大，超过了这个大小，就会通过磁盘分页文件进行排序，显然相比内存效率会降低很多，可以通过修改排序缓冲区大小避免这种情况发生。
分组优化
使用 group by 进行分组时，同样可以利用索引优化其执行性能。
在演示前需要先删除 tb_user 表中多余的索引，然后执行查询：
mysql> select profession,count(*) from tb_user group by profession;
+-----------------------------+----------+
| profession | count(*) |
+-----------------------------+----------+
| 软件工程 | 4 |
| 通讯工程 | 1 |
| 英语 | 1 |
| 工程造价 | 3 |
| 舞蹈 | 1 |
| 应用数学 | 2 |
| 化工 | 2 |
| 金属材料 | 1 |
| 机械工程及其自动化 | 1 |
| 无机非金属材料工程 | 1 |
| 会计 | 1 |
| 工业经济 | 1 |
| 国际贸易 | 1 |
| 城市规划 | 2 |
| 土木工程 | 1 |
| 城市园林 | 1 |
+-----------------------------+----------+
查看其执行计划：
explain select profession,count(*) from tb_user group by profession;
Using temporary说明没有使用索引进行分组，使用了临时表。
创建索引：
create index idx_pro_age_status on tb_user(profession,age,status);
再次查看执行计划：
Using index 表明这次使用索引完成分组。
因为是联合索引，所以用联合索引的多个字段分组也是可以使用索引的：
explain select profession,count(*) from tb_user group by profession,age;
但需要注意的是，这同样要遵循联合索引的最左前缀法则，比如：
explain select profession,count(*) from tb_user group by age,profession;
虽然从结果上看两个分组是相同的，但后者是先对 age 列进行分组，再对 profession 列进行分组，所以不能使用联合索引。
除了 group by 中分组列满足最左前缀法则，还有一种特殊情况：
select profession,age,count(*) from tb_user where profession='软件工程' group by age;
虽然 group by age 不满足联合索引的最左前缀法则，但是在 where 条件中 profession='软件工程' 决定了可以利用联合索引定位到 B+tree 上满足条件的节点，然后再按照 age 进行分组，所以这依然是可以使用索引的。
Limit 优化
通常使用 Limit 返回有限结果会很快，但如果数据集很大，比如包含 1000万数据的tb_sku表：
select * from tb_sku limit 0,10;
-- 10 rows in set (0.00 sec)
随着起始游标的增大，执行时长会增加：
mysql> select * from tb_sku limit 100000,10;
-- 10 rows in set (0.89 sec)
mysql> select * from tb_sku limit 1000000,10;
-- 10 rows in set (7.86 sec)
mysql> select * from tb_sku limit 2000000,10;
-- 10 rows in set (13.21 sec)
可以使用覆盖索引+子查询的方式进行优化：
select u.* from tb_sku u,(select id from tb_sku order by id asc limit 2000000,10) a where u.id=a.id;
-- 10 rows in set (7.15 sec)
理论上也可以使用下面的语句：
select * from tb_sku where id in (select id from tb_sku order by id asc limit 2000000,10);
但 MySQL 不支持 in 后面的自语句使用 limit：
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
count 优化
通常使用 count 获取表总记录数：
select count(*) from tb_user;
对于 MyISAM 引擎，MySQL 会记录一个总记录数，类似的查询会直接返回结果。但对于 innoDB 引擎，依然需要一条条记录进行累加以计算总记录数。
对于 InnoDB 引擎的表，如果要优化返回总记录数的速度，可以考虑使用 NOSQL 数据库（比如 Redis）自行维护相应表的总记录数。
需要注意的是，count 这类聚合函数是不会统计 null 值的，比如：
update tb_user set profession=null where id=24;
select count(profession) from tb_user;
-- 结果 23
但如果查询主键：
select count(id) from tb_user;
-- 结果 24
或者查询 count(*)：
select count(*) from tb_user;
-- 结果 24
因此 count(*) 与 count(主键) 从效果上是相同的，都是返回表的总记录数。
count 有这么几种用法：
-
count(*)，innoDB 引擎进行了优化，不会取值，服务层直接累加
-
count(主键)，遍历表，取出主键值传递给服务层后进行累加
-
count(列)，如果是非空列，取出值传递给服务层后进行累加，如果可以为空的列，取出值传递给服务层，由服务层判断是否为空，再决定是否累加。
-
count(数字)，不取值，将数字传递给服务层进行累加。
执行效率：count(*) = count(数字) > count(主键) > count(列)。
count(数字)并不一定要 count(1)：
mysql> select count(1) from tb_user;
+----------+
| count(1) |
+----------+
| 24 |
+----------+
mysql> select count(0) from tb_user;
+----------+
| count(0) |
+----------+
| 24 |
+----------+
mysql> select count(-1) from tb_user;
+-----------+
| count(-1) |
+-----------+
| 24 |
+-----------+
update 优化
innoDB 使用的是行级锁。可以通过下面的示例证明：
在第一个客户端中开启一个事务，并执行一个 update：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update course set name='Go lang' where id=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
在第二个客户端中同样开启事务，并执行 update 更新另一行数据：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update course set name='spring' where id=2;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
两个事务互不影响，都可以修改成功，因为执行 update 时锁的是不同的行。
但这有一个前提条件——更新时使用索引定位数据行并进行更新，如果不是，就不是使用行锁而是表锁。
在第一个客户端执行：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update course set name='Redis' where name='Go lang';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
此时满足name='Go lang'的是第一条数据，按理说只会锁定第一行数据。
但实际上如果在客户端二中执行：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update course set name='SQL Server' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update 语句无法执行，会一直等待第一个客户端中加的锁释放，因为第一个客户端的执行实际上加的是表锁而不是行锁。
如果为 course 表的 name 字段添加索引：
create index idx_name on course(name);
在执行类似的测试就会发现，此时使用的是行锁。
文章评论