mysql-单表访问方法(访问类型)

最新推荐文章于 2023-05-17 20:06:37 发布
最新推荐文章于 2023-05-17 20:06:37 发布
阅读量189 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/power_to_go/article/details/108954154
版权

测试表

mysql> show create table single_table\G
*************************** 1. row ***************************
       Table: single_table
Create Table: CREATE TABLE `single_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)


mysql> show create table single_table2\G
*************************** 1. row ***************************
       Table: single_table2
Create Table: CREATE TABLE `single_table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=20003 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

访问方法

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system。

MyISAM 存储引擎
mysql> CREATE TABLE t(i int) Engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t VALUES(1);
Query OK, 1 row affected (0.01 sec)

# 只有一条记录，此时 storage engine 是 MyISAM
mysql> explain select * from t;
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 修改 storage engine
mysql> alter table t engine=InnoDB;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
Memory 引擎
# 修改为 Memory 引擎
mysql> alter table t engine=Memory;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from t;
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

const

等值查询之主键列
mysql> explain select * from single_table where id = 1453;
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
等值查询之 unique 列
mysql> explain select * from single_table where key2 = 1949;
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | const | idx_key2      | idx_key2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref

mysql> explain select * from single_table inner join single_table2 on single_table.id = single_table2.id;
+----+-------------+---------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                       | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
|  1 | SIMPLE      | single_table  | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                      |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | single_table2 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xiaohaizi.single_table.id |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref

搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref

mysql> explain select * from single_table where key1 = 'abc';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key1 列有二级索引。先在二级索引 idx_key1 查询满足 key1 = ‘abc’ 的 id 值，然后拿着 id 值去聚簇索引上查询。

ref_or_null

查询的某一列既有等值查询，又有 NULL 值查询。

mysql> explain select * from single_table where key1 = 'abc' or key1 is null;
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table        | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | single_table | NULL       | ref_or_null | idx_key1      | idx_key1 | 303     | const |    2 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index_merge

range

查询索引列的某个范围的值。

mysql> explain select * from single_table where key2 in (1368,1644) or (key2 >= 38 and key2 <=79);
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key2      | idx_key2 | 5       | NULL |   44 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index

select 中所有的列正好是某个二级索引使用的列。直接遍历二级索引的叶子节点得到符合条件的数据。不用回表查询聚簇索引。

mysql> explain select key_part1, key_part2, key_part3 from single_table where key_part2 = 'power_to_go';
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key_part | 909     | NULL | 9921 |    10.00 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

ALL

遍历整个聚簇索引。

mysql> explain select * from single_table;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9921 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Reference

  1. 单表访问方法
  2. explain-type-column
