你VIEW的SELECT语句是什么?你直接 explain 这个SELECT语句。这样问题应该是你这个union 的 select 语句可能没有用到索引。可以排除VIEW的问题。如果仅是 select union 的问题,则可以按楼上说的,看看还需要建哪些INDEX。建议你可以贴出你的 create view as ..的语句。 和你认为没有走索引的语句,这样大家可以一起分析具体的原因。
做视图我是用存储过程动态做的 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 ;输入的含table的表结构都一样。
EXPLAIN SELECT * FROM view WHERE TIME> 100000 id select_type table type possible_keys key key_len ref rows Extra;2 DERIVED table1 ALL \N \N \N \N 1 ;3 UNION table2 ALL \N \N \N \N 1 ;\N UNION RESULT <union2,,3> ALL \N \N \N \N \N
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY \N \N \N \N \N \N \N Impossible WHERE noticed after reading const tables 2 DERIVED table1 ALL \N \N \N \N 1 3 UNION table2 ALL \N \N \N \N 1
对于使用MERGE算法处理的视图,可以使用索引。但是,对于使用临时表算法处理的视图,不能在其基表上利用索引提供的优点(尽管能够在临时表的生成过程中使用索引)。
Query OK, 0 rows affected (0.05 sec)mysql> explain select userid from aavv;
+----+--------------+------------+-------+---------------+---------+---------+------+------+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+--------------+------------+-------+---------------+---------+---------+------+------+-
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 8 |
| 2 | DERIVED | user | index | NULL | PRIMARY | 2 | NULL | 8 |
| 3 | UNION | user | index | NULL | PRIMARY | 2 | NULL | 8 |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL
+----+--------------+------------+-------+---------------+---------+---------+------
+------+-
--------+------+------+-------------+
key_len | ref | rows | Extra |
--------+------+------+-------------+
NULL | NULL | 8 | |
2 | NULL | 8 | Using index |
2 | NULL | 8 | Using index |
| NULL | NULL | NULL | |
--------+------+------+-------------+
4 rows in set (0.02 sec)
是走索引的
EXPLAIN SELECT * FROM view WHERE TIME> 1000000view 是由n个表结构相同的表union all 起来的
和你认为没有走索引的语句,这样大家可以一起分析具体的原因。
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 ;输入的含table的表结构都一样。
id select_type table type possible_keys key key_len ref rows Extra;2 DERIVED table1 ALL \N \N \N \N 1 ;3 UNION table2 ALL \N \N \N \N 1 ;\N UNION RESULT <union2,,3> ALL \N \N \N \N \N
1 PRIMARY \N \N \N \N \N \N \N Impossible WHERE noticed after reading const tables
2 DERIVED table1 ALL \N \N \N \N 1
3 UNION table2 ALL \N \N \N \N 1