database table —— index 2

https://blog.csdn.net/CrayonShinChaner/article/details/121787025#t2

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

在数据库中创建一个新表 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关注性能；

总结

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

sql索引的定义以及用法总结

索引定义：SQL Server允许用户在表中创建索引，指定按某列预先排序，从而大大提高查询速度（类似于汉语词典中按照拼音或者字画查找）。

索引作用：通过索引可以大大的提高数据库的检索速度，提高数据库的性能。

 

索引的类型：

1：唯一索引：唯一索引不允许两行有相同的索引值（其实也就是说唯一索引的这一列，每个值都是唯一的）；

例如，如果在stuInfo表中的学员员身份证号(stuID) 列上创建了唯一索引，则所有学员的身份证号不能重复。

2：主键索引：定义表主键的时候，会自动创建主键索引（其实主键索引就是唯一索引的特例），主键索引要求每一个值都是唯一的且非空。

主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

3：聚集索引：表中各行的物理顺序与键值的逻辑顺序相同，每个表只能有一个。

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。

拼音字母a，b，c，d……x，y，z就是索引的逻辑顺序，而页码1，2，3……就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字（词）对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba” 对应的字(词)页码靠后。

4：非聚集索引：非聚集索引指定表的逻辑顺序，数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。

如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的数据访问速度。

例如，按笔画排序的索引就是非聚集索引，“1”画的字（词）对应的页码可能比“3”画的字（词）对应的页码大（靠后）。

提示：SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引

索引的优缺点：

优点：加快访问速度；

加强行的唯一性

缺点：带索引的表在数据库中的存储需要更多的空间；

创建索引的原则：

下列情况下可以使用索引：

该列频繁用于搜索；

该列用于对数据进行排序；

下列情况下避免使用索引：

列中仅仅包含几个不同的值；

表中仅包含几行。为小型表创建索引可能不太划算，因为SQLServer在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长。

sql数据库中的索引

数据库索引：联合索引基本知识

0.预备

假设我们有表 user (id,name)列

---

1.联合索引是个什么东西

我们知道，对于表的单列(如id)数据，是可以建立索引的，对于多列(id和name组合,或者,name和id组合)，也可以建立索引。联合索引，也称之为组合索引。

先来看单列索引的逻辑结构。

 由此得出索引的三个特征：索引有序+高度较低+存储列值

联合索引也满足这三个特征，但这里的逻辑图就不画了，而是以更直观的方式来展现其查找逻辑，这里应注意，联合索引的列有前后，以id列在前、name列在后为例。

建联合索引前的情况

建联合索引后的情况(先按ID排序+再按Name排序)

2.建立联合索引的语法

CREATE INDEX indexName ON tableName(column1,column2,...,columnN);

比如

CREATE idx_un_userid_username ON user(id,name);

 

联合索引可以建立多列(列数大于2)的索引,建议列数最多不要越过3列，超过3列，应重新设计表。

3.联合索引的好处

1. 避免回表
   在执行计划中，table access by index rowid代表是回表动作。
   如在user的id列建有索引，select id from user这个不用回表，直接从索引中读取id的值，而select id,name from user中，不能返回除id列其他的值，所以必须要回表。
   如果建有了id和name列的联合索引，则可以避免回表。
   另外，建立了id和name的联合索引(id列在在前)，则select id from user可以避免回表，而不用单独建立id列的单列索引。

2. 两个单列查询返回行较多，同时查返回行较少，联合索引更高效。
   如果select * from user where id=2 和select * from user where name=’tom’ 各自返回的行数比较多，而select * from user where id=2 and name=’tom’返回的行数比较少，那么这个时候使用联合索引更加高效。

 

4.什么时候该用联合索引以及如何设计组合索引更高效

1. 等值查询中，查询条件a返回的条目比较多，查询条件b返回的条目比较多，而同时查询a、b返回的条目比较少，那么适合建立联合索引；

2. 对于有等值查询的列和范围查询的列，等值查询的列建在前、范围查询的列建在后比较实用；

3. 如第3点A中的另外说到，如果联合索引列的前置列与索引单列一致，那么单列查询可以用到索引，这样就避免了再建单列索引，因此联合索引的前置列应尽量与单列一致；

---

5.联合索引在DML时候的性能分析

• Insert：索引越多插入明显慢得多，这是因为记录必须与索引同时更新，而要维护索引那种有序排列的结构，就必须把新增的索引键值插入到特定的位置，而不是随机排放，这里就涉及到重 组数据的动作，如果索引块存不下，如果索引块存不下，则还要涉及到扩展索引块的动作，这都需要很大的开销。
  相对于有序插入，无序插入时索引的影响更加惊人，因为有序插入时，由于插入的数据有一定的顺序，可以在准备工作后快速扩展新块和批量重组，而对于无序操作，批量则不可能。

