MySQL 面试题解析

CHAR、VARCHAR 的区别是什么？

• CHAR 的特点
  • CHAR 表示定长字符串，长度是固定的；
  • 如果插入数据的长度小于 CHAR 的固定长度时，则用空格填充；
  • 因为长度固定，所以存取速度要比 VARCHAR 快很多，甚至能快 50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
  • 对于 CHAR 来说，最多能存放的字符个数为 255，和编码无关。
• VARCHAR 的特点
  • VARCHAR 表示可变长字符串，长度是可变的；
  • 插入的数据是多长，就按照多长来存储；
  • VARCHAR 在存取方面与 CHAR 相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
  • 对于 VARCHAR 来说，最多能存放的字符个数为 65532。

总之，结合性能角度（CHAR 更快）和节省磁盘空间角度（VARCHAR 更小），具体情况还需具体来设计数据库才是妥当的做法。

DELETE、TRUNCATE 与 DROP 的区别是什么？

• DELETE 命令从一个表中删除某一行，或多行；
• TRUNCATE 命令永久地从表中删除每一行。
• DROP 命令是删除表和表中所有数据；

	DELETE	TRUNCATE	DROP
类型	属于 DML	属于 DDL	属于 DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据	表结构还在，删除表中的所有数据	从数据库中删除表、所有的数据行、索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

什么是触发器，MySQL 中都有哪些触发器？

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。

在 MySQL 数据库中有如下六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

FLOAT 和 DOUBLE 的区别是什么？

• FLOAT 类型数据可以存储至多 8 位十进制数，并在内存中占 4 字节。
• DOUBLE 类型数据可以存储至多 18 位十进制数，并在内存中占 8 字节。

如何在 MySQL 中获取当前日期？

SELECT CURRENT_DATE();

如何查询第 n 高的工资？

SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT n-1, 1

请写出下面 MySQL 数据类型表达的意义（INT(0)、CHAR(16)、VARCHAR(16)、DATETIME、TEXT）

知识点分析

此题考察的是 MySQL 数据类型。MySQL 数据类型属于 MySQL 数据库基础，由此延伸出的知识点还包括如下内容：

• MySQL 基础操作
• MySQL 存储引擎
• MySQL 锁机制
• MySQL 事务处理、存储过程、触发器

数据类型考点：

1. 整数类型，包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示 1 字节、2 字节、3 字节、4 字节、8 字节整数。任何整数类型都可以加上 UNSIGNED 属性，表示数据是无符号的，即非负整数。

   长度：整数类型可以被指定长度，例如：INT(11) 表示长度为 11 的 INT 类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL 属性配合使用才有意义。

   例子，假定类型设定为 INT(5)，属性为 UNSIGNED ZEROFILL，如果用户插入的数据为 12 的话，那么数据库实际存储数据为 00012。

2. 实数类型，包括 FLOAT、DOUBLE、DECIMAL。

   DECIMAL 可以用于存储比 BIGINT 还大的整型，能存储精确的小数。

   而 FLOAT 和 DOUBLE 是有取值范围的，并支持使用标准的浮点进行近似计算。

   计算时 FLOAT 和 DOUBLE 相比 DECIMAL 效率更高一些，DECIMAL 你可以理解成是用字符串进行处理。

3. 字符串类型，包括 VARCHAR、CHAR、TEXT、BLOB。

   VARCHAR 用于存储可变长字符串，它比定长类型更节省空间。

   VARCHAR 使用额外 1 或 2 个字节存储字符串长度。列长度小于 255 字节时，使用 1 字节表示，否则使用 2 字节表示。

   VARCHAR 存储的内容超出设置的长度时，内容会被截断。

   CHAR 是定长的，根据定义的字符串长度分配足够的空间。

   CHAR 会根据需要使用空格进行填充方便比较。

   CHAR 适合存储很短的字符串，或者所有值都接近同一个长度。

   CHAR 存储的内容超出设置的长度时，内容同样会被截断。

   使用策略：

   对于经常变更的数据来说，CHAR 比 VARCHAR 更好，因为 CHAR 不容易产生碎片。

   对于非常短的列，CHAR 比 VARCHAR 在存储空间上更有效率。

   使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

   尽量避免使用 TEXT/BLOB 类型，查询时会使用临时表，导致严重的性能开销。

