Bagaimanakah Mysql mengendalikan jadual data yang besar? Perkongsian penyelesaian

Bagaimanakah Mysql mengendalikan jadual data yang besar? Artikel berikut akan memperkenalkan kepada anda penyelesaian pemprosesan jadual data besar Mysql, saya harap ia akan membantu anda.

Senario:

Apabila terdapat lebih banyak data dalam jadual pangkalan data perniagaan kami, jika anda dan saya mengalami perkara berikut Senario yang sama, mari kita selesaikan masalah ini bersama-sama

• Sisipan data, masa pertanyaan adalah panjang
• Perluasan keperluan perniagaan seterusnya akan memberi impak yang lebih besar pada medan baharu dalam jadual
• Bukan semua data dalam jadual adalah data yang sah Kami hanya perlu menanyakan

jumlah data jadual penilaian dalam selang masa

Kami Jumlah data boleh dinilai dari tiga aspek: kapasiti jadual/ruang cakera/kapasiti contoh Seterusnya, mari kita kembangkan dan lihat

Kapasiti jadual:

Jadual. kapasiti terutamanya Menilai berdasarkan bilangan rekod, panjang purata, pertumbuhan, volum baca dan tulis, dan jumlah saiz jadual. Secara amnya, untuk jadual OLTP, adalah disyorkan bahawa satu jadual tidak boleh melebihi 20 juta baris data dan jumlah saiz hendaklah dalam lingkungan 15G. Kelantangan akses: Kelantangan baca dan tulis satu jadual adalah dalam 1600/s

Cara untuk menanyakan data baris: Pernyataan SQL klasik yang biasanya kami gunakan semasa menanyakan jumlah data yang terdapat dalam jadual adalah seperti berikut:

pilih kiraan(*) daripada jadual
• pilih kiraan(1) daripada jadual Tetapi apabila jumlah data terlalu besar, pertanyaan sedemikian mungkin tamat masa, jadi kami perlu menukar kaedah pertanyaan

• gunakan nama perpustakaan

• tunjukkan status jadual seperti 'nama jadual'; atau tunjukkan status jadual seperti 'nama jadual'G;

Kaedah di atas bukan sahaja boleh menanyakan data jadual, tetapi juga output jadual Untuk maklumat terperinci, tambah G untuk memformat output. Termasuk nama jadual, versi enjin storan, bilangan baris, bilangan bait setiap baris, dsb. Anda boleh mencubanya sendiri

Ruang cakera

Lihat kapasiti pangkalan data yang ditentukan

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

Soal penggunaan cakera semua jadual dalam satu pangkalan data

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

Hasil pertanyaan adalah seperti berikut:

Saiz data yang disyorkan Dalam 70% daripada penggunaan cakera. Pada masa yang sama, untuk sesetengah data yang berkembang pesat, anda boleh mempertimbangkan untuk menggunakan cakera perlahan yang besar untuk pengarkiban data (untuk mengarkib, sila rujuk Pelan 3)

Kapasiti Instance

MySQL ialah sebuah model perkhidmatan berasaskan benang, jadi dalam beberapa senario dengan konkurensi tinggi, satu contoh tidak boleh menggunakan sepenuhnya sumber CPU pelayan, dan daya pemprosesan akan tersekat pada lapisan mysql Anda boleh mempertimbangkan mod contoh anda sendiri berdasarkan perniagaan

Punca masalah

Kami telah pun menyemak saiz jadual data kami di atas. Jadi apakah punca mengapa semakin besar volum data dalam satu jadual , semakin perlahan kecekapan pelaksanaan perniagaan?

Apabila jumlah data dalam jadual mencapai puluhan atau ratusan juta, kesan penambahan indeks tidak begitu ketara. Sebab prestasi menjadi lebih teruk adalah kerana

tahap struktur pokok untuk mengekalkan indeks menjadi lebih tinggi Apabila menanyakan sekeping data, lebih banyak IO cakera perlu dialami, jadi prestasi pertanyaan menjadi lebih perlahan. B

Adakah anda masih ingat berapa banyak data yang boleh disimpan oleh pokok B?

Unit storan terkecil bagi enjin storan InnoDB ialah halaman dan saiz halaman ialah

. 16k

