简单理解MySQL索引
|
我想大家应该多多少少做过一些慢sql的优化吧，通常会是公司的leader或者是运维人员扔给你一些执行速度慢的sql语句，然后让你去优化。当遇到慢sql的时候，大家的第一直觉是不是查看索引有没有创建，索引是不是有命中等等。那这个索引到底是什么东西，为什么能提高数据的查询速度呢？那么下面就简单的介绍一下索引以及索引底层的数据结构等。 什么是索引 索引是帮助Mysql高效获取数据的一种排好序的数据结构。通常由数据表中的一列或多列组成，可以用来快速查询数据表中的数据，从而避免扫描全表。索引好比书的目录，通过目录可以快速定位到我们需要的章节，从而加快查找效率。对于系统能有良好的运行，索引可以说非常关键，尤其是在表中的数据量越来越大时，索引的影响愈发重要。当然，索引也并非全是优点，也有缺点的存在： 优点： 缺点： 索引的数据结构 索引按底层数据结构类型分为Hash索引和BTree索引。Hash索引底层基于Hash表实现，BTree索引底层是基于多路平衡搜索树实现的。那为什么不使用二叉树或者红黑树这些呢？下面就来简单的分析一下为什么要使用BTree和为什么不使用二叉树。先给大家安利一个网站叫Data Structure Visualizations，这个网站可以动态演示各种数据结构的动画，比较方便理解。 二叉查找树 二叉树这里就不多介绍了，就是左节点的值小于右节点。那索引为什么不使用二叉树呢。假设有一张文章表document。其中id设为主键自增。那么这个时候像数据表document中添加数据，索引的底层数据结构使用的是二叉树。那么就会出现下图这种情况。 这是因为id设置了自增，二叉树竟然退化成了链表。要知道数据和索引都是存储在磁盘上呢，一旦退化成链表，那和全盘扫表没什么区别。如果由几十万的数据，我要查找id靠后的数据，那就意味着要进行几十万次的IO操作。这个效率不能在低下了。所以，这就是为什么不使用二叉树的原因。 红黑树 红黑树在二叉树的基础上，当插入节点时会做一次平衡。这样虽然可以避免二叉树退化成链表的情况，但是当数据量增大时，数的高度也随之变大。最然说红黑树的查找效率比较高，但是随着数据量增大，树的高度是不可控的。如果存在百万的数据，要查找叶子节点数据，这个效率同样低下。所以mssql 按关键字排序，索引没有使用红黑树作为数据存储结构。 Hash Hash索引底层的实现是通过Hash表来实现的，mysql会计算索引的hash值，根据hash对数组进行填充。mysql对于Hash冲突采用链地址发，和java中的hashmap一样，只不过不会转红黑树。hash索引的等值查找效率比较高，只要进行一次hash运算就可以定位到目标索引的位置，但是由于数据结构的限制，无法进行范围查询和大小比较等。如果数据量越来越大，那么hash冲突的几率也会越来越大。 B-Tree 上面有提到红黑树的查找效率比较好，但是树的高度不可控。那么有什么办法让树的高度尽量可控呢，此时就有的红黑树的一个变种B-Tree。B-Tree的数据结构与红黑树类似，B-Tree的每个节点都存储着多个有序的索引数据，这种也叫做一个数据页。在InnoDB存储引擎中，每个数据页大概有16kb,这个数据页大小是可以调整的，但是在非必要的情况下不建议修改这个大小。 B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据，有可能是索引行所在的磁盘地址，也有可能是索引所在行的其他数据列。在整个B-Tree上面，不会存在相同的key。如果基于B-Tree查找某个数据，IO的次数相对于其他其他数据结构要少很多。 B+Tree B+Tree是在B-Tree的基础上做了一些变动。B+Tree的每个非叶子节点都去掉了索引的data,data数据统一放到了叶子节点中。这样做的好处是为了让非叶子节点的数据页存储更多的索引数据，上面提到了每个数据页的大小在16kb左右，如果在非叶子节点带上索引的data数据，那么同一大小的数据页B-Tree存储数据能力要小于B+Tree。同样的数据量，B-Tree树的高度可能要高于B+Tree。初次之外，B+Tree在叶子节点使用了指针，相邻的节点用指针相连，提高了数据的区间连续性。所以B+Tree是索引底层实现的最优选择。 数据页的大小为16KB，一般表的主键类型为int（占4字节）或bigint（占8字节） 如果用bigint作为主键，指针类型(磁盘地址)一般为6字节，也就是说非叶子节点所在一个页中大概存储16KB/(8B+6B)=1170，也就是说，一个数据页可以存储1170个索引和指针信息。而叶子节点的一个页大概存储16KB/1KB（索引+数据大概为1kb）= 16。也就是说一个深度为3的B+Tree索引可以维护1170 * 1170 * 16 = 2000多万条记录。所以当Mysql数据大于2000多万查询性能会下降。 B-Tree和B+Tree的区别对比 在结构方面： 在系统方面： Hash和B+Tree的区别对比索引如何定位数据 在B-Tree的每个节点和B+Tree叶子节点都带有索引的data,这个data在不同的存储引擎里面存放的数据是不一样的。比如主键索引在常见的InnoDB存储引擎和MyISAM存储引擎中，data存储的分别是该索引行的数据和索引行的磁盘地址。MyISAM存储引擎下，一张表在文件内可以分为.frm , .MYD , . MYI三种文件格式，frm文件存储数据表的结构，MYD文件存储表的数据，MYI文件存储表的索引。在MYI索引文件中存储了索引行的磁盘文件地址，当通过索引定位数据时，先查找到数据的地址，根据地址在定位到数据。 在InnoDB存储引擎下，一张表在文件内可分为.frm，.idb两种文件格式，同样frm文件存储表的结构。但是与MyISAM不同的是，InnoDB将索引和数据绑定到了一起并存储到了idb文件中。当要通过索引定位数据时，找到索引也就表示找到了数据行，这个效率要比MyISAM高很多，减少了通过磁盘地址在定位数据的操作。这种将索引和数据绑定在一起的索引也就是常说的聚簇索引。反之，为非聚簇索引 上面讲述的都是主键索引，B+Tree索引字段都是单值主键。那么二级索引(除主键索引外的索引)是如何组织索引数据的呢？比如将document表中的title和content列设置为复合索引，那么索引在排序时，先按照title进行排序，再次按照content进行排序。如果title相同，那么就会按照content进行排序。但是和主键索引有区别的时，二级索引的叶子节点并不是存储索引行的数据，而是存储索引行的主键数据。当通过二级索引查找数据时，先要定位到叶子节点并根据叶子节点的主键，再次到主键索引上进行数据查找，这个过程也叫做回表。 Explain执行计划 当我们拿到一条满sql后，如何确定sql满在哪里呢？那么就可以使用Explain来分析慢sql语句。比如像索引是否命中，表的读取顺序等。使用Explain可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈。在 select 语句之前增加 explain 关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，并不会真正的执行这条SQL。比如有三张表：文章表document_info,标签表label_info和关联表document_label。分析一个简单的查询语句 可以看到打印的结构有id,select_type,table,partitions,type等一些字段。那么这些字段都有什么意义呢？这些在mysql的官网上面有所介绍EXPLAIN Output Columns。 id id的值表示sql语句中select语句的执行序号，这个值可以为NULL,但是只有在使用union联合查询时，id会出现NULL。id的值越大表示select语句执行的优先级就越高。 select_type select_type的值有很多个，比如SIMPLE，PRIMARY，UNION等。表示select语句的查询复杂程度。 类型 描述 SIMPLE 单纯的一个select查询，没用使用子查询或者union PRIMARY 复杂查询中，最外层的查询 UNION union 查询中的第二个以及以后的select语句 DEPENDENT UNION union 查询中的第二个以及以后的select语句，取决于外部查询 UNION RESULT 将union查询结果作为临时表，并通过临时表查询结果的select语句 SUBQUERY SQL中的子查询(不包含from中的子查询) DEPENDENT SUBQUERY 在WHERE列表中包含了子查询，子查询基于外层 DERIVED 派生表。一般在from后，比如select * from (select * from xxx) xxx UNCACHEABLE SUBQUERY 无法使用缓存的子查询语句 UNCACHEABLE UNION 无法使用缓存的union查询语句 type type列表示sql的连接类型，即mysql如何查询数据表的行，查询行的大概范围。常见的连接类型查询效率由最佳到最差可以分为：system>const>eq_ref>ref>range>index>ALL。一般情况下的查询sql要保证在range级别，最好达到ref。 possible_keys 在分析执行sql时，可能会用到的索引。如果经过分析使用索引的查询效率可能没有不使用索引查询的效率低时，那么在最终执行sql时就不会使用索引。 key 执行sql时，最终实际用到的索引。如果没有用到索引，则显示为NULL。 key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，这个长度是数据库字段类型的长度，并非实际内容长度。比如document_info的id主键设置为int类型，int类型占4字节，所以key_len为4。 ref 显示哪些列或常量与键列中指定的索引进行比较，从而从表中选择结果行。 rows 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数 extra 该列表示sql分析时的额外字段信息，该字段值有很多，但常见的有Using index，Using where，Using index condition等。 索引失效场景 在sql查询时，如果使用+,-,*,/,!=时，会造成索引失效。 竟然有人说使用not in()，or会导致索引失效，其实没有那么绝对，mysql优化器会根据当前表的情况和回表次数判断需不需要走索引。 在sql查询时，如果使用像left，length，date等一下函数时，会导致索引失效。这是因为B+Tree索引数据与使用函数后的数据可能不一致。 比如将document_info中title设为索引字段，执行like模糊查询语句时，导致了索引失效并进行全表扫描。那为什么使用like 'xx%'就可以使用索引呢？这个还是因为和B+Tree有关，like 'xx%'前部分字段在索引树上仍然可以保证有序。 如果在日常开发中经常会用到like '%xx%'，但是还是想让使用索引改怎么优化呢？这个时候可以使用覆盖索引，让查询的字段，能在一个索引树上获取就可以。比如将document_info中的title和content设置为复合索引，在执行sql时就可以命中索引。 顾名思义，在复合索引中，最左侧的字段优先匹配。因此，在创建复合索引时，where子句中使用最频繁的字段放在组合索引的最左侧。而在查询时，要想让查询条件走索引，则需满足：最左边的字段要出现在查询条件中，如果遇到范围查询(>、 （编辑：92站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
