文档章节

MySQL索引原理及如何高效利用索引

骑着机车敲代码
 骑着机车敲代码
发布于 2017/01/06 17:29
字数 1577
阅读 157
收藏 1

##1、索引基础 要理解MySQL索引是如何工作的,最简单的方式就是去看看一本书的“索引”部分:如果想在一本书中找个某个特定的主题,一般会先看书的“索引”,然后通过索引找到对应的页码。

在MySQL中,存储引擎也是使用类似的方法使用索引,在查询时,先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。

##2、索引的类型

索引有很多种,可以为不同的场景提供更好的性能。在MySQL中,索引是在存储引擎层而不是在服务层实现的。不同的存储引擎有不同的索引实现方式。

###2.1 B-Tree索引 当人们谈到索引时,如果没有特别说明的话,一般指的都是B-Tree索引(** 实际上很多存储引擎使用的是B+Tree**),它使用B-Tree数据结构来实现存储数据。大多数MySQL引擎都支持这种索引。

存储引擎以不同的方式使用B-Tree索引,性能也各有不同,各有优劣。例如:MyISAM使用前缀压缩技术使得索引更小,但InnDB则按照原数据格式进行压缩。(传送门:MyISAM与InnoDB区别

B+Tree结构如下

B+Tree结构

B+Tree的详细结构以及查找过程详见网上,这里不做介绍。

** 可以使用B+Tree索引的查询类型:**

  • 全指匹配:

  • 匹配最左前缀:

  • 匹配列前缀:

  • 匹配范围值:

  • 精确匹配某一列并范围匹配另外一列:

  • 只访问索引的查询:

** 关于B+Tree索引的限制:**

  • 如果不是按照索引的最左列开始查找,则无法使用索引。

  • 不能跳过索引中的列。

  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。

###2.2 哈希索引 暂时没了解过,略,以后用到了再补充。

###2.3 空间数据索引 暂时没了解过,略,以后用到了再补充。

###2.4 全文索引 暂时没了解过,略,以后用到了再补充。

##3、高性能索引策略

###3.1 独立的列

“独立的列”指的是索引列不能是表达式的一部分,也不能是函数的参数。

例如:下面这个查询无法使用id的索引:

mysql> select * from user where id +1=5;

凭肉眼就能判断出来的,但是MySQL无法自动解析这个方程式。所以我们应该简化WHERE条件习惯,始终讲索引列单独放在比较符号的一侧。

###3.2 前缀索引和索引的选择性

对于BLOB、TEXT或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。

下面演示一下如何创建前缀索引:

mysql> alert table sakila.city_demo add key (city(7));

###3.3 多列索引

很多人对多列索引有误解,一个常见的错误就是,为每个列创建独立的索引或者按照错误的顺序创建多列索引。

如,表film_actor在字段film_id和actor_id上各有一个单列索引。但对于下面这个查询WHERE条件,这两个单列索引都不是很好的选择;

mysql> select film_id,actor_id from sakila.film_actor where actor_id = 1 OR film_id =1;

在老的MySQL中,对这个查询会使用全表扫描。除非改写成如下的SQL:

mysql> select film_id,actor_id from askila.film_actor where actor_id = 1 
union all
 select film_id,actor_id from askila.film_actor where film_id = 1 
 and actor_id <> 1;

##4、MySQL中能够使用索引的场景

  • 全值匹配

    对索引列指定具体值进行查询。

  • 匹配值的范围查询

    对索引的值进行范围查找。

  • 匹配最左前缀

    仅仅使用索引中最左边列进行查询。比如在col1+col2+col3字段上的联合索引能够被包含col1、(col1+col2)、(col1+col2+col3)的等值查询利用到,不能够被col2、(col2+col3)的等值查询利用。

  • 仅仅对索引进行查询

    查询的列都在索引的字段中。

  • 匹配列前缀

    仅仅使用索引的第一列,并且只包含索引第一列的开头一部分进行查询。例如:查询出title是以“最美丽的”开头的所有数据,也就是SQL中的“%最美丽的”。

  • 能够实现索引匹配部分精确而其他部分进行范围匹配

    例如:表A 索引列为col1,则select * from a where col1=123 and col2>10 ;

##5、MySQL中存在索引但不能使用索引的场景

  • 以%开头的LIKE查询不能够利用B+Tree索引

  • 数据类型出现隐式转换的时候也不会使用索引

    如索引字段为字符串类型,则在查询时候一定得把双引号包起来。如col1为索引字段且类型为字符串,用col1 = 123将不会使用索引,用col1 = '123' 则可以使用索引。

  • 复合索引的情况下,假如查询条件不包含索引列最左边部分,即不满足最左原则,无法使用索引

    比如在col1+col2+col3字段上的联合索引能够被包含col1、(col1+col2)、(col1+col2+col3)的等值查询利用到,不能够被col2、(col2+col3)的等值查询利用

  • 如果MySQL估计使用索引比全表扫描更慢,则不使用索引

    查询以“S”开头的标题的电影,需要返回记录比例很大,MySQL预估使用索引还不如全表扫描快。这个可以通过explain来分析。

  • 用OR条件隔开的条件,如果or前面的条件中有列的索引,而后面的列中没有索引,则涉及的索引都不会用到

    表A的索引为col1,select * From A where col1 =123 OR col2 = 456;//无法使用索引

© 著作权归作者所有

骑着机车敲代码
粉丝 16
博文 70
码字总数 31439
作品 0
杭州
程序员
私信 提问
MySQL · 最佳实践 · 如何索引JSON字段

概述 MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。...

阿里云云栖社区
2017/12/22
0
0
财务平台亿级数据量毫秒级查询优化之elasticsearch原理解析

说在前面 财务平台进行分录分表以后,随着数据量的日渐递增,业务人员对账务数据的实时分析响应时间越来越长,体验性慢慢下降,之前我们基于mysql的性能优化做了一遍,可以说基于mysql该做的...

天河2018
2018/07/13
327
0
不懂MySQL索引优化?甩你这份清单别嫌多

周四见 公开课系列 We,知数堂 习惯用实力介绍自己—我们只分享干货 重磅福利来袭 2018年5月24日,20:30-21:30 周四见 不见不散! 主讲嘉宾:叶金荣 知数堂联合创始人 & 3306π社区负责人 资深...

n88lpo
2018/05/24
0
0
索引初识一 MySql

1 mysql索引类型【主要分4类索引】 创建索引: 1.添加PRIMARY KEY(主键索引) 【主键:一种唯一性索引,必须指定为primary key 】 mysql> ALTER TABLE ADD PRIMARY KEY ( ) 2.添加UNIQUE(唯...

技术林工
2017/05/18
0
0
掌握 MySQL 这 19 个骚操作,效率至少提高3倍

本文我们来谈谈项目中常用的MySQL优化方法,共19条,利用好这19条方法,会让你的效率提升至少3倍。 1、EXPLAIN 做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(...

Java填坑路
03/08
84
0

没有更多内容

加载失败,请刷新页面

加载更多

Docker常用命令小记

除了基本的<font color="blue">docker pull</font>、<font color="blue">docker image</font>、<font color="blue">docker ps</font>,还有一些命令及参数也很重要,在此记录下来避免遗忘。 ......

程序员欣宸
昨天
3
0
MAT使用-jvm内存溢出问题分析定位

1.MAT简介: MAT 全称 Eclipse Memory Analysis Tools 是一个分析 Java堆数据的专业工具,可以计算出内存中对象的实例数量、占用空间大小、引用关系等,看看是谁阻止了垃圾收集器的回收工作,...

xiaomin0322
昨天
4
0
内网和外网之间的通信(端口映射原理)

首先解释一下“内网”与“外网”的概念: 内网:即所说的局域网,比如学校的局域网,局域网内每台计算机的IP地址在本局域网内具有互异性,是不可重复的。但两个局域网内的内网IP可以有相同的...

Jack088
昨天
5
0
3.深入jvm内核-原理、诊断与优化-4. GC算法和种类

一、GC算法和种类 GC的概念 GC算法 引用计数法 标记清除 标记压缩 复制算法 可触及性 Stop-The-World GC的对象是堆空间和永久区 引用计数法 老牌垃圾回收算法 通过引用计算来回收垃圾 使用者...

hexiaoming123
昨天
4
0
MySQL中的哈希索引

Memory中的哈希索引 哈希索引是基于哈希表实现的,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的...

我的眼里只有眼屎
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部