创建索引
-- 给 t_test表中的name字段 添加name_index 索引
create index name_index on t_test(name);
查看索引
查看当前表中的索引,通过使用EXPLAIN 关键字来查询。
mysql> explain select * from t_test;
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_test | NULL | index | NULL | name_index | 768 | NULL | 9 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
索引查询type
EXPLAIN 结果中的type字段
- Tips:常见的扫描方式
- system:系统表,少量数据,往往不需要进行磁盘IO
- const:常量连接
- eq_ref:主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
- ref:非主键非唯一索引等值扫描
- range:范围扫描
- index:索引树扫描
- ALL:全表扫描(full table scan)
type扫描方式由快到慢