exlpain，结合索引原则

最新推荐文章于 2024-09-12 12:42:46 发布

兔小牙牙

最新推荐文章于 2024-09-12 12:42:46 发布

阅读量196

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_25102767/article/details/103903177

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

exlpain，结合索引原则
建议联合使用：
show variables like “%pro%”;
set profiling = 1;
show profiles;
在这里插入图片描述
查索引和不查索引区别
注意，该值，断开连接就没有了。

mysql> explain select * from tableA;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tableA | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9945 |      100 | NULL  |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set

type:
const:层数查找，索引： streamnumber=4891
eq_reg:范围查找，索引，主键
ref：基于某个索引的查找
rang：索引的范围查找
index：索引的扫描
all：全表的扫描

extra列：
看到Using filesort。则需要优化：需要额外步骤来存储结果
看到 Using temporary ，则需要优化：需要创建临时表来存储结果

create index indx——xx on 表（列）

描述表结构：

desc tableA;

查看索引：

show index from tableA ;

create index info_all on tableA(streamnumber,callId);

mysql> explain select streamnumber  from tableA;
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table              | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tableA | NULL       | index | NULL          | PRIMARY | 4       | NULL | 9979 |      100 | Using index |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set

mysql> explain select streamnumber,appid   from tableA;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tableA | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9979 |      100 | NULL  |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set

mysql> explain select max(callDuration ) from tableA;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tableA | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9979 |      100 | NULL  |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------+
mysql> explain select max(streamnumber) from tableA;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set

6、不等于要慎用
！=和<> 使用索引失效
方法：select * 的* 换为索引列全拼，
int行，<>是折半查找，扫描半表。
mysql> select count(*) from tableA where streamnumber>12000;
±---------+
| count(*) |
±---------+
| 2932 |
±---------+
1 row in set

7、null not影响大
is not null 等会索引失效，使用覆盖索引挽救

8、like查询
%字段%
%字段
索引失效
字段%索引不失效

对应char，varchar类索引：

%字段
mysql>

explain select * from tableA where callId like '%
50096531330957326';

9、字符类型的加引号，否则索引失效
callId =150096531330957326;没有加引号

mysql> show profiles;
| 55 | 0.0005985 | select * from tableA where callId =‘150096531330957326’ |
| 56 | 0.00101525 | select * from tableA where callId =‘150096531330957326’ |
±---------±-----------±----------------------------------------------------------------------------------------+
15 rows in set

问题2： 150096531330957326太长，结果不准确

mysql> select count()
from tableA where callId =‘150096531330957326’;
±---------+
| count() |
±---------+
| 41 |
±---------+
1 row in set

1500965313
select count(*) from tableA where callId like ‘1500965313%’；

mysql> select count()
from tableA where callId = 150096531330957326;
±---------+
| count() |
±---------+
| 444 |
±---------+
1 row in set

explain select count(*) from tableA where appid=‘50002’ and streamnumber>10000 and callId like ‘1500965313%’；
10、or改为unino效率高，用or索引失效。除非or的都有索引
index_merge：索引合并

mysql>

explain select count(*)  from tableA where  callId ='150096531330957326' or
 apiid='50002';

mysql> select count() from tableA where apiid=‘50002’;
±---------+
| count() |
±---------+
| 4873 |
±---------+
1 row in set

mysql>

 select count(*)  from tableA where apiid='50002
' union select count(*)  from tableA where  callId ='150096531330957326';

±---------+
| count(*) |
±---------+
| 4873 |
| 41 |
±---------+
2 rows in set

兔小牙牙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录