MySQL-数据库调优

数据库调优的步骤：

1.分析

1.1 查看系统性能参数

1
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

• Connections：连接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。
• Innodb_rows_read：Select查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的行数
• Innodb_rows_deleted：执行DELETE操作删除的行数
• Com_select：查询操作的次数。
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作的次数。
• Com_delete：删除操作的次数。

查询现在有多少条慢查询记录

1
2
3
4
5
6
7
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

1.2 统计SQL的查询成本

1
SHOW STATUS LIKE 'last_query_cost';

查询最近一次执行的SQL命令需要检索的页的个数。

2.定位执行慢的 SQL

如果我们在分析服务器状态时发现还是比较慢和卡顿，就需要找到执行比较慢的SQL。

MySQL的慢查询日志用来记录在MySQL中响应时间超过阈值的语句，然后我们找到这些慢的SQL语句有针对性的记性优化。默认MySQL数据库没有开启慢查询日志，需要手动设置，如果不是调优需要，一般不要开启，因为会影响性能。

2.1 开启慢查询日志参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show variables like '%slow_query_log%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |  //默认是关闭的
+----------------+-------+
1 row in set (0.00 sec)

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| slow_query_log      | ON                                              |
| slow_query_log_file | /var/lib/mysql/iz2zeip0loevltpebhi0txz-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)

查看阈值(执行时间超过这个阈值的SQL语句被认为是慢SQL)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> show variables like '%long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

2.2 慢查询日志分析工具

MySQL提供了日志分析工具mysqldumpslow。通过mysqldumpslow 可以查看慢查询日志

mysqldumpslow 命令的具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

1
mysqldumpslow -a -s t -t 5 /var/lib/mysql/iz2zeip0loevltpebhi0txz-slow.log

返回的就是慢SQL的语句，然后我们可以通过性能分析工具EXPLAIN来分析这个SQL语句

如何关闭慢查询日志？

MySQL服务器停止慢查询日志功能有两种方法：

①方式1：永久性方式

1
2
3
#配置文件
[mysqld]
slow_query_log=OFF

执行完后需要重启才能生效

②方式2：临时性方式

1
SET GLOBAL slow_query_log=off;

如何删除慢查询日志？

1
mysqladmin -uroot -p flush-logs slow //重建

重新生成慢查询日志文件

3.EXPLAIN分析慢SQL

EXPLAIN查看SQL语句的执行计划，并不是真正执行这个SQL语句，而是看看如果要执行它，优化器会采取什么样的步骤

MySQL 5.6.3以前只能 EXPLAIN SELECT；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE，DELETE

3.1EXPLAIN各个字段

1
2
3
EXPLAIN SELECT ...
#或者
DESCRIBE SELECT ...

