MySQL进阶:2.索引

【专栏目录】
MySQL进阶:1.MySQL基础环境搭建
MySQL进阶:2.索引
MySQL进阶:3.视图
MySQL进阶:4.为什么不推荐使用存储过程和触发器
MySQL进阶:5.存储引擎
MySQL进阶:6.定位SQL性能问题
MySQL进阶:7.SQL优化

本文导读

本文主要对索引进行详解

1.索引概述

索引的本质:一种有序的数据结构,一般常用的是多路平衡搜索树,即BTREE

索引的作用:帮助MySQL高效获取数据

条件 查询数据方式
没有索引 顺序遍历数据表查询
使用索引 根据数据结构(索引)的查询顺序查询

【☆☆☆必看】

什么是索引

2.索引的优缺点

优点 缺点
类似书本的目录索引,提高数据检索效率,降低数据库的IO成本 索引本质是数据结构,也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引需要占用额外的磁盘空间
索引会对数据进行排序,降低数据排序的成本,降低CPU的消耗 索引会降低表的更新速度,如增删改,因为更新表时需要更新索引,调整索引的结构(这点结合更新二叉树理解)

3.索引结构

索引 说明
BTREE索引 最常见的索引类型,大部分存储引擎都支持BTREE索引,对应数据结构是B+树
HASH索引 只有Memory引擎支持,使用场景简单
R-tree索引 空间索引,只支持MyISAM引擎,主要用于地理空间数据类型,通常使用较少
Full-text索引 全文索引,支持MyISAM引擎,5.6版本之后支持InnoDB引擎

一般没有特殊说明,默认使用的都是B+树(多路平衡搜索树,不一定是二叉)结构的索引;

其中聚合索引、复合索引、前缀索引、唯一索引默认使用的都是B+树,统称为索引。

3.1B树结构

可参考我的另一篇博客图解B树构建过程

B树和二叉搜索树相比,查询数据效率更高,因为对于相同数量来说,BTREE的层级更少,因此搜索速度更快。

3.2B+树结构

可参考我的另一篇博客图解B+树构建过程

B+树是B树的另一种形态,区别如下:

  1. n叉B树每个节点最多包含n-1个key,n叉B+树每个节点最多包含n个key;
  2. B+树的叶子节点包含所有的key,并且按照key的大小有序排列;
  3. B+树的非叶子节点都可以看作是key的索引部分

4.索引分类

分类 解释
单值索引 一个索引只包含单个列,一个表可以有多个单值索引
唯一索引 单值索引的特殊情况,索引列的值必须唯一,但是允许有空值
复合索引 一个索引包含多个列

建议使用复合索引,原因是复合索引可以给MySQL提供的选择比较多,能够根据实际情况选择最优的索引。比如:对某个表的a、b、c列创建复合索引,那就相当于创建了三个索引:[a]、[a,b]、[a,b,c]。

5.索引语法

下面的语句均是在mysql环境中执行

5.1创建索引

mysql> create index [index_name] on [table_name]([col_name...]);

// 创建单值索引
// 例如,给student表的stu_number列创建索引,索引名为index_stu_number:
mysql> create index index_stu_number on student(stu_number);

// 创建复合索引
// 例如,给student表的stu_number、stu_age列创建索引,索引名为index_stu_number:
mysql> create index index_stu_number on student(stu_number,stu_age);

5.2查看索引

mysql> show index form [table_name];

5.3删除索引

mysql> drop index [index_name] on [table_name];

6.索引设计原则

索引的设计可以遵循一些已有的原则,创建索引的时候需要尽量考虑符合这些原则,便于提升索引的使用效率,更高效地使用索引

  1. 查询频次较高,且数据量较大的数据表,适合建立索引;
  2. 索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合;
  3. 使用唯一索引:区分度越高,效率越高;
  4. 索引不是越多越好,索引越多维护成本越高,对于增删改这些操作,都会影响索引的构建;
  5. 建议缩小索引的大小,即使用短索引。因为索引也是占据磁盘块空间的,如果索引相对较小,那么一块内就可以存储多个索引,从而减少IO次数,进而提升效率;
  6. 利用最左索引(复合索引中的第一个列,后面会详细讲)。

7.索引的最左前缀法则

假设对某数据表中的a、b、c三列创建复合索引,那么实际就创建了三个索引:[a]、[a,b]、[a,b,c],那么查询列项只要属于这三种索引,就满足最左前缀法则,除此之外都属于违背最左前缀法则,会导致索引失效。即以下几种查询列项的组合就不会导致索引失效:

  • [a]
  • [a,b]
  • [b,a]
  • [a.b.c]
  • [a,c,b]
  • [b,a,c]
  • [b,c,a]
  • [c,a,b]
  • [c,b,a]

示例:

# 对UserEO表中的name和passwd创建一个复合索引
mysql> create index index_name_passwd_token on UserEO (name,passwd,token);

# 满足最左前缀法则的情况
mysql> select * from UserEO where name ='wy';
mysql> select * from UserEO where name ='wy' and passwd ='123123';
mysql> select * from UserEO where passwd ='123123' and name ='wy';
mysql> select * from UserEO where name ='wy' and passwd ='123123' and token ='';
mysql> select * from UserEO where name ='wy' and token ='' and passwd ='123123';
mysql> select * from UserEO where passwd ='123123' and name ='wy' and token ='';
mysql> select * from UserEO where passwd ='123123' and token ='' and name ='wy';
mysql> select * from UserEO where token ='' and name ='wy' and passwd ='123123';
mysql> select * from UserEO where token ='' and passwd ='123123' and name ='wy';

8.索引失效情况

  1. like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效;
  2. or语句前后没有同时使用索引;
  3. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描;
  4. 违背最左前缀法则;
  5. 在索引列上使用 IS NULL 或 IS NOT NULL有时会导致索引失效,这需要结合第8点分析,当索引列里的值为NULL的情况占多数时,IS NULL就不会走索引,因为这种情况全表扫描比走索引更快;反过来,索引列不为NULL的情况占多数时,IS NOT NULL不会走索引;
  6. 在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 优化方法: key<>0 改为 key>0 or key<0;
  7. 对索引字段进行计算操作、字段上使用函数。(索引为 emp(ename,empno,sal));
  8. 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效;
  9. 在索引字段上使用in会走索引,not in不走索引。
展开阅读全文

Windows版YOLOv4目标检测实战:训练自己的数据集

04-26
©️2020 CSDN 皮肤主题: 像素格子 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值