4. 枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。

   有时可以使用 ENUM 代替常用的字符串类型。

   ENUM 存储非常紧凑，会把列表值压缩到一个或两个字节。

   ENUM 在内部存储时，其实存的是整数。

   尽量避免使用数字作为 ENUM 枚举的常量，因为容易混乱。

   排序是按照内部存储的整数。

5. 日期和时间类型，尽量使用 TIMESTAMP，空间效率高于 DATETIME，

   用整数保存时间戳通常不方便处理。

   如果需要存储微秒，可以使用 BIGINT 存储。

看到这里，这道真题是不是就比较容易回答了。

答：INT(0) 表示数据是 INT 类型，长度是 0；CHAR(16) 表示固定长度字符串，长度为 16；VARCHAR(16) 表示可变长度字符串，长度为 16；DATETIME 表示时间类型；TEXT 表示字符串类型，能存储大字符串，最多存储 65535 字节数据。

MySQL 基础操作

MySQL 的连接和关闭：mysql -u -p -h -P

• -u：指定用户名
• -p：指定密码
• -h：主机
• -P：端口

进入 MySQL 命令行后：G、c、q、s、h、d

• G：打印结果垂直显示
• c：取消当前 MySQL 命令
• q：退出 MySQL 连接
• s：显示服务器状态
• h：帮助信息
• d：改变执行符

MySQL 存储引擎

• InnoDB 存储引擎：

  • 默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀。
  • 数据存储在共享表空间，可以通过配置分开。也就是多个表和索引都存储在一个表空间中，可以通过配置文件改变此配置。
  • 对主键查询的性能高于其他类型的存储引擎。
  • 内部做了很多优化，从磁盘读取数据时会自动构建 hash 索引，插入数据时自动构建插入缓冲区。
  • 通过一些机制和工具支持真正的热备份。
  • 支持崩溃后的安全恢复。
  • 支持行级锁。
  • 支持外键。

• MyISAM 存储引擎：

  • 拥有全文索引、压缩、空间函数。
  • 不支持事务和行级锁、不支持崩溃后的安全恢复。
  • 表存储在两个文件，MYD 和 MYI。
  • 设计简单，某些场景下性能很好，例如获取整个表有多少条数据，性能很高。
  • 全文索引不是很常用，不如使用外部的 Elasticsearch 或 Lucene。

• 其他表引擎：

  • Archive、Blackhole、CSV、Memory

使用策略：在大多数场景下建议使用 InnoDB 存储引擎。

MySQL 锁机制

表锁是日常开发中的常见问题，因此也是面试当中最常见的考察点，当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。共享锁和排他锁，就是读锁和写锁。

• 共享锁，不堵塞，多个用户可以同时读一个资源，互不干扰。
• 排他锁，一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。

锁的粒度

• 表锁，系统开销最小，会锁定整张表，MyISAM 使用表锁。
• 行锁，最大程度的支持并发处理，但是也带来了最大的锁开销，InnoDB 使用行锁。

MySQL 事务处理

• MySQL 提供事务处理的表引擎，也就是 InnoDB。
• 服务器层不管理事务，由下层的引擎实现，所以同一个事务中，使用多种引擎是不靠谱的。
• 需要注意，在非事务表上执行事务操作，MySQL 不会发出提醒，也不会报错。

MySQL 存储过程

• 为以后的使用保存的一条或多条 MySQL 语句的集合，因此也可以在存储过程中加入业务逻辑和流程。
• 可以在存储过程中创建表，更新数据，删除数据等等。

使用策略

• 可以通过把 SQL 语句封装在容易使用的单元中，简化复杂的操作；
• 可以保证数据的一致性；
• 可以简化对变动的管理。

MySQL 触发器

提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。

