SELECT city,name,age FROM t WHERE city='杭州'ORDERBY name LIMIT 1000;
存储过程
1 2 3 4 5 6 7 8 9 10 11 12 13
DELIMITER ;; CREATEPROCEDURE idata() BEGIN DECLARE i INT; SET i=0; WHILE i<4000 DO INSERTINTO t VALUES (i,'杭州',concat('zhongmingmao',i),'20','XXX'); SET i=i+1; END WHILE; END;; DELIMITER ;
CALL idata();
全字段排序
city索引树
满足city=’杭州’的行,主键为ID_X ~ ID_(X+N)
sort buffer
1 2 3 4 5 6 7 8 9 10 11 12 13 14
mysql> EXPLAIN SELECT city,name,age FROM t FORCE INDEX(city) WHERE city='杭州'ORDERBY name LIMIT 1000\G; ***************************1.row*************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: city key: city key_len: 50 ref: const rows: 4000 filtered: 100.00 Extra: Using index condition; Using filesort
rows=4000:EXPLAIN是不考虑LIMIT的,代表匹配条件的总行数
Using index condition:表示使用了索引下推
Using filesort:表示需要排序,MySQL会为每个线程分配一块内存用于排序,即sort buffer
In optimizer trace output, num_tmp_files did not actually indicate number of files. It has been renamed to num_initial_chunks_spilled_to_disk and indicates the number of chunks before any merging has occurred.
mysql> EXPLAIN SELECT city,name,age FROM t FORCE INDEX(city_user) WHERE city='杭州'ORDERBY name LIMIT 1000\G; ***************************1.row*************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: city_user key: city_user key_len: 50 ref: const rows: 4000 filtered: 100.00 Extra: Using index condition
ALTERTABLE t ADD INDEX city_user_age(city, name, age);
explain
Using index:表示使用覆盖索引
1 2 3 4 5 6 7 8 9 10 11 12 13 14
mysql> EXPLAIN SELECT city,name,age FROM t FORCE INDEX(city_user_age) WHERE city='杭州'ORDERBY name LIMIT 1000\G; ***************************1.row*************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: city_user_age key: city_user_age key_len: 50 ref: const rows: 4000 filtered: 100.00 Extra: Usingwhere; Using index
mysql> EXPLAIN SELECT*FROM t FORCE INDEX(city_user) WHERE city IN ('杭州','苏州') ORDERBY name LIMIT 100\G ***************************1.row*************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: range possible_keys: city_user key: city_user key_len: 50 ref: NULL rows: 4001 filtered: 100.00 Extra: Using index condition; Using filesort
mysql> EXPLAIN SELECT*FROM t FORCE INDEX(city_user) WHERE city IN ('杭州') ORDERBY name LIMIT 100\G ***************************1.row*************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: city_user key: city_user key_len: 50 ref: const rows: 4000 filtered: 100.00 Extra: Using index condition
解决方案
拆分语句,包装在同一个事务
SELECT * FROM t WHERE city='杭州' ORDER BY name LIMIT 100;:不需要排序,客户端用一个内存数组A保存结果
SELECT * FROM t WHERE city='苏州' ORDER BY name LIMIT 100;:不需要排序,客户端用一个内存数组B保存结果