Selain indeks, apakah faktor lain yang membuat pertanyaan mysql perlahan?

Saya telah mahir menggunakan ctrl c dan ctrl v untuk membangunkan kod dadih selama bertahun-tahun.

Mengapa pertanyaan mysql lambat Masalah ini sering dihadapi dalam pembangunan sebenar, dan ia juga merupakan soalan yang sering ditanya dalam temu bual.

Apabila menghadapi masalah seperti ini, biasanya kita fikir ia adalah kerana indeks.

Selain indeks, apakah faktor lain yang boleh menyebabkan pertanyaan pangkalan data menjadi perlahan?

Apakah operasi yang boleh meningkatkan keupayaan pertanyaan mysql?

Dalam artikel hari ini, kita akan bercakap tentang senario yang boleh menyebabkan pertanyaan pangkalan data menjadi perlahan, dan memberikan sebab dan penyelesaiannya.

Proses pertanyaan pangkalan data

Mari kita lihat dahulu proses apa yang akan dilalui oleh pernyataan pertanyaan.

Sebagai contoh, kita mempunyai jadual pangkalan data

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `gender` int(8) NOT NULL DEFAULT '0' COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`),
  KEY `idx_gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Kod aplikasi yang biasa kita tulis (go atau C atau sebagainya), kemudian ia dipanggil klien.

Lapisan bawah pelanggan akan mengambil kata laluan akaun dan cuba mewujudkan pautan panjang TCP ke mysql.

modul pengurusan sambungan mysql akan menguruskan sambungan ini.

Selepas mewujudkan sambungan, klien melaksanakan pernyataan sql pertanyaan. Contohnya:

select * from user where gender = 1 and age = 100;

Pelanggan akan menyambungkan pernyataan sql ke mysql melalui rangkaian.

Selepas mysql menerima pernyataan sql, ia akan terlebih dahulu menentukan sama ada terdapat ralat tatabahasa dalam pernyataan SQL dalam penganalisis , seperti pilih Jika kurang satu l, ia akan ditulis sebagai slect Ralat You have an error in your SQL syntax; akan dilaporkan. Laporan ralat ini sangat biasa bagi orang kurang upaya seperti saya.

Seterusnya ialah pengoptimum, di mana ia akan memilih indeks mana untuk digunakan berdasarkan peraturan tertentu. Selepas

, fungsi antara muka enjin storan dipanggil melalui pelaksana.

Enjin storan adalah sama dengan komponen mysql sebenarnya mendapat baris data dan mengembalikan data Ya, anda boleh sama ada menggunakan MyISAM yang tidak menyokong transaksi, atau anda boleh menggantikannya dengan Innodb yang menyokong transaksi. Ini boleh ditentukan semasa membuat jadual. Contohnya,

CREATE TABLE `user` (
  ...
) ENGINE=InnoDB;

kini paling biasa digunakan sebagai InnoDB.

Mari fokus pada perkara ini.

Dalam InnoDB, kerana mengendalikan cakera secara langsung akan menjadi lebih perlahan, satu lapisan memori ditambahkan untuk mempercepatkan, dipanggil kolam penimbal Terdapat banyak halaman memori di dalamnya, setiap halaman adalah 16KB . Beberapa halaman memori menyimpan data baris demi baris yang dilihat dalam jadual pangkalan data, dan beberapa menyimpan maklumat indeks.

Tanya SQL kepada InnoDB. Berdasarkan indeks yang dikira dalam pengoptimum sebelumnya, halaman indeks yang sepadan akan disoal Jika ia tidak berada dalam kumpulan penimbal, halaman indeks akan dimuatkan daripada cakera. kemudian mempercepatkan pertanyaan melalui halaman indeks untuk mendapatkan lokasi khusus halaman data . Jika halaman data ini tiada dalam kumpulan penimbal, ia dimuatkan daripada cakera.

Dengan cara ini kami mendapat baris data yang kami inginkan.

Akhir sekali, hasil data yang diperoleh dikembalikan kepada pelanggan.

Analisis Pertanyaan Lambat

Jika proses di atas lambat, kita boleh lihat di mana prosesnya perlahan dengan menghidupkan

. profiling

mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Kemudian laksanakan pernyataan sql seperti biasa.

Masa pelaksanaan pernyataan SQL ini akan direkodkan Pada masa ini, jika anda ingin melihat pernyataan yang telah direkodkan, anda boleh melaksanakan

