图解:基于B+树索引结构,MySQL可以这么优化

首先通过索引可以很大程度的提高检索效率,这个就不解释了

关于B+树的相关知识和应用可以点击传送门

1. 索引失效的情况

  • 字符串索引字段必须加单引号,不然索引失效

  • 在索引字段上使用任何操作(计算、函数、类型转换等),都导致走全表扫描

  • where查询条件中使用不等于、大于、小于等操作,会导致索引失效

  • 少用or,用它来连接时,可能会导致索引失效

  • is null 和is not null也会无法使用索引

  • like 以通配符开头"%abc" 会造成全表扫描

2. 查看索引使用情况

使用下面的语句进行查看

explain + sql;

执行结果

image

id

标志这个sql语句被分为几个(行数)独立的sql执行,执行顺序依照(1)从大到小(2)从上到下 依次排列执行

select_type

查询类型

SIMPLE    简单的select查询,查询中不包含子查询或者UNION
PRIMARY 查询中包含子查询,其中最外层查询为PRIMARY
SUBQUERY 别的查询语句(select或where)中的子查询
DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中 ,,,,临时表!
UNION 若第二个SELECT出现在UNION之后,则被标记为UNION:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
UNION RESULT UNION的结果

table

表示当前行的子查询涉及的表

type

表示查询用到了那种索引类型

从最好到最差依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system   表中只有一条记录,一般来说只在系统表里出现。
const 表示通过一次索引查询就查询到了,一般对应索引列为primarykey 或者unique where语句中 指定 一个常量,因为只匹配一行数据,MYSQL能把这个查询优化为一个常量,所以非常快。
eq_ref 唯一性索引扫描。此类型通常出现在多表的 join 查询,对于每一个从前面的表连接的对应列,当前表的对应列具有唯一性索引,最多只有一行数据与之匹配。
ref 非唯一性索引扫描。同上,但当前表的对应列不具有唯一性索引,可能有多行数据匹配。此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
range 索引的范围查询。查询索引关键字某个范围的值。
index 全文索引扫描。与all基本相同,扫描了全文,但查询的字段被索引包含,故不需要读取表中数据,只需要读取索引树中的字段。
all 全文扫描。未使用索引,效率最低。

key 和 possible key 与 key_len

possible key 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.
key 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
覆盖索引:指在查询中,要查询的字段被某个索引的所有字段包含,(type一般为index),那么这个索引只出现在key中,不出现在possible key中。

ref

显示索引的哪一列被使用了
ref 显示使用哪个列或常数与key一起从表中选择行。在eq_ref 与ref中才不为null
如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func

rows

MySQL 查询优化器根据统计信息
估算 SQL 要查找到结果集需要扫描读取的数据行数 这个值非常直观显示 SQL 的效率好坏
原则上 rows 越少越好

extra

额外信息

Using Filesort 表示对结果排序时无法使用索引,使用了一个临时索引对结果排序。
Using Temporary 在查询中使用了临时表保存中间结果。
Using Index 表示使用了覆盖索引(见5.) 如果同时出现了Using Where 表示索引被同时用来查找对应的键,若未出现,则仅仅用来读取数据。
Using Join Buffer 表示在查询的时候 Join使用的非常多,使用了连接缓存,可以在配置文件中把缓冲区的join buffer调大一点。
Using where 使用了where
Impossible where where后的值总是false 比如 (...where i=1 and i=2)
distinct 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

3. 建立自增整数主键

首先建表必须建立一个主键

在InnoDB引擎中,只有主键索引才是聚集索引

也就是说只有主键索引的的叶子节点才挂接的有数据

辅助索引的叶子节点只挂接主键的值

然后再用主键值通过主键索引检索具体的数据内容

image

建表时设置一个自增的整数主键,会自动建立索引
在插入数据时,由于主键本身就是自增有序的

可以尽量减少B+树为自平衡而做的旋转、合并和拆分操作

从而提高效率,也可以减少磁盘空间中碎片的产生

如果是字符串类型的主键,并且没有什么规律的话
会导致插入的时候比较随机

可能会导致较多的旋转、合并和拆分操作

降低效率,还会在磁盘空间中产生碎片

增加碎片整理的开销

如果你没有建立任何主键

而MySQL中InnoDB引擎是要求表必须有一个主键的

MySQL会将选一个不包含null的字段将它当做主键,并建立索引

如果连这样的字段都没有,就会使用行号生成一个聚集索引,把它当做主键,这个行号大小为6bytes

但是这个行号是全局的,供所有没有主键的表用

