MySQL OCP5.7-使用索引提高查询性能

1.查询employees库的titles表:
root@localhost[(none)]>use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost[employees]>select emp_no,title from titles where title='Manager' AND to_date>NOW();
+--------+---------+
| emp_no | title   |
+--------+---------+
| 110039 | Manager |
| 110114 | Manager |
| 110228 | Manager |
| 110420 | Manager |
| 110567 | Manager |
| 110854 | Manager |
| 111133 | Manager |
| 111534 | Manager |
| 111939 | Manager |
+--------+---------+
9 rows in set (0.46 sec)

2.通过explain分析SQL的执行计划:
root@localhost[employees]>explain select emp_no,title from titles where title='Manager' and to_date>NOW()\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
   partitions: NULL
         type: ALL        #表示全表扫描
possible_keys: NULL        #没有使用任何索引
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 441535        #扫描表的大概行数
     filtered: 3.33        #表示在执行查询时根据条件筛选行数占比,这也是一个估计值
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified

3.在title表的title字段上创建单值索引:
root@localhost[employees]>create index titles_title on titles(title);
Query OK, 0 rows affected (2.66 sec)
Records: 0  Duplicates: 0  Warnings: 0


4.再次运行下面的查询语句:
root@localhost[employees]>select emp_no,title from titles where title='Manager' AND to_date>NOW();
+--------+---------+
| emp_no | title   |
+--------+---------+
| 110039 | Manager |
| 110114 | Manager |
| 110228 | Manager |
| 110420 | Manager |
| 110567 | Manager |
| 110854 | Manager |
| 111133 | Manager |
| 111534 | Manager |
| 111939 | Manager |
+--------+---------+
9 rows in set (0.01 sec)
这次查询时间为0.01 sec,上次执行同样的语句,查询时间为0.46 sec


5.通过EXPLAIN 再次来查看上面SQL语句的执行计划:
root@localhost[employees]>explain select emp_no,title from titles where title='Manager' and to_date>NOW()\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
   partitions: NULL
         type: ref                            #查询的类型
possible_keys: titles_title        #可能使用的索引
          key: titles_title                #查询已使用的索引
      key_len: 202                        #索引的长度
          ref: const                    
         rows: 24                                #只需要查询24行就可以得到全部符合条件的数据
     filtered: 33.33                        #表示在执行查询时根据条件筛选行数占比,这也是一个估计值
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified


6.在表titles上创建字段title,to_date的复合索引:
root@localhost[employees]>create index titles_title_date on titles(title,to_date);
Query OK, 0 rows affected (2.93 sec)
Records: 0  Duplicates: 0  Warnings: 0


7.通过EXPLAIN查看上面SQL的执行计划:
root@localhost[employees]>explain select emp_no,title from titles where title='Manager' and to_date>NOW()\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
   partitions: NULL
         type: range        #range查询类型
possible_keys: titles_title,titles_title_date        #可能使用的索引
          key: titles_title_date                #优化器真实使用的索引
      key_len: 206                                #索引的长度
          ref: NULL
         rows: 9                                    #查询满足条件的数据需要扫描的行数
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified


8.删除上面创建的两个索引:
root@localhost[employees]>drop index titles_title on titles;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@localhost[employees]>drop index titles_title_date on titles;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

9.执行下面的SQL:
root@localhost[employees]>select count(*),ROUND(salary,-3) as base from salaries where salary between 50000 and 80000 and to_date>NOW() group by base;
+----------+-------+
| count(*) | base  |
+----------+-------+
|     1791 | 50000 |
|     3826 | 51000 |
|     3873 | 52000 |
|     4123 | 53000 |
|     4241 | 54000 |
|     4497 | 55000 |
|     4758 | 56000 |
|     4875 | 57000 |
|     4862 | 58000 |
|     5221 | 59000 |
|     5226 | 60000 |
|     5428 | 61000 |
|     5414 | 62000 |
|     5416 | 63000 |
|     5695 | 64000 |
|     5469 | 65000 |
|     5767 | 66000 |
|     5886 | 67000 |
|     5803 | 68000 |
|     5852 | 69000 |
|     5741 | 70000 |
|     5509 | 71000 |
|     5457 | 72000 |
|     5261 | 73000 |
|     5256 | 74000 |
|     4969 | 75000 |
|     4711 | 76000 |
|     4545 | 77000 |
|     4379 | 78000 |
|     4136 | 79000 |
|     2046 | 80000 |
+----------+-------+
31 rows in set (3.00 sec)


