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.
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
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;
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;
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
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.2Berapa banyak penunjuk disimpan dalam nod bukan daun? Kami menganggap bahawa ID kunci utama ialah
1170 * 16=187201170 *1170 *16 =21902400Bagaimana untuk menyelesaikan masalah terlalu banyak data dalam satu jadual dan pertanyaan perlahan
这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案
为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率
分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。
我们首先看一下分区有什么优缺点：
表分区有什么好处？
与单个磁盘或文件系统分区相比，可以存储更多的数据。
对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
一些查询可以得到极大的优化，这主要是借助于满足一个给定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之类的操作
知道了两个知识后，我们来看一下分库分表的方案
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.
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?
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
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
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.
2. Isu silang pangkalan data dan gabungan silang jadual
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.
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.
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
| 方案 | 试用场景 | 优点 | 缺点 |
|---|---|---|---|
| 数据表分区 | 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!
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
