一、数据库索引扩展信息¶
Extar列表示额外的情况或额外的信息说明，其中重点需要关注点信息为：filesort 表示涉及到额外排序操作，将严重浪费CPU资源；
哪些查询语句情况涉及到排序操作：
- 情况一：查询语句中含有 order by ，表示触发式的排序；
- 情况二：查询语句中含有 group by，表示隐藏式的排序；
- 情况三：查询语句中含有 DISTINCT，表示会先进行排序后再取消重复；
数据库查询出现排序情况演示说明：
进入到world数据库中，查看city索引信息设置，并将额外无用索引进行清理：
# 查看指定表索引信息
mysql> use world;
mysql> show index from city;
# 删除无用索引信息
mysql> alter table city drop index ix_na_po;
mysql> alter table city drop index ix_n;
mysql> alter table city drop index idx;
索引信息清理后，索引状态情况：
利用辅助索引信息作为条件，查看所有中国的城市情况信息：
mysql> select * from city where countrycode='CHN';
mysql> desc select * from city where countrycode='CHN';
-- 获取默认查询执行计划情况
在没有进行排序前的执行计划情况：
模拟出现查询数据的排序情况：
# 模拟情况一：利用order by实现排序
mysql> desc select * from city where countrycode='CHN' order by population;
执行计划信息输出：
执行计划优化处理：
# 错误设想创建索引：因为本身索引构建过程就存在自动排序问题
alter table city add index idx(population)
实际效果信息输出：
没有改变最终的执行计划结果，因为在使用索引时，只能使用单一的索引树，不能跨越多颗索引树进行使用，因此优化失败；
# 正确优化处理方式：创建联合索引
mysql> alter table city add index idx1(countrycode,population);
实际效果信息输出：
特殊情况说明：在order by信息出现在group by之后，是无法实现索引优化处理的
# 模拟情况二：利用group by实现排序
mysql> desc select district,count(*) from city where countrycode='CHN' group by district;
mysql> desc select district,count(*) from city where countrycode='CHN' group by district order by sum(population);
实际效果信息输出：
从上图执行计划输出信息可以看出，因为group by操作后，已经将数据信息存放在了临时表中，order by排序就不能再用索引了；
二、数据库索引应用总结¶
2.1 建立索引原则规范（DBA运维规范）¶
- 数据表中必须要有主键索引（创建表时指定），建议是与业务无关的自增列；
- 数据表中某些列若经常作为 where/order by/group by/join on/distinct条件信息，最好将相应列设置索引（产品功能/用户行为）
- 数据表中最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引；（最左列-减少回表次数 - 减少磁盘IO）
- 数据表中列值长度较长的索引列，建议可以使用前缀索引；(防止索引树层次过高)
- 数据表中不建议建立大量索引，最好降低索引条目，不要创建无用索引，不常用的索引要定期清理（percona toolkit）
- 数据表中的索引信息做调整维护时，尽量避开业务繁忙期，或者通过软件工具做调整维护（pt-ost）
- 数据表中的联合索引创建过程要遵循索引最左原则；
2.2 索引应用失效情况（开发工作规范）¶
- 数据表信息查询时，没有设置查询条件信息；
- 数据表信息查询时，查询的条件没有建立索引；
-- 执行SQL注入语句问题
select * from t1;
select * from t1 where id=1001 or 1=1;
2.3 查询结果规范要求¶
当查询结果集数据是原表中的大部分数据，超过了总行数的25%，优化器便自动判断没必要走索引了，因为可以借助预读功能获取数据
可以通过精细查找指定数据的范围，从而达到优化的效果；（read_head预读相关参数）
2.4 索引失效情况处理¶
当频繁的对数据表中索引列值做修改、删除等操作时，会导致索引统计信息过旧或不真实，最终造成索引功能失效；
本身索引是有自我维护的机制能力，但并不是实时调整更新的，需要有一定的间隔时间做调整；
一般索引失效的表现情况为：select查询语句平常查询时很快，但突然某天执行就变慢了，就是索引失效了，统计数据不真实；
索引统计的信息存储位置：
-- mysql库中的相应表
innodb_index_stats
innodb_table_stats
当索引失效时，可以使用命令重新进行统计信息获取，使索引功能再次生效：
-- 表示立即更新过久的统计信息（也可以将索引删除重建）
mysql > analyze table world.city;
在查询条件过程中，使用了函数信息在索引列上，或者对索引进行了运算（+ - * / ！等），都会导致索引功能失效，建议尽量避免；
# 错误举例：
select * from test where id-1=9;
# 正确举例：
select * from test where id=10;
-- 总之尽量避免条件信息出现 算数运算 函数运算 子查询
# 子查询补充：
# 子查询指一个查询语句嵌套在另一个查询语句内部的查询
# SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据
# 查询中国城市人口大于北京人口数量的城市信息
查询01：查询中国 北京的人口数量
select population from city where countrycode='CHN' and name='Peking';
查询02：查询中国 基于北京人口数量 大于北京人口数量城市
select * from city where countrycode='CHN' and population > 'xxx'
mysql> select * from city where CountryCode='CHN' and population > (select population from city where CountryCode='CHN' and name='Peking');
在查询数据信息过程中，出现了隐式转换也会导致索引失效；
# 创建测试数据表
mysql> create table test (id int,name varchar(20),telno char(11));
mysql> insert into test values (1,'a','110'),(2,'b','123'),(3,'c','120'),(4,'d','119'),(5,'e','130');
# 创建索引信息
mysql> alter table test add index idx(telno);
# 查询数据信息
mysql> select * from test where telno='110';
mysql> select * from test where telno=110;
-- 上面两条语句都能查看到结果信息，但是有一条语句是没有走索引的
执行计划结果输出：
因为本身查询条件列的数据类型为字符类型，但是作为条件时当成了数字类型，数据库会将数值类型通过隐式转换函数转换为字符类型；
由于，条件中若加上了函数信息，就会导致索引功能失效，所以隐式转换也会造成索引失效；
在查询条件过程中，应用了特殊数据匹配方法时，也会导致索引失效，一般是辅助索引失效；
-- 应用以上特殊符号信息，也会导致辅助索引失效
<> , not in , like "%_"
三、数据库索引知识扩展¶
3.1 数据库服务索引功能特性¶
- 在新的数据库服务中，支持不可见索引功能
# 在创建索引或修改索引时，可以设置不可见或可见索引（默认）
mysql> alter table test alter index idx invisible;
mysql> alter table test add index idx1(name) invisible;
-- 在做批量数据导入时，辅助索引信息可以设置为不可见，优化器就不会加载识别索引信息
执行计划信息输出：
- 在新的数据库服务中，支持倒序索引功能
在早期数据库中，所有索引列创建索引信息，都是按照从小到大顺序进行排序，在最新数据库中，可以灵活调整索引排序方式；
# 官方解释说明
idx(a,b,c)
-- 创建a b c 索引列 并按照从小到大排序
desc select * from where xx order by a,b desc,c 索引全覆盖
order by a,b desc,c
-- 由于排序中出现了逆向排序，所以只有a列会走索引，查询b和c还是会再进行排序处理，不会利用索引排序
# 最新版数据库索引创建
idx(a,b desc,c)
-- 可以灵活调整索引排序方式，应对不同的查询条件，从而避免排序问题对CPU资源的消耗
3.2 数据库服务自主优化能力¶
3.2.1 自主优化功能一：AHI（索引的索引）¶
AHI全称（中文名称）为自适应的hash索引/散列索引，用于在内存中建立索引，快速锁定内存中的热点数据索引页位置；
正常情况下，所有数据都是存储在磁盘中的，如果想访问读取相应磁盘的数据信息，都是会将磁盘数据调取存放在内存中，即消耗IO；
对于数据库服务而言，想要读取数据信息，也是会从磁盘中读取存储页，在放入内存中被数据库服务进行访问，索引访问也是一样的；
但是当数据页大量的被存放在内存中后，从大量内存中的数据页找到想要的，也是比较困难的事情；
因此，可以对内存中经常被访问数据索引页建立一个hash索引，从而可以帮助数据库服务快速定位内存中想要找的索引数据页；
AHI功能配置信息:
mysql> show variables like 'innodb_adaptive_hash_index';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_adaptive_hash_index | ON |
+--------------------------------------+-------+
1 row in set (0.00 sec)
3.2.2 自主优化功能二：CHANGE BUFFER¶
早期版本称为 insert buffer，只是对插入操作有作用，版本更新后(5.6)，可以对插入 修改 删除操作都有作用效果；
change buffer主要是针对辅助索引的缓冲区，属于内存结构上的应用；
changerbuffer应用原理：假设现在需要插入一行数据信息
① 插入一行数据信息到表中，将会实时立即更新聚簇索引信息，因为利用聚簇索引是用来获取数据页上详细原表数据信息的；
② 插入一行数据信息到表中，不会实时立即更新辅助索引信息，因为利用辅助索引是用来获取索引页上聚簇索引数据信息的；
如果此时实时更新了辅助索引的信息，有可能会导致出现数据页分裂，造成辅助索引树结构变化，形成索引树访问阻塞(锁机制)；
③ 为了避免辅助索引树结构变更，对数据库服务并发访问的影响，可以将插入的数据信息，暂时存储在缓冲区中；
当利用辅助索引检索数据时，可以将检索到数据页范围信息调取到内存中，与缓存区数据进行合并，自然可以检索到插入的数据；
说明：在数据表中插入 修改 删除数据时，聚簇索引树会进行同步实时更新，辅助索引树会进行异步延时更新。
change_buffer功能配置信息:
mysql> show variables like '%change_buffer%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
--all： 默认值。开启buffer inserts、delete-marking operations、purges
--none： 不开启change buffer
3.2.3 自主优化功能三：ICP （索引下推）¶
属于5.6之后引用的数据库服务新特性，称之为索引下推功能，主要是针对联合索引功能起作用；
ICP应用原理：假设创建联合索引进行数据检索
```tiki wiki idx(a,b,c) where a=10 and b like '%x%' and c=z
在没有ICP优化机制情况：
基于联合索引的特性，查找检索数据只会依据a进行检索，可能检索到的数据页是100个数据块，会将数据放入内存中；
数据信息到达内存中后，在根据b和c的条件信息进行定位最终的聚簇索引信息，进行回表查询；
> 说明：基于数据库优化器的特性，遵循联合索引引用原则，SQL层面只能检索到联合索引中的A；
在应用ICP优化机制情况：
基于联合索引的特性，查找检索数据只会依据a进行检索，但是b和c也属于联合索引中的索引部分，在SQL层不能再进行索引情况下；
可以将b和c的检索工作下推交给引擎层完成，可以让引擎再调取数据到内存之前，再根据b和c的条件进行一次过滤；
可以将过滤后的数据信息再放入到内存中，然后结合获取到的聚簇索引信息，进行回表查询；
> 说明：基于数据库优化器的特性，可以将SQL层完成不了的检索工作，下推给引擎层完成，从而减少磁盘IO消耗，以及回表策略
ICP功能配置信息:
```sql
mysql> show variables like '%switch%';
mysql> set global optimizer_switch='index_condition_pushdown=off';
-- 实现测试练习完，需要恢复开启（操作可以省略）
# 测试练习
mysql> select * from t100w where k1='qj' and k2 like '%v%';
mysql> desc select * from t100w where k1='qj' and k2 like '%v%';
-- extra列显示using index condition信息，表示应用了索引下推
# 进行压测
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from t100w where k1='qj' and k2 like '%v%" engine=innodb --number-of-queries=20000 -uroot -p123456 -h192.168.30.101 -verbose
3.2.4 自主优化功能四：MRR¶
MRR，全称（Multi-Range Read Optimization 多范围读取操作）；
简单来说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。
描述说明中涉及到的问题：
① 为什么要把随机读转换为顺序读？ 减少磁盘压力
② 为什么顺序读就能提升读取性能？
③ 如何将随机读去转换为顺序读取？ MRR
MRR功能配置信息:
mysql > set optimizer_switch='mrr=on';
mysql > set global optimizer_switch='mrr_cost_based=off';
Query OK, 0 rows affected (0.06 sec)