show profiles;

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.06811025 | select * from user where age>=60                  |
|        2 | 0.00151375 | select * from user where gender = 2 and age = 80  |
|        3 | 0.00230425 | select * from user where gender = 2 and age = 60  |
|        4 | 0.00070400 | select * from user where gender = 2 and age = 100 |
|        5 | 0.07797650 | select * from user where age!=60                  |
+----------+------------+---------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

dan memberi perhatian. kepada

di atas, sebagai contoh, query_id yang sepadan dengan query_id ialah 1. Jika anda ingin menyemak penggunaan masa tertentu bagi pernyataan SQL ini, anda boleh melaksanakan arahan berikut. select * from user where age>=60

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000074 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000034 |
| init                 | 0.000032 |
| System lock          | 0.000027 |
| optimizing           | 0.000020 |
| statistics           | 0.000058 |
| preparing            | 0.000018 |
| executing            | 0.000013 |
| Sending data         | 0.067701 |
| end                  | 0.000021 |
| query end            | 0.000015 |
| closing tables       | 0.000014 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000027 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

Melalui item di atas, anda boleh melihat di mana masa tertentu dibelanjakan. Sebagai contoh, dapat dilihat daripada di atas bahawa Menghantar data mengambil masa paling lama Ini merujuk kepada masa yang diambil untuk

pelaksana mula menanyakan data dan menghantar data kepada klien, kerana jadual saya. mempunyai Puluhan ribu penyertaan, jadi bahagian ini mengambil masa yang paling lama dan mengikut jangkaan.

Secara amnya, dalam proses pembangunan kami, kebanyakan masa dihabiskan dalam peringkat

, dan jika lambat pada peringkat ini, sebab yang paling mungkin untuk difikirkan ialah sebab berkaitan indeks. Sending data

Sebab berkaitan indeks

Isu berkaitan indeks umumnya boleh dianalisis menggunakan perintah explain. Melaluinya, anda boleh melihat indeks yang mana digunakan dan mungkin berapa banyak baris yang akan diimbas dan maklumat lain.

mysql akan melihat indeks mana yang hendak dipilih dalam fasa pengoptimum dan kelajuan pertanyaan akan menjadi lebih pantas.

Secara amnya terdapat beberapa faktor yang perlu dipertimbangkan, seperti:

• Berapa banyak baris yang perlu diimbas untuk memilih indeks ini(baris)
Dalam susunan untuk menggabungkan ini Untuk mengambil baris,
• berapa banyak halaman 16kb yang perlu dibaca?
Indeks biasa memerlukan sandaran jadual, tetapi indeks kunci utama tidak
• Adakah sandaran jadual kos besar?

Kembali ke pernyataan sql yang disebut dalam profil rancangan, mari gunakan

untuk menganalisisnya. explain select * from user where age>=60

Dalam pernyataan di atas,

ialah SEMUA, yang bermaksud typeimbasan jadual penuh dan merujuk kepada possible_keys Indeks yang mungkin digunakan di sini adalah indeks biasa yang dibina untuk umur, tetapi sebenarnya indeks yang digunakan oleh pangkalan data adalah dalam lajur , iaitu key. Dengan kata lain, NULL sql ini tidak menggunakan indeks, tetapi mengimbas keseluruhan jadual .

Ini kerana terdapat terlalu banyak baris data yang layak (

) dalam jadual data Jika anda menggunakan indeks umur, anda perlu membacanya daripada indeks umur dan indeks umur ialah rows Indeks biasa juga perlu kembali ke jadual untuk mencari kunci utama yang sepadan untuk mencari halaman data yang sepadan. Lagipun, ia tidak kos efektif seperti menggunakan kunci utama secara langsung. Jadi saya akhirnya memilih imbasan jadual penuh.

Sudah tentu perkara di atas hanyalah contoh Sebenarnya, apabila mysql melaksanakan sql,

tiada indeks digunakan atau indeks yang digunakan tidak memenuhi jangkaan kami Ini sering berlaku, dan terdapat banyak. senario di mana indeks gagal , seperti menggunakan tanda ketaksamaan , penukaran tersirat , dsb. Saya percaya anda telah banyak menghafal ini apabila menghafal esei lapan bahagian, jadi saya tidak akan menerangkan secara terperinci.

Mari kita bercakap tentang dua masalah yang mudah dihadapi dalam pengeluaran.

Indeks tidak memenuhi jangkaan

