MySQL 执行计划explain与索引数据结构推演过程是什么

了解MySQL 执行计划explain与索引数据结构推演过程是什么？这个问题可能是我们日常学习或工作经常见到的。希望通过这个问题能让你收获颇深。下面是小编给大家带来的参考内容，让我们一起来看看吧！先建好数据库表，演示用的MySQL表，建表语句：后续执行计划，查询优化，索引优化等等知识的演练，基于以上几个表来操作。要进行SQL调优，你得知道要调优的SQL语句是怎么执行的，查看SQL语句的具体执行过程，以加快SQL语句的执行效率。可以使用explain + SQL语句来模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。关于explain可以看看官网介绍。字段id，select_type等字段的解释：idselect查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序。id号分为三类：这个查询，用explain执行一下，id序号都是1，那么MySQL的执行顺序就是从上到下执行的。这个例子的执行顺序是先执行id为2的，然后执行id为1的。还是上面那个例子，先执行id为2的，然后按顺序从上往下执行id为1的。select_type主要用来分辨查询的类型，是普通查询还是联合查询还是子查询。这条语句的select_type包含了primary和union这条SQL执行包含了PRIMARY、DEPENDENT SUBQUERY、DEPENDENT UNION和UNION RESULT举例：例如：table对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集。typetype显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想到的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下。访问的类型有很多，效率从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般情况下，得保证查询至少达到range级别，最好能达到ref通常，可以通过添加索引来避免ALL官网上举例如下：SELECT * FROM tbl_name
WHERE key_column = 10;SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);value IN (SELECT key_column FROM single_table WHERE some_expr)value IN (SELECT primary_key FROM single_table WHERE some_expr)SELECT * FROM ref_tableWHERE key_column=expr OR key_column IS NULL;SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_p开发云主机域名art1=other_table.column
AND ref_table.key_column_part2=1;SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;例如：possible_keys显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用key实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠key_len表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好ref显示索引的哪一列被使用了，如果可能的话，是一个常数rows根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好extra包含额外的信息想要了解索引的优化方式，必须要对索引的底层原理有所了解。索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。表越大花费的时间越多，如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。既然MySQL索引能帮助我们快速查询到数据，那么它的底层是怎么存储数据的呢？hashhash表的索引格式hash表存储数据的缺点：事实上，MySQL存储引擎是memory时，索引数据结构采用的就是hash表。二叉树二叉树的结构是这样的：二叉树会因为树的深度而造成数据倾斜，如果树的深度过深，会造成io次数变多，影响数据读取的效率。AVL树 需要旋转，看图例：红黑树 除了旋转操作还多了一个变色的功能（为了减少旋转），这样虽然插入的速度快，但是损失了查询的效率。二叉树、AVL树、红黑树 都会因为树的深度过深而造成io次数变多，影响数据读取的效率。再来看一下 B树B树特点：图例说明：每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为 16 和 34，P1 指针指向的子树的数据范围为小于 16，P2 指针指向的子树的数据范围为 16~34，P3 指针指向的子树的数据范围为大于 34。查找关键字过程：1、根据根节点找到磁盘块 1，读入内存。【磁盘 I/O 操作第 1 次】2、比较关键字 28 在区间（16,34），找到磁盘块 1 的指针 P2。3、根据 P2 指针找到磁盘块 3，读入内存。【磁盘 I/O 操作第 2 次】4、比较关键字 28 在区间（25,31），找到磁盘块 3 的指针 P2。5、根据 P2 指针找到磁盘块 8，读入内存。【磁盘 I/O 操作第 3 次】6、在磁盘块 8 中的关键字列表中找到关键字 28。由此，我们可以得知B树存储的缺点：官网：Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees不要误会，其实MySQL索引的存储结构是B+树，上面我们一顿分析，知道B树是不合适的。mysql索引数据结构—B+TreeB+Tree是在BTree的基础之上做的一种优化，变化如下：1、B+Tree每个节点可以包含更多的节点，这个做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将数据范围变为多个区间，区间越多，数据检索越快。2、非叶子节点存储key，叶子节点存储key和数据。3、叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高。B+树存储查找示意图：注意：在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。由于B+树叶子结点只存放data，根节点只存放key，那么我们计算一下，即使只有3层B+树，也能制成千万级别的数据。假设有这样一个表如下，其中id是主键：我们对普通列建普通索引，这时候我们来查：由于name建了索引，查询时先找name的B+树，找到主键id后，再找主键id的B+树，从而找到整行记录。这个最终会回到主键上来查找B+树，这个就是回表。如果是这个查询：就没有回表了，因为直接找到主键id，返回就完了，不需要再找其他的了。没有回表就叫覆盖索引。再来以name和age两个字段建组合索引(name, age)，然后有这样一个查询：这时按照组合索引(name, age)查询，先匹配name，再匹配age，如果查询变成这样：直接不按name查了，此时索引不会生效，也就是不会按照索引查询—这就是最左匹配原则。加入我就要按age查，还要有索引来优化呢？可以这样做：可能也叫谓词下推。。。t1有10条记录，t2有20条记录。我们猜想一下，这个要么按这个方式执行：先t1,t2按id合并（合并后20条），然后再查t1.name,t2.name或者：先把t1.name,t2.name找出来，再按照id关联如果不使用索引条件下推优化的话，MySQL只能根据索引查询出t1,t2合并后的所有行，然后再依次比较是否符合全部条件。当使用了索引条件下推优化技术后，可以通过索引中存储的数据判断当前索引对应的数据是否符合条件，只有符合条件的数据才将整行数据查询出来。感谢各位的阅读！看完上述内容，你们对MySQL 执行计划explain与索引数据结构推演过程是什么大概了解了吗？希望文章内容对大家有所帮助。如果想了解更多相关文章内容，欢迎关注开发云行业资讯频道。

相关推荐: 简单的这些不为人知的小技巧你都知道吗？

　　随着开发云主机域名科技的发展，手机在生活越来越重要，微信渐渐成为我们生活中沟通的重要媒介。现在很多的中老年人也开始使用微信了，但是微信上有很多功能，他们都不会使用，如果我们不在他们的身边，他们又想使用微信的话，那么该怎么办呢？下面将方法分享给大家！ 　　一…