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 ('', '中铁铁路', '山东青岛');
INSERT INTO `tb_company` VALUES ('', '大船集团', '山东青岛'); -- ----------------------------
-- 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 ('', 'RD', '');
INSERT INTO `tb_dept` VALUES ('', 'HR', '');
INSERT INTO `tb_dept` VALUES ('', 'MK', '');
INSERT INTO `tb_dept` VALUES ('', 'MIS', '');
INSERT INTO `tb_dept` VALUES ('', 'FD', ''); -- ----------------------------
-- 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 ('', '张三', '', '');
INSERT INTO `tb_emp` VALUES ('', '李四', '', '');
INSERT INTO `tb_emp` VALUES ('', '王二', '', '');
INSERT INTO `tb_emp` VALUES ('', '麻子', '', '');
INSERT INTO `tb_emp` VALUES ('', '小马', '', '');
INSERT INTO `tb_emp` VALUES ('', '马旭', '', '');
INSERT INTO `tb_emp` VALUES ('', '小丁', '', '');
INSERT INTO `tb_emp` VALUES ('', '小西', '', '');
INSERT INTO `tb_emp` VALUES ('', '小明', '', '');
INSERT INTO `tb_emp` VALUES ('', '小红', '', '');
INSERT INTO `tb_emp` VALUES ('', '小张', '', '');
INSERT INTO `tb_emp` VALUES ('', '小王', '', '');
INSERT INTO `tb_emp` VALUES ('', '小杨', '', '');
INSERT INTO `tb_emp` VALUES ('', '小兰', '', '');
INSERT INTO `tb_emp` VALUES ('', '小花', '', '');
INSERT INTO `tb_emp` VALUES ('', '小紫', '', '');
执行计划包含信息各字段解释：
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操作，在找到第一匹配的元素后即停止找同样值的动作。
前三项重点掌握。