1.
索引
1.1.
二叉索引
B+tree是由二叉树》平衡二叉树》B-tree演化而来。
二叉树每个节点最多两个子节点，左子树键值永远小于右子树，并小于根键值。
1.2.
平衡二叉树结构
平衡二叉树在二叉树结构基础上提高，必须满足左右两个子树的高度差的绝对值不超过1，且左子树和右子树都是一颗平衡二叉树，，随时要保证插入后的整棵二叉树是平衡的，通郭左旋或右旋使不平衡的树变平衡。
1.3.
B-tree结构
B-tree又称Btree，每个节点最多4个子节点，除了根节点和叶子节点，其他节点最少2个子节点。所有叶子节点在同一层，叶子节点不包括任何关键字信息。
1.4.
B+tree
B+tree使Btree的变体，是一种多路搜索树，所有关键字和数据都保存在叶子节点中，并且包含关键字记录的指针。
总结：B+tree索引是双向链表结构，检索比B-tree快，访问关键字的顺序是连续性的，不用再访问上一个节点，且叶子节点包含所有的数据信息。
1.4.1.
聚集索引和普通索引
B+tree分为两大类，一类叫聚集索引，一类叫非聚集索引（普通索引）。
InnoDB存储引擎是索引组织表，聚集索引是一种索引组织表形式，索引键值的逻辑顺序决定了表数据行的物理存储顺序。
聚集索引叶子节点存放表中所有行数据记录的信息，即数据即索引、索引即数据。创建表时建主键（聚集索引），如不建主键则InnoDB会选择第一个不包含由Null值得唯一索引作为主键，如果唯一索引没有，则默认为该表生成一个6字节得rowid为主键。
普通索引在叶子节点不包含所有行得数据记录，只在叶子节点存有自己本身键值和主键得值。检索数据，通过普通索引叶子节点上主键来获取想要查找的行数据记录。
普通索创建语法：
alter table tab_name add index index_name(col1);
或：
create index inde_name on tab_name(col1);
查看表中有哪些索引；
show index from tab_name;
索引创建实验
l
创建测试库
mysql> create database test;
l
创建测试表
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`address` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
l
查看表结构
[test]>desc t;
+———+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| address | varchar(20) | NO | | NULL | |
+———+————-+——+—–+———+—————-+
l
创建存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_auto_insertdata`$$
CREATE PROCEDURE `proc_auto_insertdata`()
BEGIN
DECLARE init_data INTEGER DEFAULT 1;
WHILE init_data <= 60000 DO
INSERT INTO test.t VALUES(CONCAT('name', init_data), init_data + 10);
SET init_data = init_data + 1;
END WHILE;
END$$
DELIMITER ;
l
调用存储过程插入数据
CALL proc_auto_insertdata();
数据插入完成，看数据文件10M
l
查看执行计划
test> explain select * from t where name='name11';
l
创建索引
create index idx_tname on t(name);
l
再次查看执行计划
优化方法
l
执行计划查看方法：
1.
看查询类型type，如出现all，代表全表扫描；
2.
看key列，看是否使用l 索引。null表示没有使用索引；
3.
看rows列，SQL执行过程中被扫描的行数；
4.
看extra列，观察是否有Using filesort或Using temporary，这些影响性能。
5.
看filtered列，（5.7增加，5.6用explain extended增加此列），代表返回结果的行占需要读取行的百分比。
l
SQL优化思路：
1.
查看表的数据类型是否设计的合理，是否遵守选区数据类型越简单越小的原则。
2.
表中碎片是否整理。
3.
表的统计信息是否收集。
4.
查看执行计划如没用到索引，需创建。
5.
创建索引前，查看索引的选择性，判断字段是否合适创建索引。选择性指不重复的索引值（基数，cardinality）和记录总数的比值，比值越高越好。
6.
创建索引后，再看执行计划，比对前后。
l
合理创建索引：
1.
经常被查询的列。
2.
经常用于表连接的列。
3.
经常排序分组的类。
1.4.2.
ICP、MRR和BKA
ICP(Index Condition Pushdown)
是mysql使用索引从表重检索行数据的一种优化方式。5.6开始支持。之前存储引擎取所有数据给server使用索引过滤处理。使用ICP之后，可以使用索引的话，存储引擎过滤完数据再给server层。ICP能减少引擎层访问基表的次数和server层访问存储引擎的次数。
通过optimizer_switch参数中的index_condition_pushdow来控制，默认开启。
[mysql]>show variables like '%pushdown%';
关闭：
set optimizer_switch=”index_condition_pushdown=on|off”;
使用ICP优化时，执行计划extra列会显示Using index condition。
5.7中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 |
MBR(Multi-Range Read Optimization)
,5.6后增加。通过optimizer_switch参数中两个选项控制，参数默认开启。
mrr_cost_basd：通过基于成本的算法来确定开启mrr特性，on自动，off强制开启。
MBR作用：把普通索引上的叶子节点上找到的主键值的集合存储到read_rnd_buffer中，然后再该buffer中对主键值排序，然后用排序号的主键值集合去访问表中的数据，将随机IO编程顺序IO，降低查询过程IO开销。
使用MBR优化时，执行计划extra列会显示Using MBR。
BKA（Batched Key Access）,提高表join性能的算法，作用是读取被join表的记录时候使用顺序IO。
BKA原理：多表join语句，使用索引访问第二个join表时，使用一个join buffer来收集第一个操作对象生成的相关列值，BKA构建好key后，批量传给引擎层做索引查找，key通过MBR接口提交给引擎。
通过optimizer_switch参数的batched_key_access选项控制，默认关闭。
要开启该参数，必须强制使用MBR才行。
SET global optimizer_switch=’mrr=on,mrr_cost_based=off’;
SET global optimizer_switch=’batched_key_access=on’;
当BKA使用时，执行计划extra列会显示Using join buffer(Batched Key Access)。
1.4.3.
主键索引和唯一索引
主键索引就是聚集索引，每表只能有一个。必须满足三个条件：
l
主键值必须唯一。
l
不能包含null值。
l
一定要保证该值是自增属性。可以保证写入数据的顺序也是自增的，提高存取效率。
创建主键语法：
alter table tab_name add primary key(col);
唯一索引，不允许有重复值，但允许空值，可以有多个唯一索引。
语法：
alter table tab_name add unique(col);
1.4.4.
覆盖索引
数据在索引中，查到索引不必再回表查询数据。执行计划extra列中会出现Using index。
如使用覆盖索引，一定要让select列出所需要的列，坚决不能直接写出select *
1.4.5.
前缀索引
对于BLOB、TEXT或很长的varchar类型的列，为他们前几个字符建立的索引，就是前缀索引。不能再ORDER BY 或GROUP BY中使用前缀索引，也不能用作覆盖索引。
alter table tab_name add key(col_name(prefix_length));
注意：最关键的参数prefix_length，这个值需要根据实际表的内容来得到合适的索引选择性。
1.4.6.
联合索引
联合索引又叫复合索引，是表中两个或两个以上的列创建的索引。
create index idx_c1_c2 on t(c1,c2);
选择性高的列放前面。
1.5.
哈希索引
哈希索引采用哈希算法，把键值换算成新的哈希值。哈希索引只能进行等值查询，不能进行排序、模糊查找、范围查询等。检索时不需要像B+tree那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立即定位到相应的位置。
1.6.
索引总结
索引优点
l
提高数据检索效率
l
提高据合函数效率
l
提高排序效率
l
使用覆盖索引可以避免回表
索引创建四个不要
l
选择性低的字段不要创建索引
l
很少查询的列不要创建索引
l
大数据类型字段不要创建索引
l
尽量避免不要使用NULL，应指定列为NOT NULL。
使用不到索引的情况
l
通过索引扫描的行记录数超过全表30%，优化器不会走索引，而走全表扫描。
l
联合索引中，第一个查询条件不是最左侧列。
l
联合索引中，第一个索引列使用范围查询，只能使用到部分索引，有ICP出现。
l
联合索引中，第一个查询条件不是最左前缀列。
l
模糊查询条件列最左以通配符%开始。
l
两个单列索引，一个用于检索，一个用户排序。只能使用到一个索引，因为查询语句最多只能使用一个索引，考虑建立联合索引。
l
查询字段上有索引，但使用了函数运算。
