标题: 如何查看MYSQL索引是否使用及相关信息 [打印本页] 作者: 郑全 时间: 5 天前 标题: 如何查看MYSQL索引是否使用及相关信息 1.创建索引
CREATE INDEX idx_user_phone ON user(phone);
2.删除索引
DROP INDEX idx_taskid_state_id_phoneno ON call_autocall_data1026;
3.查看MYSQL有哪些索引
SHOW INDEX FROM call_autocall_data1026;
4.查看表定义
show create table qylink.call_autocall_data1026;
5.查看索引的创建时间
SELECT
INDEX_NAME,
CREATE_TIME AS 索引创建时间
FROM information_schema.STATISTICS
JOIN information_schema.TABLES
USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
TABLE_SCHEMA = 'qylink'
AND TABLE_NAME = 'call_autocall_data1026'
AND INDEX_NAME = 'idx_taskid_state_id';
6.强制走索引
SELECT
id AS id,
phoneno AS phoneno
FROM
call_autocall_data1026
FORCE INDEX (idx_taskid_state_id)
WHERE
id not in (-1) and
taskid = 37
AND state = 0
order by id asc
LIMIT
4000;
--
FORCE INDEX (idx_taskid_state_id)
FORCE INDEX (索引名字)
7.看SQL语句执行计划
explain
SELECT
id AS id,
phoneno AS phoneno
FROM
call_autocall_data1026
FORCE INDEX (call_autocall_data_state)
WHERE
id not in (-1) and
taskid = 37
AND state = 0
order by id asc
LIMIT
4000;
+----+-------------+------------------------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | call_autocall_data1026 | NULL | range | call_autocall_data_state | call_autocall_data_state | 12 | NULL | 2 | 10.00 | Using index condition; Using where |
+----+-------------+------------------------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)