I am struggling to optimize the following query which takes an average of 2000 ms.
我正在努力优化下面的查询，平均需要2000 ms。
select count(pk)
from mytable
where (pk<>5
and url='test.png'
and (data=124578 or (data is null and pk=1234578)))
and type in (123,456,789,015,789)
Here are some info :
以下是一些信息:
select count(*) from mytable
1 526 588 lines
1 526 588行
show indexes in mytable
Table non_unique key_name seq_in_index column_name collation cardinality
mytable 0 PRIMARY 1 PK A 1405079
mytable 1 data 1 data A 1405079
mytable 1 Media_Code_30 1 code A 1405079
mytable 1 codeVersionIDX_30 1 code A 1405079
Explain :
解释:
id select_type table type possible_keys key key_len ref rows extra
1 SIMPLE mytable ref_or_null PRIMARY,data data 9 const 635908 Using where
I really have no idea whether this is optimized enough, or it can be better by creating new indexes (or composite indexes).
我真的不知道这个优化是否足够，或者创建新的索引(或复合索引)会更好。
The query, however, cannot be changed as it is coming from another system on which I do not have my hands on!
但是，查询不能更改，因为它来自另一个系统，而我对此无能为力!
4 个解决方案
#1
2
Hmmm, this query seems difficult to set up an index for:
嗯，这个查询似乎很难建立一个索引:
select count(pk)
from mytable
where (pk <> 5 and url = 'test.png' and
(data = 124578 or (data is null and pk = 1234578))
) and
type in (123, 456, 789, 015, 789);
My suggestion is to start with type and include the other columns in the index: mytable(type, url, data, pk). This is a covering index and that might improve performance a bit.
我的建议是从类型开始，包括索引中的其他列:mytable(类型、url、数据、pk)。这是一个覆盖指标，可能会提高性能。
#2
2
This is not the Answer. Only some sample for using index
这不是答案。只有一些使用索引的样本。
SAMPLE
样本
small Table
小桌子
MariaDB [bb]> SHOW CREATE TABLE useindex\G
*************************** 1. row ***************************
Table: useindex
Create Table: CREATE TABLE `useindex` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`num1` int(11) DEFAULT NULL,
`num2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2031586 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
number of rows and sample data
行数和示例数据
MariaDB [bb]> SELECT count(*) FROM USEindex;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
1 row in set (0.43 sec)
MariaDB [bb]> SELECT * FROM useindex LIMIT 10;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
| 1 | 405 | 906 |
| 2 | 656 | 656 |
| 3 | 906 | 407 |
| 4 | 156 | 157 |
| 5 | 406 | 908 |
| 6 | 656 | 659 |
| 7 | 907 | 409 |
| 8 | 157 | 160 |
| 9 | 407 | 910 |
| 10 | 657 | 661 |
+----+------+------+
10 rows in set (0.00 sec)
EXPLAIN 3 queries (no index)
解释3个查询(无索引)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | useindex | ALL | NULL | NULL | NULL | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | useindex | ALL | NULL | NULL | NULL | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | useindex | ALL | NULL | NULL | NULL | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
add 2 indexe
加上2 indexe
MariaDB [bb]> ALTER TABLE useindex
-> ADD KEY n1 (num1),
-> ADD KEY n2 (num2);
Query OK, 0 rows affected (12.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
EXPLAIN with index
解释与指数
see if the WHERE with 2 fields, MySQL use only one INDEX
看看有两个字段的地方，MySQL是否只使用一个索引
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | useindex | ref | n1 | n1 | 5 | const | 2003 | |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | useindex | ref | n2 | n2 | 5 | const | 1993 | |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | useindex | ref | n1,n2 | n2 | 5 | const | 1993 | Using where |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
add COMPOSITE Index over both fields
在两个字段上添加复合索引
MariaDB [bb]> ALTER TABLE useindex
-> ADD KEY n12 (num1,num2);
Query OK, 0 rows affected (7.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
EXPLAIN Queries again
解释再次查询
Now they use the COMOSITE Index
现在他们使用了魔岛网站索引
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | useindex | ref | n1,n12 | n12 | 5 | const | 2003 | Using index |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | useindex | ref | n2 | n2 | 5 | const | 1993 | |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
| 1 | SIMPLE | useindex | ref | n1,n2,n12 | n12 | 10 | const,const | 1 | Using index |
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
Tell the OPTIMIZER to MERGE INDEX
告诉优化器合并索引
MariaDB [bb]> SET optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.00 sec)
drop INDEX n12
下降指数n12
MariaDB [bb]> alter table useindex drop key n12;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
RUN one QUERY with MERGE
使用MERGE运行一个查询
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
| 1 | SIMPLE | useindex | index_merge | n1,n2 | n2,n1 | 5,5 | NULL | 1 | Using intersect(n2,n1); Using where; Using index |
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [bb]>
#3
0
MySQL can only use one INDEX in a query.
MySQL只能在查询中使用一个索引。
so it is necessary to have composite index. the best way for the order of the fields in the index is the field which reduce the resultset mostly and so on.
因此有必要建立复合指数。索引中字段的顺序最好的方法是减少结果集的字段，依此类推。
lets say you have a table with names and birthday and you have many duplicates of names and only a few of birthday. so then create the index like (birthday,name) the mysql search first the records with the searched day and then only in this little result the name
假设你有一张桌子，上面有名字和生日，你有很多重复的名字，只有几个生日。然后创建索引，比如(生日，名字)mysql搜索，先搜索记录和搜索日期，然后在这个小的结果中输入名字
#4
0
The best index for that query is
该查询的最佳索引是
INDEX(url, type) -- in that order
Indexes should start with any columns that are "= constant" (url in this case), then move on to one more column that is more complex (type IN ...). See my cookbook.
索引应该从“= constant”(本例中为url)的任何列开始，然后再移动到更复杂的列(输入…)。看到我的食谱。
#1
2
Hmmm, this query seems difficult to set up an index for:
嗯，这个查询似乎很难建立一个索引:
select count(pk)
from mytable
where (pk <> 5 and url = 'test.png' and
(data = 124578 or (data is null and pk = 1234578))
) and
type in (123, 456, 789, 015, 789);
My suggestion is to start with type and include the other columns in the index: mytable(type, url, data, pk). This is a covering index and that might improve performance a bit.
我的建议是从类型开始，包括索引中的其他列:mytable(类型、url、数据、pk)。这是一个覆盖指标，可能会提高性能。
#2
2
This is not the Answer. Only some sample for using index
这不是答案。只有一些使用索引的样本。
SAMPLE
样本
small Table
小桌子
MariaDB [bb]> SHOW CREATE TABLE useindex\G
*************************** 1. row ***************************
Table: useindex
Create Table: CREATE TABLE `useindex` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`num1` int(11) DEFAULT NULL,
`num2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2031586 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
number of rows and sample data
行数和示例数据
MariaDB [bb]> SELECT count(*) FROM USEindex;
+----------+
| count(*) |
+----------+
| 2000000 |
+----------+
1 row in set (0.43 sec)
MariaDB [bb]> SELECT * FROM useindex LIMIT 10;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
| 1 | 405 | 906 |
| 2 | 656 | 656 |
| 3 | 906 | 407 |
| 4 | 156 | 157 |
| 5 | 406 | 908 |
| 6 | 656 | 659 |
| 7 | 907 | 409 |
| 8 | 157 | 160 |
| 9 | 407 | 910 |
| 10 | 657 | 661 |
+----+------+------+
10 rows in set (0.00 sec)
EXPLAIN 3 queries (no index)
解释3个查询(无索引)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | useindex | ALL | NULL | NULL | NULL | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | useindex | ALL | NULL | NULL | NULL | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | useindex | ALL | NULL | NULL | NULL | NULL | 1996444 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
add 2 indexe
加上2 indexe
MariaDB [bb]> ALTER TABLE useindex
-> ADD KEY n1 (num1),
-> ADD KEY n2 (num2);
Query OK, 0 rows affected (12.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
EXPLAIN with index
解释与指数
see if the WHERE with 2 fields, MySQL use only one INDEX
看看有两个字段的地方，MySQL是否只使用一个索引
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | useindex | ref | n1 | n1 | 5 | const | 2003 | |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | useindex | ref | n2 | n2 | 5 | const | 1993 | |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | useindex | ref | n1,n2 | n2 | 5 | const | 1993 | Using where |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
add COMPOSITE Index over both fields
在两个字段上添加复合索引
MariaDB [bb]> ALTER TABLE useindex
-> ADD KEY n12 (num1,num2);
Query OK, 0 rows affected (7.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
EXPLAIN Queries again
解释再次查询
Now they use the COMOSITE Index
现在他们使用了魔岛网站索引
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | useindex | ref | n1,n12 | n12 | 5 | const | 2003 | Using index |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | useindex | ref | n2 | n2 | 5 | const | 1993 | |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
| 1 | SIMPLE | useindex | ref | n1,n2,n12 | n12 | 10 | const,const | 1 | Using index |
+------+-------------+----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
Tell the OPTIMIZER to MERGE INDEX
告诉优化器合并索引
MariaDB [bb]> SET optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.00 sec)
drop INDEX n12
下降指数n12
MariaDB [bb]> alter table useindex drop key n12;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
RUN one QUERY with MERGE
使用MERGE运行一个查询
MariaDB [bb]> EXPLAIN SELECT * FROM useindex WHERE num1 = 500 AND num2 = 600;
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
| 1 | SIMPLE | useindex | index_merge | n1,n2 | n2,n1 | 5,5 | NULL | 1 | Using intersect(n2,n1); Using where; Using index |
+------+-------------+----------+-------------+---------------+-------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [bb]>
#3
0
MySQL can only use one INDEX in a query.
MySQL只能在查询中使用一个索引。
so it is necessary to have composite index. the best way for the order of the fields in the index is the field which reduce the resultset mostly and so on.
因此有必要建立复合指数。索引中字段的顺序最好的方法是减少结果集的字段，依此类推。
lets say you have a table with names and birthday and you have many duplicates of names and only a few of birthday. so then create the index like (birthday,name) the mysql search first the records with the searched day and then only in this little result the name
假设你有一张桌子，上面有名字和生日，你有很多重复的名字，只有几个生日。然后创建索引，比如(生日，名字)mysql搜索，先搜索记录和搜索日期，然后在这个小的结果中输入名字
#4
0
The best index for that query is
该查询的最佳索引是
INDEX(url, type) -- in that order
Indexes should start with any columns that are "= constant" (url in this case), then move on to one more column that is more complex (type IN ...). See my cookbook.
索引应该从“= constant”(本例中为url)的任何列开始，然后再移动到更复杂的列(输入…)。看到我的食谱。