深入理解MySQL索引原理和实现——为什么索引可以加速查询？

青旅半醒 2021-12-04 09:49 261阅读 0赞

### 深入理解MySQL索引原理和实现——为什么索引可以加速查询？ ###

说到索引，很多人都知道“索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。”

但是索引是怎么实现的呢？因为索引并不是关系模型的组成部分，因此不同的DBMS有不同的实现，我们针对MySQL数据库的实现进行说明。本文内容涉及MySQL中索引的语法、索引的优缺点、索引的分类、索引的实现原理、索引的使用策略、索引的优化几部分。

一、MySQL中索引的语法

二、索引的优缺点

三、索引的分类

四、索引的实现原理

1、哈希索引：

2、全文索引：

3、BTree索引和B+Tree索引

\*    聚簇索引和非聚簇索引

五、索引的使用策略

六、索引的优化

### 一、MySQL中索引的语法 ###

创建索引

在创建表的时候添加索引

CREATE TABLE mytable(  
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL,  
INDEX \[indexName\] (username(length))  
);  
在创建表以后添加索引

ALTER TABLE my\_table ADD \[UNIQUE\] INDEX index\_name(column\_name);  
或者  
CREATE INDEX index\_name ON my\_table(column\_name);  
注意：

1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够

2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

**根据索引查询**

具体查询：
    SELECT * FROM table_name WHERE column_1=column_2;(为column_1建立了索引)
     
    或者模糊查询
    SELECT * FROM table_name WHERE column_1 LIKE '%三'
    SELECT * FROM table_name WHERE column_1 LIKE '三%'
    SELECT * FROM table_name WHERE column_1 LIKE '%三%'
     
    SELECT * FROM table_name WHERE column_1 LIKE '_好_'
     
    如果要表示在字符串中既有A又有B，那么查询语句为：
    SELECT * FROM table_name WHERE column_1 LIKE '%A%' AND column_1 LIKE '%B%';
     
    SELECT * FROM table_name WHERE column_1 LIKE '[张李王]三';  //表示column_1中有匹配张三、李三、王三的都可以
    SELECT * FROM table_name WHERE column_1 LIKE '[^张李王]三';  //表示column_1中有匹配除了张三、李三、王三的其他三都可以
     
    //在模糊查询中，%表示任意0个或多个字符；_表示任意单个字符（有且仅有），通常用来限制字符串长度;[]表示其中的某一个字符；[^]表示除了其中的字符的所有字符
     
    或者在全文索引中模糊查询
    SELECT * FROM table_name WHERE MATCH(content) AGAINST('word1','word2',...);

**删除索引**

DROP INDEX my_index ON tablename；
    或者
    ALTER TABLE table_name DROP INDEX index_name;

**查看表中的索引**

SHOW INDEX FROM tablename

**查看查询语句使用索引的情况**

//explain 加查询语句
    explain SELECT * FROM table_name WHERE column_1='123';

### 二、**索引的优缺点** ###

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

### 三、**索引的分类** ###

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

1、主键索引：即主索引，根据主键pk\_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE ‘table\_name’ ADD PRIMARY KEY pk\_index(‘col’)；  
2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE ‘table\_name’ ADD UNIQUE index\_name(‘col’)；  
3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE ‘table\_name’ ADD INDEX index\_name(‘col’)；  
4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）

ALTER TABLE ‘table\_name’ ADD FULLTEXT INDEX ft\_index(‘col’)；  
5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE ‘table\_name’ ADD INDEX index\_name(‘col1’,‘col2’,‘col3’)；  
\*遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

\*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE ‘table\_name’ ADD INDEX index\_name(col1(4),col2（3))；

表示使用col1的前4个字符和col2的前3个字符作为索引