飞天班第49节：数据库高级应用-2

1、MySQL的SQL优化

索引分类

主键索引：根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE table_name ADD PRIMARY KEY pk_index('col')；

唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE table_name ADD UNIQUE index_name('col')；

普通索引：B树

ALTER TABLE table_name ADD INDEX index_name('col')；

全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX ft_index('col')；

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

-- 创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

组合索引

ALTER TABLE table_name ADD INDEX index_name('col1','col2','col3')；

相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

列名过长导致索引key过长，可以只取col1和col2的前几个字符作为索引

ALTER TABLE table_name ADD INDEX index_name(col1(4))；
ALTER TABLE table_name ADD INDEX index_name(col1(4),col2（3))；

表示使用col1的前4个字符和col2的前3个字符作为索引

索引命中规则

# 查看执行计划，type=all是全表扫描，并没有使用索引 
explain select * from pms_product where id=1;
# 组合索引一定是最左匹配原则,mysql的where条件顺序是自左向右的。
# 如果你在表上建立了很多组合索引，索引文件膨胀，修改、删除、更新会比较慢
# 关于组合索引的命中一定要注意最左匹配原则
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；
组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

查看执行计划，看是否启用了索引

索引失效的情况

1.在组合索引中不能有列的值为NULL

2.LIKE操作中，’%aaa%’不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。

3.在索引的列上使用表达式或者函数会使索引失效

4.在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。

5.类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email=’99999’。

6.在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。

7.如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效

8.尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引

索引的优化

1、最左前缀

2、带索引的模糊查询优化，使用全文索引来进行优化

3、使用短索引

例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度。

• 适合建立索引的列

  • 频繁查询的列
  • 有外键关联的列

• 不适合建立索引的列

  • 值变化少的列(大量重复值的列)
  • 频繁更新的列
  • 表的记录比较少：比如配置表

• 如何保证数据库死而不僵，kill 耗资源的客户端连接

  • show processlist;

    查询数据库的当前连接，如果是root帐号，能看到所有用户的当前连接。如果是其他普通帐号，则只能看到自己占用的连接。show processlist只能列出当前100条。如果想全部列出，可以使用SHOW FULL PROCESSLIST命令

    user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

    ⑤.command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

    ⑥.time列，显示这个状态持续的时间，单位是秒

  • kill id ，把show processlist查询出来的id杀掉，杀掉会话连接

-- 当前用户的所有会话连接
show full processlist;

-- 当前数据库有哪些事务正在执行
select * from information_schema.INNODB_TRX it 

2、数据库架构的设计

设计的步骤

• 逻辑设计

  先做E-R图

  设计的时候要满足第三范式，实际应用中，我们会主动打破第三范式，提升查询效率（比如做一些名称的冗余，

  把brand_id和brand_name一起写入，避免关联brand表查询名称）

• 物理设计

  数据库结构落库，借助工具PDMAN, 把数据库结构模型同步到实际使用的数据库如mysql,sqlserver

数据库命名规范

可以参考阿里巴巴Java开发手册

• 数据库名称：小写加下划线

• 数据库名称禁止使用保留字

• 见名知意，表的命名遵循“业务名称_表的作用”，如alipay_task,trade_config

• 临时表：tmp_tablename_20200620
• 备份表：bak_tablename_20200620
• 所有存储相同的列名和类型长度必须一致

数据库设计规范

• 尽量使用innoDB，v5.6以后innoDB已经是默认引擎
• 字符集统一UTF-8，一个汉字占3个字节，5.0版本以上，varchar(100)，指的是100字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放100个。
• 一定要给列加注释
• 控制一个单表的数据量大小
• 控制表的宽度，列限制为4096
• 禁止在表中建立预留字段：ext_float_1,ext_char_2
• 禁止在数据库中存放图片，文件，二进制流
  • 不得不存：将内容数据和文件流程数据分开（外键表），使用时再关联，就是将大字段分表
  • select * 就会将大字段带出，浪费内存、I/O
• 禁止对线上数据库进行压测
  • 会产生大量的垃圾数据和日志文件
• 禁止从开发环境连接测试或生产数据库

数据库索引设计规范

