Analisis mendalam tentang pengoptimum pertanyaan dalam MySQL (penjelasan terperinci tentang prinsip kerja)

青灯夜游
Lepaskan: 2022-09-29 20:50:20
ke hadapan
3498 orang telah melayarinya

Artikel ini akan memberi anda analisis mendalam tentang pengoptimum pertanyaan dalam MySQL dan membantu anda memahami prinsip kerja pengoptimum pertanyaan mysql saya harap ia akan membantu anda!

Analisis mendalam tentang pengoptimum pertanyaan dalam MySQL (penjelasan terperinci tentang prinsip kerja)

Untuk pernyataan SQL, pengoptimum pertanyaan terlebih dahulu menyemak sama ada ia boleh ditukar menjadi JOIN, dan kemudian mengoptimumkan JOIN

Pengoptimuman dibahagikan kepada: 1. Pengoptimuman Syarat, 2. Kira kos imbasan jadual penuh, 3. Cari semua indeks yang boleh digunakan, 4. Kira kos kaedah akses berbeza untuk setiap indeks, 5. Pilih indeks dengan kos dan akses terkecil kaedah

1 Hidupkan log pengoptimum pertanyaan

-- 开启
set optimizer_trace="enabled=on"; 
-- 执行sql
-- 查看日志信息
select * from information_schema.OPTIMIZER_TRACE;
-- 关闭
set optimizer_trace="enabled=off";
Salin selepas log masuk

Prinsip Pengoptimum

  • 1. Pemindahan Pemalar (penyebaran_pemalar)
    a = 1 DAN b > sql di atas boleh ditukar kepada:
    a = 1 DAN b > >
    2. Pemindahan nilai sama (equality_propagation)

    a = b dan b = c dan c = 5
  • Sql di atas boleh ditukar kepada:
  • a = 5 dan b = 5 dan c = 5




    3. Alih keluar keadaan tidak berguna (penyingkiran_keadaan remeh)

    a = 1 dan 1 = 1
  • Sql di atas boleh ditukar kepada:
  • a = 1




    4 Berdasarkan kos

    Pertanyaan boleh mempunyai rancangan pelaksanaan yang berbeza Anda boleh memilih indeks untuk membuat pertanyaan, atau anda boleh memilih imbasan jadual penuh -pelan kos untuk melaksanakan pertanyaan.
  • 1) Kos I/O
  • Enjin storan InnoDB menyimpan kedua-dua data dan indeks pada cakera Apabila kita ingin menanyakan rekod dalam jadual, kita perlu memuatkan data atau indeks ke dalam memori terlebih dahulu dan kemudian beroperasi. Masa yang hilang dalam proses pemuatan dari cakera ke memori dipanggil kos I/O

    2) Kos CPU
    Kos membaca dan menyemak sama ada rekod memenuhi syarat carian yang sepadan, menyusun set hasil, dsb. Masa dipanggil kos CPU.
    Enjin storan InnoDB menetapkan bahawa kos lalai untuk membaca halaman ialah 1.0, dan kos lalai untuk membaca dan menyemak sama ada rekod memenuhi syarat carian ialah 0.2.



    3. Langkah pengoptimuman berasaskan kos

  • Sebelum pernyataan pertanyaan satu jadual sebenarnya dilaksanakan, pengoptimum pertanyaan MySQL akan mengetahui semua pilihan yang mungkin digunakan untuk melaksanakan pernyataan . Selepas membandingkan penyelesaian, cari penyelesaian kos terendah ini ialah apa yang dipanggil pelan pelaksanaan Hanya selepas itu antara muka yang disediakan oleh enjin storan akan dipanggil untuk melaksanakan pertanyaan.
Mari analisa langkah-langkah ini dengan contoh Pernyataan pertanyaan jadual tunggal adalah seperti berikut:


1. Cari semua indeks yang mungkin mengikut syarat carian
select * from employees.titles where emp_no > &#39;10101&#39; and emp_no < &#39;20000&#39; and to_date = &#39;1991-10-10&#39;;
Salin selepas log masuk

• emp_no > '10101', syarat carian ini boleh menggunakan indeks kunci utama PRIMER. • to_date = ‘1991-10-10’, syarat carian ini boleh menggunakan indeks sekunder idx_titles_to_date.

