mysql学习11：第六章:索引

1.   索引

1.1.   二叉索引

B+tree是由二叉树》平衡二叉树》B-tree演化而来。

我们提供的服务有：做网站、网站设计、微信公众号开发、网站优化、网站认证、拜泉ssl等。为成百上千家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的拜泉网站制作公司

二叉树每个节点最多两个子节点，左子树键值永远小于右子树，并小于根键值。

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   查询字段上有索引，但使用了函数运算。