mysql数据库详解（三）：索引与sql优化

关注

原创最新推荐文章于 2026-03-23 16:19:46 发布 421 阅读 AI 写同款· GEO 优化 ›

MySQL进阶【四】—— MySQL索引优化实战示例表 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp NO 阅读详情

作为一个稀有的Java妹子，所写的所有博客都只是当作自己的笔记，留下证据自己之前是有用心学习的～哈哈哈哈（如果有不对的地方，也请大家指出，不要悄悄咪咪的不告诉我）

一、索引是什么

索引是一种可以高效查询的数据结构。数据库的查询是经常被使用的操作，我们往往希望查询的效率高，如果是按照顺序查找的方法，当数据量变大的时候，查询效率就会变得非常低，所以mysql的开发者设计了索引来优化查询效率。

二、索引的结构

索引根据数据库的引擎可以分为很多种，B-tree，HASH，全文索引等，mysql主要是使用了B-tree，所以这篇主要介绍B-tree结构。

B-tree结构

从名字上可以看出这是一种树型结构，有根节点、叶子节点、非叶子节点，所有的节点存放key和data，key就是索引的值，data存放数据，key的左右都有指针。
在这里插入图片描述

B+tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。
两者的不同点在于B+tree只有叶子节点有data，其他节点只有key;叶子节点不存储指针
在这里插入图片描述

三、mysql不同的存储引擎索引的区别

1.MyISAM

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。主索引和辅助索引的结构是一样的，只是主索引key要求唯一，辅助索引不要求。MyISAM的索引是非聚集的，数据文件和索引文件是分开存放的，先根据索引找到存放数据的地址，再通过地址获取数据。

2.InnoDB

InnoDB引擎也是使用B+tree作为索引结构，不过叶节点的data域存放的是一行完整的数据，并且数据文件本身就是索引文件，也被叫做聚集索引。
在这里插入图片描述

以上两种索引结构叶子节点都有指针，从左至右顺序的连接起来。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
InnoDB的辅助索引的data域存放的是主键，所以根据辅助索引查询时，是先根据辅助索引查询到主键索引，再根据主键索引查询数据。

四、InnoDB中索引的分类

1、主键索引
2、唯一索引
3、普通索引
4、全文索引（没使用过）

因为InnoDB是聚集索引，所以就算不给表建主键也会默认添加，所以一般来讲都会给表设置一个自动递增的主键。

1.联合索引

刚才列举的所有索引都可以建联合索引，联合索引只是涉及多个列，且满足最左原则。比如有这样一个联合索引，(a，b，c)那么只有 a，ab，abc这三种才会使索引生效，b，bc不会使用到索引。因为InnoDB是聚集索引，所以不论索引是在建表时建立的还是建表后数据量大了建立的，数据文件和索引文件都只有一个，即数据是按照索引来存放的，联合索引就是按照a,b,c的顺序来存放的，如果想要用索引，那么第一个匹配的必须是a,然后再是b,最后是c，如果一开始是b,那么根本不会匹配上。

因为mysql有查询优化器，所以就算联合索引是a,b,c，如果where 条件是 b,c,a也会用到联合索引。

2.explain

怎样知道查询有没有用到索引呢，通过explain就可以查到

explain select * from tb_system_user_log where user_id = 13424256

在这里插入图片描述
1、id:sql里每条语句执行的先后顺序，数字越大越先被执行（子查询的情况）
2、select_type：显示sql的复杂程度
3、table:表
4、type:显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。

system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
all Full Table Scan 将遍历全表以找到匹配的行

5、possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
6、key：实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
7、key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。在不损失精确性的情况下，长度越短越好。
8、ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
9、rows：显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。
10、extra：

关键字	解释
Distinct	在select部分使用了distinc关键字一旦mysql找到了与行相联合匹配的行，就不再搜索了
no tables used	不带from字句的查询或者From dual查询
Using filesort	看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来查询如何对返回row(行)排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中(这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序)
Using index	查询时不需要回表查询，直接通过索引就可以获取查询的数据。，这发生在对表的全部的请求列都是同一个索引的部分的时候。说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。例如select id from tb_user where id = 1 ，id是主键
Using temporary	看到这个的时候，查询需要优化了。表示使用了临时表存储中间结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上(常用于GROUP BY 和 ORDER BY操作中)。
using where	表示MySQL将对存储引擎层提取的结果进行过滤，过滤条件字段无索引。例如：select * from test where age > 30; 所以，Using where本身其实和是否使用索引无关，它表示的是Server层对存储引擎层返回的数据所做的过滤。当然该过滤可能会回表，也可能不会回表，取决于查询字段是否被索引覆盖。

五、索引失效

1、违背最左原则
2、隐式转换导致索引失效，比如索引字段的类型是varchar,但是却使用了其他类型
select * form tb_test where id = 121;不使用
select * form tb_test where id = “121”;使用索引
3、对索引列进行运算导致索引失效,运算包括(+，-，*，/，! 等)
select * from test where id-1=9;不使用
select * from test where id=10;使用
4、使用 <> 、not in 、not exist、!=
5、like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
6、字符型字段为数字时在where条件里不添加引号.
7、当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
8、判断字段是否为空使用=或者！=,应该使用is null或者is not null
9、where中使用OR来连接条件，导致引擎放弃索引来执行全表扫描，可以使用UNION进行合并查询：select id from t where num = 30 union select id from t where num = 40;