sql server 表没有索引会怎么样 sql表的索引

转载

话不是这么说的 2023-11-21 12:05:49

文章标签 sql 数据库索引主键存储引擎 文章分类 SQL Server 数据库

文章目录

前言

一、MySQL索引

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

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

3、基数、选择性、回表

二、MySQL扩展

总结

sql server 表没有索引会怎么样 sql表的索引_索引

前言

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

sql server 表没有索引会怎么样 sql表的索引_索引

一、MySQL索引

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

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

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

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

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

sql server 表没有索引会怎么样 sql表的索引_主键_06

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

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

建议：在MySQL中创建表时，尽量指定一个显示的自增主键
优势：显示指定的主键可以是普通的int类型，这样存储空间就是4字节，在二级索引的叶子结点中存储主键的所占用空间就会变小
扩展：二级子节点之所以选择存储主键的值，而不是存储主键的指针，是为了避免如果主键位置发生变化时，需要修改二级索引的叶子节点对应存储的指针。

sql server 表没有索引会怎么样 sql表的索引_主键_06

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来查看索引选择性如何、看到组合索引中每个字段的选择性，以及计算索引大小。