SQL优化

作者: 坠尘_ae94 | 来源:发表于2021-03-08 12:38 被阅读0次

MySQL优化

体系结构

MySQL Server由以下组成：Connection Pool（连接池组件）；Management Service & Utilities（管理服务和工具组件）；SQL Interface；Optimizer；Caches & Buffers；Pluggable Storage Engines（索引依靠于存储引擎）；File System；

索引

索引：帮助MySQL高校获取数据的数据结构（有序）。

索引设计原则

以下情况推荐建立索引：

主键自动建立唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，高并发下倾向创建组合索引
查询中排序的字段，排序字段通过索引访问大幅提高排序速度
查询中统计或分组字段

同时，设计索引时可以遵循一些原则：

对查询频次较高，且数据量比较大的表建立索引；
索引字段的选择，最佳候选应当从where子句中的条件中提取；
使用唯一索引，区分度越高，使用索引的效率越高；
索引越多越不易维护；
使用短索引，索引创建后使用硬盘存储，短索引可以提升索引访问的I/O效率，可以提升总体的访问效率；
利用符合索引，对N个列组合而成的组合索引，就相当于创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

SQL性能监测

查看SQL执行频率

以下命令显示当前session中所有统计参数的值：

show status like 'Com_______';

可以查看当前数据库以什么操作为主：

mysql> show status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 10    |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.10 sec)

explain分析执行计划

explain select * from cnarea_2019 where id = 1;

image-20210305105526395

关于参数的初步解释可以查看参考一。

慢查询日志

默认关闭。

show variables like 'slow_query%';
show variables like 'long_query%';
# 临时开启
set global slow_query_log_file='/var/lib/mysql/tmp_slow.log';
set global long_query_time=1;
set global slow_query_log='ON';

image-20210305132149856

生成的文件格式如下：

image-20210305132605864

包含用户、数据库地址以及详细的SQL语句。

索引的使用

索引验证

在测试索引之前，我们先准备一张500万左右的数据表：

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  4999865 |
+----------+
1 row in set (2.51 sec)

mysql> select * from user limit 3;
+----+------------+------+------------+------------+
| id | username   | age  | address    | company    |
+----+------------+------+------------+------------+
|  1 | 88094c37-b |   54 | ecf48a5c-9 | e244831c-6 |
|  2 | 79c2f062-c |   71 | a03f8695-d | bd040bfb-1 |
|  3 | 18dd03ab-9 |   51 | 3332d698-a | 4b4fc273-a |
+----+------------+------+------------+------------+
3 rows in set (0.01 sec)

存储一些随机生成的数据。

当前表除主键外并没有索引。

mysql> select * from user where id = 3000000;
+---------+------------+------+------------+------------+
| id      | username   | age  | address    | company    |
+---------+------------+------+------------+------------+
| 3000000 | 3b7df0e5-d |   92 | 425d44f5-a | 2b508d46-0 |
+---------+------------+------+------------+------------+
1 row in set (0.00 sec)

mysql> select * from user where username = '3b7df0e5-d';
+---------+------------+------+------------+------------+
| id      | username   | age  | address    | company    |
+---------+------------+------+------------+------------+
| 3000000 | 3b7df0e5-d |   92 | 425d44f5-a | 2b508d46-0 |
+---------+------------+------+------------+------------+
1 row in set (2.93 sec)

可以看到，通过主键ID进行查询快于使用username查询，接下来我们为username添加索引。