power_to_go
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql-connector-java-8.0.23.jar
12-18
MySQL Connector/J遵循Java Database Connectivity (JDBC) API规范，它是Java平台的标准接口，用于访问各种类型的数据库。JDBC驱动程序分为四种类型：Type 1、Type 2、Type 3和Type 4。MySQL Connector/J属于Type 4...
Mysql的访问类型type总结
上善亦若水的博客
01-11 1785
Type访问类型，效率低到高如下： all < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < ref < eq_ref < const < system 1. all 全表扫描，Mysql遍历全表来找到匹配的行，在大数据量...
mysql访问类型最好的_【干货满满】最全的MySQL性能指南（一）：选择最佳的数据类型...
weixin_39860064的博客
02-07 142
对于 MySQL 数据库来说，好的逻辑表和物理表的规划至关重要，我们需要根据查询语句来针对性地设计 Schema ，没有万能好用的 Schema。一个 denormalized 的 schema 可以在某些场景下加速语句查询，但是放在其他应用场景下就会适得其反。增加辅助表可以优化查询，但是表的维护成本就很高。MySQL 的实现原理决定了这一事实。《最全的MySQL性能指南》系列文章重点讨论数据库索...
MySQL单表访问的方法
qq_43352723的博客
10-15 207
单表访问的方法单表访问的方法一、访问方法的概念二、访问方法的方式2.1 const2.2 ref2.3 ref_or_null2.4 range2.5 index2.6 all2.7. 索引合并2.7.1 Intersection索引合并2.7.2 Union索引合并2.7.3 Sort-Union索引合并 单表访问的方法 先创建一个表： CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100)
Mysql中七种常见索引访问类型
weixin_44027397的博客
03-03 635
文章目录前言建表语句1.ALL2.index3.range4.ref5.eq_ref6.const7.system访问效率 前言 在少量数据的情况下,数据库有无索引，select语句是否走索引并不影响查询效率； 但是在现实世界中，数据是十分庞大的，这时候数据库有索引和select语句走索引对查询效率可能会有几十倍，几百倍甚至更多倍的提高！因此我们在写select语句时一定要通过explain的type来分析索引的访问类型，下面介绍七种种索引的访问类型。 建表语句 employee表 CREATE TA
一文带你了解MySQL之单表访问方法
Multis
05-17 1520
今天学写了MySQL针对单表的访问方式（access method）以及优化特性-索引合并（index merge）的知识点，下面我们来总结一下：const：通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。ref：搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。ref_or_null：使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为：ref_or_null。
mysql-connector-java-8.0.30连接器
10-30
MySQL Connector/J 8.0.30 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java Database Connectivity）规范的驱动程序，允许Java开发者在应用中无缝地访问MySQL数据库。MySQL Connector/J使得...
mysql-connector-java-8.0.15.jar
最新发布
01-06
MySQL Connector/J 8.0.15 是 MySQL 数据库与 Java 应用程序之间的关键桥梁，它是一个 JDBC（Java Database Connectivity）驱动程序，允许 Java 开发人员在 Java 应用程序中连接到 MySQL 数据库。MySQL Connector/J ...
mysql-connector-java-8.0.29.jar
05-30
JDBC提供了一组统一的API，使得程序员可以使用相同的代码来访问不同类型的数据库，提高了代码的可复用性和平台独立性。 MySQL Connector/J 8.0.29 版本是针对MySQL 8.0数据库的，它支持最新的特性和优化，例如：SSL...
mysql-5.5.40-winx64-msi
02-13
- 安全性是数据库管理的重要方面，建议定期更改root用户的密码，限制远程访问，并启用防火墙规则以保护MySQL服务。 - 对于生产环境，推荐使用更稳定和安全的更新版本，因为旧版本可能存在已知的安全漏洞。 6. **...
MySQL单表的访问方法和连接的原理
Minor的Java技术博客
07-04 412
MySQL的SQL查询语句对表的访问或者是查询可以有多种方式，每种方式执行的成本、花费的时间大不一样，MySQL定义了很多种表的访问方式或者叫访问类型来。通过主键索引或者唯一二级索引和常数值进行查询的时候因为目标结果是唯一确定的，MySQL认为这种查询效率十分高，成本消耗几乎忽略不计，这种访问方式定义为常数级访问const。通过二级索引和常数值进行查询的时候，扫描区间是一个单点区间，MySQL讲这种查询方式定义为ref访问类型。这种类型是ref的一种特殊存在，区别是它多了一些扫描Null值的扫描区间。如果查
MySQL之单表访问方法
cainiao1412的博客
03-17 1094
概念 一般我们查询想要的数据，只需要将特定的SQL规则告诉MySQL即可，具体MySQL如何访问找到数据是MySQL的事情，用户不需要关心， 不过像开车去目的地一样，走一环、走二环都可以到，无非是距离远近，时间长短的问题，MySQL也是一样，如何查询都可以，只要能查到正确的数据即可， MySQL将对数据不同的访问方式称为 access method (访问方法)，同一个SQL可以用不同的访问方法执行，不过不同的方法花费的成本差异也是巨大的，本篇则是介绍不同的访问方法，以及何时使用它们。 首先建立一张
mysql单表访问类别
xxsmirror的博客
07-25 46
const：通过主键索引直接定位数据页中的记录项 ref：二级索引与常数值条件比较 ref_or_null：二级索引与常数值条件比较，以及值为null的对象 range：范围区间查询 index：扫描全部二级索引 all：全表扫描 ...
mysql单表访问方法
weixin_44655181的博客
11-13 265
单表访问方法 单表访问时，根据索引列的匹配情况有如下几种访问方法。 1 const 根据主键或者唯一索引列直接与一个常数比较， 称为const 访问。 唯一索引列中的值不重复，和主键一样，所以使用二分法查询的时候几乎一样快。 例如： SELECT * FROM single_demo WHERE key1 = 'abc' 其中key1是主键 SELECT * FROM single_demo W...
一文搞懂 MySQL 单表查询的底层实现
ff00yo的博客
04-12 639
对于我们这些 MySQL的使用者来说，平时用的最多的就是查询功能。DBA时不时丢过来一些慢查询语句让优化，如果连查询是怎么执行的都不清楚还优化个毛线，所以是时候掌握真正的技术了。 MySQL有一个称为 查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的 执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按...
MySQL-单表访问方法
solo_jm的博客
07-22 209
条条大路通罗马 我们执行一条查询sql，MySQL有多种查询方式可以获取到结果，比如全表扫描、走聚簇索引，如果有二级索引可以先查二级索引再回表查聚簇索引，就跟我们要去一个地方一样，可以现在走路、骑车、驾车等方式，各种方法性能各不相同。 访问方法 const 假如我们有主键id，以及唯一索引列col1，执行以下sql： SELECT * FROM TABLE id = 1; SELECT * FROM TABLE col1 = 'a'; //先从二级索引树定位到一个主键id，在通过该主键id去聚簇索引中
连接MySQL的5种方式
热门推荐
SeeYouAgain的博客
10-11 6万+
我们从事项目开发，和数据库打交道是必不可少的环节。今天为大家分享Java中连接数据库（本文演示使用的是MySQL数据库,其他关系型数据库类似）常用的5种方式，读者可以自行比较优缺点，在实际项目开发或学习研究中采用其中一种即可！ 首先，需要导入相关jar包。这里是5种连接方式jar包的总和，按项目实际需要添加即可。截图如下： 一、直接代码连接 测试代码如下：...
mysql explain type 访问类型解读
weixin_44344597的博客
12-20 1295
MYSQL EXPLAIN type访问类型分析 type显示的是访问类型,是一个较为重要的指标,值从优到劣分别为: system > const > eq_ref > ref > range > index > all 一般来说,至少要保证查询级别到达了range级别,最好达到了ref级别. 下面我们对访问类型进行解读: 1.system 访问类型最高的,属于...
type const mysql_mysql的执行计划的type详解
weixin_39866874的博客
01-19 555
type 这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。执行效率排序#1.system#2.const * mysql能对查询的某部分进行优化并将其转化成一个常量。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。explain select * from (select * from film where...
mysql-monitor在windows安装方法
05-23
以下是在 Windows 上安装 mysql-monitor 的步骤： 1.下载 mysql-monitor 安装包，可从官方网站 https://dev.mysql.com/downloads/monitor/ 获取。 2.双击安装包，选择语言后点击“下一步”。 3.阅读许可协议并...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值