本文是作为MySQL使用指南一文的进一步细节延申和补充.
- 分页查询
- 联合索引
- 回表
(由于Typora的字数超过2万速度会明显下降, 4万字已经出现卡顿, 这里将更多内容拆出来讨论).
# 创建测试的表
drop table if exists test_w;
create table test_w (
id int UNSIGNED not null PRIMARY key auto_increment,
grade float UNSIGNED not null,
birth_date date not null,
s_name varCHAR(15) not null,
address varchar(16) not null
);
# 写入随机的数据
drop PROCEDURE if exists pro_test_w;
delimiter $$
CREATE PROCEDURE pro_test_w ( IN total INT ) BEGIN
DECLARE
i INT DEFAULT 1;
START TRANSACTION;
WHILE
i <= total DO
INSERT INTO test_w ( grade, birth_date, s_name, address )
VALUES
(
ROUND( RAND() * 10, 1 ),
DATE_ADD( now(), INTERVAL floor( 10000 * rand()) DAY ),
CONCAT ( 'name_', i ),
CONCAT ( 'address_', i )
);
SET i = i + 1;
END WHILE;
COMMIT;
END $$
delimiter;
# 为保证显示出足够的时间差异, 将这里的数字调节大一点
call pro_test_w(1500000);
# 比较消耗时间, 注意
上述操作, 先得到150万行的随机测试数据.
一. 分页查询
分别对不同的偏移程度进行取25条数据.
- 10
- 1250000
MySQL root@localhost:test_db> EXPLAIN select grade, s_name from test_w order by grade limit 10, 25;
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| 1 | SIMPLE | test_w | <null> | ALL | <null> | <null> | <null> | <null> | 1491849 | 100.0 | Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
1 row in set
Time: 0.006s
MySQL root@localhost:test_db> EXPLAIN select grade, s_name from test_w order by grade limit 950000, 25;
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| 1 | SIMPLE | test_w | <null> | ALL | <null> | <null> | <null> | <null> | 1491849 | 100.0 | Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
可以看到, 上述的返回数据的方式均需要全表扫描.
MySQL root@localhost:test_db> select grade, s_name from test_w order by grade limit 10, 25;
+-------+-----------+
| grade | s_name |
+-------+-----------+
| 0.0 | name_4023 |
| 0.0 | name_2404 |
| 0.0 | name_6336 |
| 0.0 | name_472 |
| 0.0 | name_4705 |
| 0.0 | name_2889 |
| 0.0 | name_2504 |
| 0.0 | name_3088 |
| 0.0 | name_5769 |
| 0.0 | name_5962 |
| 0.0 | name_3045 |
| 0.0 | name_520 |
| 0.0 | name_83 |
| 0.0 | name_554 |
| 0.0 | name_2890 |
| 0.0 | name_4883 |
| 0.0 | name_3930 |
| 0.0 | name_1848 |
| 0.0 | name_1111 |
| 0.0 | name_2242 |
| 0.0 | name_811 |
| 0.0 | name_1259 |
| 0.0 | name_6429 |
| 0.0 | name_2482 |
| 0.0 | name_4537 |
+-------+-----------+
25 rows in set
Time: 0.433s
MySQL root@localhost:test_db> select grade, s_name from test_w order by grade limit 1250000, 25;
+-------+-------------+
| grade | s_name |
+-------+-------------+
| 8.3 | name_104964 |
| 8.3 | name_105016 |
| 8.3 | name_105119 |
| 8.3 | name_105191 |
| 8.3 | name_105204 |
| 8.3 | name_105299 |
| 8.3 | name_105484 |
| 8.3 | name_105490 |
| 8.3 | name_105493 |
| 8.3 | name_105663 |
| 8.3 | name_105691 |
| 8.3 | name_105925 |
| 8.3 | name_105990 |
| 8.3 | name_106309 |
| 8.3 | name_106310 |
| 8.3 | name_106615 |
| 8.3 | name_106631 |
| 8.3 | name_106641 |
| 8.3 | name_106657 |
| 8.3 | name_106855 |
| 8.3 | name_106918 |
| 8.3 | name_106926 |
| 8.3 | name_106954 |
| 8.3 | name_107094 |
| 8.3 | name_107238 |
+-------+-------------+
25 rows in set
Time: 0.817s
但是其返回数据消耗的时间差异很大:
- Time: 0.433s
- Time: 0.817s
这里可以看到这么简单的数据偏移操作返回数据也是颇为消耗时间的, 这就是全表扫描带来的压力, 同时偏移的范围的扩大, 消耗的时间也在不断地增加.
这里对需要排序的grade一项进行添加索引的操作.
alter table test_w add index idx_grade (grade);
MySQL root@localhost:test_db> EXPLAIN select grade, s_name from test_w order by grade limit 10, 25;
+----+-------------+--------+------------+-------+---------------+-----------+---------+--------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------+---------+--------+------+----------+--------+
| 1 | SIMPLE | test_w | <null> | index | <null> | idx_grade | 4 | <null> | 35 | 100.0 | <null> |
+----+-------------+--------+------------+-------+---------------+-----------+---------+--------+------+----------+--------+
MySQL root@localhost:test_db> EXPLAIN select grade, s_name from test_w order by grade limit 1250000, 25;
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| 1 | SIMPLE | test_w | <null> | ALL | <null> | <null> | <null> | <null> | 1491849 | 100.0 | Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
可以看到其中的变化, 添加索引后, limit 10, 25;偏移量小的语句使用了索引而limit 1250000, 25;依然还是使用全表扫描的方式.
MySQL root@localhost:test_db> select grade, s_name from test_w order by grade limit 1250000, 25;
+-------+-------------+
| grade | s_name |
+-------+-------------+
| 8.3 | name_104964 |
| 8.3 | name_105016 |
| 8.3 | name_105119 |
| 8.3 | name_105191 |
| 8.3 | name_105204 |
| 8.3 | name_105299 |
| 8.3 | name_105484 |
| 8.3 | name_105490 |
| 8.3 | name_105493 |
| 8.3 | name_105663 |
| 8.3 | name_105691 |
| 8.3 | name_105925 |
| 8.3 | name_105990 |
| 8.3 | name_106309 |
| 8.3 | name_106310 |
| 8.3 | name_106615 |
| 8.3 | name_106631 |
| 8.3 | name_106641 |
| 8.3 | name_106657 |
| 8.3 | name_106855 |
| 8.3 | name_106918 |
| 8.3 | name_106926 |
| 8.3 | name_106954 |
| 8.3 | name_107094 |
| 8.3 | name_107238 |
+-------+-------------+
25 rows in set
Time: 0.824s
MySQL root@localhost:test_db> select grade, s_name from test_w order by grade limit 10, 25;
+-------+-----------+
| grade | s_name |
+-------+-----------+
| 0.0 | name_2242 |
| 0.0 | name_2404 |
| 0.0 | name_2482 |
| 0.0 | name_2504 |
| 0.0 | name_2531 |
| 0.0 | name_2672 |
| 0.0 | name_2694 |
| 0.0 | name_2889 |
| 0.0 | name_2890 |
| 0.0 | name_3045 |
| 0.0 | name_3088 |
| 0.0 | name_3930 |
| 0.0 | name_4023 |
| 0.0 | name_4153 |
| 0.0 | name_4228 |
| 0.0 | name_4537 |
| 0.0 | name_4705 |
| 0.0 | name_4883 |
| 0.0 | name_5040 |
| 0.0 | name_5214 |
| 0.0 | name_5520 |
| 0.0 | name_5769 |
| 0.0 | name_5962 |
| 0.0 | name_6336 |
| 0.0 | name_6429 |
+-------+-----------+
25 rows in set
Time: 0.007s
- Time, 0.007s
- Time: 0.824s
二者的差异就更为明显了, 由偏移量大的依然还是使用全表扫描的方式, 没有使用索引.
这里强制偏移量大的SQL语句使用索引,看看变化.
MySQL root@localhost:test_db> EXPLAIN select grade, s_name from test_w force index (idx_grade) order by grade limit
-> 1250000, 25;
+----+-------------+--------+------------+-------+---------------+-----------+---------+--------+---------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered
| Extra |
+----+-------------+--------+------------+-------+---------------+-----------+---------+--------+---------+----------+--------+
| 1 | SIMPLE | test_w | <null> | index | <null> | idx_grade | 4 | <null> | 1250025 | 100.0
| <null> |
+----+-------------+--------+------------+-------+---------------+-----------+---------+--------+---------+----------+--------+
MySQL root@localhost:test_db> select grade, s_name from test_w force index (idx_grade) order by grade limit 1250000,
-> 25;
+-------+--------------+
| grade | s_name |
+-------+--------------+
| 8.3 | name_1247790 |
| 8.3 | name_1247906 |
| 8.3 | name_1247950 |
| 8.3 | name_1247969 |
| 8.3 | name_1248146 |
| 8.3 | name_1248243 |
| 8.3 | name_1248300 |
| 8.3 | name_1248532 |
| 8.3 | name_1248671 |
| 8.3 | name_1248812 |
| 8.3 | name_1248815 |
| 8.3 | name_1248949 |
| 8.3 | name_1249038 |
| 8.3 | name_1249061 |
| 8.3 | name_1249062 |
| 8.3 | name_1249233 |
| 8.3 | name_1249381 |
| 8.3 | name_1249921 |
| 8.3 | name_1249934 |
| 8.3 | name_1249978 |
| 8.3 | name_1250079 |
| 8.3 | name_1250176 |
| 8.3 | name_1250258 |
| 8.3 | name_1250385 |
| 8.3 | name_1250396 |
+-------+--------------+
25 rows in set
Time: 7.354s
强制索引之后, 时间的消耗对比
- Time: 0.824s
- Time: 7.354s
索引没有为大的偏移量这个语句带来速度的提升, 相反, 消耗的时间增加了将近8倍.
这里可以回答在MySQL使用手册那篇文章提到的, MySQL的优化器的作用.
注意优化器的解析处理, 这将改变一些理论上的行为. 例如优化器认为走全表扫描比对索引来的更快, 也许并不会使用索引.
二. 联合索引
如果一个索引包含( 或者说覆盖) 所有需要查询的字段的值 我们就称之为" 覆盖索引" .
alter table test_w add index idx_grade_s_name (grade, s_name);
将需要查询的字段添加到联合索引中去.
MySQL root@localhost:test_db> EXPLAIN select grade, s_name from test_w order by grade limit 1250000, 25;
+----+-------------+--------+------------+-------+---------------+------------------+---------+--------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+--------+---------+----------+-------------+
| 1 | SIMPLE | test_w | <null> | index | <null> | idx_grade_s_name | 86 | <null> | 1250025 | 100.0 | Using index |
+----+-------------+--------+------------+-------+---------------+------------------+---------+--------+---------+----------+-------------+
可以看到, 该语句的执行已经转为使用索引的方式.
MySQL root@localhost:test_db> select grade, s_name from test_w order by grade limit 1250000, 25;
+-------+-------------+
| grade | s_name |
+-------+-------------+
| 8.3 | name_771974 |
| 8.3 | name_771978 |
| 8.3 | name_77200 |
| 8.3 | name_772160 |
| 8.3 | name_772332 |
| 8.3 | name_772430 |
| 8.3 | name_772537 |
| 8.3 | name_772725 |
| 8.3 | name_772759 |
| 8.3 | name_772784 |
| 8.3 | name_772811 |
| 8.3 | name_772822 |
| 8.3 | name_772918 |
| 8.3 | name_773078 |
| 8.3 | name_773079 |
| 8.3 | name_773153 |
| 8.3 | name_77325 |
| 8.3 | name_773450 |
| 8.3 | name_773591 |
| 8.3 | name_773746 |
| 8.3 | name_773994 |
| 8.3 | name_774021 |
| 8.3 | name_774102 |
| 8.3 | name_774109 |
| 8.3 | name_774159 |
+-------+-------------+
25 rows in set
Time: 0.259s
- Time: 0.259s
时间的消耗大幅度下降
但是, 联合索引, 有一个很明显的弊端, 那就是需要将大量查询的字段纳入到索引当中去, 假如需要检索的字段更多, 显然不希望将所有的字段添加到索引当中去, 这会导致索引消耗过于巨大.
三. 回表
将上述的语句变换一下, 将一个新的字段加入到select语句中, 上述的联合索引就失效了.
MySQL root@localhost:test_db> EXPLAIN select grade, s_name, address from test_w order by grade limit 1250000, 25;
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| 1 | SIMPLE | test_w | <null> | ALL | <null> | <null> | <null> | <null> | 1491849 | 100.0 | Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
1 row in set
3.1 手动回表
需要注意这里的子语句查询, This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery.
MySQL root@localhost:test_db> select grade, s_name from test_w where id in (select id from test_w order by grade lim
-> it 1250000, 25);
(1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")
MySQL root@localhost:test_db> explain select grade, s_name from test_w as b join (select id from test_w order by gra
-> de limit 1250000, 25) as a on a.id = b.id;
+----+-------------+------------+------------+--------+---------------+-----------+---------+--------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+-----------+---------+--------+---------+----------+-------------+
| 1 | PRIMARY | <derived2> | <null> | ALL | <null> | <null> | <null> | <null> | 1250025 | 100.0 | <null> |
| 1 | PRIMARY | b | <null> | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | 100.0 | <null> |
| 2 | DERIVED | test_w | <null> | index | <null> | idx_grade | 4 | <null> | 1250025 | 100.0 | Using index |
+----+-------------+------------+------------+--------+---------------+-----------+---------+--------+---------+----------+-------------+
3 rows in set
Time: 0.008s
这里可以看到索引的使用情况.
MySQL root@localhost:test_db> select grade, s_name from test_w as b join (select id from test_w order by grade limit
-> 1250000, 25) as a on a.id = b.id;
+-------+--------------+
| grade | s_name |
+-------+--------------+
| 8.3 | name_1247790 |
| 8.3 | name_1247906 |
| 8.3 | name_1247950 |
| 8.3 | name_1247969 |
| 8.3 | name_1248146 |
| 8.3 | name_1248243 |
| 8.3 | name_1248300 |
| 8.3 | name_1248532 |
| 8.3 | name_1248671 |
| 8.3 | name_1248812 |
| 8.3 | name_1248815 |
| 8.3 | name_1248949 |
| 8.3 | name_1249038 |
| 8.3 | name_1249061 |
| 8.3 | name_1249062 |
| 8.3 | name_1249233 |
| 8.3 | name_1249381 |
| 8.3 | name_1249921 |
| 8.3 | name_1249934 |
| 8.3 | name_1249978 |
| 8.3 | name_1250079 |
| 8.3 | name_1250176 |
| 8.3 | name_1250258 |
| 8.3 | name_1250385 |
| 8.3 | name_1250396 |
+-------+--------------+
25 rows in set
Time: 0.207s
消耗的时间甚至比联合索引消耗的时间更少.
MySQL root@localhost:test_db> EXPLAIN select grade, s_name, address from test_w order by grade limit 1250000, 25;
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
| 1 | SIMPLE | test_w | <null> | ALL | <null> | <null> | <null> | <null> | 1491849 | 100.0 | Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+--------+---------+----------+----------------+
1 row in set
Time: 0.006s
MySQL root@localhost:test_db> select grade, s_name, address from test_w as b join (select id from test_w order by gr
-> ade limit 1250000, 25) as a on a.id = b.id;
+-------+--------------+-----------------+
| grade | s_name | address |
+-------+--------------+-----------------+
| 8.3 | name_1247790 | address_1247790 |
| 8.3 | name_1247906 | address_1247906 |
| 8.3 | name_1247950 | address_1247950 |
| 8.3 | name_1247969 | address_1247969 |
| 8.3 | name_1248146 | address_1248146 |
| 8.3 | name_1248243 | address_1248243 |
| 8.3 | name_1248300 | address_1248300 |
| 8.3 | name_1248532 | address_1248532 |
| 8.3 | name_1248671 | address_1248671 |
| 8.3 | name_1248812 | address_1248812 |
| 8.3 | name_1248815 | address_1248815 |
| 8.3 | name_1248949 | address_1248949 |
| 8.3 | name_1249038 | address_1249038 |
| 8.3 | name_1249061 | address_1249061 |
| 8.3 | name_1249062 | address_1249062 |
| 8.3 | name_1249233 | address_1249233 |
| 8.3 | name_1249381 | address_1249381 |
| 8.3 | name_1249921 | address_1249921 |
| 8.3 | name_1249934 | address_1249934 |
| 8.3 | name_1249978 | address_1249978 |
| 8.3 | name_1250079 | address_1250079 |
| 8.3 | name_1250176 | address_1250176 |
| 8.3 | name_1250258 | address_1250258 |
| 8.3 | name_1250385 | address_1250385 |
| 8.3 | name_1250396 | address_1250396 |
+-------+--------------+-----------------+
25 rows in set
Time: 0.206s
而且比联合索引使用更为方便, 可以任意添加字段, 而不需要将之纳入到联合索引当中去.
3.2 性能的影响
但是需要注意的是手动回表的方式, 也并非完全是高效的.
这受到这个子语句中的数据量的影响
(select id from test_w where grade > 8.8)
MySQL root@localhost:test_db> select grade, s_name, address from test_w where grade > 8.8;
+-------+--------------+-----------------+
| grade | s_name | address |
+-------+--------------+-----------------+
| 9.8 | name_16 | address_16 |
| 9.4 | name_23 | address_23 |
| 9.2 | name_33 | address_33 |
| 9.0 | name_39 | address_39 |
| 8.9 | name_45 | address_45 |
| 9.8 | name_50 | address_50 |
| 9.3 | name_51 | address_51 |
| 9.1 | name_71 | address_71 |
| 10.0 | name_75 | address_75 |
| 9.9 | name_98 | address_98 |
| 9.0 | name_100 | address_100 |
| 9.6 | name_111 | address_111 |
| 8.9 | name_115 | address_115 |
| 9.0 | name_116 | address_116 |
| 9.0 | name_138 | address_138 |
| 9.0 | name_142 | address_142 |
| 9.2 | name_144 | address_144 |
| 9.1 | name_148 | address_148 |
| 9.3 | name_171 | address_171 |
| 9.8 | name_173 | address_173 |
| 9.9 | name_175 | address_175 |
| 8.9 | name_185 | address_185 |
| 8.9 | name_191 | address_191 |
| 9.6 | name_200 | address_200 |
| 9.6 | name_216 | address_216 |
| 9.3 | name_217 | address_217 |
| 9.9 | name_235 | address_235 |
| 9.2 | name_245 | address_245 |
| 9.1 | name_251 | address_251 |
| 8.9 | name_264 | address_264 |
| 9.1 | name_275 | address_275 |
| 10.0 | name_276 | address_276 |
| 9.3 | name_289 | address_289 |
| 9.1 | name_290 | address_290 |
| 9.9 | name_291 | address_291 |
| 8.8 | name_298 | address_298 |
187602 rows in set
Time: 15.274s
直接检索的方式比手动回表的方式速度更快.
ySQL root@localhost:test_db> select grade, s_name, address from test_w where id in (select id from test_w where gra
-> de > 8.8);
+-------+--------------+-----------------+
| grade | s_name | address |
+-------+--------------+-----------------+
| 8.8 | name_298 | address_298 |
| 8.8 | name_354 | address_354 |
| 8.8 | name_487 | address_487 |
| 8.8 | name_617 | address_617 |
| 8.8 | name_727 | address_727 |
| 8.8 | name_746 | address_746 |
| 8.8 | name_791 | address_791 |
| 8.8 | name_824 | address_824 |
| 8.8 | name_921 | address_921 |
| 8.8 | name_938 | address_938 |
| 8.8 | name_1004 | address_1004 |
| 8.8 | name_1079 | address_1079 |
| 8.8 | name_1267 | address_1267 |
| 8.8 | name_1298 | address_1298 |
| 8.8 | name_1431 | address_1431 |
| 8.8 | name_1738 | address_1738 |
| 8.8 | name_1757 | address_1757 |
| 8.8 | name_2063 | address_2063 |
| 8.8 | name_2072 | address_2072 |
| 8.8 | name_2089 | address_2089 |
| 8.8 | name_2391 | address_2391 |
| 8.8 | name_2469 | address_2469 |
| 8.8 | name_2483 | address_2483 |
| 8.8 | name_2507 | address_2507 |
| 8.8 | name_2515 | address_2515 |
| 8.8 | name_2522 | address_2522 |
| 8.8 | name_2574 | address_2574 |
| 8.8 | name_2675 | address_2675 |
| 8.8 | name_2724 | address_2724 |
| 8.8 | name_2736 | address_2736 |
| 8.8 | name_2766 | address_2766 |
| 8.8 | name_2849 | address_2849 |
| 8.8 | name_3231 | address_3231 |
| 8.8 | name_3392 | address_3392 |
| 8.8 | name_3395 | address_3395 |
| 8.8 | name_3438 | address_3438 |
187602 rows in set
Time: 31.309s
当数据量变小时, 这里二者的性能差异也随着变化
MySQL root@localhost:test_db> select grade, s_name, address from test_w where id in (select id from test_w where bir
-> th_date > '2050-06-28');
+-------+--------------+-----------------+
| grade | s_name | address |
+-------+--------------+-----------------+
| 5.0 | name_155 | address_155 |
| 2.7 | name_196 | address_196 |
| 8.7 | name_268 | address_268 |
| 1.9 | name_1264 | address_1264 |
| 3.4 | name_1526 | address_1526 |
| 4.7 | name_2339 | address_2339 |
| 1.5 | name_2631 | address_2631 |
| 8.8 | name_3909 | address_3909 |
| 2.7 | name_4506 | address_4506 |
| 6.8 | name_4530 | address_4530 |
| 7.9 | name_5021 | address_5021 |
| 2.5 | name_5148 | address_5148 |
| 2.2 | name_5274 | address_5274 |
| 3.7 | name_5995 | address_5995 |
| 4.4 | name_6323 | address_6323 |
| 8.6 | name_6552 | address_6552 |
| 2.5 | name_6619 | address_6619 |
| 8.7 | name_6694 | address_6694 |
| 9.8 | name_6774 | address_6774 |
| 7.0 | name_6782 | address_6782 |
| 6.4 | name_7439 | address_7439 |
| 8.2 | name_8065 | address_8065 |
| 9.8 | name_8528 | address_8528 |
| 6.1 | name_10099 | address_10099 |
| 6.2 | name_10749 | address_10749 |
| 9.3 | name_10884 | address_10884 |
| 7.4 | name_11121 | address_11121 |
| 4.5 | name_11284 | address_11284 |
| 1.6 | name_11664 | address_11664 |
| 7.5 | name_12016 | address_12016 |
| 4.9 | name_12219 | address_12219 |
| 2.5 | name_12723 | address_12723 |
| 1.7 | name_14061 | address_14061 |
| 6.2 | name_14686 | address_14686 |
| 7.1 | name_15274 | address_15274 |
| 0.7 | name_15600 | address_15600 |
3409 rows in set
Time: 1.823s
MySQL root@localhost:test_db> select grade, s_name, address from test_w where birth_date > '2050-06-28';
+-------+--------------+-----------------+
| grade | s_name | address |
+-------+--------------+-----------------+
| 5.0 | name_155 | address_155 |
| 2.7 | name_196 | address_196 |
| 8.7 | name_268 | address_268 |
| 1.9 | name_1264 | address_1264 |
| 3.4 | name_1526 | address_1526 |
| 4.7 | name_2339 | address_2339 |
| 1.5 | name_2631 | address_2631 |
| 8.8 | name_3909 | address_3909 |
| 2.7 | name_4506 | address_4506 |
| 6.8 | name_4530 | address_4530 |
| 7.9 | name_5021 | address_5021 |
| 2.5 | name_5148 | address_5148 |
| 2.2 | name_5274 | address_5274 |
| 3.7 | name_5995 | address_5995 |
| 4.4 | name_6323 | address_6323 |
| 8.6 | name_6552 | address_6552 |
| 2.5 | name_6619 | address_6619 |
| 8.7 | name_6694 | address_6694 |
| 9.8 | name_6774 | address_6774 |
| 7.0 | name_6782 | address_6782 |
| 6.4 | name_7439 | address_7439 |
| 8.2 | name_8065 | address_8065 |
| 9.8 | name_8528 | address_8528 |
| 6.1 | name_10099 | address_10099 |
| 6.2 | name_10749 | address_10749 |
| 9.3 | name_10884 | address_10884 |
| 7.4 | name_11121 | address_11121 |
| 4.5 | name_11284 | address_11284 |
| 1.6 | name_11664 | address_11664 |
| 7.5 | name_12016 | address_12016 |
| 4.9 | name_12219 | address_12219 |
| 2.5 | name_12723 | address_12723 |
| 1.7 | name_14061 | address_14061 |
| 6.2 | name_14686 | address_14686 |
| 7.1 | name_15274 | address_15274 |
| 0.7 | name_15600 | address_15600 |
3409 rows in set
Time: 1.825s
四. 小结
综上, 可以看到, 各种方式的执行优劣并不是完全固定的, 根据数量的大小的变化情况来决定使用那些SQL语句.
适当的使用索引(联合索引)能够大幅度提升执行的速度.
