mysql 语句的性能优化

1.sql语句优化

1）最左前缀原则

定义：最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。

举例说明：上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)---这三者不从username开始，(username,last_login)---断层，少了password，都无法利用到索引。

尽量利用索引。 如果一个查询where子句中确实不需要用到索引列，那就用“补洞”的方法用上。

2)     Order by 优化

filesort优化算法.

在mysql version()<4.1之前，优化器采用的是filesort第一种优化算法，先提取键值和指针，排序后再去提取数据，前后要搜索数据两次，第一次若能使用索引则使用，第二次是随机读(当然不同引擎也不同)。mysql version()>=4.1,更新了一个新算法，就是在第一次读的时候也把selcet的列也读出来，然后在sort_buffer_size中排序(不够大则建临时表保存排序顺序)，这算法只需要一次读取数据。所以有这个广为人传的一个优化方法，那就是增大sort_buffer_size。Filesort第二种算法要用到更的空间，sort_buffer_size不够大反而会影响速度，所以mysql开发团队定了个变量max_length_for_sort_data，当算法中读出来的需要列的数据的大小超过该变量的值才使用，所以一般性能分析的时候会尝试把max_length_for_sort_data改小。

a.单独order by 用不了索引，考虑加where 或加limit用上索引。没limit查询是遍历表的，加了limit后，索引可以使用，看索引列。

b.对于where + orerby 类型的SQL语句，where满足最左前缀原则，且order by的列是where子句用到的索引列的子集。即(a,b,c)是索引，where满足最左前缀原则且order by中列a、b、c的任意组合

c.在比较常用的oder by的列和where中常用的列建立多列索引，这样优化起来的广度和扩张性都比较好，当然如果要考虑UNION、JOIN、COUNT、IN等进来就复杂很多了

3)     隔离列

隔离列是指查询语句中把索引列隔离出来，也就是说不能在语句中把索引列包含进函数或表达式中，如id+1=2、inet_aton('210.38.196.138')---ip转换成整数、convert(123,char(3))---数字转换成字符串、date函数等mysql内置的大多函数

非隔离列影响性能很大甚至是致命的

4)     OR、IN、UNION ALL，可以尝试用UNION ALL

a.or会遍历数据表就算有索引

b.对于in，如果是常量的话，可一大胆的用in。MySQL用IN效率不好，通常是指in中嵌套一个子查询，因为MySQL的查询重写可能会产生一个不好的执行计划，而如果in里面是常量的话，性能没有影响。

     c.UNION All 直接返回并集，可以避免去重的开销。之所说“尝试”用UNION All 替代 OR来优化sql语句，因为这不是一直能优化的了，这里只是作为一个方法去尝试。

5)     索引选择性

索引选择性是指不重复的索引值（也叫基数cardinality）与表中数据行数的比值，索引选择性=基数/数据行，基数可以通过“show index from 表名”查看。高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行，唯一索引的选择性最佳，值为1。

对于非唯一索引或者说要被创建索引的列的数据内容很长，可以选择其前缀作为索引。

如：select count(distinct(username))/count(*)  from abc，count(distinct(username))/count(*)就是索引选择性的值。假如username列数据很长，则可以通过select count(distinct(concat(first_name, left(last_name, N))/count(*)  from one;测试出接近1的索引选择性，其中N是索引的长度，穷举法去找出N的值，然后再建索引。

6)     重复或多余索引

 在满足功能需求的情况下建最少索引。对于INNODB引擎的索引来说，每次修改数据都要把主键索引，辅助索引中相应索引值修改，这可能会出现大量数据迁移，分页，以及碎片的出现。

-------------

Mysql 查询执行流程

1、查询缓存，判断sql语句是否完全匹配，再判断是否有权限，两个判断为假则到解析器解析语句，为真则提取数据结果返回给用户。

2、解析器解析。解析器先词法分析，语法分析，检查错误比如引号有没闭合等，然后生成解析树。

3、预处理。预处理解决解析器无法决解的语义，如检查表和列是否存在，别名是否有错，生成新的解析树。

4、优化器做大量的优化操作。

5、生成执行计划。

6、查询执行引擎，负责调度引擎获取相应数据

7、返回结果。