10. 索引

1. 索引种类

B树
R树
hash
全文

2. B树索引类型

1.聚簇索引（好处：减少回表，将随机io转换成顺序io）
2.辅助索引（好处：减少排序）
    2.1 普通辅助索引
    2.2 覆盖索引（好处：减少回表查询几率）
3.唯一键索引

3. 索引作用

优化查询
排除缓存，数据库的查询
    全表扫描
    索引扫描

4.索引管理

4.1 创建索引

alter table stu add index idx_name(sname);

#方法2（不常用）
create index idx_name on stu(sname);

4.2 删除索引

alter table stu drop index idx_name;

#方法2（不常用）
drop index idx_name on stu(sname);

4.3 查看索引

desc stu;
show index from stu\G

4.5 联合索引

alter talbe city add index idx_co_po(countrycode,population);

4.6 前缀索引

alter table city add index idx_dis(district(10));

4.7 唯一索引

alter table city add unique index idx_na(name);

5. B树索引结构（原理，未整理）

数据页：16kb

# 参考：https://blog.csdn.net/qq_42415326/article/details/90084381?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164575952516780271922868%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=164575952516780271922868&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-5-90084381.pc_search_result_cache&utm_term=B%E6%A0%91%E7%B4%A2%E5%BC%95%E5%8E%9F%E7%90%86%E9%80%9A%E4%BF%97%E6%98%93%E6%87%82&spm=1018.2226.3001.4187

6. explain(desc)命令的应用

获取优化器选择后的执行计划

6.1 查看执行计划

explain select id,name from student where name='oldboy'\G

#重要参数
type:           #索引类型
possible_keys:   #可能会走的索引
key:            #真正走的索引
extra:          #额外信息

6.2 索引扫描类型type

1.all：全盘扫描
explain select * from city;
线上业务一般不会出现

2.index:索引扫描
explain select countrycode from city;
基本很少出现

3.range:索引范围扫描
explain select * from city where countrycode like 'ch%';
explain select * from city where countrycode in('chn','usa');
><like有额外优化
而in/or没有优化

#注意：%在前走的是all扫描！！！
explain select * from city where countrycode like '%hn';

4.ref:辅助索引等值查询
explain select * from city where countrycode='chn';
union all
select * from city where countrycode='usa';

5.system/const:
#where条件列是主键或唯一键的等值查询时
explain select * from city where id=1;

6.3 extra

using temporary
using filesort    #####重点
using join buffer
如果出现以上附加信息，检查order by,group by,distinct,join条件列上有没有索引!!!

案例：
explain select * from city where countrycode='chn' order by population;
添加索引后，效率提升
alter table city add index idx_po(population);
explain select * from city where countrycode='chn' order by population;

如果查询条件符合覆盖索引时，优先选择覆盖索引
不符合覆盖索引时，优先走where条件的索引

优化方法：将where列和order列建立联合索引
explain select * from city where countrycode='chn' order by population desc limit 10;
alter table city add index idx_co_po(countrycode,population);

6.4 explain的使用场景

1.MySQL出现性能问题
2.获取到问题语句
    数据库卡住了
    某一段时间慢

可以写入简历

（1）背景：数据库卡住
处理过程：
    1.show processlist;获取问题语句
    2.explain分析执行计划，有没有走索引，索引的类型
    3.建立索引或者更改语句

（2）背景：一段时间慢
处理过程：
    1.记录慢日志slowlog，分析slowlog
    2.explain法分析SQL执行计划，有没有走索引，索引的类型
    3.建索引，改语句

7. 建立索引的原则（运维规范）

7.1 建表时必须要有主键，如果没有可以作为主键条件的列，则创建无关列

主键索引和唯一键索引，在查询中使用是效率最高的

7.2 经常作为where条件的列，order by，group by，join on的条件列，创建索引

1.如果某个字段经常用来作为查询条件，那么该字段的查询速度会影响整个表的查询速度。为这样的字段建立索引，可以提高整个表的查询速度。
    1.1 经常查询
    1.2 列的重复值少（业务层面调整）
2.经常order，group，join等操作的字段，排序操作会浪费很多时间，如果为其建立索引，可以有效避免排序操作

7.3 最好使用唯一值多的列作为索引，如果索引列重复值较多，可以使用联合索引

