哪个网站可以卖自己的设计,建筑人才网一砖一瓦,洪梅镇仿做网站,python能写网页吗目录 1、背景2、环境3、访问类型【1】const【2】ref【3】ref_or_null【4】range【5】index【6】all 4、总结 1、背景
mysql通过查询条件查询到结果的过程就叫访问方法#xff0c;一条查询语句的访问方法有很多种#xff0c;接下来我们就来讲一下各种访问方法。
2、环境
创… 目录 1、背景2、环境3、访问类型【1】const【2】ref【3】ref_or_null【4】range【5】index【6】all 4、总结 1、背景
mysql通过查询条件查询到结果的过程就叫访问方法一条查询语句的访问方法有很多种接下来我们就来讲一下各种访问方法。
2、环境
创建表
mysql CREATE TABLE test2- (- id INT AUTO_INCREMENT PRIMARY KEY,- str1 VARCHAR(255),- str2 VARCHAR(255),- str3 CHAR(5),- str4 VARCHAR(255),- str5 CHAR(10),- INDEX idx_str1 (str1),- UNIQUE INDEX idx_str3 (str3),- INDEX idx_str4_str5 (str4, str5)- ) ENGINE InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)插入100条数据
mysql INSERT INTO test2 (str1, str2, str3, str4, str5) VALUES- (value1, data1, abc, value4_1, value5_1),- (value2, data2, def, value4_2, value5_2),- ...- (value99, data99, yz91, value4_99, value5_99),- (value100, data100, yz92, value4_100, value5_100);
Query OK, 100 rows affected (0.02 sec)
Records: 100 Duplicates: 0 Warnings: 03、访问类型
【1】const
通过主键索引或者唯一索引查询一条记录的方法就为const可以通过explain关键字来看查询语句的访问方式通过主键查询示例
mysql explain select * from test2 where id 3;
----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test2 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)type字段就是访问方式我们再看看通过唯一索引查询的示例
mysql explain select * from test2 where str3 abc;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test2 | NULL | const | idx_str3 | idx_str3 | 16 | const | 1 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)【2】ref
使用普通二级索引进行等值匹配时访问类型就为ref示例如下
mysql explain select * from test2 where str1 value7;
----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test2 | NULL | ref | idx_str1 | idx_str1 | 767 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)【3】ref_or_null
二级索引进行等值匹配时又想把值为NULL的查询出来这种查询类型就为ref_or_null先把上面插入的数据部分记录的str1字段改为NULLsql如下
mysql update test2 set str1 NULL where id in (3, 6, 8, 9, 34, 78, 89);
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0再看查询类型
mysql explain select * from test2 where str1 value7 or str1 null;
------------------------------------------------------------------------------------------------------------------
---------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra|
------------------------------------------------------------------------------------------------------------------
---------------
| 1 | SIMPLE | test2 | NULL | ref_or_null | idx_str1 | idx_str1 | 768 | const | 2 | 100.00 | Using i
ndex condition |
------------------------------------------------------------------------------------------------------------------
---------------
1 row in set, 1 warning (0.00 sec)【4】range
顾名思义范围查询就是range示例如下
mysql explain select * from test2 where id 2 and id 7;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test2 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 100.00 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)【5】index
使用组合索引中非最左边作为查询条件时并且查询的字段不需要回表这个时候就会将组合索引叶子节点全部扫描一遍这种查询方式就叫index示例如下
mysql explain select str4, str5 from test2 where str5 value5_15;
------------------------------------------------------------------------------------------------------------------
----------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra|
------------------------------------------------------------------------------------------------------------------
----------------
| 1 | SIMPLE | test2 | NULL | index | idx_str4_str5 | idx_str4_str5 | 799 | NULL | 100 | 10.00 | Using whe
re; Using index |
------------------------------------------------------------------------------------------------------------------
----------------
1 row in set, 1 warning (0.00 sec)【6】all
对主键索引所在的叶子节点进行全表扫描就叫all示例如下
mysql explain select * from test2;
-----------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | test2 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)4、总结
mysql中优化器会将我们的查询条件进行优化我们可以通过explain关键字来查看单表查询的访问方式。
