最佳实践
1、冷热数据分离
以文章内容系统为例：标题、作者、分类、创建时间、点赞数、回复数、最近回复时间
1.1、冷数据：可以理解成偏静态的数据，会频繁的被读取，但是几乎或者很少被改变，这类数据对读的性能要求较高，数据存储可以使用myisam引擎
1.2、热数据：数据内容被频繁改变，这类数据对并发读写要求较高，我们可以使用innodb引擎存储
根据具体的使用场景使用不同的存储引擎，以达到性能的相对最优，将文章内容系统的表结构进行冷热拆分，拆分后的表结构如下：
1.3、拆分前后性能比对
插入100000条数据，对拆分前后的文章表做查询，性能比对的趋势如下，同样都是模拟50个并发，一共2500次请求，每个线程50个请求，采用ID随机，这样更贴近真实的查询场景，很明显拆分后的效果更胜一筹：
拆分后单表测试：
mysqlslap -h227.0.0.1 -uroot -P3306 -p --concurrency=50 --iterations=1 --engine=myisam --number-of-queries=2500 --query='select * from cms_blog_static where id=RAND()*1000000' --create-schema=test
未拆分测试：
mysqlslap -h227.0.0.1 -uroot -P3306 -p --concurrency=50 --iterations=1 --engine=innodb --number-of-queries=2500 --query='select * from cms_blog where id=RAND()*1000000' --create-schema=test
2、减少单行数据大小
对于拆分以后的数据表，我们能否进一步降低单行数据的大小呢，总结起来常用的方法如下：
2.1、设置合理的字段长度
大家都知道不同的字段类型占用的存储空间不同，如下图：
| 类型 | 长度（字节） | 定长/非定长 |
| | TINYINT | 1 | 定长 |
|
| SMALLINT | 2 | 定长 | |
| MEDIUMINT | 3 | 定长 |
|
| INT | 4 | 定长 |
|
| BIGINT | 8 | 定长 |
|
| FLOAT(m) | 4字节(m<=24)、8字节(m>=24 and m<=53) | 非定长 |
|
| FLOAT | 4 | 定长 |
|
| DOUBLE | 8 | 定长 |
|
| DOUBLE PRECISION | 8 | 定长 |
|
| DECIMAL(m,d) | m字节(m>d)、d+2字节(m| 非定长 |
| |
| NUMBER(m,d) | m字节(m>d)、d+2字节(m| 非定长 |
| |
| DATE | 3 | 定长 |
|
| DATETIME | 8 | 定长 |
|
| TIMESTAMP | 4 | 定长 |
|
| TIME | 3 | 定长 |
|
| YEAR | 1 | 定长 |
|
| CHAR(m) | m | 非定长 |
|
| VARCHAR(m) | l字节，l就是实际存储字节(l<=m) | 非定长 |
|
| BLOB, TEXT | l+2字节，l就是实际存储字节 | 非定长 |
|
| LONGBLOB, LONGTEXT | l+4字节，l就是实际存储字节 | 非定长 |
|
我们在实际使用中，需要根据实际的需要选择合理的类型，能有效的减小单行数据的大小，比如，user_status，一般我们定义成tinyint(1)即可，没必要定义成int，白白多占用3个字节
2.2、设置合理的索引长度
2.2.1、对于需要建索引的字段，如果字段占用的空间越大，对于索引来说，建立索引的长度就越长，索引页大小不变的情况下，数据条数就越少，查询需要做IO的次数就越频繁
2.2.2、对于某些索引字段，如果我们可以通过前缀字段能达到很好的区分度，则可以控制创建索引的长度，目的是索引页的含的数据行数更多，减少IO,方式如下：
//如下，我们根据字段1和字段2，指定的组合索引的长度
alter table table_name add index index_name (field1(length2),field2(length3))
2.2.3、索引的选择性
索引本身是由开销的，首先是存储资源，然后插入和更新带来的对B+Tree树的维护，数据更新带来的性能下降，所以对于我们的原则是：索引该不该建，以及用什么字段建
数据量少-则不建，区分度或者选择性不高-则不建，数据量少大家很容易理解，小于1W条数据全表扫描也能接收，选择性或者区分度是指，数据的分散程度，比如某个用户表，有一个性别字段，数据量越大它的索引选择性越差，计算公式如下：
//返回值范围(0,1]，该值越大，索引选择性越高
select distinct(col)/count(*) from table_name
同理，对于需要控制索引长度的字段，计算选择性如下：
select distinct(left(col,n))/count(*) from table_name
2.2.4、性能比较
通过对100w的数据，对数据行大小做优化，前后性能比对结果如下：
优化前后的表结构定义如下：
--优化前
CREATE TABLE `cms_blog` (
`id` bigint(20) NOT NULL auto_increment,
`title` varchar(60) NOT NULL,
`creator` varchar(20) NOT NULL,
`blog_type` tinyint(1) not NULL,
`reply_praise` int(10) UNSIGNED,
`reply_count` int(10) UNSIGNED,
`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8;
--创建索引
alter table cms_blog add index idx_reply_count (reply_count);
--优化后reply_praise和reply_count
CREATE TABLE `cms_blog_v2` (
`id` bigint(20) NOT NULL auto_increment,
`title` varchar(60) NOT NULL,
`creator` varchar(20) NOT NULL,
`blog_type` tinyint(1) not NULL,
`reply_praise` MEDIUMINT(10) UNSIGNED,
`reply_count` MEDIUMINT(10) UNSIGNED,
`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8;
--创建索引
alter table cms_blog_v2 add index idx_reply_count (reply_count);
压测脚本如下：
--压测脚本，旧表
mysqlslap -h227.0.0.1 -uroot -P3306 -p --concurrency=50 --iterations=1 --engine=innodb --number-of-queries=2500 --query='select * from cms_blog where reply_count>999990' --create-schema=test
--压测脚本，新表
mysqlslap -h227.0.0.1 -uroot -P3306 -p --concurrency=50 --iterations=1 --engine=innodb --number-of-queries=2500 --query='select * from cms_blog_v2 where reply_count>999990' --create-schema=test
性能表现，旧表和新表压测表现如下：
新表优化后性能明显有提升：
2.3、主键的选择
尽量使用保持单调性的自增主键，避免使用uuid、hash方式、业务自定义主键，减少索引重建对性能的影响
3、分散数据页查询
3.1、数据分区
数据分区可以有效的提升查询的性能，充分利用不同分区所关联的IO存储，在逻辑上是属于同一张表，物理上可以分散到不同的磁盘存储，缺点是跨分区查询的性能稍差，所以互联网公司在实际当中很少用到数据分区，一般建议物理分表的方式实现
CREATE TABLE table_name (
id INT AUTO_INCREMENT,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500),
INDEX idx (id)
) ENGINE = INNODB
PARTITION BY LIST(store_id) (
PARTITION p1
VALUES IN (1, 3, 4, 17)
INDEX DIRECTORY = '/var/path3'
DATA DIRECTORY = '/var/path2';
3.1.1、分区方式
3.1.1.1、RANGE partitioning
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
3.1.1.2、LIST Partitioning
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
3.1.1.3、COLUMNS Partitioning
--range columns
CREATE TABLE rc1 (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
PARTITION p0 VALUES LESS THAN (5, 12),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);
--list columns
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);3.1.1.4、HASH Partitioning
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
3.1.1.5、KEY Partitioning
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
3.2、数据分表/分库
数据分表解决的问题，提升单表的并发能力，文件分布在不同的表文件，对IO性能进一步提升，另外对读写锁影响的数据量变少，插入数据需要做索引重建的数据减少，insert或update性能会更好
3.2.1、分表和分库方式
3.2.1.1：哈希取模方式，hash(关键字)%N
3.2.1.2：按照时间，如按照年或者月分表
3.2.1.3、按照业务，以订单业务为例，平台订单、三方订单
3.2.2、分库分表中间件
整体来说分为在客户端实现，和代理端实现，比如：cobar、sharding-jdbc、mycat等，具体使用可以自行检索
