MySQL：带你理解索引的原理与实现

最新推荐文章于 2024-09-01 09:15:00 发布

小米内推官_AngelDg

最新推荐文章于 2024-09-01 09:15:00 发布

阅读量226

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/AngelDg/article/details/107583158

版权

MySQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

索引

特性：

索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。 所以它的价值，在于提高一个海量数据的检索速度。

常见索引分为:

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)–解决中子文索引问题。

案例:

先整一个海量表，在查询的时候，看看没有索引时有什么问题?

--构建一个8000000条记录的数据
--构建的海量表数据需要有差异性，所以使用存储过程来创建

-- 产生随机字符串
delimiter $$
create function rand_string(n INT) 
returns varchar(255)
begin
	declare chars_str varchar(100) default
		'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do
 	set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    set i = i + 1;
    end while;
	return return_str;
    end $$
delimiter ;

--产生随机数字
delimiter $$
create function rand_num() 
returns int(5)
begin
	declare i int default 0;
	set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

--创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
	set autocommit = 0;
	repeat
		set i = i + 1;
		insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  		until i = max_num
	end repeat;
	commit;
end $$
delimiter ;

-- 执行存储过程，添加8000000条记录 
call insert_emp(100001, 8000000);

到此，已经创建出了海量数据的表了。

查询员工编号为998877的员工

select * from EMP where empno=998877;

可以看到耗时4.93秒，这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。

解决方法，创建索引

alter table EMP add index(empno);

换一个员工编号，测试看看查询时间

select * from EMP where empno=123456;

创建索引

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含对数据表里所有记录的引用指针。

创建主键索引

主键使用关键字 PRIMARY KEY 来创建。

第一种方式

 -- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

第二种方式:

 -- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

第三种方式:

create table user3(id int, name varchar(30)); 
 -- 创建表以后再添加主键
alter table user3 add primary key(id);

主键索引的特点:

一个表中，最多有一个主键索引(主键不可重复)
主键索引的效率高
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

唯一索引的创建

唯一索引可以保证数据记录的唯一性，使用关键字 unique key 来创建。

第一种方式

 -- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

第二种方式

 -- 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

第三种方式

create table user6(id int primary key, name varchar(30)); 
alter table user6 add unique(name);

唯一索引的特点:

一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据
如果一个唯一索引上指定not null，等价于主键索引

普通索引的创建

普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务：加快对数据的访问速度。

普通索引允许被索引的数据列包含重复的值。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。

第一种方式

create table user8(id int primary key,
    name varchar(20),
    email varchar(30),
	index(name) --在表的定义最后，指定某列为索引 
	);

第二种方式

create table user9(id int primary key, name varchar(20), email varchar(30)); 
alter table user9 add index(name); --创建完表以后指定某列为普通索引

第三种方式

create table user10(id int primary key, name varchar(20), email varchar(30)); 
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点:

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

全文索引的创建

FULLTEXT（全文）索引，仅可用于 MyISAM 和 InnoDB（现在比较占优），针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
)engine=MyISAM;

 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 ...');

//创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

查询有没有database数据

如果使用如下查询方式，虽然查询出数据，但是没有使用到全文索引

mysql> select * from articles where body like '%database%'; 
+----+-------------------+------------------------------------------+ 
| id |       title       |                   body                   | 
+----+-------------------+------------------------------------------+ 
| 1  |   MySQL Tutorial  |        DBMS stands for DataBase ...      | 
| 5  | MySQL vs. YourSQL | In the following database comparison ... | 
+----+-------------------+------------------------------------------+

可以用explain工具看一下，是否使用到索引

mysql> explain select * from articles where body like '%database%'\G 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
         type: ALL	<== type为ALL表示没有用到索引
possible_keys: NULL
		  key: NULL <== key为null表示没有用到索引
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

如何使用全文索引呢?

-- 全文索引的查询有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法
mysql> SELECT * FROM articles
	-> WHERE MATCH (title,body) AGAINST ('database'); 
+----+-------------------+------------------------------------------+ 
| id |       title       | body                                     | 
+----+-------------------+------------------------------------------+ 
| 5  | MySQL vs. YourSQL | In the following database comparison ... | 
| 1  | MySQL Tutorial    | DBMS stands for DataBase ...             | 
+----+-------------------+------------------------------------------+

通过explain来分析这个sql语句

 mysql> explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')\G 
 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
         type: fulltext	<= type不会ALL
possible_keys: title
		  key: title
      key_len: 0
          ref:
         rows: 1
        Extra: Using where

注意：

对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。
5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引
在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。

索引创建注意事项：

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引

注意：

索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够
创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

主键索引和唯一索引的区别:

主键是一种约束（目的是对这个表的某一列进行限制），唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

MySQL主键索引和普通索引的区别

主键索引索引着数据，然后普通索引索引着主键ID值(这是在innodb中(B+树)，但是如果是Myisam中（B树），主键索引和普通索引是没有区别的都是直接索引着数据)
当你查询用的是where id=x 时，那只需要扫描一遍主键索引，然后拿到相应数据。但是如果是查询的普通索引的话，那么会先扫描一次普通索引，拿到主键值，然后再去扫主键索引，拿到所需要的数据，这个过程叫做回表。