请说明 InnoDB 和 MyISAM 的区别

• InnoDB 支持事务，MyISAM 不支持；
• InnoDB 数据存储在共享表空间，MyISAM 数据存储在文件中；
• InnoDB 支持行级锁，MyISAM 只支持表锁；
• InnoDB 支持崩溃后的恢复，MyISAM 不支持；
• InnoDB 支持外键，MyISAM 不支持；
• InnoDB 不支持全文索引，MyISAM 支持全文索引。

InnoDB 引擎的特性

• 插入缓冲（insert buffer）
• 二次写（double write）
• 自适应哈希索引（ahi）
• 预读（read ahead）

请说明 VARCHAR 和 TEXT 的区别

• VARCHAR 可指定字符数，TEXT 不能指定，内部存储 VARCHAR 是存入的实际字符数 + 1 个字节（n<=255）或 2 个字节(n>255)，TEXT 是实际字符数 + 2 个字节。
• TEXT 类型不能有默认值。
• VARCHAR 可直接创建索引，TEXT 创建索引要指定前多少个字符。VARCHAR 查询速度快于 TEXT，在都创建索引的情况下，TEXT 的索引几乎不起作用。
• 查询 TEXT 需要创建临时表。

VARCHAR(50) 中 50 的含义

最多存放 50 个字符，VARCHAR(50) 和 VARCHAR(200) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度（Memory 引擎也一样）。

INT(20) 中 20 的含义

是指显示字符的长度，不影响内部存储，只是当定义了 ZEROFILL 时，前面补多少个 0。

简单描述 MySQL 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响？

知识点分析

此题主要考察的是 MySQL 索引的基础和类型，由此延伸出的知识点还包括如下内容：

• MySQL 索引的创建原则
• MySQL 索引的注意事项
• MySQL 索引的原理

索引的基础

• 索引类似于书籍的目录，要想找到一本数的某个特定主题，需要先查找书的目录，定位对应的页码。
• 存储引擎使用类似的方式进行数据查询，先去索引当中找到对应的值，然后根据匹配的索引找到对应的数据行。

索引的使用场景

• 对于非常小的表，大部分情况下全表扫描效率更高。
• 中到大型表，索引非常有效。
• 特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术来解决。

索引的类型

索引很多种类型，是在 MySQL 的存储引擎实现的。

• 普通索引：最基本的索引，没有任何约束限制。
• 唯一索引：和普通索引类似，但是具有唯一性约束。
• 主键索引：特殊的唯一索引，不允许有空值。

索引的区别

• 一个表只能有一个主键索引，但是可以有多个唯一索引。
• 主键索引一定是唯一索引，唯一索引不是主键索引。
• 主键可以与外键构成参照完整性约束，防止数据不一致。
• 联合索引：将多个列组合在一起创建索引，可以覆盖多个列。（也叫复合索引，组合索引）
• 外键索引：只有 InnoDB 类型的表才可以使用外键索引，保证数据的一致性、完整性、和实现级联操作（基本不用）。
• 全文索引：MySQL 自带的全文索引只能用于 MyISAM，并且只能对英文进行全文检索（基本不用）。

索引对性能的影响

• 大大减少服务器需要扫描的数据量。
• 帮助服务器避免排序和临时表。
• 将随机 I/O 变顺序 I/O。
• 大大提高查询速度。
• 降低写的速度（不良影响）。
• 磁盘占用（不良影响）。

创建索引的语法

1. 首先创建一个表：CREATE TABLE t1 (id INT PRIMARY KEY, username VARCHAR(20), password VARCHAR(20));
2. 创建单个索引的语法：CREATE INDEX 索引名 ON 表名(字段名)
3. 索引名一般是：表名_字段名
4. 给 id 创建索引：CREATE INDEX t1_id ON t1(id);
5. 创建联合索引的语法：CREATE INDEX 索引名 ON 表名(字段名 1，字段名 2)
6. 给 username 和 password 创建联合索引：CREATE INDEX t1_username_password ON t1(username, password)，其中 INDEX 还可以替换成 UNIQUE，PRIMARY KEY，分别代表唯一索引和主键索引。
7. 删除索引：DROP INDEX t1_username_password ON t1

