Mysql索引的最左前缀原则是什么

---

前言

之所以有这个最左前缀索引

归根结底是mysql的数据库结构 B+树

在实际问题中 比如

索引index (a,b,c)有三个字段，

使用查询语句select * from table where c = '1' ，sql语句不会走index索引的

select * from table where b =‘1’ and c ='2' 这个语句也不会走index索引

1. 定义

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

为了更好辨别这种情况，通过建立表格以及索引的情况进行分析

2. 全索引顺序

建立一张表，建立一个联合索引，如果顺序颠倒，其实还是可以识别的，但是一定要有它的全部部分

建立表

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

建立索引ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

索引的顺序位name-age-pos

显示其索引有没有show index from staffs;

通过颠倒其左右顺序，其执行都是一样的

主要的语句是这三句

1. explain select *from staffs where name='z3'and age=22 and pos='manager';

2. explain select *from staffs where pos='manager' and name='z3'and age=22;

3. explain select *from staffs where age=22 and pos='manager' and name='z3';

以上三者的顺序颠倒，都使用到了联合索引

最主要是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划

不论以何种顺序都可使用到联合索引

3. 部分索引顺序

3.1 正序

如果是按照顺序（缺胳膊断腿的），都是一样的

1. explain select *from staffs where name=‘z3’;

2. explain select *from staffs where name='z3’and age=22;

3. explain select *from staffs where name='z3’and age=22;

其type都是ref类型，但是其字段长度会有微小变化，也就是它定义的字长长度变化而已

3.2 乱序

如果部分索引的顺序打乱

1. 只查第一个索引explain select *from staffs where name='z3';

2. 跳过中间的索引 explain select *from staffs where name='z3' and pos='manager';

3. 只查最后的索引 explain select *from staffs where pos='manager';

可以发现正序的时候

AILOGO

LOGO123旗下的AI智能LOGO生成器，只需输入品牌名称就能免费在线生成公司logo设计及配套企业VI，轻松打造您的个性品牌！

139 查看详情

如果缺胳膊少腿，也是按照正常的索引

即使跳过了中间的索引，也是可以使用到索引去查询

但是如果只查最后的索引

type就是all类型，直接整个表的查询了（这是因为没有从name一开始匹配，直接匹配pos的话，会显示无序，）

有些时候type就是index类型，这是因为还是可以通过索引进行查询

index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描

4. 模糊索引

类似模糊索引就会使用到like的语句

所以下面的三条语句

如果复合最左前缀的话，会使用到range或者是index的类型进行索引

1. explain select *from staffs where name like '3%'; 最左前缀索引，类型为index或者range

2. explain select *from staffs where name like '%3%'; 类型为all，全表查询

3. explain select *from staffs where name like '%3%';，类型为all，全表查询

5. 范围索引

如果查询多个字段的时候，出现了中间是范围的话，建议删除该索引，剔除中间索引即可

具体思路如下

建立一张单表

CREATE TABLE IF NOT EXISTS article(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) UNSIGNED NOT NULL,
	category_id INT(10) UNSIGNED NOT NULL,
	views INT(10) UNSIGNED NOT NULL,
	comments INT(10) UNSIGNED NOT NULL,
	title VARCHAR(255) NOT NULL,
	content TEXT NOT NULL
);

INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

经过如下查询：

explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

发现其上面的单表查询，不是索引的话，他是进行了全表查询，而且在extra还出现了Using filesort等问题

所以思路可以有建立其复合索引

具体建立复合索引有两种方式：

1. create index idx_article_ccv on article(category_id,comments,views);

2. ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );

但这只是去除了它的范围，如果要去除Using filesort问题的话，还要将其中间的条件范围改为等于号才可满足

发现其思路不行，所以删除其索引 DROP INDEX idx_article_ccv ON article;

主要的原因是：

这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。

当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

所以建立复合索引是对的

但是其思路要避开中间那个范围的索引进去

只加入另外两个索引即可create index idx_article_cv on article(category_id, views);

以上就是Mysql索引的最左前缀原则是什么的详细内容，更多请关注其它相关文章！

