MySQL 索引

为什么要使用索引?

  • 索引能极大的减少存储引擎需要扫描的数据量

  • 索引可以把随机IO变为顺序IO

  • 索引可以帮助我们在进行分组、排序时,避免使用临时表

二叉查找树 Binary Search Tree

  • 左子树小于根节点 根节点小于右子树

选择树形结构是想遍历的时候 更快速更高效。但是这种数据结构可能会存在以下问题:

当数据均比根节点大时,会构造成一个只有右子树的二叉查找树,其实就相当于一个链表了。

此时查询遍历效率并不高

平衡二叉查找树 AVL tree

特点:节点的高度差不超过1 否则会进行左旋或右旋重新构造子树 维持节点高度差不超过1的规则

这棵树 在遍历上比二叉查找树更优,但是随着数据量的增大,树的高度会很高,层数很大,如果节点上保存有数据的话,当要查找一个深度很深的数据时,遍历的层数过多,会导致IO操作耗时很大

节点存储的数据量太少(相比多路平衡查找路 B tree)

树的种类中有对二叉查找树、平衡二叉查找树作介绍,如需了解请点击查看

多路平衡查找树 B tree

特点:节点上存储数据

在这里插入图片描述

图中为平衡3路查找树

多路体现在磁盘块上的节点个数 ,如上面的B-树可以叫做2-3路平衡查找树

查找规则:

  • 如果节点小于17 则走p1子树,如果=17 则命中

  • 如果17<节点<35 则走p2

  • 如果节点=35 则命中

  • 如果节点>35 则走p3

磁盘块存储的数据量比AVL Tree节点存储的要多,因为AVL tree顶多有两个子树,而B tree可以有路数+1个子树;

树的深度 也降低了 查找某节点数据的效率也上升了。

对比AVL和B Tree,如果查找数据为6的数据节点,AVL需要遍历3次,而B tree只需要2次。当数据量更大时 区别会更明显

加强版多路平衡查找树 B+ Tree

在这里插入图片描述

特点:只有叶子节点保存数据 且数据是有序排列的。从这一点 单个磁盘块保存的路数就会比B Tree多很多。

节点查找的规则 依据左闭合。

  • 当1<=x<28 ->p1

  • 当28<=x<66 ->p2

  • 当66<=x ->p3


B树与B+树的区别:
  • B+节点关键字搜索采用闭合区间

  • B+非叶节点不保存数据相关信息,只保存关键字和子节点的引用

  • B+关键字对应的数据保存在叶子节点中

  • B+叶子节点是顺序排列的,并且相邻节点具有顺序引用的关系

选用B+树作为索引的数据结构的原因:
  • B+树是B-树的变种,它拥有B-树的优势

  • B+树扫库、表能力更强

  • B+树的磁盘读写能力更强

  • B+树的排序能力更强

  • B+树的查询效率更加稳定


MyISAM下数据文件的形式:

  • frm文件 表定义文件

  • myd 表中数据文件

  • myi 表中索引

InnoDB下数据文件的形式:

  • frm 表定义文件

  • idb 表中数据和索引


列的离散性:

count(distinct col):count(col)

这个值越大 离散性越好 可选择性就越好


联合索引列选择原则

1,经常用的列优先 【最左匹配原则】

2,选择性(离散度)高的列优先【离散度高原则】

3,宽度小的列优先【最少空间原则】

如果查询列可通过索引节点中的关键字直接返回,则该索引称之为 覆盖索引

因为如果命中了节点 就不用再遍历到具体的叶子节点拿数据了,所以有时尽量少写select *

覆盖索引可减少数据库IO,将随机IO变为顺序IO,可提高查询性能

explain select age from users;//idx_union_uname&userLevel&age age列是索引中的一个

在这里插入图片描述

可以看出这时查询是走索引了。

同样的 explain select age,phoneNum from users;//这个时候不走索引 因为phoneNum不是索引列

在这里插入图片描述

