深入索引实现原理

雨点打透心脏的1/2处 2022-06-06 14:08 260阅读 0赞

## 一 、基本概念： ##

primary key :  
与外键 生成完整性约束 ， 唯一 非空 。 自动生成唯一索引

foreign key :  
一个表的外健 就是另外一个表的主键

index :  
加快访问速度，排序 ，索引不能做为外键

## 二、 外键: ##

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model 给 pc 表 设置一个外键 ，外键名叫 fk_cpu_model FOREIGN KEY (cpumodel) 外键在 cpumodel 上生效 REFERENCES parts(model);     约束自 parts 表的 model 字段

## 三、索引操作: ##

1. 普通索引
    
    创建索引：           CREATE INDEX <索引的名字> ON tablename (列的列表)； 修改索引： ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表)； 创建表的时候指定索引： CREATE TABLE tablename ([…],INDEX[索引的名字](列的列表));
    删除索引：           DROP INDEX index_name ON talbe_name/ALTER TABLE table_name DROP INDEX index_name 2. 唯一性索引 创建索引： CREATE UNIQUE INDEX <唯一索引的名字> ON tablename (列的列表)； 修改索引： ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)； 创建表的时候指定索引： CREATE TABLE tablename ([…],UNIQUE[索引的名字](列的列表));
    删除索引：           ALTER TABLE table_name DROP PRIMARY KEY 3. 主键 修改索引： ALTER TABLE tablename ADD PRIMARY KEY(列的列表);
    
    4. 全文索引
    
    修改索引：           ALTER TABLE tablename ADD PRIMARY KEY(列的列表);
    
    5. 聚集索引
    
       索引的键值的逻辑顺序决定了表中相应行的物理顺序，所以一个表只能有一个聚集索引，但该索引可以包含多个列，聚集索引对于要搜索范围值的列很有效果，因为往往查找到了查询范围的第一个列之后，后续索引的值都在物理相邻。
        主键为聚集索引时，整张表就会以主键生成一个索引。（一个以主键排序规则生成的平衡树）    
    
    6. 组合索引
    创建索引：           ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
                       限制索引字段长度可以减少索引文件大小，提高 insert 效率 会建立 如下索引： name,city,age | name,city | name mysql索引最左前缀原则 *注意事项*： 用到上述三个索引的查询才会用到该组合索引 用like 时候，如果是以 %开头 则不会使用索引 修改索引： ALTER TABLE tablename ADD PRIMARY KEY(列的列表);

以4000w用户表为例子 。 索引和非索引 25s 1.9ms 。 创建索引时间耗费：58.81s

## 四、创建索引注意事项 ##

1.  不允许为 NULL－－－不会被创建成索引，会导致组合索引失效 。 小数据 简单的数据类型（int来存储ip地址）
2.  不要模糊查询 %开头
3.  不要进行运算 不要用 NOT IN 这些都是会导致索引失效的
4.  创建索引会使查找变快，增加删除 修改时候也会维护索引，降低速度了。不过一般没影响，就是看中读速度 不注重写速度。

## 五、创建索引的实质： ##

为什么创建索引就变快了：（实现原理）  
B树 二叉树 （平衡，非平衡） 一个节点一个关键字 小左大右  
B－树 多路搜索树 多叉  
B＋树 多路搜索树 关键字都在叶子节点中

有索引的时间复杂度是 O(logn) 以 查找次数＝ log(4000W,10) 这里结果是小于4的 假设搜查结果为1亿结果也只是各位数的

O（Logn）< O(n)

![这里写图片描述][SouthEast]

对于非聚集索引，也就是常规的，主键表，加上常规的索引。

非聚集索引 也是平衡树， 只不过每添加一个索引，就会再主键的聚集索引之外再单独创建一个索引字段的新的索引文件。

对于聚集索引，可以通过聚集索引查到数据，而非聚集索引，需要通过主键索引查到主键值，然后再去查找对应字段索引的值

## 六、聚集索引和非聚集索引区别： ##

create index index_birthday on user_info(birthday);
    创建一个 user_info 上 以 birthday 字段的 名为 index_birthday 的索引
    
    select user_name from user_info where birthday = '1991-11-1’ 查询 birthday 为1991-11-1 的记录 首先去查找 birthday 索引对应的主键ID记录 ，在去聚集索引查找对应ID的的真实数据存储的位置，最后返回取得数据的username 替换为双字段的组合索引： create index index_birthday_and_user_name on user_info(birthday, user_name); 这样如上面组合索引的 最左前缀 原理，实际是创建了 username，birthday 和 birthday 两个索引 所以上面的查询username 的就变成了直接从组合索引去查找，直接返回username

[SouthEast]: /images/20220606/b67d515eea1a493aac338e20d7dc6860.png