MySQL 索引的创建原则

• 最适合创建索引的列是出现在 WHERE 或 ON 子句中的列，或连接子句中的列而不是出现在 SELECT 关键字后的列。
• 索引列的基数越大，数据区分度越高，索引的效果越好。
• 对于字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间。
• 根据情况创建联合索引，联合索引可以提高查询效率。
• 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
• 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。

MySQL 索引的注意事项

• 联合索引遵循前缀原则

  KEY(a, b, c)
  WHERE a = 1 AND b = 2 AND c = 3
  WHERE a = 1 AND b = 2
  WHERE a = 1
  # 以上 SQL 语句可以用到索引
  WHERE b = 2 AND c = 3
  WHERE a = 1 AND c = 3
  # 以上 SQL 语句用不到索引

• LIKE 查询，% 不能在前

  WHERE name LIKE "%wang%"
  # 以上语句用不到索引，可以用外部的 Elasticsearch、Lucene 等全文搜索引擎替代。

• 列值为空（NULL）时是可以使用索引的，但 MySQL 难以优化引用了可空列的查询，它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在 MySQL 内部进行特殊处理。

• 如果 MySQL 估计使用索引比全表扫描更慢，会放弃使用索引。

  例如：表中只有 100 条数据左右。对于 SQL 语句 WHERE id > 1 AND id < 100，MySQL 会优先考虑全表扫描。

• 如果关键词 OR 前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。

• 列类型是字符串，查询时一定要给值加引号，否则索引失效。

  # 列 name VARCHAR(16)，存储了字符串 "100"。
  WHERE name = 100;
  # 以上 SQL 语句能搜到，但无法用到索引。

MySQL 索引的原理

MySQL 索引是用一种叫做聚簇索引的数据结构实现的，下面我们就来看一下什么是聚簇索引。

聚簇索引是一种数据存储方式，它实际上是在同一个结构中保存了 B+ 树索引和数据行，InnoDB 表是按照聚簇索引组织的（类似于 Oracle 的索引组织表）。

InnoDB 通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。

注：

• B+ 树是一种树数据结构，是一个 n 叉排序树，每个节点通常有多个孩子，一棵 B+ 树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。
• B+ 树通常用于数据库和操作系统的文件系统中。NTFS、ReiserFS、NSS、XFS、JFS、ReFS 和 BFS 等文件系统都在使用 B+ 树作为元数据索引。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。

列值为 NULL 时，查询是否会用到索引？

在 MySQL 里 NULL 值的列也是走索引的。当然，如果计划对列进行索引，就要尽量避免把它设置为可空，MySQL 难以优化引用了可空列的查询，它会使索引、索引统计和值更加复杂。

以下语句是否会应用索引：SELECT FROM users WHERE YEAR(adddate) < 2007;

不会，因为只要列涉及到运算，MySQL 就不会使用索引。

MyISAM 索引实现？

MyISAM 存储引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。MyISAM 的索引方式也叫做非聚簇索引的，之所以这么称呼是为了与 InnoDB 的聚簇索引区分。

MyISAM 索引与 InnoDB 索引的区别？

• InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引。
• InnoDB 的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

有 A(id, sex, par, c1, c2)、B(id, age, c1, c2) 两张表，其中 A.id 与 B.id 关联，现在要求写出一条 SQL 语句，将 B 中 age>50 的记录的 c1，c2 更新到 A 表中同一记录中的 c1，c2 字段中

考点分析

这道题主要考察的是 MySQL 的关联 UPDATE 语句

延伸考点

• MySQL 的关联查询语句
• MySQL 的关联 UPDATE 语句

针对刚才这道题，答案可以是如下两种形式的写法：

