如何查看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)