Bagaimanakah MySQL dioptimumkan? Mari bercakap tentang pengoptimuman prestasi daripada 5 dimensi

Jika penemuduga bertanya kepada anda: Dari dimensi apakah anda akan mengoptimumkan prestasi MySQL? Bagaimana anda akan menjawab?

Apa yang dipanggil pengoptimuman prestasi secara amnya menyasarkan pengoptimuman pertanyaan MySQL. Memandangkan kami mengoptimumkan pertanyaan, kami secara semula jadi perlu mengetahui terlebih dahulu pautan yang dilalui oleh operasi pertanyaan, dan kemudian memikirkan pautan mana yang boleh dioptimumkan.

Saya menggunakan gambar untuk menunjukkan langkah asas yang perlu dilalui oleh operasi pertanyaan.

Berikut memperkenalkan beberapa strategi untuk pengoptimuman MySQL dari 5 perspektif.

1. Pengoptimuman konfigurasi sambungan

Memproses sambungan ialah langkah pertama dalam hubungan antara klien MySQL dan pelayan MySQL. Yang pertama Jika anda tidak boleh berjalan dengan baik, jangan bercakap tentang kisah seterusnya.

Memandangkan sambungan adalah urusan kedua-dua pihak, kami secara semula jadi mengoptimumkannya dari kedua-dua bahagian pelayan dan pihak pelanggan.

1.1 Konfigurasi Pelayan

Apa yang perlu dilakukan oleh pelayan ialah menerima seberapa banyak sambungan pelanggan yang mungkin anda mengalami ralat error 1040: Too many connections? Ini kerana minda pelayan tidak cukup luas, dan susun aturnya terlalu kecil!

Kita boleh menyelesaikan masalah sambungan yang tidak mencukupi dari dua aspek:

1 Meningkatkan bilangan sambungan yang tersedia dan mengubah suai pembolehubah persekitaran max_connections, secara lalai Bilangan maksimum sambungan pada pelayan ialah 151

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

2. Lepaskan sambungan tidak aktif tepat pada masanya value Smaller

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

MySQL mempunyai banyak parameter konfigurasi, dan kebanyakan parameter memberikan nilai lalai Nilai lalai direka dengan teliti oleh pengarang MySQL dan boleh memenuhi keperluan kebanyakan situasi . , tidak disyorkan untuk mengubah suai secara terburu-buru tanpa mengetahui maksud parameter.

1.2 Pengoptimuman Pelanggan

Apa yang pelanggan boleh lakukan ialah meminimumkan bilangan kali ia mewujudkan sambungan dengan pelayan Sambungan yang telah ditetapkan boleh digunakan sebagai Gunakannya. Jangan buat sambungan baharu setiap kali anda melaksanakan pernyataan SQL.

Penyelesaiannya ialah menggunakan Kolam Sambungan untuk menggunakan semula sambungan.

Kolam sambungan pangkalan data biasa termasuk DBCP, C3P0, Alibaba Druid dan Hikari dua yang pertama jarang digunakan dan dua yang terakhir sedang giat dijalankan.

Tetapi harus diingat bahawa lebih besar kumpulan sambungan, lebih baik Contohnya, saiz kumpulan sambungan maksimum lalai Druid ialah 8, dan saiz kumpulan sambungan maksimum lalai Hikari ialah 10. . Meningkatkan saiz kumpulan sambungan secara buta, kecekapan pelaksanaan sistem mungkin dikurangkan. kenapa?

Untuk setiap sambungan, pelayan akan membuat urutan berasingan untuk memprosesnya Semakin banyak sambungan, semakin banyak urutan yang akan dibuat oleh pelayan. Apabila bilangan utas melebihi bilangan CPU, CPU mesti memperuntukkan kepingan masa untuk melakukan penukaran konteks bagi utas Penukaran konteks yang kerap akan menyebabkan banyak overhed prestasi.

Hikari rasmi memberikan PostgreSQL formula nilai yang disyorkan untuk saiz kumpulan sambungan pangkalan data, CPU核心数*2 1. Dengan mengandaikan bahawa bilangan teras CPU pelayan ialah 4, cuma tetapkan kumpulan sambungan kepada 9. Formula ini juga boleh digunakan untuk pangkalan data lain pada tahap tertentu, dan anda boleh menyombongkannya semasa temu duga.

2. Pengoptimuman seni bina

2.1 Menggunakan cache

