第3章 MySQL索引管理

索引在录入数据时 会锁表注意

3.1 查询索引

第一个:

desc 表名;
或
show index from 表名;

image.png image.png

3.2 创建索引 (辅助索引)

辅助索引细分为:

单列

创建方法:

ALTER TABLE city ADD INDEX 索引名(表名);

删除方法

ALTER TABLE city DROP INDEX 索引名;

查看

desc 表名;

唯一索引

条件 需要是唯一值 不能有空值

创建:

ALTER TABLE city ADD UNIQUE INDEX 索引名(表名);

删除:

ALTER TABLE city DROP UNIQUE INDEX 索引名;

查询

SHOW INDEX FROM 表;

联合索引

联合索引 将多个列放在一起做索引

创建:

ALTER TABLE 表名 ADD INDEX 索引名(列名,列名);

删除:

ALTER TABLE 表名 DROP INDEX 索引名;

如何确认一个查询,对于联合索引应用长度

例如我们创建( a,b,c)列的索引

联合索引时 遇到不等值条件时 会既然截停 如果从左到右条件顺序不对时也会截停

联合索引构建过程

创建联合索引注意顺序

在生成叶子节点的时候 按输入的顺序 进行排序 先排列a 在按照b列二次排列

查询的时候 多条件 会先查找a列 然后在找b列 得出聚簇列信息

小结:

1. 构建时,取出ID列 + a列 + b列 依次 a – b -c 的顺序进行数据行排列

2. 支节点和根节点指挥保存最左列的索引值

3. 联合索引查询时 遵循最左原则 最左列尽量使用重复值最少的列

4. 使用时 尽量应用完整

联合索引规则

1. 把唯一值多的放在最前面

2. 理论上要将不等值的列放在最后面

3. key_len 覆盖长度越长越好

4. 查询条件结果

前缀索引

截取前几个字符长度 作为辅助索引数 做辅助列

为的是降低叶子节点/支节点暂用空间 减小索引数的高度

创建

ALTER TABLE city ADD INDEX idx_na(表名(n个字符));

删除

ALTER TABLE city DROP INDEX idx_na;
判断前缀何时长度
计算前缀索引重复度低于百分之10 即可设为前缀索引
alter tables t1 add index idx(name(10));
例如: city表中,name列要做前缀索引,判断多少个字符合适

select count(*) from world.city ;   ----> 4079 
select count(distinct (left(name,5)) from world.city ; ---> 3554

取前五个字符 计算后超过百分之10 

select count(distinct (left(name,6)) from world.city ; ---> 3775

取前六个字符  计算后发现重复值低于百分之10 我们就可以建立以前六个字符创建的前缀索引

3.3 索引树高度影响原因

4. 表内行数多 解决办法 拆分表 : 归档表, 分区表, 分布式系统存储

5. 索引列值过长 解决想法 : 前缀索引

6. 数据类型影响

a) 选择尽量简短的数据类型替代繁琐的数据类型

b) 变长数据最好是vatvhar()

c) 灵活使用enum()类型