- 论坛徽章:
- 0
|
mysql derived table and view the performance
February 25th, 2007 admin Posted in SQL SERVER |
Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause.
In MySQL 5.0 support for views was added.
MySQL 4.1 from the beginning, it has derived support table, or on-line view of the basic FROM clause of the query.
These features are quite related to each other but how do they compare in terms of performance?
These features are related to each other between, but the performance comparison between them how to do »
Derived Tables in MySQL 5.0 seems to have different implementation from views, even though I would expect code base to be merged as it is quite the same task in terms of query optimization.
MySQL 5.0 in the table seems to be derived and the ways to achieve different view, although I understand from the merger of the code base that view on the query optimization should be the same.
Derived Tables are still handled by materializing them in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example).
Derived temporary table to table are still the way to deal with explicit, but are not indexed temporary table (and therefore best not to like, as in the case of connecting two derived table).
One more thing to watch for is the fact derived table is going to be materialized even to execute EXPLAIN statement. So if you have done mistake in select in from clause, ie forgotten join condition you might have EXPLAIN running forever.
On the other hand the need to consider is that the derivative forms of treatment needs to be significant, although only the implementation of EXPLAIN statement. So if everything in FROM in SELELCT operational mistakes, for example, have forgotten to write on the link conditions, then EXPLAIN may have been in operation.
Views on other hand do not have to be materialized and normally executed by rewriting the query. It only will be materialized if query merge is impossible or if requested by view creator.
View is different, it need not be explicit with only a simple query to rewrite the click. Not only in trying to create a joint enquiries or when the request needs to be explicit treatment.
What does it mean in terms of performance:
This means that they are the differences in performance are as follows:
PLAIN TEXT
SQL:
Query ON base TABLE executes USING INDEX AND it IS very fast
In the basic form for the implementation of the index, which very quickly
MySQL> SELECT * FROM test WHERE i = 5;
+—+———————————-+
| I | j |
+—+———————————-+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+—+———————————-+
1 row IN SET (0.03 sec)
Same query USING derived TABLE crawls:
In derivatives on the table do the same query, such as car break Laoniu Rafah
mysql> SELECT * FROM (SELECT * FROM test) t WHERE i = 5;
+---+----------------------------------+
| I | j |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET (1 min 40.86 sec)
Query USING VIEW IS fast again:
In an attempt to query on, fast and up
mysql> CREATE VIEW v AS SELECT * FROM test;
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT * FROM v WHERE i = 5;
+---+----------------------------------+
| I | j |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET (0.10 sec)
Here are couple of explains IF you are curios
Below the two EXPLAIN results may allow you are very surprised
mysql> EXPLAIN SELECT * FROM v WHERE i = 5;
+----+-------------+-------+-------+-------------- -+---------+---------+-------+------+-------+
| Id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------- -+---------+---------+-------+------+-------+
| 1 | PRIMARY | test | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------- -+---------+---------+-------+------+-------+
1 row IN SET (0.02 sec)
mysql> EXPLAIN SELECT * FROM (SELECT * FROM test) t WHERE i = 5;
+----+-------------+------------+------+---------- -----+------+---------+------+---------+---------- --- +
| Id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------- -----+------+---------+------+---------+---------- --- +
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1638400 | USING WHERE |
| 2 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1638400 | |
+----+-------------+------------+------+---------- -----+------+---------+------+---------+---------- --- +
2 rows IN SET (54.90 sec)
Note how long it took just TO execute EXPLAIN FOR derived TABLE
Please note that after many spent a long time before the implementation of End EXPLAIN.
So what does it mean in practice:
In practice, this means that:
Avoid derived tables - If there is other way to write the query it will be faster in most cases. In many cases even separate temporary table will be faster as you can add proper indexes to the table in this case.
Avoid the use of derivatives table - if possible, preferably using other means to prepare query, the majority of cases than those derived from the table to fast. In many cases, even independent of the temporary table to have the fast, because they can increase the appropriate index.
Consider using temporary views instead of derived tables If you really need to use subselect in from clause consider creating view using it in the query and dropping it after query was executed.
May consider trying to replace the temporary use of derivatives in Table If you do need to use in the FROM clause of enquiries, enquiries can be considered in an attempt to create, when trying to delete End enquiries.
In any case it is pretty annoying gotcha which I hope MySQL will fix in next MySQL versions - the fact queries in this example behave differently is illogical and counter intuitive.
In any case, this is very annoying, I hope MYSQL to the next version of its solution - in this example for the performance is so different in fact the only illogical roughly estimates.
http://www.dd64.com/mysql-derived-table-and-view-the-performance/ |
|
免费注册
发表于 2011-03-17 16:41