• 单张表的索引数量建议不超过5个，如果列比较多可以酌情增加
• 每个innodb表都应该有个主键，innodb是一个索引组织表
  • 表数据的存放都是按照主键顺序来的
  • 如果没有主键，mysql会优先选择一个非空唯一索引来做排序
  • 如果非空唯一索引都没有，mysql会自己生成一个36字节的主键，但性能不好
  • 不要使用UUID，MD5，HASH等字符串做主键，建议使用增长序列来做主键，如自增或雪花算法生成主键
• 组合索引的字段匹配是自左向右
  • 一般将区分度最高的列放在组合索引最左侧
  • 将字段长度小的放最左侧
  • 最频繁的放最左侧
• 避免建立冗余和重复索引（index(a,b,c) index(b,c) index(a)）
• 尽量避免使用外键约束

数据库字段设计规范

• 优先选择符合存储的最小数据类型
• 避免使用TEXT、BLOB类型
• 避免ENUM类型：修改枚举类型值需要alter语句
• 尽量将列定义为NOT NULL
• 日期格式建议使用timestamp或int来保存，不要使用varchar字符串存放日期类型

数据库的开发规范

• 程序连接数据库的SQL一定使用Preparement
  • 降低词法和语法分析的重复执行
  • 防止SQL注入
• 索引使用尽量避免前后%
• 使用join或exists来优化in操作
• 不同的应用访问数据库用不同的账号，可以知道发生错误是哪个账号造成的
• 禁止使用不含列名的insert
• 避免子查询（子查询结果集无法使用索引）
• 避免使用JOIN连接过多的表，阿里手册建议不要超3张表
• 减少数据库的交互次数

数据库操作行为规范

• 超100w行的批量写操作，分批进行
• 禁止为程序用户授予super权限
  • grant all privileges 不能授所有权
  • 授权的时候遵循权限最小原则
  • 当数据库连接慢，MySQL会给super留一个保留连接

作业2：根据自己公司的业务设计一套数据库设计规范标准

3、MySQL数据库分区表应用

分区表介绍

MySQL自己本身是支持表的逻辑分区的

查看数据库是否支持分区表

mysql> show plugins;

为什么要使用分区表：解决以下问题

• 是否遇到几千万的大表
• 查询困难，历史数据是不太关心的
• 如果历史数据要归档，将数据从原来的库中挪走

如果有一种文件组织形式，将2017年的数据放一个文件，将2018的放一个，2019年的放一个，2020年的放一个

这个时候就可以通过MySQL提供的分区表实现

分区表的分区类型

• HASH分区（取模分区）
• LIST分区
• RANGE分区
• KEY分区

HASH分区

• 根据MOD将分区键计算后分到指定表区域
• 可以基本平均的分布
• HASH分区键值必须是INT类型，或者通过函数转成INT

CREATE TABLE `customer_login_log` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(customer_id) PARTITIONS 4;

insert into customer_login_log values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log values(2,'2020-06-20 22:30:02',2,1);
insert into customer_login_log values(3,'2020-06-20 22:30:03',3,1);
insert into customer_login_log values(4,'2020-06-20 22:30:04',4,1);

创建以后，查看数据文件，.idb文件记录表的数据和索引，被拆分成4个文件如下，所以表分区的数据和索引物理上是分开的。

-rw-r----- 1 mysql mysql  8767 Jun 20 22:30 customer_login_log.frm
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p0.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p1.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p2.ibd
-rw-r----- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p3.ibd

查看分区表是否创建成功

mysql> explain partitions select * from customer_login_log;

查询每个分区多少数据

select table_name,partition_name,partition_description,table_rows 
from information_schema.PARTITIONS 
where table_name='customer_login_log';

查询具体的某个分区数据

-- 查询p1,p2分区的数据
select * from customer_login_log partition(p1,p2);
select * from customer_login_log partition(p3) where customer_id=3;

LIST分区

• 按照分区键的枚举来进行分区的
• 各分区的列表不能重复
• 每一行数据都必须要找到对应的分区才能插入数据

CREATE TABLE `customer_login_log_list` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(login_type)(
	PARTITION p_odd VALUES in (1,3,5,7,9),
	PARTITION p_even VALUES in (2,4,6,8)
);

