11. 索引应用规范总结
11.1 建立索引的原则（DBA运维规范）
(1) 必须要有主键,无关列。
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期，建议用pt-osc
(7) 联合索引最左原则
11.2 不走索引的情况（开发规范）
11.2.1 没有查询条件，或者查询条件没有建立索引
select * from t1 ;
select * from t1 where id=1001 or 1=1;
11.2.2 查询结果集是原表中的大部分数据，应该是15-25%以上。
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。
可以通过精确查找范围，达到优化的效果。
1000000
>500000 and
11.2.3 索引本身失效，统计信息不真实（过旧）
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建
现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? --->索引失效,统计数据不真实
innodb_index_stats
innodb_table_stats
mysql> ANALYZE TABLE world.city;
11.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询
11.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
11.2.6 <> ，not in 不走索引（辅助索引）
11.2.7 like "%_" 百分号在最前面不走
12. 彩蛋(扩展)：优化器针对索引的算法
12.1 自优化能力：
12.1.1 MySQL索引的自优化-AHI（自适应HASH索引）
a. 限制
MySQL的InnoDB引擎，能够手工创建只有Btree。
AHI 只有InnoDB表会有，MySQL自动维护的。
AHI作用：
自动评估"热"的内存索引page,生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的效果。
相当与索引的索引。
参考：
https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
12.1.2 MySQL索引的自优化-Change buffer
限制：
比如insert，update，delete 操作时会使用change buffer。
对于聚簇索引会直接更新叶子节点。
对于辅助索引，不是实时更新的。
insert into t1 (id,name,age) values(33,'d',18)
在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。
参考：
https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html
12.1.3 8.0 版本索引的新特性
a. 不可见索引。invisable/visable index
针对优化器不可见。但是索引还在磁盘存在，还会自动维护。
对于索引维护时，不确定索引是否还有用。这时可以临时设定为invisable。
b. 倒序索引。
select * from t1 where c = order by a ASC , b desc
idx(c,a, b desc)
12.2 可选的优化器算法-索引
12.2.1 优化器算法查询
select @@optimizer_switch;
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on
12.2.2 如何修改?
1. my.cnf
optimizer_switch='batched_key_access=on'
2. set global optimizer_switch='batched_key_access=on';
3. hints 了解一下
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
12.2.3 index_condition_pushdown （ICP）
介绍： 索引下推 ，5.6+ 加入的特性
idx(a,b,c)
where a = and b 不等值 and c =
作用： SQL层做完过滤后，只能用a,b的部分辅助索引，将c列条件的过滤下推到engine层，进行再次过滤。排除无用的数据页。
最终去磁盘上拿数据页。
大大减少无用IO的访问。
测试1: ICP开启时
idx(k1,k2)
mysql> SET global optimizer_switch='index_condition_pushdown=ON'
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 = 'Za' and k2 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.114 seconds
Minimum number of seconds to run all queries: 1.114 seconds
Maximum number of seconds to run all queries: 1.114 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 6.945 seconds
Minimum number of seconds to run all queries: 6.945 seconds
Maximum number of seconds to run all queries: 6.945 seconds
Number of clients running queries: 100
Average number of queries per client: 200
测试2:ICP关闭时：
idx(k1,k2)
mysql> SET global optimizer_switch='index_condition_pushdown=OFF'
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1='Za' and k2 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 3.125 seconds
Minimum number of seconds to run all queries: 3.125 seconds
Maximum number of seconds to run all queries: 3.125 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 31.102 seconds
Minimum number of seconds to run all queries: 31.102 seconds
Maximum number of seconds to run all queries: 31.102 seconds
Number of clients running queries: 100
Average number of queries per client: 200
具体参考 ：
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
https://mariadb.com/kb/en/index-condition-pushdown/
练习：
验证： k1> 'ZA' and k2='rsEF'
分别验证 三种情况：
1. ICP
2. No-ICP
3. 优化索引
