数据库优化

show plugins;

什么时候考虑使用分区？

• 一张表的查询速度已经慢到影响使用的时候。
• sql经过优化
• 数据量大
• 表中的数据是分段的
• 对数据的操作往往只涉及一部分数据，而不是所有的数据

表分区必须用主建

CREATE TABLE `user_info` (

  `id` int(11) NOT NULL auto_increment COMMENT 'id',

  `name` varchar(10) default NULL COMMENT '姓名',

  `age` int(2)  COMMENT '年龄',

  `password` varchar(10) default NULL,

  PRIMARY KEY  (`id`,`age`)

) ENGINE=GsDB DEFAULT CHARSET=utf8

PARTITION BY RANGE(age)

(

    PARTITION   minor values less than (17),

    PARTITION  young  values less than (50),

    PARTITION  middle  values less than (80),

    PARTITION  old  values less than (100)

)

select  *  from user_info PARTITION(minor)

select  *  from user_info PARTITION(young)

select  *  from user_info PARTITION(middle)

select  *  from user_info PARTITION(old)

什么时候考虑分表？

• 一张表的查询速度已经慢到影响使用的时候。
• sql经过优化
• 数据量大
• 当频繁插入或者联合查询时，速度变慢

分表解决的问题

分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了

• 查询一次的时间短了
• 数据分布在不同的文件，磁盘I/O性能提高
• 读写锁影响的数据量变小
• 插入数据库需要重新建立索引的数据减少

多个数据库

• 事务的支持，分库分表，就变成了分布式事务
• join时跨库，跨表的问题
• 分库分表，读写分离使用了分布式，分布式为了保证强一致性，必然带来延迟，导致性能降低，系统的复杂度变高。

常用的解决方案：

对于不同的方式之间没有严格的界限，特点不同，侧重点不同。需要根据实际情况，结合每种方式的特点来进行处理。

选用第三方的数据库中间件（Atlas，Mycat，TDDL，DRDS），同时业务系统需要配合数据存储的升级。

基础数据存储

Mysql：只存储非文本的基础信息。包括：评论状态，用户，时间等基础数据。以及图片，标签，点赞等附加信息。数据组织形式（不同的数据又可选择不同的库表拆分方案）：

• 评论基础数据按用户ID进行拆库并拆表
• 图片及标签处于同一数据库下，根据商品编号分别进行拆表
• 其它的扩展信息数据，因数据量不大、访问量不高，处理于同一库下且不做分表即可

文本存储

文本存储（评论的内容）使用了mongodb、hbase

• 选择nosql而非mysql
• 减轻了mysql存储压力，释放msyql，庞大的存储也有了可靠的保障
• nosql的高性能读写大大提升了系统的吞吐量并降低了延迟

总的来说，优先考虑分区。当分区不能满足需求时，开始考虑分表，合理的分表对效率的提升会优于分区。

1、保存格式为YYYYMMDDHHMMSS（年月日时分秒）的整数，所以，它与时区无关，存入的是什么值就是什么值，不会根据当前时区进行转换。

2、从mysql 5.6.4中，可以存储小数片段，最多到小数点后6位，显示时格式为 yyyy-MM-dd HH:mm:ss[.222222]

      mysql5.5中，没有小数片段，精确到秒。所以，我再从5.6版本迁移到5.5时，因生成的sql中datetime(6)有小数片段,无法导入。

3、存储范围：从1000-01-01 00:00:00 到'9999-12-31 23:59:59'

4、长度，8个字节，datetime(n),n不是存储长度，而是显示的小数位数，即使小数位数是0，存储是也是存储的6位小数，仅仅显示0位而已;要想显示小数，设置datetime(n),n=3显示小数点后3位，毫秒,n=6显示小数点后6位，微秒。

 

timestamp：

1、存入的是自1970-01-01午夜(格林尼治标准时间)以来的秒数，它和unix时间戳相同。所以它与时区有关，查询时转为相应的时区时间。比如，存储的是1970-01-01 00:00:00，客户端是北京，那么就加8个时区的小时1970-01-01 08:00:00。

2、有小数片段，至少从5.5就开始有

3、存储范围：'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' 

4、可以当做时间戳使用，在更新时，自动更新，这一列只能由系统自动更新，不能由sql更新，这个在乐观锁时有广泛的应用

6、长度，4字节，因为存储长度的原因，决定了它支持的范围的比datetime的要小

7、显示时，显示日期和时间

 

date

date，时分秒都存储了，但只显示日期。对应Java中的java.sql.Date

 

 

datetime与时区无关、timestamp与时区有关

1、查看当前时区，并创建表test_date,一个是timestamp列，一个是datetime列

 

2、插入两条数据，相同的时间。修改时区为0时区（格林尼治时区）后，查看时间，发现timestamp改变了，datetime没变。

 

 

总结

datetime、timestamp精确度都是秒，datetime与时区无关，存储的范围广(1001-9999)，timestamp与时区有关，存储的范围小(1970-2038)。

TIMESTAMP

4个字节储存;值以UTC格式保存;.时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。

DATETIME

8个字节储存;实际格式储存;与时区无关;datetime 以'YYYY- MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'TIMESTAMP值不能早于1970或晚于2037

INT

存时间戳。占用资源少，查询速度快。条件范围搜索使用between没什么问题。查询条件自由拼接。

datetime和timestamp相对于int来说也有一个小小的好处，就是对于时间类型来说，可以有一系列的时间函数可以用.