insert into customer_login_log_list values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log_list values(2,'2020-06-20 22:30:02',2,2);
insert into customer_login_log_list values(3,'2020-06-20 22:30:03',3,3);
insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,4);

如果分区键不在分区中，无法插入数据

mysql> insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,0);
ERROR 1526 (HY000): Table has no partition for value 0

RANGE分区

• 根据分区的不同范围值将数据放不同文件中
• 多个分区要连续，不能重叠
• 要有封口的MAXVALUE

CREATE TABLE `customer_login_log_range` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
	PARTITION p2016 VALUES LESS THAN (2017),
	PARTITION p2017 VALUES LESS THAN (2018),
	PARTITION p2018 VALUES LESS THAN (2019),
	PARTITION p2019 VALUES LESS THAN (2020),
  PARTITION p2020 VALUES LESS THAN (2021),
	PARTITION pmax VALUES LESS THAN MAXVALUE   -- 可以不指定maxvalue，后期方便添加分区
);

insert into customer_login_log_range values(1,'2016-06-20 22:30:01',1,1);
insert into customer_login_log_range values(2,'2017-06-20 22:30:02',2,2);
insert into customer_login_log_range values(3,'2018-06-20 22:30:03',3,3);
insert into customer_login_log_range values(5,'2019-06-20 22:30:04',4,4);
insert into customer_login_log_range values(6,'2020-06-20 22:30:04',4,4);
insert into customer_login_log_range values(7,'2021-06-20 22:30:04',4,4);
insert into customer_login_log_range values(8,'2022-06-20 22:30:04',4,4);
insert into customer_login_log_range values(9,'2023-06-20 22:30:04',4,4);

# 指定分区查询
select * from customer_login_log_range partition(p2016,p2017,pmax);

添加分区

• 第一种情况是没有添加MAXVALUE的，如何添加分区

  可以直接 add PARTITION

  CREATE TABLE `customer_login_log_range1` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  PARTITION BY RANGE(YEAR(login_time))(
  PARTITION p2016 VALUES LESS THAN (2017),
  PARTITION p2017 VALUES LESS THAN (2018),
  PARTITION p2018 VALUES LESS THAN (2019),
  PARTITION p2019 VALUES LESS THAN (2020)
  );
    
  alter table customer_login_log_range1 add PARTITION(
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
  )
  

• 第二中情况是已添加MAXVALUE的，添加分区时需要重新分区

  alter table customer_login_log_range PARTITION BY RANGE(YEAR(login_time))(
  	PARTITION p2016 VALUES LESS THAN (2017),
  	PARTITION p2017 VALUES LESS THAN (2018),
  	PARTITION p2018 VALUES LESS THAN (2019),
  	PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
  )
  

  查看分区

  select table_name,partition_name,partition_description,table_rows 
  from information_schema.PARTITIONS 
  where table_name='customer_login_log_range';
  

  

一个没有创建分区的表，如何变为分区表

