MySQL索引及其SQL优化

      文中将分为四个方面去了解一下MYSQL5.7 

1，mysql索引的数据结构

1.1 索引的定义

索引是帮助MySQL高效获取数据的排好序的数据结构，索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

1.2 索引的优缺点

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；
劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

1.3 索引的实现原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等。

1.3.1 常用的储存引擎为什么是B+树类型的？

下面我们来比较一下二叉树，hash，Btree ，B+tree 的区别。

 

1.4 mysql的存储引擎

执行：show ENGINES ;

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO

 对于MySQL来说，它提供了很多种类型的存储引擎（或者说不通的表类型），我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。

1.4.1 MyISAM 储存引擎

 新建一张表 student 搜索引擎为myisam 

执行语句 ： show global variables;  

查看datadir 的路径

执行语句：SELECT table_name, table_type, engine FROM information_schema.tables 
WHERE table_schema = 'test' ORDER BY table_name DESC;

查看某数据库中的表的搜索引擎。结果如下，我的test数据库下只有两张表，

下面我们去 datadir 目录下面看下他的储存结构：

它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。
每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：由上图可知，myisam 会产生三个文件               .frm(存储表定义)
         MYD(MYData，存储数据)
         MYI(MYIndex，存储索引)

MyISAM索引结构: MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。如下图：

 B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

因此，过程为： MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，根据data域的值去读取相应数据记录。

1.4.2 Innodb 储存引擎

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。(在mysql5.5之后就是默认的储存引擎)
      Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。
      该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。
      但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。
      当需要使用数据库事务时，该引擎当然是首选。
      由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。
      但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。
      可以从灾难中恢复（通过bin-log日志等），支持自动增加列属性auto_increment

也是B+Treee索引结构。Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。 

InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。
建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

上图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

而且，与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

因此，过程为：Innodb储存引擎将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 13”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

2，msyql执行计划 explain

EXPLAIN：使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈（我们最常用的是用explanin关键字来查看sql语句走不走索引，以及走索引的什么级别上）

使用的表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50718
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50718
 File Encoding         : 65001

 Date: 19/06/2019 23:37:42
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for actor
-- ----------------------------
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor`  (
  `id` int(11) NOT NULL,
  `name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of actor
-- ----------------------------
INSERT INTO `actor` VALUES (1, 'a', '2017-12-22 15:27:18');
INSERT INTO `actor` VALUES (2, 'b', '2017-12-22 15:27:18');
INSERT INTO `actor` VALUES (3, 'c', '2017-12-22 15:27:18');

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `rec_id` int(11) NOT NULL AUTO_INCREMENT,
  `no` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`rec_id`) USING BTREE,
  INDEX `index_employe_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, '2014001', '华东旭', '湖北黄石', '23');

-- ----------------------------
-- Table structure for film
-- ----------------------------
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of film
-- ----------------------------
INSERT INTO `film` VALUES (1, 'film0', 11.00);
INSERT INTO `film` VALUES (2, 'film1', 23.00);
INSERT INTO `film` VALUES (3, 'film2', 14.00);

-- ----------------------------
-- Table structure for film_actor
-- ----------------------------
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor`  (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_film_actor_id`(`film_id`, `actor_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of film_actor
-- ----------------------------
INSERT INTO `film_actor` VALUES (1, 1, 1, NULL);
INSERT INTO `film_actor` VALUES (2, 1, 2, NULL);
INSERT INTO `film_actor` VALUES (3, 2, 1, NULL);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `rec_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) NOT NULL,
  `mark` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`rec_id`) USING BTREE,
  UNIQUE INDEX `UK_SCORE_STU_ID`(`stu_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 680);
INSERT INTO `score` VALUES (2, 2, 56);
INSERT INTO `score` VALUES (3, 4, 230);
INSERT INTO `score` VALUES (4, 6, 488);
INSERT INTO `score` VALUES (5, 8, 519);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, 'huadongxu', 23);
INSERT INTO `student` VALUES (2, 'raofei', 28);
INSERT INTO `student` VALUES (3, '张三', 3);
INSERT INTO `student` VALUES (4, '李四', 25);
INSERT INTO `student` VALUES (5, '王五', 78);

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, 'huadongxu', 23);

