Bagaimana untuk mengoptimumkan pernyataan SQL dalam MySQL

1. Gambaran Keseluruhan

Semasa proses pembangunan sistem aplikasi, disebabkan jumlah data awal yang kecil, pembangun memberi lebih perhatian kepada pelaksanaan berfungsi apabila menulis pernyataan SQL, apabila sistem aplikasi dilancarkan secara rasmi. sebagai data pengeluaran Dengan pertumbuhan pesat dalam volum, banyak pernyataan SQL telah mula menunjukkan masalah prestasi secara beransur-ansur, dan kesannya terhadap persekitaran pengeluaran menjadi lebih besar dan lebih besar Pada masa ini, pernyataan SQL yang bermasalah ini telah menjadi hambatan keseluruhan prestasi sistem, jadi kita mesti mengoptimumkannya.

2. Gunakan perintah status show untuk memahami kekerapan pelaksanaan pelbagai SQL

Selepas klien MySQL berjaya disambungkan, anda boleh memberikan maklumat status pelayan melalui status [sesi|global] rancangan perintah, atau anda boleh Gunakan perintah mysqladmin extended-status pada sistem pengendalian untuk mendapatkan mesej ini. menunjukkan status [sesi|global] boleh menambah parameter "session" atau "global" seperti yang diperlukan untuk memaparkan keputusan statistik pada peringkat sesi (sambungan semasa) dan keputusan statistik di peringkat global (sejak kali terakhir pangkalan data dimulakan ). Jika tidak ditulis, parameter lalai ialah "sesi".

Arahan berikut memaparkan nilai semua parameter statistik dalam sesi semasa:

-- 查看会话所有统计的值
SHOW STATUS LIKE 'Com_%';
Or
SHOW SESSION STATUS LIKE 'Com_%';

Arahan berikut memaparkan nilai semua statistik parameter dalam global semasa:

--Lihat nilai semua statistik global

SHOW GLOBAL STATUS LIKE 'Com_%';

Com_xxx mewakili bilangan kali setiap pernyataan xxx dilaksanakan . Kami biasanya mengambil berat tentang Parameter statistik berikut:

• Com_select: Bilangan kali untuk melakukan operasi PILIH, hanya 1 akan terkumpul untuk satu pertanyaan.

• Com_insert: Bilangan kali operasi INSERT dilakukan Untuk operasi INSERT masukkan kelompok, hanya satu terkumpul.

• Com_update: Bilangan kali operasi UPDATE dilakukan.

• Com_delete: Bilangan kali untuk melakukan operasi DELETE.

Parameter di atas akan dikumpul untuk semua operasi jadual enjin storan. Parameter ini hanya digunakan pada enjin storan InnoDB, dan algoritma pengumpulannya sedikit berbeza.

• Innodb_rows_read: Bilangan baris yang dikembalikan oleh pertanyaan SELECT.

• Innodb_rows_inserted: Bilangan baris yang disisipkan dengan menjalankan operasi INSERT.

• Innodb_rows_updated: Bilangan baris yang dikemas kini mengikut operasi KEMASKINI.

• Innodb_rows_deleted: Bilangan baris yang dipadamkan oleh operasi DELETE.

Melalui parameter di atas, anda boleh memahami dengan mudah sama ada sistem aplikasi pangkalan data semasa terutamanya berdasarkan operasi sisipan dan kemas kini atau operasi pertanyaan, serta pelaksanaan kasar pelbagai jenis SQL Apakah nisbah. Tanpa mengira komit atau rollback, kiraan operasi kemas kini akan terkumpul dan objek kiraan ialah bilangan pelaksanaan.

Untuk aplikasi transaksi, Com_commit dan Com_rollback boleh digunakan untuk memahami penyerahan transaksi dan rollback Bagi pangkalan data dengan operasi rollback yang sangat kerap, ini mungkin bermakna terdapat masalah dalam penulisan aplikasi. Selain itu, parameter berikut membantu pengguna memahami situasi asas pangkalan data.

• Sambungan: Bilangan percubaan untuk menyambung ke pelayan MySQL.

• Masa aktif: Masa bekerja pelayan.

• Slow_queries: Bilangan pertanyaan perlahan.

3 Cari pernyataan SQL dengan kecekapan pelaksanaan yang rendah

Anda boleh mencari pernyataan SQL dengan kecekapan pelaksanaan yang rendah dalam dua cara berikut.

• Cari pernyataan SQL dengan kecekapan pelaksanaan yang rendah melalui log pertanyaan perlahan Apabila bermula dengan pilihan --log-slow-queries[=file_name], mysqld menulis fail yang mengandungi semua masa pelaksanaan. melebihi fail Log pernyataan SQL untuk long_query_time saat.

