MySQL进阶 - SQL性能优化

• MySQL进阶 - SQL性能优化
• 参考资料
• 插入数据
  • insert
  • 大批量插入数据
• 主键优化
  • 页分裂
  • 页合并
  • 索引设计原则
• order by优化
• group by优化
• limit优化
• count优化
• update优化（避免行锁升级为表锁）
• 优化数据访问
  • 减少请求的数据量
  • 减少服务器端扫描的行数
• 重构查询方式
  • 切分大查询
  • 分解大连接查询

参考资料

• MySQL 8种性能优化方式 (opens new window)

插入数据

insert

普通插入：

create table tb_test(
	id int comment'用户Id',
	name varchar(50) not null comment '用户名'
) COMMENT='用户测试表';

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

1. 采用批量插入（一次插入的数据不建议超过1000条）

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

2. 手动提交事务

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;

3. 主键顺序插入

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

大批量插入数据

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

# 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
select @@local_infile;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

主键优化

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

行数据，都是存储在聚集索引的叶子节点上的。InnoDB的逻辑结构图：

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂

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

主键顺序插入:

主键乱序插入:

目前表中已有数据的索引结构(叶子节点)如下：

页合并

目前表中已有数据的索引结构(叶子节点)如下：

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

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

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定

索引设计原则

• 满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
• 尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号
• 业务操作时，避免对主键的修改

order by优化

MySQL的排序，有两种方式：

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序

2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

示例：

-- A. 数据准备

mysql> show index from tb_user;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

-- B. 执行排序SQL

mysql> explain select id,age,phone from tb_user order by age ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,age,phone from tb_user order by age, phone ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

-- 由于 age, phone 都没有索引，所以此时再排序时，出现Using filesort，排序性能较低。

-- C. 创建索引
mysql> create index idx_user_age_phone_aa on tb_user(age,phone);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- D. 创建索引后，根据age, phone进行升序排序
mysql> explain select id,age,phone from tb_user order by age ;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 37      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,age,phone from tb_user order by age, phone ;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 37      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 建立索引之后，再次进行排序查询，就由原来的Using filesort， 变为了 Using index，性能就是比较高的了。

-- E. 创建索引后，根据age, phone进行降序排序
mysql> explain select id,age,phone from tb_user order by age desc , phone desc ;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 37      | NULL |   24 |   100.00 | Backward index scan,Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 也出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序
-- 时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。

-- F. 根据phone，age进行升序排序，phone在前，age在后。
mysql> explain select id,age,phone from tb_user order by phone,age ;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 37      | NULL |   24 |   100.00 | Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

-- 排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Using filesort。

-- G. 根据age, phone进行降序一个升序，一个降序
mysql> explain select id,age,phone from tb_user order by age asc , phone desc ;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 37      | NULL |   24 |   100.00 | Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

-- 因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。
-- 为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序。

-- H. 创建联合索引(age 升序排序，phone 倒序排序)
mysql> create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show index from tb_user;
+---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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      |         |               |
| tb_user |          1 | idx_user_age_phone_aa |            1 | age         | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_user |          1 | idx_user_age_phone_aa |            2 | phone       | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
| tb_user |          1 | idx_user_age_phone_ad |            1 | age         | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_user |          1 | idx_user_age_phone_ad |            2 | phone       | D         |          24 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

-- I. 然后再次执行如下SQL
mysql> explain select id,age,phone from tb_user order by age asc , phone desc ;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 37      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意事项：降序索引，MySQL版本可能要8.0以上

总结：

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

group by优化

-- A. 数据准备，删除其他索引
mysql> show index from tb_user;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

-- A. 在没有索引的情况下，执行如下SQL，查询执行计划
mysql> 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 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

-- B. 针对于 profession ， age， status 创建一个联合索引
mysql> create index idx_user_pro_age_sta on tb_user(profession , age , status);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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 | 42      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select profession , count(*) from tb_user group by profession,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 | 42      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 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 | 42      | NULL |   24 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 发现，如果仅仅根据age分组，就会出现 Using temporary ；而如果是 根据profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作，
-- 在联合索引中，也是符合最左前缀法则的。

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

如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

一起来看看执行limit分页查询耗时对比：

mysql> select count(*) from big_data;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.49 sec)

mysql> select * from big_data limit 0,10;
+----+-------+------+---------------+
| id | name  | age  | email         |
+----+-------+------+---------------+
|  1 | test0 |   37 | test0@163.com |
|  2 | test1 |   45 | test1@163.com |
|  3 | test2 |   13 | test2@163.com |
|  4 | test3 |   30 | test3@163.com |
|  5 | test4 |   12 | test4@163.com |
|  6 | test5 |   21 | test5@163.com |
|  7 | test6 |   17 | test6@163.com |
|  8 | test7 |   23 | test7@163.com |
|  9 | test8 |   15 | test8@163.com |
| 10 | test9 |    3 | test9@163.com |
+----+-------+------+---------------+
10 rows in set (0.02 sec)

mysql> select * from big_data limit 1000000,10;
+---------+-------------+------+---------------------+
| id      | name        | age  | email               |
+---------+-------------+------+---------------------+
| 1000001 | test1000000 |    4 | test1000000@163.com |
| 1000002 | test1000001 |   27 | test1000001@163.com |
| 1000003 | test1000002 |   23 | test1000002@163.com |
| 1000004 | test1000003 |   33 | test1000003@163.com |
| 1000005 | test1000004 |   47 | test1000004@163.com |
| 1000006 | test1000005 |   35 | test1000005@163.com |
| 1000007 | test1000006 |   33 | test1000006@163.com |
| 1000008 | test1000007 |    9 | test1000007@163.com |
| 1000009 | test1000008 |   49 | test1000008@163.com |
| 1000010 | test1000009 |   18 | test1000009@163.com |
+---------+-------------+------+---------------------+
10 rows in set (0.45 sec)

mysql> select * from big_data limit 5000000,10;
+---------+-------------+------+---------------------+
| id      | name        | age  | email               |
+---------+-------------+------+---------------------+
| 5000001 | test5000000 |   47 | test5000000@163.com |
| 5000002 | test5000001 |   50 | test5000001@163.com |
| 5000003 | test5000002 |    7 | test5000002@163.com |
| 5000004 | test5000003 |   36 | test5000003@163.com |
| 5000005 | test5000004 |   10 | test5000004@163.com |
| 5000006 | test5000005 |   43 | test5000005@163.com |
| 5000007 | test5000006 |   36 | test5000006@163.com |
| 5000008 | test5000007 |   48 | test5000007@163.com |
| 5000009 | test5000008 |   31 | test5000008@163.com |
| 5000010 | test5000009 |   13 | test5000009@163.com |
+---------+-------------+------+---------------------+
10 rows in set (2.36 sec)

mysql> select * from big_data limit 9000000,10;
+---------+-------------+------+---------------------+
| id      | name        | age  | email               |
+---------+-------------+------+---------------------+
| 9000001 | test9000000 |    2 | test9000000@163.com |
| 9000002 | test9000001 |   12 | test9000001@163.com |
| 9000003 | test9000002 |    3 | test9000002@163.com |
| 9000004 | test9000003 |   30 | test9000003@163.com |
| 9000005 | test9000004 |   43 | test9000004@163.com |
| 9000006 | test9000005 |   22 | test9000005@163.com |
| 9000007 | test9000006 |   33 | test9000006@163.com |
| 9000008 | test9000007 |   48 | test9000007@163.com |
| 9000009 | test9000008 |   42 | test9000008@163.com |
| 9000010 | test9000009 |   15 | test9000009@163.com |
+---------+-------------+------+---------------------+
10 rows in set (3.77 sec)

通过测试发现越往后，分页查询效率越低，这就是分页查询的问题所在。

因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

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

例如：

-- 此语句耗时很长
mysql> select * from big_data limit 9000000,10;
+---------+-------------+------+---------------------+
| id      | name        | age  | email               |
+---------+-------------+------+---------------------+
| 9000001 | test9000000 |    2 | test9000000@163.com |
| 9000002 | test9000001 |   12 | test9000001@163.com |
| 9000003 | test9000002 |    3 | test9000002@163.com |
| 9000004 | test9000003 |   30 | test9000003@163.com |
| 9000005 | test9000004 |   43 | test9000004@163.com |
| 9000006 | test9000005 |   22 | test9000005@163.com |
| 9000007 | test9000006 |   33 | test9000006@163.com |
| 9000008 | test9000007 |   48 | test9000007@163.com |
| 9000009 | test9000008 |   42 | test9000008@163.com |
| 9000010 | test9000009 |   15 | test9000009@163.com |
+---------+-------------+------+---------------------+
10 rows in set (3.20 sec)

-- 通过覆盖索引加快速度，直接通过主键索引进行排序及查询
mysql> select id from big_data order by id limit 9000000, 10;
+---------+
| id      |
+---------+
| 9000001 |
| 9000002 |
| 9000003 |
| 9000004 |
| 9000005 |
| 9000006 |
| 9000007 |
| 9000008 |
| 9000009 |
| 9000010 |
+---------+
10 rows in set (2.18 sec)

-- 通过连表查询即可实现第一句的效果，并且能达到第二句的速度
mysql> select s.* from big_data as s, (select id from big_data order by id limit 9000000, 10) as a where s.id = a.id;
+---------+-------------+------+---------------------+
| id      | name        | age  | email               |
+---------+-------------+------+---------------------+
| 9000001 | test9000000 |    2 | test9000000@163.com |
| 9000002 | test9000001 |   12 | test9000001@163.com |
| 9000003 | test9000002 |    3 | test9000002@163.com |
| 9000004 | test9000003 |   30 | test9000003@163.com |
| 9000005 | test9000004 |   43 | test9000004@163.com |
| 9000006 | test9000005 |   22 | test9000005@163.com |
| 9000007 | test9000006 |   33 | test9000006@163.com |
| 9000008 | test9000007 |   48 | test9000007@163.com |
| 9000009 | test9000008 |   42 | test9000008@163.com |
| 9000010 | test9000009 |   15 | test9000009@163.com |
+---------+-------------+------+---------------------+
10 rows in set (2.43 sec)

count优化

在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（前提是不适用where）；
• InnoDB 在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。

要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数，如创建key-value表存储在内存或硬盘，或者是用redis

count的几种用法

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

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

各种用法的性能：

• count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）
• count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
• count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加
• count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

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

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

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

如以下两条语句： update course set name = 'javaEE' where id = 1;， 这句由于id有主键索引，所以只会锁这一行； update course set name = 'SpringBoot' where name = 'PHP' ; 这句由于name没有索引，所以会把整张表都锁住进行数据更新，解决方法是给name字段添加索引

优化数据访问

减少请求的数据量

• 只返回必要的列：最好不要使用 SELECT * 语句。
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

重构查询方式

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 减少锁竞争；
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

补充：

数据库怎么优化查询效率？

• 存储引擎选择：如果数据表需要事务处理，应该考虑使用 InnoDB，因为它完全符合 ACID 特性。如果不需要事务处理，使用 MyISAM 是比较好。
• 分表分库，主从架构。
• 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
• Update 语句，如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志
• 对于多张大数据量（这里几百条就算大了）的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，性能很差。