索引建立的规则

  • 索引列的数据长度能少则少。

  • 索引一定不是越多越好,越全越好,一定是建合适的。

  • 匹配列前缀可用到索引 like 9999%。like %9999%、like %9999用不到索引;

  • Where 条件中 not in 和 <>操作无法使用索引; 无法确定走多路里的哪一路

  • 匹配范围值,order by 也可用到索引;

  • 多用指定列查询,只返回自己想到的数据列,少用select *;

  • 联合索引中如果不是按照索引最左列开始查找,无法使用索引;

  • 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引;

  • 联合索引中如果查询中有某个列的范围查询,则其右边的所有列都无法使用索引;


索引优化策略

  1. 索引列上不能使用表达式或函数

    select … from product

    where to_days(out_date(索引列))-to_days(current_date)<=30;

    上面的out_date索引会失效。

    改进:

    select … from product

    where out_date<=date_add(current_date,interval 30 day);

  2. 前缀索引和索引列的选择性

    InnoDB 索引最大是768字节,MyISAM是1000字节;超过后 无法建立前缀索引

    create index index_name on table(col_name(n))

    前缀索引的长度的选择要谨慎,要保证前缀索引的列离散性尽量大

    索引的选择性是不重复的索引值和表的记录数的比值

  3. 联合索引

    如何选择索引列的顺序?

    • 经常会被使用到的列优先
    • 可选择性高的列优先
    • 宽度小的列优先(一次io 数据页上的数据会更多)
  4. 覆盖索引

    优点

    • 可以优化缓存,减少磁盘IO操作
    • 可以减少随机IO,变随机IO操作变为顺序IO操作
    • 可以避免对InnoDB主键索引的二次查询
    • 可以避免MyISAM表进行系统调用

    无法使用覆盖索引的情况

    • 存储引擎不支持覆盖索引
    • 查询中使用了太多的列
    • 使用了双%号的like查询
  5. 使用索引扫描来优化排序

    • 通过排序操作

    • 按照索引顺序扫描数据

      • 索引的列顺序与Order By子句的顺序完全一致
      • 索引中所有列的方向(升序、降序)和Order By子句完全一致
      • Order By中的字段完全在关联表的第一张表中
      explain select * from rental where rental_date='2015-05-09' order by id;
      -- 通过执行计划 Extra:Using where;发现使用了索引排序;
      -- 如果没有使用的到索引排序 Extra:Using where;Using filesort
      explain select * from rental where rental_date>'2015-05-09' order by id;
      -- rental_date是联合索引最左列,如果使用范围查找 那么排序不走索引  Extra:Using where;Using filesort
      
  6. 对长字符串模拟Hash索引优化查询

film表title varchar(255),如果对它使用索引,除了前缀索引,还可以使用这种方式

-- 新增一列

alter table file add title_md5 varchar(32);

update film set title_mdf=md5(title);

create index idx_md5 on file(title_md5);
explain select * from file where title_md5=md5('abc') and title='abc';
  • 只能处理键值的全值匹配查找
  • 所使用的Hash函数决定着索引键的大小

7.利用索引优化锁

  • 索引可以减少锁定的行数
  • 索引可以加快处理速度,同时也加快了锁的释放
begin;
-- last_name 不是索引列
select * from actor where last_name='willis' for update;-- 加上排他锁
-- 可以查询出数据,事务未提交

-- 另一事务
begin;
-- last_name 不是索引列
select * from actor where last_name='wojiushiwo' for update;-- 加上排他锁
-- 会被阻塞住,查不出数据。原因是上面的事务的查询操作为表中所有行都加上了排他锁

解决方式:为last_name加上索引,这样排他锁的粒度就小很多,事务2也不会阻塞住

8.索引的维护和优化

  • 删除重复和冗余的索引

    如在一列上添加了如下索引:

    primary key(id),unique key(id),index(id)

    冗余索引,如

    Index(a),Index(a,b)

    工具:pt-duplicate-key-check h=127.0.0.1可以检查重复和冗余索引

9.查找未被使用过的索引

10.更新索引统计信息及减少索引碎片

analyze table table_name

Optimize table table_name(使用不当会导致锁表)

发布了101 篇原创文章 · 获赞 22 · 访问量 8万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览