视图上的MySQL索引不起作用 [英] Mysql index on view not working
问题描述
我创建了一个名为"myview"的视图，如下所示.
I created a view named 'myview' as below.
create view myview
as select 'a' source,col1,col2
from table_a
union
select source,col1,col2
from table_b
;
table_a 在 col1 上有一个索引， table_b 在 source 上的 col1 <下有一个索引/code>.当我如下查询 myview 时，没有使用索引.
table_a has an index on col1, table_b has an index on source, col1. When I query on myview as below, no index is used.
select *
from myview
where source = a
and col1 = 'xxx'
;
如何使索引适用于该查询?
How do I make the indexes work on this query?
CREATE TABLE `table_a` (
`col1` VARCHAR(50) NULL DEFAULT NULL,
`col2` VARCHAR(50) NULL DEFAULT NULL,
INDEX `table_a_idx01` (`col1`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;
CREATE TABLE `table_b` (
`source` VARCHAR(50) NULL DEFAULT NULL,
`col1` VARCHAR(50) NULL DEFAULT NULL,
`col2` VARCHAR(50) NULL DEFAULT NULL,
INDEX `table_b_idx01` (`source`, `col1`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;
create view myview
as select 'a' source,col1,col2
from table_a
union
select source,col1,col2
from table_b
INSERT INTO table_a (col1, col2)
VALUES
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2');
INSERT INTO table_b (source,col1, col2)
VALUES
('b','test2', 'testcol2'),
('b','test2', 'testcol2'),
('b','test2', 'testcol2'),
('b','test2', 'testcol2'),
('b','test2', 'testcol2'),
('b','test2', 'testcol2');
解释
explain
select *
from table_a
where col1 = 'test'
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,table_a,ref,table_a_idx01,table_a_idx01,153,const,5,Using index condition
explain
select *
from table_b
where source = 'b'
and col1 = 'test'
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,table_b,ref,table_b_idx01,table_b_idx01,306,const,const,1,Using index condition
解释我的观点
explain
select *
from myview
where source = 'b'
and col1 = 'test'
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ref,<auto_key0>,<auto_key0>,306,const,const,1,Using where
2,DERIVED,table_a,ALL,\N,\N,\N,\N,6,\N
3,UNION,table_b,ALL,\N,\N,\N,\N,6,\N
\N,UNION RESULT,<union2,3>,ALL,\N,\N,\N,\N,\N,Using temporary
如您所见，视图选择不会调整索引.
推荐答案
您无法在视图上创建索引: https://stackoverflow.com/a/7922711/3595565
You can not create an index on a view: http://dev.mysql.com/doc/refman/5.7/en/view-restrictions.html, so you have to hope the index is used. https://stackoverflow.com/a/7922711/3595565
解决方法
There is a workaround mentioned in the comments of another part of the documentation: https://dev.mysql.com/doc/refman/5.5/en/create-view.html In which you create a regular table and set your specialized index and load the data from the view into the table.
如上所述创建物化视图仿真看起来不错，唯一的问题是我们正在继承缺少MySQL的索引视图暴露出来.
Creating a materialized view emulation as described above looks good, the only problem is that we are inheriting the lack of indexes MySQL views expose.
我的解决方案是根据我的要求创建正确索引的表具有与视图完全相同的结构，然后运行像这样的东西:
My solution is to create a correctly indexed table according to my needs, having the exact same structure as the view, and then running something like this:
LOCK TABLES materializedView WRITE;
TRUNCATE materializedView;
INSERT INTO materializedView SELECT * FROM regularView;
UNLOCK TABLES;
这样，materializedView中的所有索引都会保留在每个刷新".
That way all indexes from materializedView are preserved on every "refresh".
我打算在我现在正在做的应用程序中使用它，在哪里我们将拥有比插入/更新更多的SELECT选择.如果我保持我的SELECT的常规视图，我会要求服务器制造大量的每当有人需要知道有多少个项目时都要进行计算产品"A"的库存，相反，我将对带有正确的SKU，存储和期间索引的"materializedView".
I'm planning to use this in an application I'm doing right now, where we will have a lot more SELECTs than inserts/updates. If I keep a regular view for my SELECTs, I'll be asking the server to make tons of calculations every time someone needs to know how many items are on stock for product "A", instead, I'll have all SELECTs towards the "materializedView" with correct SKU, Store and Period indexes.
每当有人运行INSERT或更新，将以20:1的比例.(每个更新选择20个或插入)
The view "refresh" will occur every time someone runs an INSERT or UPDATE, which will be on a 20 to 1 ratio. (20 Selects for every Update or Insert)
我希望一切都按我的计划顺利进行.问候;-)
I hope things go as smooth as I'm planning. Greetings ;-)
为什么您的查询不使用索引?
当在 SELECT 中使用 UNION 时，mysql创建一个临时表来保存数据.因此，由于视图是更复杂查询的捷径"，因此在调用select时它将再次执行联合，请使用临时表...使用临时表算法来处理数据.
When using UNION in a SELECT mysql creates a temporary table to save the data. Thus as a view is a 'shortcut' for your more complex query, when calling the select it will again execute the union, use a temporary table... use the temptable alghorithm to process the data.
再次检查手册: http://dev.mysql.com/doc/refman/5.7/en/view-restrictions.html
索引可用于使用合并算法处理的视图.但是，使用临时表算法处理的视图是无法利用其基础表上的索引(尽管索引可以在临时表的生成过程中使用.
Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).
结论:查询中的 UNION 阻碍了视图使用索引.
Conclusion: The UNION in your query hinders the view from using the indexes.
来源
我想联合会导致视图使用临时表算法，它会创建一个临时表，然后将where条件应用于该临时表.
I guess the union causes the view to use the temptable algorithm, it creates a temporary table and then apply the where condition to the temporary table.
当前，联合查询始终使用临时表来存储结果返回给用户之前.[...]
Currently, union queries always use a temporary table to store the result before it is returned to the user. [...]
已在MySQL 5.7中修复 http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html
Fixed in MySQL 5.7 http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html
服务器不再为UNION语句使用临时表符合某些资格.而是从临时表中保留仅创建执行结果列所需的数据结构类型转换.[...]
The server no longer uses a temporary table for UNION statements that meet certain qualifications. Instead, it retains from temporary table creation only the data structures necessary to perform result column typecasting.[...]
一些测试数据以检查分析器
CREATE TABLE test1 (
id int auto_increment PRIMARY KEY,
col1 varchar(50),
col2 varchar(50)
);
CREATE TABLE test2 (
id int auto_increment PRIMARY KEY,
col1 varchar(50),
col2 varchar(50)
);
INSERT INTO test1 (col1, col2)
VALUES
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2'),
('test', 'testcol2');
INSERT INTO test2 (col1, col2)
VALUES
('test2', 'testcol2'),
('test2', 'testcol2'),
('test2', 'testcol2'),
('test2', 'testcol2'),
('test2', 'testcol2'),
('test2', 'testcol2');
CREATE VIEW testview AS
SELECT * FROM test1
UNION
SELECT * FROM test2;
检查探查器:
SET PROFILING = 1;
SELECT * FROM testview WHERE id = 1;
+----+-------+----------+
| id | col1 | col2 |
+----+-------+----------+
| 1 | test | testcol2 |
| 1 | test2 | testcol2 |
+----+-------+----------+
SHOW PROFILE;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000017 |
| Waiting for query cache lock | 0.000004 |
| checking query cache for query | 0.000029 |
| checking permissions | 0.000006 |
| Opening tables | 0.000121 |
| System lock | 0.000012 |
| checking permissions | 0.000014 |
| checking permissions | 0.000032 |
| optimizing | 0.000004 |
| statistics | 0.000007 |
| preparing | 0.000006 |
| executing | 0.000003 |
| Sending data | 0.000046 |
| optimizing | 0.000003 |
| statistics | 0.000004 |
| preparing | 0.000003 |
| executing | 0.000002 |
| Sending data | 0.000023 |
| optimizing | 0.000003 |
| statistics | 0.000003 |
| preparing | 0.000003 |
| executing | 0.000002 |
| Sending data | 0.000008 |
| removing tmp table | 0.000005 |
| Sending data | 0.000005 |
| Waiting for query cache lock | 0.000002 |
| Sending data | 0.000024 |
| init | 0.000011 |
| optimizing | 0.000006 |
| statistics | 0.000004 |
| preparing | 0.000006 |
| executing | 0.000002 |
| Sending data | 0.000021 |
| end | 0.000003 |
| query end | 0.000004 |
| closing tables | 0.000002 |
| removing tmp table | 0.000004 |
| closing tables | 0.000006 |
| freeing items | 0.000005 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000013 |
| Waiting for query cache lock | 0.000002 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000003 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
我不能从个人资料中获取太多信息，但是它确实提到了临时表，足够(对我而言)来验证我的结论.
I can't take too much information out of the profile, but it does mention temporary table, enough (for me) to validate my conclusion.
这篇关于视图上的MySQL索引不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
