Mysql性能优化实战：索引的使用技巧和注意事项

典型案例分析

小猿正在开发一个网上商城系统,现在有一个需求需要查询订单以及关联的商品信息,因此小猿立刻写出了如下语句：

语句看着十分简单,可执行的时候却让小猿懵了,结果执行了1000多秒还是没有出结果,效率实在太低,为了找寻原因,小猿通过EXPLAIN来解析查询过程：

先解释上图几个关键字段的含义：

type：all 全表扫描

possible_key 可能用到的索引

key：真正用到的索引

rows：扫描的行数

从上图可以看出,这条语句执行采用的是全表扫描,没有走任何索引因此效率十分低.

接下来小猿分析了下现有的建表语句：

发现相关字段都已经加上了索引可是为何实际查询并没有找索引而是采用了全表扫描?

细心观察会发现订单表主键是 order_id + org_id ,订单明细表则是 org_id+order_id ,两边建立的索引顺序相反导致通过order_id来关联是无法启用索引进行查询的.

解决的方法有两种：1、重建索引 2、为语句加上org_id的关联条件,让现有索引生效.

修改后的语句如下：

耗时只要2毫秒,使用索引后速度提升相当惊人!

使用explain解析执行语句可以发现,已经没有之前的全表扫描了

其实上述的建表语句自己就存在着问题,我们可以通过SHOW INDEX命令来查看Cardinality字段值的高低来判断是否需要建立索引.

通过上图的分析可以看出org_id的的区分度是非常低的,建立索引的意义并不大,在实际应用中,Cardinality/n_row_in_table应尽可能的接近1,如果非常小,那用户需要考虑是否还有必要创建这个索引.

索引使用的注意事项：

1、创建多列索引,查询条件没有从索引的第一列开始比对无法使用索引,也就是文章开头出现的问题.

2、like查询时把%写在前面,索引无法进行比对.好比：like ‘%xxx%’.

3、列类型是字符串,WHERE条件中需要字符串用引号引起来.

4、不能再列上面进行计算.好比WHERE YEAR(dt) = 2017.

5、OR语句中的前后两个条件中,都要满足索引的使用情况语句才会用上索引.

6、否定的写法无法使用索引.如：<>、not in、not exists.

库表设计原则：

1、采用InnoDB存储引擎,禁用Memory引擎.

2、够用原则,能用TINYINT就不用INT.

3、有小数点的价格用DECIMAL.

4、主键字段尽量用数值型.

5、分库分表的垂直拆分与水平拆分.

索引设计原则：

1、选择区分度高的字段上创建.

2、过滤的字段上创建.

3、连接的字段上创建.

4、主键尽量用数值型.

5、必定要设置主键.