Rumah >pangkalan data >tutorial mysql >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
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
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.
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.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
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
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 语句尽量走索引,走合适的索引。
在执行 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);
在项目中如果发现部分 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!