MYSQL优化学习总结

mysql学习小结---索引的使用及优化

 

1. 索引那些事

　　1.1 复合索引

　　复合索引是指：包含一个或者多个列的索引。但复合索引的触发是有条件的。

　　假设我们现在有一个复合索引a，a中包含了三个列（id，name，sex）

　　对于a索引的使用会出现多种情况，当使用id或者id,name或者id,name,sex时复合索引会被触发。

　　但是，当时用到name或者sex或者name,sex时a复合索引是不会被触发的。因为索引会遵从索引的最左前缀原则。 

　　也就是说，在使用复合索引的时候必须包含复合索引中的第一个列，且第一个列在首位复合索引才会被触发。

　　当然也存在特殊情况，当查询条件使用到了id,name的时候也可能只有id列起作用。

 

　　1.2 索引存在，但不使用索引的情况

　　如果mysql觉得使用索引比全表扫描更慢更慢则不会使用索引（例如：要查询某个字段中大于1小于100的值时）。

　　在使用OR做判断条件时，只有OR中所有的列都添加了索引，索引才会生效。如果OR条件添加了复合索引，但是列值不是复合索引的第一部分同样也不会使用（最左前缀原则）。

　　在建立索引后使用like进行检索信息的时候一定要注意%的放置位置。当%被放置在第一位时就会导致索引失效，反之索引生效。

　　注意where后比对条件的数据类型要一致，例如name为字符串类型，name=123会导致索引失效，name='123'则会生效。

 

 　　下面将详细介绍索引的触发条件及各语句的优化。

 

2. 索引在各语句及关键字上的使用

　　2.1 优化insert语句

　　若在使用insert语句一次需要插入多行数据时，尽量在一条语句内完成，例如：insert into test values(1,2),(2,3),(3,4)

　　但是，需要注意sql语句的长度。可以在max_allowed_packe中修改，默认大小是1M。

 

　　2.2 优化group by语句

　　在使用group by分组的时候sql是会自动排序的这和order by排序类似，如果不想使用排序可以在group by后加order by null

　　例如：select * from test group by id order by null

　　添加order by null 后会减少分组查询时排序所需要的时间。

　　

　　2.3 优化order by语句

　　如果order by前面有where子句，并且where后的列和order by的列合起来需要达到复合索引的最左前缀原则，且过滤性条件为常量（此处的常量是指=而不是<或者>这种范围性查询）的时候才会触发索引。

　　order by中ASC、DESC要一致才会使用索引。

　　order by的顺序和索引顺序相同（最左前缀原则）

 

　　2.4 子查询

　　在sql中尽量避免子查询的使用，子查询虽然写起来很简答，可以简化很多业务逻辑，但是子查询效率是很低的。

　　可以使用join连接的方式来替代子查询。因为join连接的查询方式mysql不需要在内存中创建临时表来完成子查询中逻辑上的需要两个步骤的查询工作。

　　所以join连接的查询方式是要比子查询的效率高的。

 

　　2.5 OR条件

　　OR条件前后的每个列都必须有索引才会使用到索引。

 

 

3. sql提示hint

　　hint语句是sql优化中很有用的一种优化方式，因为它是可以根据需求来控制索引的使用情况的。

　　hint语句将强制mysql生成一个临时的结果集，当临时结果集生成后会释放锁资源。

 

　　3.1 USE INDEX

　　hint中的USE INDEX语句是给mysql提供希望参考的索引，这样sql在执行的时候就不会考虑使用其它的索引。

　　例如：select * from test use index (索引名) where id = 2

　　

　　3.2 IGNORE INDEX

　　hint中的IGNORE INDEX语句是让mysql值执行过程中忽略一个或者多个索引。

　　例如：select * from test ignore index (索引名) where id = 2

 

　　3.3 FORCE INDEX

　　hint中的FORCE INDEX语句是强制让mysql使用某个索引。

　　这个语句是指某些情况下即便使用索引，即便它的效率不是很高，但仍然会强制sql去使用这些索引。

　　

 　　如果刨除业务上的需要当我们在考虑是否为列创建或者修改索引时，我们可以使用show index from table1语句来看返回结果中的Cardinality列来决定后续的操作，该字段可以很直观的展示出表中该列值的不重复记录的数据量（这个值是预估的）。我们可以根据这个来判定列值选择性的高低，选择性越高代表着索引的效果越好。个人猜测这个和查找算法有关系，当有索引时我们采用的是二分法，无索引时采用的是顺序查找。再加上索引的后期维护及其它多方面的考虑对于选择性低的列我们还是不创建索引的好。

 

本文为茴香饺子博客的原创，欢迎转载，转载请注明出处　　

茴香饺子博客http://www.cnblogs.com/hxjz/