如何合理地建立 Mysql 索引

前言

索引基础知识
建立索引

alter table table_name add index (index_name);

删除索引

ALTER  TABLE  table_name   DROP  INDEX  index_name;

查看表中的索引

show index from table_name;

如何优化SQL语句？

SQL语句优化的思路：

1. 先看表的数据类型是否涉及得合理，有没有遵守选取数据类型越简单越小的原则；
2. 表中的碎片是否整理；
3. 表的统计信息是否收集，只有统计信息准确，执行计划才可以帮助我们优化SQL；
4. 查看执行计划，检查索引的使用情况，没有用到索引，考虑创建；
5. 在创建索引之前，还要查看说索引的选择性，来判断这个字段是否合适创建索引（尽量选择重复值少的字段）。
6. 创建索引之后，再查看一下执行计划，对比两次结果，看是否查询效率提高了。

合理创建索引的三个 “经常”：

1. 经常被查询的列（一般放在where 条件后面）；
2. 经常用于表连接的列；
3. 经常排序分组的列（order by 或者 group by 后面的字段）。

一个案例分析创建索引前后的效率区别

建立索引之前，需要确定建立该索引是否对查询效率有帮助，我们可以通过explain来查看某个查询语句的查询计划：

EXPLAIN select * from t where name = '45126-NAME'

• type，如果出现all关键字，代表是全表扫描
• key，如果是Null代表没有使用索引
• rows，表示在SQL执行过程中被扫描的行数，该数值越大，意味着耗时越大
• extra，如果出现Using filesort 或者 Using temporary关键字，将很影响数据库性能
• filtered，指返回结果的行占需要独到的行的百分比

然后，我们给t表的name字段添加索引：

alter table t add index (name)

再查看执行计划：

可以看到，添加索引后，rows值从59912变成了1，说明实际扫描行数变得很短，扫描时间相应的变短了。