Kongsi maklumat berguna! Ringkasan analisis praktikal pertanyaan lambat MySQL

Pertanyaan perlahan MySQL, yang nama penuhnya ialah log pertanyaan perlahan, ialah rekod log yang disediakan oleh MySQL, yang digunakan untuk merekodkan penyataan yang masa tindak balasnya melebihi ambang dalam MySQL. Kami akan memperkenalkan statik, dan anda boleh merujuknya jika perlu.

1 Mengapakah kita perlu melakukan ini?

1 Apakah SQL yang perlahan?

Ini merujuk kepada pertanyaan lambat MySQL, khususnya merujuk kepada SQL yang masa berjalannya melebihi nilai long_query_time.

Kita sering mendengar bahawa log binari MySQL biasa termasuk binlog, relay log relay, buat semula log rollback redolog, undolog, dll. Untuk pertanyaan perlahan, terdapat juga log pertanyaan perlahan, slowlog, yang digunakan untuk merekodkan kenyataan yang masa tindak balasnya melebihi ambang dalam MySQL.

Jangan terpedaya dengan nama pertanyaan lambat, memikirkan bahawa log pertanyaan lambat hanya akan merekodkan penyata pilihan Malah, ia juga akan merekodkan penyata sisipan, kemas kini dan penyata DML lain yang masa pelaksanaannya melebihi ambang yang ditetapkan. oleh long_query_time.

# 查看慢SQL是否开启
show variables like "slow_query_log%";

# 查看慢查询设定的阈值 单位:秒
show variables like "long_query_time";

Untuk AliSQL-X-Cluster yang kami gunakan, iaitu XDB, pertanyaan perlahan didayakan secara lalai dan long_query_time ditetapkan kepada 1 saat.

2 Mengapa pertanyaan lambat menyebabkan kegagalan?

SQL yang sangat perlahan selalunya disertai dengan sejumlah besar imbasan baris, pengisihan fail sementara atau pemadaman cakera yang kerap Kesan langsung ialah IO cakera meningkat, SQL biasa juga menjadi SQL yang perlahan dan masa pelaksanaan berskala besar. keluar.

Selepas Double 11 tahun lepas, sebagai tindak balas kepada masalah yang didedahkan dari segi teknikal, barisan CTO Cainiao melancarkan beberapa projek tadbir urus khas masing-masing menerima satu sebagai penaja projek khas tadbir urus SQL yang perlahan.

2 Sejauh mana

1 Bagaimana untuk mengukur keterukan SQL perlahan dalam aplikasi?

Purata mikro

sum(aone应用慢SQL执行次数)
-----------------------
sum(aone应用SQL执行次数)

Kami percaya bahawa semakin besar nilai, semakin besar impaknya; jadi kecil.

Kes yang melampau ialah setiap SQL yang dilaksanakan dalam aplikasi semuanya adalah SQL yang perlahan, dan nilainya ialah 1; setiap SQL yang dilaksanakan dalam aplikasi bukanlah SQL yang perlahan, dan nilainya ialah 0.

Namun, masalah yang dibawa oleh penunjuk ini ialah diskriminasi yang tidak baik, terutamanya untuk SQL QPS adalah sangat tinggi dan dalam kebanyakan kes, SQL bukan pertanyaan yang lambat sekali-sekala akan terharu.

Persoalan lain, adakah SQL yang perlahan sekali-sekala benar-benar lambat SQL? Kami menghadapi banyak SQL yang direkodkan dalam log pertanyaan perlahan Malah, ia mungkin dipengaruhi oleh SQL lain yang perlahan, jitter cakera MySQL, pemilihan pengoptimum dan sebab lain, supaya prestasi pertanyaan biasa jelas bukan SQL yang perlahan tetapi. menjadi SQL perlahan.

Purata Makro

sum(慢SQL 1执行次数)    sum(慢SQL n执行次数)
-----------------  +  ------------------
sum(SQL 1执行次数)      sum(SQL n执行次数)
---------------------------------------
                   n

Algoritma ini berdasarkan fakta bahawa SQL yang lambat ditangkap mempunyai bilangan pelaksanaan tertentu, yang boleh mengurangkan kesan lambat palsu SQL.

