mysql-整合

一种是表锁定（myisam存储引擎），一个是行锁定（innodb存储引擎）。两种引擎介绍在后文中，先扯扯分区和分表

3种分表方法

第一种，做集群

做mysql集群，例如：利用mysql cluster ，mysql proxy，mysql replication，drdb

第二种，根据对象的唯一标识，确定散列到哪一个表 ,user_01, user_02

第三种，创建2个 ENGINE=MyISAM 的表 user_1 ， user_2

               创建一个MERGE表联合前面那2个表：

1. mysql> CREATE TABLE IF NOT EXISTS `alluser` ( 
2. ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
3. ->   `name` varchar(50) DEFAULT NULL, 
4. ->   `sex` int(1) NOT NULL DEFAULT '0', 
5. ->   INDEX(id) 
6. -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;

分表语句

INSERT INTO user1(user1.id,user1.name,user1.sex) value SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000

INSERT INTO user2(user2.id,user2.name,user2.sex) value SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000

利用merge引擎做的分表，在实际磁盘里是没有 alluser.MYD数据文件和.MYI索引文件，只有alluser.frm表结构文件。但多了一个alluser.MRG文件， 该文件里存放的是与 user1user2分表的关系，以及插入数据的方式

 

分区以后还是只是一个表，但是多了几个分区文件

aa#P#p1.MYD    分区1的数据

aa#P#p1.MYI     分区1的索引文件

aa#P#p3.MYD   分区3的数据

aa#P#p3.MYI    分区3的索引文件

aa.frm    结构文件 

aa.par    分区信息记录文件

 

分区和分表的侧重点：

   分表(merge为例)是为了提高mysql的并发能力，举例:3个线程同时在3张小表中找数据快，还是在一张大表中找数据快？

   分区，是为了突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。

1.在《高性能Mysql》这本书的‘如何使用分区’这一小章中，列举的常见问题中，有以下一个问题：
分区列和索引列不匹配
如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在列a上定义了索引，而在列b上定义的分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每个分区内对应的索引。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。

 

2.  在应用到生产系统前运行基准测试和性能测试

依赖于你的数据库的用途，你可能得到巨大的性能提高也可能一无所获。如果不小心，甚至有可能会降低性能。

比如：一个使用月分区的表，在总是进行日期范围查询时可以得到极优的速度。但如果没有日期查询，那么会进行全表扫描。

分区对于海量数据性能提高是一个关键的工具。什么才是海量的数据取决于部署的硬件。盲目使用分区不能保证提高性能，但是在前期基准测试和性能测试的帮助下，可以成为完美的解决方案。

3.  Archive 表可以成为一个很好的折衷方案

Archive 表分区后可以得到巨大的性能提高。当然也依赖于你的用途，没有分区时任何查询都是全表扫描。如果你有不需要变更的历史数据，还要进行按时间的分析统计，使用Archive引擘是极佳的选择。它会使用10-20％的原空间，对于聚集查询有比MyISAM  /InnoDB表更好的性能。

虽然一个很好的优化的分区MyISAM 表性能可能好于对应的Archive表, 但是需要10倍的空间。

 

 

 

 

MySQL 官方对InnoDB是这样解释的：InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
    InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
    InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
   InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。
   InnoDB被用来在众多需要高性能的大型数据库站点上产生。著名的Internet新闻站点Slashdot.org运行在InnoDB上。 Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的.

数据结构：

B+树的节点没有value（B树节点内存放key和value），是为了争取更多的度，value都存放在叶子节点中

mysiam和innoDB的区别：

myisam 比 innodb更节省磁盘空间， 但是innodb支持提交，回滚，崩溃处理事务，外键。

mysiam和innoDB都是使用B+树，但是mysiam是非聚簇索引，既索引value存放的是数据地址，而innoDB存放的是实际数据。

mysiam的辅助索引和主索引没多大区别，都是value存放地址，只是辅助索引可以有多个相同key。

innoDB的辅助索引value存储的是主键的KEY，而不是地址。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

插入一句话，此话本该归纳如下面优化模块中的，提到数据结构就先说了吧，永远为表设定一个自增主键ID，并尽可能的利用此ID进行构造。尽可能的用上unsigned,并尽可能的小。能用tinyint（1字节)、smallint(2字节）、mediumint（3字节),就不用int(4)字节。具体原因就是，这样的索引在innoDB下表现出卓越的性能。主索引自增，越单一，越简单，越好，如果使用非单调的字段作为主键，那么维护B+树开销将加大。主索引越小，辅助索引也能更小，毕竟咱引用的就是主键，对吧。

具体是否在该列建立索引的计算公式为

SELECT count(DISTINCT(列))/count(*) AS Selectivity FROM 表;

在使用 InnoDB 存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的 自增字段作为主键。

可以用多字段索引，如果两个字段拼接太长，那么可以截取某字段的一部分，比如A字段+B字段的前4位。

优化：

尽量使用固长的字符类型，当表中所有的字段都是固定长度，DB 会认为表是static类型，如果有固定长度和非固定长度的字段尽量采用垂直分割，将表分割。

静态类型的表有以下特点：

a、非常快，由于是固定长度，DB很容易计算出下行的偏移量，所以读取速度会很快，如果不是固定长度，要找到下行数据必须找到主键（这也是主键使用自增ID的重要的原因）

b、容易缓存

c、崩溃后容易重建，因为行是在固定位置

d、比变长需要更多的磁盘空间

e、一般不用优化，除非删除了大量数据，采用optimize table 进行优化

innodb_buffer_pool_size ： 决定性的影响。这个参数主要缓存innodb表的索引，数据，插入数据时的缓 冲。为Innodb加速优化首要参数。默认的设置只有8M，可以设置60-80%的内存，在内存容量允许的情况下面设置比InnoDB tablespaces大10%的内存大小。

innodb_flush_log_at_trx_commit：如果设置为1的话，同步刷新log，如果设置为0，性能，唰的就上去了。

innodb_data_file_path：文件存储方案，=/disk1 /ibdata1:900M;/disk2/ibdata2:50M:autoextend 意思就是在data1存储900M，满了就放data2放50M， 再满了，就自动扩充8M存放。

innodb_data_home_dir：放置表空间数据的目录，默认在mysql的数据目录，设置到和MySQL安装文件不同的分区可以提高性能。

innodb_log_file_size：该参数决定了recovery speed。太大的话recovery就会比较慢，太小了影响查询性能，一般取256M可以兼顾性能和recovery的速度。
innodb_log_buffer_size： 磁盘速度是很慢的，直接将log写道磁盘会影响InnoDB的性能，该参数设定了log buffer的大小，一般4M。如果有大的blob操作，可以适当增大。

innodb_flush_method： 设置InnoDB同步IO的方式：
1) Default – 使用fsync（）。
2) O_SYNC 以sync模式打开文件，通常比较慢。
3) O_DIRECT，在Linux上使用Direct IO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和double buffering（mysql buffering 和OS buffering）。

innodb_thread_concurrency： InnoDB kernel最大的线程数。
1) 最少设置为(num_disks+num_cpus)*2。
2) 可以通过设置成1000来禁止这个限制

参考博文：http://www.uml.org.cn/sjjm/201107145.asp#nav-1