A perfect interpretation of the stored procedure of MySQL 5.6 MRR

MySQL 5.6 is coming soon, and 5.6 has made many optimizations to the optimizer. This time I mainly explain MRR (MULTI-RANGE-READ ).

I used stored procedures to explain the changes in this process. Let's take a look.

For statements:

1. SelectLog_timeFromPersonWhereNick_name ='Lucy';

Table Structure:

1. CREATE TABLE'Person '(
2. 'Id'Int(10) unsignedNOT NULLAUTO_INCREMENT,
3. 'Nick _ name'Varchar(40)NOT NULL,
4. 'Log _ time'Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5. PRIMARY KEY('Id '),
6. KEY'Idx _ nick_name '('Nick _ name ')
7. ) ENGINE = InnoDB AUTO_INCREMENT = 5DEFAULTCHARSET = latin1

The first is MySQL 5.5.

1. DELIMITER $
2. USE 'TT' $
3. DROP PROCEDUREIf exists 'SP _ range_scan5_5 '$
4. CREATEDEFINER = 'admin' @ '%'PROCEDURE'SP _ range_scan5_5 '()
5. BEGIN
6. -- Sample SQL statement is below.
7. -- Select log_time from person where nick_name = 'Lucy ';
8. DECLAREIINTUNSIGNEDDEFAULT0;
9. DECLARECntINTUNSIGNEDDEFAULT0;
10. SET@ Result ='';
11. SELECT COUNT(1)INTOCntFROMPersonWHERENick_name ='Lucy';
13. Loop1: WHILE I <cnt
14. DO
15. SET@ Stmt = CONCAT ('Select id into @ v_id from person where nick_name =''Lucy''Order by nick_name asc limit', I,', 1');
16. PREPARES1FROM@ Stmt;
17. EXECUTES1;
19. SET@ Result = CONCAT (@ result,'Select log_time from person where id = @ v_id');
20. SET@ Result = CONCAT (@ result,'Union all');
21. SETI = I + 1;
22. ENDWHILE loop1;
23. SET@ Result = SUBSTR (@ result, 1, CHAR_LENGTH (@ result)-CHAR_LENGTH ('Union all'));
24. PREPARES1FROM@ Result;
25. EXECUTES1;
26. DROP PREPARES1;
27. SET@ Result =NULL;
28. END$
29. DELIMITER;

MySQL 5.6.

1. DELIMITER $
2. USE 'TT' $
3. DROP PROCEDUREIf exists 'SP _ range_scan5_6 '$
4. CREATEDEFINER = 'admin' @ '%'PROCEDURE'SP _ range_scan5_6 '()
5. BEGIN
6. -- Sample SQL statement is below.
7. -- Select log_time from person where nick_name = 'Lucy ';
8. DECLAREIINTUNSIGNEDDEFAULT0;
9. DECLARECntINTUNSIGNEDDEFAULT0;
10. DECLAREIds TEXT;
11. SETIds ='';
12. SELECT COUNT(1)INTOCntFROMPersonWHERENick_name ='Lucy';
14. Loop1: WHILE I <cnt
15. DO
16. SET@ Stmt = CONCAT ('Select id into @ v_id from person where nick_name =''Lucy''
17. Order ByNick_nameAscLimit', I ,', 1 ');
18. PREPARES1FROM@ Stmt;
19. EXECUTES1;
20. SETIds = CONCAT (ids, @ v_id,',');
21. SETI = I + 1;
22. ENDWHILE loop1;
23. SETIds = CONCAT ('(', SUBSTR (ids, 1, CHAR_LENGTH (ids)-1 ),')');
24. SET@ Result = CONCAT ('Select log_time from person where id in', Ids );
25. PREPARES1FROM@ Result;
26. EXECUTES1;
27. DROP PREPARES1;
28. SET@ Result =NULL;
29. END$
30. DELIMITER;