相关数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE s1 (
	 id INT AUTO_INCREMENT,
	 key1 VARCHAR(100),
	 key2 INT,
	 key3 VARCHAR(100),
	 key_part1 VARCHAR(100),
	 key_part2 VARCHAR(100),
	 key_part3 VARCHAR(100),
	 common_field VARCHAR(100),
	  PRIMARY KEY (id),
	  INDEX idx_key1 (key1),
	  UNIQUE INDEX idx_key2 (key2),
	  INDEX idx_key3 (key3),
	  INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
	 id INT AUTO_INCREMENT,
	 key1 VARCHAR(100),
	 key2 INT,
	 key3 VARCHAR(100),
	 key_part1 VARCHAR(100),
	 key_part2 VARCHAR(100),
	 key_part3 VARCHAR(100),
	 common_field VARCHAR(100),
	  PRIMARY KEY (id),
	  INDEX idx_key1 (key1),
	  UNIQUE INDEX idx_key2 (key2),
	  INDEX idx_key3 (key3),
	  INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

①table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法,该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。有几个表就有几行记录结果

比如：EXPLAIN SELECT * from s1;

EXPLAIN SELECT * FROM s1 INNER JOIN s2;有两条记录结果，它的table列分别是s1、s2

②id

在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

如果有子查询，并且查询优化器没有将它优化为多表查询，一般就有多个id值

1
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

  • id如果相同，可以认为是一组，从上往下顺序执行

  • 在所有组中，id值越大，优先级越高，越先执行

  • 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

③select_type

SIMPLE

1
EXPLAIN SELECT * FROM s1;

这个语句的类型是SIMPLE，连接查询EXPLAIN SELECT * FROM s1 INNER JOIN s2;也是 SIMPLE 类型

PRIMARY、UNION、UNION RESULT

1
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

对于包含UNION或UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，最左边(或者是外层的表)的查询的select_type的值是primary，其余的小查询的select_type是UNION，使用临时表对UNION进行去重，针对该临时表的查询的select_type为UNION RESULT

SQL语句1 UNION/UNION ALL SQL语句2

两个SQL语句执行的目标表的结构必须是一样的

UNION和UNION ALL关键字都是将两个结果集合并为一个，不过UNION ALL不去重，返回所有行记录，UNION去重，会按照字段的顺序进行排序，然后去除重复的行记录，然后再返回

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> select * from student;
+----+-------+------+------+---------+
| id | stuno | name | age  | classId |
+----+-------+------+------+---------+
|  1 |     1 | 1    |    1 |       1 |
|  2 |     2 | 2    |    2 |       2 |
+----+-------+------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from student_copy;
+----+-------+------+------+---------+
| id | stuno | name | age  | classId |
+----+-------+------+------+---------+
|  1 |     1 | 1    |    1 |       1 |
|  2 |     2 | 2    |    2 |       2 |
+----+-------+------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from student union select * from student_copy;
+----+-------+------+------+---------+
| id | stuno | name | age  | classId |
+----+-------+------+------+---------+
|  1 |     1 | 1    |    1 |       1 |
|  2 |     2 | 2    |    2 |       2 |
+----+-------+------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from student union all select * from student_copy;
+----+-------+------+------+---------+
| id | stuno | name | age  | classId |
+----+-------+------+------+---------+
|  1 |     1 | 1    |    1 |       1 |
|  2 |     2 | 2    |    2 |       2 |
|  1 |     1 | 1    |    1 |       1 |
|  2 |     2 | 2    |    2 |       2 |
+----+-------+------+------+---------+
4 rows in set (0.00 sec)

SUBQUERY、DEPENDENT SUBQUERY

1
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

in后面的子查询SELECT key1 FROM s2是不相关子查询

1
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

in后面的子查询是相关子查询

不相关子查询：子查询的不需要父查询把结果传进来

相关子查询：子查询需要父表查询的某些结果才能执行

  • DEPEDENT UNION、DEPEDENT SUBQUERY

在UNION UNION ALL中如果内查询是相关子查询，就会

1
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

DERIVED

1
mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;

对派生表SELECT key1, count(*) as c FROM s1 GROUP BY key1 的查询，派生表对应的子查询type就是DERIVED

MATERIALIZED

1
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);

查询优化器将子查询SELECT key1 FROM s2进行查询物化后与外层查询进行连接查询

④partitions

分区

⑤type

MySQL针对单表的执行查询的访问方法。完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL。

system

当表中只有一条记录并且这个表的存储引擎是MyIASM或者Memory，这时的type是system

const

主键primary key 或者唯一二级索引union index列与常数进行匹配时，对单标的访问方法就是const

这个表中id是主键

eg_ref

在连接查询中，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问，则对该被驱动表的访问方法是eg_ref

从执行计划中看出，s1为驱动表，s2为被驱动表，在访问s2表的时候是通过主键的等值匹配来访问的，所以这个表的执行方法为eg_ref

join(inner join)、left join、right join的区别

ref、ref_or_null

当通过普通二级索引列与常量进行等值匹配来查询某个表，那对该表的访问方法就是ref

如果对普通二级索引进行等值匹配时，该列可以为null，则对该表的访问方法就是ref_or_null

index_merge

当对两个普通二级索引进行等值匹配，且两个索引对应的列的连接方式为or，则对该表的访问方法就是index_merge

unique_subquery

在一些包含in子查询的查询语句中，如果查询优化器决定将in子查询转化为exists子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询的执行计划的type就是unique_subquery

range

使用索引获取某些范围区间的记录，就有可能使用到range访问方法

index

使用覆盖索引，但需要扫描全部的索引记录时，该表的访问方法就是index

什么是覆盖索引？

一个索引包含了满足查询结果的数据就叫做覆盖索引，这个索引是联合索引/二级索引，即索引列+主键包含select到FROM之间需要查询的列。

假如现在有一个表t，它有3个列，c1，c2，c3，c4, 它有一个二级索引c2，主键索引c1，如果有一个语句是select * from t where c2 = 1; 会去二级索引中找，如果找到了一条行记录，就可以得到c2，c1的值，但是要返回的结果是全列，因此需要回表，找到聚簇索引中的行记录，将其返回。

如果有一个语句是select c1,c2 from t where c2 = 1; 就可以直接在二级索引中找，找到就直接返回，因为在这个索引包含了满足查询结果的数据列，不需要回表的操作，这就是覆盖索引

好处：

1.避免InnoDB表进行索引的二次查询（回表）

二级索引在叶子节点保存的是索引列和主键的行信息，在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对聚簇索引的二次查询

2.可以把随机I/O变成顺序I/O，加快查询效率