CREATE TABLE `customer_login_log_range_no` (
  `customer_id` int(10) unsigned NOT NULL COMMENT '登录用户ID',
  `login_time` datetime NOT NULL COMMENT '用户登录时间',
  `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',
  `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into customer_login_log_range_no values(1,'2016-06-20 22:30:01',1,1);
insert into customer_login_log_range_no values(2,'2017-06-20 22:30:02',2,2);
insert into customer_login_log_range_no values(3,'2018-06-20 22:30:03',3,3);
insert into customer_login_log_range_no values(5,'2019-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(6,'2020-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(7,'2021-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(8,'2022-06-20 22:30:04',4,4);
insert into customer_login_log_range_no values(9,'2023-06-20 22:30:04',4,4);

# 直接修改为分区表，就可以了
alter table customer_login_log_range_no PARTITION BY RANGE(YEAR(login_time))(
	PARTITION p2016 VALUES LESS THAN (2017),
	PARTITION p2017 VALUES LESS THAN (2018),
	PARTITION p2018 VALUES LESS THAN (2019),
	PARTITION p2019 VALUES LESS THAN (2020),
	PARTITION p2020 VALUES LESS THAN (2021),
	PARTITION p2021 VALUES LESS THAN (2022),
	PARTITION pmax VALUES LESS THAN MAXVALUE
)
# 数据会按照分区规则进行数据重新组装，数据会进入相应分区
# 这种会使服务器资源消耗比较大(400多万数据要1分多钟)

分区前只有一个数据和索引文件.ibd

alter table分区后，

数据会按照分区规则进行数据重新组装，数据会进入相应分区

删除分区

如果要删除分区请使用命令，不能直接删除文件，否则导致表数据不可用。

# 删除分区p2016及2016年的数据
alter table customer_login_log_range drop partition p2016;

作业:分区归档

作业3：如果我们要删除分区前需要进行数据归档，如何来做，自己做一下

解决方案1: create table as select

-- 创建备份表
create table bak_customer_login_log_range_p2017 as select * from customer_login_log_range partition(p2017)
-- drop分区表
alter table customer_login_log_range drop partition p2017;

解决方案2: exchange partition

参考博客：https://blog.csdn.net/quincy/article/details/106953769

-- 创建备份表
create table bak_customer_login_log_range_p2017 like customer_login_log_range;
-- 删除备份表的分区
alter table bak_customer_login_log_range_p2017 remove partitioning;
-- exchange 分区表的数据到备份表
alter table customer_login_log_range exchange partition p2017 with table bak_customer_login_log_range_p2017;
-- drop分区表
alter table customer_login_log_range drop partition p2017;

4、减少误操作

字段增删改的限制

• 新加的字段一定要允许为空

  程序发布失败，可以不用回滚数据库

• 线上环境必填字段一定不能删除，字段名称一定不要修改

  如果先把数据库中字段名称改了，程序还没来得及发。这时所有涉及该字段的增删改查，同样也会报字段不存在的异常。

• 修改字段类型要兼容，字段长度，要小心

• 表名前面带库名

  update `trade2`.`order` set status=1,edit_date=now(),edit_user='admin' where status=0;
  

中间结果写入临时表

把要更新的数据的id先查询出来放入临时表，当发现更新不对时，只用通过临时表中的id作为查询条件更新数据即可。

操作数据前先备份

create table order_bak_2021031722 select * from`order`;

5、Mysql优化的三大方案

优化现有mysql数据库

按阶段总结如下，都是精华

1.数据库设计和表创建时就要考虑性能

2.sql的编写需要注意优化

3.分区

4.分表

5.分库

1.数据库设计和表创建时就要考虑性能

mysql数据库本身高度灵活，造成性能不足，严重依赖开发人员能力。也就是说开发人员能力高，则mysql性能高。这也是很多关系型数据库的通病，所以公司的dba通常工资巨高。

设计表时要注意：

1.表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。

2.尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。

3.使用枚举或整数代替字符串类型

4.尽量使用TIMESTAMP而非DATETIME

5.单表不要有太多字段，建议在20以内

6.用整型来存IP

索引

1.索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描，show profiles;查看最近发送到服务器的SQL语句。

2.应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描

3.值分布很稀少的字段不适合建索引，例如”性别”这种只有两三个值的字段

4.字符字段只建前缀索引

5.字符字段最好不要做主键

6.不用外键，由程序保证约束

7.尽量不用UNIQUE，由程序保证约束

8.使用多列索引时注意顺序和查询条件保持一致（最左匹配原则），同时删除不必要的单列索引

使用合适的数据类型

1. 使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob

2. 使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数，节省查询开销

   -- 使用int 存储 ip 主要使用到mysql的两个转ip函数
   -- INET_ATON（） 将IP转为十进制数字
   select INET_ATON('127.0.0.1');
   -- INET_NTOA（）将数字转换为IP
   select INET_NTOA(2130706433);
   -- 比如要查某一网段的所有IP
   select * from table where ip between inet_aton('192.168.0.1') and inet_aton('192.168.0.255'); 
      
   -- 原理
   -- 1、ip 是由4个8位的二进制数字组成，所以逆向把ip转成二进制就可以这样拼接
   select concat(bin(127),lpad(bin(0),8,0),lpad(bin(0),8,0),lpad(bin(1),8,0));
   -- 结果
   1111111000000000000000000000001   
   -- 系统的INET_ATON（）函数也是这么算的
   select bin(inet_aton('127.0.0.1'));  
   -- 结果
   1111111000000000000000000000001
   -- 结论：INET_ATON() 就是把IP的每一段转为二进制拼接起来，然后将这个32位的二进制数字转为10进制数字，INET_NTOA（）就相反，把十进制转为二进制，然后每8位二进制转为十进制数字拼接就是IP地址
   

3. 使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar（4）
4. 尽可能使用not null定义字段
5. 尽量少用text，非用不可最好分表

选择合适的索引

(1) 查询频繁的列，在where，group by，order by，on从句中出现的列

(2) where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列

(3) 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好

(4) 离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高

2.sql的编写需要注意优化

(1) 使用limit对查询结果的记录进行限定，使用主键id限定范围

Mysql表数据量很大，分页查询很慢，有什么优化方案

(2) 避免select *，将需要查找的字段列出来

(3) 使用连接（join）来代替子查询

(4) 拆分大的delete或insert语句

(5) 可通过开启慢查询日志来找出较慢的SQL

(6) 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边

(7) 少用JOIN，不超过3张表关联

(8) 使用同类型进行比较，比如用’123’和’123’比，123和123比

(9) 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5

3、表分区

分区表是一个独立的逻辑表，但是底层由多个物理子表组成，用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化。

好处

• 可以让单表存储更多的数据
• 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
• 部分查询能够从查询条件确定只落在少数分区上，速度会很快
• 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备
• 可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
• 可以备份和恢复单个分区

限制

• 一个表最多只能有1024个分区
• 所有分区必须使用相同的存储引擎
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

4、分表

分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户。

分表分为垂直拆分和水平拆分，通常以某个字段做拆分项。比如以id字段拆分为100张表：表名为 tableName_id%100

5、分库

把一个数据库分成多个，mysql 可以做主从，双主，可以使用Mycat 做mysql集群化，使用shardingjdbc做分库，后面的文章我有讲到。

换数据库

mysql性能不行，那就换个。为保证源程序代码不修改，保证现有业务平稳迁移，故需要换一个100%兼容mysql的数据库。

开源选择

1.TiDB https://github.com/pingcap/tidb

2.Cubrid https://www.cubrid.org/

3.开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距，有很多坑要踩，如果你公司要求必须自建数据库，那么选择该类型产品，不建议

云数据选择

1.阿里云POLARDB

POLARDB 是阿里云自研的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量最高可达 100T，性能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征，又具有开源数据库简单、可扩展、持续迭代的优势，而成本只需商用数据库的 1/10。

2.阿里云OcenanBase

3.阿里云HybridDB for MySQL (原PetaData)

云数据库HybridDB for MySQL （原名PetaData）是同时支持海量数据在线事务（OLTP）和在线分析（OLAP）的HTAP（Hybrid Transaction/Analytical Processing）关系型数据库。

4.腾讯云DCDB

DCDB又名TDSQL，一种兼容MySQL协议和语法，支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表，数据却均匀的拆分到多个分片中；每个分片默认采用主备架构，提供灾备、恢复、监控、不停机扩容等全套解决方案，适用于TB或PB级的海量数据场景。

大数据引擎处理

数据量过亿了，没得选了，只能上大数据了。

开源解决方案

hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本，一般公司是玩不起的，没十万投入是不会有很好的产出的！

云解决方案

这个就比较多了，也是一种未来趋势，大数据由专业的公司提供专业的服务，小公司或个人购买服务，大数据就像水/电等公共设施一样，存在于社会的方方面面。

国内做的最好的当属阿里云。

我选择了阿里云的MaxCompute配合DataWorks，使用超级舒服，按量付费，成本极低。

MaxCompute可以理解为开源的Hive，提供sql/mapreduce/ai算法/python脚本/shell脚本等方式操作数据，数据以表格的形式展现，以分布式方式存储，采用定时任务和批处理的方式处理数据。DataWorks提供了一种工作流的方式管理你的数据处理任务和调度监控。

当然你也可以选择阿里云hbase等其他产品，我这里主要是离线处理，故选择MaxCompute，基本都是图形界面操作，大概写了300行sql，费用不超过100块钱就解决了数据处理问题。