使用索引对MySQL千万级数据查询优化

索引的定义

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

 

索引的分类

MySQL的索引可分为两类：单列索引和组合索引

单列索引：指一个索引只包含一列。包含主键索引(primary key)、唯一索引(unique index)、普通索引(index)。

多列索引：指一个索引包含两个或两个以上的列。

 

索引性能验证

可能以上内容大家都十分熟悉。而且不管是老师教学的过程中，亦或是查资料、看博客的过程中，都有提到使用索引对查询效率的提升。但提升的程度究竟如何，却语焉不详。为了解决这个疑惑，笔者将用1000万条数据进行验证。

1.建立数据库

2.建立数据表

千万鳖用InnoDB鸭！！！！用InnoDB插10万条就要用一个小时，不要问我怎么知道的哈哈哈。InnoDB支持事务安全、行锁、表锁、外键等功能，所以开销较大。因为是做实验，所以数据库引擎采用了MyISAM。

3.创建存储过程

循环插入1000万组记录

4.调用存储过程完成插入

5.不使用索引进行查询

不使用索引的情况下查询1000万组数据平均用时大概3s左右。

6.建立索引

使用ALTER语句为nickname添加索引。

7.使用索引进行查询

使用索引，时间变为了0.00s。可以看到索引对查询效率的提升。然而笔者觉得这个提升效果并不明显，所以准备再多插入一些数据进行实验。

8.删除并新建存储过程

因为存储过程的删除语句只能改变存储过程的特征,不能修改过程的参数以及过程体。所以必须删除原有的存储过程然后新建。

这里笔者又准备插入4000万条记录进行实验。

9.调用新的存储过程

10.执行时间很长，吃个瓜

11.查询记录总数

查询下记录总数，真的是5000万条嗷。因为本身表中就存在主键索引，所以查询速度还是很快的。

12.使用索引进行查询

因为上文中已经为nickname建立了索引，所以查询是按索引查询的，用时还是0.00s。

13.使用ALTER删除索引

14.不使用索引进行查询

删除索引后，查询时间达到了40s左右

15.总结

在MySQL的查询中，正确的使用索引。甚至能为查询效率达到上万倍的提升。在使用SELECT语句时，务必能够使用到索引，能极大的提升查询效率。

但是如何建立索引，如何保证使用索引，还是有很多约束。笔者也会在接下的博文中详细的阐述MySQL的一些简单的优化策略。希望与大家共同学习共同进步~~~~

以上内容如有缺漏，敬请指正~

 

最后给出以上实验的所有所有的SQL语句，方便大家实验：

#创建test_database库
CREATE DATABASE test_database charset=utf8;

#使用test_database库
USE test_database;

#创建test_table表
CREATE TABLE test_table(
    id bigint(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    username varchar(10) DEFAULT NULL,
    nickname varchar(10) DEFAULT NULL,
    password varchar(50) DEFAULT NULL
)ENGINE=MyISAM AUTO_INCREMENT=100000001 DEFAULT CHARSET=utf8;

#定义存储过程testproc
DELIMITER // 
CREATE PROCEDURE testproc()
BEGIN
    DECLARE num int;
    SET num=1;
    WHILE num <=10000000 DO
        INSERT INTO test_table(username,nickname,password) VALUES(num,'昵称',PASSWORD(num));
        SET num=num+1;
    END WHILE;
END //
DELIMITER ; 

#查看及删除存储过程testproc
-- SHOW PROCEDURE STATUS LIKE 't%';
-- DROP PROCEDURE testproc;

#调用存储过程testproc
CALL  testproc();

#test_table表计数
SELECT COUNT(*) FROM test_table;

#查询，利用不存在的属性保证能遍历所有记录
SELECT * FROM test_table WHERE nickname='姓名';

#为nickname添加索引
ALTER TABLE test_table ADD INDEX nickname_index(nickname);

#查看test_table表中索引
-- SHOW INDEX FROM test_table;

#删除nickname的索引
ALTER TABLE test_table DROP INDEX nickname_index;