MySQL数据库-索引的介绍和使用

MySQL数据库-索引

数据库是用来存储数据，在互联网应用中，数据库存储的数据可能会很多（大数据），数据表中数据的查询速度会随着数据量的增长而逐渐变慢，从而导致相应用户请求的速度变慢，造成用户体验差。为了提高数据的查询效率，我们需要在数据库中用到索引。

数据准备

-- 创建数据表(准备存储500万数据)
CREATE TABLE tb_testindex(fid INT PRIMARY KEY,	-- 主键sid INT UNIQUE,			-- 唯一键tid	int,				-- 普通字段name VARCHAR(20),remark VARCHAR(20)
);
-- 数据准备：创建存储过程，向tb_testindex表插入500万条数据
CREATE PROCEDURE proc_readydata()
BEGINDECLARE i INT DEFAULT 1;WHILE i < 5000000 DOINSERT INTO tb_testindex(fid,sid,tid,name,remark) VALUES(i,i,i,'test_name','test_remark');SET i = i+1;END WHILE;
END;
-- 调用存储过程，准备五百万条数据
CALL proc_readydata();
-- 查询数据
SELECT COUNT(1) FROM tb_testindex;

1.索引介绍

索引：用来提高数据表中数据的查询效率。

索引，就是将数据表中的某一列/某几列的值取出来，构造成便于查找的结构进行存储，生成数据表的目录，当进行数据查找的时候，则先在目录中进行查找，得到对应数据的地址，然后再根据地址到数据表中快速的获取数据记录，避免全表扫描。

2.索引分类

MySQL索引，根据创建的列不同，可以分为：

• 主键索引：在数据表的主键上创建的索引，这个字段必须被primary key修饰，每张表只能有一个主键
• 唯一索引：在数据表的唯一键上创建的索引，必须被unique修饰，此列的所有值只能出现一次，可以为NULL
• 普通索引：在普通字段上创建的索引，没有唯一性限制
• 组合索引：两个及以上的字段联合起来创建的索引

说明：

1. 在创建数据表时，将字段声明为主键，会自动在主键字段上创建索引–主键索引
2. 在创建数据表时，将字段声明为唯一键，会自动创建主键–唯一索引

3.创建索引

3.1 唯一索引

-- 创建唯一索引：创建唯一索引的列可以为空，但不能重复
CREATE UNIQUE INDEX index_tid ON tb_textindex(tid);

3.2 普通索引

-- 创建普通索引：不要求创建索引的列必须不能重复
CREATE INDEX index_tid ON tb_textindex(name);

3.3 组合索引

-- 创建组合索引：在多个字段上创建索引
CREATE INDEX index_tid ON tb_textindex(tid,name);

3.4 全文索引

全文索引，是MySQL5.6版本以后新增的索引：通过此索引，可以进行全文检索操作，因为Mysql的全文检索不支持中文，因此全文索引不被开发者关注，在应用开发中，通常是通过全文搜索，搜索引擎如ES（数据库中间件）实现全文检索。

create fulltext index <index_name> on 表名（字段名）；

4.索引使用

索引创建完成以后不需要调用，当根据创建索引的类进行数据查询的时候，会自动使用索引。

组合索引也需要根据创建索引的所有字段进行查询时触发。

• 在命令行窗口中，可以查询语句的查询规划：

explain select * from tb_testindex where tid = 250000\G;

5.查看索引

-- 命令行
SHOW CREATE TABLE tb_testindex;

-- 查询索引
show index from tb_testindex;

-- 查询索引
show keys from tb_testindex;

6.删除索引

-- 删除索引
-- 索引是建立在字段上的，不同表中可能出现相同名称的索引，所以删除索引时需要指定表名
drop index index_tid on tb_testindex;

7.索引总结

7.1 优点

• 索引提高查询效率
• 索引可以避免服务器排序，将随机的IO变成顺序IO

7.2 缺点

• 索引是根据数据表列的值创建，当数据表中数据发生DML操作时，会重新生成索引文件
• 索引文件也会占用磁盘空间

7.3 索引使用注意事项

• 如果数据表中数据不多时，不建议使用索引，全表扫描可能会更快
• 数据量大，但是DML操作频繁，不建议使用索引
• 不要在数据重复度高的列上创建索引（性别）
• 创建索引之后，要注意查询SQL语句的编写，避免索引失效