MySQL索引优化-show index

162 阅读4分钟

查询数据库索引有很多种方法,本文只讨论 show index语句,先给出基础语法:

SHOW [EXTENDED] {INDEX | INDEXES | KEYS} 
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

先创建一个表 t_user_order,包含一个主键索引,一个唯一索引,三个普通索引,且随机录入10480条数据,如下:

-- auto-generated definition
create table t_user_order
(
    id           int unsigned auto_increment comment '主键自增id'
        primary key,                    -- 主键索引
    code         varchar(64) not null comment '订单号',
    uid          varchar(64) not null comment '用户uid',
    pay_amount   int         not null comment '支付金额',
    pay_status   tinyint     not null comment '支付状态,1:支付中,2:支付成功,4:支付关闭',
    order_status tinyint     not null comment '订单状态,1:已创建,2:已收款,4:已完成,8:已关闭',
    constraint uniq_code
            unique (code)               -- 唯一索引
);

create index idx_order_status
    on t_user_order (order_status);     -- 普通索引

create index idx_pay_amount
    on t_user_order (pay_amount);       -- 普通索引

create index idx_uid
    on t_user_order (uid)
    comment '用户uid索引';               -- 普通索引

接下来执行下面任意一条SQL,结果都是相同的:

show index from t_user_order;
show index from t_user_order in test;
show index from test.t_user_order;

show keys from t_user_order;
show keys in t_user_order from test;
show keys from test.t_user_order;

show indexes from t_user_order;
show indexes from t_user_order in test;
show indexes from test.t_user_order;

输出:

TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
t_user_order0PRIMARY1idA10379BTREEYES
t_user_order0uniq_code1codeA10638BTREEYES
t_user_order1idx_order_status1order_statusA4BTREEYES
t_user_order1idx_pay_amount1pay_amountA358BTREEYES
t_user_order1idx_uid1uidA835BTREE用户uid索引YES

可选的 EXTENDED 关键字会输出包括 MySQL 内部的隐藏索引信息,举个栗子:

show extended index from t_user_order in test;

输出结果:

TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
t_user_order0PRIMARY1idA10379nullnullBTREEYESnull
t_user_order0PRIMARY2DB_TRX_IDAnullnullnullBTREEYESnull
t_user_order0PRIMARY3DB_ROLL_PTRAnullnullnullBTREEYESnull
t_user_order0PRIMARY4codeAnullnullnullBTREEYESnull
t_user_order0PRIMARY5uidAnullnullnullBTREEYESnull
t_user_order0PRIMARY6pay_amountAnullnullnullBTREEYESnull
t_user_order0PRIMARY7pay_statusAnullnullnullBTREEYESnull
t_user_order0PRIMARY8order_statusAnullnullnullBTREEYESnull
t_user_order0uniq_code1codeA10638nullnullBTREEYESnull
t_user_order0uniq_code2idAnullnullnullBTREEYESnull
t_user_order1idx_order_status1order_statusA4nullnullBTREEYESnull
t_user_order1idx_order_status2idAnullnullnullBTREEYESnull
t_user_order1idx_pay_amount1pay_amountA358nullnullBTREEYESnull
t_user_order1idx_pay_amount2idAnullnullnullBTREEYESnull
t_user_order1idx_uid1uidA835nullnullBTREE用户uid索引YESnull
t_user_order1idx_uid2idAnullnullnullBTREE用户uid索引YESnull

where子句则可以对输出的表格做进一步的过滤。

然后给出输出结果的表格头代表的含义:

  • Table
    表名
  • Non_unique
    如果索引不能包含重复内容,则为 0;如果可以,则为 1。
  • Key_name
    索引的名称。如果索引是主键,则名称总是 PRIMARY。
  • Seq_in_index
    索引中的列序列号,从 1 开始。
  • Column_name
    列名
  • Collation
    列在索引中的排序方式。其值可以是 A(升序)、D(降序)或 NULL(未排序)。
  • Cardinality
    估算的唯一值数量(基于采样统计),可以运行 ANALYZE TABLE 来更新。它的值是根据以整数形式存储的统计数据计算的,所以即使是小表,值也不一定精确。值越大,MySQL 在进行连接时使用索引的机会就越大。
  • Sub_part
    索引前缀。如果列只有部分索引,则为索引字符数;如果整个列都有索引,则为 NULL。
  • Packed
    索引压缩方式(如COMPRESSED行格式),默认NULL表示未压缩
  • Null
    索引是否包含空值。
  • Index_type
    索引结构类型(BTREEFULLTEXTHASHRTREE),
  • Comment
    索引扩展信息: disabled=索引被禁用 ;INVISIBLE=MySQL 8.0+不可见索引
  • Index_comment
    索引注释
  • Visible
    索引是否可见(详细可点击查看
  • Expression
    MySQL 8.0.13 及更高版本支持函数索引部分(详细可点击查看),如果使用了函数索引,Column_name 列为空,Expression 表示索引部分的表达式。

参考文档:dev.mysql.com/doc/refman/…