Tidak dapat dielakkan bahawa terdapat beberapa pertanyaan yang perlahan dalam sistem . Pertanyaan ini Sama ada jumlah data adalah besar, atau pertanyaan adalah kompleks (banyak jadual yang berkaitan atau pengiraan kompleks), menyebabkan pertanyaan itu menduduki sambungan untuk masa yang lama.

Jika kesahihan data jenis ini tidak begitu kukuh (ia tidak berubah setiap saat, seperti laporan harian), kami boleh meletakkan data jenis ini ke dalam sistem cache semasa tempoh sah cache data, Dapatkan data terus daripada sistem cache, yang boleh mengurangkan tekanan pada pangkalan data dan meningkatkan kecekapan pertanyaan.

2.2 Pengasingan baca-tulis (kelompok, replikasi tuan-hamba)

Pada peringkat awal projek, pangkalan data biasanya berjalan pada pelayan, semua permintaan baca dan tulis daripada pengguna secara langsung akan menjejaskan pelayan pangkalan data ini Lagipun, jumlah konkurensi yang boleh ditanggung oleh pelayan tunggal adalah terhad.

Untuk menangani masalah ini, kami boleh menggunakan berbilang pelayan pangkalan data pada masa yang sama, tetapkan salah satu daripadanya sebagai ketua pasukan, dipanggil nod master dan nod yang tinggal sebagai ahli pasukan, dipanggil slave. Pengguna menulis data hanya pada nod master dan permintaan baca diedarkan kepada pelbagai nod slave. Penyelesaian ini dipanggil Baca dan tulis pemisahan. Beri nama kumpulan kecil yang terdiri daripada ketua kumpulan dan ahli kumpulan, kluster.

Nota: Ramai pembangun tidak berpuas hati dengan perkataan yang menyinggung master-slave (kerana mereka fikir ia akan dikaitkan dengan diskriminasi kaum, hamba hitam, dll.), jadi Kempen untuk menukar nama telah dilancarkan.

Dipengaruhi oleh ini, MySQL secara beransur-ansur akan berhenti menggunakan istilah seperti master dan slave dan menggantikannya dengan source dan replica fahami sahaja apabila anda menemuinya.

Menggunakan kluster pasti akan menghadapi masalah, iaitu cara mengekalkan konsistensi data antara berbilang nod. Lagipun, permintaan tulis hanya dihantar ke nod master dan hanya data nod master ialah data terkini Bagaimana operasi tulis pada nod master boleh disegerakkan ke setiap nod slave. ?

Replikasi tuan-hambaTeknologi ada di sini! Saya secara ringkas memperkenalkan log binlog dalam artikel saya sebelum ini, jadi saya mengalihkannya secara langsung.

binlog ialah komponen teras yang melaksanakan fungsi replikasi induk-hamba MySQL. Nod master akan merekodkan semua operasi tulis ke dalam binlog Nod slave akan mempunyai benang I/O khusus untuk membaca binlog nod master dan menyegerakkan operasi tulis ke nod slave semasa.

Seni bina kluster ini mempunyai kesan yang sangat baik untuk mengurangkan tekanan pada pelayan pangkalan data utama Walau bagaimanapun, apabila data perniagaan meningkat, jika jumlah data dalam jadual tertentu Jika prestasi pertanyaan satu jadual meningkat dengan mendadak, prestasi pertanyaan satu jadual akan menurun dengan ketara, dan masalah ini tidak dapat diselesaikan walaupun dengan pengasingan membaca dan menulis Lagipun, semua nod menyimpan data yang sama daripada satu jadual adalah lemah, prestasi semua nod adalah lemah.

Pada masa ini, kami boleh menyebarkan data satu nod kepada berbilang nod untuk storan Ini ialah sub-pangkalan data dan sub-jadual.

2.3 Sub-pangkalan data dan sub-jadual

Maksud nod dalam sub-pangkalan data dan sub-jadual adalah agak luas Jika pangkalan data digunakan sebagai nod , ia adalah sub-pangkalan data; jika risalah adalah Sebagai nod, jadual adalah sub-jadual.

Semua orang tahu bahawa sub-pangkalan data dan sub-jadual dibahagikan kepada sub-pangkalan data menegak, sub-jadual menegak, sub-pangkalan data mendatar dan sub-jadual mendatar, tetapi setiap kali saya tidak dapat mengingati konsep ini, Saya akan menerangkannya secara terperinci untuk membantu semua orang faham.

2.3.1 Sub-pangkalan data menegak

Buat beberapa pemotongan menegak berdasarkan pangkalan data tunggal dan bahagikannya mengikut perniagaan logik ke dalam pangkalan data yang berbeza, ini ialah sub-pangkalan data menegak.

2.3.2 Sub-jadual menegak

Sub-jadual menegak berada dalam jadual tunggal Pada asasnya, buat potongan menegak (atau beberapa potongan) untuk membahagikan beberapa perkataan dalam jadual kepada beberapa jadual kecil Operasi ini perlu dinilai berdasarkan perniagaan tertentu Biasanya, medan yang kerap digunakan (medan panas) dibahagikan kepada Jadual , medan yang tidak kerap digunakan atau tidak digunakan serta-merta (medan sejuk) dibahagikan kepada satu jadual untuk meningkatkan kelajuan pertanyaan.

Ambil gambar di atas sebagai contoh: Biasanya butiran produk agak panjang, dan apabila melihat senarai produk, selalunya tidak perlu memaparkan butiran produk dengan segera ( biasanya klik butang butiran akan dipaparkan), tetapi akan memaparkan maklumat produk yang lebih penting (harga, dsb.). Mengikut logik perniagaan ini, kami menjadikan jadual produk asal menjadi sub-jadual menegak.

2.3.3 Pemisahan jadual mendatar

Simpan data satu jadual ke beberapa jadual data mengikut peraturan tertentu (dipanggil peraturan sharding dalam jargon), secara mendatar Berikan jadual data potongan (atau beberapa potongan) dan ia akan menjadi jadual mendatar.

2.3.4 水平分库

水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。

2.3.5 总结

水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。

2.4 消息队列削峰

通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。

这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。

3. 优化器——SQL分析与优化

处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。

只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。

3.1 慢查询

慢查询就是执行地很慢的查询（这句话说得跟废话似的。。。），只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。

因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。

除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

3.1.1 打开慢日志

有两种打开慢日志的方式

1、修改配置文件my.cnf

此种修改方式系统重启后依然有效

# 是否开启慢查询日志
slow_query_log=ON
# 
long_query_time=2
slow_query_log_file=/var/lib/mysql/slow.log

2、动态修改参数（重启后失效）

mysql> set @@global.slow_query_log=1;
Query OK, 0 rows affected (0.06 sec)

mysql> set @@global.long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

3.1.2 慢日志分析

MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：

mysql> SELECT sleep(5);

然后我们查询用时最多的1条慢查询：

