索引

1. 查询优化

• 缓存查询
• 全表扫描
• 索引扫描

Btree  查单个数据，或者是在同一个叶子上的（不在一个叶子上会拖慢查询效率）
三层，根，枝，叶

B+tree   
记录了相邻叶子节点的指针（双向链表）
更适合查一个范围（即使数据不在一个叶子上，通过指针跳转）

b*tree
在枝节点做上了双向链表，优化范围查询

2. 索引

• 索引种类

Btree  Rtree HASH  FullText

• Btree 索引分类

聚集索引：基于主键，自动生成，没有主键就选择唯一键，数据都在叶子节点上，根和枝节点存主键标识

辅助索引：
叶子节点只存列的值和聚集索引对应的键值
普通索引  一个列建一个索引;
覆盖索引，多个列建一个索引，不需要再通过聚集索引查询，不需要回表查;
特点：最左匹配原则，假如将abc三个列建索引，走索引的查询有ab,a ,abc,  不走索引的情况b,c,bc;如果更多的是单个字段查询就用普通索引

唯一索引：
没有主键时做为聚集索引；
列值唯一，有主键时就是普通索引，;
列的值有重复时，唯一索引更好

• 聚集索引和辅助索引区别

1. 聚集索引的叶子节点存储的是整行数据,就是数据页;
2. 辅助索引的叶子节点存列值和对应的主键值（便于回表查询，有条件的查询）,

• 索引管理

1. 辅助索引(MUL)
创建： 在BBS表中给email，phone字段创建索引
alter table BBS add key idx_email(email);
create index idx_phone on BBS(phone);

查看索引：
desc 表名;
show index from 表名;

删除索引：
alter table 表名 drop index idx_email;
drop index idx_phone on  表名;

2. 前缀索引(MUL)
叶子节点固定16kb,列值越长叶子节点越多，导致枝节点越多，层级变多，效率变慢

保证前几个字符确认唯一的一个列，适用于较长的字段

注：将password字段的前10个字符建成索引
alter table 表名 add index idx(password(10));

3. 唯一键索引(UNI)，列里面不能有重复值
alter table 表名 add unique key uni_email(email);

4. 覆盖索引(联合索引)
减少回表查询，数据从辅助索引中就可以获取
alter table t1 add index idx_gam(gender,age,money);

注：mysql8.0版本后可以先让索引失效，用的时候再生效，就不需要继续创建和删除的操作了
\G 查看

• explain

导入sql文件
mysql> source /root/world.sql

explain select * from city where CountryCode="CHN";

重要字段：
type 查询类型
作用：判断是全表扫描，还是索引扫描(ALL是全表扫描，其他就是索引扫描)
判断出具体哪一种类的索引扫描
type具体类型介绍：
    ALL：全表扫描
    index：全索引扫描
    range:索引范围扫描
    ref:辅助索引的等值查询
    eq_ref:	多表链接查询(join on )
	const ,system :主键或唯一键等值查询
注：性能逐渐增强，type的类型是range级别以上，建索引才有意义

Extra:
using  filesort 文件排序，占用CPU时间
将order by  group by  distinct 后的列和where条件列建立联合索引

3. 索引使用原则

3.1 索引建立规范

• 建表时一定要有主键
• 选择唯一性索引(主键索引和唯一索引在查询中使用的效率最高，如果重复值较多可以使用联合索引)
• 为排序，分组和联合操作的字段建索引
• 做为where查询条件的字段建索引
• 字段值较长，用前缀来索引
• 清除不常使用的索引

3.2 不使用索引情况

• 没有查询条件
• 查询的结果集是原表中的大部分数据（25%以上）
• 索引失效，统计的数据不真实
• 对查询条件进行运算
• 隐式转化导致索引失效

表是t1,字段telnum，type类型是char,值是数字110
select * from t1 where telnum=110;  是全表扫描（ALL）
select * from t1 where telnum='110';   等值查询（ref)

• like "%_" 百分号在最前面
• 引用联合索引里非第一位置的索引列作为查询条件
• <> ，not in 不走索引