在二级索引中索引列是按顺序排的，但是回表的时候是根据主键值进行搜索的，不同的主键值他们不一定在一个页或者一个区中，因此要加载的页可能也是不同的，所以这是一个随机I/O，而覆盖索引是按顺序读取页的，是一个顺序I/O

ALL

全表扫描

1
EXPLAIN SELECT * FROM s1;

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

⑥possible_keys和key

possible_keys表示在某个查询语句中，对某个表执行单标查询时可能用到的索引有哪些，但不一定真正被查询使用，key列表示实际用到的索引有哪些，如果为NULL则没有使用索引。

⑦key_len

实际使用到的索引长度(即 字节数)

帮你检查是否充分的利用上了索引，值越大越好，主要针对联合索引。

  • varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
  • varchar(10)变长字段且不允许NULL。长度= 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
  • char(10)固定字段且允许NULL。长度= 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
  • char(10)固定字段且不允许NULL。长度= 10 * ( character set：utf8=3,gbk=2,latin1=1)
  • int且不允许为NULL。长度=4
  • int且允许为NULL。长度=5

⑧ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

⑨rows

预估的需要读取的记录数，越小说明获取的记录越可能在同一页中，进行I/O的次数就越少，效率就越高。

⑩filtered

某个表经过where搜索条件过滤后剩余记录条数的百分比

⑪Extra

用来说明一些额外信息。比如有：

No tables used

当查询语句中没有FROM子句时会提示这个信息

EXPLAIN SELECT 1;

Impossible WHERE

查询语句中的WHERE子句永远为FALSE时

EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

Using where

当全表扫描来对某个表进行查询时，并且该语句有WHERE，where子句中查询的列中有不是索引的列

EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ //key1是主键，仅仅是这个不会有Using where

Using index

即覆盖索引，当我们查询列表以及搜索条件中只包含属于某个索引的列，在使用覆盖索引时在Extra列会提示这个额外信息，不需要回表了

mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';

Using index condition

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';

即索引下推

索引条件下推（ICP，Index Condition Pushdown），是一种在存储引擎层使用索引过滤数据的优化方式。更多针对联合索引

1
2
3
4
5
//现在tuser中只有一个联合索引idx_age_name_address(age,name,address)，一个主键索引id
SELECT * FROM tuser
WHERE age = 10 
AND NAME LIKE '%张%'
AND ismale = 1;

此时用了联合索引，但仅仅是age列，因为name是模糊的，索引失效，当查询时，会首先去联合索引的B+树中查找age，查到多条行记录后，会接着筛选这几条记录中符合name like ‘%张%’条件的记录，然后将得到的记录对应的主键值进行回表，寻找匹配记录。这就是ICP
—如果没有ICP，应该是在查询时，根据联合索引，如果查询到多条记录，会直接回表，然后在主键索引的满足这些id的记录中查找符合name like ‘%张%’和ismale = 1的记录，相当于回表需要的I/O次数很可能比ICP的多，因为回表的记录数多。

ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。

默认情况下ICP是打开的，也可以通过SQL命令打开或关闭

Using filesort

mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

当排序操作ORDER BY无法使用到索引，只能在内存中或者磁盘中进行排序，Mysql把这种在内存中或者磁盘中进行排序的方式称为文件排序(filesort)，非常耗费性能，最好想办法将使用文件排序的执行方式改为使用索引进行排序，Extra=Using filesort

mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

由于在key1上有索引idx_key1，所以这个语句可以使用idx_key1索引直接取出key1的10条数据，然后再进行回表。此时显示的Extra=NULL

3.2 EXPLAIN的四种输出格式

  1. 传统格式

    explain select * from s1 order by key1 limit 10;

    输出格式就是上面一直看到的那种表格

  2. json格式

    explain format=json select * from s1 order by key1 limit 10;

    输出格式是json，比传统格式的信息更全面，包括查询成本

  3. Tree格式

    explain format=tree select * from s1 order by key1 limit 10;

    展示层次结构

  4. 可视化输出

    使用MySQL Workbench可视化查看MySQL的执行计划

4.索引优化和查询优化