Ringkasnya, indeks yang boleh digunakan dalam pernyataan pertanyaan di atas, iaitu satu-satunya kunci yang mungkin adalah PRIMER dan idx_titles_to_date.


2 Kira kos imbasan jadual penuh

Untuk enjin storan InnoDB, imbasan jadual penuh bermaksud meletakkan indeks berkelompok ke dalam The. rekod dibandingkan dengan keadaan carian yang diberikan secara bergilir-gilir, dan rekod yang memenuhi syarat carian ditambahkan pada set hasil Oleh itu, halaman yang sepadan dengan indeks berkelompok perlu dimuatkan ke dalam memori, dan kemudian ia diperiksa sama ada. rekod memenuhi syarat carian. Memandangkan kos pertanyaan = kos I/O kos CPU, dua maklumat diperlukan untuk mengira kos imbasan jadual penuh: 1) Bilangan halaman yang diduduki oleh indeks berkelompok

2) Bilangan halaman dalam jadual Bilangan rekod

MySQL mengekalkan satu siri maklumat statistik untuk setiap jadual Gunakan pernyataan SHOW TABLE STATUS untuk melihat maklumat statistik jadual.

Baris

SHOW TABLE STATUS LIKE &#39;titles&#39;;
Salin selepas log masuk
mewakili bilangan rekod dalam jadual. Nilai ini adalah tepat untuk jadual yang menggunakan enjin storan MyISAM dan merupakan anggaran untuk jadual yang menggunakan enjin storan InnoDB.

Data_length

menunjukkan bilangan bait ruang storan yang diduduki oleh jadual. Untuk jadual yang menggunakan enjin storan MyISAM, nilai ini ialah saiz fail data Untuk jadual yang menggunakan enjin storan InnoDB, nilai ini bersamaan dengan ruang storan yang diduduki oleh indeks berkelompok, yang bermaksud bahawa nilai boleh dikira seperti ini. Saiz:

Tajuk kami menggunakan saiz halaman lalai sebanyak 16KB, dan hasil pertanyaan di atas menunjukkan bahawa nilai Panjang_Data ialah 20512768, jadi kami boleh menyimpulkan secara terbalik bilangan halaman dalam indeks berkelompok:

Data_length = 聚簇索引的页面数量 x 每个页面的大小
Salin selepas log masuk
Kami kini mempunyai anggaran bilangan halaman yang diduduki oleh indeks berkelompok dan bilangan rekod dalam jadual, jadi kami boleh mengira kos imbasan jadual penuh. Tetapi MySQL membuat beberapa penalaan halus apabila ia berkaitan dengan pengiraan kos sebenarnya.

Kos I/O: 1252
聚簇索引的页面数量 = Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252
Salin selepas log masuk
1 = 1252. 1252 merujuk kepada bilangan halaman yang diduduki oleh indeks berkelompok dan 1.0 merujuk kepada pemalar kos untuk memuatkan halaman.

Kos CPU: 4420700.2=88414 442070 merujuk kepada bilangan rekod dalam jadual dalam statistik, yang merupakan anggaran untuk enjin storan InnoDB dan 0.2 merujuk. kepada Pemalar kos yang diperlukan untuk mengakses rekod

Jumlah kos: 1252 88414 = 89666. Ringkasnya, jumlah kos mengimbas jadual penuh tajuk ialah 89666.

Analisis mendalam tentang pengoptimum pertanyaan dalam MySQL (penjelasan terperinci tentang prinsip kerja)
我们前边说过表中的记录其实都存储在聚簇索引对应B+树的叶子节点中,所以只要我们通过根节点获得了最左边的叶子节点,就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程其实有的B+树内节点是不需要访问的,但是MySQL在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算I/O成本的依据,是不区分内节点和叶子节点的。

3、计算PRIMARY需要成本

计算PRIMARY需要多少成本的关键问题是:需要预估出根据对应的where条件在主键索引B+树中存在多少条符合条件的记录。

范围区间数
当我们从索引中查询记录时,不管是=、in、>、<这些操作都需要从索引中确定一个范围,不论这个范围区间的索引到底占用了多少页面,查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。
本例中使用PRIMARY的范围区间只有一个:(10101, 20000),所以相当于访问这个范围区间的索引付出的I/O成本就是:1 x 1.0 = 1.0

