SQL 索引

最新推荐文章于 2024-10-09 23:12:44 发布

ash_to_dast

最新推荐文章于 2024-10-09 23:12:44 发布

阅读量175

点赞数

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.csdn.net/weixin_41730591/article/details/103306117

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

参考文章：MySQL索引（细节十分清楚

索引

数据量较大的情况下，减少IO次数——加速查询
其中的primary key 不能NULL，唯一，UNIQUE唯一，还有约束作用）
建立索引，也就是建立目录的过程
提前建立索引：如果有大量数据再建，创建速度很慢。
索引的数据结构之一——BTREE

Btree 原理

B+树上面是树根，中间是树枝，下面是叶子节点，每一个块是磁盘块（BLOCK块），代表一次IO往内存读取的内容，每个块里面有多个数据项和指针，节点不包括指针，其中节点处的磁盘块内的数据项才是真实的数据，上面的数据项相当于假想的数据（目录）。比如寻找17-35之间的某一个数据，那么从上面磁盘块开始，找到介于两者之间的磁盘块，最后往下找到一个对应的节点。
h=log(m+1)/N，数据量N是不变的，m代表的是每个磁盘块的数据项的数量，m越大，数据项数量越多，那么h越小，IO次数越少。这也是为什么把真实的数据项放在节点位置，一旦放在内层的节点，那么数据项数量会下降，导致h升高。并且这也需要我们建立索引的数据项的所占空间越小越小，比如更多倾向于使用Id作为主键而不是name。

聚集索引和辅助索引

聚集索引

对于MyISAM引擎会单独建立索引文件，但是InnoDB把数据和索引放在一个文件里面。
对于InnoDB来说
（1）如果有主键，那么就是聚集索引
（2）如果没有，取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键来聚集索引
（3）如果都不符合，自己内部创建一个隐藏的主键索引（主键索引对于InnoDB来说就是用来存储数据的原则，便于后期的查询

辅助索引（非聚集

辅助索引的叶子节点不包含记录的全部数据，但是聚集索引包括
辅助索引如果可以直接在叶子结点找到想要的数据，那么是覆盖索引
辅助索引的叶子结点包括辅助索引的数据和主键值，这样如果辅助索引在叶子结点找不到想要的数据，那么就可以再次聚类索引一次Btree

主要的几个索引

普通索引INDEX：加速查找
唯一索引：
主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
唯一索引UNIQUE:加速查找+约束（不能重复
联合索引：
PRIMARY KEY(id,name):联合主键索
UNIQUE(id,name):联合唯一索引
INDEX(id,name):联合普通索引

索引的创建与删除

聚类索引
主键索引

CREATE TABLE t1 (ID int primary key);
CREATE TABLE t1 (ID int,PRIMARY KEY(ID));
ALTER TABLE t1 ADD PRIMARY KEY(ID);
ALTER TABLE t1 DROP PRIMARY KEY;

唯一索引

CREATE TABLE T1
(ID INT UNIQUE);
CREATE TABLE T1 
(ID INT,UNIQUE KEY uni_name(ID));
ALTER TABLE T1 ADD UNIQUE KEY uni_name(ID);
ALTER TABLE T1 DROP INDEX uni_name;

普通索引

CREATE TABLE T1
(ID INT,INDEX index_name(ID));
CREATE index index_name ON T1;
ALTER TABLE T1 ADD index index_name(ID);
ALTER TABLE T1 DROP INDEX index_name;
DROP INDEX index_name ON T1;