[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log

Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  SELECT sleep(N)

其中，

• Count：表示这个SQL执行的次数
• Time：表示执行的时间，括号中的是累积时间
• Locks：表示锁定的时间，括号中的是累积时间
• Rows：表示返回的记录数，括号中的是累积数

更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。

3.2 查看运行中的线程

我们可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。

其中，

• Id：线程的唯一标志，可以使用Id杀死指定线程
• User：启动这个线程的用户，普通账户只能查看自己的线程
• Host：哪个ip和端口发起的连接
• db：线程操作的数据库
• Command：线程的命令
• Time：操作持续时间，单位秒
• State：线程的状态
• Info：SQL语句的前100个字符

3.3 查看服务器运行状态

使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。

-- 查看select的次数
mysql> SHOW GLOBAL STATUS LIKE 'com_select';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 168241 |
+---------------+--------+
1 row in set (0.05 sec)

3.4 查看存储引擎运行信息

SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。

例如：

SHOW ENGINE INNODB STATUS;

上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。

3.5 EXPLAIN执行计划

通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？

MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询...）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么，等等等等。

EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。

这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节，之后单独成篇。

3.6 SQL与索引优化

3.6.1 SQL优化

SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：

• 使用小表驱动大表；用join改写子查询；or改成union
• 连接查询中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本；被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低
• 大偏移量的limit，先过滤再排序

针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB），大家感受一下：

-- 1. 大偏移量的查询
mysql> SELECT * FROM user_innodb LIMIT 9000000,10;
Empty set (8.18 sec)

-- 2.先过滤ID（因为ID使用的是索引），再limit
mysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;
Empty set (0.02 sec)

3.6.2 索引优化

为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。

推荐阅读：《如何用好MySQL索引？你必须了解这些事！》，感兴趣的读者可以看一下。
https://www.php.cn/mysql-tutorials-493147.html

4. 存储引擎与表结构

4.1 选择存储引擎

一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。

建议根据不同的业务选择不同的存储引擎，例如：

• 查询操作、插入操作多的业务表，推荐使用MyISAM；
• 临时表使用Memory；
• 并发数量大、更新多的业务选择使用InnoDB；
• 不知道选啥直接默认。

4.2 优化字段

字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。

4.2.1 整数类型

MySQL提供了6种整数类型，分别是

• tinyint
• smallint
• mediumint
• int
• integer
• bigint

不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。

例如，是否被删除的标识，建议选用tinyint，而不是bigint。

4.2.2 字符类型

你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不够，还会直接设置成varchar(1024)的长度？

如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。

4.2.3 Tidak kosong

Cuba tetapkan medan bukan kosong kepada NOT NULL dan berikan nilai lalai, atau gunakan nilai khas dan bukannya NULL.

Oleh kerana storan dan pengoptimuman jenis NULL akan mengalami masalah prestasi yang lemah, sebab khusus tidak akan dibincangkan di sini.

4.2.4 Jangan gunakan kekunci asing, pencetus dan fungsi lihat

Ini juga merupakan prinsip yang disebut dalam "Manual Pembangunan Alibaba". Terdapat tiga sebab:

• Mengurangkan kebolehbacaan, dan anda perlu menyemak kod pangkalan data semasa menyemak kod; Untuk program, pangkalan data hanya melakukan kerja storan dan melakukan ini dengan baik

• Kerja pengesahan integriti data harus diselesaikan oleh pembangun dan bukannya bergantung pada sumber luaran, sekali anda menggunakan kunci asing, anda akan mendapati bahawa ia menjadi amat sukar untuk memadamkan beberapa data sampah semasa ujian.

• 4.2.5 Storan imej, audio, video

Jangan simpan fail besar secara langsung, tetapi simpan alamat akses fail besar.

4.2.6 Pemisahan medan besar dan redundansi data

Pecahan medan besar sebenarnya ialah pemisahan jadual menegak yang dinyatakan sebelum ini medan dengan jumlah data yang besar untuk mengelakkan terlalu banyak lajur dan jumlah data yang terlalu besar Terutama jika anda biasa menulis

, masalah yang disebabkan oleh terlalu banyak lajur dan jumlah data yang besar akan menjadi serius.

Lewahan medanSELECT * Pada dasarnya, ia tidak menepati paradigma reka bentuk pangkalan data, tetapi ia sangat kondusif untuk mendapatkan semula pantas. Sebagai contoh, apabila ID pelanggan disimpan dalam jadual kontrak, nama pelanggan boleh disimpan secara berlebihan, supaya tidak perlu mendapatkan nama pengguna berdasarkan ID pelanggan semasa membuat pertanyaan. Oleh itu, ia juga merupakan teknik pengoptimuman yang lebih baik untuk membuat tahap redundansi tertentu untuk logik perniagaan.

5. Pengoptimuman perniagaan

Tegasnya, pengoptimuman perniagaan bukan lagi cara penalaan MySQL, tetapi pengoptimuman perniagaan boleh mengurangkan tekanan akses pangkalan data dengan sangat berkesan. Contoh biasa perkara ini ialah Taobao Berikut ialah beberapa contoh mudah untuk memberi anda beberapa idea:

Pada masa lalu, membeli-belah bermula pada malam Double 11. Baru-baru ini, Pada masa lalu. beberapa tahun, bahagian pra-jualan untuk Double 11 semakin lama, bermula lebih daripada setengah bulan lebih awal, dan pelbagai model sampul merah deposit telah muncul tanpa henti Kaedah ini dipanggil

lencongan pra-jualan

. Ini boleh mengalihkan permintaan perkhidmatan pelanggan, dan anda tidak perlu menunggu sehingga awal pagi Double Eleven untuk membuat pesanan secara kolektif; strategi
• yang mengumpulkan sumber pengkomputeran untuk perkhidmatan yang tidak penting untuk memastikan perniagaan teras semasa;
  Semasa Double Eleven, Alipay amat mengesyorkan menggunakan pembayaran Huabei dan bukannya pembayaran kad bank pertimbangan adalah untuk meningkatkan kelekatan perisian, sebaliknya, menggunakan Yu'e Bao sebenarnya menggunakan Alibaba Pelayan dalaman mempunyai kelajuan akses yang pantas, tetapi menggunakan kad bank memerlukan panggilan antara muka bank, yang jauh lebih perlahan berbanding.

• Ini menyimpulkan ringkasan pengoptimuman MySQL Terdapat banyak butiran yang tidak disebutkan, yang membuatkan saya rasa artikel ini tidak sempurna. Walau bagaimanapun, terdapat terlalu banyak perkara pengetahuan untuk dibincangkan secara terperinci. Tidak mustahil untuk menulis semuanya sekaligus.

[Cadangan berkaitan:
• tutorial video mysql

  ]