mysql索引

索引的类型

Btree   ： B树索引（Balance Tree），又称为平衡树，根据二叉树演变而来  InnoDB ， MyISAM
Hash    :  哈希索引。 Memory相关引擎。InnoDB中也会维护自己的AHI(自适应)的hash索引。
FullTEXT:  全文索引。一般是在大字段使用。一般用ES数据库存储大字段。
GIS     ： 地理位置索引。一般MongoDB可以替代。

聚簇索引

前导内容
段 ： 一个表就是一个段，会由多个区构成
区 ： 又称之为簇，由连续的64个页构成，默认1M
页 ： 一个数据页是16KB，MySQL IO的最小单元
生成条件
（1）创建InnoDB表时，如果设置了主键 ID列 PK。自动把ID列作为聚簇索引列。
（2）没有主键，会选择第一个唯一键（UK）作为聚簇索引列
（3）如果都没有，生成隐藏列（ROWID），作为聚簇索引列。
作用
（1）在存储数据时，InnoDB 申请连续的磁盘空间，进行存储。
（2）数据在存储时，按照聚簇索引列值的顺序在磁盘有序存储。 （物理上的数据页就是按照逻辑上索引列值顺序存储的）
聚簇索引的B树构建
（1） 数据行在存储时，按照聚簇索引顺序逻辑顺序和物理顺序有序存储
（2） 叶子节点，就是数据行所在的数据页，数据即索引。
（3） 非叶子节点构建，存储了叶子结点ID范围+指针
（4） 根节点，存储了非叶子节点的ID范围+指针

B+tree ：在叶子结点加入双向指针
B*tree： 在非叶子节点加入双向指针（默认）
用于范围查找，查询io到达叶子节点时通过指针查询数据页附近数据页的数据
目的： 1. 减少IO次数 2. 减少IO量级 3.减少随机IO

辅助索引

普通索引，主要的作用：优化非聚簇索引列的条件查询。

辅助索引构建过程原理
（1）“提取” 辅助索引列+ID列值
（2）按照辅助索引列值进行从小到大排序
（3）将有序的值（转换为ascii码）均匀存储到数据页中，生成叶子节点
（4）将叶子节点中的辅助索引列值的范围+指针，生成非叶子节点
（5）提取下层非叶子节点的范围+指针，生成根节点
按照辅助索引列作为查询条件时
（1）通过扫描需要的索引页，获取到查询条件对应的ID值。
（2）拿着ID值，再回到聚簇索引进行扫描，最终获取数据行。又被称之为：回表查询。
回表来的问题
从辅助索引范围扫描得出ID值之后，回到聚簇索引扫描的过程。IO次数、IO量会增多、随机IO会增多。
减少回表
（1）查询条件尽量精准。
（2）MRR技术，将辅助索引扫描到的ID,先排序再一次性回表。
影响索引树的高度因素
聚簇索引: 一般建议2-3层为佳。三层聚簇索引可以存储2000w+的数据行。
辅助索引：3-4 层为佳

1. 数据行越多
   分区表（逻辑）、历史数据归档（pt-archive）、分库分表（物理层次）
2. 数据类型影响
   varchar char
   enum
3. 索引列值长度
   前缀索引。（字符串类型可以只取前几个字符作为索引列值）

索引的操作管理

查看表的索引

desc city;
+-----+
| Key |
+-----+
explain city;

创建索引
原则： 经常作为查询条件的列作为索引列。
创建单列索引：
mysql> alter table city add index idx_name(name);
创建联合索引：
mysql> alter table city add index i_n_d_p(name,district,population);
创建前缀索引：
mysql> alter table city add index i_nn(name(5));
删除索引
mysql> alter table city drop index idx_name;
mysql> alter table city drop index i_n_d_p;
mysql> alter table city drop index i_nn;
压力测试

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose #concurrency用户连接数 number-of-queries请求次数

执行查询语句分析

mysql> desc select * from t100w where k2='780P';

table         ：针对的表
type          ：查询类型，反馈：是否走索引，索引级别？
possible_keys ：可能会用到的索引
key           ：真正走的索引
key_len       ：索引的覆盖长度，判断联合索引使用情况。
rows          ：需要扫描的数据行数（预估）
Extra         ：额外信息

8.4 type输出详解
8.4.1 ALL 不走任何索引，全表扫描
例子： 
查询的列没有索引：
mysql> desc select * from t100w where k1='ab';
不等值的条件查询
mysql> desc select * from t100w where k2 !='abcc';
mysql> desc select * from t100w where k2 not in ('abcc','abdd');
like '%aa%'前面带%的模糊查询
mysql> desc select * from t100w where k2 like '%aa%';

