ITPub博客

mysql学习11:第六章:索引

翻译 MySQL 作者:studywell 时间:2018-11-07 17:03:54 0 删除 编辑

 

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

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29519108/viewspace-2219114/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-04

  • 博文量
    278
  • 访问量
    395432