MySQL如何查看SQL查询是否用到了索引(How does MySQL check whether indexes are used in SQL queries)

文章目录

  • 建表
  • 对比加索引前后的SQL查询情况
  • 解释Explain得到的结果
    3.1 type 反应查询语句的性能
    3.2 possible_keys: SQL查询时用到的索引。
    3.3 key 显示SQL实际决定查询结果使用的键(索引)。如果没有使用索引,值为NULL
    3.4 rows 显示MySQL认为它执行查询时必须检查的行数

索引 是提高MySQL查询性能的非常有用的一个工具,当我们对数据库中的某些字段建立了索引,那么怎么查看在执行的SQL查询的过程中是否用到了这些索引呢?
查询SQL语句的执行情况通常通过关键字 explain 来进行.

  • 建表
    例如,如下的数据表

use test;

drop table if exists ;
create table
(
int not null auto_increment,
varchar(50) not null,
varchar(20) not null,
varchar(100),
int default 0,
primary key ()
)Engine=InnoDB DEFAULT CHARSET=utf8;

student
student
id
name
number
address
age
id

insert into student (, , , )
values
(“马云”, “18000001”, “浙江省杭州市余杭区”, 55),
(“马化腾”, “18000002”, “广东省深圳市南山区”, 50),
(“张一鸣”, “18000003”, “北京市海淀区”, 38),
(“王兴”, “18000004”, “北京市朝阳区”, 40),
(“李彦宏”, “18000005”, “北京市海淀区”, 45),
(“程维”, “18000006”, “北京市海淀区”, 42),
(“雷军”, “18000007”, “北京市朝阳区”, 54),
(“刘备”, “18000008”, “四川省成都市青羊区”, 60),
(“诸葛亮”, “18000009”, “四川省成都市武侯区”, 43),
(“关羽”, “18000010”, “湖北省荆州市荆州区”, 58),
(“张飞”, “18000011”, “四川省阆中市”, 56),
(“曹操”, “18000012”, “河南省洛阳市老城区”, 63),
(“孙权”, “18000013”, “江苏省南京市建邺区”, 49),
(“李世民”, “18000014”, “陕西省西安市长安区”, 38),
(“李隆基”, “18000015”, “陕西省西安市长安区”, 28),
(“朱元璋”, “18000016”, “江苏省南京市玄武区”, 61),
(“朱棣”, “18000017”, “北京市东城区”, 39);

name
number
address
age
  • 对比加索引前后的SQL查询情况
    EXPLAIN 关键字查看SQL查询过程的情况:

SQL查询 address 为北京市的人:

explain select name, address from student where address like “北京市%”;
1

在 student 表上加索引:

alter table student add index (address(9));
1

为了加快索引,我们采用前缀索引,因为一个汉字在UTF-8编码下占3个字节,因此选择对address字段的前9位加索引。

再次执行上面的SQL查询语句:

  • 解释Explain得到的结果
    3.1 type 反应查询语句的性能
    我们主需要注意一个最重要的的 type 的信息很明显地体现出是否用到了索引:

type 结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说,得保证查询至少达到 range 级别,最好能达到 ref 级别,否则就可能出现性能问题。

3.2 possible_keys: SQL查询时用到的索引。
可以看到,没加索引时,possible_keys 的值为 NULL,加了索引后的值为 address,即用到了索引address(索引默认为(column_list)中的第一个列的名字).

3.3 key 显示SQL实际决定查询结果使用的键(索引)。如果没有使用索引,值为NULL
可以看到,没加索引时,key 的值为 NULL,加了索引后的值为 address,即决定查询结果用到了索引address

3.4 rows 显示MySQL认为它执行查询时必须检查的行数
可以看到,没加索引时,rows 的值为17,即数据表student中所有数据,说明没加索引时的SQL查询是全表扫描;

加了索引后,rows 的值为6,数据库表中address以“北京市”开头的一共也就6条,SQL在执行查询操作时,一共也检查了6行,不必进行全表扫描查询,可以很容易得出结论:加索引的SQL查询性能远高于不加索引的情况。

总结

通过在SQL查询语句前面添加关键字 explain 就可以分析SQL查询语句的性能了.
————————————————
版权声明:本文为CSDN博主「Kant101」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_27198345/article/details/116382587

————————

Article catalogue

  • Build table
  • Compare the SQL queries before and after indexing
  • Explain the results obtained by explain
    3.1 performance of type response query statement
    3.2 possible_ Keys: the index used in SQL query.
    The actual query results are displayed using the SQL key (3.3). If no index is used, the value is null
    3.4 rows displays the number of rows MySQL thinks it must check when executing a query

