Mysql索引是什么“鬼”_云计算

发布时间:2024-07-11 19:35

阅读量:36

架构师（JiaGouX）

我们都是架构师！

架构未来，你来不来？

前言

索引有很多种，hash索引，B树索引，B+树索引，全文索引等。Mysql支持多种存储引擎，多种存储引擎对索引的支持也各不相同。本文探究Mysql为什么使用B+树来作为索引的数据结构，索引的原理以及Sql中索引的优化。

Mysql官方对索引的定义是：索引（Index）是帮助Mysql高效获取数据的数据结构。提取句子主干就是：索引是数据结构。

索引的原理

索引的目的

索引的目的在于提高查询或检索效率。例如我们要在字典中查询“mysql”这个单词，是不是先要查询m开头的单词表，然后在查询第二个字母为y的单词，然后缩小范围继续找，知道找到“mysql”这个单词为止或者查无此词。这就好像我们沿着一个树从树根开始找，沿着主干，树干，到最后的末梢，走了其中的一条路径。这比一个查询一个链表的结构，从头找到尾，在大多数情况下，效率要高得多。

Mysql的索引为什么是B+树

为什么不用普通的二叉树，这里就不必多说了，因为对于大的数据量，二叉树的高度太高，索引的效率低下。这里主要说明为什么不用B树（B-树就是B树），而是用B+树。

B树（B-树）介绍

我们都知道二叉树查询的时间复杂度为Ｏ(logN)，查询效率已经够高了，但为什么还要有B树和B+树呢？答案是磁盘IO。我们都知道，IO操作的效率很低，当有存储的有很大的数据量，查询的时候，我们不可能把全部数据都加载到内存中，只能逐一加载磁盘页，每个磁盘页对应树的节点，造成大量的磁盘IO操作（最坏情况下，磁盘IO操作次数是树的高度），平衡二叉树由于树的高度太大造成磁盘IO读写过于频繁，从而导致效率低下，所以多路查找树-B树/B+树应运而生。

下面是一个三阶的B树（实际中节点元素很多）

B树有以下特点：

B+树

B+树有以下特点：

为什么B+树比B树更适合Mysql索引

Mysql的索引实现

我们知道Mysql有两种常用的存储引擎，MyISAM和InnoDB，这两种存储引擎对索引的实现方式是不同的。

MyISAM索引实现

MyISAM使用B+树作为索引的结构，叶子结点的data域存放的是数据记录的地址。

上图中是以Col1作为主键的MyISAM主索引的示意图。可以看到，组下面一层叶子结点的data域存放的是数据记录的地址。如果我们在字段Col2上建一个辅助索引，那么索引的结构如下：

MyISAM索引检索算法是这样的，首先按照B+树的搜索算法查询索引，如果指定的key存在，则取出data域的值，然后用data域的地址查询数据记录。MyISAM的索引方式也叫“非聚集的”，跟InnoDB的“聚集索引”相区分，因为数据记录和索引不在一起。

InnoDB索引实现

InnoDB的索引实现方式与MyISAM的索引实现方式的区别有两个：

第一，InnoDB的数据文件本身就是索引文件。在InnoDB中，数据文件本身就是按B+树组织的一个索引结构，而且是主索引结构。数据和索引在一起，叶子结点保存了完整的数据记录，这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二，InnoDB辅助索引的data域存储的是相应记录主键的值而不是地址。如图，下图是定义在Col3上的一个辅助索引的示意图。叶子结点存储了col3的值和对应的主键col1的值。

索引优化

墙裂建议使用自增主键

在使用InnoDB作为存储引擎时，如果没有特殊需要，请永远是用一个与业务无关的自增字段作为主键，而且这个字段长度不宜过大。为什么？InnoDB使用聚集索引，数据记录本身存放在主索引（B+树）的叶子结点上，这就要求同一个叶子结点（大小为一个内存页或磁盘页）的数据记录按主键顺序存放，每当一条新的记录插入时，mysql会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。如果使用自增主键，那么每次插入新的记录，记录就会顺序插入到当前节点的下一个位置。这样就会形成一个紧凑的索引结构，每次插入不需要移动已有数据，因此效率很高。如下图：

如果使用非自增主键（例如身份证号或学号这种无序字符串），每次插入主键近似随机，每次记录都要插入到现有索引页的中间的某个位置，这时不得不移动元素来完成插入，增加了开销。如下图：

索引的最左前缀原则

联合索引：mysql可以将多个列按照顺序作为一个索引，这种索引叫做联合索引。

索引的最左匹配原则是：假如索引列分别为A，B，C，顺序也是A，B，C，那么：

