首页 > 数据库 >MySQL联合索引生效验证

MySQL联合索引生效验证

时间:2023-07-01 13:37:59浏览次数:50  
标签:验证 explain 索引 gid student MySQL where select

建表、添加数据,用于测试

CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gid` varchar(20) NOT NULL,
`cid` int(11) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_Gid_Cid_SId` (`gid`,`cid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into student (gid,cid,uid,name) values
('10001',1,1,'张三'),
('10002',2,2,'李四'),
('10003',3,3,'王五');

select * from student;

 

查询结果字段说明

id:编号
select_type:查询类型
table:表
type:类型
possible_keys:预测用到的索引
key:实际使用的索引
key_len:实际使用的索引的长度
ref:表之间的引用
rows:通过索引查询到的数据量
Extra:额外的信息

 

type 字段解释

type 表示对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。

常用的类型有: ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)

ALL — MySQL将遍历全表以找到匹配的行(全表扫描)
index —index与ALL区别为index类型只遍历索引树
range —只检索给定范围的行,使用一个索引来选择行
ref —表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
eq_ref —类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
const —当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量。
system —system是const类型的特例,当查询的表只有一行的情况下,使用system
NULL —MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成

 

全部索引字段生效

explain select * from student where gid='10001' and cid=1 and uid=1;
explain select * from student where gid = '10001' and cid = 1 and uid>1;


预计不使用索引,实际全部索引字段生效

  前提是 gourp by 和 order by 后面使用的字段是联合索引的字段,且查询的字段也是联合索引的字段,那么索引会全生效

  这里 type 的类型为 index,综上所述,应该是扫描了整个索引树,比全表扫描好点。

explain select uid from student order by cid;
explain select gid from student group by gid;


只有一个索引字段生效

explain select * from student where gid='10001';
explain select * from student where gid='10001' and uid=1;
explain select * from student where gid > '10001';


只有两个索引字段生效

explain select * from student where gid='10001' and cid=1;
explain select * from student where gid = '10001' and cid > 1;


预计会使用索引,实际没使用索引

  下面关于 between 的语句,是将 gid 字段改成 int ,再使用 between 做的测试,仍然不使用索引。

  之所以预计会使用索引,是看很多博客都说 >=、<= 和 between 会使用索引,或者说符合最左原则会使用,所以验证了下。发现 >、<、>=、<= 都走索引,between 不走索引。

explain select * from student where gid like '100%';
explain select * from student where gid between 10001 and 10003;

 

like 真正能使用索引的情况

explain select * from student where gid like 'test%';

PS:

  根据上面验证的情况,猜测 like 语句的参数值(与百分号拼接的内容)不能是数字类型,否则也会不生效。

  另外,百分号在前(后缀)和在两边(中缀)索引不会生效。

 

以下sql未使用索引

  where 子句中包好 or 会导致联合索引失效。

explain select * from student where cid=1 and uid=1;
explain select * from student order by gid;
explain select name from student order by gid;
explain select uid from student order by name;
explain select * from student where gid='10001' and cid=1 or uid=1;

 

说明

  以上内容均经过本地测试。

 

参考:地址1地址2

 

标签:验证,explain,索引,gid,student,MySQL,where,select
From: https://www.cnblogs.com/zhousjcn/p/17519154.html

相关文章

  • jmeter接口测试 -- 连接数据库(MySQL)
    三个步骤一、下载MySQL的连接驱动1、先查看MySQL的版本1)服务器上查看:mysql--version 2)在连接工具上查看 2、下载连接驱动,下载地址:https://dev.mysql.com/downloads/connector/j/1)选择系统(windows系统才选择我下图中......
  • 启动mysql时报错Failed to start mysqld.service: Unit not found
    问题描述:启动mysql时报错Failedtostartmysqld.service:Unitnotfound,如下所示:数据库:mysql5.7.42系统:rhel7.31、问题重现--mysql安装过程[root@leo-mysql-master~]#yumlocalinstallhttps://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm[root@leo......
  • 基于物联网、区块链、RSA加密验证技术实现的防伪溯源系统源码
    一物一码防伪溯源系统能准确获取产品生产经营各个环节的真实信息,利用物联网、云计算、区块链、人工智能、5G等先进技术,结合特有的码码关联和RSA加密验证技术,建立区块链的“身份证”,针对产品种植到销售各环节的质量安全数据进行及时采集上传,数据具有不可逆,不可篡改等特点,实现产品溯......
  • yum安装mysql时出现Public key for mysql-community-common-5.7.42-1.el7.x86_64.rpm
    问题描述:yum安装mysql时出现Publickeyformysql-community-common-5.7.42-1.el7.x86_64.rpmisnotinstalled告警,如下所示:数据库:mysql5.7.42系统:rhel7.31、问题重现[root@leo-mysql-master~]#yuminstall-ymysql-community-serverLoadedplugins:langpacks,product......
  • mysql 联合表查询从表即使有索引依然ALL的一个原因-索引ALL解决,字符编码方式不一致导
    mysql联合表查询从表即使有索引依然ALL的一个原因-索引ALL解决,字符编码方式不一致导致全表搜索那就是主表和从表的关联字段的编码方式不一样!!!产生的现象:解决之后,正确的使用了t2.order_noref索引,而不是ALL检查mysql主体编码方式,是否由于后来新建的表的关联字段和之前的主表的......
  • MYSQL:首次找不到.log文件
    一开始在文件中没有SXR.log文件。 后退,在my.ini中更改 一开始general-log=0,将其改为1即可。 ......
  • MySQL批量新增或修改时出现异常: Lock wait timeout exceeded; try restarting transact
    标题:解决MySQL批量新增或修改时出现"nestedexceptionisjava.sql.SQLException:Lockwaittimeoutexceeded;tryrestartingtransaction"异常摘要在使用MySQL数据库进行批量新增或修改操作时,有时候会遇到"nestedexceptionisjava.sql.SQLException:Lockwaittimeoutexcee......
  • 前端Vue自定义登录界面切换tabs 账号登录验证码登录模版 支持手机号校验邮箱校验验证
    前端Vue自定义登录界面切换tabs账号登录验证码登录模版支持手机号校验邮箱校验、验证码发送, 下载完整代码请访问uni-app插件市场地址:https://ext.dcloud.net.cn/plugin?id=13298效果图如下:cc-loginTabs使用方法<!--selIndex:选中序列 tabs-arr:tabs数组 @tabClic......
  • 30秒了解Mysql
    本篇主要是一文带大家大致了解什么是Mysql。本篇文章主要涉及的内容有:什么是数据库?数据库中的几个基本术语?Mysql中InnoDB引擎支持的4种事务隔离级别Mysql中如何使用ENUM?Mysql中的存储引擎mysql中char与varchar的区别?主键和候选键的区别?blob和text的区别?Mysql中的锁数据库三范式什么......
  • 30秒了解Mysql
    本篇主要是一文带大家大致了解什么是Mysql。本篇文章主要涉及的内容有:什么是数据库?数据库中的几个基本术语?Mysql中InnoDB引擎支持的4种事务隔离级别Mysql中如何使用ENUM?Mysql中的存储引擎mysql中char与varchar的区别?主键和候选键的区别?blob和text的区别?Mysql中的锁数据......