Ringkasan dan perkongsian idea pengoptimuman pertanyaan lambat mysql

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql terutamanya memperkenalkan isu berkaitan tentang pengoptimuman pertanyaan perlahan, termasuk menggunakan log pertanyaan perlahan untuk mencari SQL pertanyaan perlahan dan menganalisis pertanyaan lambat melalui explain Query SQL dan mengubah suai SQL supaya SQL boleh diindeks sebanyak mungkin. Mari kita lihat bersama-sama.

Pembelajaran yang disyorkan: tutorial video mysql

1 idea pengoptimuman pertanyaan perlahan

Apabila pertanyaan perlahan berlaku, idea pengoptimuman ialah:

• Gunakan log pertanyaan perlahan untuk mencari SQL pertanyaan perlahan

• Gunakan explain untuk menganalisis SQL pertanyaan perlahan

• Ubah suai SQL dan cuba buat indeks SQL

2 Log pertanyaan perlahan

MySQL menyediakan fungsi - log pertanyaan perlahan, yang akan merekodkan SQL pertanyaan yang masanya melebihi ambang masa yang ditetapkan direkodkan dalam log, membolehkan kami mencari pertanyaan perlahan dan mengoptimumkan pernyataan SQL yang sepadan.

Mula-mula semak pembolehubah global yang berkaitan dengan pertanyaan perlahan dalam MySQL:

mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events           | OFF                           |
| ft_query_expansion_limit               | 20                            |
| have_query_cache                       | YES                           |
| log_queries_not_using_indexes          | OFF                           |
| log_throttle_queries_not_using_indexes | 0                             |
==========================================================================
| long_query_time                        | 10.000000                     |【1】慢查询的时间阈值
==========================================================================
| query_alloc_block_size                 | 8192                          |
| query_cache_limit                      | 1048576                       |
| query_cache_min_res_unit               | 4096                          |
| query_cache_size                       | 16777216                      |
| query_cache_type                       | OFF                           |
| query_cache_wlock_invalidate           | OFF                           |
| query_prealloc_size                    | 8192                          |
==========================================================================
| slow_query_log                         | OFF                           |【2】慢查询日志是否开启
| slow_query_log_file                    | /var/lib/mysql/Linux-slow.log |【3】慢查询日志文件存储位置
==========================================================================
+----------------------------------------+-------------------------------+
15 rows in set (0.00 sec)

Di sini kami memberi tumpuan terutamanya pada tiga pembolehubah:

• long_query_time, slow pertanyaan Ambang masa, dalam saat, jika masa pelaksanaan pernyataan SQL melebihi nilai ini, maka MySQL akan menentukannya sebagai pertanyaan perlahan

• slow_query_log, sama ada fungsi log pertanyaan perlahan adalah dihidupkan, ia dimatikan secara lalai Selepas dibuka, rekod pertanyaan perlahan

• slow_query_log_file, lokasi storan fail log pertanyaan perlahan

Fungsi log pertanyaan perlahan lalai dimatikan, jadi kita perlu Dayakan fungsi ini

# 开启慢查询日志
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
# 设置慢查询时间阈值
mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

Selepas menetapkan seperti ini, MySQL akan kehilangan konfigurasi ini apabila ia dimulakan semula, dan ia perlu diubah suai dalam fail konfigurasi untuk berkesan secara kekal.

3 terangkan

Kita boleh gunakan explain untuk menganalisis pelaksanaan pernyataan SQL, sebagai contoh:

mysql> explain select sum(1+2);

Hasil pelaksanaan adalah seperti berikut, anda boleh lihat bahawa terdapat adalah banyak medan

Kami terutamanya melihat beberapa medan penting:

• select_type mewakili jenis pertanyaan bagi pernyataan pertanyaan, termasuk mudah pertanyaan, subkueri, dsb.

• jadual mewakili jadual pertanyaan, yang tidak semestinya wujud. Ia mungkin jadual sementara yang diperoleh dalam pertanyaan ini

  jenis mewakili jenis dapatkan semula, gunakan Imbasan jadual penuh, imbasan indeks, dsb.

• kunci_mungkin mewakili lajur indeks yang boleh digunakan

• kunci mewakili lajur indeks yang sebenarnya digunakan dalam pertanyaan, seperti yang ditunjukkan dalam Pengoptimum pertanyaan memutuskan

3.1 medan pilih_jenis

Medan jenis 3.2

Untuk enjin storan InnoDB, lajur jenis biasanya semua atau indeks.

Mengenai nilai medan jenis, prestasi pelaksanaan SQL yang sepadan secara beransur-ansur menjadi lebih teruk dari atas ke bawah.

3.3 medan tambahan