# 会有  # 网站建设介绍视频  # 泰州营销型网站建设企业  # 青州市网站建设服务  # seo独立站优化资料  # 越秀快照seo公司咨询客服  # 刷seo还管用吗  # 河东区营销推广策划中心  # 营销型网站建设技术外包  # seo主流搜索引擎  # 本溪品牌网站建设  # MySQL  # 您的  # 是一个  # 如何设置  # 都是  # 出现了  # 跳过  # 会使  # 这是因为  # 镜像 

相关栏目： 【 Google疑问12 】 【 Facebook疑问10 】 【 优化推广96088 】 【 技术知识133117 】 【 IDC资讯59369 】 【 网络运营7196 】 【 IT资讯61894 】

相关推荐： 火柴人战争网页版在线玩  小米civi如何设置锁屏时间  美发店速赢秘籍  c++如何实现一个简单的RPC框架_c++远程过程调用原理与实践  虫虫漫画绿色安全入口_虫虫漫画绿色安全入口安全看漫画  苹果iPhone14ProMax如何新建AppleID_iPhone14ProMax新建AppleID具体流程  泰拉瑞亚网页版在线登录入口 泰拉瑞亚官方正版入口  Excel如何设置动态下拉菜单_Excel表格下拉选项快速方法  《三角洲行动》战斗步枪与机枪类改装代码分享  mysql如何回滚事务_mysql ROLLBACK事务回滚方法  《edge浏览器》关闭翻译功能方法  《土豆雅思》修改密码方法  红手指专业版app注册教程  Windows Audio服务启动失败怎么办_电脑没声音的终极服务修复法【修复】  CSS过渡如何实现按钮悬停效果_transition属性控制背景颜色变化  Windows自带的便笺数据如何备份_防止数据丢失的便利贴迁移教程【干货】  mysql中如何配置字符集和排序规则_mysql字符集排序配置  poki官网最新入口 poki小游戏大全入口  如何查询个人病历记录  OPPO手机参数配置如何开启护眼模式_OPPO手机参数配置护眼模式开启指南  qq邮箱怎么注册_QQ邮箱注册步骤与注意事项  Cassandra中复合主键、二级索引与ORDER BY排序的限制与解决方案  百度小说看书时如何翻页_百度小说手动翻页与自动翻页设置  韩剧圈正版官网入口_韩剧圈官方指定登录  国际经济与贸易就业方向解析  《合金装备4》有望推出重制版！制作人发话了  《sketchbook》选中部分图案移动方法  VS Code快捷键when上下文子句的妙用  Win10运行窗口在哪里打开 Win10调出运行命令框快捷键【技巧】  疯狂小鸟微信小游戏入口 疯狂小鸟网页版秒玩  C++中的explicit关键字有什么作用_C++类型转换控制与explicit使用  mysql怎么查询数据_mysql基础查询语句使用教程  微信朋友圈怎么设置三天可见 微信朋友圈设置指定天数可见步骤【教程】  电脑的“恢复环境(WinRE)”找不到怎么办_Windows系统恢复环境重建【高级修复】  ToDesk远程摄像头功能使用方法_ToDesk远程视频画面查看设置教程  Django模型动态关联检查：高效管理复杂关系  Windows 11怎么删除恢复分区_Windows 11使用Diskpart命令强行删除分区  QQ邮箱PC端登录页面_QQ邮箱网页版登录界面  《画加》约稿流程  抖音小程序怎么开通？小程序开通条件是什么？  解决J*aScript动态图片上传中ID重复问题：在同一页面显示多张独立图片  跨语言测试实践：使用Python Selenium测试现有J*a Web项目  iSpring三分屏制作教程  Firefox OS应用开发：解决XMLHttpRequest跨域请求阻塞问题  使用jQuery精确检测除指定元素外任意位置的点击事件  QQ网页版入口导航 QQ网页版在线访问通道  菜鸟裹裹怎样获得取件码_菜鸟裹裹获得取件码步骤  VS Code如何设置默认配置  动漫岛汉化官网网 动漫岛官方动漫汉化地址  掌握Go App Engine项目结构与GOPATH：包管理与导入实践 

 2023-05-28