必优博客

总结一下一些常用的MySQL索引及Profiling小知识，

一，查看某个表的索引

1. mysql > show indexes from [tablename] \G

1. Table: album
2. Non_unique: 0
3. Key_name: PRIMARY
4. Seq_in_index: 1
5. Column_name: album_id
6. Collation: A
7. Cardinality: 29
8. Sub_part: NULL
9. Packed: NULL
10. Null:
11. Index_type: BTREE
12. Comment:

二，强制索引

1. mysql > select * from [tablename] force index([indexname]) where [xxxx]

三，创建索引

1. mysql > create index [index_name] on [tablename] ([index]);

四，删除索引

1. mysql > drop index [index_name] on [tablename];

索引的好坏直接影响到MySQL数据库的执行性能，可结合Explain和Profiling工具进行检查。

Profiling使用方法如下：

1. mysql > set profiling = 1;

1. mysql > select * from [tablename]

1. mysql > show profiles;

1. mysql > show profile [cpu, block] io for query [xx];

掌握profiling检查MySQL的执行情况。