mysql> create index ind_username on user(username);
Query OK, 0 rows affected (32.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

再进行测试：

mysql> select * from user where id = 4320000;
+---------+------------+------+------------+------------+
| id      | username   | age  | address    | company    |
+---------+------------+------+------------+------------+
| 4320000 | dce56995-b |   26 | bc64326a-e | b01d5ba2-8 |
+---------+------------+------+------------+------------+
1 row in set (0.00 sec)

mysql> select * from user where username = "dce56995-b";
+---------+------------+------+------------+------------+
| id      | username   | age  | address    | company    |
+---------+------------+------+------------+------------+
| 4320000 | dce56995-b |   26 | bc64326a-e | b01d5ba2-8 |
+---------+------------+------+------------+------------+
1 row in set (0.00 sec)

建立索引后数据查询效率与主键查询速度相当。

索引使用

删除之前的索引

mysql> drop index ind_username on user;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

为username、age、addresss创建复合索引：

mysql> create index ind_username_age_address on user(username,age,address);
Query OK, 0 rows affected (46.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

先使用explain观察SQL执行计划

mysql> explain select * from user where username = "dce56995-b" and age = 26 and company = "b01d5ba2-8"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: ind_username_age_address
          key: ind_username_age_address
      key_len: 88
          ref: const,const
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from user where username = "dce56995-b" and age = 26\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: ind_username_age_address
          key: ind_username_age_address
      key_len: 88
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from user where username = "dce56995-b"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: ind_username_age_address
          key: ind_username_age_address
      key_len: 83
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

可以看到以上三个查询条件都可以走ind_username_age_address索引。

再看以下例子

mysql> explain select * from user where age = 26 and username = "dce56995-b"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: ind_username_age_address
          key: ind_username_age_address
      key_len: 88
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

交换and左右条件该走索引依然会走。

但是如果查询未经过最左边的列时便不会走索引

mysql> explain select * from user where age = 26 and address = "dce56995-b"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4981012
     filtered: 1.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

下面总结了索引失效的可能原因。

索引失效

不符合最左匹配原则

mysql> explain select * from user where  age = 26 and company = "b01d5ba2-8"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4981012
     filtered: 1.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

复合索引中范围查询右边的列

MySQL8中测试仍会走索引

mysql> explain select * from user where username = "adb-5" and age > 64 and company = "e938fd76-e"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: range
possible_keys: ind_username_age_address
          key: ind_username_age_address
      key_len: 88
          ref: NULL
         rows: 1
     filtered: 10.00
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from user where username = "adb-5" and age = 64 and company = "e938fd76-e"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: ind_username_age_address
          key: ind_username_age_address
      key_len: 88
          ref: const,const
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

只是type发生了变化。

like查询前有‘%’，eg: "%Hello"
like使用索引如何避免失效

我测试的时候发现都会走索引。

mysql> explain select * from user where username = "%adb-5" and age = 64 and company = "e938fd76-e"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: ind_username_age_address
          key: ind_username_age_address
      key_len: 88
          ref: const,const
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from user where username = "adb-5" and age = 64 and company = "%e938fd76-e"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: ind_username_age_address
          key: ind_username_age_address
      key_len: 88
          ref: const,const
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

猜测：MySQL8对like模糊查询进行了优化。

or连接的条件，一个有索引，一个没有，则整个索引都失效

mysql> explain select * from user where username = "adb-5" or company = "Hello"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: ind_username_age_address
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4981012
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

字符串未加单引号，存在隐式数据转换可能导致索引失效

mysql> explain select * from user where username = 13\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: ind_username_age_address
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4981012
     filtered: 10.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

ERROR: 
No query specified

在索引列上进行运算操作

mysql> explain select * from user where substring(username,2,3) = "abc"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4981012
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

MySQL内部优化（MySQL觉得全表扫描会更快）

回表：简单来说就是数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据主键再次到数据块里获取数据。

常见SQL优化

主键优化

没有特别的需要的话尽量使用一个与业务无关的自增字段作为主键。

原因：1.占用空间小；2.插入搜索快；3.检索搜索快

优化insert语句

1.如果需要同时对一张表插入很多数据时，尽量使用多个值表的insert语句。

insert into user(username,age) values("Hello",3),("World",4);

2.在事务中进行数据插入。

start transaction;
insert into user(username,age) values("Hello",3);
commit;

3.主键有序插入。

优化order by语句

mysql> explain select age,username from user order by age limit 10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: NULL
          key: ind_username_age_address
      key_len: 1111
          ref: NULL
         rows: 4981014
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select age,username,company from user order by age limit 10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4981014
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

索引覆盖：所要查询的列已包含在索引中，不需要再查询主表。

Extra中为Using Index

第一条语句中的age，username都有对应的索引，而第二条语句中的company没有对应的索引。所以第一条语句做到了索引覆盖，查询效率更优。

MySQL5.x分组后会进行排序，而8则不会。要禁用排序可以用 order by null

优化嵌套查询

MySQL 4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接(JOIN) 替代。

示例，查找有角色的所有的用户信息（子查询）:

 select * from t_user where id in (select user_id from user_role);

优化后（多表联查）：

select * from t_user u, user_role ur where u.id = ur.user_id;

优化分页

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  4999867 |
+----------+
1 row in set (3.03 sec)

mysql> select * from user limit 4000000,10;
+---------+------------+------+------------+------------+
| id      | username   | age  | address    | company    |
+---------+------------+------+------------+------------+
| 4000001 | 273b8cfa-8 |   14 | 8ac53fae-8 | 58682f84-1 |
| 4000002 | e15525b4-7 |   84 | 6c462c67-5 | 86d91e7c-3 |
| 4000003 | 96db55fb-f |   15 | d64c3ca8-4 | 83c58363-1 |
| 4000004 | ed3d258a-a |   76 | d7ef91ef-9 | 1179e728-3 |
| 4000005 | c03a8321-b |   15 | 176320ef-6 | 109928fe-8 |
| 4000006 | 5ca5eba0-2 |   97 | 06d920cd-7 | 5f37f70e-a |
| 4000007 | ed5500ac-4 |   17 | a49b060b-4 | b92af423-a |
| 4000008 | 659d36ca-d |   57 | 3ec9420a-7 | 048298c2-5 |
| 4000009 | 40e965ba-1 |   96 | 778e34c3-4 | de69750e-d |
| 4000010 | 3d9146e1-0 |    1 | acc5050f-b | 59a0e1a9-6 |
+---------+------------+------+------------+------------+
10 rows in set (2.41 sec)

分页查询很慢，可以进行如下优化(多表联查)

mysql> select * from user u, (select id from user order by id limit 4000000,10) a where u.id = a.id;
+---------+------------+------+------------+------------+---------+
| id      | username   | age  | address    | company    | id      |
+---------+------------+------+------------+------------+---------+
| 4000001 | 273b8cfa-8 |   14 | 8ac53fae-8 | 58682f84-1 | 4000001 |
| 4000002 | e15525b4-7 |   84 | 6c462c67-5 | 86d91e7c-3 | 4000002 |
| 4000003 | 96db55fb-f |   15 | d64c3ca8-4 | 83c58363-1 | 4000003 |
| 4000004 | ed3d258a-a |   76 | d7ef91ef-9 | 1179e728-3 | 4000004 |
| 4000005 | c03a8321-b |   15 | 176320ef-6 | 109928fe-8 | 4000005 |
| 4000006 | 5ca5eba0-2 |   97 | 06d920cd-7 | 5f37f70e-a | 4000006 |
| 4000007 | ed5500ac-4 |   17 | a49b060b-4 | b92af423-a | 4000007 |
| 4000008 | 659d36ca-d |   57 | 3ec9420a-7 | 048298c2-5 | 4000008 |
| 4000009 | 40e965ba-1 |   96 | 778e34c3-4 | de69750e-d | 4000009 |
| 4000010 | 3d9146e1-0 |    1 | acc5050f-b | 59a0e1a9-6 | 4000010 |
+---------+------------+------+------------+------------+---------+
10 rows in set (2.24 sec)

也可以用如下方式

mysql> select * from user where id > 4000000 limit 10;
+---------+------------+------+------------+------------+
| id      | username   | age  | address    | company    |
+---------+------------+------+------------+------------+
| 4000001 | 273b8cfa-8 |   14 | 8ac53fae-8 | 58682f84-1 |
| 4000002 | e15525b4-7 |   84 | 6c462c67-5 | 86d91e7c-3 |
| 4000003 | 96db55fb-f |   15 | d64c3ca8-4 | 83c58363-1 |
| 4000004 | ed3d258a-a |   76 | d7ef91ef-9 | 1179e728-3 |
| 4000005 | c03a8321-b |   15 | 176320ef-6 | 109928fe-8 |
| 4000006 | 5ca5eba0-2 |   97 | 06d920cd-7 | 5f37f70e-a |
| 4000007 | ed5500ac-4 |   17 | a49b060b-4 | b92af423-a |
| 4000008 | 659d36ca-d |   57 | 3ec9420a-7 | 048298c2-5 |
| 4000009 | 40e965ba-1 |   96 | 778e34c3-4 | de69750e-d |
| 4000010 | 3d9146e1-0 |    1 | acc5050f-b | 59a0e1a9-6 |
+---------+------------+------+------------+------------+
10 rows in set (0.00 sec)