SQL- 索引

原创已于 2022-09-21 10:54:46 修改 · 3.7k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#学习 #mysql #sql

于 2022-03-07 20:23:35 首次发布

本文深入探讨了SQL索引的概念，包括索引的分类（如普通索引、唯一性索引、主键索引和多列索引）、索引的执行过程及其对查询效率的影响。详细讲解了B树和B+树的特性，以及它们在数据库索引中的应用。此外，还介绍了哈希结构和不同存储引擎（如MyISAM和InnoDB）的索引区别。文章最后讨论了索引设计的原则，强调了最左侧原则，并提醒读者在创建索引时需谨慎，避免过度索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

索引介绍

索引是一种数据结构，创建在表上的，是对数据库表中的一行或者是多列的值进行排序的一种结果，使用索引可以提高查询速度，避免全表扫描
优点：提高查询效率，避免全表扫描
缺点：并非越多越好，过多的索引会导致CPU的使用率居高不小

索引分类：

普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引
唯一性索引：使用unique修饰的字段，值是不能重复，主键索引隶属于唯一性索引
主键索引：使用primary key修饰的字段会自动创建索引
单列索引：在一个字段上创建索引
多列索引：在表中多个字段上创建索引
全文索引：使用fulltext参数设置全文索引，只支持char\varchar和text类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度，只有在MyISAM存储引擎支持
聚集性索引：在InNoDB引擎下建立的索引，将索引及数据存放在一起
非聚集性索引：在MyISAM引擎下建立的索引，将索引和数据分开存储在不同的文件中

索引SQL

1、在创建表时创建索引

create table 表名(
属性名 属性类型 [完整性约束],
...,
属性名 属性类型 [完整性约束]
[unqiue|fulltext|spatial] index|key  [别名] 属性名 （asc,desc）
);

unqiue|fulltext|spatial都是可选参数
unqiue表示是唯一性索引
fulltext表示索引为全文索引
spatial表示索引为空间索引

index|key参数值需要创建索引的属性的名称，属性必须是已经存在的，如果不存在，需要先创建
别名：可以给自己所创建的属性起一个别名，可以是任意的字段
asc|desc是可选参数，asc:表示升序排列 ，desc：表示降序排列

创建普通索引：

mysql> create table index1(
    -> id int,
    -> name varchar(20),
    -> sex varchar(2),
    -> index (name)
    -> );
Query OK, 0 rows affected (0.04 sec)

在这里插入图片描述
创建唯一性索引：

