SQL性能分析
MySQL常见瓶颈：
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候。
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。
- 服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态。
EXPLAIN：
定义：
explain 叫做查看执行计划，使用 explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理执行的SQL语句的。分析SQL或是表结构的性能瓶颈。
功用：
- 查看表的读取顺序。
- 查看数据读取操作的操作类型。
- 查看哪些索引可以使用。
- 查看哪些索引实际使用。
- 查看表之间的引用。
- 查看每张表有多少行被优化器查询。
使用方式：EXPLAIN + SQL语句
执行计划包含的信息：
mysql> explain select * from tb_company; +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | tb_company | ALL | NULL | NULL | NULL | NULL | 2 | NULL | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
准备数据库信息：
创建数据库：
mysql> create database db_index; Query OK, 1 row affected (0.02 sec)
使用数据库：
mysql> use db_index; Database changed
创建表添加数据：
/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50203 Source Host : localhost:3306 Source Database : db_test Target Server Type : MYSQL Target Server Version : 50203 File Encoding : 65001 Date: 2017-06-27 16:57:42 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for tb_company -- ---------------------------- DROP TABLE IF EXISTS `tb_company`; CREATE TABLE `tb_company` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `companyName` varchar(40) DEFAULT NULL COMMENT '公司名称', `address` varchar(40) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of tb_company -- ---------------------------- INSERT INTO `tb_company` VALUES ('1', '中铁铁路', '山东青岛'); INSERT INTO `tb_company` VALUES ('2', '大船集团', '山东青岛'); -- ---------------------------- -- Table structure for tb_dept -- ---------------------------- DROP TABLE IF EXISTS `tb_dept`; CREATE TABLE `tb_dept` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门主键', `deptName` varchar(30) DEFAULT NULL COMMENT '部门名称', `locAdd` varchar(40) DEFAULT NULL COMMENT '楼层', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of tb_dept -- ---------------------------- INSERT INTO `tb_dept` VALUES ('1', 'RD', '11'); INSERT INTO `tb_dept` VALUES ('2', 'HR', '12'); INSERT INTO `tb_dept` VALUES ('3', 'MK', '13'); INSERT INTO `tb_dept` VALUES ('4', 'MIS', '14'); INSERT INTO `tb_dept` VALUES ('5', 'FD', '15'); -- ---------------------------- -- Table structure for tb_emp -- ---------------------------- DROP TABLE IF EXISTS `tb_emp`; CREATE TABLE `tb_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工主键', `name` varchar(20) DEFAULT NULL COMMENT '员工姓名', `deptId` int(11) DEFAULT NULL COMMENT '部门外键', `companyId` int(11) DEFAULT NULL COMMENT '公司外键', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of tb_emp -- ---------------------------- INSERT INTO `tb_emp` VALUES ('1', '张三', '1', '1'); INSERT INTO `tb_emp` VALUES ('2', '李四', '1', '1'); INSERT INTO `tb_emp` VALUES ('3', '王二', '1', '1'); INSERT INTO `tb_emp` VALUES ('4', '麻子', '2', '1'); INSERT INTO `tb_emp` VALUES ('5', '小马', '2', '1'); INSERT INTO `tb_emp` VALUES ('6', '马旭', '3', '1'); INSERT INTO `tb_emp` VALUES ('7', '小丁', '4', '1'); INSERT INTO `tb_emp` VALUES ('8', '小西', '95', '1'); INSERT INTO `tb_emp` VALUES ('9', '小明', '1', '2'); INSERT INTO `tb_emp` VALUES ('10', '小红', '1', '2'); INSERT INTO `tb_emp` VALUES ('11', '小张', '2', '2'); INSERT INTO `tb_emp` VALUES ('12', '小王', '3', '2'); INSERT INTO `tb_emp` VALUES ('13', '小杨', '4', '2'); INSERT INTO `tb_emp` VALUES ('14', '小兰', '5', '2'); INSERT INTO `tb_emp` VALUES ('15', '小花', '96', '2'); INSERT INTO `tb_emp` VALUES ('16', '小紫', '90', '20');
执行计划包含信息各字段解释：
id：select 查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序。
1. id相同：执行顺序由上至下。
mysql> explain select e.* from tb_emp e, tb_dept d, tb_company c -> where e.deptId = d.id and e.companyId = c.id; +----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+ | 1 | SIMPLE | c | index | PRIMARY | PRIMARY | 4 | NULL | 2 | Using index | | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 16 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | db_index.e.deptId | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+ 3 rows in set (0.00 sec)
执行顺序为： c表(tb_company) ==》 e表(tb_emp) ==》 d表(tb_dept)。
2. id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先执行。
mysql> explain select * from tb_dept d -> where d.id = ( -> select e.deptId from tb_emp e -> where e.companyId = ( -> select c.id from tb_company c -> where c.companyName = '大船集团') limit 1); +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | d | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | | 2 | SUBQUERY | e | ALL | NULL | NULL | NULL | NULL | 16 | Using where | | 3 | SUBQUERY | c | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 3 rows in set (0.00 sec)
执行顺序为：c表(tb_company) ==》 e表(tb_emp) ==》 d表(tb_dept)。
3. id既有相同，又有不同，同时存在：id如果相同，可以认为是一组，从上往下顺序执行，在所有的组中，id值越大，优先级越高，越先执行。
mysql> explain select e.* from ( -> select c.id from tb_company c -> where c.companyName = '大船集团') s1, tb_emp e -> where e.companyId = s1.id; +----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | NULL | | 1 | PRIMARY | e | ALL | NULL | NULL | NULL | NULL | 16 | Using where; Using join buffer (Block Nested Loop) | | 2 | DERIVED | c | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------------+ 3 rows in set (0.00 sec)
执行顺序为：c表(tb_company) ==》 <derived2>表(s1) ==》 e表(tb_emp)。
注意：derived = 衍生。 <derived2>意义为：id为2那条执行操作的衍生表。
select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等复杂查询。
- SIMPLE：简单的select查询，查询中不包含子查询或者UNION。
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
- SUBQUERY：在select或where列表中包含子查询。
- DERIVED：在from列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。
- UNION：第二个select出现在UNION之后，则被标记为UNION；若UNION包含在from字句的子查询中，外层select将被标记为DERIVED。
- UNION RESULT ：从UNION表获取结果的select。
- SIMPLE:
mysql> explain select * from tb_emp; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | tb_emp | ALL | NULL | NULL | NULL | NULL | 16 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
- PRIMARY，SUBQUERY，DERIVER，UNION，UNION RESULT：
mysql> explain select * from ( -> select e.name, e.companyId, d.deptName from tb_emp e -> left join tb_dept d on e.deptId = d.id -> union -> select e1.name, e1.companyId, d1.deptName from tb_emp e1 -> right join tb_dept d1 on e1.deptId = d1.id) s1 -> where s1.companyId = ( -> select c.id from tb_company c -> where c.companyName = '大船集团'); +----+--------------+------------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+ | 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | const | 10 | Using where | | 4 | SUBQUERY | c | ALL | NULL | NULL | NULL | NULL | 2 | Using where | | 2 | DERIVED | e | ALL | NULL | NULL | NULL | NULL | 16 | NULL | | 2 | DERIVED | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using join buffer (Block Nested Loop) | | 3 | UNION | d1 | ALL | NULL | NULL | NULL | NULL | 5 | NULL | | 3 | UNION | e1 | ALL | NULL | NULL | NULL | NULL | 16 | Using where; Using join buffer (Block Nested Loop) | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+ 7 rows in set (0.01 sec)
table：显示这一行的数据是哪张表。
type：访问类型。
- 结果值从最好到最坏依次：system-->const-->eq_ref-->ref-->fulltext--.ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->ALL
- 常见的结果值最好到最坏：system-->const-->eq_ref-->ref-->range-->index-->ALL
访问类型级别：
- system：表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，这个可以忽略不计。
- const：表示通过索引一次就能找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
-
mysql> explain select * from ( -> select * from tb_emp where id = 1) e; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | tb_emp | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ 2 rows in set (0.00 sec)
- eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
-
mysql> explain select * from tb_emp e, tb_dept d -> where e.id = d.id; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | db_index.d.id | 1 | NULL | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ 2 rows in set (0.00 sec)
此SQL没有任何实际意义，只为测试。
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值得行，然后他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
-
mysql> create index idx_deptId on tb_emp(deptId); --创建普通索引 Query OK, 0 rows affected (1.63 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from tb_emp e, tb_dept d -> where e.deptId = d.id; +----+-------------+-------+------+---------------+------------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------------+---------+---------------+------+-------+ | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | e | ref | idx_deptId | idx_deptId | 5 | db_index.d.id | 1 | NULL | +----+-------------+-------+------+---------------+------------+---------+---------------+------+-------+ 2 rows in set (0.00 sec)
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般在where语句中使用between、<、>、in等的查询，这种范围扫描索引比全盘扫描要好，因为它只需要开始于索引的某一点，结束于某个点，不用扫描全部索引。
-
mysql> explain select * from tb_emp where id between 1 and 8; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | tb_emp | range | PRIMARY | PRIMARY | 4 | NULL | 8 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from tb_emp where id > 5; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | tb_emp | range | PRIMARY | PRIMARY | 4 | NULL | 11 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from tb_emp where id in (1, 3, 5, 6, 7); +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | tb_emp | range | PRIMARY | PRIMARY | 4 | NULL | 5 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from tb_emp where id in (1, 3, 5, 6, 7, 9); --注意：in中参数超过5个就会变成全扫描。 +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tb_emp | ALL | PRIMARY | NULL | NULL | NULL | 16 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
-
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然ALL和index都是读全表，但是index是从索引中读取，而ALL是从硬盘读取。
- ALL：Full TableScan，将遍历全表以找到匹配行。
-
mysql> explain select id from tb_emp; +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | tb_emp | index | NULL | idx_deptId | 5 | NULL | 16 | Using index | +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from tb_emp; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | tb_emp | ALL | NULL | NULL | NULL | NULL | 16 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key：实际使用的索引，若为null， 则没有使用索引。查询中若是使用了付该索引，则该索引仅出现在key列表中。
mysql> explain select * from tb_emp; --全扫描。 +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | tb_emp | ALL | NULL | NULL | NULL | NULL | 16 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> explain select * from tb_emp where deptId = 1; --使用idx_deptId索引。 +----+-------------+--------+------+---------------+------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------------+---------+-------+------+-------+ | 1 | SIMPLE | tb_emp | ref | idx_deptId | idx_deptId | 5 | const | 5 | NULL | +----+-------------+--------+------+---------------+------------+---------+-------+------+-------+ 1 row in set (0.00 sec) mysql> explain select deptId from tb_emp; --使用覆盖索引idx_deptId +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | tb_emp | index | NULL | idx_deptId | 5 | NULL | 16 | Using index | +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> create index idx_deptId_companyId on tb_emp(deptId, companyId); --创建索引 Query OK, 0 rows affected (1.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from tb_emp where deptId = 1; --理论索引为idx_deptId,idx_deptId_companyId 实际使用索引为：idx_deptId +----+-------------+--------+------+---------------------------------+------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------------------------+------------+---------+-------+------+-------+ | 1 | SIMPLE | tb_emp | ref | idx_deptId,idx_deptId_companyId | idx_deptId | 5 | const | 5 | NULL | +----+-------------+--------+------+---------------------------------+------------+---------+-------+------+-------+ 1 row in set (0.01 sec)
- 覆盖索引：
- 理解方式一：就是select的数据列从索引中就能取得，就不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
- 理解方式二：数据库使用索引可以找到一列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含(或覆盖)满足查询结果的数据就叫做覆盖索引。
个人感觉理解方式一比较容易接受。
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精度的前提下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算所得，不是通过表内检索出的。
mysql> desc tb_emp; --查看表结构 +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | deptId | int(11) | YES | MUL | NULL | | | companyId | int(11) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> explain select * from tb_emp where deptId = 1; +----+-------------+--------+------+---------------------------------+------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------------------------+------------+---------+-------+------+-------+ | 1 | SIMPLE | tb_emp | ref | idx_deptId,idx_deptId_companyId | idx_deptId | 5 | const | 5 | NULL | +----+-------------+--------+------+---------------------------------+------------+---------+-------+------+-------+ 1 row in set (0.00 sec) mysql> explain select * from tb_emp where deptId = 1 and companyId = 1; +----+-------------+--------+------+---------------------------------+----------------------+---------+-------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------------------------+----------------------+---------+-------------+------+-------+ | 1 | SIMPLE | tb_emp | ref | idx_deptId,idx_deptId_companyId | idx_deptId_companyId | 10 | const,const | 3 | NULL | +----+-------------+--------+------+---------------------------------+----------------------+---------+-------------+------+-------+ 1 row in set (1.57 sec)
ref：显示索引的哪一列被使用，如果可能的话，尽量是一个常数。哪些列或常量被用于查找索引列上的值。
mysql> explain select * from tb_emp e, tb_dept d -> where e.deptId = d.id; +----+-------------+-------+------+---------------------------------+------------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------------+------------+---------+---------------+------+-------+ | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | e | ref | idx_deptId,idx_deptId_companyId | idx_deptId | 5 | db_index.d.id | 1 | NULL | +----+-------------+-------+------+---------------------------------+------------+---------+---------------+------+-------+ 2 rows in set (0.00 sec)
由上面数据可知 tb_emp 表的 idex_deptId 被充分使用，deptId 匹配 tb_dep t表中的 id 字段。
db_index.d.id意义：db_index 数据库中 d(tb_dept) 表 id 字段。
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
mysql> explain select * from tb_emp e, tb_dept d -> where e.deptId =d.id; +----+-------------+-------+------+---------------------------------+------------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------------+------------+---------+---------------+------+-------+ | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | e | ref | idx_deptId,idx_deptId_companyId | idx_deptId | 5 | db_index.d.id | 1 | NULL | +----+-------------+-------+------+---------------------------------+------------+---------+---------------+------+-------+ 2 rows in set (0.00 sec) mysql> drop index idx_deptId on tb_emp; --删除索引idx——deptId Query OK, 0 rows affected (1.56 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from tb_emp e, tb_dept d where e.deptId =d.id; +----+-------------+-------+------+----------------------+----------------------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------------+----------------------+---------+---------------+------+-------+ | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | e | ref | idx_deptId_companyId | idx_deptId_companyId | 5 | db_index.d.id | 1 | NULL | +----+-------------+-------+------+----------------------+----------------------+---------+---------------+------+-------+ 2 rows in set (0.00 sec) mysql> drop index idx_deptId_companyId on tb_emp; --删除索引idx_deptId_companyId Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from tb_emp e, tb_dept d where e.deptId =d.id; +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 16 | NULL | | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ 2 rows in set (0.00 sec)
Extra：包含不适合在其他列中显示但十分重要的额外信息。
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为”文件排序“。
-
mysql> desc tb_emp; --查看表结构 +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | deptId | int(11) | YES | | NULL | | | companyId | int(11) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) --无索引情况下排序。 mysql> explain select * from tb_emp order by name; +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | tb_emp | ALL | NULL | NULL | NULL | NULL | 16 | Using filesort | +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) --创建索引 mysql> create index idx_name_deptId_companyId on tb_emp(name, deptId, companyId); Query OK, 0 rows affected (1.59 sec) Records: 0 Duplicates: 0 Warnings: 0 --不按照表内索引顺序进行排序 mysql> explain select * from tb_emp order by deptId; +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | tb_emp | index | NULL | idx_name_deptId_companyId | 73 | NULL | 16 | Using index; Using filesort | +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) --覆盖索引，不按照表内索引顺序进行排序 mysql> explain select name, deptId, companyId from tb_emp order by companyId; +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | tb_emp | index | NULL | idx_name_deptId_companyId | 73 | NULL | 16 | Using index; Using filesort | +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) --按照表内索引顺序进行排序。 --注意：可以用一个或多个进行排序，必须按照表内索引顺序进行排序。 mysql> explain select * from tb_emp order by name, deptId, companyId; +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-------------+ | 1 | SIMPLE | tb_emp | index | NULL | idx_name_deptId_companyId | 73 | NULL | 16 | Using index | +----+-------------+--------+-------+---------------+---------------------------+---------+------+------+-------------+ 1 row in set (0.00 sec)
- Using temporay：MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
-
--按照表内索引顺序进行分组 mysql> explain select name, deptId, companyId from tb_emp group by name, deptId, companyId; +----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+ | 1 | SIMPLE | tb_emp | index | idx_name_deptId_companyId | idx_name_deptId_companyId | 73 | NULL | 16 | Using index | +----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+ 1 row in set (0.01 sec) --不按照表内索引顺序进行分组 mysql> explain select name, deptId, companyId from tb_emp group by name, companyId, deptId; +----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | tb_emp | index | idx_name_deptId_companyId | idx_name_deptId_companyId | 73 | NULL | 16 | Using index; Using temporary; Using filesort | +----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+----------------------------------------------+ 1 row in set (0.00 sec) --无索引进行分组 --1.删除索引 mysql> drop index idx_name_deptId_companyId on tb_emp; Query OK, 0 rows affected (1.60 sec) Records: 0 Duplicates: 0 Warnings: 0 --2.查看表结构 mysql> desc tb_emp; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | deptId | int(11) | YES | | NULL | | | companyId | int(11) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) --3.进行分组 mysql> explain select name, deptId, companyId from tb_emp group by name, deptId, companyId; +----+-------------+--------+------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+---------------------------------+ | 1 | SIMPLE | tb_emp | ALL | NULL | NULL | NULL | NULL | 16 | Using temporary; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+------+---------------------------------+ 1 row in set (0.00 sec)
- Using index：使用了索引进行操作。
- Using where：使用了where条件进行操作。
- Using join buffer：使用了连接进行操作。
- impossible where：where 后面的条件总是为false，不能用来获取任何元素。
- select tables optimized away：在没有group by 字句的情况下，基于索引优化MIN/MAX操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段在进行计算，查询执行计划生成的接管即完成优化。
- distinct：优化distinct操作，在找到第一匹配的元素后即停止找同样值的动作。
前三项重点掌握。
