SQL的索引详细介绍

 文章目录

前言

一、MySQL索引 

1、聚集索引(主键索引)

2、二级索引（辅助索引）

3、基数、选择性、回表

二、MySQL扩展

总结

---

前言

        大多数性能问题都和SQL语句有关，本文主要介绍了MySQL中的索引以及索引的重要性。

---

一、MySQL索引 

        索引的定义：在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（类似于图书目录，通过图书页码迅速找到所需内容）。一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。索引包含一个表中列的值，并且这些值存储在一个数据结构中。

优点：提高数据检索的效率，降低数据排序的成本。
缺点：会降低更新表的速度。

1、聚集索引(主键索引)

        聚集索引是将主键与行记录存储在一起，当根据主键进行查询时，可直接在表中获取到数据，不用回表查询。InonDB的所有的表都是索引组织表，主键与数据存放在一起。InnoDB选择聚集索引遵循以下原则：

• 在创建表时，如果指定了主键，则将其作为聚集索引。
• 如果没有指定主键，则选择第一个NOT NULL（非空）的唯一索引作为聚集索引。
• 如果没有唯一索引，则内部会产生一个6字节的rowID（主键值）作为主键 

2、二级索引（辅助索引）

二级索引的叶子结点存储了索引值+rowID（主键值） 。

建议：在MySQL中创建表时，尽量指定一个显示的自增主键

优势：显示指定的主键可以是普通的int类型，这样存储空间就是4字节，在二级索引的叶子结点中存储主键的所占用空间就会变小

扩展：二级子节点之所以选择存储主键的值，而不是存储主键的指针，是为了避免如果主键位置发生变化时，需要修改二级索引的叶子节点对应存储的指针。 

3、基数、选择性、回表

• 基数：字段distinct后的值，主键或NOT NULL（非空）的唯一索引的基数等于表的总行数。
• 选择性：指基数与总行数的比值乘以100%，通常表示在字段上是否适合创建索引。
• 回表：当需要查询的字段不能在索引中完全获取时，需要回表查询取出所需的数据。

为了体现出基数的重要性，做下列测试： 

❶测试相同数据值的cost值的消耗

Ⅰ、在数据库中随便选择一个数据表。

Ⅱ、在数据库中创建一个新表 t1 。

create table t1 (id int , c1 char(20),c2 char(20),c3 char(20));

Ⅲ、插入6条values值相同的测试数据。

insert into t1 values (10,'a','b','c');

Ⅸ、为 t1 表添加索引。

create index idx_cl on t1 (c1);

Ⅴ、通过执行计划查看cost值的消耗。（已创建索引）

explain format=json select * from t1 where c1 ='a';

Ⅵ、删除索引。

drop index idx_cl on t1;

Ⅶ、再次通过执行计划查看cost值的消耗。（未创建索引）

explain format=json select * from t1 where c1 ='a';

总结：对比两次查询的cost值，通过索引查询的cost值比全表扫描的cost值打。通过索引查询时，索引数据都是重复的（基数低），即做了一个索引全扫描，同时     “ SELECT * ”扫描完索引后要回表查询 id , c2 , c3 这几个字段。

❷清空数据表方便做不同值对比

Ⅷ、清空t1表

truncate table t1;

❸测试不同数据值的cost值的消耗

Ⅸ、插入6条测试数据，其中 c1 为不同的值。

insert into t1 values (10,'a','b','c');

Ⅹ、通过执行计划查看cost值的消耗。（未创建索引）

explain format=json select * from t1 where c1 ='a';

Ⅺ、为 t1 表添加索引。

 create index idx_cl on t1 (c1);

Ⅻ、通过执行计划查看cost值的消耗。（已创建索引）

explain format=json select * from t1 where c1 ='a';

总结：测试不同数据值时，因为字段的值不重复（基数高），通过索引查询的cost值比全表扫描的cost值小。

        一般情况下，在创建索引的时候就应该考虑上面的内容(回表、基数、选择性)，在MySQl中通过系统表innodb_index_stats来查看索引选择性如何、看到组合索引中每个字段的选择性，以及计算索引大小。 

---

 二、MySQL扩展

① SQL优化的重点是：减少SQL语句的扫描行数

② 索引：是一种数据结构，通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。

③ MySQL存储引擎包括：

• InnoDB存储引擎
• MyISAM存储引擎
• MERGE存储引擎
• MEMORY存储引擎
• ARCHIVE存储引擎
• CSV存储引擎
• BLACKHOLE存储引擎（黑洞引擎）
• PERFORMANCE_SCHEMA存储引擎
• Federated存储引擎

④ InnoDB 和 MyISAM区别：

• InnoDB支持主外键、事务；
• InnoDB是行锁，操作时候只锁一行数据，适合高并发；MyISAM是表索；
• InnoDB不仅缓存索引，还缓存真实数据；MyISAM只缓存索引；
• InnoDB需要表空间大；
• InnoDB关注事务，MyISAM关注性能；

---

总结

        创建索引确实会使查询操作变得更加快速，但不能盲目的创建索引，应只为那些查询操作频繁的列创建索引，因为索引会降低添加、删除、更新操作的速度，执行这些操作的同时会对索引文件进行重新排序或更新。

---