mysql> create table index2(
    -> id int,
    -> name varchar(20),
    -> sex varchar(2),
    -> unique index idx_id(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

在这里插入图片描述

2、在已经存在的表上创建索引
第一种：通过create语法创建索引

create [unqiue|fulltext|spatial] index 索引名 on 表名 (属性[asc|desc])

示例SQL：

create index idx_id on index1(id);

在这里插入图片描述
第二种：通过alter语法创建

alter  table 表名 add  [unqiue|fulltext|spatial] 
index 索引名(属性名 [asc|desc])

alter table index2 add index idx_sex(sex);

在这里插入图片描述

3、删除索引

drop index 索引名 on 表名;

 drop index idx_sex on index2;

在这里插入图片描述

索引执行过程

查询student为例演示索引执行过程：

mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `SID` int(10) DEFAULT NULL,
  `Sname` varchar(20) DEFAULT NULL,
  `Sage` int(10) DEFAULT NULL,
  `Ssex` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

该表中只有主键索引
查询student表中名称为"小"开头的同学的信息：

select * from student where Sname like '小%';

需用explain查看SQL语句的执行计划，
使用示例，在查询的select前面添加上explain关键字

mysql> explain select * from student where Sname like '小%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |    12.50 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

explain关键字执行计划如上所示：
table属性：表示查询的表
possible_keys属性：可能用到的key,显示null表示没有用到索引
key属性：真实执行过程中使用到的索引
rows属性：表名当前查询操作影响数据的行数
Extra属性：备注信息，显示SQL执行过程中使用到的关键信息
从上面的执行计划可知：在student表中查询“小”开头的学生信息，是做了整个表查询操作，把表中所有的行（ rows: 8）全部扫描一遍，才找到需要的”小“开头的学生信息，假如当前表有5百万行数据，要查找”小“开头的学生信息，要搜索的是5百万行数据，这样效率太低
解决方案：添加索引
在这里插入图片描述
添加索引之后，再分析执行计划：

mysql> select * from student where Sname like '小%';
+------+--------+------+------+
| SID  | Sname  | Sage | Ssex |
+------+--------+------+------+
|   15 | 小徐   |   22 | 男   |
|   11 | 小红   |   23 | 男   |
+------+--------+------+------+
2 rows in set (0.01 sec)

mysql> explain select * from student where Sname like '小%';
+----+-------------+---------+------------+-------+-----------------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys         | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+-----------------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name,idx_name_age | idx_name | 63      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+-----------------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

在这里插入图片描述
在添加索引之后，在student表中查询“小”开头的学生信息，发现在执行过程中会有索引选择idx_name,idx_name_age，真正执行过程使用了idx_name，整个数据查询影响的数据行数是2行数，可以发现，使用索引对SQL语句查询效率提升是很大的。
explain关键字，可以查看SQL执行计划，帮忙分析SQL语句能否正确使用索引。

索引底层结构

MySQL支持两种索引：一种是基于B树索引、一种是基于哈希表索引，这两种索引查询效率比较高。
MySQL中B树（B+树）的索引结构

B树特征

大多数的存储引擎都支持B树索引，B树通常意味着所有的值按照顺序存储，并且每个叶子节点到根的距离相同，B树索引能够加快数据访问速度，且效率基本相同
![在这里插入图片描述](https://img-blog.csdnimg.cn/45f0cf1bf1584e6b98e998b69d913cd8.png?x-oss-process=image/watermark,type_d3F5 LXplbmhlaQ,shadow_50,text_Q1NETiBA5Lii5Lii5LiiRHIu,size_20,color_FFFFFF,t_70,g_se,x_16)
B树特征：
1、B树根节点至少包含两个孩子；
2、树中每个节点最多含有m个孩子（m >= 2）;
3、除了根节点和叶节点外，其他每个节点至少含有ceil（m/2）个孩子，ceil为向上取整；
4、所有叶子节点位于同一层（高度相同）；
5、假设每个非终端节点中包含有n个关键字信息，其中

Ki(i=1…n)为关键字，且按顺序升序排列
关键字的个数n必须满足：[ceil(m/2)-1] <= n <= m-1;
非叶子节点的指针P[1],P[2],…,P[M];其中K[1]指向关键字小于K[1]的子树，P[M-1]指向关键字大于K[M-1]的子树，其他P[i]指向关键字属于（K[i-1],K[i]）的子树，比如图中关键字为8这个节点，P1所对应的这个子树，其值均小于8.
查询效率：O（log n）

B+树特征（优于B树）

B+树是B树的变体，基本定义与B树相同，不同点：

非叶子结点的子树指针与关键字个数相同(所以相对于B树，B+树能够存储更多的关键字)
非叶子结点的子树指针P[i]，指向关键字值**[K[i],K[i+1])**的子树，注意区间为左开右闭
非叶子结点仅仅用来索引，数据都保存在叶子结点中 (B+树所有的检索都是从根部开始，检索到叶子结点才能结束，而且非叶子结点不存储数据的话就能存储更多关键字)
B+树相对于B树更矮
所有叶子结点均有一个链指针指向下一个叶子节点

B+树更适合用来做存储引擎索引：
B+树的特点使得磁盘IO的代价更小，B+树的内部节点并没有指向关键字具体指针信息，因此其内部节点相对于B树更小，所以B+树磁盘读写的代价更低
B+树查询效率更加稳定（所有数据都存放在叶子节点，查询效率是O（log n））
B+树更利于数据库的扫描，进行范围查询

哈希结构

在这里插入图片描述
哈希结构和B树结构都不支持范围查询，哈希表对数据不排序，范围查询效率比较低，要查询整个哈希表结构；
哈希结构是通过一定的算法查询数据，在相同情况下，遇到大量的哈希值相等时行性能不一定比B树性能高。

主键索引、辅助索引、聚集索引、非聚集索引

mysql两种主流的存储引擎是MyISAM和INNODB
MyISAM存储引擎相关索引
主键索引：
MyISAM引擎使用B+树作为索引结构，叶子节点的data域存放的是数据记录地址
在这里插入图片描述
辅助索引：
MyISAM中，主键索引和辅助索引在结构上是一样的，B+树构建辅助索引，其叶子节点data域存放的是地址；
主键索引要求key是唯一的，辅助索引的key是可以重复的。

根据以上图可知，在 MyISAM中，按照B+树的搜索算法搜索索引，如果指定key存在，则取出其data域的值，然后以data域的值为地址，读取相应地址数据； MyISAM中将索引和数据分开存储，因此 MyISAM的索引方式称之为非聚集索引；
在磁盘存储上,myisam非聚集索引将关键字和数据分开存储的，对应student表存在三个文件：student.MYD、student.MYI、student.frm

student.frm(存储表的结构)
student.MYD(存储表的数据)
student.MYI(存放表的索引数据)

INNODB存储引擎相关索引
主键索引：
INNODB中的主键索引，叶子节点中，索引关键字和数据是一起存放的。在这里插入图片描述
可以看到索引关键字和数据一起存储在叶子节点上。
辅助索引：
在INNODB的辅助索引中，叶子节点上存放的是索引的关键字和对应的主键，
辅助索引的B+树，先根据索引关键字找到相应的主键，再去主键索引树上找到对应的行记录数据

从索引树上看，INNODB的关键字和数据是存放在一块的，这种索引称之为聚集索引。
在磁盘存储上，INNODB聚集索引存在两个文件，分别是student.frm和student.ibd

student.frm(存储表的结构)
student.ibd(存放索引和数据)

索引执行过程分析

1、单表查询-普通索引
对于student表来进行数据查询，通过SID（主键）来查询的时候，当对主键进行查询操作时，因为使用primary key主键索引（innodb会自动给主键字段创建主键索引树），当通过SID作为where检索条件过滤时，首先从B+树的主键索引快速找到数据，因为是INNODB的存储引擎，将主键索引和数据都存放在一起，找到SID，就可以找到这一行数据，不用把整个表扫描一遍、效率比较高。

mysql> explain select * from student where SID = 15;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

使用Sname字段查询“小徐”

 explain select * from student where Sname = '小徐';
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name,idx_name_age | idx_name | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

根据name字段来查询学生信息，用到了idx_name辅助索引，INNODB的辅助索引叶子节点存储的是辅助索引的值和对应的行的主键（此处对应的主键SID），根据以上的SQL，先查询Sname字段的辅助索引B+树，找到Sname=’小徐‘的节点，获取对应的主键SID=15，然后在拿SID=15去主键索引树上寻找数据，上面SQL总共搜索两次B+索引树。
通过Sname找到对应主键ID

explain select SID from student where Sname = '小徐';
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys         | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name,idx_name_age | idx_name | 63      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

  SQL执行过程中使用了idx_name辅助索引，在辅助索引树上根据Sname来查找对应行的主键，上面select中只查询SID，因此在辅 explain select Ssex from student where Sname = '小徐';

 explain select Ssex from student where Sname = '小徐';
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name,idx_name_age | idx_name | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

查询两次B+树，一次通过Sname辅助索引树查询到主键，在通过主键索引树查询到对应一行数据获取Ssex值

2、单表查询索引执行过程-普通索引+排序或者分组概念
一个订单表，包含用户id，商品id，以及下单时间

CREATE TABLE `orderlist` (
  `userid` int(11) NOT NULL,
  `productid` int(11) NOT NULL,
  `date` datetime DEFAULT NULL
) ENGINE=InnoDB

查询用户id=1且按照日期升序排序的结果；

select * from orderlist where userid=1 order by date;
+--------+-----------+---------------------+
| userid | productid | date                |
+--------+-----------+---------------------+
|      1 |         1 | 2022-03-16 15:04:23 |
|      1 |         2 | 2022-03-16 15:08:55 |
|      1 |         5 | 2022-03-16 15:09:23 |
+--------+-----------+---------------------+
3 rows in set (0.01 sec)

分析SQL执行计划：

mysql> explain select * from orderlist where userid=1 order by date;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | orderlist | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

在这里插入图片描述
通过explain分析，该SQL执行时查询了整张表，效率比较低，对数据查询userid=1之后的所有数据记录，还要按照data字段进行升序排序，Extra出现了fileSort:得到的所有的结果集，对结果集进行文件排序，出现了该fileSort，SQL语言性能比较差，需要进行优化；

给userid和date分别创建索引

create index idx_userid on orderlist(userid);
create index idx_date on orderlist(date);

重复执行SQL，查看执行计划，看有什么不同
在这里插入图片描述
在SQL执行过程中，并没有选择合适的索引，通过强制指定索引：force index(索引名)

在使用force强制索引，如果强制使用idx_userid，SQL执行查询在辅助索引上找到关键字找到主键，然后在拿主键id，到主键索引树上去搜索数据，搜索到数据之后，需要根据date进行文件排序，效率是比较低的；给定userid的索引，给定date索引为什么出现filesort和全表扫描。
注意：一个SQL查询操作，一次执行使用一个索引，因此选用某一个索引，其他索引不在起作用
继续优化，需要创建userid和date的联合索引，由于userID是where的过滤条件，因此联合索引userID在前，date在后；

 create index idx_userid_date on orderlist(userid,date);

再来查看SQL执行计划：
在这里插入图片描述
给定userid和date创建联合索引后，不会出现fileSort，根据userId=1查询辅助索引树，找到的数据也已经按照date排好序，然后在去主键索引树去查找整行数据就可以啦，效率比较高，
之所以还要使用force index（idx_userid_date）是防止SQL优化器优化索引的时候，因为测试的数据量比较小，有时候使用索引不一定比整表的效率高。

3、多表查询：连接查询索引的执行过程以及优化
使用多表查询连接的时候，MySQL会首先判断那个表小，表小主要指的是数据行数少。小表无论如何都是整表遍历的，是不使用索引的，但是大表就需要用到索引。所以在连接查询时，小表总是需要整表搜索的，建索引是没有用的，大表创建索引是能够提高查询效率的。
小表是决定查询的次数，大表决定查询时间。

 explain select * from tb1 inner join tb2 on tb1.id=tb2.id

小表没有用索引，大表用到了索引。
在这里插入图片描述
在该表中tb2有3条数据，tb1有8表数据，所以tb2是小表，进行全表扫描，大表是tb2,根据索引进行过滤查询
通过执行计划：先查询的是tb2的整表，然后在tb1的表的索引树上查找

explain select * from tb1 inner join tb2 on tb1.id=tb2.id where tb1.name="li"

tb1表根据where条件过滤后，符合条件的数据然后才比较大小表，tb1是有8条数据，根据检索条件过滤完剩1条数据，tb2是3条数据，此时小表就变成了tb1,tb2就是大表，tb2上的索引就可以用到了。
在连接查询中，大小表的角色是不一定的，没有where子句，那么就按照表的行数来决定大小表，如果有where子句，那么就按照条件过滤后的函数来确定大小表。

左前缀原则

MySQL中索引可以以一定的顺序引用多例，这种索引称之为联合索引，假如User表中name和city加联合索引就是（name,city）,从而左前缀原则指的是，如果查询的时候条件精确匹配索引的左边连续一列或者几列则可以用到索引。

select * from user where name=XX and city=XX;  //命中索引的
select * from user where name=XX ; //命中
select * from user where city=XX ; //不能命中索引
select * from user where city=XX and name=XXX; //命中索引的