UPDATE A, B SET A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id;
UPDATE A INNER JOIN B ON A.id = B.id SET A.c1 = B.c1, A.c2 = B.c2;
# 再加上 B 中 age > 50 的条件：
UPDATE A, B set A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id and B.age > 50;
UPDATE A INNER JOIN B ON A.id = B.id set A.c1 = B.c1, A.c2 = B.c2 WHERE B.age > 50;

MySQL 的关联查询语句

• 交叉连接（CROSS JOIN）
• 内连接（INNER JOIN）
• 外连接（LEFT JOIN 或 RIGHT JOIN）
• 联合查询（UNION 与 UNION ALL）
• 全连接（FULL JOIN）
• 嵌套查询

交叉连接（CROSS JOIN）

SELECT * FROM A, B(,C)
SELECT * FROM A CROSS JOIN B (CROSS JOIN C)
# 没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用

内连接（INNER JOIN）

SELECT * FROM A, B WHERE A.id = B.id
SELECT * FROM A INNER JOIN B ON A.id = B.id
# 多表中同时符合某种条件的数据记录的集合，INNER JOIN 可以缩写为 JOIN

内连接分为三类

• 等值连接：ON A.id = B.id
• 不等值连接：ON A.id > B.id
• 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外连接（LEFT JOIN 或 RIGHT JOIN）

• 左外连接：LEFT OUTER JOIN，以左表为主，先查询出左表，按照 ON 后的关联条件匹配右表，没有匹配到的用 NULL 填充，可以简写成 LEFT JOIN。
• 右外连接：RIGHT OUTER JOIN，以右表为主，先查询出右表，按照 ON 后的关联条件匹配左表，没有匹配到的用 NULL 填充，可以简写成 RIGHT JOIN。

联合查询（UNION 与 UNION ALL）

SELECT * FROM A UNION SELECT * FROM B UNION ...

• 就是把多个结果集集中在一起，UNION 前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
• 如果使用 UNION ALL，不会合并重复的记录行
• 效率 UNION 高于 UNION ALL

全连接（FULL JOIN）

MySQL 不支持全连接

可以使用 LEFT JOIN 和 UNION 和 RIGHT JOIN 联合使用

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION
SELECT * FROM A RIGHT JOIN B ON A.id=B.id

嵌套查询

用一条 SQL 语句的结果作为另外一条 SQL 语句的条件，效率不好把握。

SELECT * FROM A WHERE id IN (SELECT id FROM B)

解题方法

根据考题要搞清楚表的结果和多表之间的关系，根据想要的结果思考使用那种关联方式，通常把要查询的列先写出来，然后分析这些列都属于哪些表，才考虑使用关联查询

MySQL 中 IN 和 EXISTS 区别

MySQL 中的 IN 语句是把外表和内表作 hash 连接，而 EXISTS 语句是对外表作 loop 循环，每次 loop 循环再对内表进行查询。一直大家都认为 EXISTS 比 IN 语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

• 如果查询的两个表大小相当，那么用 IN 和 EXISTS 差别不大。
• 如果两个表中一个较小，一个是大表，则子查询表大的用 EXISTS ，子查询表小的用 IN。
• NOT IN 和 NOT EXISTS：如果查询语句使用了 NOT IN，那么内外表都进行全表扫描，没有用到索引；而 NOT EXISTS 的子查询依然能用到表上的索引。所以无论那个表大，用 NOT EXISTS 都比 NOT IN 要快。

一个 6 亿的表 A，一个 3 亿的表 B，通过外键 tid 关联，你如何最快的查询出满足条件的第 50000 到第 50200 中的这 200 条数据记录。

• 如果 A 表 TID 是自增长，并且是连续的，B 表的 ID 为索引

  SELECT * FROM a, b WHERE a.tid = b.id AND a.tid > 50000 LIMIT 200;

• 如果 A 表的 TID 不是连续的，那么就需要使用覆盖索引，tid 要么是主键，要么是辅助索引，B 表 id 也需要有索引。

  SELECT * FROM b, (SELECT tid FROM a LIMIT 50000, 200) a WHERE b.id = a .tid;

拷贝表（拷贝数据，源表名：a ，目标表名：b）