• Log pertanyaan perlahan direkodkan selepas pertanyaan selesai, jadi apabila sistem aplikasi mencerminkan masalah kecekapan pelaksanaan, pertanyaan log pertanyaan lambat tidak dapat mengesan masalah tersebut arahan untuk melihat MySQL semasa Benang yang sedang berjalan, termasuk status benang, sama ada untuk mengunci jadual, dsb., boleh menyemak status pelaksanaan SQL dalam masa nyata, dan pada masa yang sama mengoptimumkan beberapa operasi kunci meja.

4 Analisis pelan pelaksanaan SQL yang tidak cekap melalui EXPLAIN

Selepas mencari pernyataan SQL dengan kecekapan pelaksanaan yang rendah, anda boleh menggunakan arahan EXPLAIN atau DESC untuk mendapatkan cara MySQL. dilaksanakan Maklumat pernyataan SELECT, termasuk cara jadual disambungkan dan susunan sambungan semasa pelaksanaan pernyataan SELECT Sebagai contoh, jika anda ingin mengira bilangan semua tangga inventori, anda perlu mengaitkan jadual_stok_barang dan jadual_harga_saham barang, dan lakukan operasi jumlah pada medan_harga_barangan. Pelan pelaksanaan SQL yang sepadan adalah seperti berikut:

EXPLAIN SELECT SUM(sp.Qty)
FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.ID=sp.GoodsStockID;

Seperti yang ditunjukkan dalam. rajah di atas, penjelasan ringkas bagi setiap lajur adalah seperti berikut:

• select_type: mewakili jenis SELECT, nilai biasa ialah:

• SIMPLE (jadual ringkas, iaitu tiada cantuman jadual atau subkueri digunakan).

• PRIMER (pertanyaan utama, iaitu pertanyaan luar), UNION (pernyataan pertanyaan kedua atau seterusnya dalam UNION), ◎SUBQUERY (PILIHAN pertama dalam subquery) )tunggu.

• jadual: Jadual yang mengeluarkan set hasil.

• type：表示表的连接类型，性能由好到差的连接类型为：

• system（表中仅有一行，即常量表）。

• const（单表中最多有一个匹配行，例如primary key或者unique index）。

• eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）。

• ref（与eq_ref类似，区别在于不是使用primary key或者unique index，而是使用普通的索引）。

• ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）。

• index_merge（索引合并优化）。

• unique_subquery（in的后面是一个查询主键字段的子查询）。

• index_subquery（与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询）。

• range（单表中的范围查询）。

• index（对于前面的每一行，都通过查询索引来得到数据）。

• all（对于前面的每一行，都通过全表扫描来得到数据）。

• possible_keys：表示查询时，可能使用的索引。

• key：表示实际使用的索引。

• key_len：索引字段的长度。

• rows：扫描行的数量。

• filtered：返回结果的行占需要读到的行(rows列的值)的百分比。

• Extra：执行情况的说明和描述。

• Using index（此值表示mysql将使用覆盖索引，以避免访问表）。

• Using where（mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。“Using where”有时提示了一种可能性：查询可以从不同的索引中受益。

• Using temporary（mysql 对查询结果排序时会使用临时表）。

• MySQL will apply an external index sorting on the results instead of reading rows from the table in index order.。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成）。

• Range checked for each record(index map: N) （没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的）。

5.确定问题并采取相应的优化措施

经过以上定位步骤，我们基本就可以分析到问题出现的原因。此时我们可以根据情况采取相应的改进措施，进行优化提高语句执行效率。
在上面的例子中，已经可以确认是goods_stock是走主键索引的，但是对goods_stock_price子表的进行了全表扫描导致效率的不理想，那么应该对goods_stock_price表的GoodsStockID字段创建索引，具体命令如下：

-- 创建索引
CREATE INDEX idx_stock_price_1 ON goods_stock_price (GoodsStockID);
-- 附加删除跟查询索引语句
ALTER TABLE goods_stock_price DROP INDEX idx_stock_price_1;
SHOW INDEX FROM goods_stock_price;

创建索引后，我们再看一下这条语句的执行计划，具体如下：

EXPLAIN SELECT SUM(sp.Qty)
FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.ID=sp.GoodsStockID;

可以发现建立索引后对goods_stock_price子表需要扫描的行数明显减少（从 3 行减少到1行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显。

Atas ialah kandungan terperinci Bagaimana untuk mengoptimumkan pernyataan SQL dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!