一、show index from table
该命令有助于诊断性能低下的查询,尤其是查询是否使用了可用的索引。下面介绍下 这个命令显示的结果列的含义:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
1️⃣Table:索引所在的表名
2️⃣Non_unique:0表示该索引是唯一索引,1表示该索引不是唯一索引
3️⃣Key_name:索引的名称
4️⃣Seq_in_index:索引列在索引中的位置,从1开始。对于组合索引来说,这个字段很重要。
5️⃣Column_name:索引列的名称
6️⃣Collation:索引列的值以什么方式存储在索引中。在MySQL中,A 表示有排序,B+树索引使用该方式;NULL 表示无序的,Heap索引使用该方式;
7️⃣Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。索引列所有值去重后的数量。该值除以该表的行数越接近1越好,如果非常小,则需要考虑是否可以删除该索引!
8️⃣Sub_part:数值 N 表示只对该列的前 N 个字符进行索引;NULL 表示索引整个列的值
9️⃣Packed:指示关键字是否被压缩,NULL 表示没有压缩
🔟Null:索引列是否可以为空
1️⃣1️⃣Index_type:索引类型,BTREE 表示B+树索引。一共有四种(BTREE, FULLTEXT, HASH, RTREE)。
1️⃣2️⃣Comment:注释
1️⃣3️⃣Index_comment:注释
二、analyze table t
本语句用于分析和存储表的关键字分布。在分析期间,使用一个读取锁定对表进行锁定。这对于MyISAM、BDB和InnoDB表有作用。对于MyISAM表,本语句与使用myisamchk -a
相当。
MySQL使用已存储的关键字分布来决定,当对除常数以外的对象执行联合时,表按什么顺序进行联合。本语句会返回一个含有以下列的表:
三、MySQL 中的索引
#添加主键索引
alter table mchopin add private key (id);
#添加唯一索引,唯一索引和主键索引唯一的区别是唯一索引可以为空
alter table mchopin add unique (id);
#添加全文索引
alter table mchopin add fulltext (id);
#添加普通索引
alter table mchopin add index idx_id (id);
#添加组合索引
alter table mchopin add index idx_id_book_author (id,book,author);
当需要删除一个索引时,使用语句:
#删除索引 idx_id
alter table mchopin drop index idx_id;
需要特别注意的是,当使用组合索引时,where 语句中最常使用的字段名,应该放在组合索引中的第一个。比如,以下组合索引的定义:
#添加组合索引
alter table novel add index idx_id_book_author (id,book,author);
基本就可以判断,id,book,author 作为 where 的查询条件的频率,从大到小应该是:id > book > author。还需要知道的一点是,触发组合索引的条件是向后匹配组合索引中的字段。还是看上面这个组合索引的例子,当查询语句的 where 条件包括下面任何一种情况,才会触发组合索引:
id
id, book
id, book, author
如果是以下任何一种情况,则不会触发组合索引:
book
author
book, author