MySQL查询合理使用索引(Index)：别让你的数据库负重前行

2020-12-30

在冯小刚冯导作为导演拍摄的《天下无贼》中有一句经典台词，那就是出自葛优之口：21世纪什么最贵？人才！从这句话说出到现在，已经16年过去了，那么在现在这个大数据时代，什么最贵呢？那就是数据！做好数据管理是首要的大事，而在数据管理方面，数据库是最好的依托。那个手写数据，人工查阅的年代已经过去很久远了！

数据库的分类，可以简要的分为关系型数据库和非关系型数据库，对应的数据就是结构化的数据和非结构化的数据，主要作用就是存储和管理数据，现在市面上有很多成熟的数据库产品，像关系型数据库：Oracle、MySQL、sql server、Postgre等，非关系型数据库redis、MongoDB等，面向列存储的数据库Hbase和图形存储数据库Neo4j等，今天这篇分享就以Mysql数据库为例，讲解一下数据库当中的索引。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。在 WEB应用方面，MySQL是最好的 RDBMS 应用软件之一。要想挖掘MySQL作为数据库的强大功能，就要学好怎么利用索引来提高查询响应速度，通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力。索引优化时mysql中的一种优化方式。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。也就是说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

从上图中可以看出，MySQL的存储引擎默认是InnoDB，而InnoDB中的索引使用B+Tree实现的，这个数据结构能够很好的减少查找过程中磁盘I/O的存取次数，从而使得MySQL的查询性能提高好几个数量级，关于索引的分类，一般分为下面几类：

主键索引（PRIMARY）：

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

唯一索引（UNIQUE）：

ALTER TABLE `table_name` ADD UNIQUE (`column`)

联合索引（INDEX）：

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

普通索引（INDEX）：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

全文索引（FULLTEXT）：

ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

主键索引是一个表中只有一个主键索引，不允许为null，而唯一索引是一个表中可以有多个唯一索引，但是这列的值要唯一，允许值为null，联合索引就是对多个列建立索引，这里面要注意最左匹配原则，普通索引就是对表中一列建立索引，没有什么限制，而全文索引一般用于检索文本时使用，一般用于模糊查询。

下面就来看一下这些索引的应用，先来创建一个用户表：

CREATE TABLE `tb_user` (
 `id` BIGINT (20),
 `user_name` VARCHAR (200),
 `user_password` VARCHAR (200),
 `birth` DATETIME ,
 `sex` CHAR (4),
		`age` int(8),
 `email` VARCHAR (200),
 `mobile` VARCHAR (200),
 `create_date` DATETIME ,
 `update_date` DATETIME ,
 `description` VARCHAR (800)
) ENGINE=INNODB;

写一个存储过程，向里面插入一百万条数据用来测试：

DELIMITER $$
CREATE
 PROCEDURE insert_tb_user()
 BEGIN
 DECLARE i INT;
 SET i=0;
 WHILE i>=0 && i<= 1000000 DO
 INSERT INTO tb_user
 (`id`, `user_name`, `user_password`, `birth`, `sex`, `age`, `email`, `mobile`, `create_date`, `update_date`, `description`) VALUES
 (i,'admin','123456',NOW(),'M',i,'admin@qq.com','12345678123',NOW(),NOW(),'超级管理员');
 SET i=i + 1;
 END WHILE;
 
 END$$
DELIMITER ;

执行存储过程：

call insert_tb_user()

利用下面的语句将里面的值更改一下，要不都是一样的：

update tb_user a set a.user_name=concat('admin', id);
update tb_user a set a.user_password=concat('admin', id);
update tb_user a set a.mobile=concat('12345', id);

或者在存储过程中加上这些函数处理，使每一行的值都不一样！一百万条数据建好后，如下显示：

要测试是否使用索引对查询速度的影响，首先要把查询缓存给关掉，要不下次查询走了缓存，就对索引测试有影响了，如下，查询缓存已经关闭了：

我们使用语句show index from tb_user来查询现在表中建立索引的情况，发现现在一个索引也没有：

这时我们来查询一下：

通过主键来查询，才一百万条数据，就已经耗时将近1秒了，这是不能忍的，对于主键，我们来建立主键索引，看查询速度有什么变化：

建立主键索引耗时5.4秒，耗时这么久是因为要建立索引树，也就是B+Tree。运行刚才那一条查询语句：

就在那一瞬间，数据就被查询出来了，效率高低一竿见影呀！

对用户名建立唯一索引，语句为ALTER TABLE tb_user ADD UNIQUE KEY (user_name)：

保存了，重复的值，这是因为我改了其中的一个用户名，来验证唯一索引对改列值唯一性的要求。把值改回来再重新建立唯一索引：

联合索引、普通索引和全文索引，按照上面写的建立索引的语句，都可以正确的为每列建立索引，大家动手试一下把！