Index is a very useful tool to improve the performance of MySQL query. When we index some fields in the database, how can we check whether these indexes are used in the process of executing SQL query?
Query the execution of SQL statements usually through the keyword explain

  • Build table
    For example, the following data table

use test;

drop table if exists ;
create table
(
int not null auto_increment,
varchar(50) not null,
varchar(20) not null,
varchar(100),
int default 0,
primary key ()
)Engine=InnoDB DEFAULT CHARSET=utf8;

student
student
id
name
number
address
age
id

insert into student (, , )
values
(“Ma Yun”, “18000001”, “Yuhang District, Hangzhou City, Zhejiang Province”, 55),
(“Ma Huateng”, “18000002”, “Nanshan District, Shenzhen City, Guangdong Province”, 50),
(“Zhang Yiming”, “18000003”, “Haidian District, Beijing”, 38),
(“Wang Xing”, “18000004”, “Chaoyang District, Beijing”, 40),
(“Robin Lee”, “18000005”, “Haidian District, Beijing”, 45),
(“Cheng Wei”, “18000006”, “Haidian District, Beijing”, 42),
(“Lei Jun”, “18000007”, “Chaoyang District, Beijing”, 54),
(“Liu Bei”, “18000008”, “Qingyang District, Chengdu City, Sichuan Province”, 60),
(“ZHUGE Liang”, “18000009”, “Wuhou District, Chengdu City, Sichuan Province”, 43),
(“Guan Yu”, “18000010”, “Jingzhou District, Jingzhou City, Hubei Province”, 58),
(“Zhang Fei”, “18000011”, “Langzhong City, Sichuan Province”, 56),
(“Cao Cao”, “18000012”, “old urban area of Luoyang City, Henan Province”, 63),
(“Sun Quan”, “18000013”, “Jianye District, Nanjing City, Jiangsu Province”, 49),
(“Li Shimin”, “18000014”, “Chang’an District, Xi’an City, Shaanxi Province”, 38),
(“Li Longji”, “18000015”, “Chang’an District, Xi’an City, Shaanxi Province”, 28),
(“Zhu Yuanzhang”, “18000016”, “Xuanwu District, Nanjing City, Jiangsu Province”, 61),
(“Zhu Di”, “18000017”, “Dongcheng District, Beijing”, 39);

name
number
address
age
  • Compare the SQL queries before and after indexing
    Explain keyword to view the SQL query process:

People whose SQL query address is Beijing:

explain select name, address from student where address like “北京市%”;
1

Add an index to the student table:

alter table student add index (address(9));
1

In order to speed up the index, we use prefix index. Because a Chinese character occupies 3 bytes under UTF-8 coding, we choose to index the first 9 bits of the address field.

Execute the above SQL query statement again:

  • Explain the results obtained by explain
    3.1 performance of type response query statement
    We need to pay attention to one of the most important type information, which clearly shows whether the index is used:

The result value of type from good to bad is:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

Generally speaking, it is necessary to ensure that the query reaches at least the range level, preferably the ref level, otherwise performance problems may occur.

3.2 possible_ Keys: the index used in SQL query.
As you can see, when the index is not added, it is possible_ The value of keys is null, and the value after adding the index is address, that is, the index address is used (the index defaults to the name of the first column in column_list)

The actual query results are displayed using the SQL key (3.3). If no index is used, the value is null
It can be seen that when the index is not added, the value of key is null and the value after the index is added is address, which determines that the index address is used in the query result

3.4 rows displays the number of rows MySQL thinks it must check when executing a query
It can be seen that when the index is not added, the value of rows is 17, that is, all the data in the data table student, indicating that the SQL query without index is a full table scan;

After adding the index, the value of rows is 6, and there are only 6 addresses starting with “Beijing” in the database table. When SQL executes the query operation, a total of 6 rows are also checked. It is not necessary to scan and query the whole table. It is easy to conclude that the performance of SQL query with reference is much higher than that without reference.

summary

By adding the keyword explain in front of the SQL query statement, you can analyze the performance of the SQL query statement
————————————————
Copyright notice: This article is the original article of CSDN blogger “kant101”, which follows the CC 4.0 by-sa copyright agreement. Please attach the original source link and this notice for reprint.
Original link: https://blog.csdn.net/qq_27198345/article/details/116382587