Maison > Article > base de données > Présentation de l'outil d'optimisation des performances de MySQL
Plus de recommandations d'apprentissage gratuites associées : Tutoriel MySQL (Vidéo) Introduction à
MySQL fournit une commande EXPLAIN, qui peut analyser l'instruction SELECT
et afficher les informations détaillées de l'exécution SELECT
pour les développeurs Optimisation ciblée . L'utilisation de la commande
EXPLAIN est très simple. Il suffit d'ajouter Explain avant l'instruction SELECT, par exemple :
EXPLAIN SELECT * from user_info WHERE id < 300;
Afin de faciliter la démonstration de l'utilisation de EXPLAIN. Ensuite, nous devons d'abord créer deux tables pour tester et ajouter les données correspondantes :
CREATE TABLE `user_info` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL DEFAULT '', `age` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name_index` (`name`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 INSERT INTO user_info (name, age) VALUES ('xys', 20); INSERT INTO user_info (name, age) VALUES ('a', 21); INSERT INTO user_info (name, age) VALUES ('b', 23); INSERT INTO user_info (name, age) VALUES ('c', 50); INSERT INTO user_info (name, age) VALUES ('d', 15); INSERT INTO user_info (name, age) VALUES ('e', 20); INSERT INTO user_info (name, age) VALUES ('f', 21); INSERT INTO user_info (name, age) VALUES ('g', 23); INSERT INTO user_info (name, age) VALUES ('h', 50); INSERT INTO user_info (name, age) VALUES ('i', 15);
CREATE TABLE `order_info` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `user_id` BIGINT(20) DEFAULT NULL, `product_name` VARCHAR(50) NOT NULL DEFAULT '', `productor` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL'); INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX'); INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL'); INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA'); INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');
La sortie de la commande EXPLAIN est à peu près la suivante :
mysql> explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
de chaque colonne La signification est la suivante :
Ensuite, concentrons-nous sur le. les plus importants Plusieurs champs.
select_type
représente le type de requête. Ses valeurs communes sont :
La catégorie de requête la plus courante doit être SIMPLE
Par exemple, lorsque notre requête n'a pas de sous-requête ou de requête UNION, elle est généralement du type SIMPLE
, par exemple :
mysql> explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
Si nous utilisons une requête UNION, alors les résultats de sortie EXPLAIN seront similaires au suivant :
mysql> EXPLAIN (SELECT * FROM user_info WHERE id IN (1, 2, 3)) -> UNION -> (SELECT * FROM user_info WHERE id IN (3, 4, 5)); +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where | | 2 | UNION | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
représente la table ou la table dérivée impliquée dans la requête. Le champ
type
est plus important. fournit des informations importantes pour déterminer si la requête est efficace. En fonction de la base. Grâce au champ type
, nous déterminons si la requête est 全表扫描
ou 索引扫描
, etc.
const
Par exemple, la requête suivante utilise un index de clé primaire, donc type
mysql> explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
const
mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: index possible_keys: user_product_detail_index key: user_product_detail_index key_len: 314 ref: NULL rows: 9 filtered: 100.00 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: test.order_info.user_id rows: 1 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec)
=
最左前缀
mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: ref possible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.01 sec)
ref
type
est le plus long des index utilisés dans cette requête range
Par exemple, l'exemple suivant est une requête de plage : ref
key_len
mysql> EXPLAIN SELECT * -> FROM user_info -> WHERE id BETWEEN 2 AND 8 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 7 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
Using index
mysql> EXPLAIN SELECT name FROM user_info \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: index possible_keys: NULL key: name_index key_len: 152 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec).
.
mysql> EXPLAIN SELECT age FROM user_info WHERE age = 20 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
通常来说, 不同的 type 类型的性能关系如下:ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL
类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index
类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.
possible_keys
表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys
中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key
字段决定.
此字段是 MySQL 在当前查询时所真正使用到的索引.
表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:
我们来举两个简单的栗子:
mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: range possible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 ref: NULL rows: 5 filtered: 11.11 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec)
上面的例子是从表 order_info 中查询指定的内容, 而我们从此表的建表语句中可以知道, 表 order_info
有一个联合索引:
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
不过此查询语句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH'
中, 因为先进行 user_id 的范围查询, 而根据 最左前缀匹配
原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有 user_id
, 因此在 EXPLAIN
中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20) NOT NULL DEFAULT '0'
, 则 key_length 应该是8.
上面因为 最左前缀匹配
原则, 我们的查询仅仅使用到了联合索引的 user_id
字段, 因此效率不算高.
接下来我们来看一下下一个例子:
mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: ref possible_keys: user_product_detail_index key: user_product_detail_index key_len: 161 ref: const,const rows: 2 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)<p>这次的查询中, 我们没有使用到范围查询, key_len 的值为 161. 为什么呢? 因为我们的查询条件 <code>WHERE user_id = 1 AND product_name = 'p1'</code> 中, 仅仅使用到了联合索引中的前两个字段, 因此 <code>keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161</code></p> <h3>rows</h3> <p>rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.<br>这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.</p> <h3>Extra</h3> <p>EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:</p> <ul><li>Using filesort<br>当 Extra 中有 <code>Using filesort</code> 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 <code>Using filesort</code>, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.<br>例如下面的例子:</li></ul> <pre class="brush:php;toolbar:false">mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: index possible_keys: NULL key: user_product_detail_index key_len: 253 ref: NULL rows: 9 filtered: 100.00 Extra: Using index; Using filesort 1 row in set, 1 warning (0.00 sec)
我们的索引是
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
但是上面的查询中根据 product_name
来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort
.
如果我们将排序依据改为 ORDER BY user_id, product_name
, 那么就不会出现 Using filesort
了. 例如:
mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: index possible_keys: NULL key: user_product_detail_index key_len: 253 ref: NULL rows: 9 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)
相关免费推荐:编程视频课程
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!