MySQL数据库索引以及失效场景详解
作者:靖节先生 发布时间:2024-01-26 04:12:23
1. MySQL索引概述
1.1 索引的概念
什么是索引,索引就是排好序的快速查找数据结构。
1.2 索引的特点
索引的优点
1.提高数据检索的效率, 降低数据库的IO成本。
2.通过索引列对数据进行排序, 降低数据排序的成本, 降低了CPU的消耗。
索引的缺点
1.虽然索引大大提高了查询速度, 同时却会降低更新表的速度, 如对表进行INSERT、 UPDATE和DELETE。 因为更新表时, MySQL不仅要保存数据, 还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息。
2.实际上索引也是一张表, 该表保存了主键与索引字段, 并指向实体表的记录, 所以索引列也是要占用空间的。
1.3 索引的分类
MySQL 使用的是 Btree 索引。另外还有B+tree 索引,B-tree 索引,具体原理不在细说,原理详情参考官网。
简单说下以下几个常用索引。
单值索引
概念:即一个索引只包含单个列, 一个表可以有多个单列索引
唯一索引
概念: 索引列的值必须唯一, 但允许有空值
主键索引
概念: 设定为主键后数据库会自动建立索引, innodb为聚簇索引。
复合索引
概念: 即一个索引包含多个列
1.4 索引的使用场景
适合创建索引的情况
1.主键自动建立唯一索引;
2.频繁作为查询条件的字段应该创建索引
3.查询中与其它表关联的字段, 外键关系建立索引
4.单键/组合索引的选择问题, 组合索引性价比更高
5.查询中排序的字段, 排序字段若通过索引去访问将大大提高排序速度
6.查询中统计或者分组字段
不适合创建索引的情况
1.表记录太少
2.经常增删改的表或者字段
3.Where 条件里用不到的字段不创建索引
4.过滤性不好的不适合建索引
2. 索引失效场景
2.1 索引失效9种场景
1.全值匹配:查询条件的列与索引列的字段,顺序完全相同。
2. 最佳左前缀:查询条件的列与索引列的字段相同,顺序不同,从不同顺序列开始后边都不走索引。
3. 索引计算:不要在索引上做任何计算
4. 索引范围:索引列上不能有范围查询,比如大于,小于,大于等于,小于等于。
5. 索引覆盖:尽量使用覆盖索引
6. 不等: 使用不等于(!= 或者 <>)的时候
7. null:字段的is not null 与is null
8. like:like的前后模糊匹配
9. or:减少使用or
2.2 索引失效场景总结
全值匹配,左前缀。
索引计算范围要覆盖。
不等于(!= 或者 <>)扫全表,null走索引,not不走。
like模后不模前,见or就走union all
3. 索引失效验证
索引测试环境
1.mysql版本:5.7.27-log,查询语句:select VERSION();
2.建表语句及数据:mysql批量插入数据
3.1 全值匹配
-- 全值匹配
-- 查看sql执行计划
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd';
-- 创建联合索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
创建索引前
创建索引后
3.2 最佳左前缀
-- 创建索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
-- 缺少联合索引的第一个字段
explain select sql_no_cache * from emp where deptId = 4 and name = 'abcd';
-- 联合索引的第一二个字段,缺少最后一个字段
explain select sql_no_cache * from emp where age = 30 and deptId = 4;
-- 联合索引的第一三各字段,缺少第二个字段
explain select sql_no_cache * from emp where age = 30 and name = 'abcd' ;
查询字段与索引字段顺序的不同会导致, 索引无法充分使用, 甚至索引失效!
原因: 使用复合索引, 需要遵循最佳左前缀法则, 即如果索引了多列, 要遵守最左前缀法则。 指的是查询从索引的最左前列开始并且不跳过索引中的列。
结论: 过滤条件要使用索引必须按照索引建立时的顺序, 依次满足, 一旦跳过某个字段, 索引后面的字段都无法被使用
3.3 索引计算
不要在索引上做任何计算!
不在索引列上做任何操作(计算、 函数、 (自动 or 手动)类型转换), 会导致索引失效而转向全表扫描。
1.在查询列上使用函数
-- 索引不带计算
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
-- 索引字段计算
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;
2. 在查询列上做了转换
-- 创建单值索引,字符串类型 name
create index idx_name on emp(name);
-- 字符串加单引号情况
explain select sql_no_cache * from emp where name='30000';
-- 字符串不加单引号, 则会在 name 列上做一次转换!
explain select sql_no_cache * from emp where name=30000;
3.4 索引范围:索引列上不能有范围查询
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';
建议: 将可能做范围查询的字段的索引顺序放在最后
3.5 索引覆盖:尽量使用覆盖索引
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
3.6 不等: 使用不等于(!= 或者 <>)的时候
mysql 在使用不等于(!= 或者<>)时, 有时会无法使用索引会导致全表扫描。
3.7 null:字段的is not null 与is null
当字段允许为 Null 的条件下:
is not null 用不到索引, is null 可以用到索引。
3.8 like:like的前后模糊匹配
前缀不能出现模糊匹配!
3.9 or:减少使用or
使用 union all 或者 union 来替代:
总结
来源:https://blog.csdn.net/m0_37583655/article/details/122342460
猜你喜欢
- 在团队意见PK中,运用对方的知识背景说服对方,这就是技术性击倒。这样通常能把对方驳得哑口无言,我经常被这样击倒,甚至觉得怎么那么多牛逼的设计
- 由于tornado内置的AsyncHTTPClient功能过于单一, 所以自己写了一个基于Tornado的HTTP客户端库, 鉴于自己多处使
- 一、前言越来越多的网站和App开始为用户搭建签到系统,以此来吸引和留住用户。签到系统是一种轻量、互动性强的营销方式,通过用户签到获取免费权益
- ASP正则表达式,RegExp对象提供简单的正则表达式支持功能。RegExp对象的用法: Function RegExpTest(
- 示意图:python双向链表实现代码:#!/usr/bin/python# -*- coding: utf-8 -*-class Node(
- 首先说明代码只是帮助理解,并未写出梯度下降部分,默认参数已经被固定,不影响理解。代码主要实现RNN原理,只使用numpy库,不可用于GPU加
- isdigit() isalpha()用于判断字符串的类型要求1统计某个字符串中的数字数量并输出,例如字符串为“adsf
- 当使用Python的flask框架来开发网站后台,解析前端Post来的数据,通常都会使用request.form来获取前端传过来的数据,但是
- 我们在为大家整Python程序员面试试题中,发现了一些被面试官问到的最多的一些问题,以下就是本篇内容:Python是个非常受欢迎的编程语言,
- 在使用pymongo时遇到了一个小坑:在Flask框架中,将字典插入mongodb后再返回就报错@app.route('xxxx
- 多线程概述多线程使得程序内部可以分出多个线程来做多件事情,充分利用CPU空闲时间,提升处理效率。python提供了两个模块来实现多线程thr
- 本文实例讲述了Yii配置与使用memcached缓存的方法。分享给大家供大家参考,具体如下:1. 下载memcached软件包,解压,把me
- 最近认识了一个做Python语音识别的朋友,聊天时候说到,未来五到十年,Python人工智能会在国内掀起一股狂潮,对各种应用的冲击,不下于淘
- 社会上的任何人,都不愿意自己给人留下难以交往的印象,就算是那些冷漠、寡情的人他们也在不断地寻求一种通道,达到与他人的交流和沟通。如果,在你与
- 前言:工作中遇到以下小问题,解决方法如下,可能比较暴力,暂时留档,再进行优化。要求:将列表中json的 ‘id&
- 下面是我们经常会用到且非常有用的MySQL命令。下面你看到#表示在Unix命令行下执行命令,看到mysql>表示当前已经登录MySQL
- csv(Comma-Separated Values)文件是什么?它是一种文件格式,一般也被叫做逗号分隔值文件,可以使用 Excel 软件或
- 以前的Sony Ericsson牌DVD影碟机坏掉了,上周到沃尔玛买了个philips的回来,于是又淘了一些DVD回来看。在使用遥控的时候忽
- 最近发现一个叫 Bootstrap 的好东西,Bootstrap 是现在最流行的响应式 CSS 框架,它以移动设备优先,能够快速适应不同设备
- 一、多进程的实现方法一# 方法包装 多进程from multiprocessing import Processfrom ti