预估范围内的记录数
优化器需要计算索引的某个范围区间到底包含多少条记录,对于本例来说就是要计算PRIMARY在(10101, 20000)这个范围区间中包含多少条数据记录,计算过程是这样的:

步骤1:先根据emp_no > 10101这个条件访问一下PRIMARY对应的B+树索引,找到满足emp_no > 10101这个条件的第一条记录,我们把这条记录称之为区间最左记录。

步骤2:然后再根据emp_no < 20000这个条件继续从PRIMARY对应的B+树索引中找出第一条满足这个条件的记录,我们把这条记录称之为区间最右记录。

步骤3:如果区间最左记录和区间最右记录相隔不太远(只要相隔不大于10个页面即可),那就可以精确统计出满足emp_no > '10101' and emp_no < '20000'条件的记录条数。否则只沿着区间最左记录向右读10个页面,计算平均每个页面中包含多少记录,然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了,怎么估计区间最左记录和区间最右记录之间有多少个页面呢?计算它们父节点中对应的目录项记录之间隔着几条记录就可以了。

根据上面的步骤可以算出来PRIMARY索引的记录条数,所以读取记录的CPU成本为:26808*0.2=5361.6,其中26808是预估的需要读取的数据记录条数,0.2是读取一条记录成本常数。

PRIMARY的总成本
确定访问的IO成本+过滤数据的CPU成本=1+5361.6=5362.6

Analisis mendalam tentang pengoptimum pertanyaan dalam MySQL (penjelasan terperinci tentang prinsip kerja)

4、计算idx_titles_to_date需要成本

Analisis mendalam tentang pengoptimum pertanyaan dalam MySQL (penjelasan terperinci tentang prinsip kerja)
因为通过二级索引查询需要回表,所以在计算二级索引需要成本时还要加上回表的成本,而回表的成本就相当于下面这个SQL执行:

select * from employees.titles where 主键字段 in (主键值1,主键值2,。。。,主键值3);
Salin selepas log masuk

所以idx_titles_to_date的成本 = 辅助索引的查询成本 + 回表查询的成本

5、比较各成本选出最优者

选择成本最小的索引

四、基于索引统计数据的成本计算

有时候使用索引执行查询时会有许多单点区间,比如使用IN语句就很容易产生非常多的单点区间,比如下边这个查询:

select * from employees.titles where to_date in (&#39;a&#39;,&#39;b&#39;,&#39;c&#39;,&#39;d&#39;, ..., &#39;e&#39;);
Salin selepas log masuk

很显然,这个查询可能使用到的索引就是idx_titles_to_date,由于这个索引并不是唯一二级索引,所以并不能确定一个单点区间对应的二级索引记录的条数有多少,需要我们去计算。计算方式我们上边已经介绍过了,就是先获取索引对应的B+树的区间最左记录和区间最右记录,然后再计算这两条记录之间有多少记录(记录条数少的时候可以做到精确计算,多的时候只能估算)。这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index pe

如果只有几个单点区间的话,使用index pe的方式去计算这些单点区间对应的记录数也不是什么问题,可是如果很多呢,比如有20000次,MySQL的查询优化器为了计算这些单点区间对应的索引记录条数,要进行20000次index pe操作,那么这种情况下是很耗性能的,所以MySQL提供了一个系统变量eq_range_index_pe_limit,我们看一下这个系统变量的默认值:SHOW VARIABLES LIKE ‘%pe%’;为200

也就是说如果我们的IN语句中的参数个数小于200个的话,将使用index pe的方式计算各个单点区间对应的记录条数,如果大于或等于200个的话,可就不能使用index pe了,要使用所谓的索引统计数据来进行估算。像会为每个表维护一份统计数据一样,MySQL也会为表中的每一个索引维护一份统计数据,查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法。

Cardinality属性表示索引列中不重复值的个数。比如对于一个一万行记录的表来说,某个索引列的Cardinality属性是10000,那意味着该列中没有重复的值,如果Cardinality属性是1的话,就意味着该列的值全部是重复的。不过需要注意的是,对于InnoDB存储引擎来说,使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值,并不是精确的。可以根据这个属性来估算IN语句中的参数所对应的记录数:

1)使用SHOW TABLE STATUS展示出的Rows值,也就是一个表中有多少条记录。

