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
