mysql优化方案

1、当where语句后面有多列条件时，为这多个列建立一个联合索引比为每个列建立独立索引要好的多。由于为每个列建立独立索引，mysql在查询时，其实是先每个列的索引去查询数据，然后把查询回来的数据做一次合并。而联合索引的话，mysql只需要用一个索引去查询数据，然后就能得到结果。

2、建立联合索引时，一定要注意各个列的顺序，mysql在使用联合索引时，会遵循“最左匹配”原则。（注意：最左匹配并不是说，sql的where子句里面的列，一定要按顺序排列）比如有A、B、C三个列，建立索引的顺序也是ABC。那么，要注意一下几点：
①、如果不是按照索引的最左列开始查找，就无法使用索引。比如查找B列等于某个值的数据，或者C列等于某个值的数据。
②、不能跳过索引中的列。比如要查找A列为某个值，C列为某个值的数据，由于C列跳过了B列，则查找C列的数据无法用到索引。
③、如果某一列用到范围查找，则其右边的列就不能再使用索引了。比如要查找A列的值大于3小于7，且B列的值为9的数据 ，由于A列使用了范围查找，则B列就不能使用索引了。解决方案是：如果知道A列的值只有少数几个。比如A列只有4、5、6三个值，则sql可以这样写： where A in (3,4,5) and B=9 。这样B列就可以继续使用索引了。然后，这种情况，其实是mysql优化器入了如下的事情：将一条sql语句转化为3条语句：where A=3 and B=9; where A=4 and B=9; where A=5 and B=9;分别执行后，将结果合并。所以，切不可为了使用索引而乱用in。

3、有时候想要建索引的那一列，数据比较大，比如是url之类的，这时候就可以使用前缀索引。因为关键字所占的空间越小，一个block所能容纳的关键字就越多，这样就可以建一个比较高阶的b+树，这样b+树的深度就越小，查询就越快。

4、建立索引时，尽量符合“三星系统”：索引将相关的记录放到一起获得一星；索引中的数据顺序和查找的排序顺序一致（即最左匹配），获得二星；索引中的列包含查询需要的全部列，获得三星（即覆盖索引）。

5、innodb采用聚类索引，就是把数据放在b+树的叶子节点中。innodb又把每个叶子节点成为节点页。每页大小为16k。当一页已经装满数据后，还要往该页中插入数据，这时候就会产生“页分裂”，所谓的页分裂跟b+树的节点分裂是一个道理，就是把一页中的数据分到两页中，这时候就会有比较大的io，就会导致性能下降。而且大量的页分裂，也会产生大量的碎片。

6、按索引扫描来排序
假设我们的sql语句有这样的：order by user_id。然后我们为user_id建立的索引，那么存储引擎就会按索引树的顺序来扫描数据，然后出来的数据就是正确的排序数据，这样就不需要再进行一次file_sort，从而优化了性能。
但这样做并不一定会有优化作用。因为存储引擎扫描索引树时，每找到一个索引就去table中获取数据，当这些数据并不是按索引的顺序存放时，那么这次读取数据就会产生随机io，这一操作造成的消耗是巨大的。这时候，可能还不如直接的顺序全表扫描，然后把数据加载到内存中后，再去排序。当然，如果可以使用覆盖索引，那就不需要再去table中获取数据，也就没有了随机io。这就大大提升了性能。
要使用索引扫描来排序，需要注意如下几点：
①、需要满足索引的最左匹配，应该说：where子句和order by子句的字段加起来，需要满足索引的最左匹配。
②、各个字段的排序方向需要一致；
③、order by子句中所有的字段都在索引中。
④、如果需要多张表，则order by子句中的字段需要全部来自第一张表。这里说的第一张表，指的是被优化器优化后的第一张表。

7、优化limit 10000,10这种的sql。最好就是先用子查询，使用覆盖索引找出需要的索引，然后再根据索引把数据返回。还有一种优化方法就是：表中增加一个自增id字段。以后每次查询时，都把上次获取的最大id传进来，然后只要筛选符合条件，and id>old_id limit 10；这里的数据就要按id排序。

8、多个连表查询，容易造成线程阻塞。原因是：比如查询A，需要a、b两个表的资源。查询B需要b、a两个表的资源。那么，当查询A查完a表，会把a表的相关行锁住，然后去查询b表；查询B查完b表，会把b表的相关行锁住，然后去查询a表那么，这时候如果A查询要扫描的b表数据被B查询锁住，B查询要扫描的a表数据被A查询锁住，这样就会造成阻塞。

9、使用in而不要使用or，因为mysql会把in中的值进行排序，然后通过二分查找的方法来确定表中的数据是否满足条件，这样子，判断每条数据的是否可取的时间复杂度为O(logn)。如果是or，则优化器不会对其进行排序，所以时间复杂度为O(n)。

10、STRAIGHT_JOIN的使用。在连表查询的时候，mysql优化器在优化后，会选择一个结果集较小的表为驱动表，这时候，就有可能改变连表的顺序。有时候这样做，会破坏一些我们原本想要的索引排序，加上STRAIGHT_JOIN之后，mysql优化器就不会改变sql连表的顺序，这样就可以使用索引。

11、如果可以，尽量不要使用in子查询（这里注意是子查询）。最好分两次来查询，先把in子查询中的数据查询出来，然后再组装sql进行查询。因为mysql在执行时，会逐行遍历数据，然后每次都执行以下in子句，判断数据是否符合，所以就造成执行了n次in子句。in和exists有点类似，要用哪个，要分情况的。用in时，外表使用索引，内表全部扫描，用exists时，内表使用索引，外表全部扫描。5.6版本及以后版本有改进in子查询的机制。

12、外键束缚会带来一定的开销，当数据量比较大的时候，使用外键束缚会产生瓶颈。