Apabila QPS sesetengah aplikasi sangat rendah, iaitu bilangan kali SQL dilaksanakan sehari adalah sangat kecil, jika SQL palsu ditemui, ralat statistik akan berlaku.

Bilangan pelaksanaan

sum(aone应用慢SQL执行次数)
-----------------------
           7

Mengira purata bilangan pelaksanaan SQL yang perlahan setiap hari dalam minggu lalu boleh menghapuskan masalah SQL palsu yang disebabkan oleh makro purata.

Bilangan templat SQL perlahan

Dimensi di atas semuanya mempunyai had masa Untuk mengesan pemprosesan sejarah SQL perlahan, kami juga memperkenalkan SQL global perlahan dimensi kuantiti templat.

count(distinct(aone应用慢SQL模板) )

2 Matlamat

• Aplikasi teras: Selesaikan semua SQL yang perlahan

• Aplikasi biasa: Penunjuk purata mikro menurun sebanyak 50%

3 laporan CTO

berdasarkan CTO -D ialah unit berdasarkan purata wajaran statistik penunjuk pelbagai dimensi dan aplikasi ringkasan di atas, kedudukan dari rendah ke tinggi, menyerlahkan tiga teratas dan penyiaran setiap minggu.

3 Mengapa saya perlu melakukannya

Tekaan mungkin berkaitan dengan latar belakang saya. Saya mempunyai latar belakang C/C dan bertanggungjawab untuk reka bentuk dan pelaksanaan multi jarak jauh peringkat syarikat -seni bina aktif di syarikat saya sebelum ini saya tahu serba sedikit tentang MySQL.

Selain itu, ia mungkin tiada kaitan dengan minat Perniagaan pasukan kecil saya baru sahaja bermula, dan tidak ada SQL yang perlahan, jadi ia boleh dimasukkan ke dalam pelbagai bidang perniagaan.

Empat Sokongan Tindakan

1 Protokol MySQL Kumpulan

Petikan daripada protokol indeks:

[Mandatori 】Penyertaan lebih daripada tiga meja adalah dilarang. Jenis data medan yang perlu dicantumkan mestilah benar-benar konsisten apabila menanyakan korelasi berbilang jadual, pastikan medan yang dikorelasikan perlu mempunyai indeks.

Nota: Walaupun anda menyertai jadual berganda, anda mesti memberi perhatian kepada indeks jadual dan prestasi SQL.

[Mandatori] Apabila membuat indeks pada medan varchar, panjang indeks mesti ditentukan Ia tidak perlu mengindeks keseluruhan medan Panjang indeks ditentukan berdasarkan perbezaan teks sebenar.

Nota: Panjang dan perbezaan indeks ialah sepasang percanggahan Secara amnya, untuk data jenis rentetan, perbezaan akan setinggi 90% untuk indeks dengan panjang 20. Anda boleh menggunakan kiraan. (kiri yang berbeza(nama lajur , ditentukan oleh perbezaan panjang indeks))/bilangan(*).

【Mandatori】Kabur kiri atau kabur penuh adalah dilarang sama sekali dalam carian halaman Jika perlu, sila gunakan enjin carian untuk menyelesaikan masalah.

Nota: Fail indeks mempunyai ciri padanan awalan paling kiri B-Tree Jika nilai di sebelah kiri tidak ditentukan, indeks ini tidak boleh digunakan.

[Disyorkan] Cegah penukaran tersirat yang disebabkan oleh jenis medan yang berbeza, menyebabkan kegagalan indeks.

[Rujukan] Elakkan salah faham melampau berikut semasa membuat indeks:

1) Lebih baik mempunyai terlalu banyak indeks daripada kekurangannya

认为一个查询就需要建一个索引。

2） 吝啬索引的创建

认为索引会消耗空间、严重拖慢更新和新增速度。

3） 抵制唯一索引

认为唯一索引一律需要在应用层通过“先查后插”方式解决。

2 DB变更标准

DDL需要控制变更速度，注意灰度和并发控制，变更发布需要在规定的变更发布窗口内。

五 分享一些我参与优化的例子

1 数据分布不均匀

1）分库分表不合理

该业务数据分了8个库，每个库分了16张表，通过查看表空间可以看到数据几乎都分布在各个库的某2张表中。分库分表的策略有问题，另外过高预估了业务增量，这个持保留意见。

