MySQL explain & 有/无索引的区间查找

最新推荐文章于 2023-04-19 15:05:58 发布
最新推荐文章于 2023-04-19 15:05:58 发布
阅读量1.2k 收藏 1
点赞数
分类专栏： # 数据库-关系型-非关系型
原文链接：https://www.cnblogs.com/phpdragon/p/8231533.html
版权

转载自： https://www.cnblogs.com/phpdragon/p/8231533.html，实践如下

  • MySQL explain

https://www.cnblogs.com/xuanzhi201111/p/4175635.html

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

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

  1. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

  2. index: Full Index Scan，index与ALL区别为index类型只遍历索引树

  3. range:只检索给定范围的行，使用一个索引来选择行

  4. ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

  5. eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

  6. const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

  7. NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

主键区间查询

mysql> EXPLAIN SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999990 AND id < 8999999;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_user | range | PRIMARY       | PRIMARY | 8       | NULL |    7 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT count(*) FROM test_user WHERE id > 8900000 AND id < 8999999;
+----------+
| count(*) |
+----------+
|    99998 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT count(*) FROM test_user WHERE id > 8999900 AND id < 8999999;
+----------+
| count(*) |
+----------+
|       98 |
+----------+
1 row in set (0.00 sec)

mysql>

查询时间跟查询的区间数据量成相对的正比增长，同时使用到了主键索引。

字符串区间查找

SELECT id, username, email, PASSWORD FROM test_user WHERE username > 'username_800000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419';

1121471 rows in set (3.58 sec)

查询未使用索引和聚合索引，进行了全表扫描，所以查询比较的慢。

mysql> explain SELECT id, username, email, PASSWORD FROM test_user WHERE username > 'username_800000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419';
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys                                                         | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_user | ALL  | index_name,index_union_name_password,index_union_name_password_status | NULL | NULL    | NULL | 9702644 | Using where |
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql>
  • username 虽然有索引，但是区间查询仍然是遍历全表
mysql> show index in test_user;
+-----------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_user |          0 | PRIMARY                          |            1 | id          | A         |     9702644 |     NULL | NULL   |      | BTREE      |         |               |
| test_user |          1 | index_name                       |            1 | username    | A         |     9702644 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password        |            1 | username    | A         |     9702644 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password        |            2 | password    | A         |     9702644 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password_status |            1 | username    | A         |     9702644 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password_status |            2 | password    | A         |     9702644 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password_status |            3 | status      | A         |     9702644 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

mysql>  explain SELECT id, username, email, PASSWORD FROM test_user WHERE username > 'username_900000';
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys                                                         | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_user | ALL  | index_name,index_union_name_password,index_union_name_password_status | NULL | NULL    | NULL | 9702644 | Using where |
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT id, username, email, PASSWORD FROM test_user WHERE username > 'username_000000' and username < 'username_900000';
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys                                                         | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_user | ALL  | index_name,index_union_name_password,index_union_name_password_status | NULL | NULL    | NULL | 9702644 | Using where |
+----+-------------+-----------+------+-----------------------------------------------------------------------+------+---------+------+---------+-------------+
1 row in set (0.05 sec)

mysql>

非字符串,有/无索引的区间查询对比

使用了index 遍历索引树的方式

mysql> desc test_user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(50) | YES  | MUL | NULL    |                |
| email    | varchar(30) | YES  |     | NULL    |                |
| password | varchar(32) | YES  |     | NULL    |                |
| status   | tinyint(1)  | YES  |     | 0       |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> explain SELECT count(*) FROM test_user WHERE status > 0;
+----+-------------+-----------+-------+---------------+----------------------------------+---------+------+---------+--------------------------+
| id | select_type | table     | type  | possible_keys | key                              | key_len | ref  | rows    | Extra                    |
+----+-------------+-----------+-------+---------------+----------------------------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | test_user | index | NULL          | index_union_name_password_status | 254     | NULL | 9702644 | Using where; Using index |
+----+-------------+-----------+-------+---------------+----------------------------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
  • 新增普通的Integer字段

同样的查询，主键是range(查询需要 0.09 sec)， 非主键是扫描全表的方式（(需要 3.45 sec)

