时间戳

首页 > 解决方案 > MySql 查询不使用索引集

mysql - MySql 查询不使用索引集

问题描述

最近我们将一个数据库从 MariaDB 10.2 切换到 Percona Server (Mysql 5.7)，我们有一个查询大约需要 15 秒（之前大约是 0.5），因为查询优化器没有使用主表上的任何索引。因为app逻辑，我们不能改变查询格式，需要让DB使用索引。

查询结构很简单：

EXPLAIN SELECT `clients`.`id` AS `t0_c0`,
       `client`.`name1` AS `t0_c1`,
       `client`.`name2` AS `t0_c2`,
       `users`.`id` AS `t1_c0`,
       `users`.`suffix` AS `t1_c1`,
       `users`.`position` AS `t1_c2`,
       `users`.`first_name` AS `t1_c3`,
       `users`.`last_name` AS `t1_c4`,
       `privateData`.`id` AS `t2_c0`,
       `privateData`.`first_name` AS `t2_c1`,
       `privateData`.`last_name` AS `t2_c2`,
       `tariff`.`id` AS `t3_c0`,
       `tariff`.`provider_id` AS `t3_c1`,
       `tariff`.`tariff_type` AS `t3_c2`,
       `tariff`.`name` AS `t3_c3`,
       `providers`.`id` AS `t4_c0`,
       `providers`.`name1` AS `t4_c1`,
       `providers`.`name2` AS `t4_c2`,
       `addresses`.`id` AS `t5_c0`,
       `addresses`.`zipcode` AS `t5_c1`,
       `addresses`.`country` AS `t5_c2`,
       `addresses`.`city` AS `t5_c3`,
       `private`.`id` AS `t6_c0`,
       `private`.`first_name` AS `t6_c1`,
       `private`.`last_name` AS `t6_c2`,
       `commercial`.`id` AS `t7_c0`,
       `commercial`.`name1` AS `t7_c1`,
       `commercial`.`name2` AS `t7_c2`,
       `commercial`.`name_on_invoice` AS `t7_c3`,
       `commercial`.`organization_type` AS `t7_c4`,
       `organizations`.`id` AS `t8_c0`,
       `organizations`.`person_id` AS `t8_c1`,
       `organizations`.`address_id` AS `t8_c2`,
       `organizations`.`status` AS `t8_c3`,
       `shaddresses`.`id` AS `t9_c0`,
       `shaddresses`.`zipcode` AS `t9_c1`,
       `shaddresses`.`country` AS `t9_c2`,
       `shaddresses`.`city` AS `t9_c3`,
       `shprivate`.`id` AS `t10_c0`,
       `shprivate`.`first_name` AS `t10_c1`,
       `shprivate`.`last_name` AS `t10_c2`,
       `coraddresses`.`id` AS `t11_c0`,
       `coraddresses`.`zipcode` AS `t11_c1`,
       `coraddresses`.`country` AS `t11_c2`,
       `corprivate`.`id` AS `t12_c0`,
       `corprivate`.`first_name` AS `t12_c1`,
       `corprivate`.`last_name` AS `t12_c2`,
FROM `client` `client`
LEFT OUTER JOIN `users` `users` ON (`client`.`user_id`=`users`.`id`)
AND (users.status!=5)
LEFT OUTER JOIN `private` `privateData` ON (`users`.`person_id`=`privateData`.`id`)
LEFT OUTER JOIN `tariff` `tariff` ON (`client`.`rate_id`=`tariff`.`id`)
LEFT OUTER JOIN `providers` `providers` ON (`client`.`provider_id`=`providers`.`id`)
LEFT OUTER JOIN `addresses` `addresses` ON (`client`.`main_address_id`=`addresses`.`id`)
LEFT OUTER JOIN `private` `private` ON (`client`.`main_person_id`=`private`.`id`)
LEFT OUTER JOIN `commercial` `commercial` ON (`client`.`main_organization_id`=`commercial`.`id`)
LEFT OUTER JOIN `organizations` `organizations` ON (`client`.`id_organization`=`organizations`.`id`)
AND (organizations.status!=5)
LEFT OUTER JOIN `addresses` `shaddresses` ON (`client`.`shipping_address_id`=`shaddresses`.`id`)
LEFT OUTER JOIN `private` `shprivate` ON (`client`.`shipping_person_id`=`shprivate`.`id`)
LEFT OUTER JOIN `addresses` `coraddresses` ON (`client`.`correspondense_address_id`=`coraddresses`.`id`)
LEFT OUTER JOIN `private` `corprivate` ON (`client`.`correspondense_person_id`=`corprivate`.`id`)
WHERE (client.status!=5)
ORDER BY client.id DESC
LIMIT 10
OFFSET 10

我可以更改任何索引，但是，我不能更改查询。在旧主机上，它在 0.2 秒内运行，但是，它使用客户端表中的索引的优化器。使用 Percona Server (mysql 5.7) 需要 15 秒。优化器没有使用客户表中的任何索引。使用来自客户表的 FORCE INCEX() 不到 1 秒（复合索引在大约 0.2 秒内进行）。表“提供者”只有 1 行。我已经在“客户”表上设置了索引，但是，在解释中它们没有显示为可能的键。

我试图将 MySql 变量 'max_seeks_for_key' 设置为 1，但是，它仍然没有使用索引。

我认为我缺少一些基本的东西，但我不知道是什么。

这个查询的解释是：

在此处输入图像描述

ORDER BY 正在生成 TEMPORARY TABLE 并且正在使用所有资源（即使没有 INDEX，也没有 order by 在不到一秒的时间内运行）。

任何想法都值得赞赏。

标签： mysqloptimizationindexingmysql-5.7percona-xtradb-cluster

解决方案

这应该比使用以下方法更有效FORCE：

SELECT  ...
    FROM ( SELECT  id
              FROM  client
              WHERE  status != 5
              ORDER BY  id DESC
              LIMIT  10 OFFSET 10 
          ) AS ids
    JOIN  client USING(id)
    LEFT OUTER JOIN  `users` `users`  ON (`client`.`user_id`=`users`.`id`)
      AND  (users.status!=5)
    LEFT OUTER JOIN  `private` `privateData`  ON (`users`.`person_id`=`privateData`.`id`)
    LEFT OUTER JOIN  `tariff` `tariff`  ON (`client`.`rate_id`=`tariff`.`id`)
    LEFT OUTER JOIN  `providers` `providers`  ON (`client`.`provider_id`=`providers`.`id`)
    LEFT OUTER JOIN  `addresses` `addresses`  ON (`client`.`main_address_id`=`addresses`.`id`)
    LEFT OUTER JOIN  `private` `private`  ON (`client`.`main_person_id`=`private`.`id`)
    LEFT OUTER JOIN  `commercial` `commercial`  ON (`client`.`main_organization_id`=`commercial`.`id`)
    LEFT OUTER JOIN  `organizations` `organizations`  ON (`client`.`id_organization`=`organizations`.`id`)
      AND  (organizations.status!=5)
    LEFT OUTER JOIN  `addresses` `shaddresses`  ON (`client`.`shipping_address_id`=`shaddresses`.`id`)
    LEFT OUTER JOIN  `private` `shprivate`  ON (`client`.`shipping_person_id`=`shprivate`.`id`)
    LEFT OUTER JOIN  `addresses` `coraddresses`  ON (`client`.`correspondense_address_id`=`coraddresses`.`id`)
    LEFT OUTER JOIN  `private` `corprivate`  ON (`client`.`correspondense_person_id`=`corprivate`.`id`)
    ORDER BY  client.id DESC -- Yes, repeated here

推荐阅读