性能优化之mysql索引优化

sql及索引优化

如何通过慢查询日志发现有问题的sql？

1. 查询次数多且每次查询占用时间长的sql
   通常为pt-query-digest分析的前几个查询

2. IO大的sql
   注意pt-query-digest分析中的rows examine项

3. 未命中索引的sql
   注意pt-query-digest分析中rows examine 和 rows send的对比

 

 

 

磁盘IO与预读：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间，旋转时间，传输时间三个部分。虽然平常理论上来说没什么压力，

但是数据库如果动辄十万百万乃至千万级数据，显然是个灾难。

 

了解下mysql的B+树:一种为了减少IO操作快速搜索到数据的数据结构，如下图：

 

    说明：

        蓝色部分磁盘块

        黄色部分指针

        深蓝色目标数据块
    分析一下查找的过程：

    如果要查询数据为29的值：

    1.将磁盘一加入到内存中此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针

    2.通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO

    3. 29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询

    总结：总计三次IO，真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，

每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

解释下什么叫二分法：其实就是一种通过不断排除不可能的东西，来最终找到需要的东西的一种方法，所以可以理解成排除法。之所以叫二分，是因为

每次排除都把所有的情况分为“可能”和不可能两种，然后抛弃所有“不可能”的情况。

  

优化sql实际上主要目的是为了减少IO操作：
1.如果想在mysql配置方面减少IO的操作：尽可能使用缓存，减少读写对数据库的随机IO的请求，同时减少写的随机IO的随时发生，利用各种buffer去缓存。

2.创建索引：合理的索引是为了少量IO操作达到数据的获取。

  

建立索引的几大原则：
1.在where 从句，group by从句，on从句中出现的列

2.最左前缀匹配，非常重要的原则，mysql会一直向左匹配直到遇到范围查询（>,<,between,like）就停止匹配
eg:a=1 and b=2 and c=3 and d=4   索引  add index (a,b,c,d)  这样创建d是用不到索引的
eg:a=1 and b=2 and c=3 and d=4   索引  add index(a,b,d,c) 这样创建则都可以用到，a,b,d的顺序可以任意调整

3.离散度大的列放到联合索引的前面 

    计算离散度的公式是count(distinct col)/count(*),表示字段不重复的比例，比例越大我们扫描的记录数越少。

    select * from payment where staff_id=2 and customer_id=584;

    是index(sftaff_id,customer_id)好？还是index（customer_id,staff_id）？
    由于customer_id的离散度更大，所以应该使用index（customer_id,staff_id)

4.索引列不能参与计算，保持列干净
    索引列不能参与计算，保持列干净，在where语句中索引字段不要使用函数，进行检索的时候需要把所有元素都应用函数才能比较，先人成本太大。

5.索引字段越小越好
    使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。

6.尽量的扩展索引，不要新建索引。
    

说了这么多索引的优点但是索引也是有负面的：

1. 每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能

   2.在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，所花的时间越长。

什么叫做好的索引：

1. 查询频繁（业务逻辑决定）

2. 区分度高

3. 长度小（与区分度保持一个平衡就是一个最优的效果）

4. 尽量能覆盖常用查询字段（并不表示所有字段都建立索引）

sql及索引优化

如何通过慢查询日志发现有问题的sql？

1. 查询次数多且每次查询占用时间长的sql
   通常为pt-query-digest分析的前几个查询

2. IO大的sql
   注意pt-query-digest分析中的rows examine项

3. 未命中索引的sql
   注意pt-query-digest分析中rows examine 和 rows send的对比

 

 

 

磁盘IO与预读：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间，旋转时间，传输时间三个部分。虽然平常理论上来说没什么压力，

但是数据库如果动辄十万百万乃至千万级数据，显然是个灾难。

 

了解下mysql的B+树:一种为了减少IO操作快速搜索到数据的数据结构，如下图：

    说明：

        蓝色部分磁盘块

        黄色部分指针

        深蓝色目标数据块
    分析一下查找的过程：

    如果要查询数据为29的值：

    1.将磁盘一加入到内存中此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针

    2.通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO

    3. 29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询

    总结：总计三次IO，真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，

每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

解释下什么叫二分法：其实就是一种通过不断排除不可能的东西，来最终找到需要的东西的一种方法，所以可以理解成排除法。之所以叫二分，是因为

每次排除都把所有的情况分为“可能”和不可能两种，然后抛弃所有“不可能”的情况。

  

优化sql实际上主要目的是为了减少IO操作：
1.如果想在mysql配置方面减少IO的操作：尽可能使用缓存，减少读写对数据库的随机IO的请求，同时减少写的随机IO的随时发生，利用各种buffer去缓存。

2.创建索引：合理的索引是为了少量IO操作达到数据的获取。

  

建立索引的几大原则：
1.在where 从句，group by从句，on从句中出现的列

2.最左前缀匹配，非常重要的原则，mysql会一直向左匹配直到遇到范围查询（>,<,between,like）就停止匹配
eg:a=1 and b=2 and c=3 and d=4   索引  add index (a,b,c,d)  这样创建d是用不到索引的
eg:a=1 and b=2 and c=3 and d=4   索引  add index(a,b,d,c) 这样创建则都可以用到，a,b,d的顺序可以任意调整

3.离散度大的列放到联合索引的前面 

    计算离散度的公式是count(distinct col)/count(*),表示字段不重复的比例，比例越大我们扫描的记录数越少。

    select * from payment where staff_id=2 and customer_id=584;

    是index(sftaff_id,customer_id)好？还是index（customer_id,staff_id）？
    由于customer_id的离散度更大，所以应该使用index（customer_id,staff_id)

4.索引列不能参与计算，保持列干净
    索引列不能参与计算，保持列干净，在where语句中索引字段不要使用函数，进行检索的时候需要把所有元素都应用函数才能比较，先人成本太大。

5.索引字段越小越好
    使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。

6.尽量的扩展索引，不要新建索引。
    

说了这么多索引的优点但是索引也是有负面的：

1. 每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能

   2.在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，所花的时间越长。

什么叫做好的索引：

1. 查询频繁（业务逻辑决定）

2. 区分度高

3. 长度小（与区分度保持一个平衡就是一个最优的效果）

4. 尽量能覆盖常用查询字段（并不表示所有字段都建立索引）