Dalam pembangunan sebenar, terdapat beberapa situasi khas Sebagai contoh, sesetengah jadual pangkalan data mempunyai jumlah data yang kecil dan sedikit indeks pada permulaan . Apabila melaksanakan SQL, ia sememangnya digunakan Indeks yang sepadan dengan jangkaan anda. Tetapi seiring dengan berlalunya masa, lebih ramai orang sedang membangun, jumlah data menjadi lebih besar, dan beberapa indeks berulang dan berlebihan mungkin ditambah. Mungkin berlaku bahawa indeks lain yang tidak memenuhi jangkaan anda digunakan. Ini menyebabkan pertanyaan menjadi perlahan secara tiba-tiba.

Masalah seperti ini boleh diselesaikan dengan mudah dengan menyatakan indeks

melalui force index. Contohnya,

Ia boleh dilihat daripada

bahawa selepas menambah indeks daya, SQL akan menggunakan indeks idx_age. explain

Ia masih sangat perlahan selepas mengindeks

Bagi sesetengah SQL, jika anda menggunakan perintah

, jelas bahawa ia diindeks, tetapi ia masih lambat sangat. Secara umumnya terdapat dua situasi: explain

Yang pertama ialah perbezaan indeks terlalu rendah, seperti laluan penuh pautan URL pada halaman web, yang digunakan untuk pengindeksan Sepintas lalu, semuanya nama domain yang sama. Jika awalan

Jika indeks tidak cukup panjang, maka pengindeksan adalah serupa dengan imbasan jadual penuh Pendekatan yang betul ialah cuba membuat indeks pembezaan lebih tinggi, seperti mengalih keluar nama domain , hanya gunakan bahagian akhir URI untuk pengindeksan.

Jenis kedua ialah data yang dipadankan dalam indeks terlalu besar pada masa ini, yang perlu diberi perhatian ialah

baris medan dalam menerangkan.

Ia digunakan untuk

menganggarkan bilangan baris yang perlu disemak untuk pernyataan pertanyaan ini. Ia mungkin tidak tepat sepenuhnya, tetapi ia boleh menggambarkan susunan magnitud yang kasar.

Apabila ia sangat besar, situasi berikut biasanya biasa berlaku.

• 如果这个字段具有唯一的属性，比如电话号码等，一般是不应该有大量重复的，那可能是你代码逻辑出现了大量重复插入的操作，你需要检查下代码逻辑，或者需要加个唯一索引限制下。
• 如果这个字段下的数据就是会很大，是否需要全部拿？如果不需要，加个limit限制下。如果确实要拿全部，那也不能一次性全拿，今天你数据量小，可能一次取一两万都没啥压力，万一哪天涨到了十万级别，那一次性取就有点吃不消了。你可能需要分批次取，具体操作是先用order by id排序一下，拿到一批数据后取最大id作为下次取数据的起始位置。

连接数过小

索引相关的原因我们聊完了，我们来聊聊，除了索引之外，还有哪些因素会限制我们的查询速度的。

我们可以看到，mysql的server层里有个连接管理，它的作用是管理客户端和mysql之间的长连接。

正常情况下，客户端与server层如果只有一条连接，那么在执行sql查询之后，只能阻塞等待结果返回，如果有大量查询同时并发请求，那么后面的请求都需要等待前面的请求执行完成后，才能开始执行。

因此很多时候我们的应用程序，比如go或java这些，会打印出sql执行了几分钟的日志，但实际上你把这条语句单独拎出来执行，却又是毫秒级别的。 这都是因为这些sql语句在等待前面的sql执行完成。

怎么解决呢？

如果我们能多建几条连接，那么请求就可以并发执行，后面的连接就不用等那么久了。

而连接数过小的问题，受数据库和客户端两侧同时限制。

数据库连接数过小

mysql的最大连接数默认是100, 最大可以达到16384。

可以通过设置mysql的max_connections参数，更改数据库的最大连接数。

mysql> set global max_connections= 500;
Query OK, 0 rows affected (0.00 sec)

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

上面的操作，就把最大连接数改成了500。

应用侧连接数过小

数据库连接大小是调整过了，但貌似问题还是没有变化？还是有很多sql执行达到了几分钟，甚至超时？

那有可能是因为你应用侧（go，java写的应用，也就是mysql的客户端）的连接数也过小。

应用侧与mysql底层的连接，是基于TCP协议的长链接，而TCP协议，需要经过三次握手和四次挥手来实现建连和释放。如果我每次执行sql都重新建立一个新的连接的话，那就要不断握手和挥手，这很耗时。所以一般会建立一个长连接池，连接用完之后，塞到连接池里，下次要执行sql的时候，再从里面捞一条连接出来用，非常环保。

