mysql存储引擎

DBMS的瓶颈在于IO而非CPU。

慎用数据库的一些特性，例如外键（业务量爆炸的时候分库分表将是一个灾难）。虽然不建议使用物理外键，但是相关联的列上一定要建立索引。

  • 在双十一的场景中取消掉计划任务（例如定时备份）。

在大促中什么影响了数据库性能

超高的QPS和TPS

QPS(Query Per Second)
风险：效率低下的SQL。数据库中的性能问题80%就是由慢查询造成的。也就是大多数的数据库问题可以通过对SQL进行优化来解决。

TPS（Transition Per Second）

大量并发和超过的CPU使用率

高并发可能导致DB连接被占满；超高CPU使用率可能因为CPU资源耗尽而出现宕机。这里并发量指的是同一时刻需要DB服务器处理请求的数量，而连接量往往比并发量大得多，因为现在的系统中每一个前端服务器（例如nginx,tomcat,apache）都会对数据库建立多个连接，而在众多的连接中往往只有几个是在请求数据库处理的，其他的大多数连接都会处于sleep状态。数据库对于允许建立的连接数目是有限的（max_connections默认为100）

磁盘IO

磁盘IO性能突然下降（使用更快的磁盘设备，SSD，fashion IO），其他大量消耗磁盘性能的计划任务（调整计划任务，做好磁盘维护）。例如平时在主库上进行备份的，在大促的时候应该切换到从库上备份。

网卡流量

大多数网卡是1000mbps (bits) = 1000 / 8 = 100 MB/s(byte)

如何避免无法连接数据库的情况：

  1. 减少从服务器的数量。（从服务器需要从主服务器上进行复制）
  2. 进行分级缓存
  3. 避免使用SELECT *
  4. 分离业务网络和服务器网络

大表带来的问题

大表可以从2个维度来进行定义：

  • 记录数目巨大，单表超过1000W行

  • 表数据文件巨大，超过10G

  • 大表往往意味着慢查询（很难在一定时间内过滤出所需要的数据）的产生，大表往往会降低SQL的效率。

  • 大表对DDL也会产生影响。例如建立索引需要很长时间。5.5版本以下建立索引会锁表，而>=5.5的时候虽然不会锁表，但是会引起主从延迟。

对于大表有2种策略：分库分表和历史数据归档

分库分表把大表分成多个小表

难点：

  • 分表主键的选择
  • 分表后跨分区数据的查询和统计

大表的历史数据归档，可以尽量减少业务前后端的影响。难点在于：

  • 归档时间点的选择
  • 如何进行归档操作

大事务带来的问题

事务的隔离性（ISOLATION），SQL标准中定义了4种隔离级别：

1
SHOW VARIABLES LIKE '%iso%';
  • 未提交读（READ UNCOMMITED）
  • 已提交读 (READ COMMITED)
  • 可重复读 (REPEATABLE READ)，InnoDB默认级别。
  • 可串行化（SERIALIZABLE）,最高

验证事务的隔离级别我们可以分别连接到两个mysql shell，在一个shell中进行插入操作

MySQL默认隔离级别-可重复读

大事务的运行时间比较长，操作的数据比较多的事务。可能造成锁定太多数据，造成大量的阻塞和锁超时。回滚的时间较长，并且在回滚的过程中数据也是被锁定的。执行时间长可能造成主从延迟。

处理大事务一般有以下的策略：

  1. 避免一次性处理太多数据，可以进行分批处理；
  2. 查询操作（SELECT）可以从事务中移除，事务中只保留必要的写操作。

影响MySQL性能的几个因素

CPU数量和主频的权衡

如果我们的应用是计算密集型的就应该选择更高主频的U，因为现在的MySQL不支持对同一SQL进行并发处理，但是对于高并发的应用，CPU的数量越多越好。

在MyISAM中会将索引缓存在内存中，而数据通过OS进行缓存；InnoDB会同时在内存中缓存数据和索引。

缓存系统不但对读操作影响深远，对写操作也是有好处的。例如商品浏览次数，如果我们每次浏览都对数据库进行更新操作，必将导致大量IO，我们可以在每次浏览的时候修改缓存中的计数器，当计数器达到某一个值（例如100）的时候进行写库操作，这样就将100次的更新操作合并成了一次更新。缓存能够延缓DB的更新频次。

