mysql相关知识

1）、mysql的几种存储引擎，ISAM、MyISAM、HEAP（也称为MEMORY）、CSV、BLACKHOLE、ARCHIVE、PERFORMANCE_SCHEMA、InnoDB、Berkeley、Merge、Federated和Cluster/NDB等，主要介绍ISAM、MYISAM、INNERDB、HEAP、merge
ISAM 该引擎读取速度非常快，但不支持事务、不支持索引、不支持容错、不支持字段管理也不支持外键。5.2以上的mysql版本已经不再支持该引擎
MYISAM引擎是基于isam引擎产生的，所以该引擎也继承了ISAM快速读取的优点，MYISAM引擎支持了索引、字段管理，其最与isam最大的不同就是表格锁定的机制来优化多个并发的读写操作，但是需要经常运行OPTIMIZE TABLE table_name命令，来恢复被更新机制所浪费的空间，否则碎片也会随之增加，最终影响数据访问性能。空间碎片的产生是由于myisam删除数据之后并没有回收该内存空间，而是等着与之前相似的数据再次插入，但是实际上这种情况偏少，所以会产生大量的空间浪费。
INNERDB引擎为mysql提供了ACID事务支持、系统崩溃修复能力和多版本并发控制的行级锁;该引擎支持自增长列（auto_increment）,自增长列的值不能为空，如果在使用的时候为空则自动从现有值开始增值，如果有但是比现在的还大，则直接保存这个值; 该引擎存储引擎支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表。该引擎在5.5后的MySQL数据库中为默认存储引擎。


HEAP引擎又叫memory引擎，从字面意思就可以看出该引擎属于内存引擎，该引擎以磁盘文件的形式保存表结构但是数据却保存在内存中，其特点是快速的操作，但是不稳定。当机器或数据库异常或者重启的话数据也随之消失。

merge可以用在逻辑分表。MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。类似于查询语句中的union，但要求这几个myisam结构完全相同。

2）、分表分区
如果单表数据量非常大的情况下，sql查询时间也将变长，这就如同从100个人里查出10个人和从100亿人查出10个人一样。分表分区就是为了将这100亿人分成几个小块从而减少查询基数来减少查询时间。一个sql到达数据库执行过程大概是：1、数据库接收到用户sql 2、将该sql加入到sql执行队列中 3、执行sql 4、返回执行结果
执行过程中耗时操作主要集中在两个地方：1、队列排队时间 2、该sql自身执行耗时。解决问题也从这两方面考虑：减少队列排队时间和加快单条sql查询速度。
（1）减少队列排队时间：
mysql集群：比如100个sql请求分散到10个mysql数据库和100个sql请求发送到1个数据库，每个数据库排队的时间肯定不同
1、同步集群（mysql cluster）
结构：（data + sql + mgm节点）
特点：
1) 内存级别的，对硬件要求较低，但是对内存要求较大。换算比例为：1：1.1；
2) 数据同时放在几台服务器上，冗余较好；
3) 速度一般；
4) 建表需要声明为engine=ndbcluster
5) 扩展性强；
6) 可以实现高可用性和负载均衡，实现对大型应用的支持；
7) 必须是特定的mysql版本，如：已经编译好的max版本；
8) 配置和管理方便，不会丢失数据；
2、异步集群（mysql replication）
结构：（master + slave）
特点：
1) 主从数据库异步数据；
2) 数据放在几台服务器上，冗余一般；
3) 速度较快；
4) 扩展性差；
5) 无法实现高可用性和负载均衡（只能在程序级别实现读写分离，减轻对主数据库的压力）；
6) 配置和管理较差，可能会丢失数据；

（2）预先估计会出现大数据量并且访问频繁的表，将其分为若干个表
比如保存用户消息的表，可以把消息表分成100张，msg1、msg2...msg100。根据不同的用户id将消息数据放入不同的表中，查询的时候根据用户id判断要去哪个表里查询
优点：减少了数据基数，缩短了一条查询sql执行的时间
缺点：扩展性差，再次加msg表或者通过用户id判断消息表的算法修改数据会乱。

（3）利用merge存储引擎来实现分表

现在有两个结构相同的表t1，t2。现在利用merge引擎来实现分表，总表为t，其结构与t1，t2相同，sql语句如下：
CREATE TABLE IF NOT EXISTS `t` ( 
`id` int(11) NOT NULL AUTO_INCREMENT,  
`name` varchar(50) DEFAULT NULL,  
`sex` int(1) NOT NULL DEFAULT '0',  
INDEX(id)  
) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; 
优点：适用于有数据之后再分表的情况，代码改动小
缺点：t1，t2，t必须维持这个状态，引擎不能再修改；主键唯一性约束可能会有问题；效率没有直接分表效果好

（4）分区
mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。
分区的三种情况：
1）range分区
/*创建range分区表*/
mysql> CREATE TABLE IF NOT EXISTS `user` (
->?? `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
->?? `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
->?? `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',
->?? PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM? DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
-> PARTITION BY RANGE (id) (
->???? PARTITION p0 VALUES LESS THAN (3),
->???? PARTITION p1 VALUES LESS THAN (6),
->???? PARTITION p2 VALUES LESS THAN (9),
->???? PARTITION p3 VALUES LESS THAN (12),
->???? PARTITION p4 VALUES LESS THAN MAXVALUE
-> );
 
/*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区*中*/
mysql> alter table aa partition by RANGE(id)
-> (PARTITION p1 VALUES less than (1),
-> PARTITION p2 VALUES less than (5),
-> PARTITION p3 VALUES less than MAXVALUE);
2)list分区
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
->?? `id` int(11) NOT NULL? COMMENT '用户ID',
->?? `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',
->?? `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
->?? `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'
-> ) ENGINE=INNODB? DEFAULT CHARSET=utf8
-> PARTITION BY LIST (province_id) (
->???? PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
->???? PARTITION p1 VALUES IN (9,10,11,12,16,21),
->???? PARTITION p2 VALUES IN (13,14,15,19),
->???? PARTITION p3 VALUES IN (17,18,20,22,23,24)
-> );