“携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第5天，点击查看活动详情

MySQL查看索引

在MySQL中，经常会使用索引，可以使用SHOW INDEX查看索引。

1. 登录mysql

C:\Users>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2. 查看所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| adults             |
| book               |
| flms               |
| flms2              |
| hibernate          |
| hxsbm              |
| mysql              |
| news               |
| performance_schema |
| pfo                |
| salecar            |
| struts2            |
| test               |
| testes             |
+--------------------+
18 rows in set (0.08 sec)
mysql> use test;
Database changed

3. 查看数据库下的表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| car            |
| customer       |
| employee       |
| sale           |
| savepicture    |
+----------------+
5 rows in set (0.01 sec)

4. 查看sale表的索引

mysql> show index from sale\G;
*************************** 1. row ***************************
        Table: sale
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

结尾的\G需要注意：

在MySQL的sql语句后加上`\G`，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即**将查到的结构旋转90度变成纵向**

不加\G，查询结果成横向：

mysql> show index from sale;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sale  |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

而\g等价于;

mysql> show index from sale\g
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sale  |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

查看索引的语句

show index from <表名>[ from <数据库名> ]

主要参数说明

参数	说明
Table	表示创建索引的数据表名
Non_unique	表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0
Key_name	表示索引的名称
Seq_in_index	表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序
Column_name	表示定义索引的列字段
Collation	表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类
Sub_part	表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL
Packed	指示关键字如何被压缩。若没有被压缩，值为 NULL
Null	用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO
Index_type	显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）
Comment	显示备注