MySQL内部临时表的具体使用_Mysql_

 CREATE TABLE t1(id INT PRIMARY KEY, a INT, b INT, INDEX(a)); DELIMITER ;; CREATE PROCEDURE idata() BEGIN DECLARE i INT; SET i=1; WHILE (i<= 1000) DO INSERT INTO t1 VALUES (i,i,i); SET i=i+1; END WHILE; END;; DELIMITER ; CALL idata();

 (SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2); mysql> EXPLAIN (SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2); +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | UNION | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Backward index scan; Using index | | NULL | UNION RESULT |  | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+

 mysql> EXPLAIN (SELECT 1000 AS f) UNION ALL (SELECT id FROM t1 ORDER BY id DESC LIMIT 2); +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | UNION | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Backward index scan; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+

 -- 16777216 Bytes = 16 MB mysql> SHOW VARIABLES like '%tmp_table_size%'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+

 -- MySQL 5.6上执行 mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m; +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | Using index; Using temporary; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+ mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m; +------+-----+ | m | c | +------+-----+ | 0 | 100 | | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | | 6 | 100 | | 7 | 100 | | 8 | 100 | | 9 | 100 | +------+-----+

 -- 跳过最后的排序阶段，直接从临时表中取回数据 mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL; +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | Using index; Using temporary | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+ -- t1中的数据是从1开始的 mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL; +------+-----+ | m | c | +------+-----+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | | 6 | 100 | | 7 | 100 | | 8 | 100 | | 9 | 100 | | 0 | 100 | +------+-----+

 SET tmp_table_size=1024;

 -- 内存临时表的上限为1024 Bytes，但内存临时表不能完全放下100行数据，内存临时表会转成磁盘临时表，默认采用InnoDB引擎 -- 如果t1很大，这个查询需要的磁盘临时表就会占用大量的磁盘空间 mysql> SELECT id%100 AS m, count(*) AS c FROM t1 GROUP BY m ORDER BY NULL LIMIT 10; +------+----+ | m | c | +------+----+ | 1 | 10 | | 2 | 10 | | 3 | 10 | | 4 | 10 | | 5 | 10 | | 6 | 10 | | 7 | 10 | | 8 | 10 | | 9 | 10 | | 10 | 10 | +------+----+

 -- MySQL 5.7上执行 ALTER TABLE t1 ADD COLUMN z INT GENERATED ALWAYS AS(id % 100), ADD INDEX(z); -- 使用了覆盖索引，不需要临时表，也不需要排序 mysql> EXPLAIN SELECT z, COUNT(*) AS c FROM t1 GROUP BY z; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | z | z | 5 | NULL | 1000 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：浅谈Mysql在什么情况下会使用内部临时表_Mysql_
                                                                
下一篇：MySQL教程彻底学懂存储过程_Mysql_