Pengoptimuman pertanyaan penalaan prestasi MySQL

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql terutamanya isu yang berkaitan tentang pengoptimuman prestasi, termasuk pengoptimuman pertanyaan dan kandungan lain Mari kita lihat bersama-sama .

Pembelajaran yang disyorkan: tutorial video mysql

Sebelum menulis pertanyaan pantas, anda perlu jelas bahawa perkara yang paling penting ialah masa tindak balas , dan anda perlu tahu berapa lama setiap langkah diambil semasa pelaksanaan keseluruhan pernyataan SQL Anda perlu mengetahui langkah-langkah yang merupakan langkah utama yang menyeret ke bawah kecekapan pelaksanaan pertanyaan. Kemudian optimumkan Senario aplikasi yang berbeza mempunyai kaedah pengoptimuman yang berbeza.

1. Sebab pertanyaan lambat

1.

2 CPU

3 🎜>

5. Panggilan sistem

6 Hasilkan maklumat statistik

7 Mengunci masa menunggu

2. Sebab utama prestasi pertanyaan rendah ialah terlalu banyak data yang diakses Beberapa pertanyaan pastinya perlu menapis sejumlah besar data Kami boleh mengoptimumkan dengan mengurangkan jumlah data yang diakses

(1) Sahkan sama ada aplikasi. berada dalam Dapatkan sejumlah besar data lebih daripada yang diperlukan

(2) Sahkan sama ada lapisan pelayan mysql menganalisis sejumlah besar baris data lebih daripada yang diperlukan

2. Sama ada data yang tidak diperlukan diminta daripada pangkalan data

(1) Tanya rekod yang tidak diperlukan (kita sering tersilap berfikir bahawa mysql hanya akan mengembalikan data yang diperlukan. Malah, mysql mengembalikan semua keputusan dahulu dan kemudian melakukan pengiraan. Dalam tabiat pembangunan harian, kita sering menggunakan pilih penyataan untuk pertanyaan dahulu Sebilangan besar hasil, dan kemudian tutup set hasil selepas mendapatkan baris N pertama Kaedah pengoptimuman adalah untuk menambah had selepas pertanyaan)

(2) Kembalikan semua lajur apabila berbilang. jadual dikaitkan (pilih * daripada pelakon dalam sertai pelakon_filem menggunakan ( pelakon_id) sambung dalaman filem menggunakan(id_filem) di mana filem.title='Academy Dinosaur';pilih pelakon.* daripada pelakon...;)

( 3) Sentiasa keluarkan semua lajur (dalam keperluan perusahaan syarikat , dilarang menggunakan pilih *. Walaupun kaedah ini boleh memudahkan pembangunan, ia akan menjejaskan prestasi pertanyaan, jadi cuba jangan gunakannya)

(4) Berulang kali bertanya data yang sama (jika anda perlu berulang kali melaksanakan Pertanyaan yang sama, dan mengembalikan data yang sama setiap kali. Oleh itu, berdasarkan senario aplikasi ini, kami boleh cache bahagian data ini, yang boleh meningkatkan pertanyaan kecekapan)

3. Pengoptimuman proses pelaksanaan

1. Cache pertanyaan

Sebelum menghuraikan pernyataan pertanyaan, jika cache pertanyaan dihidupkan. , maka mysql akan memberi keutamaan untuk menyemak sama ada pertanyaan mencecah data dalam cache pertanyaan Jika jika pertanyaan berlaku pada cache pertanyaan, kebenaran pengguna akan diperiksa sebelum mengembalikan keputusan. mysql akan melangkau semua peringkat dan mendapatkan hasil terus dari cache dan mengembalikannya kepada klien

2 pemprosesan pengoptimuman pertanyaan

Selepas pertanyaan mysql dicache, ia akan. ikuti langkah berikut: menghuraikan SQL, prapemprosesan dan mengoptimumkan pelan pelaksanaan SQL Jika sebarang ralat berlaku dalam langkah ini, boleh menamatkan pertanyaan.

(1) Penghurai tatabahasa dan prapemprosesan mysql menghuraikan pernyataan SQL melalui kata kunci dan menjana pepohon penghuraian mysql akan menggunakan peraturan tatabahasa mysql untuk mengesahkan dan menghuraikan pertanyaan mengesahkan sama ada kata kunci yang salah digunakan atau susunannya betul, dsb. Prapemproses akan selanjutnya menyemak sama ada pokok parse itu sah, seperti sama ada nama jadual dan nama lajur wujud, sama ada terdapat sebarang kesamaran, dan juga mengesahkan kebenaran, dsb. .

( 2) Pengoptimum pertanyaan

Apabila tiada masalah dengan pepohon sintaks, pengoptimum akan menukarnya menjadi pelan pelaksanaan Pernyataan pertanyaan boleh menggunakan banyak kaedah pelaksanaan, dan hasil yang sepadan boleh yang diperoleh pada akhirnya, kaedah pelaksanaan yang berbeza membawa kecekapan yang berbeza.