INSERT INTO b(a, b, c) SELECT d, e, f FROM a;

Student(S#, Sname, Sage, Ssex) 学生表 Course(C#, Cname, T#) 课程表 SC(S#, C#, score) 成绩表 Teacher(T#, Tname) 教师表，查询没学过“叶平”老师课的同学的学号、姓名

SELECT Student.S#, Student.Sname
  FROM Student
 WHERE S# NOT IN (SELECT DISTINCT (SC.S#)
                    FROM SC, Course, Teacher
                   WHERE SC.C# = Course.C#
                     AND Teacher.T# = Course.T#
                     AND Teacher.Tname = '叶平');

随机取出 10 条数据

SELECT *
  FROM users
 WHERE id >= ((SELECT MAX(id) FROM users) - (SELECT MIN(id) FROM users)) *
       RAND() + (SELECT MIN(id) FROM users) LIMIT 10
# 此方法效率比直接用 SELECT * FROM users ORDER BY RAND() LIMIT 10 高很多

请简述项目中优化 SQL 语句执行效率的方法，从哪些方面，SQL 语句性能如何分析？

考点分析

这道题主要考察的是查找分析 SQL 语句查询速度慢的方法

延伸考点

• 优化查询过程中的数据访问
• 优化长难的查询语句
• 优化特定类型的查询语句

如何查找查询速度慢的原因

记录慢查询日志，分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用 pt-query-digest 工具进行分析。

• 使用 show profile

  set profiling=1;
  # 开启，服务器上所有执行语句会记录执行时间，存到临时表中
  show profiles
  show profile for query 临时表 ID

• 使用 show status show status 会返回一些计数器，show global status 会查看所有服务器级别的所有计数，有时根据这些计数，可以推测出哪些操作代价较高或者消耗时间多。
• show processlist 观察是否有大量线程处于不正常的状态或特征

• 使用 explain 分析单条 SQL 语句

id 由一组数字组成。表示一个查询中各个子查询的执行顺序。

• id 相同执行顺序由上至下。
• id 不同，id 值越大优先级越高，越先被执行。
• id 为 NULL 时表示一个结果集，不需要使用它查询，常出现在包含 UNION 等查询语句中。

select_type 每个子查询的查询类型，一些常见的查询类型。

id	select_type	description
1	SIMPLE	不包含任何子查询或 UNION 等查询
2	PRIMARY	包含子查询最外层查询就显示为 PRIMARY
3	SUBQUERY	在 SELECT 或 WHERE 子句中包含的查询
4	DERIVED	FROM 子句中包含的查询
5	UNION	出现在 UNION 后的查询语句中
6	UNION RESULT	从 UNION 中获取结果集

type 访问类型（非常重要，可以看到有没有走索引）

• ALL：扫描全表数据
• index：遍历索引
• range：索引范围查找
• index_subquery：在子查询中使用 ref
• unique_subquery：在子查询中使用 eq_ref
• ref_or_null：对 NULL 进行索引的优化的 ref
• fulltext：使用全文索引
• ref：使用非唯一索引查找数据
• eq_ref：在 JOIN 查询中使用 PRIMARY KEY 或 UNIQUE NOT NULL 索引关联。

possible_keys 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL 时就要考虑当前的 SQL 是否需要优化了。

key 显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL。查询中若使用了覆盖索引（索引的数据覆盖了需要查询的所有数据），则该索引仅出现在 key 列表中。

key_length 索引长度

• ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• rows 返回估算的结果集数目，并不是一个准确的值。
• extra 的信息非常丰富，常见的有：
  • Using index 使用覆盖索引
  • Using where 使用了用 where 子句来过滤结果集
  • Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
  • Using temporary 使用了临时表 sql 优化的目标可以参考阿里开发手册

推荐：SQL 性能优化的目标至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

• consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
• ref：指的是使用普通的索引（normal index）。
• range：对索引进行范围检索。

反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。

优化查询过程中的数据访问

