文章目录
一、认识索引
1.1 什么是索引
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
比如常见的数组,它的索引就是它的下标,我们可以通过索引直接找到对应的数据。
索引的出现就是为了提高数据库查找的性能,而且创建索引也是很简单的。但是增加索引会带来一下其他的问题,就像在数组中间插入数据,需要移动后面所有的数据,甚至还可能要扩容。数据库索引也类似,比如插入、更新、删除一个数据的效率会降低,因为要修改索引,会增大IO交换量。
索引的价值:提高一个海量数据的检索速度。
常见索引分为:
- 主键索引(primary key)
- 唯一索引(unique)
- 普通索引(index)
- 全文索引(fulltext)–解决中子文索引问题。
1.2 MySQL与磁盘交互基本单位
而 MySQL
作为一款应用软件,可以想象成一种特殊的文件系统。它有着更高的IO场景,所以,为了提高基本的IO效率, MySQL
的InnoDB
引擎IO的基本单位是 16KB
,也就是说每次和磁盘交互的单位是 16KB
。
这个基本数据单元,在 MySQL
这里叫做page
mysql> system clear;
mysql> show global status like "innodb_page_size";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
- 为什么交互的基本单位为Page(页)
- 由冯诺依曼体系可知,IO的时间远远大于CPU运算的时间,如果每次要那个数据就加载那个数据,这样IO的次数就提高了,程序所需要的时间就会提高,效率也就降低了。因此,mysql提高效率的本质是减少IO。
- 根据局部性原理,连续查找的数据一般是在一个小范围的,因此我们进行IO的时候就加载一个page到内存之中,如果下次数据在这个区间之间,就不需要进行IO,提高了程序的效率。
-
MySQL会预先向内存申请一个大空间
Buffer Pool
,用来和磁盘交互,存放page
。 -
显然对于海量数据,MySQL同时加载多个
page
,那么就需要对多个page
进行管理。在页之间是通过双向链表连接起来的,页内的数据是通过单向链表连接的,这样插入和删除的效率高。 但是链表的查找效率是非常低的,这就要通过目录来解决了。 -
目录,就是每本书都会有的,如果我们知道想要查找的内容主标题,就可以通过目录快速定位到某一个章节,这样就提高链表的查询效率。
1.3 提高单个page的查找速率
索引是通过在一个page
内部建立页目录的方式来提高页内的查找效率。
首先创建一个简单的表,设置有主键,数据的插入是无序的,但查询结果却是按主键排序的,这是为什么?
mysql> select * from user; --发现竟然默认是有序的!是谁干的呢?排序有什么好处呢?
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 56 | 欧阳锋 |
| 2 | 26 | 黄蓉 |
| 3 | 18 | 杨过 |
| 4 | 16 | 小龙女 |
| 5 | 36 | 郭靖 |
+----+-----+-----------+
5 rows in set (0.00 sec)
显然对于这几个数据,它们是存储在同页内。数据之间是通过主键关联起来的,所以为了提高查找效率,MySQL会依赖主键自动排序。
对于有主键索引的数据,可以通过主键建立页目录,类似于书目录,这是用空间换时间的做法。
对于上面的数据建立页目录,如下:
这样查找id = 4
的数据就可以减少一半的搜索次数。所以MySQL使用主键索引自动排序的目的就是为了方便建立目录。
1.4 提高多个page的查找速率
其实解决方法类似,给所有的page也带上目录,用一个page
来充当目录,里面的每一项指向一页。
- 使用一个目录项来指向某一页,而这个目录项存放的就是将要指向的页中存放的最小数据的键值。
- 和页内目录不同的地方在于,这种目录管理的级别是页,而页内目录管理的级别是行。
- 其中,每个目录项的构成是:键值+指针。
- 加入一个页无法存放所有的目录,需要扩展出新的页来充当页目录,对于这两个页目录,同样需要一个页目录来管理,如下图。
存在一个目录页来管理页目录,目录页中的数据存放的就是指向的那一页中最小的数据。有数据,就可通过比较,找到该访问那个Page,进而通过指针,找到下一个Page。
可以发现,这就是一个B+ 树,所有的有效数据都在叶子结点上,查找到某一个含有效数据的页,就可以查找左右相关的页(叶子结点之间通过双向链表连接),同样符合局部性原理。
总结一下有索引的情况下的查找:
- Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
- 查找的时候,自定向下找,只需要加载部分目录页到内存,即可完成算法的整个查找过程,大大减少了IO次数
1.5 没有显式指定索引怎么办
上面的组织形式是以主键为索引的组织形式,也就是先按照主键进行排序放到数据页中,再用目录页将数据页组织成B+树。如果没有设置主键该以谁为索引呢?
当我们没有手动设置索引的时候, InnoDB 会自动使用表的隐藏主键DB_ROW_ID : 6 byte
,隐含的自增ID,用它作为索引,但是因为它是隐藏的,无法使用它来查找,所以查找效率会很低。
1.6 普通索引(第二索引)
有时候我们搜索数据并不是通过主键(或者唯一键)来搜索的,也有可能是通过非主键列来搜索,而主键索引又是通过主键来完成的,所以主键索引就失效了。这时候可以通过建立普通(辅助)索引来解决这一问题。
比如我们想寻找name字段的一条信息,就可以给name字段创建普通索引。普通索引也是一棵B+树,但是和上面主键的B+树有一些区别:
- 该B+树的叶子节点存放的不再是完整的用户记录,而是只记录name列和主键值;
- 目录页记录除了存储索引列(name)和页号之外,同时还存储了主键值。之所以存主键值,主要是因为数据页和目录页不再按照主键进行排序,而是按照name排序。按照name排序时,会出现name相同的情况,此时就可以根据主键进行排序。
- 此时通过普通索引,就能够找到name对应的主键了,然后我们就可以再从主键索引中找到name对应的完整信息。这个过程称为回表。
1.7 复合索引
我们的主键也有可能是复合主键。
包含多个列的主键始终会自动以复合索引的形式创建索引,其列的顺序是它们在表定义中出现的顺序,而不是在主键定义中指定的顺序。
假设我们以id列和name列作为主键,那么在系统创建主键索引时,自然也是创建一棵B+树,但就不是以id为索引了,而是以id和name为索引,当id字段相同时,则会按照name排序。并且在搜索时,也是先匹配id字段,然后再name字段。
同理,我们也可以对多个非主键的字段建立普通的复合索引,其方式和上面类似,比如假设我们为name列和phone列建立联合索引,创建一棵B+树:
- 叶子节点存放的是name列、phone列和主键值;
- 目录页记录除了存储索引列(name和phone)和页号之外,同时还存储了主键值。按照name排序时,会出现name相同则按照phone排序,phone再相同则按照主键进行排序。
二、InnoDB 为什么采用B+树
- 链表
链表是线性结构的,查找的时候需要线性遍历。 - 二叉搜索树
在某些场景下,二叉搜索树会退化成链表。 - AVL树和红黑树
和B+树相比,B+树的层数更低,每层进行一次IO,树越矮,IO的次数越少,AVL和红黑树相对B+树来说,比B+树更高。 - Hash
官方的索引实现方式中, MySQL 是支持HASH的,不过 InnoDB 和 MyISAM 并不支持。Hash跟进其算法特征,决定了虽然有时候也很快(O(1)),不过,在面对范围查找就明显不行。 - B树
B树非叶子节点,既有数据,又有Page指针;而B+,只有叶子节点有数据,其他目录页,只有键值和Page指针。
B+树非叶子节点不存储数据,这样一个节点就可以存储更多的key。可以使得树更矮,所以IO操作次数更少。
B+叶子节点,全部相连,而B没有。叶子节点相连,更便于进行范围查找。
三、聚簇索引 VS 非聚簇索引
上面InnoDB使用的索引结构就是聚簇索引
3.1 非聚簇索引
MyISAM
引擎同样使用B+树作为索引结果,叶节点的data
域存放的是数据记录的地址。下图为 MyISAM
表的主索引,
Col1
为主键。
其中, MyISAM
最大的特点是,将索引Page
和数据Page
分离,也就是叶子节点没有数据,只有对应数据的地址。
而 InnoDB
是将索引和数据放在一起的。
MyISAM 这种用户数据与索引数据分离的索引方案,叫做非聚簇索引
3.2 两种差别
- MyISAM的主键索引(非聚簇索引)的叶子节点存储对应数据的地址;
- InnoDB 的主键索引(聚簇索引)的叶子节点则是存储用户的整条数据。
- MyISAM的普通索引其叶子节点也只是存储对应数据的地址,和主键索引没区别;
- InnoDB 的普通索引其叶子节点存储索引值和对应的主键,想找到对应的数据还需要进行回表操作。
- 聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
- 相比之下,聚簇索引适合排序,非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的,排序很快。非聚簇索引则没有按序存放,需要额外消耗资源来排序。
四、索引相关操作
4.1 创建索引
4.1.1 创建主键索引
创建主键之后,MySQL会默认使用主键创建主键索引,所以创建主键的方式就是创建主键索引的方式。
- 第一种方式
在创建表的时候,直接在字段名后指定 primary key
create table table_name (id int primary key, name varchar(10));
- 第二种方式:
在创建表的最后,指定某列或某几列为主键索引
create table table_name (id int , name varchar(10), primary key(id) );
- 第三种方式:
创建表以后再添加主键
create table table_name (id int , name varchar(10));
alter table table_name add primary key(id);
主键索引的特点:
- 一个表中,最多有一个主键索引,当然可以是复合索引
- 主键索引的效率高(主键不可重复)
- 创建主键索引的列,它的值不能为null,且不能重复
- 主键索引的列基本上是int
4.1.2 创建唯一键索引
同理
- 第一种方式
-- 在表定义时,在某列后直接指定unique唯一属性。
create table table_name (id int primary key, name varchar(30) unique);
- 第二种方式
--创建表时,在表的后面指定某列或某几列为unique
create table table_name (id int primary key, name varchar(30), unique(name));
- 第三种方式
创建表以后再添加唯一键
create table table_name (id int primary key, name varchar(30));
alter table table_name add unique(name);
唯一索引的特点:
- 一个表中,可以有多个唯一索引
- 查询效率高。
- 如果在某一列建立唯一索引,必须保证这列不能有重复数据。
- 如果一个唯一索引上指定not null,等价于主键索引。
4.1.3 普通索引的创建
- 第一种方式
create table table_name (
id int primary key,
name varchar(10),
index(name) --在表的定义最后,指定某列为索引
);
- 第二种方式
create table table_name (id int primary key,name varchar(10));
alter table table_name add index(name); -- 添加普通索引
- 第三种方式
create table table_name (id int primary key,name varchar(10));
create index idx_name on table_name(name); -- 创建一个索引名为 idx_name 的索引,索引列为name
普通索引的特点:
- 一个表中可以有多个普通索引,普通索引在实际开发中用的比较多
- 如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引
4.1.4 全文索引的创建
当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有要求,要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文。如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。
类似于搜索引擎,根据部分关键字,查找含有关键字的文章。
全文索引关键字为FULLTEXT
。
Database changed
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body) -- 根据文章标题和主体创建全文索引
-> )engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO articles (title,body) VALUES
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...'),
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> ('MySQL vs. YourSQL','In the following database comparison ...'),
-> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
- 查询包含
mysqld
的文章
这样使用查询,虽然能够查到数据,但是没有使用到全文索引,只是普通的条件查询。
mysql> select * from articles where body like '%mysqld%';
+----+-------------------+-------------------------------------+
| id | title | body |
+----+-------------------+-------------------------------------+
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
+----+-------------------+-------------------------------------+
1 row in set (0.00 sec)
- 使用全文索引查询
SELECT * FROM 表名WHERE MATCH (索引) AGAINST (查找内容);
mysql> select * from articles where match (title, body) against ('mysqld');
+----+-------------------+-------------------------------------+
| id | title | body |
+----+-------------------+-------------------------------------+
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
+----+-------------------+-------------------------------------+
1 row in set (0.00 sec)
4.1.5 索引创建原则
- 比较频繁作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
- 更新非常频繁的字段不适合作创建索引
- 不会出现在where子句中的字段不该创建索引,也就是说索引是用来条件查找的,否则没意义
4.2 查询索引
- 第一种方法:
show keys from 表名 \G;
- 第二种方法:
show index from 表名\G;
- 第三种方法:
desc 表明;
比如查询前面的全文索引:
有两个索引,三个字段
mysql> show index from articles \G
*************************** 1. row ***************************
Table: articles
Non_unique: 0 -- 0表示唯一索引
Key_name: PRIMARY -- 索引的名字
Seq_in_index: 1 -- 索引中的列序列号,从1开始
Column_name: id -- 索引的字段名
Collation: A -- 校对规则,列以什么方式存储在索引中,大概意思就是字符序
Cardinality: 6 -- 基数的意思,表示索引中唯一值的数目的估计值
Sub_part: NULL -- 前置索引的意思,如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL
Packed: NULL -- 指示关键字如何被压缩。如果没有被压缩,则为NULL
Null: -- 如果列含有NULL,则含有YES
Index_type: BTREE -- 平衡树索引,B+ 树
Comment:
Index_comment:
*************************** 2. row ***************************
Table: articles
Non_unique: 1
Key_name: title
Seq_in_index: 1
Column_name: title
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:
*************************** 3. row ***************************
Table: articles
Non_unique: 1
Key_name: title
Seq_in_index: 2
Column_name: body
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:
3 rows in set (0.01 sec)
4.3 删除索引
- 第一种方法-删除主键索引:
alter table 表名 drop primary key;
- 第二种方法-其他索引的删除:
alter table 表名 drop index 索引名;
索引名就是show keys from 表名中的Key_name 字段,如果为复合索引,名字默认为第一个字段。
mysql> alter table table_name drop index idx_name;
- 第三种方法方法:
drop index 索引名 on 表名;
mysql> drop index name on table_name;
4.4 explain 关键字
作用:它可以对 SQL 语句进行分析,并输出 SQL 执行的详细信息,以供开发人员针对性优化。
例如:
五、索引覆盖、索引最左匹配原则、索引下推
-
索引覆盖
主键索引:叶子节点保存数据。
辅助索引:叶子节点保存主键值。
如果辅助索引上已经存在我们需要的数据,那么引擎就不会去主键上去搜索数据了。 -
索引最左匹配原则
还是id和name为复合索引。
它们在索引过程中是以id排序,id相同才以name排序。此时如果以name查找,就不能用这个复合索引,因为并不是以name排序的。这时候应该以name创建一个普通索引。 -
索引下推
还是id和name为复合索引。
我们要查询所有id为10,name为’%川’的人的信息,把%加在name字段前面的时候,是无法利用索引的顺序性来进行快速比较的,也就是说这条查询语句中只有id字段可以使用索引进行快速比较和过滤。所以会筛选出所有id为10的主键,然后进行回表操作,如果id为10的信息过多,就会产生多次回表操作。
索引下推就是过滤的动作由下层的存储引擎层通过使用索引来完成,而减少不必要的回表操作。