MySQL 索引的一些细节分享

前几天同事问了我个 mysql 索引的问题，虽然大概知道，但是还是想来实践下，就是 is null，is not null 这类查询是否能用索引，可能之前有些网上的文章说都是不能用索引，但是其实不是，我们来看个小试验

CREATE TABLE `null_index_t` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `null_key` varchar(255) DEFAULT NULL,
 `null_key1` varchar(255) DEFAULT NULL,
 `null_key2` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_1` (`null_key`) USING BTREE,
 KEY `idx_2` (`null_key1`) USING BTREE,
 KEY `idx_3` (`null_key2`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

用个存储过程来插入数据

delimiter $	#以delimiter来标记用$表示存储过程结束
create procedure nullIndex1()
begin
declare i int;
declare j int;
set i=1;
set j=1;
while(i<=100) do
	while(j<=100) do
		IF (i % 3 = 0) THEN
	   INSERT INTO null_index_t ( `null_key`, `null_key1`, `null_key2` ) VALUES (null , LEFT(MD5(RAND()), 8), LEFT(MD5(RAND()), 8));
  ELSEIF (i % 3 = 1) THEN
			 INSERT INTO null_index_t ( `null_key`, `null_key1`, `null_key2` ) VALUES (LEFT(MD5(RAND()), 8), NULL, LEFT(MD5(RAND()), 8));
	 ELSE
			 INSERT INTO null_index_t ( `null_key`, `null_key1`, `null_key2` ) VALUES (LEFT(MD5(RAND()), 8), LEFT(MD5(RAND()), 8), NULL);
  END IF;
		set j=j+1;
	end while;
	set i=i+1;
	set j=1;
end while;
end
$
call nullIndex1();

然后看下我们的 is null 查询

EXPLAIN select * from null_index_t WHERE null_key is null;

再来看看另一个

EXPLAIN select * from null_index_t WHERE null_key is not null;

从这里能看出来啥呢，可以思考下

从上面可以发现，is null应该是用上了索引了，所以至少不是一刀切不能用，但是看着is not null好像不太行额
我们在做一点小改动，把这个表里的数据改成 9100 条是 null，剩下 900 条是有值的，然后再执行下

然后再来看看执行结果

EXPLAIN select * from null_index_t WHERE null_key is null;


EXPLAIN select * from null_index_t WHERE null_key is not null;

是不是不一样了，这里再补充下我试验使用的 mysql 是 5.7 的，不保证在其他版本的一致性，
其实可以看出随着数据量的变化，mysql 会不会使用索引是会变化的，不是说 is not null 一定会使用，也不是一定不会使用，而是优化器会根据查询成本做个预判，这个预判尽可能会减小查询成本，主要包括回表啥的，但是也不一定完全准确。

以上就是MySQL 索引的一些细节分享的详细内容，更多关于MySQL 索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL 8.0 之索引跳跃扫描(Index Skip Scan)

    前言 MySQL 8.0.13开始支持 index skip scan 也即索引跳跃扫描.该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况,优化器依然能组使用组合索引. talk is cheap ,show me the code 实践 使用官方文档的例子,构造数据 mysql> CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2)); Query OK, 0 rows affected (0.

  • MySQL如何基于Explain关键字优化索引功能

    explain显示了MySQL如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句.简单讲,它的作用就是分析查询性能. explain关键字的使用方法很简单,就是把它放在select查询语句的前面. mysql查看是否使用索引,简单的看type类型就可以.如果它是all,那说明这条查询语句遍历了所有的行,并没有使用到索引. 比如:explain select * from company_info where cname like '%小%' explain

  • MySql范围查找时索引不生效问题的原因分析

    MySql范围查找时索引不生效问题的原因分析

    1 问题描述 本文对建立好的复合索引进行排序,并取记录中非索引字段,发现索引不生效,例如,有如下表,DDL语句为: CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_da

  • MySQL 全文索引的原理与缺陷

    MySQL全文索引一种特殊的索引,它会把某个数据表的某个数据列出现过的所有单词生成一份清单. alter table tablename add fulltext(column1,column2) 说明: 只能在MyISAM数据表中创建 全文索引是以空格或标点隔开才能搜到的,搜中文是搜不到(有专门的应用支持中文分词可以搜中文,但都不理想) 少于3个字符的单词不会被包含在全文索引里,可以通过修改my.cnf修改选项 ft_min_word_len=3 重新启动MySQL服务器,用repair ta

  • MySQL如何优化索引

    1.  MySQL如何使用索引 索引用于快速查找具有特定列值的行.如果没有索引,MySQL必须从第一行开始,然后遍历整个表以找到相关的行.表越大,花费越多.如果表中有相关列的索引,MySQL可以快速确定要在数据文件中间查找的位置,而不必查看所有数据.这比顺序读取每一行要快得多. 大多数MySQL索引(PRIMARY KEY,UNIQUE,INDEX和FULLTEXT)存储在B树(B-tree)中.例外情况:空间数据类型的索引使用R树: MEMORY表还支持哈希索引. InnoDB对FULLTEX

  • Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

    Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

    背景 在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢.这个varchar字段有一个复合索引.其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据. 使用的是mysql 5.6版本,innoDB引擎 实际情况如下 下面我们来看一下执行的结果 在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字.不然就会报错 还有可能查出来的数据不是我们想要的数据.如下图 分析 从执行结果来

  • MySQL唯一索引和普通索引选哪个？

    想象这样一个场景,在设计一张用户表时,每人的身份证号是唯一的,需要搜索.但由于身份证号字段较大,不好将其作为主键.在业务代码已经保证插入身份证唯一的情况下,可以选择建立唯一索引和普通索引,这时该如何选择呢?接下来,将从查询和更新的执行过程进行分析. 查询过程 假设 k 是表 t 上的索引,在搜索 select id from t where k=5 时,会先从 k 这棵 B+ 的树根开始,按层搜索叶子节点,找到 k=5 的数据页,然后在数据页内容进行二分法定位. 对于普通索引,找到 k=5 的记

  • MySql如何查看索引并实现优化

    MySql如何查看索引并实现优化

    mysql中支持hash和btree索引.innodb和myisam只支持btree索引,而memory和heap存储引擎可以支持hash和btree索引 我们可以通过下面语句查询当前索引使用情况: show status like '%Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first

  • MySQL 索引的一些细节分享

    MySQL 索引的一些细节分享

    前几天同事问了我个 mysql 索引的问题,虽然大概知道,但是还是想来实践下,就是 is null,is not null 这类查询是否能用索引,可能之前有些网上的文章说都是不能用索引,但是其实不是,我们来看个小试验 CREATE TABLE `null_index_t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `null_key` varchar(255) DEFAULT NULL, `null_key1` varchar(255) D

  • 美团网技术团队分享的MySQL索引及慢查询优化教程

    美团网技术团队分享的MySQL索引及慢查询优化教程

    MySQL凭借着出色的性能.低廉的成本.丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库.虽然性能出色,但所谓"好马配好鞍",如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如"精通MySQL"."SQL语句优化"."了解数据库原理"等要求.我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,

  • 分享15个Mysql索引失效的场景

    分享15个Mysql索引失效的场景

    目录 背景 数据库及索引准备 创建表结构 初始化数据 数据库版本及执行计划 1 联合索引不满足最左匹配原则 2 使用了select * 3 索引列参与运算 4 索引列参使用了函数 5 错误的Like使用 6 类型隐式转换 7.使用OR操作 8 两列做比较 9 不等于比较 10 is not null 11 not in和not exists 12 order by导致索引失效 13 参数不同导致索引失效 14 其他 小结 背景 无论你是技术大佬,还是刚入行的小白,时不时都会踩到Mysql数据库不

  • MySQL索引概念及七种索引类型分享介绍

    目录 1 索引的概念 2 索引的类型 1 索引的概念 索引(在MySQL中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构,这也是索引最基本的功能. 索引对于良好的性能非常关键.数据量越大时,索引对性能的影响也越重要,好的索引可以将查询性能提高几个数量级.在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但是在数据量逐渐增大时,糟糕的索引会使MySQL的性能急剧的下降. 索引优化是查询性能优化最有效的手段. 如果想要在一本书中找到某个特定主题,一般会先看书的目录,找

  • 分享几道关于MySQL索引的重点面试题

    分享几道关于MySQL索引的重点面试题

    前言 索引是对数据库中一或多个列值的排序,帮助数据库高效获取数据的数据结构 假如我们用类比的方法,数据库中的索引就相当于书籍中的目录一样,当我们想找到书中的摸个知识点,我们可以直接去目录中找而不是在书中每页的找,但是这也抛出了索引的一个缺点,在对数据库修改的时候要修改索引到导致时间变多. 但MySQL 索引你真的懂吗?这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则? 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话,

  • 详解MySQL索引原理以及优化

    前言 本文是美团一位大佬写的,还不错拿出来和大家分享下,代码中嵌套在html中sql语句是java框架的写法,理解其sql要执行的语句即可. 背景 MySQL凭借着出色的性能.低廉的成本.丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库.虽然性能出色,但所谓"好马配好鞍",如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如"精通MySQL"."SQL语句优化"."了解数据库原理"等要求.我

  • MySQL索引用法实例分析

    本文实例分析了MySQL索引用法.分享给大家供大家参考,具体如下: MYSQL描述: 一个文章库,里面有两个表:category和article.category里面有10条分类数据.article里面有20万条.article里面有一个"article_category"字段是与category里的"category_id"字段相对应的.article表里面已经把 article_category字义为了索引.数据库大小为1.3G. 问题描述: 执行一个很普通的查

  • mysql索引必须了解的几个重要问题

    本文讲述了mysql索引必须了解的几个重要问题.分享给大家供大家参考,具体如下: 1.索引是做什么的? 索引用于快速找出在某个列中有一特定值的行.不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行. 表越大,花费的时间越多.如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据. 大多数MySQL索引(PRIMARY KEY.UNIQUE.INDEX和FULLTEXT)在B树中存储.只是空间列类型的索引使用R-树,并且MEMORY

  • mysql索引基数概念与用法示例

    本文实例讲述了mysql索引基数概念与用法.分享给大家供大家参考,具体如下: Cardinality(索引基数)是mysql索引很重要的一个概念 索引基数是数据列所包含的不同值的数量.例如,某个数据列包含值1.2.3.4.5.1,那么它的基数就是5.索引的基数相对于数据表行数较高(也就是说,列中包含很多不同的值,重复的值很少)的时候,它的工作效果最好.如果某数据列含有很多不同的年龄,索引会很快地分辨数据行.如果某个数据列用于记录性别(只有"M"和"F"两种值),那么

  • mysql索引覆盖实例分析

    本文实例讲述了mysql索引覆盖.分享给大家供大家参考,具体如下: 索引覆盖 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.这种查询速度非常快,称为"索引覆盖". 假设有一张t15表,在表中建立了一个联合索引:cp(cat_id,price) 当我们使用下面的sql语句,会出现索引覆盖的情况.不信我们可以来查看一下,这里的Extra中显示了Using index,表示这条sql语句刚好用到了索引覆盖. select price from t1

随机推荐