7.4 列值长度较长的索引列，建议使用前缀索引

例如，text、blog类型的字段，进行全文检索会很浪费时间。
如果只检索字段的前面的若干个字符，可以提高检索速度

以上是重点关注的，以下的是能保证则保证的

7.5 降低索引条目，一方面不要创建没用的索引，不常使用的索引要清理

索引不是越多越好。
1.每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大。
2.修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

表中的数据呗大量更新，或者数据的使用方式改变后，原有的索引可能不再需要。
数据库管理员应当定期找出这些索引，将他们删除，从而减少索引对更新操作的影响。

7.6 索引维护要避开业务繁忙期

大表加索引，要在业务不繁忙期间操作

7.7 开发和DBA经常吵架的原因

因为索引维护的问题，索引会造成插入较慢。

解决办法：
    插入大量数据时，可以删掉索引
    但是一定要记住，当输入完成后，必须再把索引创建好！！！

8. 不走索引的情况（开发规范）

8.1 没有查询条件，或者查询条件没有建立索引

（1）select * from t1;
在业务数据库中，特别是数据量比较大的表，没有全盘扫描这种需求！
    1.对用户查看来讲是非常痛苦的。
    2.对服务器来讲是毁灭性的。

（2）select * from t1 where name='z3';   #name列没有索引

解决办法：
    1.换成有索引的列作为查询条件
    2.将name列建立索引

8.2 查询结果集是原表中的大部分数据（原表的25%以上）

当查询的结果集超过总行数的25%，则优化器觉得就没有必要走索引了。
例：t1表id，name，id：1-100w，id列有索引
select * from t1 where id>500000;

解决办法：
    1.如果业务允许，可以使用limit控制。
    2.结合业务判断，有没有更好的方式，如果没有更好的方案，尽量不要在mysql存放这个数据，可以放到redis里。

id>2 and id<6这种情况可以改写成如下
id=3
union
id=4
union
id=5

8.3 索引本身失效，统计 数据不真实

索引有自我维护的能力
对于表内容变化比较频繁的情况，有可能会出现索引失效。

解决办法：
    1.重建索引
    2.定期检查

8.4 查询条件中使用函数在索引列上，或者对索引列进行运算（+-*/！等）

1.算术运算
    例：select * from test where id-1=9;
    改写：
    select * from test where id=10;
2.函数运算 
3.子查询

8.5 隐式转换导致索引失效（这点必须重视，开发中经常会犯）

例：
create table people(id int not null primary key auto_increment,name varchar(20) not null,telnum char(11) not null unique key);
insert into people(name,telnum) values('zs',110),('l4','119'),('w5',120);
explain select * from people where telnum=110;      #全表扫描
explain select * from people where telnum='110';    #const扫描
慢日志会记录下此条语句！

8.6 单独的>,<,in有可能走索引，也有可能不走索引，和结果集有关，尽量结合业务添加limit

例：
explain select * from people where telnum <> '110';
explain select * from people where telnum not in ('110','119');

8.7 模糊查询（like"%_"）百分号在最前面不走索引

例:
explain select * from people where telnum like '11%'; #range索引扫描
explain select * from people where telnum like '%10'; #不走索引

对于%10%类的搜索需求，可以使用elasticsearch数据库

8.8 单独引用联合索引里非第一位置的索引列作为条件查询时，不走索引

例：复合索引
create table t1(id int,name varchar(20),age int,sex enum('m','f'),money int);
alter table t1 add index idx_m_a_s(money,age,sex);

走索引的情况：
explain select name,age,sex,money from t1 where money=30 and age=30 and sex='m';
explain select name,age,sex,money from t1 where money=30 and age=30;
explain select name,age,sex,money from t1 where money=30 ande sex='m';
不走索引的情况：
explain select name,age,sex,money from t1 where age=20 and sex='m';
explain select name,age,sex,money from t1 where age=20;
explain select name,age,sex,money from t1 where sex='m';

疑问的情况：
explain select name,age,sex,money from t1 where age=20 and sex='m' and money=30;
explain select name,age,sex,money from t1 where age=20 and money=30;
explain select name,age,sex,money from t1 where sex='m' and money=30;

---

最后更新: 2022-02-25 03:53:42