MySQL menggunakan pengoptimum berasaskan kos Semasa pengoptimuman, ia akan cuba meramalkan kos pertanyaan menggunakan pelan pertanyaan tertentu dan memilih yang mempunyai kos terkecil.

a. pilih count(*) daripada filem_actor; menunjukkan status seperti 'last_query_cost';

Anda boleh melihat bahawa pernyataan pertanyaan ini memerlukan kira-kira 1104 halaman data untuk mencari data yang sepadan dikira melalui satu siri maklumat statistik.

(a) Bilangan halaman dalam setiap jadual atau indeks

(b) Kardinaliti indeks

(c) The panjang baris indeks dan data

(d) Taburan indeks

b Dalam banyak kes, mysql akan memilih pelan pelaksanaan yang salah atas sebab berikut:

(a ) Maklumat statistik tidak tepat (InnoDB tidak dapat mengekalkan maklumat statistik yang tepat tentang bilangan baris dalam jadual data kerana seni bina mvccnya)

(b) Anggaran kos pelan pelaksanaan tidak sama dengan kos pelaksanaan sebenar (kadangkala walaupun pelan pelaksanaan perlu membaca lebih banyak halaman, kosnya lebih kecil, kerana jika halaman ini dibaca secara berurutan Atau jika halaman ini dibaca sudah dalam ingatan, maka kos aksesnya akan menjadi sangat kecil Tahap mysql tidak tahu halaman mana yang ada dalam memori dan mana yang ada pada cakera, jadi mustahil untuk mengetahui berapa kali IO diperlukan semasa pelaksanaan pertanyaan

(c) Pengoptimuman mysql mungkin berbeza daripada apa yang anda fikirkan (pengoptimuman mysql adalah berdasarkan pengoptimuman model kos, tetapi ia mungkin bukan pengoptimuman terpantas)

(d ) mysql tidak pertimbangkan pertanyaan lain yang dilaksanakan serentak

(e) mysql tidak menganggap kos operasi di luar kawalannya (kos melaksanakan prosedur tersimpan atau fungsi yang ditentukan pengguna)

c

(a) Pengoptimuman statik (analisis terus pepohon hurai dan lengkapkan pengoptimuman)

(b) Pengoptimuman dinamik (pengoptimuman dinamik berkaitan dengan konteks pertanyaan dan mungkin juga berkaitan kepada Nilai, berkaitan dengan bilangan baris yang sepadan dengan indeks)

(c) MySQL hanya perlu mengoptimumkan pertanyaan secara statik sekali, tetapi pengoptimuman dinamik perlu dinilai semula setiap kali ia dilaksanakan

d, Jenis pengoptimuman pengoptimum

(a) Takrifkan semula susunan jadual yang berkaitan (perkaitan jadual data tidak selalu dilakukan dalam susunan yang dinyatakan dalam pertanyaan. Ia adalah fungsi penting pengoptimum untuk menentukan susunan perkaitan)

(b) Tukar cantuman luar kepada cantuman dalam adalah lebih cekap daripada cantuman luar

(c) Menggunakan peraturan transformasi yang setara, mysql boleh menggunakan beberapa persamaan. perubahan untuk memudahkan ungkapan Perancangan kesatuan

(d) Optimumkan kiraan(), min(), max() (sama ada indeks dan lajur boleh menjadi nol biasanya boleh membantu mysql mengoptimumkan jenis ungkapan ini: contohnya, untuk mencari nilai minimum Nilai lajur tertentu, anda hanya perlu menanyakan rekod paling kiri indeks, tidak perlu perbandingan imbasan teks penuh)

(e) Anggarkan dan tukarkannya menjadi ungkapan malar, apabila mysql mengesan ungkapan yang boleh ditukar kepada masa yang tetap, ungkapan itu akan sentiasa dianggap sebagai pemalar. (terangkan pilih film.film_id,film_actor.actor_id daripada filem inner join film_actor menggunakan(film_id) di mana film.film_id = 1)

(f) Imbasan liputan indeks, apabila lajur dalam indeks mengandungi semua lajur yang perlu digunakan dalam Lajur pertanyaan, anda boleh menggunakan indeks penutup

(g) pengoptimuman subkueri (mysql boleh menukar subkueri kepada bentuk yang lebih cekap dalam beberapa kes, dengan itu mengurangkan berbilang pertanyaan kepada data berbilang kali. Akses, sebagai contoh, masukkan data yang sering ditanya ke dalam cache)

(h) Penyebaran setara (jika nilai dua lajur dikaitkan dengan persamaan, maka mysql boleh meletakkan keadaan di mana salah satu daripada lajur. Diteruskan kepada yang lain:

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

Di sini medan film_id digunakan untuk perkaitan kesetaraan Lajur film_id bukan sahaja digunakan pada jadual filem tetapi juga pada jadual pelakon_filem

e. Pertanyaan berkaitan

explain select film.film_id from film inner join film_actor using(film_id
 ) where film.film_id > 500 and film_actor.film_id > 500;）

Pertanyaan berkaitan MySQL adalah sangat penting, tetapi sebenarnya strategi untuk melaksanakan pertanyaan berkaitan adalah agak mudah: MySQL melakukan operasi berkaitan gelung bersarang untuk mana-mana persatuan, iaitu , MySQL mula-mula menggelungkan satu baris dalam jadual Data kemudiannya disarangkan ke dalam jadual seterusnya untuk mencari baris yang sepadan, dan seterusnya sehingga baris yang sepadan ditemui dalam semua jadual Kemudian, berdasarkan baris yang sepadan dalam setiap jadual, mysql akan cuba kembalikan lajur yang diperlukan dalam pertanyaan Selepas mencari semua baris yang sepadan dalam jadual yang berkaitan, jika jadual berkaitan terakhir tidak dapat mencari lebih banyak baris, mysql kembali ke jadual berkaitan tahap sebelumnya untuk melihat sama ada lebih banyak rekod yang sepadan boleh ditemui, dan sebagainya. dihidupkan untuk pelaksanaan berulang secara keseluruhan Inilah ideanya, tetapi perlu diingatkan bahawa terdapat banyak variasi dalam proses pelaksanaan sebenar:

f, pengoptimuman pengisihan

Walau apa pun, pengisihan ialah satu operasi yang sangat mahal, jadi dari perspektif prestasi Untuk memulakan, pengisihan harus dielakkan sebanyak mungkin atau sejumlah besar data harus diisih sebanyak mungkin

Adalah disyorkan untuk menggunakan indeks untuk mengisih, tetapi apabila indeks tidak boleh digunakan, MySQL perlu menyusunnya dengan sendirinya Jika jumlah data adalah kecil, ia akan diisih dalam memori Jika jumlah data adalah besar, anda perlu menggunakan cakera, yang dipanggil failsort dalam mysql jumlah data yang hendak diisih adalah lebih kecil daripada penimbal isihan (tunjukkan pembolehubah seperti '%sort_buffer_size%';), mysql menggunakan memori untuk pengisihan pantas , jika memori tidak mencukupi untuk mengisih, maka mysql akan membahagikan pokok itu terlebih dahulu blok, isih setiap blok bebas menggunakan isihan pantas, simpan hasil isihan setiap blok pada cakera, dan kemudian gabungkan blok yang diisih , dan akhirnya mengembalikan hasil isihan Berikut ialah algoritma pengisihan:

(. a) Dua pengisihan penghantaran

Bacaan data pertama ialah membaca medan yang perlu diisih, dan kemudian mengisihnya , kali kedua ialah membaca baris data mengikut hasil yang diisih mengikut keperluan.

Kaedah ini agak tidak cekap Sebabnya apabila membaca data untuk kali kedua, kerana ia telah disusun, semua rekod perlu dibaca pada masa ini, lebih banyak IO secara rawak, dan kos bacaan data akan menjadi lebih tinggi

Kelebihan dua penghantaran adalah untuk menyimpan data sesedikit mungkin semasa pengisihan, supaya penimbal pengisihan dapat menampung sebanyak mungkin baris untuk operasi pengisihan

(b) Pengisihan penghantaran tunggal

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

（c）如何选择

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

四、优化特定类型的查询

1、优化count()查询

count()是特殊的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数。

（1）总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的。

（2）使用近似值

在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值
其实在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。

（3）更复杂的优化

一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

2、优化关联查询

（1）确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序

当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引。

（2）确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

3、优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替

4、优化limit分页

在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的话需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。
要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能。

优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列。

select film_id,description from film order by title limit 50,5;
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

5、优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化

除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高。

6、推荐使用用户自定义变量

用户自定义变量是一个容易被遗忘的mysql特性，但是如果能够用好，在某些场景下可以写出非常高效的查询语句，在查询中混合使用过程化和关系话逻辑的时候，自定义变量会非常有用。
用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都存在。

（1）自定义变量的使用

set @one :=1
set @min_actor :=(select min(actor_id) from actor)
set @last_week :=current_date-interval 1 week;

（2）自定义变量的限制

a、无法使用查询缓存

b、不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句

c、用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信

d、不能显式地声明自定义变量地类型

e、mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行

f、赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号。

g、使用未定义变量不会产生任何语法错误。

（3）自定义变量的使用案例

a、优化排名语句

在给一个变量赋值的同时使用这个变量

select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;

查询获取演过最多电影的前10名演员，然后根据出演电影次数做一个排名

select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;

b、避免重新查询刚刚更新的数据

当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么

update t1 set  lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;
update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;

c、确定取值的顺序

在赋值和读取变量的时候可能是在查询的不同阶段

（a）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;

因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期

（b）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name

当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的  。

（c）解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：

set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;

推荐学习：mysql视频教程

Atas ialah kandungan terperinci Pengoptimuman pertanyaan penalaan prestasi MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!