【Mysql索引】如何提高数据查询速度？_索引如何提高查询速度

---

前言

系统运转时间长了以后，数据量不断地累积，变得越来越庞大，很多查询的速度就变得特别慢。这个时候，我们就采用了 MySQL 提供的高效访问数据的方法索引，有效地解决了这个问题，甚至之前的一个需要 8 秒钟才能完成的查询，现在只用 0.3秒就搞定了，速度提升了 20 多倍。

一、索引是什么？

MySQL 中的索引，就相当于图书馆的检索目录，它是帮助 MySQL 系统快速检索数据的一种存储结构。我们可以在索引中按照查询条件，检索索引字段的值，然后快速定位数据记录的位置，这样就不需要遍历整个数据表了。而且，数据表中的字段越多，表中数据记录越多，速度提升越是明显。Mysql是使用的B+树实现。

二，单字段索引

如何创建单字段索引？

--直接增加索引
create  index  索引名   ON table  表名（字段）;

--创建表的同时创建索引的语法如下所示
CREATE TABLE 表名 
	(
	字段 数据类型，
	...
	{INDEX| KEY } 索引名（字段）
	)
--修改表时创建索引的语法如下所示
ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段);

1
2
3
4
5
6
7
8
9
10
11
12
13

• 这里有个小问题要提醒你一下，给表设定主键约束或者“唯一性约束的时候，MySQL 会自动创建主键索引或唯一性索引。

单字段创建的原理

借助EXPLAIN关键字查看sql语句的执行效率

• type=range:表示使用索引查询特定范围的数据记录。
• rows=5411：表示需要读取的记录数。p
• possible_keys=index_trans:表示可以选择的索引是 index_trans。
• key=index_trans：表示实际选择的索引是 index_trans。
• extra=Using index condition;Using where;Using MRR：这里面的信息对 SQL 语句的执行细节做了进一步的解释，包含了 3 层含义：第一个是执行时使用了索引，第二个是执行时通过 WHERE 条件进行了筛选，第三个是使用了顺序磁盘读取的策略。

如何选择索引字段

我建议你在选择索引字段的时候，要选择那些经常被用做筛选条件的字段。这样才能 发挥索引的作用，提升检索的效率。

当查询条件后面存在多个索引时候 ，执行的时候具体执行哪一个索引是优化器决定的。

MySQL 在索引中而不是数据表中寻找满足条件的索引记录，再通过索 引记录中的指针来定位数据表中的数据。这样，索引就能发挥作用了。

1.选择合适的字段创建索引：

• NOT NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

三，组合索引

如何创建 组合索引？

所以，如果有多个索引，而这些索引的字段同时作为筛选字段出现在查询中的时候， MySQL会选择使用最优的索引来执行查询操作。

--直接给数据表创建索引的语法如下
 CREATE INDEX 索引名 ON TABLE 表名（字段1，字段2， ...);
--创建表的同时创建索引： 
CREATE TABLE 表名 
 (字段,数据类型,
 ....
 {index | key} 索引名(字段1,字段2, ...);
)

--修改表时创建索引： 
 ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段1，字段2, ...);
1
2
3
4
5
6
7
8
9
10
11

组合索引的原理

组合索引的多个字段是有序的，遵循左对齐的原则。

重要：比如我们创建的组合索引，排序的方式 是branchnumber、cashiernumber和itemnumber。因此，筛选的条件也要遵循从左向 右的原则，如果中断，那么，断点后面的条件就没有办法利用索引了。

总结

删除索引

-- 普通索引
DROP INDEX 索引名 ON 表名;
--主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;
1
2
3
4

最后，我来跟你说说索引的成本。索引能够提升查询的效率，但是建索引也是有成本的，主 要有 2 个方面。

• 一个存储空间的开销，还有一个是数据操作上的开销。 存储空间的开销，是指索引需要单独占用存储空间。
• 数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧 的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索 引能够指向正确的记录。

因此，索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。