mysql> EXPLAIN select * from test_user where id > 900000 and id < 1000000;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | test_user | range | PRIMARY       | PRIMARY | 8       | NULL | 197944 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from test_user where no > 900000 and no < 1000000;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | test_user | ALL  | NULL          | NULL | NULL    | NULL | 9710494 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql>
  • 普通字段添加索引
mysql> ALTER TABLE `test_user` ADD INDEX no_index(`no`);
Query OK, 0 rows affected (15.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index in test_user;
+-----------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_user |          0 | PRIMARY                          |            1 | id          | A         |     9710494 |     NULL | NULL   |      | BTREE      |         |               |
| test_user |          1 | index_name                       |            1 | username    | A         |     9710494 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password        |            1 | username    | A         |     9710494 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password        |            2 | password    | A         |     9710494 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password_status |            1 | username    | A         |     9710494 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password_status |            2 | password    | A         |     9710494 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | index_union_name_password_status |            3 | status      | A         |     9710494 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_user |          1 | no_index                         |            1 | no          | A         |     9710494 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

mysql>
  • 再次查询，会采用range方式查询
mysql> EXPLAIN select * from test_user where no > 900000 and no < 1000000;
+----+-------------+-----------+-------+---------------+----------+---------+------+--------+-----------------------+
| id | select_type | table     | type  | possible_keys | key      | key_len | ref  | rows   | Extra                 |
+----+-------------+-----------+-------+---------------+----------+---------+------+--------+-----------------------+
|  1 | SIMPLE      | test_user | range | no_index      | no_index | 5       | NULL | 196280 | Using index condition |
+----+-------------+-----------+-------+---------------+----------+---------+------+--------+-----------------------+
1 row in set (0.00 sec)

补充代码

ALTER TABLE test_user add COLUMN `no` int(11) DEFAULT 0;

CREATE DEFINER=`root`@`localhost` PROCEDURE `sample_data`.`update_no`()
BEGIN
    DECLARE i int;
    set i = 1;
    while i <= 10000000 do
        UPDATE test_user set no = i where id = i;
        set i = i + 1;
    end WHILE;
END

call update_no();
master-dragon
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 区间索引_MySQL索引优化（四）—— 范围查询优化
weixin_39737947的博客
01-25 2032
范围查询可以通过一个索引的单个或者多个索引值的区间来检索数据记录，也适用于符合索引。下面就区间是怎么样从where子句中被提取出来的作出详细解释。使用单值索引的范围查询对于单值索引来说索引值的区间能够很好地通过where子句中的相应条件很好的表示出来。单值索引的范围查询被定义为:对于BTree索引和Hash索引来讲，包含索引键和 =, <=>, IN(), IS NULL 或者 IS ...
MySQL索引
HoldonWithYourGoal的博客
06-26 1253
一、索引类型 MySQL索引是一种特殊的数据结构，InnoDB的索引是表空间的一部分。索引保存着涉及所有记录的引用指针。 1、普通索引（index） 2、唯一索引 主键索引（primary key） 唯一索引（unique） 3、联合索引 联合主键索引：primary key（id, name, age） 联合唯一索引：unique(id, name...
MySQL——索引
小志的博客
11-04 1187
目录一、mysql中如果没有索引如何查找数据1、方式一：在一个页中查找2、方式二：在多个页中查找二、mysql中索引的概念1、前提条件2、目录项记录3、如何区分普通用户记录和目录项记录4、普通用户记录和目录项的区别有哪些？三、mysql中B+树1、如何快速定位目录项2、上图中B+树的解释 一、mysql中如果没有索引如何查找数据 1、方式一：在一个页中查找 比如数据较少，都存储在一个页中。在查找记录时，是要根据搜索条件的不同分为两种情况的。 第一种情况：以主键列作为搜索条件 （1）、在页目录（Page
最完整的Explain总结，再也不用担心SQL优化了
weixin_50205273的博客
08-25 569
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中） CREATETABLE`film`( `id`int(11)NOTNULLAUTO_INCREMENT, `name`varchar(10)DEFAULTNULL, PRIMARYKEY(`id`), KEY`idx_name`(`name`) ...
Mysql时间范围查询走不走索引问题
yzx3105的博客
10-26 5503
因此，在进行范围查询时，比如>、< 、>=、
MySQL中的索引index（超详细）
MrWangisgoodboy的博客
04-19 4778
索引就是MySQL中可以高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足 特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构 上实现高级查找算法，这种数据结构就是索引。添加索引原则上来说可以大大的提高数据获取的效率。
MySQL范围查询的索引问题
weixin_40641900的博客
12-24 6143
首先声明，我使用的MySQL 8.0版本做的测试 1.当查询字段被索引覆盖时，会使用索引。 2.当查询字段未全部被索引覆盖时，like 查询%放前面不会走索引，% 放后面会走索引 3. 范围查询只有使用 ‘<>’ 或between 查询且非覆盖索引查询不走索引，其他如<，<=，>, >= 都是走索引的 ...
mysql之explain使用详解(分析索引)
09-09
在这里对explain的各个字段进行详细的分析，来帮助大家分析自己所写的sql是否最佳的使用了索引,需要的朋友可以参考下
Mysql实验之使用explain分析索引的走向
09-09
索引是mysql的必须要掌握的技能，同时也是提供mysql查询效率的手段。通过以下的一个实验可以理解？mysql的索引规则，同时也可以不断的来优化sql语句
mysql索引和explain的详解
12-14
B树是为了磁盘或其它存储设备设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。 多叉平衡。 B树和B+树的区别： B树和B+树的最大区别在于非叶子节点是否存储数据的问题 在...
MySQL的两种索引方法如何提高查询速度
大道的专栏
01-21 545
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 索引方法 Mysql的索引方法有两种，BTERR和HASH。 散列表（Hash Table）和B+Tree 都是一些常用的数据结构，再数据结构前篇中介绍过一些，关于散列表和树的数据结构，后续也会在《数据结构后篇》中总结一下。 Hash Hash索引如何提高查询速度 Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个Hash表(可以是数组结构)中。 那
mysql非聚集索引区间查询_MySQL聚集索引和非聚集索引
weixin_42131367的博客
01-28 277
MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类，理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式，第一类是拼音检索(前提是知道该汉字读音)，比如拼音为cheng的汉字排在拼音chang的汉字后面，根据拼音找到对应汉字的页码(因为按拼音排序，二分查找很快就能定位)，这就是我们通常所说的字典序；第二类是部首笔画检索，根据笔画找到对应汉字，查到汉字...
MySQL索引详解
TANGYUXIANG1的博客
10-14 2194
官方上面说索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引数据结构。
全网都在说一个错误的结论
weixin_57907028的博客
10-13 3000
前文我将 product_no 字段设置为了索引，这种二级索引只有一个字段。如果将多个字段组合成一个索引，那么这种二级索引就被称为联合索引。联合索引 ``(product_no, name)` 的 B+Tree 示意图如下：可以看到，联合索引的非叶子节点用两个字段的值作为 B+Tree 的索引值。**联合索引的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。**记住这句话，很重要！
MySql范围查找时，索引不生效问题原因
热门推荐
qq_25188255的博客
07-31 2万+
1 问题描述 本文对建立好的复合索引进行排序,并取记录中非索引字段，发现索引不生效，例如，有如下表，DDL语句为： CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` var...
mysql时间范围查询 索引无效
lppl010_的专栏
01-31 1万+
https://ask.csdn.net/questions/719039 对于range类型，选择性小于17%了，就会走全表扫描，而非range mysql mysql时间范围查询 索引无效 5C 死活不走带时间的索引 求大佬帮忙给个解决方案~ 编辑于：2018.12.29 16:59 0 分享 |评论0|收藏0|浏览199 ZoeLulur 声望： 0 4个回答 ...
mysql给数据量大的表添加索引的办法
weixin_34416649的博客
07-28 4525
有一个问题，一张表有3百万条记录，随着时间的增加，记录量会更多，此时查询速度很慢。在创建此表前没有未相应字段添加索引，所以此时需要为表添加索引。但是因为数据量大的原因，索引添加不成功，想了很多办法，终于在短时间内解决了。 　　办法如下： 　　1、进入mysql界面。mysql-uroot-hlocalhost-plovelivegm; 　　2、导出相应表的数据。...
mysql索引优化 explain
最新发布
05-26
在MySQL中，优化查询的一个重要手段就是使用索引。EXPLAIN是用来分析查询语句的工具，可以通过分析EXPLAIN输出结果来进行索引优化。下面是一些常用的优化技巧： 1. 索引覆盖：通过使用覆盖索引，可以避免MySQL访问...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值