我们一般写代码的时候，都会通过第三方的orm库来对数据库进行操作，而成熟的orm库，百分之一千万都会有个连接池。

而这个连接池，一般会有个大小。这个大小就控制了你的连接数最大值，如果说你的连接池太小，都还没有数据库的大，那调了数据库的最大连接数也没啥作用。

一般情况下，可以翻下你使用的orm库的文档，看下怎么设置这个连接池的大小，就几行代码的事情，改改就好。比如go语言里的gorm里是这么设置的

func Init() {
  db, err := gorm.Open(mysql.Open(conn), config)
  sqlDB, err := db.DB()
  // SetMaxIdleConns 设置空闲连接池中连接的最大数量
  sqlDB.SetMaxIdleConns(200)
  // SetMaxOpenConns 设置打开数据库连接的最大数量
  sqlDB.SetMaxOpenConns(1000)
}

buffer pool太小

连接数是上去了，速度也提升了。

曾经遇到过面试官会追问，有没有其他办法可以让速度更快呢？

那必须要眉头紧锁，假装思考，然后说：有的。

我们在前面的数据库查询流程里，提到了进了innodb之后，会有一层内存buffer pool，用于将磁盘数据页加载到内存页中，只要查询到buffer pool里有，就可以直接返回，否则就要走磁盘IO，那就慢了。

也就是说，如果我的buffer pool 越大，那我们能放的数据页就越多，相应的，sql查询时就更可能命中buffer pool，那查询速度自然就更快了。

可以通过下面的命令查询到buffer pool的大小，单位是Byte。

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

也就是128Mb。

如果想要调大一点。可以执行

mysql> set global innodb_buffer_pool_size = 536870912;
Query OK, 0 rows affected (0.01 sec)

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

这样就把buffer pool增大到512Mb了。

但是吧，如果buffer pool大小正常，只是别的原因导致的查询变慢，那改buffer pool毫无意义。

但问题又来了。

怎么知道buffer pool是不是太小了？

这个我们可以看buffer pool的缓存命中率。

通过 show status like 'Innodb_buffer_pool_%';可以看到跟buffer pool有关的一些信息。

Innodb_buffer_pool_read_requests表示读请求的次数。

Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数。

所以buffer pool的命中率就可以这样得到：

buffer pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

比如我上面截图里的就是，1 - (405/2278354) = 99.98%。可以说命中率非常高了。

一般情况下buffer pool命中率都在99%以上，如果低于这个值，才需要考虑加大innodb buffer pool的大小。

当然，还可以把这个命中率做到监控里，这样半夜sql变慢了，早上上班还能定位到原因，就很舒服。

还有哪些骚操作？

前面提到的是在存储引擎层里加入了buffer pool用于缓存内存页，这样可以加速查询。

那同样的道理，server层也可以加个缓存，直接将第一次查询的结果缓存下来，这样下次查询就能立刻返回，听着挺美的。

按道理，如果命中缓存的话，确实是能为查询加速的。但这个功能限制很大，其中最大的问题是只要数据库表被更新过，表里面的所有缓存都会失效，数据表频繁的更新，就会带来频繁的缓存失效。所以这个功能只适合用于那些不怎么更新的数据表。

另外，这个功能在8.0版本之后，就被干掉了。所以这功能用来聊聊天可以，没必要真的在生产中使用啊。

总结

• 数据查询过慢一般是索引问题，可能是因为选错索引，也可能是因为查询的行数太多。
• 客户端和数据库连接数过小，会限制sql的查询并发数，增大连接数可以提升速度。
• innodb里会有一层内存buffer pool用于提升查询速度，命中率一般>99%，如果低于这个值，可以考虑增大buffer pool的大小，这样也可以提升速度。
• 查询缓存（query cache）确实能为查询提速，但一般不建议打开，因为限制比较大，并且8.0以后的mysql里已经将这个功能干掉了。

最后

最近原创更文的阅读量稳步下跌，思前想后，夜里辗转反侧。

我有个不成熟的请求。

离开广东好长时间了，好久没人叫我靓仔了。

大家可以在评论区里，叫我一靓仔吗？

我这么善良质朴的愿望，能被满足吗？

如果实在叫不出口的话，能帮我点下右下角的点赞和在看吗？

【相关推荐：mysql视频教程】

Atas ialah kandungan terperinci Selain indeks, apakah faktor lain yang membuat pertanyaan mysql perlahan?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!