Daun pokok B menyimpan data dan nod dalaman menyimpan penunjuk nilai kunci. Jadual tersusun indeks menentukan halaman mana data berada melalui kaedah carian binari nod dan penunjuk bukan daun, dan kemudian pergi ke halaman data untuk mencari data yang diperlukan;

Andaikan pokok B Jika ketinggiannya ialah

, terdapat satu nod akar dan beberapa nod daun. Jumlah bilangan rekod yang disimpan dalam pokok B ini ialah = bilangan penunjuk nod akar * bilangan baris yang direkodkan dalam satu nod daun.

Jika saiz data bagi satu baris rekod ialah 1k, maka bilangan rekod yang boleh disimpan dalam satu nod daun =16k/1k =16.

2Berapa banyak penunjuk disimpan dalam nod bukan daun? Kami menganggap bahawa ID kunci utama ialah

jenis bigint, dengan panjang 8 bait

(
• Penemuduga bertanya kepada anda tentang jenis int, int ialah 32 bit, 4 bait
), dan saiz penuding berada dalam kod sumber InnoDB ditetapkan kepada 6 bait, jadi 8 6=14 bait, 16k/14B =16*1024B/14B = 1170
• Oleh itu, pokok B dengan ketinggian 2 boleh menyimpan Rekod data sedemikian. Dengan cara yang sama, pokok B dengan ketinggian 3 boleh menyimpan , yang bermaksud bahawa ia boleh menyimpan kira-kira 20 juta rekod. Ketinggian B-tree biasanya 1-3 lapisan, yang boleh memenuhi keperluan penyimpanan berpuluh-puluh juta tahap data.

Jika B-tree ingin menyimpan lebih banyak data, tahap struktur pokok akan menjadi lebih tinggi Apabila menanyakan sekeping data, lebih banyak IO cakera perlu dialami, jadi prestasi pertanyaan menjadi lebih perlahan.

1170 * 16=187201170 *1170 *16 =21902400Bagaimana untuk menyelesaikan masalah terlalu banyak data dalam satu jadual dan pertanyaan perlahan

Setelah mengetahui punca, kita perlu mempertimbangkan cara mengoptimumkan pangkalan data untuk selesaikan masalah

这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案

方案一：数据表分区

为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率

分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。

我们首先看一下分区有什么优缺点：

表分区有什么好处？

• 与单个磁盘或文件系统分区相比，可以存储更多的数据。

• 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

• 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

• 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

• 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

表分区的限制因素

• 一个表最多只能有1024个分区。

• MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

• 如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

• 分区表中无法使用外键约束。

• MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

在进行分区之前可以用如下方法 看下数据库表是否支持分区哈

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

方案二：数据库分表

为什么要分表：分表后，显而易见，单表数据量降低，树的高度变低，查询经历的磁盘io变少，则可以提高效率

mysql 分表分为两种 水平分表和垂直分表

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

水平分表

定义：数据表行的拆分，通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。 比如一个表有4000万数据，查询很慢，可以分到四个表，每个表有1000万数据

垂直分表

定义：列的拆分，根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分，然后利用主键关联。或者一个数据库里面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据

缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加join 或 union之类的操作

知道了两个知识后，我们来看一下分库分表的方案

1. Skema modulo:

Sebelum membahagi, anggarkan jumlah data. Sebagai contoh, jadual pengguna mempunyai 40 juta data, dan kini data perlu dibahagikan kepada 4 jadual user1 user2 uesr3 user4. Contohnya, id = 17, 17 modulo 4 ialah 1, tambah , jadi data ini disimpan dalam jadual user2.

Nota: Auto_increment hendaklah dialih keluar daripada jadual selepas pemisahan mendatar. ID pada masa ini boleh diperoleh menggunakan jadual sementara peningkatan sendiri ID, atau menggunakan kaedah redis incr.

Kelebihan: Data dibahagikan sama rata kepada pelbagai jadual, dan kebarangkalian isu hangat adalah sangat rendah.

Kelemahan: Ia akan menjadi sukar untuk mengembangkan dan memindahkan data pada masa hadapan Apabila jumlah data meningkat, apa yang sebelum ini dibahagikan kepada 4 jadual kini akan dibahagikan kepada 8 jadual perubahan nilai modulo dan pemindahan data perlu dilakukan lagi.

2.skim julat julat