8.4.2 index 全索引扫描
mysql> desc select k2 from t100w;

8.4.3 range 索引范围扫描 （> < >= <= ,like , in ,or）
mysql> desc select * from city where id<10;
mysql> desc select * from city where countrycode like 'Ch%';
mysql> desc select * from city where countrycode in ('CHN','USA');

8.4.4 ref 辅助索引等值查询
mysql> desc select * from city where countrycode='CHN';

8.4.5 eq_ref 多表连接中非驱动的关联条件是主键或唯一键
mysql> desc select * from city left join country on city.countrycode=country.code ;

8.4.6 const(system) 主键或唯一键等值查询
mysql> desc select * from city where id=10;

8.4.7 NULL 查不到数据
mysql> desc select * from city where id=1000000000;

key_len  索引覆盖长度
作用
如果单列索引比较长，需要评估是否需要前缀索引。
如果是联合索引，帮助判断应用了几部分。
key_len是索引列的定义的最大预留长度。
数字类型：  
数据类型             是否非空           key_len
int                  not null            4
tinyint              not null            1
int                  		             4+1
tinyint                                  1+1
字符集  
  utf8      ：最多3个字节字符
  utf8mb4   ：最多4个字节字符
字符串类型： char varchar  （ut8mb4）
数据类型                  是否非空           key_len
char(10)                  not null           10*4
varchar(10)               not null           10*4+2 
char(10)                             		 10*4+1
varchar(10)                                  10*4+2+1 
varchar预留两个字节
联合索引是遵循最左原则。
(1) 在查询中，至少要有最左列索引才生效
(2) 最左列是第一过滤条件，精确查找的排左，范围查找的排右
extra 说明
Using filesort 此次查询中发生了额外的排序操作
需要把排序列也作为联合索引列

索引的优化

select count(distinct countrycode) from world.city;
查看重复值，distinct去重

• 为经常需要where 、ORDER BY、GROUP BY、join on等操作的字段创建索引，join连接两个表时尽量优先数据行较少的表
• 限制索引的数目
  (1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
  (2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
  (3) 优化器的负担会很重,有可能会影响到优化器的选择.
  percona-toolkit中有个工具,专门分析索引是否有用
• 大表加索引,要在业务不繁忙期间操作
• 尽量少在经常更新值的列上建索引，更新频繁的表可使用optimize table ad_visit_history;进行碎片整理优化
• 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引

使用索引的规范操作

• 精确范围
• 查询不要超过25%以上，否则不走索引
• 内容变化比较频繁，可能会出现索引失效，尽量删除重建
  mysql.innodb_index_stats 存放索引的状态
  mysql.innodb_table_stats 存放表的状态
• 不在查询条件上使用函数或运算
• 字符串类型查询时不加引号不走索引
• 尽量不使用not in和!=，<>有时候走索引，有时不走，改成union

优化器

• MySQL索引的自优化-AHI（自适应HASH索引）帮助InnoDB快速读取索引页。加快索引读取的效果。
• MySQL索引的自优化-Change buffer
  比如insert，update，delete 数据。
  对于聚簇索引会立即更新。
  对于辅助索引，不是实时更新的。
  在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
  Change buffer 功能是临时缓冲辅助索引需要的数据更新。
  当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。
• 8.0版本对于倒叙的优化(a,b desc )，联合索引会根据a排序，再根据b排序
• index_condition_pushdown索引下推，左列的条件不是精确查询，会查询右列精确查询条件，排除无用的数据，由innodb引擎完成
• 开启减少回表的功能 set global optimizer_switch=‘mrr=on,mrr_cost_based=off’;
  globa全局生效，第二个参数是自适应优化器的代价
• BNLJ
  在 A和B关联条件匹配时，不再一次一次进行循环。
  而是采用一次性将驱动表的关联值拿到缓冲区和非驱动表匹配，一次性返回结果
  主要优化了， CPU消耗，减少了IO次数
• BKA
  主要作用，使用来优化非驱动表的关联列的辅助索引。
  BNL+ MRR的功能。
  开启方式：
  mysql> set global optimizer_switch=‘mrr=on,mrr_cost_based=off’;
  mysql> set global optimizer_switch=‘batched_key_access=on’;
  重新登陆生效。

select @@optimizer_switch;查询优化器
select @@optimizer_switch=‘derived_merge’;查询具体优化器的值
配置文件修改，例如derived_merge=on
set修改，重启失效
查询时应用优化器SELECT * BKA(t1) NO_BKA(t2) FROM t1 INNER JOIN t2 WHERE …;