CentOS系统参数优化

对于一个处于监听状态的端口都会有自己的监听队列

内核相关参数(/etc/sysctl.conf)

1
2
kernel.shmmax = 4294967295 # 单个共享内存段的最大值，应足够大，以便能够在一个内存段中容纳下整个InnoDB缓冲池的大小

增加资源限制（/etc/security/limits.conf）

该文件实际上是Linux PAM也就是插入式认证模块的配置文件。打开文件数目的限制。

1
2
* soft nofile 65535 # * 表示对所有用户有效，soft，当前系统生效的设置
* hard nofile 65535  # hard 系统所能设置的最大值，nofile 所限制的资源是打开文件的最大数目 65535 限制数量

磁盘调度策略（/sys/block/devname/queue/scheduler），例如：

1
2
cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]

以上的默认磁盘调度策略是cfq，除此之外还存在noop(电梯式调度策略)，deadline（截止时间调度策略，数据库最好选择），anticipatory（预料IO调度策略）

1
echo deadline > /sys/block/sda/queue/scheduler

文件系统对性能的影响

ext3/4系统挂载参数/etc/fstab

MySQL体系结构

MySQL的插入式存储引擎将数据的处理和数据存储相分离。

MySQL体系结构

SQL语句如何从文件中获得我们所需要查询的数据，这个的具体实现方式则是下一层存储引擎层进行实现。存储是针对于表而不是针对于库的。在一个库中可以使用不同的存储引擎建立不同的表

MySQL存储引擎

MySQL存储引擎

MyISAM

5.5版本之前的默认存储引擎，也是系统表和临时表（在排序、分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表）使用的存储引擎。。由MYD和MYI组成。该引擎使用表级锁，对表中的数据进行修改时会锁表，在对表中的数据进行读取时需要加共享锁。表损坏修复可以使用以下命令：

1
2
mysql> check table t_username; 
mysql> repair table t_username;

还可以使用myisamchk命令行工具

测试MyISAM存储引擎

1
mysql> CREATE TABLE t_test(id INT,name VARCHAR(20)) ENGINE=MyISAM;

查看mysql数据文件的位置可以使用show variables where Variable_name ='datadir';命令，我们进入数据库数据文件所在目录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@muke-49-468585-v52l3 db_test]# pwd                                  
/data/mysqld/data/db_test                                                 
[root@muke-49-468585-v52l3 db_test]# ll                                   
total 10                                                                  
-rw-r----- 1 mysql mysql   65 Jul 23 11:25 db.opt                         
-rw-r----- 1 mysql mysql 8586 Jul 23 11:25 t_test.frm  # 表结构信息
-rw-r----- 1 mysql mysql    20 Jul 23 11:25 t_test.MYD # 表数据            
-rw-r----- 1 mysql mysql 1024 Jul 23 11:25 t_test.MYI  # 索引信息              
[root@muke-49-468585-v52l3 db_test]# myisampack -b -f t_test.MYI  # 压缩表
[root@muke-49-468585-v52l3 db_test]# ll                                   
total 11                                                                  
-rw-r----- 1 mysql mysql   65 Jul 23 11:25 db.opt                         
-rw-r----- 1 mysql mysql 8586 Jul 23 11:25 t_test.frm                     
-rw-rw-rw- 1 mysql mysql   54 Jul 23 11:32 t_test.MYD # 由于数据较小，压缩后的尺寸更大了                    
-rw-r----- 1 mysql mysql 1024 Jul 23 11:36 t_test.MYI                     
-rw-rw-rw- 1 mysql mysql   20 Jul 23 11:32 t_test.OLD # 压缩前文件的备份

支持全文索引，支持压缩（myisampack)，压缩之后进行插入操作报错，提示该表是只读的。

限制：

<5.0版本默认表大小为4G，如果需要存储大表需要修改MAX_Rows和AVG_ROW_LENGTH,5.0版本之后默认支持256TB。

使用场景：

  • 非事务型应用
  • 只读类应用（例如报表类）
  • 空间类应用

InnoDB

