☰

如何减少mysql磁盘io？

tag-icon • tag-icon mysql performance io
如何减少mysql磁盘io？ 
mysql> explain SELECT *
    -> FROM (`items`)
    -> WHERE `states` = 'A'
    -> ORDER BY `updated_date` desc
    -> LIMIT 20520, 40;
+----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+
| id | select_type | table  | type   | possible_keys              | key                 | key_len | ref      | rows  | Extra       |
+----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+
|  1 | SIMPLE      | items  | ref    | states_updated_date,states | states_updated_date | 6       | const    | 19040 | Using where |
+----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+


    mysql> describe items;
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| url                 | varchar(255) | NO   | UNI | NULL    |                |
| vendor              | varchar(255) | NO   |     | NULL    |                |
| title               | varchar(255) | NO   |     | NULL    |                |
| price               | int(11)      | YES  |     | NULL    |                |
| contact_seller_link | varchar(255) | NO   |     | NULL    |                |
| seller              | varchar(255) | NO   |     | NULL    |                |
| query_count         | varchar(7)   | NO   |     | NULL    |                |
| warrenty            | varchar(63)  | NO   |     | NULL    |                |
| created_date        | varchar(63)  | NO   |     | NULL    |                |
| notes_count         | varchar(7)   | NO   |     | NULL    |                |
| views_count         | varchar(7)   | NO   |     | NULL    |                |
| effective_date      | varchar(63)  | NO   |     | NULL    |                |
| updated_date        | varchar(63)  | NO   | MUL | NULL    |                |
| images              | text         | NO   |     | NULL    |                |
| description         | text         | NO   |     | NULL    |                |
| extra_comment       | text         | NO   |     | NULL    |                |
| queries             | text         | NO   |     | NULL    |                |
| crawl_date          | datetime     | NO   |     | NULL    |                |
| brand               | varchar(63)  | NO   | MUL | NULL    |                |
| states              | varchar(1)   | YES  | MUL | NULL    |                |
| focal_length_id     | int(9)       | YES  | MUL | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------+

mysql> show index from items;;
+-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name               | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| items |          0 | PRIMARY                |            1 | id              | A         |      124498 |     NULL | NULL   |      | BTREE      |         |
| items |          0 | url                    |            1 | url             | A         |      124498 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | focal_length_id        |            1 | focal_length_id | A         |         669 |     NULL | NULL   | YES  | BTREE      |         |
| items |          1 | focal_length_id_states |            1 | focal_length_id | A         |         308 |     NULL | NULL   | YES  | BTREE      |         |
| items |          1 | focal_length_id_states |            2 | states          | A         |         604 |     NULL | NULL   | YES  | BTREE      |         |
| items |          1 | brand                  |            1 | brand           | A         |          26 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | updated_date           |            1 | updated_date    | A         |      124498 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | states_updated_date    |            1 | states          | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |
| items |          1 | states_updated_date    |            2 | updated_date    | A         |      124498 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | states                 |            1 | states          | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.13 sec)

它每个查询处理 19k 行，数据集约为 40k。我可以通过添加索引来改进它吗？或者增加 mysql 使用的 RAM？

答案1

您需要创建的索引在 WHERE 和 ORDER BY 子句、states 和 updated_date 中给出

ALTER TABLE items ADD INDEX states_updated_date_ndx (states,updated_date);

之后重新运行你的 EXPLAIN 计划。

您还可以通过运行来减小列的大小程序分析()像这样：

SELECT states,updated_date FROM items PROCEDURE ANALYSE();

这不会显示所有行。它只会遍历所有行并确定 states 和 date_updated 列的最佳数据类型。

如果表格项目如果是 MyISAM，则必须增加 key_buffer_size。如果是 InnoDB，则增加 innodb_buffer_pool_size。至少，确保所有缓存都足够大。

试一试 ！！！

答案2

MySQL SELECT 负载应受 CPU 限制而非 I/O 限制。如果受 I/O 限制，则应：

  • 在适当的地方添加索引
  • 优化查询
  • 增加用于缓存表和查询的内存 - 检查缓存命中率。MySQL 调谐器可以帮助您完成这项任务。

答案3

您可以通过将列更改为 CHAR(1) 来减小索引的大小states。较小的索引更适合内存。

答案4

如果没有索引或键，就不要考虑性能。

相关内容