Pisah data mengikut julat, iaitu pesanan dalam julat tertentu disimpan dalam jadual tertentu. Sebagai contoh, id=12 disimpan dalam jadual pengguna1, dan id=13 juta disimpan dalam jadual pengguna2.

Kelebihan: Kondusif untuk pengembangan data masa hadapan

Kelemahan: Jika data panas disimpan dalam satu jadual, tekanan akan berada dalam satu jadual, dan lain Tiada tekanan pada tolok.

Kami melihat kedua-dua penyelesaian di atas mempunyai kekurangan tetapi saling melengkapi Jadi apa yang akan berlaku jika kita menggabungkan kedua-dua penyelesaian ini?

3 Gabungan modulus cincang dan skema julat

Seperti yang ditunjukkan dalam rajah di bawah, kita dapat melihat bahawa kumpulan kumpulan menyimpan data dengan ID dari 0 hingga 40 juta , dan kemudian terdapat tiga pangkalan data DB0 Terdapat empat pangkalan data dalam DB1, DB2 dan DB0 Terdapat tiga pangkalan data dalam DB1 dan DB2

Jika id ialah 15000, maka ambil modulo 10 (mengapa mengambil. modulo 10 kerana terdapat 10 jadual), ambil 0 dan kemudian jatuhkan DB_0, dan kemudian jatuh ke dalam Table_0 mengikut julat.

Ringkasan: Menggabungkan modulo hash dan skema julat bukan sahaja dapat mengelakkan masalah data panas, tetapi juga memudahkan pengembangan data pada masa hadapan

Kami sudah tahu Sekarang kita mempunyai pengetahuan tentang pembahagian mysql dan sub-jadual, mari kita lihat perbezaan antara kedua-dua teknologi dan senario terpakainya

Perbezaan antara pembahagian dan sub-jadual:

1 , Dari segi pelaksanaan

• sub-jadual mysql ialah sub-jadual sebenar Selepas jadual dibahagikan kepada banyak jadual, setiap satu kecil jadual ialah jadual lengkap, sepadan dengan tiga Fail, fail data .MYD, fail indeks .MYI, struktur jadual .frm
• Pembahagian adalah berbeza Selepas jadual besar dipisahkan, ia masih satu jadual dan tidak akan menjadi dua jadual, tetapi ia Terdapat lebih banyak blok yang menyimpan data.

2. Meningkatkan prestasi

• Fokus sub-jadual ialah bagaimana untuk menambah baik keselarasan MySQL apabila mengakses data
• Bagi sekatan, bagaimana untuk memecahkan keupayaan baca dan tulis cakera untuk mencapai tujuan meningkatkan prestasi mysql.

3 Dari segi kesukaran pelaksanaan

1. Terdapat banyak cara untuk membahagikan jadual . Kaedah ini adalah kira-kira kesukaran yang sama seperti pembahagian akar dan boleh telus kepada kod program. Jika anda menggunakan kaedah pembahagian jadual lain, ia akan menjadi lebih menyusahkan daripada pembahagian. 2. Pelaksanaan partition agak mudah. ​​Tiada perbezaan antara mencipta jadual partition dan membina jadual biasa, dan ia telus pada hujung kod

Hubungan antara partition dan jadual

1. Ia boleh meningkatkan prestasi mysql dan mempunyai prestasi yang baik dalam keadaan konkurensi yang tinggi.

2. Pembahagian jadual dan pembahagian tidak tidak konsisten dan boleh bekerjasama antara satu sama lain Bagi jadual dengan bilangan lawatan yang banyak dan jumlah data jadual, kita boleh menggabungkan pembahagian jadual dan pembahagian lawatan tidak besar Tetapi untuk jadual dengan banyak data, kami boleh membahagikannya.

Masalah dengan sub-pangkalan data dan sub-jadual

1. -jadual, disebabkan oleh Data disimpan dalam perpustakaan yang berbeza, dan pengurusan transaksi pangkalan data menjadi sukar. Jika anda bergantung pada fungsi pengurusan transaksi yang diedarkan bagi pangkalan data itu sendiri untuk melaksanakan urus niaga, anda akan membayar harga prestasi tinggi jika aplikasi membantu dalam kawalan dan membentuk transaksi logik program, ia juga akan menyebabkan beban pengaturcaraan;

