explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了：
如：explain select * from role
查询参数为2个，但索引只有其中一个，索引会不会起效果？
DROP TABLE IF EXISTS user; CREATE TABLE user( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL, nick_name VARCHAR(32) NOT NULL, password VARCHAR(64) NOT NULL, email VARCHAR(50), last_pid VARCHAR(16) NOT NULL, role_id INT(11) NOT NULL, privileges TEXT NOT NULL, login_failed INT(11) DEFAULT 0, add_time TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00', modify_time TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00', last_login_ip varchar(50) not null default '127.0.0.1' ) ENGINE = InnoDB DEFAULT CHARSET = utf8;
执行
mysql> explain select id from user where name='超级管理员'; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec)
发现是没有索引的结果
执行：
mysql> ALTER TABLE `user` ADD INDEX IDX_NE (`name`);
mysql> explain select id from user where name='超级管理员'; +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | user | ref | IDX_NE | IDX_NE | 98 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
发现有了索引后的结果
mysql> explain select id from user where name='超级管理员' and last_pid='last_pid'; +----+-------------+-------+------+---------------+--------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | user | ref | IDX_NE | IDX_NE | 98 | const | 1 | Using index condition; Using where | +----+-------------+-------+------+---------------+--------+---------+-------+------+------------------------------------+ 1 row in set (0.00 sec)
发现有两个参数，也能被提速，
反序也有效果
mysql> explain select id from user where last_pid='last_pid' and name='超级管理员' ; +----+-------------+-------+------+---------------+--------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | user | ref | IDX_NE | IDX_NE | 98 | const | 1 | Using index condition; Using where | +----+-------------+-------+------+---------------+--------+---------+-------+------+------------------------------------+ 1 row in set (0.00 sec)
再加个索引：
mysql> ALTER TABLE `user` ADD INDEX IDX_NE_PD (`name`,`last_pid`); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select id from user where last_pid='last_pid' and name='超级管理员' ; +----+-------------+-------+------+------------------+--------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------+--------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | user | ref | IDX_NE,IDX_NE_PD | IDX_NE | 98 | const | 1 | Using index condition; Using where | +----+-------------+-------+------+------------------+--------+---------+-------+------+------------------------------------+ 1 row in set (0.00 sec)
mysql> explain select id from user where name='超级管理员' and last_pid='last_pid'; +----+-------------+-------+------+------------------+--------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------+--------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | user | ref | IDX_NE,IDX_NE_PD | IDX_NE | 98 | const | 1 | Using index condition; Using where | +----+-------------+-------+------+------------------+--------+---------+-------+------+------------------------------------+ 1 row in set (0.00 sec)
都有效果