• 访问数据太多导致查询性能下降
• 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
• 确认 MySQL 服务器是否在分析大量不必要的数据行
• 避免犯如下 SQL 语句错误
  • 查询不需要的数据。解决办法：使用 LIMIT 解决。
  • 多表关联返回全部列。解决办法：指定列名。
  • 总是返回全部列。解决办法：避免使用 SELECT *。
• 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存。
• 是否在扫描额外的记录。解决办法：使用 explain 进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行。
• 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
• 改变数据库和表的结构，修改数据表范式。
• 重写 SQL 语句，让优化器可以以更优的方式执行查询。

优化长难的查询语句

• 一个复杂查询还是多个简单查询。
• MySQL 内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多。
• 使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。
• 切分查询，将一个大的查询分为多个小的相同的查询。
• 一次性删除 1000 万的数据要比一次删除 1 万快，暂停一会的方案更加损耗服务器开销。
• 分解关联查询，让缓存的效率更高。
• 执行单个查询可以减少锁的竞争。
• 在应用层做关联更容易对数据库进行拆分。
• 查询效率会有大幅提升。
• 较少冗余记录的查询。

优化特定类型的查询语句

• COUNT(*) 会忽略所有的列，直接统计所有列数，不要使用 COUNT(列名)。
• MyISAM 中，没有任何 WHERE 条件的 COUNT(*) 非常快。
• 当有 WHERE 条件时，MyISAM 的 COUNT 统计不一定比其它引擎快。
• 可以使用 explain 查询近似值，用近似值替代 COUNT(*)。
• 增加汇总表。
• 使用缓存。

优化关联查询

• 确定 ON 或者 USING 子句中是否有索引。
• 确保 GROUP BY 和 ORDER BY 只有一个表中的列，这样 MySQL 才有可能使用索引。

优化子查询

• 用关联查询替代。
• 优化 GROUP BY 和 DISTINCT。
• 这两种查询可以使用索引来优化，是最有效的优化方法。
• 关联查询中，使用标识列分组的效率更高。
• 如果不需要 ORDER BY，进行 GROUP BY 时加 ORDER BY NULL，MySQL 不会再进行文件排序。
• WITH ROLLUP 超级聚合，可以挪到应用程序处理。

优化 LIMIT 分页

• LIMIT 偏移量大的时候，查询效率较低
• 可以记录上次查询的最大 ID，下次查询时直接根据该 ID 来查询

优化 UNION 查询

• UNION ALL 的效率高于 UNION

解题方法

对于此类考题，先说明如何定位低效 SQL 语句，然后根据 SQL 语句可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

SQL 语句优化的一些方法？

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引。

• 应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

  SELECT id FROM t WHERE num is NULL
  # 可以在 num 上设置默认值 0，确保表中 num 列没有 NULL 值，然后这样查询：
  SELECT id FROM t WHERE num = 0

• 应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

• 应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

  SELECT id FROM t WHERE num = 10 OR num = 20
  # 可以这样查询：
  SELECT id FROM t WHERE num = 10 UNION ALL SELECT id FROM t WHERE num = 20

• IN 和 NOT IN 也要慎用，否则会导致全表扫描，如：

  SELECT id FROM t WHERE num IN (1,2,3) 
  对于连续的数值，能用 BETWEEN 就不要用 IN 了：
  SELECT id FROM t WHERE num BETWEEN 1 AND 3

• 下面的查询也将导致全表扫描：SELECT id FROM t WHERE name LIKE '%李%' 若要提高效率，可以考虑全文检索。

• 如果在 WHERE 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

  SELECT id FROM t WHERE num=@num
  可以改为强制查询使用索引：
  SELECT id FROM t WITH(INDEX(索引名)) WHERE num=@num

• 应尽量避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

  SELECT id FROM t WHERE num/2=100
  # 应改为:
  SELECT id FROM t WHERE num=100*2

• 应尽量避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

  SELECT id FROM t WHERE substring(name, 1, 3) = 'abc'
  以 abc 开头的 id 应改为:
  SELECT id FROM t WHERE name LIKE 'abc%'

• 不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。