mysql用explain查索引_[MySQL] 利用explain查看sql语句中使用的哪个索引

一时失言乱红尘 2022-10-25 04:59 201阅读 0赞

字段类型是:

\`enterpriseId\` int(10) unsigned DEFAULT NULL,

\`email\` char(255) NOT NULL DEFAULT '',

表的索引是:

UNIQUE KEY \`emailent\` (\`email\`,\`enterpriseId\`),

KEY \`edf\` (\`enterpriseId\`,\`departId\`,\`flag\`),

有这么两条sql语句,分别表现是:

explain select email from email where enterpriseId=23684 and (email like 'aaa%');+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

\+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

| 1 | SIMPLE | email | ref | emailent,edf | edf | 5 | const | 6 | Using where |

看到key\_len的长度是5 ,可以知道使用的是edf这个索引 , 因为edf索引中的enterpriseId是int类型4个字节 ,默认null 加1个字节,总共5个字节

也就是先使用enterpriseId查到索引,在索引中使用where过滤数据

explain select email from email where enterpriseId=23684 and (email like 'aaas%');+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

\+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

\+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

在like的时候比上面多了一个字符,这个时候的索引情况是key\_len是770,可以知道使用的是emailent这个索引,因为这个的索引长度是

255\*3+5=770 varchar是255个字符,utf8下是\*3, 加上int 5个字节

like两边都有%的情况,只会使用第一个条件的edf索引

mysql> explain select \* from email where enterpriseId=23684 and (email like '%shihanasas%');+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

\+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

| 1 | SIMPLE | email | ref | edf | edf | 5 | const | 6 | Using where |

\+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+