这个原则可以结合索引的原理来理解：Mysql索引是B+树这种复合结构，当索引是联合索引，比如【name,age,sex】时，B+树是按照从左到右的顺序建立索引树的。当（张三，20，M）这样的数据来检索时，B+树会优先根据name来确定下一步的搜索方向，如果name相同再比较name和sex，最后得到检索的数据。但当（20，M）这样的数据来的时候，mysql就不知道该查哪个节点，因为建立索引的时候，name就是第一个比较因子，必须先根据name去确定下一步去哪里搜索。当（张三，M）这样的数据来时，可以根据name是“张三”，来确定下一步的搜索，然后再去匹配性别是“M”的数据，因此只能用到联合索引中name这个索引。

其他原则

1、尽量选择区分度高的列作为索引，区分度公式：count(distinct col)/count(*)，表示字段不重复的比例，比例越大，我们扫描的记录数就越少，唯一性的列的区分度为1。这就是为什么不建议在状态，性别这样区分度很小的列上建立索引的原因。

2、索引列在sql语句中不能参与运算，否则会导致索引失效。例如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。应该改成create_time = unix_timestamp(’2014-05-29’);

3、联合索引比单个索引的性价比更高。例如，建立【A，B，C】这个联合索引，相当于建立了【A】，【A,B】，【A，B，C】这三个索引。这就要求我们尽量的扩展索引而不是新建索引，具体情况还需具体分析。

4、频繁进行查询的字段应该新建索引，与其他表进行关联的字段可以考虑新建索引，查询中排序的字段可以考虑建立索引以提高排序的效率（这里举个例子，很多时候查询记录希望按照创建时间倒序返回，通常有人会这样做order by create_time desc，但是如果create_time不是索引，而这个表有自增主键id，那么order by id desc返回结果一样，但是效率会提高）。

Mysql优化

导致sql执行慢的原因

1、硬件问题：如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。

2、没有使用索引或者索引失效。

3、数据过多（分库分表）。

4、服务器或参数设置不当。

分析解决慢sql方法

1、先观察，开启慢查询日志，设置相应的阈值（比如超过3秒就是慢sql），再生产环境跑个一天，看看哪些sql比较慢。

2、explain和慢sql分析，比如sql语句写的不好，没有使用索引或者索引失效，或者sql语句太过复杂，关联查询和嵌套子查询太多等等。

3、Show Profile是比explain更近一步的执行细节，可以查询到执行每一个SQL都干了什么事，这些事分别花了多少秒。

4、找DBA或者运维对Mysql进行服务器的参数调优。

配置优化

基本配置

InnoDB配置

执行计划Explain

准备数据

CREATE TABLE `user_info` (  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,  `name` VARCHAR(50) NOT NULL DEFAULT '',  `age`  INT(11)              DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name_index` (`name`))ENGINE = InnoDB DEFAULT CHARSET = utf8;
 INSERT INTO user_info (name, age) VALUES ('xys', 20);INSERT INTO user_info (name, age) VALUES ('a', 21);INSERT INTO user_info (name, age) VALUES ('b', 23);INSERT INTO user_info (name, age) VALUES ('c', 50);INSERT INTO user_info (name, age) VALUES ('d', 15);INSERT INTO user_info (name, age) VALUES ('e', 20);INSERT INTO user_info (name, age) VALUES ('f', 21);INSERT INTO user_info (name, age) VALUES ('g', 23);INSERT INTO user_info (name, age) VALUES ('h', 50);INSERT INTO user_info (name, age) VALUES ('i', 15); 
CREATE TABLE `order_info` (  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,  `user_id`      BIGINT(20)           DEFAULT NULL,  `product_name` VARCHAR(50) NOT NULL DEFAULT '',  `productor`    VARCHAR(30)          DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`))ENGINE = InnoDB DEFAULT CHARSET = utf8;
 
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA')；INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

执行explain看看，索引使用情况在possible_keys、key和key_len这三列。

分析explain

id相同，执行顺序由上而下

id不同，值越大越先执行

select_type总共有以下几种类型：

1、SIMPLE：表示查询不使用UNION或子查询

2、PRIMARY：表示此查询是最外层的查询

3、SUBQUERY：表示此查询是第一个查询

4、UNION：表示此查询是UNION第二或随后的查询5、DEPENDENT UNION：UNION中的第二个或后面的查询语句，取决于外面的查询6、UNION RESULT：UNION的结果7、DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询，即子查询依赖于外面查询的结果8、DERIVED：衍生，表示导出表的SELECT

table表示查询涉及的表或衍生的表