AG9.1使用索引提高查询性能
2020-04-17 本文已影响0人
金桔数科
1.在employees数据库里运行如下查询，注意需要消耗多长时间。
SELECT emp_no, title FROM titles
WHERE title='Manager' AND to_date > NOW();
在一个linux终端提示符输入如下命令然后收到如下结果:
# mysql -uroot -p
Enter password: oracle
Welcome to the MySQL monitor. Commands end with ; or \g. ...
mysql>
在mysql提示符下输入如下语句和收到如下结果:
mysql> USE employees;
...
Database changed
mysql> 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.14 sec)
在上面的输出，这个语句执行了0.14秒，你可能的输出会不同。
2.在先前的语句运行explain命令.注意结尾你能看到的优化器索引选择和执行这个语句需要检查行数。
在mysql提示符输入下面的语句和收到结果如下。
mysql> 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: 426826
filtered: 3.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
这个优化器没有识别出任何可能的索引，因此它必须解释表里所有的行。这row列的值是title表实际行数的近似值。基于innodb索引的统计数字。type列的值为all表明优化器必须执行一个全表扫描。
3.在title列创建一个索引。
在mysql提示符输入如下语句和收到结果显示。
mysql> CREATE INDEX titles_title ON titles(title);
Query OK, 0 rows affected (10.79 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.再一次运行这个语句和注意它要消耗多长时间。
在mysql提示符输入如下语句和收到结果显示如下:
mysql> SELECT emp_no, title FROM titles
-> WHERE title='Manager' AND to_date > NOW();
+--------+---------+
| emp_no | title |
+--------+---------+
| 110039 | Manager |
...
| 111939 | Manager |
+--------+---------+
9 rows in set (0.00 sec)
在上面的输出，这个语句执行少于0.01秒，这个显示结果比之前快了相当多。
5.再一次运行explain命令和注意结尾处有的新索引。
在mysql提示符输入下面的语句和收到结果展示如下:
mysql> 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: 52
ref: const
rows: 24
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
这个优化器使用新索引。这个优化器估算出它基于ref类型查询和一个const值(为Manage)必须检查仅24行，和使用剩余where子句条件过滤出为行数的大约三分之一（9）。
6.添加另一个索引，以一个顺序添加titles表里title和to_date的列。
在mysql提示符输入如下语句和返回下面结果:
mysql> CREATE INDEX titles_title_date ON titles(title, to_date);
Query OK, 0 rows affected (##.## sec)
Records: 0 Duplicates: 0 Warnings: 0
7.依照第四步骤再一次在语句上运行explain命令，然后注意跟上次优化器输出有任何不同的地方。
在mysql提示符输入如下语句及返回下面结果:
mysql> 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
possible_keys: titles_title,titles_title_date
key: titles_title_date
key_len: 56
ref: NULL
rows: 9
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
这个优化器建议两个新的索引，然后选择titles_title_date索引。它必须检查的行只有9行，以及它不执行任何条件的过滤。它选择使用一个rang类型查询因此当你使用一个例如大于操作的不等对比时它不能与索引值进行相等对比。
8.删除这两个新索引。
在mysql提示符输入如下语句及返回下面结果:
mysql> DROP INDEX titles_title ON titles;
Query OK, 0 rows affected (#.## sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX titles_title_date ON titles;
Query OK, 0 rows affected (#.## sec)
Records: 0 Duplicates: 0 Warnings: 0
9.执行如下查询，然后注意消耗的时间:
SELECT COUNT(*), ROUND(salary, -3) AS `base` FROM salaries
WHERE salary BETWEEN 50000 AND 80000
AND to_date > NOW()
GROUP BY base;
在mysql提示符输入如下语句及返回下面结果:
mysql> 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 |
...
| 4136 | 79000 |
| 2046 | 80000 |
+----------+-------+
31 rows in set (1.10 sec)
上面的查询输出花了1.10秒。这个时间可能跟你的输出不同。
这个查询生成了一个以1000美元为一级在50000美元和80000美元之间的员工数矩阵图。如下图表是数据的可视化。
10.在之前的语句上运行explain命令。注意你收到关于优化器索引选择和执行语句它必须检查多少行数的结论。
在mysql提示符输入如下语句和收到结果反馈如下:
mysql> 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)
当它运行这个查询优化器不能使用任何索引，当执行一个全表扫描必须检查大约2838426行。
11.在salary列上添加一个索引，这个列是在where子句上的一个列。
在mysql提示符上输入如下语句然后返回如下结果:
mysql> CREATE INDEX salary_value
-> ON salaries(salary);
Query OK, 0 rows affected (##.## sec)
Records: 0 Duplicates: 0 Warnings: 0
12.再一次执行这个查询。注意要花多长时间。
在mysql提示符里输入如下语句然后收到结果展示:
mysql> 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 |
...
| 4136 | 79000 |
| 2046 | 80000 |
+----------+-------+
31 rows in set (0.89 sec)
上面这个查询输出花了0.89秒，这个比你之前记录的时间没有显著提高。
13.在一次对这个语句执行explain命令然后注意任何你收到任何关于新索引的结果。
在mysql提示符下输入如下语句和收到结果反馈如下:
mysql> 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: 2838912
filtered: 16.66
Extra: Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
这个优化器建议salary_value索引但是没有使用它.它选择执行一个全表扫描。它表明在salary列上的索引对这个查询没有用途。
14.按照这个顺序在salary和to_date列上创建一个索引。
在mysql 提示符输入如下语句然后收到如下结果:
mysql> CREATE INDEX salary_value_date
-> ON salaries(salary, to_date);
Query OK, 0 rows affected (##.## sec)
Records: 0 Duplicates: 0 Warnings: 0
考虑语句的where子句同时使用salary和to_date值，因此优化器会考虑这个索引。
15.再一次执行这个查询，注意它要花多长时间。
在mysql提示符输入如下语句和收到结果如下:
mysql> 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 |
...
| 4136 | 79000 |
| 2046 | 80000 |
+----------+-------+
31 rows in set (0.44 sec)
这个查询上面输出花了0.44秒，可能比之前执行快了一点。
16.在这个语句上再一次运行explain命令和注意到你收到任何关于新索引的信息。
在mysql提示符输入如下语句然后收到结果展示如下:
mysql> 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: 1419456
filtered: 33.33
Extra: Using where; Using index; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
这个优化器考虑两个新索引并选择了salary_value_date索引，而不是执行一个表扫描(之前explain输出)，它现在执行一个range类型查询，并且必须检查的行数仅仅大约1419456行，滤出三分之一。
17.按照这个顺序在to_date和salary列上创建一个索引。
在mysql提示符上输入以下语句和收到结果显示。
mysql> CREATE INDEX salary_date_value
-> ON salaries(to_date, salary);
Query OK, 0 rows affected (##.## sec)
Records: 0 Duplicates: 0 Warnings: 0
注意:这个索引与你在14步创建的索引包含相同的列，但是顺序不同。
18.再一次执行这个查询，注意花了多长时间。
在mysql提示符输入如下语句并且收到结果显示:
mysql> 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 |
...
| 4136 | 79000 |
| 2046 | 80000 |
+----------+-------+
31 rows in set (0.10 sec)
19.在这个语句上再一次运行explain命令并注意与之前优化器输出有任何不同的地方。
在mysql提示符输入如下语句并收到结果显示:
mysql> 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: 445984
filtered: 50.00
Extra: Using where; Using index; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
这个优化器考虑所有三个新索引并选择salary_date_value索引。它必须检查的行数大约445984行，降低需要完成查询的需要额外过滤总数因此提高总体性能。
索引中列的不同顺序的结果显示索引创建部分是艺术同时也是科学，同时你必须总是要去评估任何改变带来的结果。
20.删除本次练习中你创建的索引。
在mysql提示符输入如下语句且收到结果显示:
mysql> DROP INDEX salary_value ON salaries;
Query OK, 0 rows affected (#.## sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX salary_value_date ON salaries;
Query OK, 0 rows affected (#.## sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX salary_date_value ON salaries;
Query OK, 0 rows affected (#.## sec)
Records: 0 Duplicates: 0 Warnings: 0