MySQL5.5之后的默认存储引擎。使用表空间进行数据存储。具有由innodb_file_per_table这个参数决定，如果该参数为ON,则表示独立的表空间tablename.idb，如果该参数为OFF则表示系统表空间：ibdataX.

1
2
mysql> show variables like 'innodb_file_per_table';
mysql> CREATE TABLE t_innodb(id int,name varchar(20)) ENGINE='innodb';

接下来我们进入数据文件目录查看下该种存储引擎对应的数据文件：

1
2
3
4
5
6
[root@muke-49-468585-v52l3 db_test]# pwd                                  
/data/mysqld/data/db_test                                                 
[root@muke-49-468585-v52l3 db_test]# ll t_innodb*                         
-rw-r----- 1 mysql mysql  8586 Jul 23 12:54 t_innodb.frm                  
-rw-r----- 1 mysql mysql 98304 Jul 23 12:54 t_innodb.ibd  # 每一个表一个单独的表空间              
[root@muke-49-468585-v52l3 db_test]#

接下来通过mysql shell修改innodb_file_per_table全局变量为OFF

1
2
mysql> SET global innodb_file_per_table=off; 
mysql> CREATE TABLE t_innodb_g(id int,name varchar(20)) ENGINE='innodb'; # 创建新表

再次查看数据库的数据目录发现只存在frm文件：

1
2
3
4
[root@muke-49-468585-v52l3 db_test]# ll t_innodb*                         
-rw-r----- 1 mysql mysql  8586 Jul 23 12:54 t_innodb.frm                  
-rw-r----- 1 mysql mysql  8586 Jul 23 13:01 t_innodb_g.frm # 存储在共享表空间，即上一级目录的ibdata1中                
-rw-r----- 1 mysql mysql 98304 Jul 23 12:54 t_innodb.ibd

系统表空间和独立表空间之间如何选择

  • 系统表空间无法简单收缩文件的大小，删除数据后表空间的大小并不会缩小；会产生IO瓶颈
  • 独立表空间可以通过optimize table命令收缩系统文件；独立表空间可以同时向多个文件刷新数据。

强烈建议使用独立表空间。

InnoDB是如何做到事务的ACID特性的？

  • Redo Log，重做日志，实现持久性，重做日志缓冲区（show variables like 'innodb_log_buffer_size';），和重做日志文件(数据目录下的ib_logfile0和ib_logfile1，这里有两个log file文件和show variables like 'innodb_log_files_in_group';相对应)
  • Undo Log，回滚日志，未提交的事务进行回滚和实现MVC（多版本并发控制）。

Redo Log是顺序写入的，而Undo Log则是随机读写的。

InnoDB支持行级锁，可以最大程度支持并发。锁的主要作用是实现并发控制和事务的隔离性。我们可以在一个mysql shell中执行lock table t_innodb write;,然后在另一个shell中对该表进行读或者写操作都被阻塞，因为我们对t_innodb这张表加了表级排它锁。直到我们在第一个shell中执行unlock tables;，第二个shell才会有数据返回。

锁的粒度和资源的开销是负相关的。锁的粒度越小（行锁），资源占用越多，并发越大。

InnoDB状态检查可以使用show engine innodb status

其他存储引擎

查看支持的存储引擎可以使用show engines;命令

CSV存储引擎直接将表中的数据保存成CSV文件（因此每一列都不允许为空），我们可以使用vim直接查看或者编辑表中的数据（记得flush tables;），不支持索引，每次查询都会进行全表扫描，不适合大表和在线处理。该引擎可以作为数据交换的中间表（可以直接将excel导出为csv复制到mysql数据目录下）。

Archive存储引擎会使用zlib对表数据进行塔索，节省磁盘IO，数据文件的后缀为arz。只支持insert和select操作，只支持在自增id列上增加索引。使用场景是日志或者数据采集类的应用。

tips:等值查找使用hash索引，而范围查找使用Btree索引。

Memory存储引擎，也称为HEAP存储引擎，将数据保存在内存中。支持hash索引（默认）和Btree索引，索引字段都为固定长度，例如：varchar(20) = char(20)。不支持BLOB和TEXT等大字段。使用表级锁，所以尽管数据保存在内存中，并发性不见得比InnoDB高，表的大小由max_heap_table_size决定（默认16M）。该种存储引擎的应用场景是：用于查找或者映射表，例如邮编和地区的对应表和保存数据分析过程中的临时表。用于周期性聚合数据的结果表。由于Memory存储引擎的数据易丢失，所以要求数据是可以再生的。

1
2
3
mysql> create index idx_c1 on t_memory(c1);  # 在c1列上建立hash索引
mysql> create index idx_c2 using btree on t_memory(c2); # 在c2列上建立BTree索引
mysql> show index from t_memory; # 查看索引

Federated存储引擎提供了访问远程MySQL服务器上表的方法，本地不存储数据，数据全部放在远程服务器上。本地需要保存表的结构和远程服务器的信息。默认禁止，我们可以在启动时增加federated参数,适合偶尔的统计分析或者手工查询。

不要在同一个数据库中使用多个存储引擎，这样可能导致潜在的bug,例如：事务失败回滚之后只有InnoDB上进行了回滚而myisam表的中的数据并没有进行回滚，出现的数据不一致的问题。

MySQL服务器参数

mysql配置文件的加载顺序可以使用mysqld --help --verbose | grep -A 1 'Default options'命令进行查看

全局参数：set global 参数名=参数值
会话参数：set session 参数名=参数值

内存参数

要配置内存参数首先要弄清楚下面的几个问题：

  1. 确定可以使用的内存上限
  2. 确定每个连接使用的内存（我们很容易犯的错误就是给每个连接分配的内存过大），主要有以下的几个参数：sort_buffer_size,join_buffer_size,read_buffer_size,read_rnd_buffer_size

IO配置参数

InnoDB

innodb_log_file_size（单个事务日志的大小）和innodb_log_files_in_group（事务日志文件的个数）决定了InnoDB事务日志（Redo log的大小和数量），事务日志的总大小等于前两者的积。为了减少提交事务时产生的IO开销，InnoDB采用了预写日志的方式——也就是说在事务提交的时候会先写入到事务日志中，而不是每次都将修改后的数据文件刷新到磁盘中，因为事务的修改通常会将数据和索引映射到表空间的随机位置，所以刷新数据到文件将产生大量的随机IO，而记录日志所需要的则是顺序IO。所以相比于刷新脏数据到数据文件，记录日志的方式要快得多，一旦事务日志安全写入到磁盘中，事务就算是持久化了，因为即使是变更还没有写入到数据文件，如果这时候发生了服务器宕机，我们还是可以通过事务日志来恢复数据。

Innodb_flush_log_at_trx_commit:

0：每秒进行一次log写入到cache，并flush log到磁盘；
1：默认，每次事务提交的时候执行log写入cache并flush log到磁盘；
2：推荐，每次事务提交执行log写入cache，每秒执行一次flush log到磁盘。

Innodb_flush_method=O_DIRECT
Innodb_file_per_table=1
Innodb_doublewrite=1,启用双写缓存，增加安全性

MyISAM

delay_key_write，有3个值：
OFF：每次写操作后刷新键缓冲中的脏块到磁盘
ON：只对建表时制定了delay_key_write选项的表使用延迟刷新
ALL：对所有MyISAM表都使用延迟键写入。

安全配置参数

expire_logs_days，自用清理binlog的天数
max_allowed_packet，控制MySQL可以接收的包的大小
skip_name_resolve,禁用dns查找
sysdate_is_now,确保sysdate()返回正确的日期，主从复制相关
read_only,禁止非super选线的用户的写权限，建议在从复制中启用，可以保证主从复制的一致性。
skip_slave_start,禁用slave自动恢复

MySQL基准测试

基准测试是一种测量和评估软件性能指标的活动，用于建立某个时刻的性能基准，以便当系统发生软硬件变化时重新基准测试以评估变化对性能的影响。是一种针对系统设置的压力测试，应该尽量直接、简单、易于比较，用于评估服务器的处理能力（压力测试是对真实的业务数据进行测试，获得真实系统所承受的压力，基准测试可能并不关心业务逻辑，所使用的查询和业务的真实性可以和业务环境没关系）

mysql 5.7自带了基准测试工具mysqlslap，可以模拟服务器负载并输出相关的统计信息。我们可以使用它的–help来查看参数：

1
$ mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=benchmark_test

我们可以使用--print-only参数来查看生成的sql脚本，如果出现快速滚屏的问题可以用管道：cat bidfile.txt | less。

sysbench是一款非常常用的测试工具。

1
2
3
4
5
6
7
$ sysbench --test=cpu --cpu-max-prime=10000 run # 测试CPU
$ sysbench --test=fileio --file-total-size=10G prepare # 文件IO测试，生成测试文件
$ sysbench --test=fileio --number-thread=8 --init-rng=on --file-total-size=1G --file-test-mode=rndrw --report-interval=1 run # 文件IO测试
$ # 使用该工具对数据库进行基准测试的时候需要建立对应的数据库
mysql> create database db_benchmark_test;
mysql> grant all privileges on *.* to benchmark_test@'localhost' identified by '123456';
mysql> flush privileges;

以上的准备工作已经做好，接下来进入到sysbench源码的sysbench/tests/db目录下（该目录下有现成的lua脚本）

1
2
$ sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=db_benchmark_test --mysql-user=benchmark_test --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/tmp/mysql.sock prepare # 生成测试数据
$ sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=db_benchmark_test --mysql-user=benchmark_test --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/tmp/mysql.sock run # 测试

数据库结构优化

点击这里下载建表语句。数据库结构优化一方面是减少数据冗余，另一方面是尽量减少数据维护中出现的更新、插入和删除异常。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+--------------+-------------+------+-----+-------------------+-----------------------------+                                                              
| Field        | Type        | Null | Key | Default           | Extra                       |                                                              
+--------------+-------------+------+-----+-------------------+-----------------------------+                                                              
| stu_no       | int(11)     | NO   | PRI | NULL              |                             |                                                              
| stu_name     | varchar(10) | NO   |     | NULL              |                             |                                                              
| birthday     | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |                                                              
| course_name  | varchar(10) | NO   | PRI | NULL              |                             |                                                              
| score        | int(11)     | YES  |     | NULL              |                             |                                                              
| course_point | int(11)     | YES  |     | NULL              |                             |                                                              
+--------------+-------------+------+-----+-------------------+-----------------------------+   
+--------+----------+---------------------+-------------+-------+--------------+                                                                           
| stu_no | stu_name | birthday            | course_name | score | course_point |                                                                           
+--------+----------+---------------------+-------------+-------+--------------+                                                                           
|      1 | a        | 1993-07-07 00:00:00 | englisg     |  NULL |            9 |                                                                           
|      1 | a        | 1993-07-07 00:00:00 | math        |  NULL |           10 |                                                                           
|      2 | b        | 1983-07-17 00:00:00 | englisg     |  NULL |            9 |                                                                           
|      2 | b        | 1983-07-17 00:00:00 | math        |  NULL |           10 |                                                                           
+--------+----------+---------------------+-------------+-------+--------------+

首先以上的表中多次出现了冗余信息，姓名、课程、生日、学分都是冗余的

  • 插入异常：表中的某个实体随着另一个实体的存在而存在，如果缺少了某个实体就无法表示另一个实体。如果我们想要新加入一门语文课，学分是10分，但是没有任何学生选择这门课程。我们此时无法插入（因为学号是主键，但是没有默认值）
  • 更新异常：更改表中某个实体的单独属性，需要对多行进行更新。如果我们将数学学分更新为15个学分，需要更新2行数据。——选课的学生越多，需要更新的数据越多
  • 删除异常：删除表中的某个实体会导致其他实体消失。如果我们删除了所有选择数学的这门课的选课记录，数学这门课就不存在了。

解决以上的三个异常的解决之道就是对数据库进行范式化设计。

1NF:列不可拆分
2NF：其他列完全依赖于主键列
3NF：消除传递依赖

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
通俗的理解是，字段还可以再分吗？如过不能，则是符合1NF的设计。

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
简单的解释，比如你和一个女生约会建立一张表，不用每条约会记录都记录她的身高、体重，将身高体重单独的存在一张表中供查询即可。

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
打个比方，比如评论表，如果你将用户ID，用户头像都放在这留言表中，就是不合适的了。用户头像是依赖于用户ID，而不依赖该评论。

范式化设计能够有效降低数据冗余，但是有时候一个简单的查询可能需要关联多张表，影响SQL的性能，我们可以通过反范式化设计以空间换时间。

