MySQL索引详细解析(2)-木庄网络博客

例子：

explain select * from t where name = 'name1';

我们可以先从查询类型type列开始查看，如果出现all关键字，后面的内容就都可以不用看了，代表全表扫描。—— const(主键索引或者唯一二级索引进行等值匹配的情况下)，ref(普通的?级索引列与常量进?等值匹配)，index(扫描全表索引的覆盖索引)
再看key列，看是否使用了索引， null代表没有使用索引。
然后看rows列，该列用来表示在SQL执行过程中被扫描的行数，该数值越大，意味着需要扫描的行数越多，相应的耗时就更长。
最后再看extra列,在这列中要观察是否有Using filesort或者Using temporary这样的关键字出现,这些是很影响数据库性能的。
MySQL5.7的执行计划中会默认添加filtered列 (MySQL5.6 使用 explain extended 也会增加此列)，它指返回结果的行占需要读到的行(rows 列的值)的百分比。需要注意的是， explain中输出的rows只是一个估算值。本例中该表进行了全表扫描。

5. 为什么官方建议用自增长主键作为索引

减少分裂和移动的频率：结合B+Tree的特点，自增主键是连续的，在插入过程中能尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。

6. 如何创建索引

1. 在执行 CREATE TABLE 时创建索引

CREATE TABLE user_index2 (
 id INT auto_increment PRIMARY KEY,
 first_name VARCHAR (16),
 last_name VARCHAR (16),
 id_card VARCHAR (18),
 information text,
 KEY name (first_name, last_name),
 FULLTEXT KEY (information),
 UNIQUE KEY (id_card)
);

2. 使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE 可用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中 table_name 是要增加索引的表名，column_list 指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名 index_name 可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

3. 使用CREATE INDEX命令创建。

CREATE INDEX index_name ON table_name (column_list);

7. 创建索引注意事项

选择性低的字段不要创建索引(例如，性别sex、状态status)。
很少查询的列不要创建索引(项目初期就要确定好)。
大数据类型字段不要创建索引。
尽量避免不要使用NULL,应该指定列为NOTNULL(在MySQL中,含有空值的列很难进行查询优化，它们会使得索引、索引的统计信息及比较运算更加复杂。可以使用空字符串代替空值)。

8. 使用索引一定可以提高查询性能嘛

通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的I* NSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能。