MySQL的索引原理与查询优化

2021-12-21 08:24:37

一、MySQL 索引简介

1、 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

2、索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引，即一个索引包含多个列。

3、索引的详细分类：

普通索引index :加速查找
唯一索引
- 主键索引：primary key ：加速查找+约束（不为空且唯一）
- 唯一索引：unique：加速查找+约束（唯一）
联合索引
- primary key(id,name):联合主键索引
- unique(id,name):联合唯一索引
- index(id,name):联合普通索引
全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
空间索引spatial :了解就好，几乎不用

4、创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

5、索引的两大类型hash与btree

我们可以在创建上述索引的时候，为其指定索引类型，分两类

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

2、不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

6、滥用索引的缺点：

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

创建UNIQUE | FULLTEXT | SPATIAL 一样的方法

方法一：创建表时创建索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
   [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY  [indexName] (username(length))   
);

方法二：CREATE在已存在的表上创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL | INDEX ]  indexName ON mytable(username(length));

方式三：修改表结构(添加索引)

ALTER table tableName ADD  [UNIQUE | FULLTEXT | SPATIAL | INDEX ] indexName(columnName)

删除索引的语法

DROP INDEX [indexName] ON mytable;

三、实测索引的功效

1. 前期准备工作

创建一个名为text的数据库：

create database text charset utf8;

创建一张名为text的数据表

create table text(id int,name varchar(20))

通过创建存储过程，实现批量插入记录(大约需要半小时时间)

delimiter $$ #声明存储过程的结束符号为$$
create procedure insertinfo()
BEGIN
    declare i int default 1;
    while(i<1000000)do
        insert into text values(i,concat('wangjifei',i));
        set i=i+1;
    end while;
END $$ 
delimiter ; #重新声明分号为结束符号为;

查看存储过程

 show create procedure insertinfo\G

调用存储过程

 call insertinfo();

2、在没有索引的前提下测试查询速度

mysql> select * from text where id = 1234;
+------+---------------+
| id   | name          |
+------+---------------+
| 1234 | wangjifei1234 |
+------+---------------+
1 row in set (0.39 sec)

mysql> select * from text where name = 'wangjifei12345';
+-------+----------------+
| id    | name           |
+-------+----------------+
| 12345 | wangjifei12345 |
+-------+----------------+
1 row in set (0.53 sec)

3、加上索引

//1. 一定是为搜索条件的字段创建索引，比如select * from t1 where age > 5;就需要为age加上索引
//2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，插入删除更新都很慢，只有查询快
//比如create index myname on text(name);会扫描表中所有的数据，然后以name为数据项，
//创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了

//给name加上普通索引
mysql> create index myname on text(name);
Query OK, 0 rows affected (18.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

//给id加上唯一索引
mysql> create unique index myid on text(id);
Query OK, 0 rows affected (10.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

效果立竿见影,查询速度翻了几十倍

mysql> select * from text where id = 1234;
+------+---------------+
| id   | name          |
+------+---------------+
| 1234 | wangjifei1234 |
+------+---------------+
1 row in set (0.00 sec)

mysql> select * from text where name = 'wangjifei12345';
+-------+----------------+
| id    | name           |
+-------+----------------+
| 12345 | wangjifei12345 |
+-------+----------------+
1 row in set (0.01 sec)

mysql> select * from text where name = 'wangjifei823458';
+--------+-----------------+
| id     | name            |
+--------+-----------------+
| 823458 | wangjifei823458 |
+--------+-----------------+
1 row in set (0.00 sec)

四、批量添加测试数据的方法：

通过存储过程批量创建数据

1. 创建存储过程
delimiter $$ #声明存储过程的结束符号为$$
create procedure insertinfo()
BEGIN
   declare i int default 1;
   while(i<1000000)do
       insert into text values(i,concat('wangjifei',i));
       set i=i+1;
   end while;
END $$ 
delimiter ; #重新声明分号为结束符号为;

2. 查看存储过程
show create procedure insertinfo\G;

3. 调用存储过程
call insertinfo();

五、正确使用索引

覆盖索引
select * from text where name = 'wangjifei882345';
该sql命中了索引，但未覆盖索引。利用name = 'wangjifei882345'到索引的数据结构中定位到该name在硬盘中的位置，或者说再数据表中的位置。

但是我们select的字段为*，除了name以外还需要其他字段，这就意味着，我们通过索引结构取到name还不够，还需要利用该name再去找到该name所在行的其他字段值，这是需要时间的，

很明显，如果我们只select name，就减去了这份苦恼，如下select name from text where name = 'wangjifei882345';这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了name在硬盘的地址，速度很快

mysql> select name from text where name = 'wangjifei882345';
+-----------------+
| name            |
+-----------------+
| wangjifei882345 |
+-----------------+
1 row in set (0.00 sec)

联合索引
为了增加效果对比，在创建联合索引前将之前创建的普通索引删除掉了

mysql> select * from text where id = 2435353252 and name = 'wangjifei123333';
Empty set (0.58 sec)  //普通查询

mysql> create index idname on text(id,name); //创建联合索引
Query OK, 0 rows affected (26.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from text where id = 2435353252 and name = 'wangjifei123333'; 
Empty set (0.00 sec)  // 联合索引查询

码农公寓