2)使用SHOW INDEX语句展示出的Cardinality属性。

3)根据上面两个值可以算出idx_key1索引对于的key1列平均单个值的重复次数:Rows/Cardinality

4)所以总共需要回表的记录数就是:IN语句中的参数个数*Rows/Cardinality。

NULL值处理
上面知道在统计列不重复值的时候,会影响到查询优化器。
对于NULL,有三种理解方式:

  • NULL值代表一个未确定的值,每一个NULL值都是独一无二的,在统计列不重复值的时候应该都当作独立的。

  • NULL值在业务上就是代表没有,所有的NULL值代表的意义是一样的,所以所有的NULL值都一样,在统计列不重复值的时候应该只算一个。

  • NULL完全没有意义,在统计列不重复值的时候应该忽略NULL。
    innodb提供了一个系统变量:

show global variables like &#39;%innodb_stats_method%&#39;;
Salin selepas log masuk

这个变量有三个值:

  • nulls_equal:认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。如果某个索引列中NULL值特别多的话,这种统计方式会让优化器认为某个列中平均一个值重复次数特别多,所以倾向于不使用索引进行访问。

  • nulls_unequal:认为所有NULL值都是不相等的。如果某个索引列中NULL值特别多的话,这种统计方式会让优化器认为某个列中平均一个值重复次数特别少,所以倾向于使用索引进行访问。

  • nulls_ignored:直接把NULL值忽略掉。

最好不在索引列中存放NULL值才是正解

五、统计数据

InnoDB提供了两种存储统计数据的方式:

• 统计数据存储在磁盘上。

• 统计数据存储在内存中,当服务器关闭时这些这些统计数据就都被清除掉了。

MySQL给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前,innodb_stats_persistent的值默认是OFF,也就是说InnoDB的统计数据默认是存储到内存的,之后的版本中innodb_stats_persistent的值默认是ON,也就是统计数据默认被存储到磁盘中。

不过InnoDB默认是以表为单位来收集和存储统计数据的,也就是说我们可以把某些表的统计数据(以及该表的索引统计数据)存储在磁盘上,把另一些表的统计数据存储在内存中。我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式。

  • 1、基于磁盘的永久性统计数据
    当我们选择把某个表以及该表索引的统计数据存放到磁盘上时,实际上是把这些统计数据存储到了两个表里:
    • innodb_table_stats存储了关于表的统计数据,每一条记录对应着一个表的统计数据
    • innodb_index_stats存储了关于索引的统计数据,每一条记录对应着一个索引的一个统计项的统计数据
  • 2、定期更新统计数据
    • 系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据,它的默认值是ON,也就是该功能默认是开启的。每个表都维护了一个变量,该变量记录着对该表进行增删改的记录条数,如果发生变动的记录数量超过了表大小的10%,并且自动重新计算统计数据的功能是打开的,那么服务器会重新进行一次统计数据的计算,并且更新innodb_table_stats和innodb_index_stats表。不过自动重新计算统计数据的过程是异步发生的,也就是即使表中变动的记录数超过了10%,自动重新计算统计数据也不会立即发生,可能会延迟几秒才会进行计算。
    •如果innodb_stats_auto_recalc系统变量的值为OFF的话,我们也可以手动调用ANALYZE TABLE语句来重新计算统计数据。ANALYZE TABLE single_table;
  • 3、控制执行计划
    Index Hints
    •USE INDEX:限制索引的使用范围,在数据表里建立了很多索引,当MySQL对索引进行选择时,这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引,而不是全部的索引,这就需要用到USE INDEX对查询语句进行设置。
    •IGNORE INDEX :限制不使用索引的范围
    •FORCE INDEX:我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引,因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。
    基本语法格式:
SELECT * FROM table1 USE|IGNORE|FORCE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3
Salin selepas log masuk

【相关推荐:mysql视频教程

Atas ialah kandungan terperinci Analisis mendalam tentang pengoptimum pertanyaan dalam MySQL (penjelasan terperinci tentang prinsip kerja). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!

Label berkaitan:
sumber:csdn.net
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan