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.联合索引的好处
-
避免回表
在执行计划中,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列的单列索引。 -
两个单列查询返回行较多,同时查返回行较少,联合索引更高效。
如果select * from user where id=2 和select * from user where name=’tom’ 各自返回的行数比较多,而select * from user where id=2 and name=’tom’返回的行数比较少,那么这个时候使用联合索引更加高效。
4.什么时候该用联合索引以及如何设计组合索引更高效
-
等值查询中,查询条件a返回的条目比较多,查询条件b返回的条目比较多,而同时查询a、b返回的条目比较少,那么适合建立联合索引;
-
对于有等值查询的列和范围查询的列,等值查询的列建在前、范围查询的列建在后比较实用;
-
如第3点A中的另外说到,如果联合索引列的前置列与索引单列一致,那么单列查询可以用到索引,这样就避免了再建单列索引,因此联合索引的前置列应尽量与单列一致;
5.联合索引在DML时候的性能分析
-
Insert:索引越多插入明显慢得多,这是因为记录必须与索引同时更新,而要维护索引那种有序排列的结构,就必须把新增的索引键值插入到特定的位置,而不是随机排放,这里就涉及到重 组数据的动作,如果索引块存不下,如果索引块存不下,则还要涉及到扩展索引块的动作,这都需要很大的开销。
相对于有序插入,无序插入时索引的影响更加惊人,因为有序插入时,由于插入的数据有一定的顺序,可以在准备工作后快速扩展新块和批量重组,而对于无序操作,批量则不可能。 -
Delete:删除影响所有的索引,在海量数据库定位删除少量记录时,这个条件列是索引列显然是必要的,但过多的索引还是会影响明显,因为其他列的索引也要更新。在经常要删除大量记录的 时候,危害加剧。另外,delete删除索引后,索引块中的相关需要删除记录只是被打上了一个删除标志而已,并没有真正删除。
-
Update:更新的影响最小,如果是更新整条记录则与delete类似,如果是修改某列时,则不会触及到其他索引列的维护。
6.使用联合索引需要注意的地方
-
超过3个列的联合索引不合适,否则虽然减少了回表动作,但索引块过多,查询时就要遍历更多的索引块了;
-
建索引动作应谨慎,因为建索引的过程会产生锁,不是行级锁,而是锁住整个表,任何该表的DML操作都将被阻止,在生产环境中的繁忙时段建索引是一件非常危险的事情;
-
对于某段时间内,海量数据表有频繁的更新,这时可以先删除索引,插入数据,再重新建立索引来达到高效的目的。
数据库的索引
什么是数据库索引?
答:索引是定义在table基础之上,有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构,由一系列存储在磁盘上的索引项组成,每一种索引项由索引字段和行指针构成。
索引的好处?
- 通过创建索引,可以在查询的过程中,提高系统的性能;
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性;
- 在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间;
索引的坏处?
- 创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大;
- 索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大;
- 在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护;
什么是稠密索引和稀疏索引?
- 稠密索引:对于主文件中每一个记录都对应一个索引项;
- 候选键属性的稠密索引:先查索引,然后再依据索引读主文件;
- 非候选键属性的稠密索引:
- 主文件按索引字段排序,索引文件中的索引字段值无重复;
- 主文件索引字段未排序,但索引文件中的索引字段值是有重复的;
- 主文件索引字段未排序且索引文件中的索引字段值无重复,这时可以引入指针桶来处理;
- 稀疏索引:对于主文件中部分记录有索引项和它对应(要求主文件必须是按对应索引字段属性排序存储);
什么是主索引和辅助索引?
- 主索引:对每个存储块有一个索引项,每个存储块的第一个记录叫锚,通常建立在有序文件的基于主码的排序字段上,属于稀疏索引。
- 辅助索引:是定义在主文件的任一或多个非排序字段上的辅助存储结构,属于稠密索引。
补充:一个主文件可以有一个主索引,但可以有多个辅助索引。
什么是聚簇索引和非聚簇索引?
- 聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据,主文件按照对应字段排序存储,索引文件无重复排序存储。
- 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,主文件并没有按照对应字段排序存储,索引文件有重复排序存储。
补充:
- 如果主文件的某一排序字段不是主码,则该字段上每个记录取值便不唯一,此时该字段被称为聚簇字段,聚簇索引通常是定义在聚簇字段上。
- 聚簇索引通常是对聚簇字段上每一个不同值有一个索引项。
- 一个主文件只能有一个聚簇索引文件,但可以有多个非聚簇索引文件。
- 主索引通常是聚簇索引,辅助索引通常是非聚簇索引。
- 主索引/聚簇索引是能够决定记录存储位置的索引,而非聚簇索引则只能用于查询,不能改变物理位置。
B+树
B+树是通过二叉查找树,再由平衡二叉树,B树演化而来。
二叉排序树:左子树的键值总是小于根的键值,右子树的键值总是大于根的键值,因此可以通过中序遍历得到键值的排序输出。
平衡二叉树:首先复合二叉查找树的定义,其次必须满足任何节点的两个子树的高度最大差为1.(平衡二叉树相比于二叉查找树来说,查找效率更稳定,总体的查找速度也更快。)
m阶B-树:树中每个结点至多m棵子树、非叶子结点的根结点至少有两棵子树、除根结点外的非叶子结点至少有m/2棵子树、所有的叶子结点出现在同一层,并且不带信息,通常称为失败结点、树中每个结点的关键字有序,且关键字的左子树中的关键字均小于它,右子树均大于它。(单个节点可以存储多个键值和数据的平衡树)
m阶B+树:与B-树差异在于有n棵子树的结点中含有n个关键字、所有的叶子节点包含了全部关键字的信息,以及指向这些关键字记录的指针、所有的非终端结点可以看成是索引部分,结点中仅含有其子树中最大的关键字。
B+树相比B-树的优先?
- B+ 树非叶子节点上是不存储数据的,仅存储键值,而 B 树节点中不仅存储键值,也会存储数据,如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的 IO 次数又会再次减少,数据查询的效率也会更快。
- 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和<>操作