2. Isu silang pangkalan data dan gabungan silang jadual

Selepas melaksanakan pangkalan data dan pembahagian jadual, tidak dapat dielakkan bahawa data dengan korelasi logik yang kuat akan dibahagikan kepada Hidup jadual yang berbeza dan pustaka yang berbeza, pada masa ini, operasi perkaitan jadual akan dihadkan Kami tidak boleh menyertai jadual yang terletak dalam sub-pangkalan data yang berbeza, dan kami juga tidak boleh menyertai jadual dengan butiran sub-jadual yang berbeza boleh dilengkapkan dalam satu pertanyaan mungkin tidak dapat diselesaikan.

3. Beban pengurusan data tambahan dan tekanan pengiraan data

Beban pengurusan data tambahan, yang paling jelas ialah masalah kedudukan data dan pelaksanaan penambahan, pemadaman data, pengubahsuaian dan pertanyaan , ini boleh diselesaikan melalui aplikasi, tetapi mereka pasti akan menyebabkan operasi logik tambahan Sebagai contoh, untuk jadual pengguna jadual pengguna yang merekodkan skor pengguna, perniagaan memerlukan mencari 100 skor terbaik Sebelum membahagikan jadual, hanya satu Susunan demi penyata boleh dilakukan, tetapi selepas membahagikan jadual, n susunan mengikut penyata akan diperlukan untuk mengetahui 100 data pengguna teratas bagi setiap jadual pembahagian, dan kemudian gabungkan dan kira data ini untuk mendapatkan hasilnya.

Pilihan 3: Pengarkiban panas dan sejuk

Mengapa pengarkiban panas dan sejuk: Sebenarnya, sebabnya serupa dengan pilihan 2, iaitu untuk mengurangkan jumlah data dalam satu jadual dan ketinggian pokok menjadi lebih rendah , pertanyaan mengalami kurang cakera IO, yang boleh meningkatkan kecekapan. Jika data perniagaan anda mempunyai perbezaan yang jelas antara panas dan sejuk, sebagai contoh, anda hanya perlu memaparkan data untuk minggu atau bulan lalu. Dalam kes ini, data untuk minggu dan sebulan ini dipanggil data panas, dan data selebihnya ialah data sejuk. Kemudian kami boleh mengarkibkan data sejuk dalam jadual pangkalan data lain untuk meningkatkan kecekapan operasi data panas kami.

Mari bincang tentang proses pengarkiban

• Buat jadual arkib Pada dasarnya, jadual arkib yang dibuat hendaklah selaras dengan jadual asal

• Pemulaan data jadual arkib

• Proses pemprosesan data tambahan perniagaan

• Proses pemerolehan data

Cara memilih tiga pilihan di atas

方案	试用场景	优点	缺点
数据表分区	1.数据量较大 2.查询场景只在某个区 3.没有联合查询的场景	分区分表是在物理上对数据表所对应的文件进行拆分，对应的表名是不变的，所以不会影响到之前业务逻辑的sql	分表后的查询等业务会创建对应的对象，也会造成一定的开销分区数据若要聚合的话 耗费时间也较长；使用范围不适合数据量千万级以上的
数据表分表	数据量较大,无法区分明显冷热区  且数据可以完整按照区间划分	适用于对冷热分区的界限不是很明显的数据，对后续类似的数据可以采用该方式，将大表拆分成小表 提高查询插入等效率	若大数据表逐渐增多 那么对应的数据库表越来越多 每个表都需要分表；区间的划分较为固定 若后续单表的数据量大起来 也会对性能造成影响；实现复杂度相对方案三比较复杂  需要测试整个实现过程 在编码层处理 对原有业务有影响；
冷热归档分库	数据量较大；数据冷热分区明显；冷数据使用频率极低；	数据迁移的过程对业务的影响较小 开发量也较少减少成本	需要确认分表规则

Anda boleh memilih yang sesuai dengan perniagaan anda mengikut senario perniagaan anda plan, saya akan berikan sedikit idea di sini ~

Jadi di sini, apa yang saya ingin bincangkan sudah hampir tamat.

[Cadangan berkaitan: tutorial video mysql]

Atas ialah kandungan terperinci Bagaimanakah Mysql mengendalikan jadual data yang besar? Perkongsian penyelesaian. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!