9.索引及其操作

数据库是用来存储数据的，在互联网应用中的数据库中存储的数据量可能会很大，数据表中数据的查询速度会随着数据量的增长逐渐变慢，从而导致响应用户请求的速度变慢，我们如何提高数据的查询效率呢

一、索引的分类

索引就是将数据表的某一列/某几列的值取出来构造成便于查找的结构，生成数据表的目录

当我们进行数据查询的时候现在目录中进行查找得到对应的数据的地址，然后再到数据表中根据地址举哀苏获取数据记录，避免全局扫描

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

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

说明：

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

2.在创建数据表时，将字段声明为唯一键（添加唯一约束），会自动在唯一字段创建唯一索引

二、创建索引

创建我们的数据包

CREATE PROCEDURE pro_4()
BEGIN
	DECLARE i int DEFAULT 1;
	WHILE i<=50000000 DO
		INSERT INTO students(stu_num,stu_name,stu_gender,stu_age) VALUES(i,i,'w',i);
		SET i=i+1;
	END WHILE;
END;

CALL pro_4();

2.1、唯一索引

# 语法
# 创建唯一索引的列的值不能重复
CREATE UNIQUE INDEX <indexName> ON <tableName>(<columnName>);

2.2、普通索引

# 语法
# 不要求创建列的唯一性
CREATE INDEX <indexName> ON <tableName>(<columnName>);

2.3、组合索引

# 语法
# 不要求创建列的唯一性
CREATE INDEX <indexName> ON <tableName>(<columnName1>,<columnName2>);

2.4、全文索引

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

create fulltext index <indexName> ON <tableName>(<columnName1>)

三、索引使用

索引创建完成之后，无需调用，当根据创建索引的列进行查询的时候将自动进行索引查询

在命令行窗口可以查看查询语句的规划

explain select * from students where stu_name = '20188';

# 我们可以看到使用了index_text1这个key

+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+--
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+--
|  1 | SIMPLE      | students | NULL       | ref  | index_text1   | index_text1 | 162     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+--

四、查看索引

# 命令行查看
show create table students\G;

# 在哪儿都可以查看
show indexes from students;
show keys from students;

五、删除索引

# 索引是建立在表上的，不同的表上可能有相同的索引
drop index <indexName> ON <tableName>;

六、使用总结

6.1、优点

• 索引大大降低了数据库服务器在执行查询操作时扫描的数据
• 索引可以避免服务器排序，将随机IO变成顺序IO

6.2、缺点

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

6.3、注意事项

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