10.通过EXPLAIN查询上面SQL的执行计划:
root@localhost[employees]>explain select count(*),ROUND(salary,-3) as base from salaries where salary between 50000 and 80000 and to_date>NOW() group by base\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ALL        #没有使用任何索引
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2838426        #查询记录需要扫描的数据行
     filtered: 3.70
        Extra: Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified

11.在where 后面的salary条件列添加索引
root@localhost[employees]>create index salary_value on salaries(salary);
Query OK, 0 rows affected (9.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

12.创建索引后,运行上面查询需要的时间:
root@localhost[employees]>select count(*),ROUND(salary,-3) as base from salaries where salary between 50000 and 80000 and to_date>NOW() group by base;
+----------+-------+
| count(*) | base  |
+----------+-------+
|     1791 | 50000 |
|     3826 | 51000 |
|     3873 | 52000 |
|     4123 | 53000 |
|     4241 | 54000 |
|     4497 | 55000 |
|     4758 | 56000 |
|     4875 | 57000 |
|     4862 | 58000 |
|     5221 | 59000 |
|     5226 | 60000 |
|     5428 | 61000 |
|     5414 | 62000 |
|     5416 | 63000 |
|     5695 | 64000 |
|     5469 | 65000 |
|     5767 | 66000 |
|     5886 | 67000 |
|     5803 | 68000 |
|     5852 | 69000 |
|     5741 | 70000 |
|     5509 | 71000 |
|     5457 | 72000 |
|     5261 | 73000 |
|     5256 | 74000 |
|     4969 | 75000 |
|     4711 | 76000 |
|     4545 | 77000 |
|     4379 | 78000 |
|     4136 | 79000 |
|     2046 | 80000 |
+----------+-------+
31 rows in set (1.74 sec)
这次用时1.74sec,上次查询同样的SQL用了3.00sec,查询时间缩短了

13.运行EXPLAIN来查看上面SQL的执行计划
root@localhost[employees]>explain select count(*),ROUND(salary,-3) as base from salaries where salary between 50000 and 80000 and to_date>NOW() group by base\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: salary_value        #可能使用的索引
          key: NULL                        #实际使用的索引,实际没有使用到索引
      key_len: NULL
          ref: NULL
         rows: 2838426                        #全表扫描
     filtered: 16.66
        Extra: Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified

14.创建salary 和to_date的复合索引
root@localhost[employees]>create index salary_value_date on salaries(salary,to_date);
Query OK, 0 rows affected (9.70 sec)
Records: 0  Duplicates: 0  Warnings: 0
创建的索引是where条件的列,所以优化器应该能够考虑这个索引 

15.执行上面的SQL查询,查询数据返回的时间
root@localhost[employees]>select count(*),ROUND(salary,-3) as base from salaries where salary between 50000 and 80000 and to_date>NOW() group by base;
+----------+-------+
| count(*) | base  |
+----------+-------+
|     1791 | 50000 |
|     3826 | 51000 |
|     3873 | 52000 |
|     4123 | 53000 |
|     4241 | 54000 |
|     4497 | 55000 |
|     4758 | 56000 |
|     4875 | 57000 |
|     4862 | 58000 |
|     5221 | 59000 |
|     5226 | 60000 |
|     5428 | 61000 |
|     5414 | 62000 |
|     5416 | 63000 |
|     5695 | 64000 |
|     5469 | 65000 |
|     5767 | 66000 |
|     5886 | 67000 |
|     5803 | 68000 |
|     5852 | 69000 |
|     5741 | 70000 |
|     5509 | 71000 |
|     5457 | 72000 |
|     5261 | 73000 |
|     5256 | 74000 |
|     4969 | 75000 |
|     4711 | 76000 |
|     4545 | 77000 |
|     4379 | 78000 |
|     4136 | 79000 |
|     2046 | 80000 |
+----------+-------+
31 rows in set (1.10 sec)
这次查询消耗的时间为1.10sec,比上次的SQL查询的时间更短

16.通过EXPLAIN查询上面SQL语句的执行计划:
root@localhost[employees]>explain select count(*),ROUND(salary,-3) as base from salaries where salary between 50000 and 80000 and to_date>NOW() group by base\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: range
possible_keys: salary_value,salary_value_date        #可能使用的索引
          key: salary_value_date                                        #实际使用的索引
      key_len: 7
          ref: NULL
         rows: 1419213                                                #查询扫描的行数
     filtered: 33.33                                                        #表示在执行查询时根据条件筛选行数占比,这也是一个估计值
        Extra: Using where; Using index; Using temporary; Using filesort
1 row in set, 1 warning (0.01 sec)


ERROR:
No query specified
创建索引后,查询扫描的行数变小了(rows: 1419213)

17.在salaries中创建to_date和salary列的索引:
root@localhost[employees]>create index salary_date_value on salaries(to_date,salary);
Query OK, 0 rows affected (9.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

18.执行上面的SQL,查看最好的执行时间:
root@localhost[employees]>select count(*),ROUND(salary,-3) as base from salaries where salary between 50000 and 80000 and to_date>NOW() group by base;
+----------+-------+
| count(*) | base  |
+----------+-------+
|     1791 | 50000 |
|     3826 | 51000 |
|     3873 | 52000 |
|     4123 | 53000 |
|     4241 | 54000 |
|     4497 | 55000 |
|     4758 | 56000 |
|     4875 | 57000 |
|     4862 | 58000 |
|     5221 | 59000 |
|     5226 | 60000 |
|     5428 | 61000 |
|     5414 | 62000 |
|     5416 | 63000 |
|     5695 | 64000 |
|     5469 | 65000 |
|     5767 | 66000 |
|     5886 | 67000 |
|     5803 | 68000 |
|     5852 | 69000 |
|     5741 | 70000 |
|     5509 | 71000 |
|     5457 | 72000 |
|     5261 | 73000 |
|     5256 | 74000 |
|     4969 | 75000 |
|     4711 | 76000 |
|     4545 | 77000 |
|     4379 | 78000 |
|     4136 | 79000 |
|     2046 | 80000 |
+----------+-------+
31 rows in set (0.21 sec)

19.通过EXPLAIN查看上面SQL的执行计划:
root@localhost[employees]>explain select count(*),ROUND(salary,-3) as base from salaries where salary between 50000 and 80000 and to_date>NOW() group by base\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: range
possible_keys: salary_value,salary_value_date,salary_date_value        #可能要使用的索引
          key: salary_date_value                                                                #查询使用的索引
      key_len: 3
          ref: NULL
         rows: 444440                                                                                #查询符合条件的数据需要执行的行
     filtered: 50.00                                                                                    #表示在执行查询时根据条件筛选行数占比,这也是一个估计值
        Extra: Using where; Using index; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
优化器考虑所有三个新索引,并选择 salary_date_value index.它只需要检查大约444440行, 它减少了完成查询所需的额外过滤量 因此,它提高了其整体性能。
总结:
索引中不同列顺序的效果显示,索引的创建是一门艺术和科学,而且你必须总是评估任何变化的结果 

20.删除上面创建的索引:
 root@localhost[employees]>DROP INDEX salary_value ON salaries;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost[employees]>DROP INDEX salary_value_date ON salaries;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost[employees]>DROP INDEX salary_date_value ON salaries;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


 

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS