cari
Rumahpembangunan bahagian belakangtutorial phpphper优化MySQL千万级大表的方法详解

phper优化MySQL千万级大表的方法详解

首先采用Mysql存储千亿级的数据,确实是一项非常大的挑战。Mysql单表确实可以存储10亿级的数据,只是这个时候性能非常差,项目中大量的实验证明,Mysql单表容量在500万左右,性能处于最佳状态。

针对大表的优化,主要是通过数据库分库分表来解决,目前比较普遍的方案有三个:分区分库分表NoSql/NewSql。实际项目中,这三种方案是结合的,目前绝大部分系统的核心数据都是以RDBMS存储为主,NoSql/NewSql存储为辅。

分区

首先来了解一下分区方案。

分区表是由多个相关的底层表实现的。这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区,存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引只是在各个底层表上各自加上一个相同的索引。这个方案对用户屏蔽了sharding的细节,即使查询条件没有sharding column,它也能正常工作(只是这时候性能一般)。

不过它的缺点很明显:很多的资源都受到单机的限制,例如连接数,网络吞吐等。如何进行分区,在实际应用中是一个非常关键的要素之一。

下面开始举例:以客户信息为例,客户数据量5000万加,项目背景要求保存客户的银行卡绑定关系,客户的证件绑定关系,以及客户绑定的业务信息。

此业务背景下,该如何设计数据库呢。项目一期的时候,我们建立了一张客户业务绑定关系表,里面冗余了每一位客户绑定的业务信息。

基本结构大致如下:

c0e785aef05b2b1681d83881ca03326.png

 

 

查询时,对银行卡做索引,业务编号做索引,证件号做索引。随着需求大增多,这张表的索引会达到10个以上。而且客户解约再签约,里面会保存两条数据,只是绑定的状态不同。

假设我们有5千万的客户,5个业务类型,每位客户平均2张卡,那么这张表的数据量将会达到惊人的5亿,事实上我们系统用户量还没有过百万时就已经不行了。这样的设计绝对是不行的,无论是插入,还是查询,都会让系统崩溃。

 mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看), 一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。这三个文件都非常的庞大,尤其是.myd文件,快5个G了。下面进行第一次分区优化,Mysql支持的分区方式有四种:

000d8eff41545e4c08015a3202dc955.png

在我们的项目中,range分区和list分区没有使用场景,如果基于绑定编号做range或者list分区,绑定编号没有实际的业务含义,无法通过它进行查询,因此,我们就剩下 HASH 分区和 KEY 分区了,HASH分区仅支持int类型列的分区,且是其中的一列。

KEY 分区倒是可以支持多列,但也要求其中的一列必须是int类型;看我们的库表结构,发现没有哪一列是int类型的,如何做分区呢?增加一列,绑定时间列,将此列设置为int类型,然后按照绑定时间进行分区,将每一天绑定的用户分到同一个区里面去。

这次优化之后,我们的插入快了许多,但是查询依然很慢,为什么?

因为在做查询的时候,我们也只是根据银行卡或者证件号进行查询,并没有根据时间查询,相当于每次查询,mysql都会将所有的分区表查询一遍。

进行第二次方案优化,既然 HASH 分区和 KEY分区要求其中的一列必须是int类型的,那么创造出一个int类型的列出来分区是否可以?

分析发现,银行卡的那串数字有秘密。银行卡一般是16位到19位不等的数字串,我们取其中的某一位拿出来作为表分区是否可行呢,通过分析发现,在这串数字中,其中确实有一位是0到9随机生成的,我们基于银行卡号+随机位进行KEY分区,每次查询的时候,通过计算截取出这位随机位数字,再加上卡号,联合查询,达到了分区查询的目的,需要说明的是,分区后,建立的索引,也必须是分区列,否则Mysql还是会在所有的分区表中查询数据。

通过银行卡号查询绑定关系的问题解决了,那么证件号呢,如何通过证件号来查询绑定关系。

前面已经讲过,做索引一定是要在分区健上进行,否则会引起全表扫描。我们再创建了一张新表,保存客户的证件号绑定关系,每位客户的证件号都是唯一的,新的证件号绑定关系表里,证件号作为了主键,那么如何来计算这个分区健呢,客户的证件信息比较庞杂,有身份证号,港澳台通行证,机动车驾驶证等等,如何在无序的证件号里找到分区健。

为了解决这个问题,我们将证件号绑定关系表一分为二,其中的一张表专用于保存身份证类型的证件号,另一张表则保存其他证件类型的证件号,在身份证类型的证件绑定关系表中,我们将身份证号中的月数拆分出来作为了分区健,将同一个月出生的客户证件号保存在同一个区,这样分成了12个区,其他证件类型的证件号,数据量不超过10万,就没有必要进行分区了。

这样每次查询时,首先通过证件类型确定要去查询哪张表,再计算分区健进行查询。作了分区设计之后,保存2000万用户数据时银行卡表的数据保存文件就分成了10个小文件,证件表的数据保存文件分成了12个小文件,解决了这两个查询的问题,还剩下一个问题:业务编号怎么办?

一个客户有多个签约业务,如何进行保存?这时候,采用分区的方案就不太合适了,它需要用到分表的方案。

 分表

我们前面有提到过对于mysql,其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大时,操作系统对大文件的操作就会比较麻烦耗时,且有的操作系统就不支持大文件,这个时候就必须分表了。

另外对于mysql常用的存储引擎是Innodb,它的底层数据结构是B+树。当其数据文件过大的时候,查询一个节点可能会查询很多层次,而这必定会导致多次IO操作进行装载进内存,肯定会耗时的。

除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁,那么当更改表结构的时候,这时候就会树进行加锁,当表文件大的时候,这可以认为是不可实现的。所以综上我们就必须进行分表与分库的操作。

如何进行分库分表,目前互联网上有许多的版本,比较知名的一些方案:阿里的TDDL,DRDS和cobar,京东金融的sharding-jdbc;民间组织的MyCAT;360的Atlas;美团的zebra;其他比如网易,58,京东等公司都有自研的中间件。

这么多的分库分表中间件方案归总起来,就两类:client模式和proxy模式。

c18d4cba23c4d4ee9848df14844262c.png

 

 

client模式

 18b40cf89166eeccd352b6c1dbc25fe.png

 

proxy模式

无论是client模式,还是proxy模式。几个核心的步骤是一样的:SQL解析,重写,路由,执行,结果归并。个人比较倾向于采用client模式,它架构简单,性能损耗也比较小,运维成本低。

如何对业务类型进行分库分表。分库分表最重要的一步,即sharding column的选取,sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。而sharding column的选取跟业务强相关。

在我们的项目场景中,sharding column无疑最好的选择是业务编号。通过业务编号,将客户不同的绑定签约业务保存到不同的表里面去,根据业务编号路由到相应的表中进行查询,达到进一步优化sql的目的。

更多相关php知识,请访问php教程

Atas ialah kandungan terperinci phper优化MySQL千万级大表的方法详解. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!

Kenyataan
Artikel ini dikembalikan pada:cnblogs. Jika ada pelanggaran, sila hubungi admin@php.cn Padam
php怎么把负数转为正整数php怎么把负数转为正整数Apr 19, 2022 pm 08:59 PM

php把负数转为正整数的方法:1、使用abs()函数将负数转为正数,使用intval()函数对正数取整,转为正整数,语法“intval(abs($number))”;2、利用“~”位运算符将负数取反加一,语法“~$number + 1”。

php怎么实现几秒后执行一个函数php怎么实现几秒后执行一个函数Apr 24, 2022 pm 01:12 PM

实现方法:1、使用“sleep(延迟秒数)”语句,可延迟执行函数若干秒;2、使用“time_nanosleep(延迟秒数,延迟纳秒数)”语句,可延迟执行函数若干秒和纳秒;3、使用“time_sleep_until(time()+7)”语句。

php怎么除以100保留两位小数php怎么除以100保留两位小数Apr 22, 2022 pm 06:23 PM

