一个公司如何做多个网站备案,页面效果设计,建外贸网站,房地产市场发展趋势查询性能优化
MySQL查询优化器的局限性
松散索引扫描
由于历史原因#xff0c;MySQL并不支持松散索引扫描#xff0c;也就无法按照不连续的方式扫描一个索引。通常#xff0c;MySQL的索引扫描需要先定义一个起点和终点#xff0c;即使需要的数据只是这段索引中很少数的几…查询性能优化
MySQL查询优化器的局限性
松散索引扫描
由于历史原因MySQL并不支持松散索引扫描也就无法按照不连续的方式扫描一个索引。通常MySQL的索引扫描需要先定义一个起点和终点即使需要的数据只是这段索引中很少数的几个MySQL仍需要扫描这段儿索引中的每一个条目。下面我们通过一个示例说明这点。假设我们有如下索引(a,b)有下面的查询:
mysqlSELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;因为索引的前导字段是列a但是在查询中只指定了字段b,MySQL无法使用这个索引从而只能通过全表扫描找到匹配的行 如图所示。了解索引的物理结构的话不难发现还可以有一个更快的办法执行上面的查询。索引的物理结构(不是存储引擎的API)使得可以先扫描a列的第一个值对应的b列的范围然后再跳到a列不同第二个不同值扫描对应的b列的范围。 如图所示展示了如果由MySQL来实现这个过程会怎样。注意到这时就无须再使用WHERE子句过滤因为松散索引扫描已经跳过了所有不需要的记录。上面是一个简单的例子除了松散索引扫描新增一个合适的索引当然也可以优化上述查询。但对于某些场景增加索引是没用的例如对于第一个索引列是范围条件第二个索引列是等值条件的查询靠增加索引就无法解决问题。 MySQL5.0之后的版本在某些特殊的场景下是可以使用松散索引扫描的例如在一个分组查询中需要找到分组的最大值和最小值:
mysql EXPLAIN SELECT actor_id, MAX(film_id)- FROM sakila.film_actor- GROUP BY actor_id\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: film_actorpartitions: NULLtype: range
possible_keys: PRIMARY,idx_fk_film_idkey: PRIMARYkey_len: 2ref: NULLrows: 201filtered: 100.00Extra: Using index for group-by在EXPLAIN中的Extra字段显示Using index for group-by表示这里将使用松散索引扫描不过如果MySQL能写上loose index probe相信会更好理解。在MySQL很好地支持松散索引扫描之前一个简单的绕过问题的办法就是给前面的列加上可能的常数值。在MySQL5.6之后的版本关于松散索引扫描的一些限制将会通过索引下推(index condition pushdown)的方式解决
最大值和最小值优化
对于MIN()和MAX()查询MySQL的优化做得并不好。这里有一个例子:
mysql SELECT MIN(actor_id) FROM sakila.actor WHERE first_namePENELOPE;因为在first_name字段上并没有索引因此MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描那么理论上当MySQL读到的第一个满足条件的记录的时候就是我们需要找到的最小值了因为主键是严格按照actor_id字段的大小顺序排列的。但是MySQL这时只会做全表扫描我们可以通过查看SHOW STATUS的全表扫描计数器来验证这一点。一个曲线的优化办法是移除MIN(),然后使用LIMIT来讲查询重写如下:
mysql SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)- WHERE first_name PENELOPE LIMIT 1;
----------
| actor_id |
----------
| 1 |
----------
1 row in set (0.00 sec)这个策略可以让MySQL扫描尽可能少的记录数。如果你是一个完美主义者可能会说这个SQL已经无法表达她的本意了。一般我们通过SQL告诉服务器我们需要什么数据由服务器来决定如何最优地获取数据不过在这个案例中我们其实是告诉MySQL如何去获取我们需要的数据通过SQL并不能一眼就看出我们其实是想要一个最小值。确实如此有时候为了获得更高的性能我们不得不放弃一些原则
在同一个表上查询和更新
MySQL不允许对同一个张表同时进行查询和更新。这其实并不是优化器的限制如果清楚MySQL是如何执行查询就可以避免这种情况。下面是一个无法运行的SQL虽然这是一个符合标准的SQL语句。这个SQL语句尝试将两个表中相似行的数量记录到字段cnt中:
mysql UPDATE tbl AS outer_tbl- SET cnt (- SELECT COUNT(*) FROM tbl AS inner_tbl- WHERE inner_tbl.type outer_tbl.type- );
ERROR 1093(HY000):You cant specify target table outer_tbl for update in FROM clause可以通过使用生成表的形式来绕过上面的限制因为MySQL只会把这个表当作一个临时表来处理。实际上这执行了两个查询:一个是子查询中的SELECT语句另一个是多表关联UPDATE只是关联的表是一个临时表。子查询会在UPDATE语句打开表之前就完成。所以下面的查询将会正常执行:
mysql UPDATE tbl- INNER JOIN (- SELECT type, count(*) AS cnt- FROM tbl- GROUP BY type- ) AS der USING(type)- SET tbl.cnt der.cnt;
