1.索引
1.1 作用
提供了类似于书中目录的作用,目的是为了优化查询
1.2 索引的种类（算法）
B树索引
Hash索引
R树
Full text
GIS
image.png
image.png
1.3 B树算法普及
B-tree
B+tree
B*tree
1.4 在功能上的分类
1.4.1 辅助索引（二级索引）（S）怎么构建B树结构的?
（1）辅助索引是基于表的列进行生成的
（2）取出索引列的所有值（取出所有键值）
（3）进行所有键值的排序
（4）将所有的键值按顺序落到BTree索引的叶子节点上
（5）进而生成枝节点和根节点
（6）叶子节点除了存储键值之外，还存储了相邻叶子节点的指针，另外还会保存原表数据的指针
1.4.2 聚集索引（C）怎么构建B树结构的?
（1）建表时有主键列（ID）
（2）表中进行数据存储，会按照ID列的顺序，有序的存储一行一行的数据到数据页上（这个动作叫做聚集索引组织表）
（3）表中的数据页被作为聚集索引的叶子节点
（4）把叶子节点的主键值生成上层的枝节点和根节点
1.4.3 聚集索引和辅助索引构成区别总结？
聚集索引只能有一个,非空唯一,一般是主键
辅助索引,可以有多个,是配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构
1.5 辅助索引的细分
单列的辅助索引
联合多列辅助索引（覆盖索引）
唯一索引
1.6 关于索引树的高度受什么影响？
（1）数据行多 ---> 分表
（2）索引列的字符长度 ---> 前缀索引
（3）char varchar --->表设计可理
（4）enum 优化索引高度 --->能用则用
2. 执行计划
2.1 作用
上线新的查询语句之前，进行提前预估语句的性能
在出现性能问题时，找到合理的解决思路
2.2 获取执行计划
mysql [world]>desc select * from oldboy.t100w where k2='OPWX'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t100w
partitions: NULL
type: ref
possible_keys: idx_k2
key: idx_k2
key_len: 17
ref: const
rows: 276
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
table: t100w
type: ref --->索引的应用级别
possible_keys: idx_k2 ---> 可能会使用到的索引
key: idx_k2 ---> 实际上使用的索引
key_len: 17 --->联合索引覆盖长度
rows: 276 --->查询的行数（越少越好）
filtered: 100.00 --->额外的信息
2.3 执行计划的分析
type: ref --->索引的应用级别
（1）ALL：全表扫描，不走索引
没建索引，不走索引
建了索引，不走索引
（2）Index：全索引扫描
mysql [munan]>desc select k2 from t100w;
（3）range：索引范围扫描
（辅助索引：> < >= <= lirke in or）
主键：!=
mysql [munan]>desc select * from world.city where id>3000;
mysql [munan]>desc select * from world.city where id!=3000;
mysql [munan]>desc select * from world.city where countrycode like 'C%';
mysql [munan]>desc select * from world.city where countrycode in ('CHN','USA');
修改：
mysql [munan]>desc select * from world.city where countrycode='CHN' union all select * from world.city where countryycode='USA';
（4）ref：辅助索引
mysql [world]>desc select * from world.city where countrycode='CHN';
（5）eq_ref：在多表连接查询时 on 的条件列是唯一索引或主键
mysql> desc select a.name,b.name ,b.surfacearea
from city as a
join country as b
on a.countrycode=b.code
where a.population <100;
（6）const,system：主键或唯一键等值查询
（7）Extra：NULL 额外的信息
出现Using filesort
解决方法：
2.4 expla(desc)使用场景（面试题）
题目意思: 我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist; 获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
（2）一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句
3. 索引引用规范
业务
1.产品的功能
2.用户的行为
"热"查询语句 ---> 较慢---> slowlog
"热"数据
3.1 建立索引的原则（DBA运维规范）
3.1.0 说明
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?
3.1.1 （一定要做）建表时一定要有主键，一般是个无关列
3.1.2 选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
优化方案：
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;
3.1.3 (一定要做的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段，
排序操作会浪费很多时间。
where A B C ----》 A B C
in
where A group by B order by C
A,B，C
如果为其建立索引，优化查询
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。
3.1.4 尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。
3.1.5 限制索引的数目
索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用
3.1.6 删除不再使用或者很少使用的索引(percona toolkit)
pt-duplicate-key-checker
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
3.1.7 大表加索引,要在业务不繁忙期间操作
3.1.8 尽量少在经常更新值的列上建索引
3.1.9 建索引原则总结
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
3.1.10 关于联合索引（重点！！！）
（1）where A、GROUP BY B、ORDER BY C --->（A,B,C）
（2）where A B C
---> 都是等值，在5.5以前无关索引顺序，把控一个原则唯一值多的列放在联合索引的最左侧
---> 如果有不等值，例如select ... ...where A= and B> and c=
索引顺序，ACB，语句改写为ACB
网友评论