性能优化之mysql索引优化

时间:2022-07-09 05:57:07

 

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操作快速搜索到数据的数据结构,如下图:

 

性能优化之mysql索引优化
    说明:

        蓝色部分磁盘块

        黄色部分指针

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

    如果要查询数据为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. 尽量能覆盖常用查询字段(并不表示所有字段都建立索引)

  5.  

 

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操作快速搜索到数据的数据结构,如下图:

性能优化之mysql索引优化


    说明:

        蓝色部分磁盘块

        黄色部分指针

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

    如果要查询数据为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. 尽量能覆盖常用查询字段(并不表示所有字段都建立索引)

  5.