单表覆盖索引的mysql查询优化策略

Question

目前，我正在进行mysql查询优化。我的mysql表包含2亿条记录。在谷歌上做了很多次之后，我最终决定使用覆盖索引。

所以，我按照这个顺序做了一个索引

alter table table_name add index
     index_name(MODEL, COUNTRY, REGION, NETWORK, E_TUAL,
                ECHS, DEVID, COUNTRY_CODE, SOURCE);

当我运行这个查询时，效率与以前相比是很好的

SELECT E_TUAL,ECHS, DEVID, MODEL, COUNTRY, REGION, COUNTRY_CODE, NETWORK, SOURCE
FROM table_name
WHERE model = 'fox | s453' AND country = 'india' AND
      E_TUAL <= '1435755600' AND
      E_TUAL >= '1433163600'

ORDER BY E_TUAL DESC LIMIT 101偏移量0；

+----+-------------+-----------+------+---------------+--------+---------+-------------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key    | key_len | ref         | rows | Extra                       |
+----+-------------+-----------+------+---------------+--------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | mytable | ref  | genrl  | genrl | 131     | const |  239 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+--------+---------+-------------+------+-----------------------------+

但是这个查询比旧的查询(在索引之前)更糟糕。

SELECT E_TUAL,ECHS, DEVID, MODEL,COUNTRY, REGION, COUNTRY_CODE, NETWORK, SOURCE
FROM table_name
WHERE model = 'fox | s453' AND
      country is not null AND
      E_TUAL <= '1435755600' AND
      E_TUAL >= '1433163600'
ORDER BY E_TUAL DESC
LIMIT 101 OFFSET 0;

+----+-------------+-----------+-------+---------------+--------+---------+------+------+-----------------------------+
| id | select_type | table     | type  | possible_keys | key    | key_len | ref  | rows | Extra                       |
+----+-------------+-----------+-------+---------------+--------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | mytable | range | genrl  | genrl | 131     | NULL | 1105 | Using where; Using filesort |
+----+-------------+-----------+-------+---------------+--------+---------+------+------+-----------------------------+

我写了country is not null来维护顺序，所以mysql优化器使用索引

当某些列字段缺失时，请帮助如何提高效率

要使用索引，我能做些什么？

我的英语不好。所以对于任何错误我深表歉意

Answer 1

去掉覆盖索引，使用两个独立的索引：

alter table table_name add index index_name(MODEL, COUNTRY, E_TUAL);

对于第一个查询。和：

alter table table_name add index index_name(MODEL, E_TUAL);

覆盖索引可能会提供边际改进，但它将使用大量空间。相反，如果可以将这些子句用于索引，请先关注WHERE子句，然后再关注ORDER BY。

注意:对于这两个查询，您对列的排序都不是最佳的。