Mysql索引

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

索引是数据库中很重要的一个知识点，涉及数据库的设计规范，优化。抽象来理解就是目录的意思。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、什么是索引？有什么用？

• 什么是索引？

  索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

• 索引为什么可以提高检索效率呢？

  在数据库方面，查询一张表的时候有两种检索方式：
  第一种方式：全表扫描
  第二种方式：根据索引检索（效率很高）
  其实最根本的原理是缩小了扫描的范围。

例：

select name,age from user where name = '张三';
当name字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描name字段中所有的值。
当name字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

通过 explain 能查询sql语句执行的执行计划

添加索引前 rows：扫描行数25
mysql> explain select name,age from user where age = 18;
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
添加索引后 rows：扫描行数1
mysql> explain select name,age from user where age = 18;
		+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
		| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
		+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
		|  1 | SIMPLE      | user  | ref  | u_name_index  | u_name_index  | 9       | const |    1 | Using where |
		+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

• 能不能随意添加索引?

  索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改
  这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
  添加索引是给某一个字段，或者说某些字段添加索引。

• 索引的作用范围？

  添加索引是给某一个字段，或者说某些字段添加索引。

---

二、什么情况下添加索引？

当表数据量庞大之后数据查询速度肯定会变慢，这时候就可以考虑数据库索引优化来提升查询速度。

• 该字段很少的DML（增删改 ）操作
  因为字段经常进行增删改 数据库需要不停的维护索引，会增加维护成本，所以不适合添加索引。
• 该字段经常出现在where子句中
  经常出现在where查询条件中的字段适合创建索引
  在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常用在连接的列上
  这些列主要是一些外键，可以加快连接的速度
• 排序的列上
  因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间

---

三、什么情况下不适合添加索引？

以下内容引用自此连接，点击此跳转

• 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。
  这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

• 第二，对于那些只有很少数据值的列也不应该增加索引。
  这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

• 第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。
  这是因为，这些列的数据量要么相当大，要么取值很少。

• 第四，当修改性能远远大于检索性能时，不应该创建索引。
  这是因为，修改性能和检索性能是互相矛盾的。

---

四、索引的其他知识

1.索引的分类

以下内容引用自此连接，点击此跳转

• 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

1：普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。
2：唯一索引：索引列中的值必须是唯一的，但是允许为空值。
3：主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

主键索引与唯一索引的区别：

1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
2. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
3. 唯一性索引列允许空值，而主键列不允许为空值。
4. 主键索引在创建时，已经默认为非空值+ 唯一索引了。
5. 一个表最多只能创建一个主键索引，但可以创建多个唯一索引。
6. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
7. 主键可以被其他表引用为外键，而唯一索引不能。

• 组合索引：在表中的多个字段组合上创建的索引

只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
例如，这里由id、name和age 3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。

• 全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …"
通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不会用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。

2.索引的实现原理

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率
是最高的。

3.索引失效

select name from user where name like '%张%';
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的

总结

当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。