如何查看sql查询是否⽤到索引（mysql）
问题发现
我认为⼀条很简单的SQL然后跑了很久，明明我已经都建⽴相应的索引，逻辑也不需要优化。
SELECT a.custid, b.score, b.xcreditscore, b.lrscore
FROM (
SELECT DISTINCT custid
FROM sync.`credit_apply`
WHERE SUBSTR(createtime, 1, 10) >='2019-12-15'
AND rejectrule ='xxxx'
) a
LEFT JOIN (
SELECT*
FROM sync.`credit_creditchannel`
) b
ON a.custid = b.custid;
查看索引状态：
credit_apply表
mysql> show index from sync.`credit_apply`;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null| Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------
-----+---------+---------------+
| credit_apply |0|PRIMARY|1| applyId    | A        |1468496|NULL|NULL|| BTREE      |||
| credit_apply |1| index2  |1| custId      | A        |666338|NULL|NULL|| BTREE      |||
| credit_apply |1| index2  |2| createTime  | A        |1518231|NULL|NULL|| BTREE      |||
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
或者
CREATE TABLE `credit_apply` (
`applyId` bigint(20) NOT NULL AUTO_INCREMENT,
`custId` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`ruleVersion` int(11) NOT NULL DEFAULT'1',
`rejectRule` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT'DP0000',
`status` tinyint(4) NOT NULL DEFAULT'0',
`extra` text COLLATE utf8mb4_unicode_ci,
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`mobile` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT'',
PRIMARY KEY (`applyId`) USING BTREE,
KEY `index2` (`custId`,`createTime`)
) ENGINE=InnoDB AUTO_INCREMENT=1567035DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
sync.`credit_creditchannel`表
mysql> show index from sync.`credit_creditchannel` ;
+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table| Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null| Index_type | Comment | Index_comment | +----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| credit_creditchannel |0|PRIMARY|1| recId      | A        |450671|NULL|NULL|| BTREE      |||
| credit_creditchannel |1| nationalId_custid          |1| nationalId  | A        |450770|NULL|NULL|| BTREE      |||
| credit_creditchannel |1| nationalId_custid          |2| custId      | A        |450770|NULL|NULL| YES  | BTREE      |||
| credit_creditchannel |1| credit_creditchannel_custId |1| custId      | A        |450770|10|NULL| YES  | BTREE      |||
+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+---------
-+--------+------+------------+---------+---------------+
或者
CREATE TABLE `credit_creditchannel` (
`recId` bigint(20) NOT NULL AUTO_INCREMENT,
`nationalId` varchar(128) NOT NULL DEFAULT'',
`identityType` varchar(3) NOT NULL DEFAULT'',
`brief` mediumtext,
`score` decimal(10,4) NOT NULL DEFAULT'0.0000',
`npaCode` varchar(128) NOT NULL DEFAULT'',
`basic` mediumtext,
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`request` mediumtext,
`custId` varchar(128) DEFAULT'',
`xcreditScore` decimal(10,4) DEFAULT'0.0000',
`queryTime` varchar(24) DEFAULT'',
`lrScore` decimal(10,4) DEFAULT'0.0000',
PRIMARY KEY (`recId`) USING BTREE,
KEY `nationalId_custid` (`nationalId`,`custId`),
KEY `credit_creditchannel_custId` (`custId`(10))
) ENGINE=InnoDB AUTO_INCREMENT=586557DEFAULT CHARSET=utf8
我们都可以看到相应的索引。以现在简单的sql逻辑理论上⾛custid这个索引就好了
解释函数explain
mysql> explain SELECT a.custid, b.score, b.xcreditscore, b.lrscore FROM(
SELECT DISTINCT custid FROM sync.`credit_apply` WHERE SUBSTR(createtime, 1, 10) >='2019-12-15'AND rejectrule ='xxx') a
LEFT JOIN
(select*from sync.`credit_creditchannel`) b
ON a.custid = b.custid;
+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+
| id | select_type |table| partitions | type  | possible_keys |key| key_len | ref  | rows    | filtered | Extra                                              |
+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+
|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|158107|100.00|NULL|
|1|PRIMARY| credit_creditchannel |NULL|ALL|NULL|NULL|NULL|NULL|450770|100.00| Using where; Using join buffer (Block Nested Loop) |
|2| DERIVED    | credit_apply        |NULL|index| index2        | index2 |518|NULL|1581075|10.00| Using where|
+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+
3 rows in set (0.06 sec)
如何去看我们的SQL是否⾛索引？
我们只需要注意⼀个最重要的type 的信息很明显的提现是否⽤到索引：
type结果
type结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
⼀般来说，得保证查询⾄少达到range级别，最好能达到ref，否则就可能会出现性能问题。
possible_keys：sql所⽤到的索引
key：显⽰MySQL实际决定使⽤的键（索引）。如果没有选择索引，键是NULL
rows: 显⽰MySQL认为它执⾏查询时必须检查的⾏数。
分析：
我们的credit_creditchannel是ALL，⽽possible_keys是NULL索引在查询该表的时候并没有⽤到索引怪不得这么慢！！！！！！！！！
分析和搜索解决办法
换着法的改sql也没⽤；换着问⼤神也没⽤；各种搜索引擎搜才总算有点思路。
索引⽤不上的原因可能是字符集和排序规则不相同。
于是看了了两张表的字符集和两张表这个字段的字符集以及排序规则:
修改数据库和表的字符集
alter database sync default character set utf8mb4;//修改数据库的字符集
alter dit_creditchannel default character set utf8mb4;//修改表的字符集
修改表排序规则
alter table sync.`credit_creditchannel` convert to character set utf8mb4 COLLATE utf8mb4_unicode_ci;
由于数据库中的数据表和表字段的字符集和排序规则不统⼀，批量修改脚本如下：
1. 修改指定数据库中所有varchar类型的表字段的字符集为ut8mb4，并将排序规则修改为utf8_unicode_ci
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', CASE WHEN IS_NULLABLE ='NO'THEN' NOT NULL'
ELSE''
END, ';')
FROM information_schema.COLUMNS
WHERE (TABLE_SCHEMA ='databaseName'
AND DATA_TYPE ='varchar'
AND (CHARACTER_SET_NAME !='utf8mb4'
OR COLLATION_NAME !='utf8mb4_unicode_ci'));
2. 修改指定数据库中所有数据表的字符集为UTF8，并将排序规则修改为utf8_general_ci　
SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET  utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA ='sync_rs'
explain 查看是否⽤到了索引
mysql> explain SELECT a.custid, b.score, b.xcreditscore, b.lrscore FROM(
SELECT DISTINCT custid FROM sync.`credit_apply` WHERE SUBSTR(createtime, 1, 10) >='2019-12-15'AND rejectrule ='xxx') a
LEFT JOIN
(select*from sync.`credit_creditchannel`) b
ON a.custid = b.custid;
+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+
| id | select_type |table| partitions | type  | possible_keys              |key| key_len | ref      | rows    | filtered | Extra      |
+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+
|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|146864|100.00|NULL|
|1|PRIMARY| credit_creditchannel |NULL| ref  | credit_creditchannel_custId | credit_creditchannel_custId |43| a.custid |1|100.00| Using where|
|2| DERIVED    | credit_apply        |NULL|index| index2                      | index2                      |518|NULL|1468644|10.00| Using where|
+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+
就是这样！！！！
补充⼤全：
可以看到结果中包含10列信息，分别为
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
对应的简单描述如下：
id: select查询的序列号，包含⼀组数字，表⽰查询中执⾏select⼦句或操作表的顺序===id如果相同，可以认为是⼀组，从上往下顺序执⾏；在所有组中，id值越⼤，优先级越⾼，越先执⾏
select_type: 表⽰查询的类型。⽤于区别普通查询、联合查询、⼦查询等的复杂查询。
table: 输出结果集的表显⽰这⼀步所访问数据库中表名称（显⽰这⼀⾏的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上⾯的e，d，也可能是第⼏步执⾏的结果的简称
partitions：匹配的分区
type：对表访问⽅式，表⽰MySQL在表中到所需⾏的⽅式，⼜称“访问类型”。
possible_keys：表⽰查询时，可能使⽤的索引
key：表⽰实际使⽤的索引
key_len：索引字段的长度
ref：列与索引的⽐较
rows：扫描出的⾏数(估算的⾏数)
filtered：按表条件过滤的⾏百分⽐
Extra：执⾏情况的描述和说明
挑选⼀些重要信息详细说明：
select_type
SIMPLE 简单的select查询，查询中不包含⼦查询或者UNION
PRIMARY 查询中若包含任何复杂的⼦部分，最外层查询则被标记为PRIMARY
SUBQUERY 在SELECT或WHERE列表中包含了⼦查询
DERIVED 在FROM列表中包含的⼦查询被标记为DERIVED（衍⽣），MySQL会递归执⾏这些⼦查询，把结果放在临时表中
UNION 若第⼆个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM⼦句的⼦查询中，外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT
type
mysql到数据⾏的⽅式，效率排名
NULL > system > const > eq_ref > ref > range > index > all
***⼀般来说，得保证查询⾄少达到range级别，最好能达到ref。
1. system 表只有⼀⾏记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
2. const 通过索引⼀次就到了，const⽤于⽐较primary key 和 unique key，因为只匹配⼀⾏数据，所以很快。如果将主键置于where列表中，mysql就能将该查询
转换为⼀个常量
3. eq_ref 唯⼀性索引扫描，对于每个索引键，表中只有⼀条记录与之匹配。常见于主键索引和唯⼀索引区别于const eq_ref⽤于联表查询的情况
4. ref ⾮唯⼀索引扫描，返回匹配某个单独值的所有⾏，本质上也是⼀种索引访问，它返回所有匹配某个单独值的⾏，然⽽，他可能会到多个符合条件的⾏，所以
他应该属于查和扫描的混合体
5. range 只检索给定范围的⾏，使⽤⼀个索引来选择⾏，⼀般是在where中出现between、<、>、in等查询，范围扫描好于全表扫描，因为他只需要开始于索引的某
⼀点，⽽结束于另⼀点，不⽤扫描全部索引
6. index Full Index Scan，Index与All区别为index类型只遍历索引树。通常⽐All快，因为索引⽂件通常⽐数据⽂件⼩。也就是说，虽然all和index都是读全表，但是
index是从索引中读取的，⽽all是从硬盘读取的
7. ALL Full Table Scan，将遍历全表以到匹配的⾏
possible_keys
指出mysql能使⽤哪个索引在表中到记录，查询涉及到的字段若存在索引，则该索引被列出，但不⼀定被查询使⽤（该查询可以利⽤的索引，如果没有任何索引显⽰null）
实际使⽤的索引，如果为NULL，则没有使⽤索引。（可能原因包括没有建⽴索引或索引失效）
查询中若使⽤了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中 possible_keys为null
key
key列显⽰mysql实际决定使⽤的索引，必然包含在possible_keys中。如果没有选择索引，键是NULL。想要强制使⽤或者忽视possible_keys列中的索引，在查询时指定FORCE INDEX、USE INDEX或者IGNORE index
key_len
表⽰索引中使⽤的字节数，可通过该列计算查询中使⽤的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显⽰的值为索引字段的最⼤可能长度，并⾮实际使⽤长度，即key_len是根据表定义计算⽽得，不是通过表内检索出的。
ref
显⽰索引的那⼀列被使⽤了，如果可能的话，最好是⼀个常数。哪些列或常量被⽤于查索引列上的值。
rows
根据表统计信息及索引选⽤情况，⼤致估算出到所需的记录所需要读取的⾏数，也就是说，⽤的越少越好
extra
包含不适合在其他列中显式但⼗分重要的额外信息
Using Index：表⽰相应的select操作中使⽤了覆盖索引（Covering Index），避免访问了表的数据⾏，效率不错。如果同时出现using where，表明索引被⽤来执⾏索引键值的查；如果没有同时出现using where，表明索引⽤来读取数据⽽⾮执⾏查动作。
Using where：不⽤读取表中所有信息，仅通过索引就可以获取所需数据，这发⽣在对表的全部的请求列都是同⼀个索引的部分的时候，表⽰mysql服务器将在存储引擎检索⾏后再进⾏过滤
查看mysql索引Using temporary：表⽰MySQL需要使⽤临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，⽽且⽆法利⽤索引完成的排序操作称为“⽂件排序”
Using join buffer：表明使⽤了连接缓存,⽐如说在查询的时候，多表join的次数⾮常多，那么将配置⽂件中的缓冲区的join buffer调⼤⼀些。
Impossible where：where⼦句的值总是false，不能⽤来获取任何元组
Select tables optimized away：这个值意味着仅通过使⽤索引，优化器可能仅从聚合函数结果中返回⼀⾏
No tables used：Query语句中使⽤from dual 或不含任何from⼦句
以上两种信息表⽰mysql⽆法使⽤索引
1. using filesort ：表⽰mysql会对结果使⽤⼀个外部索引排序，⽽不是从表⾥按索引次序读到相关内容，可能在内存或磁盘上排序。mysql中⽆法利⽤索引完成的操作称
为⽂件排序
2. using temporary: 使⽤了⽤临时表保存中间结果，MySQL在对查询结果排序时使⽤临时表。常见于排序order by和分组查询group by。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。