范式化设计有较少的数据冗余，更行快速，但是缺点也是显而易见的：需要对多张表进行关联，很难进行索引优化。

反范式化设计将所要查询的数据集中存储在一张表中，拥有较高的查询性能，因为在InnoDB存储引擎中，进行全表扫描的效率高于表之间的关联操作，因为全表扫描是顺序IO，而表关联是随机IO。如果我们需要查询的数据都在一张表中就可以使用覆盖索引进行性能优化。

所以在实际的应用中，我们要结合范式化设计和反范式化设计。

数据库物理设计

为表中的字段选择合适的数据类型：当一个列可以有选择多种数据类型的时候，应该优先考虑数字类型，其次是日期或者二进制类型，最后是字符型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型，因为在数据库中数据处理是以页为单位的，每个页能够存储的数据是有限的（InnoDB中为16K）,越小代表每个页中能够容纳的数据越多，加载同样的数据时，使用宽度较小的数据类型比宽度大的数据类型加载的数据页少，减少磁盘IO。

有2点需要纠正int和int(10)之间没有任何区别。和大多数编程语言一样MySQL中的float和double数据类型通常是精确的，如果需要精确表示数字可以使用decimal类型

decimal类型每4字节存储9个数字，小数点占用1个字节。DECIMAL(18,9)需要9个字节来存储，整数和小数部分各4个字节，小数部分1个字节。

VARCHAR和CHAR类型的存储与存储引擎有很大关系，其占用的空间是以字符计数，根据编码的不同占用不同的存储空间。前者是变长字符串，只占用必要的存储空间，例如：UTF8编码的字符串长度为10，则定义为VARCHAR(10)即可，列最大长度小于255则只占用一个额外的字节存储字符串长度，否则占用2个字节存储字符串长度。

VARCHAR的使用场景：字符串列的最大长度比平均长度大得多；字符串列很少被更新（字符串长度发生变化导致存储页的分页从而导致存储碎片）；使用了多字节字符集存储字符串（例如utf8字符串）。

字符串存储在CHAR类型的列中会删除末尾的空格，最大宽度为255.使用场景为：字符串长度近似（例如MD5,身份账号）；短字符串（例如：性别CHAR(1)和VARCHAR(1)后者将占用额外的1个字节存储字符串长度）；经常被更新的字符串（一次分配足够的内存空间，不会导致分页）

DATETIME类型和时区无关，占用8byte存储空间，时间范围1000-01-01 00:00:00到9999-12-31 23:59:59,多数场景
TIMESTAMPunix时间戳(格林尼治)，占用4byte，1970-01-01到2038-01-19，在行的数据修改的时候可以自动修改TIMESTAMP列的值

1
2
3
4
mysql> CREATE TABLE t(d1 DATETIME,d2 TIMESTAMP);
mysql> INSERT t VALUES(NOW(),NOW());
mysql> SET TIME_ZONE='+10:00';
mysql> SELECT * FROM t;

主键的选择

主键应该尽量小（索引占用空间），顺序增长（顺序IO），业务主键和数据库主键可以不同。数据库主键可以使用自增id，业务主键在数据库中可以使用唯一索引。

表的拆分

  • 垂直拆分是将列拆分到不同的表。有2个原则：1.经常一起查询的列放在一起；2. text、blob等大字段拆分到附加表中。
  • 水平拆分可以采用主键hash的方式，例如对uid对100进行mod操作。

一些小技巧

给 MySQL 大表加字段的思路如下：

  1. 创建一个临时的新表，首先复制旧表的结构（包含索引）create table new_table like old_table;
  2. 给新表加上新增的字段
  3. 把旧表的数据复制过来 insert into new_table(filed1,filed2…) select filed1,filed2,… from old_table
  4. 删除旧表，重命名新表的名字为旧表的名字

需要注意下，执行第三步的时候，可能这个过程也需要时间，这个时候有新的数据进来，所以原来的表如果有字段记录了数据的写入时间就最好了，可以找到执行这一步操作之后的数据，并重复导入到新表，直到数据差异很小。不过还是会可能损失极少量的数据。所以，如果表的数据特别大，同时又要保证数据完整，最好停机操作。