4 perlahan Contoh pertanyaan

Sediakan data, struktur jadual data:

Menjana 2 juta keping data secara rawak

create table user_info_large (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`account` VARCHAR(20) NOT NULL COMMENT '用户账号',
`name` VARCHAR(20) NOT NULL COMMENT '用户名',
`password` VARCHAR(20) not null COMMENT '用户密码',
`area` VARCHAR(20) NOT NULL COMMENT '用户地址',
`signature` VARCHAR(50) not null COMMENT '个性签名',
PRIMARY KEY (`id`) COMMENT '主键',
UNIQUE (`account`) COMMENT '唯一索引',
KEY `index_area_signture` (`area`,  `signature`)  COMMENT '组合索引'
);

Memintas bahagian data:

mysql> select count(id) from user_info_large;
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.38 sec)

Laksanakan pernyataan SQL berikut tanpa menggunakan sebarang medan indeks:

Masa pertanyaan yang dipaparkan oleh alat Navicat adalah seperti berikut masa MySQL sebenarnya melaksanakan SQL Di Sini Termasuk penghantaran rangkaian dan masa lain:

SELECT name from user_info_large ORDER BY name desc limit 0,100000;

Masa pertanyaan khusus SQL boleh dilihat dalam log pertanyaan perlahan:

Penjelasan tentang beberapa maklumat:

# Time: 2022-09-26T13:44:18.405459Z
# User@Host: root[root] @  [ip]  Id:  1893
# Query_time: 10.162999  Lock_time: 0.000113 Rows_sent: 100000  Rows_examined: 2100000
SET timestamp=1664199858;
SELECT name from user_info_large ORDER BY name desc limit 0,100000;

Masa: Masa mula pelaksanaan SQL

• Query_time: Masa yang dibelanjakan untuk pertanyaan pernyataan SQL, anda boleh lihat bahawa ia mengambil masa 10 saat

• Lock_time: masa untuk menunggu jadual kunci

• Rows_sent: bilangan rekod yang dikembalikan oleh pernyataan

• Rows_examined: dari Bilangan rekod yang dikembalikan dalam enjin storan

• Pertanyaan perlahan yang sedang dilaksanakan tidak akan direkodkan dalam perlahan log pertanyaan Ia hanya akan direkodkan dalam log selepas menunggu pelaksanaannya selesai.

Kami boleh menggunakan senarai proses tunjukkan untuk melihat urutan yang melaksanakan SQL.

Laksanakan kenyataan berikut sekali lagi dan gunakan medan akaun indeks:

Lihat log pertanyaan perlahan dan ia tidak direkodkan.

SELECT account from user_info_large ORDER BY account desc limit 0,100000;

Sekarang gunakan explain untuk melihat pelaksanaan pernyataan SQL:

Analisisnya adalah seperti berikut:

可以看到没有使用到索引，type 为 ALL 表示全表扫描，效率最差，并且 Extra  也是外部排序。

再看看这条 SQL 语句：

explain SELECT account from user_info_large ORDER BY account desc limit 0,100000;

分析情况如下：

type 为 index，使用了索引，使用的索引字段为 account，Extra 显示为使用索引排序。

因此，在实际开发中，我们可以针对慢查询的 SQL，使用 explain 分析语句，根据分析情况以及索引的设计，重新设计 SQL 语句，让 SQL 语句尽量走索引，走合适的索引。

5 优化器与索引

在执行 SQL 时，MySQL 的优化器会根据情况选择索引，但并不能保证其执行时间一定最短，我们可以根据实际情况使用 force key (index) 让 SQL 语句强制走某个索引。

例如，以下语句执行后，key 字段为 account，并没有走主键索引。

explain SELECT count(id) from user_info_large;

如果使用 force key，就可以强制令语句走主键索引。

explain SELECT count(id) from user_info_large force key (PRIMARY);

6 总结

在项目中如果发现部分 SQL 语句执行缓慢，等待查询时间长，可以考虑优化慢查询，具体思路为：

• 通过慢查询日志定位 SQL

• 使用 explain 分析 SQL

• 修改 SQL，令其走合适的索引

 在使用 explain 时，我们主要关注这些字段：

• type

• key

• Extra

在编写 SQL 使用索引的时候，我们尽量注意一下规则：

• 模糊查询不要使用通配符 % 开头，例如 like '%abc'

• 使用 or 关键字时，两边的字段都要有索引。或者使用 union 替代 or

• 使用复合索引遵循最左原则

• 索引字段不要参加表达式运算、函数运算

推荐学习：mysql视频教程

Atas ialah kandungan terperinci Ringkasan dan perkongsian idea pengoptimuman pertanyaan lambat mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!