mysql优化

     项目增长很快，数据量越来越大，这几天也是够忙的，是时候再学一遍mysql的优化（之前也就瞟过几眼），写出高质量的sql语句，设计高性能索引，为以后数据量持续增大减坑。
     参考书籍：高性能mysql第三版。
     这部分内容会持续更新，对优化的理解需要经验的沉淀~~~

高性能索引

独立的列

    索引列不能是表达式的一部分，也不能是函数的参数。

//将无法用到索引列
mysql>SELECT id，name FROM table_test WHERE id + 1 = 3 ;
//正确表达应为索引列为独立列
mysql>SELECT id,name FROM table_test WHERE id = 2 ;

前缀索引

    当索引列过长，为节约内存与性能，取能够相对保证此索引长度的选择性接近全列索引的选择性。

//完整列的选择性
mysql>SELECT COUNT(DISTINCT 索引列) /count(*) FROM table_test
//前缀索引的选择性(n = 前缀长度)
mysql>SELECT COUNT(DISTINCT LEFT (索引列,n)) /count(*) FROM table_test

多列索引

     当出现服务器对多个索引做相交操作时（如多个and条件查询），需要创建多个列的索引，并遵循最左前缀的策略，即索引列以最左索引使用叠加。

//索引列，id，name，age
mysql>select * from table where id = 1 and name ='ygy' and age = 15; //使用索引列 id ，name ，age
mysql>select * from table where id = 1 and name ='ygy' ; //使用索引列 id ，name
mysql>select * from table where id = 1  and age = 15; //使用索引列id，未用到age，因为name未被使用。

索引扫描排序

    1.单表查询时，只有当索引的列顺序与orderby字句的顺序完全一致，并且所有列的排序方向必须一致，才会使用索引排序。
    2.多表关联查询时，只有当orderby字句引用的字段全部为第一张表时，才能使用索引排序。
    3.当索引最左前缀为指定值时，可以不遵循最左索引。

// 索引 id name age 
mysql>select * from table_test order by id , name ,age  //使用索引排序
mysql>select * from table_test order by name ,age  //未使用索引排序
mysql>select * from table_test where id = 4 order  by name ,age  //使用索引排序
mysql>select * from table_test where id = 4 order  by name ,address//未使用索引排序（address 不是索引列）

查询性能优化

优化数据访问

确认应用程序是否在检索大量超过需要的数据

    =>检查方式：
        1).减少查询不必要的记录（limit）
        2).减少不需要的列字段查询
        3).缓存相同查询数据

确认mysql服务器层是否在分析大量超过需要的数据

    =>检查方式：
        1).确认响应时间（服务时间、排队时间）
        2)扫描行数和返回的行数之比控制
        3)expalin检查扫描行数和访问类型

重构查询方式

切分查询

    采用分而治之，将大查询切分为小查询，每个查询功能完全一样，只完成一小部分。

int rows = 0 ;
do{
    rows = deleteByLimit(query);    //限制删除数量，减少锁表时间。
}while (rows > 0)

分解关联查询

    将关联查询分解为单表查询:
        1)单表查询缓存效率更高
        2)减少锁竞争
        3)减少冗余记录的查询（多表关联可能重复查询数据）