当这个行号超过56 800 235 584之后
就会产生主键冲突的问题

所以还是建议要手动建立一个主键

4. 查询条件,尽量使用主键

一般利用辅助索引的话,需要进行两棵B+树的扫描

如果能够直接使用主键索引

那么就可以只进行一棵树的扫描

比如分页查询的优化,当数据量达到百万、千万级的时候

select * from users limit 100000,1000;

查询第100000-101000的数据
这条sql语句是不执行索引的

如果这个表有一个自增的主键索引
那么这个sql就可以这么优化了

select * from users where id > 100000 limit 1000

这个就会用到主键索引
速度能提高数十倍

5. 建立索引字段的选择

5.1 离散型好的字段

什么叫离散型好?
就是这个字段重复的值越少越好
有这么一个公式可以计算

P = count(distinct col)/count(col)

P越大,离散型越好,越适合做索引字段
为什么呢?

image

比如这棵B+树,某个字段只有1和2两个值
当做为索引进行查询的时候,如果查询1
会发现在根节点的左侧,走哪条路径都行
导致唯一性选择特别差

在这种情况下,当找到需要的数据之后,还要走主键索引进行数据的读取
这种情况还不如直接全表扫描更快

5.2 最左匹配原则

即对索引项中关键字的匹配,都是从左往右进行匹配的

如果是字符串字段作为了索引字段,是怎么匹配的呢?

这个问题问的好

通过字符的ASCII码

image

比如这个结构
查询的时候,会将查询条件先跟a进行比较
大于a就往右走
小于a就往左走

因为是从最左开始匹配

所以当条件是where 字段 like '%ab';的时候
索引就会失效,因为需要进行索引匹配的地方,做了模糊查询要求

6. 合理建立和使用联合索引

实际当中,我们的业务往往不会仅仅要求通过某一个字段进行数据的查询和过滤

联合索引顾名思义,就是多个字段共同建立索引

具体怎么建立和使用联合索引呢?
举个栗子

create index idx_name_phoneNumber_age on users(name,phone_number,age)

那么针对联合索引users(name,phone_number,age)

select * from userswhere name="张三" and phone_number = "1888888888" and age = "18";

这条sql语句查询过程,肯定会用到联合索引

select * from userswhere name="张三" and phone_number >"1888888888" and age > "18";

这种情况,会用到name、phone_number字段的索引,而不会用到age的索引
原因就是离散型原则和最左匹配原则
name首选肯定会用到索引
phone_number的时候,因为是一个大于的选择条件,这个离散型是很差的
只有两种情况,大于或者小于
所以age就不会再用到索引了

select * from userswhere name="张三" and phone_number ="1888888888" and age > "18";

这种情况,三个字段都会用到索引

select * from userswhere name>"张三" and phone_number ="1888888888" and age > "18";

这种情况,只有name字段会用到索引

所以,当建立了联合索引之后
最左边的字段,就没有必要再建立索引了

7. 覆盖索引的使用

通过索引项的信息可直接返回需要查询的列,则称该索引为查询sql的覆盖索引

image

还得用到这个图

当用到覆盖所用的时候,数据库不需要再去主键索引重新读取数据
可以直接从辅助索引中拿到数据并返回
效率可至少提高一倍

假设
表users使用的是InnoDB引擎
建立了索引 PK(id) key(name,phone_number) unique(username)

那么

image
select username from users where username = ?;

用到了覆盖索引,直接返回username

不会再去查询主键索引

image
select * from users where username = ?;

只要是select * ,基本上就跟覆盖索引告别了

是不会用到覆盖索引的

image
select id, username from users where username = ?;

通过辅助索引的叶子节点已经可以拿到username和id了

所以用到了覆盖索引

select username,phone_number from users where username = ?;

索引是这么建立的 PK(id) key(name,phone_number) unique(username)
虽然username和phone_number都建立了索引
但是他们是属于两棵不同的B+数据索引
所以,肯定是要扫主键索引才能拿到两个数据的

select phone_number from users where username = ?;

这个也会走覆盖索引
因为节点内容是两个字段的组合
拆分之后就可以返回phone_number字段了

综上所述,尽量少用select *
可以很好的利用覆盖索引,提高检索效率

文/戴先生@2020年6月27日

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 211,817评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,329评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,354评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,498评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,600评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,829评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,979评论 3 408
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,722评论 0 266
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,189评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,519评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,654评论 1 340
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,329评论 4 330
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,940评论 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,762评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,993评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,382评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,543评论 2 349

推荐阅读更多精彩内容