17.1 用查询语句查看是否使用索引情况
mysql> explain select * from test where name='oldgirl'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL 从查看的结果看出，查询的时候没有走索引
key: NULL
key_len: NULL
ref: NULL
rows: 4 总结查询了 4 行
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(4) NOT NULL DEFAULT '0',
`age` tinyint(2) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`shouji` char(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
17.2 为该列创建索引，再用查询语句查看是否走了索引
mysql> alter table test add index index_name (name);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> explain select * from test where name='oldgirl'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: index_name 从下面结果看出语句查询的时候走了索引
key: index_name
key_len: 51
ref: const
rows: 1 总共查询了 1 行，效率更快
Extra: Using where
1 row in set (0.00 sec)