• Delete：删除影响所有的索引，在海量数据库定位删除少量记录时，这个条件列是索引列显然是必要的，但过多的索引还是会影响明显，因为其他列的索引也要更新。在经常要删除大量记录的 时候，危害加剧。另外，delete删除索引后，索引块中的相关需要删除记录只是被打上了一个删除标志而已，并没有真正删除。

• Update:更新的影响最小，如果是更新整条记录则与delete类似，如果是修改某列时，则不会触及到其他索引列的维护。

6.使用联合索引需要注意的地方

1. 超过3个列的联合索引不合适，否则虽然减少了回表动作，但索引块过多，查询时就要遍历更多的索引块了；

2. 建索引动作应谨慎，因为建索引的过程会产生锁，不是行级锁，而是锁住整个表，任何该表的DML操作都将被阻止，在生产环境中的繁忙时段建索引是一件非常危险的事情；

3. 对于某段时间内，海量数据表有频繁的更新，这时可以先删除索引，插入数据，再重新建立索引来达到高效的目的。

数据库的索引

什么是数据库索引？

答：索引是定义在table基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项组成，每一种索引项由索引字段和行指针构成。

索引的好处？

1. 通过创建索引,可以在查询的过程中,提高系统的性能；
2. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性；
3. 在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间；

索引的坏处？

1. 创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大；
2. 索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大；
3. 在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护；

什么是稠密索引和稀疏索引？

• 稠密索引：对于主文件中每一个记录都对应一个索引项；
  • 候选键属性的稠密索引：先查索引，然后再依据索引读主文件；
  • 非候选键属性的稠密索引：
    • 主文件按索引字段排序，索引文件中的索引字段值无重复；
    • 主文件索引字段未排序，但索引文件中的索引字段值是有重复的；
    • 主文件索引字段未排序且索引文件中的索引字段值无重复，这时可以引入指针桶来处理；

• 稀疏索引：对于主文件中部分记录有索引项和它对应（要求主文件必须是按对应索引字段属性排序存储）；

什么是主索引和辅助索引？

• 主索引：对每个存储块有一个索引项，每个存储块的第一个记录叫锚，通常建立在有序文件的基于主码的排序字段上，属于稀疏索引。
• 辅助索引：是定义在主文件的任一或多个非排序字段上的辅助存储结构，属于稠密索引。

     补充：一个主文件可以有一个主索引，但可以有多个辅助索引。

什么是聚簇索引和非聚簇索引？

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据，主文件按照对应字段排序存储，索引文件无重复排序存储。
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，主文件并没有按照对应字段排序存储，索引文件有重复排序存储。

补充：

1. 如果主文件的某一排序字段不是主码，则该字段上每个记录取值便不唯一，此时该字段被称为聚簇字段，聚簇索引通常是定义在聚簇字段上。
2. 聚簇索引通常是对聚簇字段上每一个不同值有一个索引项。
3. 一个主文件只能有一个聚簇索引文件，但可以有多个非聚簇索引文件。
4. 主索引通常是聚簇索引，辅助索引通常是非聚簇索引。
5. 主索引/聚簇索引是能够决定记录存储位置的索引，而非聚簇索引则只能用于查询，不能改变物理位置。

B+树

B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。

二叉排序树：左子树的键值总是小于根的键值，右子树的键值总是大于根的键值，因此可以通过中序遍历得到键值的排序输出。

平衡二叉树：首先复合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1.(平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。)

m阶B-树：树中每个结点至多m棵子树、非叶子结点的根结点至少有两棵子树、除根结点外的非叶子结点至少有m/2棵子树、所有的叶子结点出现在同一层，并且不带信息，通常称为失败结点、树中每个结点的关键字有序，且关键字的左子树中的关键字均小于它，右子树均大于它。（单个节点可以存储多个键值和数据的平衡树）

m阶B+树：与B-树差异在于有n棵子树的结点中含有n个关键字、所有的叶子节点包含了全部关键字的信息，以及指向这些关键字记录的指针、所有的非终端结点可以看成是索引部分，结点中仅含有其子树中最大的关键字。

 

B+树相比B-树的优先？

1. B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。
2. B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

建立索引的时机是什么？

答：在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引（以通配符%和_开头作查询时，MySQL不会使用索引）。

• 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

• 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

• 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

• like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

• 不要在列上进行运算
• 不使用NOT IN和<>操作