php除以100保留两位小数的方法:1、利用“/”运算符进行除法运算,语法“数值 / 100”;2、使用“number_format(除法结果, 2)”或“sprintf("%.2f",除法结果)”语句进行四舍五入的处理值,并保留两位小数。

php怎么根据年月日判断是一年的第几天php怎么根据年月日判断是一年的第几天Apr 22, 2022 pm 05:02 PM

判断方法:1、使用“strtotime("年-月-日")”语句将给定的年月日转换为时间戳格式;2、用“date("z",时间戳)+1”语句计算指定时间戳是一年的第几天。date()返回的天数是从0开始计算的,因此真实天数需要在此基础上加1。

php怎么替换nbsp空格符php怎么替换nbsp空格符Apr 24, 2022 pm 02:55 PM

方法:1、用“str_replace(" ","其他字符",$str)”语句,可将nbsp符替换为其他字符;2、用“preg_replace("/(\s|\&nbsp\;||\xc2\xa0)/","其他字符",$str)”语句。

php怎么判断有没有小数点php怎么判断有没有小数点Apr 20, 2022 pm 08:12 PM

php判断有没有小数点的方法:1、使用“strpos(数字字符串,'.')”语法,如果返回小数点在字符串中第一次出现的位置,则有小数点;2、使用“strrpos(数字字符串,'.')”语句,如果返回小数点在字符串中最后一次出现的位置,则有。

php字符串有没有下标php字符串有没有下标Apr 24, 2022 am 11:49 AM

php字符串有下标。在PHP中,下标不仅可以应用于数组和对象,还可应用于字符串,利用字符串的下标和中括号“[]”可以访问指定索引位置的字符,并对该字符进行读写,语法“字符串名[下标值]”;字符串的下标值(索引值)只能是整数类型,起始值为0。

php怎么读取字符串后几个字符php怎么读取字符串后几个字符Apr 22, 2022 pm 08:31 PM

在php中,可以使用substr()函数来读取字符串后几个字符,只需要将该函数的第二个参数设置为负值,第三个参数省略即可;语法为“substr(字符串,-n)”,表示读取从字符串结尾处向前数第n个字符开始,直到字符串结尾的全部字符。

See all articles

Alat AI Hot

Undresser.AI Undress

Undresser.AI Undress

Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover

AI Clothes Remover

Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool

Undress AI Tool

Gambar buka pakaian secara percuma

Clothoff.io

Clothoff.io

Penyingkiran pakaian AI

AI Hentai Generator

AI Hentai Generator

Menjana ai hentai secara percuma.

Alat panas

DVWA

DVWA

Damn Vulnerable Web App (DVWA) ialah aplikasi web PHP/MySQL yang sangat terdedah. Matlamat utamanya adalah untuk menjadi bantuan bagi profesional keselamatan untuk menguji kemahiran dan alatan mereka dalam persekitaran undang-undang, untuk membantu pembangun web lebih memahami proses mengamankan aplikasi web, dan untuk membantu guru/pelajar mengajar/belajar dalam persekitaran bilik darjah Aplikasi web keselamatan. Matlamat DVWA adalah untuk mempraktikkan beberapa kelemahan web yang paling biasa melalui antara muka yang mudah dan mudah, dengan pelbagai tahap kesukaran. Sila ambil perhatian bahawa perisian ini

PhpStorm versi Mac

PhpStorm versi Mac

Alat pembangunan bersepadu PHP profesional terkini (2018.2.1).

SublimeText3 versi Mac

SublimeText3 versi Mac

Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

MinGW - GNU Minimalis untuk Windows

MinGW - GNU Minimalis untuk Windows

Projek ini dalam proses untuk dipindahkan ke osdn.net/projects/mingw, anda boleh terus mengikuti kami di sana. MinGW: Port Windows asli bagi GNU Compiler Collection (GCC), perpustakaan import yang boleh diedarkan secara bebas dan fail pengepala untuk membina aplikasi Windows asli termasuk sambungan kepada masa jalan MSVC untuk menyokong fungsi C99. Semua perisian MinGW boleh dijalankan pada platform Windows 64-bit.

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Persekitaran pembangunan bersepadu PHP yang berkuasa