数学建模社区-数学中国

标题: mysql索引和explain的详解 [打印本页]

作者: 杨利霞 时间: 2020-5-3 15:46
标题: mysql索引和explain的详解

mysql索引和explain的详解索引原理分析

索引存储结构
索引是在存储引擎中实现的，也就是说不同的存储引擎，会使使用不同的索引
MyISAM和InnoDB存储引擎：只支持B+ TREE索引，也不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B树图示

B树是为了磁盘或其它存储设备设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。多叉平衡。

B树和B+树的区别：
B树和B+树的最大区别在于非叶子节点是否存储数据的问题

在结构上：
（1） B树是非也只节点和叶子节点都会存储数据。
（2） B+树只有叶子节点才会存储数据，而且数据都是在一行上，而且这些数据都是指针指向的，也是有顺序的。

在性能上：
（1）对于B-树相对于B+数据，B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更高。B-Tree在单条数据读写有着更强的性能。
（2）但由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。这个也决定当连表查询的时候mysql比起mongo有显著的优势。更重要的是由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。

聚集索引（MyISAM）
B+树叶节点只会存储数据行（数据文件）的指针，简单来说数据和索引不在一起，就是聚集
索引。
聚集索引包含主键索引和辅助索引都会存储数据指针的值。

辅助索引（次要索引）
在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,
而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示

同样也是一颗 B+Tree,叶子节点中保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

聚集索引（InnoDB）

主键索引（聚集索引）的叶子节点会存储数据行，也就是说数据和索引是在一起，这就是聚集索引。
辅助索引只会存储主键值
如果没有没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。

主键索引
1.InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以
唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。

mysql创建索引的时候和用法与索引息息相关，要建立合适的索引和理解一些索引的执行计划，就需要认识索引的结构。

explain的详解

参数说明：
explain后会出现十列数据，下面将介绍这下面的十列数据。

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

先附上案例表：

CREATE TABLE `taddr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(100) DEFAULT '',
  `province` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `addr_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `addr_id`(`addr_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

CREATE TABLE `type_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` varchar(255) DEFAULT '[]',
  `name` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)，
  INDEX `name_time_index`(`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

一、id
每个 SELECT语句都会自动分配的一个唯一标识符.
表示查询中操作表的顺序，有三种情况：
id相同：执行顺序由上到下
id不同：如果是子查询，id号会自增，id越大，优先级越高。
id相同的不同的同时存在
id列为null的就表示这是一个结果集，不需要使用它来进行查询。

二、select_type

查询类型，主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询

2.1、simple
表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple

EXPLAIN select * from user

EXPLAIN select u.id,u.addr_id,a.* from user u inner join taddr a on u.addr_id=a.id

2.2 primary
一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type为primary。

explain select * from taddr t inner join (
select addr_id from user ) u on t.id=u.addr_id

explain select * from user u where u.addr_id =1q
union all
select * from user u where u.addr_id =2

2.3 subquery
除了from字句中包含的一查询外，其他地方出现的子查询都可能是subquery

2.4 dependent subquery

与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

explain select u.name,(select t.province from taddr t where u.addr_id=t.id) from user u

2.5 union
union连接的两个select查询，第⼀个查询是PRIMARY，除了第一个表外，第二个以后的表select_type都是union

三、table
显示的查询表名，如果查询使用了别名，那么这里显示的是别名
如果不涉及对数据表的操作，那么这显示为null
如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

四、type

依次从好到差：
system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，
index_subquery，range，index_merge，index，ALL

除了all之外，其他的type都可以使⽤到索引，除了index_merge之外，其他的type只可以用到一个索引

4、1 system
表中只有一行数据或者是空表。

4、2const
使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

4、3 eq_ref
关键字:连接字段主键或者唯一性索引。
此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较较操作通常是 ‘=’, 查询效率较高.

EXPLAIN select u.id,u.addr_id,a.* from user u inner join taddr a on u.addr_id=a.id

4、4 ref6 b; Z2 p8 |6 U5 ~) ^: {* H6 f$ i
针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。

EXPLAIN select u.id,u.addr_id,a.* from taddr a left join user u on u.addr_id=a.id

4.5 fulltext
全文索引检索，要注意，全文索引的优先级很高，若全高索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

4、6 unique_subquery
用于where中的in形式子查询，子查询返回不重复值唯一值

4、7 index_subquery
用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

4、8 range
索引范围扫描，常用于使用>,<,is null,between ,in ,like等运算符的查询中。

explain select * from type_time a inner join (
select id from type_time where name =‘2’ and time in (‘2’,‘3’,‘4’) ) b on a.id=b.id

4、9 index
键字：条件是出现在索引树中的节点的。可能没有完全匹配索引。
索引全表扫描，把索引从头到尾扫一遍，常用于使用索引列就可以处理不需要读取数据文件的查询、可以使使用索引排序或者分组的查询。

explain select * from user group by addr_id

explain select addr_id from user

4、10 all
这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

五、possible_keys

此次查询中可能选用的索引，一个或多个

六、key
查询真正使使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

七、key_len

用于处理查询的索引长度度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查
询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的，这里不会计算进去。留意下这个列的值，算下你的多列索引总长度就知道有没有使用到所有的列了。
另外，key_len只计算where条件用到的索引长度，而排序和分组就算使用到了索引，也不会计算到key_len中。
explain select id from type_time where name =‘2’ 用到长度303

explain select id from type_time where name =‘2’ and time in (‘2’,‘3’,‘4’) 用到长度 1071

八、ref
如果是使用的常数等值查询，这里会显示const
如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

九、rows
这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB使用了MVCC并发机制）

十、extra
这个列包含不适合在其他列中显示但十分重要的额外的信息，其中比较常见有一些：

10、1 using temporary
表示使用了临时表存储中间结果。
MySQL在对查询结果order by和group by时使用临时表
临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，
used_tmp_table，used_tmp_disk_table才能看出来。

explain select * from user u inner join taddr t on u.addr_id=t.id GROUP BY t.id

10、2 using filesort
排序时无法使用到索引时，就会出现这个。常用于order by和group by语句中

说明MySQL会使用个外部的索引排序，而不是按照索引顺序进行读取。
MySQL中无法利索引索引完成的排序操作称为“文件排序“

10、3 using index
查询时不需要回表查询，直接通过索引就可以获取查询的数据。
表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免回表访问数据行，效率不
错。
如果同时出现Using Where ，说明索引被用来执行查找索引键值
如果没有同时出现Using Where ，表明索引用来读取数据来执行查找动作。

这里对索引的原理和explain做了一些介绍，需要索引需要建立之后对其改变查询方式可能会更能深刻理解 InnoDB 使用覆盖索引和非覆盖索引造成区别。这也是建立索引和使用sql需要特别考虑的问题。
————————————————
版权声明：本文为CSDN博主「筏镜」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fajing_feiyue/article/details/105616629

20.png (13.61 KB, 下载次数: 398)

欢迎光临数学建模社区-数学中国 (http://www.madio.net/)