mysql查询性能优化之二

Mysql  /  houtizong 发布于 3年前   171

1 union的限制  有时mysql无法将限制条件从外层下推到内层，这使得原本能够限制部分返回结果的条件无法应用到内层  查询的优化上。  如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序在  合并结果集的话，就需要在union的各个子句中分别使用这些子句。    例如 想将两个子查询结果联合起来，然后再取前20条记录，那么mysql会将两个表都存在临时表里  再取出前20条。这是一个糟糕的设计，我们可以在每个子查询里分别只取出limit  合适的记录，把较小的数据放在临时表里。  2 并行查询  mysql 无法利用多核特性来并行执行查询。不需要花时间在这方面。  3 哈希关联  现在mysql还不支持哈希关联，我们可以取现救果，创建自定义哈希索引。  4 松散索引扫描  mysql不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常mysql的索引扫描需要先定义一个起点和终点，即使  需要的数据只是这段索引中很少的几个，mysql仍需要扫描这段索引中每一个条目。  假如我们有(a,b) 索引，where b between 2 and 40 ,因为索引的最左前缀是a ,但是在查询中只指定了字段b,所以无法使用这个索引，  只有通过全表扫描找到匹配的行。  5 最大值和最小值优化  对于max() 和 min() mysql 优化的并不好，我们可以通过例子说明：  在产品表中大概存在17000件产品，已知产品ID prod_id 是索引列，现在需要查找出名字为 '奢华黑' 的最小产品ID    通常情况下，我们会使用sql select min(prod_id) from ls_prod where name = '奢华黑' 来执行查询。  这时候我们来查看执行时间 0.027s,这个时间看起来并不影响用户体验，是在接受的范围内，但是，随着产品库的增加，这一数值急剧  飙升，在数据量达到百万级别的时候，最慢可以达到 17s，在条件更差的机器上可能表现更差，这绝对是不可忍受的。    由于name列上并没有索引，因此Mysql会进行一次全表扫描，如果mysql能够进行主键扫描，那么理论上mysql找到第一个满足条件的  记录的时候，就是我们需要找的最小值了，因为主键是严格按照prod_id的大小顺序来排序的，但是mysql现在只做全表扫描。    一个曲线的优化办法是移除min函数，然后使用limit来将查询重写  select prod_id from ls_prod  use index(primary) where name = '奢华黑' limit 1;  这个策略可以让mysql扫描尽可能少的记录数。 可能这条sql并不符合你的审美观，但是mysql并不能一眼看出我们想要最小的值  有时候为了获取更高的性能，我们不得不放弃一些原则。  6 在同一张表上查询和更新  mysql 不允许对同一张表同时进行查询和更新，这并不是优化器的限制，如果清除mysql是如何执行查询的，就可以避免这种情况。 7 mysql提示   mysql的提示可以帮助我们选择比较优秀的执行计划  作者写道 我们应该尽可能的避免使用 for update 和 lock in share mode 这类提示。shift! 我的架构师告诉我尽可能的使用这类  足以看出这个架构师是一坨屎！mysql 5.0和更新版本会默认给记录添加这类提示，由于很容易造成服务器的锁争用，所以应该尽可能的避免使用这类  提示。    use index 、ignore index 、force index   这几个提示会告诉优化器使用或者不使用那些索引来查询记录，在mysql 5.1和后期的版本可以通过新增选项for order by和for group by来  指定是否对排序和分组有效。force index 和 use index 基本相同，在优化器选择了错误的索引或者因为某些原因使用了另一个索引的  时候，可以使用这类提示。