4.1 索引失效的例子

  1. 不符合最佳左前缀法则

    索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

  2. 计算、函数、类型转换(自动或手动)导致索引失效

    1
    2
    3
    4
    5
    6
    7
    # 函数
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
    # 计算
    CREATE INDEX idx_sno ON student(stuno);
    EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
    # 假设name字段上设置有索引,但是由于类型转换，未使用到索引
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;#隐式的类型转换

  3. 范围条件右边的列索引失效

    1
    2
    3
    4
    5
    6
    7
    8
    9
    ALTER TABLE student DROP INDEX idx_name;
    ALTER TABLE student DROP INDEX idx_age;
    ALTER TABLE student DROP INDEX idx_age_classid;
    EXPLAIN SELECT SQL_NO_CACHE * FROM student
    WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
    # student.classId>20的右侧的student.name = 'abc'的索引就会失效
    create index idx_age_name_classid on student(age,name,classid);
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 ;
    #直接交换sql语句的位置是没有用的，需要改变联合索引的位置

  4. 不等于(!= 或者<>)索引失效

    当sql语句中有!=或者<>会出现索引失效的问题，尝试改写为等于，或采用覆盖索引

  5. is null可以使用索引，is not null无法使用索引

    1
    2
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

  6. like以通配符%开头索引失效

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

  1. OR 前后存在非索引的列，索引失效

    让OR的前后条件都具备索引，如果缺少一个就会出现索引失效

    1
    2
    # age是二级索引，classid是普通列，未使用到age索引
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

4.2 外连接和内连接的查询优化

驱动表是主表，被驱动表是从表

4.2.1 外连接

刚开始所有字段都没有加索引

1
EXPLAIN SELECT SQL_NO_CACHE * FROM name LEFT JOIN book ON type.card = book.card;

left join 的执行过程是先在type表中取一个行，然后匹配所有的book表中的数据，看哪一行数据符合name.card = book.card; 假如type表中有20行数据，book表中有30行数据，则一共要查询20*30=600次。name是驱动表，book是被驱动表。

结果是：两行表格的type都为ALL，全表扫描，没有使用任何索引。

1
2
ALTER TABLE book ADD INDEX Y ( card);  #【被驱动表】，可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM name LEFT JOIN book ON type.card = book.card;

结果是：book表对应的那一行的type属性的值是ref，使用上了二级索引，这样当驱动表选定一行数据时，可以直接从被驱动表的B+树上直接定位到目标值(logn级别)，但是驱动表无法避免全表扫描(加索引和不加索引都是)

对于外连接，name也不一定都是驱动表，因为查询优化器会将语句优化为内连接，所以谁作为驱动表不一定。

4.2.2 内连接

inner join（MySQL的查询优化器可以决定谁作为驱动表和被驱动表）

1
EXPLAIN  SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

如果name表上的card有索引，而book表上的card没有，则name是被驱动表

如果book表上的card有索引，而name表上的card没有，则book是被驱动表

如果都有索引，则MySQL会评判选择哪个表作为驱动表效率更高，就选择哪个表

如果都没有索引，则小表驱动大表。（小的结果集驱动大的结果集，经过where条件筛选出来的结果集哪一个小，哪一个表就是小表。即两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”。）

查询优化器优化后的explain的结果中，上面的是驱动表，下面是被驱动表

4.2.3 JOIN原理

  1. 索引嵌套循环连接(Index Nested-Loop Join)

假如：驱动表是t1，被驱动表是t2，被驱动表t2的字段a上有索引，join过程用上了这个索引，则inner join的执行过程是：

  1. 从表t1中读入一行数据 R；
  2. 从数据行R中，取出a字段到表t2里去查找；
  3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
  4. 重复执行步骤1到3，直到表t1的末尾循环结束。

图片来源：https://achang.blog.csdn.net/article/details/122655136

  1. 块嵌套循环连接(Blocked Nested-Loop join)

不再是逐条获取驱动表的数据，而是一块一块的获取，引用了join buffer缓冲区，将驱动表join相关的部分数据列缓存到join buffer中，被驱动表中的记录一次性和缓冲区中的比较，降低了被驱动表的访问频率

这是没有索引的情况

效率：索引嵌套循环连接 > 块嵌套循环连接

4.3 子查询优化

子查询效率比较低，因为查询优化器需要创建一个临时表，帮助筛选，结束后会再撤销临时表，比较消耗性能，最好将子查询改为join连接多表查询，这样不用建立临时表。

尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

4.4 排序优化

SQL中，在WHERE子句中最好使用索引，避免全表扫描，在ORDER BY 子句中最好使用索引，避免Filesort排序。

尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

MySQL中支持两种排序，FileSort和Index

Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。

FileSort排序则一般在内存或磁盘中进行排序，占用CPU较多，如果待排序结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

1
2
3
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
#为了去掉filesort我们可以把索引建成
CREATE INDEX idx_age_name ON student(age,NAME);

如果既有where 又有order by，可能是先过滤where中的条件，然后再进行order by，如果过滤完记录就不太多了，可能不会使用这个order by的索引，也可能是先进行order by，再where，这完全是根据查询优化器的决定。

MySQL
#Linux #数据库高级 #EXPLAIN
MySQL-数据库调优
https://vickkkyz.fun/2022/04/07/计算机/mysql/数据库调化/
作者
Vickkkyz
发布于
2022年4月7日
许可协议