mysql如何让视图利用索引？

Question

刚发的帖子不够详细，来发个详细的。1、我用存储过程动态生成视图。
2、每个table在time(时间戳)字段上均做了索引
3、各个table结构一样，view里面用union all，目的是把几张表拼成一张“大表”，这个view对外就是查询表。下面是建立视图的存储过程，详细的我就不解释了，相信能够回答我这个问题的都能看懂。
CREATE DEFINER=`test`@`%` PROCEDURE `USP_ASS_CreateView`($ViewName VARCHAR(50),$TableName VARCHAR(100))
BEGIN
BEGIN
SET @DropView = CONCAT('DROP VIEW IF EXISTS ',$ViewName,';');
PREPARE DropView FROM @DropView;
EXECUTE DropView;
END;
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tablename VARCHAR(50);
DECLARE Cmd VARCHAR(8000) DEFAULT '';
DECLARE Cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_name LIKE CONCAT('%',$TableName,'%');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN Cur;
REPEAT
FETCH Cur INTO tablename;
IF NOT done THEN
SET Cmd = CONCAT(Cmd,'select * from ',tablename,' union all ');
END IF;
UNTIL done END REPEAT;
CLOSE Cur;
SET @Cmd = CONCAT('Create ALGORITHM = MERGE view  ',$ViewName,' AS ',LEFT(cmd,LENGTH(cmd)-10),';');
PREPARE stmt FROM @Cmd;
EXECUTE stmt;
END;
END$$DELIMITER ;
然后查询语句如下：explain select * from table1 where time > 100000
-----------------------------------------------------------------------------------------------------
id select_type table type possible_keys key key_len  ref rows Extra
1 SIMPLE         table1 range Index_Time   Index_Time    4  \N 1 Using  where
-----------------------------------------------------------------------------------------------------select * from view where time >100000 (view 为table1和table2的集合)
-----------------------------------------------------------------------------------------------------
id     select_type     table    type   possible_keys    key     key_len  ref   rows               Extra
1        PRIMARY                                    Impossible  WHERE  noticed  after  reading  const  tables
2        DERIVED       table1    ALL                                            1 
3        UNION         table2    ALL                                            1 
         UNION RESULT <union2,3> ALL 
-----------------------------------------------------------------------------------------------------我希望的效果是在2，3步内像在表查询中一样利用index_time索引。这个有没有办法实现？

Answer 1

用UNION　ALL生成的是一张虚拟表，估计没有索引，
将UNION　ALL的结果生成物理表，建立索引试试
OR
先生成一张结构一样的空表，建立索引，再将UNION　ALL的结果INSERT　，再查询

Answer 2

测试了一下，直接查询UNION　ALL生成的虚拟表确实无法用到索引，
你要做出选择，
查询要快：建立索引，即上述方法
否则只有接受现在的速度了

Answer 3

 对于使用MERGE算法处理的视图，可以使用索引。但是，对于使用临时表算法处理的视图，不能在其基表上利用索引提供的优点（尽管能够在临时表的生成过程中使用索引）。这个merge算法又不能包括union all于是
为仅能用临时表处理的视图指定ALGORITHM = MERGE。在这种情况下，MySQL将生成告警，并将算法设置为UNDEFINED。MERGE算法要求视图中的行和基表中的行具有一对一的关系。如果不具有该关系。必须使用临时表取而代之。如果视图包含下述结构中的任何一种，将失去一对一的关系：·         聚合函数（SUM(), MIN(), MAX(), COUNT()等）。·         DISTINCT ·         GROUP BY ·         HAVING ·         UNION或UNION ALL 看来要达到我的要求，这个思路貌似不行了

Answer 4

'select * from ',tablename,'---------
你这内部本身就是全表扫描了其实，你的这个实现效果，改下就可以了：
考虑用存储过程直接返回结果集（不考虑用视图了），在存储过程内部，因为你的“按天分表”，可以这样考虑：
SET Cmd = CONCAT(Cmd,'select * from ',tablename,'where time > 100000 union all ');

Answer 5

我明白你的意思，最开始原型就是这么做的，后迁移到mssql由于有分区视图，方便了很多
没想到回到mysql，又要改回去了。这里代码改动的就比较多了……程序复杂度大大提高...

Answer 6

UNION ALL 可以使用索引啊，关键是你是否所有表上都有这个 Index_Time    的索引？mysql> explain select id from t1 where id<10
    -> union all
    -> select id from t2 where id<10;
+----+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY      | t1         | range | PRIMARY       | PRIMARY | 4       | NULL |   10 | Using where; Using index |
|  2 | UNION        | t2         | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL | NULL |                          |
+----+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
3 rows in set (0.01 sec)mysql>

Answer 7

请提供如下信息1. show create view viewName
2. show index from table1
3. show index from table2
4. explain select * from table1 where time > 100000
5. explain select * from table2 where time > 100000
6. explain select * from table1 where time > 100000 union all select * from table2 where time > 100000
 

Answer 8

如果你的表是myisam 的可以考虑一下 merge 存储引擎。
mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    ENGINE=MERGE UNION=(t1,t2) ;

Answer 9

估计楼主是想
select * from (
select * from b1
union all
select * from b2
) a where id=10
在这里是无法使用索引的

Answer 10

mysql> explain select * from test where id>5;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | PRIMARY       | NULL | NULL    | NULL |   22 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql> explain select id from test where id>5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 4       | NULL |   17 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)mysql> explain select id from test where id>5 union select id from test where id>5;
+----+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY      | test       | range | PRIMARY       | PRIMARY | 4       | NULL |   17 | Using where; Using index |
|  2 | UNION        | test       | range | PRIMARY       | PRIMARY | 4       | NULL |   17 | Using where; Using index |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL | NULL |                          |
+----+--------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
其中id是索引字段 可见使用索引要在查询列建立索引

Answer 11

1、CREATE  ALGORITHM=UNDEFINED DEFINER=`test`@`%`  SQL  SECURITY  DEFINER  VIEW  'view_test'  AS select aa，bb，cc from table1 union all select aa,bb,cc
utf8 utf8_general_ci 
2、Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
table1 1 Index_Time 1 Time A 0 \N \N  BTREE 
3、Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
table2 1 Index_Time 1 Time A 0 \N \N  BTREE 
4、id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table1 range Index_Time Index_Time 4 \N 1 Using  where
5、id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tbl_ori_cellstatus_20090920 range Index_Time Index_Time 4 \N 1 Using  where
6、id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tbl_ori_cellstatus range Index_Time Index_Time 4 \N 1 Using  where
2 UNION tbl_ori_cellstatus_20090920 range Index_Time Index_Time 4 \N 1 Using  where
\N UNION  RESULT <union1,2> ALL \N \N \N \N \N 但我的意思不是6表达的意思，我是union all以后，再去查询，因为我的条件是动态的。

Answer 12

如果这样，建议你考虑一下 merge 存储引擎。可以参见11楼的例子。