查询索引

第一种方法: show keys from 表名

mysql> show keys from goods\G 
*********** 1. row ***********
		Table: goods <= 表名 
   Non_unique: 0 <= 0表示唯一索引
     Key_name: PRIMARY <= 主键索引
 Seq_in_index: 1
  Column_name: goods_id <= 索引在哪列 
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
   		 Null:
   Index_type: BTREE <= 以B树形式的索引
	  Comment:
1 row in set (0.00 sec)

第二种方法: (查看表中的索引)

show index from 表名;

第三种方法(信息比较简略): desc 表名;

删除索引

第一种方法-删除主键索引:

alter table 表名 drop primary key;

第二种方法-其他索引的删除:

alter table 表名 drop index 索引名; 
--索引名就是show keys from 表名中的 Key_name 字段

mysql> alter table user10 drop index idx_name;

第三种方法方法:

drop index 索引名 on 表名
mysql> drop index name on user8;

索引的优缺点

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；
劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

索引的实现原理（哈希、BTree、B+Tree）

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引等等，

哈希索引：

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

BTree索引

BTree是平衡搜索多叉树，也就是说最多可以开m个叉（m>=2）。设树的度为2d（d>1），高度为h，那么BTree要满足以下条件：

每个叶子结点的高度一样，等于h；
每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d，key和point相互间隔，结点两端一定是指针；
叶子结点指针都为null；
非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

在这里插入图片描述

在BTree的机构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构。

关键字集合分布在整颗树中
搜索有可能在非叶子节点结束
一个节点中的key从左到右非递减排列

B+Tree索引(MySQL的索引类型)

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

B+Tree中的非叶子结点不存储数据(data)，只存储键值(key)；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
B+Tree的每个非叶子节点由n个键值key和n个指针point组成(节点中仅含有其子树（根节点）中的最大（或最小）关键字)；
每个叶子节点含有一个指向相邻叶子节点的指针，带顺序访问指针的B+树提高了区间查找能力；

B+Tree的结构如下：

B+Tree对比BTree的优点：

磁盘读写代价更低

提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3，3层可存大约一百万数据）

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存）
B+Tree内节点不含data域，因此出度d更大，则h更小，I/O次数少，效率更高

故B+Tree比BTree更适合实现外存的索引结构。

查询速度更稳定

由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。
BTree 虽然每个叶子结点的高度一样（等于h），但是搜索有可能在非叶子节点结束。

带顺序索引的B+TREE

很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

B+树的劣势：

主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；
即使主键是有序递增的，大量写请求的分布仍是随机的；
由于键会重复出现，因此会占用更多的空间。但是与带来的性能优势相比，空间劣势往往可以接受，因此B+树的在数据库中的使用比B树更加广泛。

但是B树也有优点，其优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速，最好为 $O (1)$ 。

在这里插入图片描述

聚簇索引和非聚簇索引

MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

非聚簇索引：索引顺序与数据的物理排列顺序无关
聚簇索引：聚簇索引的顺序就是数据的物理存储顺序

在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引， 非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的 叶子结点的key都存储指向键值对应的数据的物理地址。（MyISAM引擎使用B+Tree作为索引结构）
非聚簇索引的 数据表和索引表是分开存储的。
非聚簇索引中的 数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。（插入顺序不受键值影响）

既然非聚簇索引的主索引和辅助索引指向相同的内容，为什么还要辅助索引这个东西呢?

索引不就是用来查询的吗，用在那些地方呢(就是 WHERE 和 ORDER BY 语句后面)，那么如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。

InnoDB——聚簇索引

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

聚簇索引的 主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。 但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

注意：

使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。
因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。
聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

下图可以形象的说明聚簇索引和非聚簇索引的区别

在这里插入图片描述

MyISAM和innoDB的区别总结如下：

	MyISAM	innoDB
索引类型	非聚簇	聚簇
支持事务	是	否
支持表锁	是	是
支持行锁	否	是（默认）
支持外键	否	是
支持全文索引	是	是（5.6以后支持）
适用操作类型	大量select下使用	大量insert、delete和update下使用

知识点习题：

table A 采用（‘X’,‘Y’,‘Z’）3列构建了索引，请问 select…from A where Z=… and X=… 能否用到该索引的优化效果？

A. 可以
B. 不可以
C. 有概率性用到
D. 换成where X=… and Z=… 后可以

正确答案：D

答案解析：

最左匹配原则

首先建立一个组合索引：index（province，city，street）

查询条件	索引是否生效
where province=? and city=? and street=?	生效
where city=? and province=? and street=?	特殊情况，mysql优化器自动处理为索引顺序，生效
where province=?	生效
where province=? and city=?	生效
where province=? and street=?	生效
where city=? and street=?	不生效
where city=?	不生效
where street=?	不生效