2）索引不合理

单表创建了idx_logistics_corp_id_special_id的联合索引，但即便这样区分度依然太低，根据实验及业务反馈(logistics_corp_id,transport_type_id)字段组合区分度非常高，且业务存在transport_type_id的单查场景。

2 索引问题

SELECT
  COUNT(0) AS `tmp_count`
FROM(
    SELECT
      `table_holder`.`user_id`,
      `table_holder`.`sc_item_id`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 1 THEN `table_holder`.`quantity`
          ELSE 0
        END
      ) AS `saleable_quantity`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 1 THEN `table_holder`.`lock_quantity`
          ELSE 0
        END
      ) AS `saleable_lock_quantity`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 401 THEN `table_holder`.`quantity`
          ELSE 0
        END
      ) AS `transfer_on_way_quantity`,
      `table_holder`.`store_code`,
      MAX(`table_holder`.`gmt_modified`) AS `gmt_modified`
    FROM
      `table_holder`
    WHERE(`table_holder`.`is_deleted` = 0)
      AND(`table_holder`.`quantity` > 0)
      AND `table_holder`.`user_id` IN(3405569954)
      AND `table_holder`.`store_code` IN('ZJJHBHYTJJ0001', '...1000多个')
    GROUP BY
      `table_holder`.`user_id`,
      `table_holder`.`sc_item_id`
    ORDER BY
      `table_holder`.`user_id` ASC,
      `table_holder`.`sc_item_id` ASC
  ) `a`;

这个case对应的表有store_code索引，因此认为没问题，没办法优化了。实则通过执行计划，我们发现MySQL选择了全表扫描。针对该case实践发现，当范围查询的个数超过200个时，索引优化器将不再使用该字段索引。

最终经过拉取最近一段时间的相关查询SQL，结合业务的数据分布，我们发现采用(is_deleted,quantity)即可解决。

判断执行计划采用的索引长度：key_len的长度计算公式(>=5.6.4)

char(10)允许NULL      =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL)
char(10)不允许NULL    =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1)
varchr(10)允许NULL    =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL) + 2(变长字段)
varchr(10)不允许NULL  =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 2(变长字段)
int允许NULL           =  4 + 1(NULL)
int不允许NULL         =  4
timestamp允许NULL     =  4 + 1(NULL)
timestamp不允许NULL   =  4
datatime允许NULL      =  5 + 1(NULL)
datatime不允许NULL    =  5

3  被人影响

用到了索引却依然被爆出扫描2千万行：

索引字段区分度很高：

同时期常规SQL变为了慢查询：

DB数据盘访问情况：

排查共用物理机其他实例的情况，发现有个库在问题时间附近有很多慢sql需要排序，写临时文件刚好写入了2GB：

多个MySQL实例leader节点混合部署在同一台物理机，虽然通过docker隔离了CPU、MEM等资源，但目前还没有做到buffer io的隔离。

4  无法解决

通过汇总分析高频的查询并结合业务得出合适的索引往往能够解决日常遇到的慢查询，但这并不是万能的。

比如有可能索引越加越多，乃至成了这样：

有些场景，比如支持多个字段组合查询，又没有必填项，如果都要通过索引来支持显然是不合理的。

查询场景下，将区分度较高的字段设定为必填项是个好习惯；查询组合很多的情况下考虑走搜索支持性更好的存储或者搜索引擎。

六  日常化处理

随着各个CTO-D线的深入治理，各项指标较之前均有非常大的改观，比如核心应用完成慢查询清零，影响最大的一些慢SQL被得以解决，而我所在的团队排名也由最初的尾部top3进入到头部top3。
慢SQL治理进入日常化，通过每周固定推送慢SQL工单、owner接手处理、结单，基本形成了定期清零的习惯和氛围，慢SQL治理专项也被多次点名表扬。

Ringkasan Tujuh

Ini adalah ringkasan yang terlewat sekarang, saya rasa proses perumusan strategi, analisis masalah dan penyelesaian cukup berbaloi untuk dikongsi dengan semua orang.

Cadangan berkaitan: "tutorial mysql"

Atas ialah kandungan terperinci Kongsi maklumat berguna! Ringkasan analisis praktikal pertanyaan lambat MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!