SET FOREIGN_KEY_CHECKS = 1;

 

2.1 id 列说明

      id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。
      复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。
      id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

 

2.2 select_type列说明

 2.2.1 SIMPLE类型

简单查询类型：（不包括UNION操作或子查询操作）（或者可以被sql优化器优化成不包括UNION或者子查询的操作）

mysql> explain select * from film WHERE name = 'film0';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		ref	idx_name	idx_name	33	const	1	100	Using index

 

2.2.2  PRIMARY/UNION 类型

PRIMARY：查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）

mysql >  explain select * from film film WHERE film.price = (SELECT MAX(film.price) FROM film film) ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	film		ALL					4	25	Using where
2	SUBQUERY	film		ALL					4	100

 查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY）

mysql> explain select * from film WHERE id = 1 UNION select * from film WHERE id = 2 ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	film		const	PRIMARY	PRIMARY	4	const	1	100
2	UNION	film		const	PRIMARY	PRIMARY	4	const	1	100
	UNION RESULT	<union1,2>		ALL							Using temporary

 Using temporary :  使用了临时表保存中间结果 ，详细参见 

2.2.3 DEPENDENT UNION/UNIOIN RESULT 类型

DEPENDENT UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）
UNION RESULT：UNION操作的结果，id值通常为NULL

mysql> EXPLAIN SELECT * FROM film WHERE id = 1 union SELECT * FROM film WHERE id = 2

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	film		const	PRIMARY	PRIMARY	4	const	1	100
2	UNION	film		const	PRIMARY	PRIMARY	4	const	1	100
	UNION RESULT	<union1,2>		ALL							Using temporary

 

 2.2.4 SUBQUERY/DEPENDENT SUBQUERY

SUBQUERY：子查询中首个SELECT（如果有多个子查询存在）：

mysql >  explain select * from film film WHERE film.price = (SELECT MAX(film.price) FROM film film) ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	film		ALL					4	25	Using where
2	SUBQUERY	film		ALL					4	100

DEPENDENT SUBQUERY：子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）：

mysql> explain select * from film s1 WHERE s1.price = (SELECT MAX(s2.price) FROM film s2 WHERE s1.`name` = s2.`name`) ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	s1		ALL					4	100	Using where
2	DEPENDENT SUBQUERY	s2		ref	idx_name	idx_name	33	test.s1.name	1	100

重点解释 子查询的查询方式依赖于外面的查询结果.用这个例子就是,先进行子查询外部的查询,得到一个结果集,.然后这个结果的每一行在跟select子查询的结果集进行匹配,也就是说,外部结果集的每一行都要关联内部结果集一次。特别关注 DEPENDENT SUBQUERY ，会严重消耗性能。

 

2.2.5 DERIVED / MATERIALIZED

DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）

mysql > EXPLAIN SELECT s1.id , s1.`name` , s2.price FROM film s1 INNER JOIN (
SELECT `name`,`price` FROM film GROUP BY `name`
) as s2 on s1.`name` = s1.`name` AND s1.price = s2.price ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	s1		ALL					4	25	Using where
1	PRIMARY	<derived2>		ref	<auto_key0>	<auto_key0>	6	test.s1.price	2	100
2	DERIVED	film		index	idx_name	idx_name	33		4	100

 

2.2.6 UNCACHEABLE SUBQUERY/UNCACHEABLE UNION

UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

 

2.3 table 列

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

mysql> EXPLAIN SELECT * FROM film WHERE id = 1 union SELECT * FROM film WHERE id = 2

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	film		const	PRIMARY	PRIMARY	4	const	1	100
2	UNION	film		const	PRIMARY	PRIMARY	4	const	1	100
	UNION RESULT	<union1,2>		ALL							Using temporary

2.4 type 列

       type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些。
       mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是 system > const > eq_ref > ref > range > index > all 。从左到右，它们的效率依次是减弱的。一般来讲，我们应该要保证查询达到range级别，最好达到ref级别。

2.4.1 all

       这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。为什么这么说呢？因为all是一种非常暴力和原始的查找方法，非常的耗时而且低效。

mysql > explain select * from film WHERE film.`name` like '%film1';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		ALL					3	33.33	Using where

 

 2.4.2 index 

       这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。
      如果一定要比效率，只需要获取这个表的数据并且排序便可以看出来谁比谁效率高了：

mysql > explain select * from employee order by `no` ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employee		ALL					1	100	Using filesort

mysql > explain select * from employee order by rec_id ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employee		index		PRIMARY	4		1	100

由上面的实例可以看出，根绝索引字段 order_by type的值 为 index。

如果连接类型为type，而且extra列中的值为‘Using index’，那么称这种情况为 索引覆盖；(即我们想要的部分就要索引字段中)

索引覆盖意味着什么呢？想象这样一种场景，如果说一本新华字典是一张表，当然前面的索引部分（假设按照部首的索引）是这张表的索引，那么索引覆盖就相当于根据部首索引获取第一个字到最后一个字（新华字典的所有字）。我们获得了字典中所有的字，然而我们并没有查一次表，因为我们想要的都早索引中，即索引覆盖。

 mysql > explain select rec_id from employee;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employee		index		PRIMARY	4		1	100	Using index

其实我是这么理解的：type中index的理解应该是扫描全表，但是扫描的是索引全表，不用回原数据表取数据。因为索引表中已经包含了所查询的所有信息了。

2.4.3  range 

        range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及'>','<'外，in和or也是索引范围扫描。

 mysql > explain select * from film WHERE id = 1 OR id = 2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		range	PRIMARY	PRIMARY	4		2	100	Using where

mysql > explain select * from film WHERE id <> 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		range	PRIMARY	PRIMARY	4		3	100	Using where

 

 2.4.4 ref 

        出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）
 

ALTER TABLE employee ADD KEY index_employe_name(`NAME`);

DESC employee;

mysql> explain select * from employee where `name` = '华东旭';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employee		ref	index_employe_name	index_employe_name	62	const	1	100

 

2.4.5 ref_eq

        ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。
        在单个表中，曾尝试了很多方法想出现ref_eq的连接类型，然而很多时候出现的都是const，因此不得不随手连接了一张表得到了想要的连接类型，该表的建表代码为。

新建一张表：

CREATE TABLE `score` (
  `rec_id` INT(11) NOT NULL AUTO_INCREMENT,
  `stu_id` INT(11) NOT NULL,
  `mark` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`rec_id`),
  UNIQUE KEY `UK_SCORE_STU_ID` (`stu_id`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

 mysql > EXPLAIN SELECT * FROM employee em , score sc WHERE em.rec_id = sc.stu_id ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	em		ALL	PRIMARY				1	100
1	SIMPLE	sc		eq_ref	UK_SCORE_STU_ID	UK_SCORE_STU_ID	4	test.em.rec_id	1	100

上面就可以看到score表是全表扫描的类型，rows=5代表外层表循环了五次（因为有五条数据），但是employee表的rows怎么是1，怎么可能？刚开始也是很疑惑，这与mysql的查询原理息息相关，rows实际反映的是查询的内循环数，针对外层的每一条数据匹配，employee的确一枪就可以命中，因此rows为1。 

 

2.4.6 const

通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

 mysql > explain SELECT * FROM score sc WHERE sc.stu_id = 2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sc		const	PRIMARY	PRIMARY	4	const	1	100

  mysql > explain SELECT * FROM score sc WHERE sc.stu_id = 2 AND sc.rec_id = 2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sc		const	PRIMARY,UK_SCORE_STU_ID	PRIMARY	4	const	1	100

 

2.5  possible_keys 列 - 有可能使用到的索引

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。下面我们来看三个例子，大概就可以理解这个possible_keys 的含义了。

2.5.1 primary

  mysql > explain SELECT * FROM score sc WHERE sc.rec_id = 2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sc		const	PRIMARY	PRIMARY	4	const	1	100

2.5.2 PRIMARY,UK_SCORE_STU_ID

mysql > 1    SIMPLE    sc        const    PRIMARY,UK_SCORE_STU_ID    PRIMARY    4    const    1    100    

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sc		const	PRIMARY,UK_SCORE_STU_ID	PRIMARY	4	const	1	100

2.5.3 NULL 

 mysql > explain SELECT * FROM score sc WHERE sc.stu_id = 2 AND sc.rec_id = 4;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE										Impossible WHERE noticed after reading const tables

 

2.6  key 列 - 实际用到的索引列

例子对比 2.5中的 possible_keys

 

2.7 key_len -- 使用到的索引所占的字节数

key_len计算规则如下：
    字符串
    char(n)：n字节长度
    varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
    数值类型
    tinyint：1字节
    smallint：2字节
    int：4字节
    bigint：8字节　　
    时间类型　
    date：3字节
    timestamp：4字节
    datetime：8字节
    如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

mysql > desc employee;

Field	Type	Null	Key	Default	Extra
rec_id	int(11)	NO	PRI		auto_increment
no	varchar(10)	NO
name	varchar(20)	NO	MUL
position	varchar(20)	NO
age	varchar(2)	NO

 这种 mysql 索引的计算规则下：解释了int(11) 的 key_len 为什么是 4 

mysql > EXPLAIN SELECT * FROM employee em WHERE em.name = '华东旭' ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	em		ref	index_employe_name	index_employe_name	62	const	1	100

3n + 2 = 3 * 20 + 2 = 62 ； 验证完毕

 

2.8 ref - 显示了之前的表在key列记录的索引中查找值所用的列或常量

显示索引的哪一列被使用了，有时候会是一个常量：表示哪些列或常量被用于用于查找索引列上的值

mysql > EXPLAIN SELECT * FROM employee em WHERE em.name = '华东旭' ;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	em		ref	index_employe_name	index_employe_name	62	const	1	100

这里用常量const 表示： 

2.9 rows - 为了找到所需的行而需要读取的行数，估算值

我们来看下面的例子:

mysql > SELECT * FROM student stu WHERE stu.id > 2 ;   查询结果如下：

id	name	age
3	张三	3
4	李四	25
5	王五	78

mysql >  EXPLAIN SELECT * FROM student stu WHERE stu.id > 2 ; 查询结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	stu		range	PRIMARY	PRIMARY	4		3	100	Using index condition

 

2.10 filtered 过滤条件

由表条件过滤的表行的估计百分比。最大值为100，这意味着没有发生行过滤。值从100下降表明过滤量增加

mysql > explain select * from film WHERE film.`name` like '%film1';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		ALL					3	33.33	Using where

mysql > explain select * from film WHERE film.`name` like 'film1%';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		range	idx_name	idx_name	33		1	100	Using index condition

mysql > explain select * from film WHERE film.`name` like 'film%';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film		ALL	idx_name				4	75	Using where

 

 例子一 不走索引 type=all , 于是查找的行 rows = 3 ，最后成功查找的行为1，因此filtered 的值为 33.33 。

 例子二 走type = range ,查找的行为1行数据，因此filter的值为100

 例子三 我又添加了一条数据，name 为 actor ，因此 rows = 4 , filtered = 75

2.11 Extra :  额外的信息

Using filesort : 说明MySQL需要对数据进行额外的排序操作，不能通过索引顺序来进行排序，这个操作比较消耗CPU资源。
Using temporary :  使用了临时表保存中间结果，MySQL在对查询结果排序时使用了临时表。常见于 order by ， group by ， join ，union 操作。
Using index : 发生了索引覆盖 ， 查询时在索引树上取到了需要查询的数据，不需要再进行回行操作。
Using join buffer : 使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接。
Using where : 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
distinct : 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
 

3，mysql索引优化

3.1 索引的代价：

一个索引都会对应一棵B+树，树中每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，所以一个索引也是会占用磁盘空间的。索引是对数据的排序，那么当对表中的数据进行增、删、改操作时，都需要去维护修改内容涉及到的B+树索引。所以在进行增、删、改操作时可能需要额外的时间进行一些记录移动，页面分裂、页面回收等操作来维护好排序。

 

3.2  B+ 树索引实战：

全值匹配： select * from t1 where b = 1 and c = 1 and d = 1;

匹配左边的列： select * from t1 where b = 1;   select * from t1 where b = 1 and c = 1;

这个是用不到索引的： select * from t1 where c = 1;

 

4，mysql锁与事务的隔离级别