目录
http://blog.csdn.net/yongchao940/article/details/55266603
4.2 replace into与 insert into ... on duplicate key update
1 分区及分表
http://blog.csdn.net/yongchao940/article/details/55266603
1.1 横向分区与纵向分区
横向：记录分
纵向：字段分
1.2 分区的内在涵义
1.2.1 分区意义
首先MYISAM、InnoDB都是支持分区的。
1. 表非常大以至于无法全部都放到内存，或者只在表的最后部分有热点数据，其他均为历史数据
2. 分区表数据更容易维护（可独立对分区进行优化、检查、修复及批量删除大数据可以采用drop分区的形式等）
3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
4. 分区表可以避免某些特殊的瓶颈（ps: InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等） 5、可以备份和恢复独立的分区，非常适用于大数据集的场景、
5. 进行大数据搜索的时候可以进行并行处理
6. 跨多个磁盘分散数据查询，来获得更大的查询吞吐量。
1.2.2 分区物理存储
对应MySQL三个文件的具体情况.
分区在逻辑上是一个表，但是实际上却可以由多个物理分区组成。在执行查询的时候，和优化器会根据分区定义过滤出我们需要数据的分区，无须全表扫描。删除数据的时候同样的道理，效率也是非常的高。
1.3 分区的常见语句及注意事项
1.3.1 注意事项
http://lobert.iteye.com/blog/1955841
http://www.simlinux.com/2014/08/19/mysql-partitions.html
范围分区添加分区只能在最大值后面追加分区
比如 现在已经有了如下分区
|
|
|
|---|---|
| p0 | 5 |
| p1 | 10 |
| p2 | 15 |
1. 分区值必须是严格递增的。如上图，那么插入 alter table orders_range add partition(partition p3 values less than (12));是无法插入成功的。
因此如果我们在程序中有 alter table orders_range add partition(partition p3 values less than MAXVALUE);
2. 此时需要添加分区，需要先删除max对应的分区，然后再添加我们需要的分区
3. 如果我们最后一个分区是[10,15)，那么我们插入的值>=15都是插入不进去的，会报错。
4. 所有分区的engine必须一样
5. 分区间隔必须是int,分区名称不能以数字开头 这个导致我们ETL中 preload删除分区无法形成这种语法。只能借用delete删除数据
6. less than 表示分割区间是 [ )形式
7. 如果表上有主键或唯一索引列，range方式分区必须加入 https://www.zhihu.com/question/21738216
每个分区值上都有独立的索引。 一般原则就是分区列必作为索引。
分区表的索引只是在各个底层表各自加上一个完全相同的索引。之于存储引擎，分区表的底层表与普通表没有区别。
8. null对分区主键的影响？
9 MySQL中插入换行
直接插入\n 或者通过char(13)的形式，
一般比如 concat_ws('\n',a,b,c)或者 concat_ws(char(13),a,b,c)
1.3.2 分区操作
1 查看分区
代码块
Plain Text
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'table_name';
2 添加分区
代码块
Plain Text
alter table orders_range add partition(partition p5 values less than (25));alter table orders_range add partition(partition p5 values less than MAXVALUE);
note:在create语句中没有进行分区的相关定义，也可以可以添加分区的。
代码块
Plain Text
*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区
中，这样就比较好了，可以省去很多事情，看下面的操作*/
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);
Query OK, 15 rows affected (0.21 sec) //对15数据进行分区
Records: 15 Duplicates: 0 Warnings: 0
3 删除分区
note: 批量删除分区
alter table xx drop drop partition p17177,p17178;
这种带有数字的直接用Excel拖动生成就行了
note:
多表记录级联删除，在一些orm中直接配置级联就可以删除，单纯的数据库而言也是可以的
DELETE FROM tablename [WHERE CONDITION]
delete from emp where ename='dony';
一次删除多个表的数据，语法如下：
delete t1,t2 from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25
4 其他操作
1.4 分区和索引
从本质来说就是两个东西，没有什么本质的关系
但是从效果来说，都能加快查询的速度。但是在修改表（包括但不限于删除）层面上，分区的效率优势就体现出来了。尤其是在表非常大的情况下。
##
1.5 MySQL分表
1.5.1 分表和分区的区别
http://blog.51yip.com/mysql/949.html
1.5.2 索引
1 首先连接字段，在连接的两个表中的字段类型一致比不一致效率高一点，但是不明显，最起码在200w数据量的情况下是不明显的。 note: in(23,225)这种情况就会走range索引
2 count(*)比sum(*)的效率高一点，但是在200w+数据量的情况下只有0.0xs的优势，也是很不明显
3 复合主键在索引层面，就相当于联合索引，也是符合在最左前缀原则
4 索引失效情况
### 从左到右，遇到范围查询即索引失效（in不包括在内）。
范围查询其实可以做进一步的细化区分，in更加细化的描述应该是多值精确匹配， between则是范围查询。
### 隐式类型转换
mysql隐士类型转换坑较多，不要使用隐士类型转换。
note:隐式类型转换还有一个查询匹配的大坑
当字段是string类型的时候，查询条件为 int 0的时候，会匹配上字段中以字符开头的记录
关于隐式转换的规则，
-
当查询字段是INT类型，如果查询条件为CHAR，将查询条件转换为INT，如果是字符串前导都是数字将会进行截取，如果不是转换为0。
-
当查询字段是CHAR类型，如果查询条件为INT，将查询字段为换为INT再进行比较，可能会造成全表扫描。示例2的name字段为CHAR类型，第一句查询转换为INT时都等于0，所以全部匹配了；第二句查询同为CHAR类型不发生转换，所以仅匹配值为0的记录。
https://blog.eood.cn/mysql_params
2 binlog
2.1 binlog
2.1.1 日志文件
http://www.cnblogs.com/martinzhang/p/3454358.html
MySQL的二进制日志记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，
还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。
二进制有两个最重要的使用场景:
其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。 二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
2.1.2 日志文件的扩展
当停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。
2.1.3 日志文件的查看
Mysql提供了mysqlbinlog命令来查看日志文件，如mysqlbinlog xxx-bin.001 | more。在记录每条变更日志的时候，日志文件都会把当前时间给记录下来，以便进行数据库恢复。
2.1.4 日志文件格式
statement格式简单来说会记录下执行的SQL语句，为了保证日志能够恢复或者同步数据，在记录下执行SQL的过程中，还必须记录下上下文环境。但并不是所有的语句statement都能记录，比如udf，没有使用order by的limit 还有其他的一些函数和存储过程。
但是只记录执行的sql语句，一般在整表修改或者alter的时候数据量会比row格式小得多
row格式会记录下变更后的数据，但是不用记录上下文。详细记录了数据修改细节。因为如果碰到alter Table之类的语句会导致所有修改后的记录都会被记录（包括没有修改的列），导致日志数据量非常大。但是不用记录
http://www.cnblogs.com/kevingrace/p/6065088.html
note：公司的许多库使用的都是statement。
MySQL 5.7.7 之后默认格式是row之前使用的是statement。
2.2 binlog开启
https://xcoder.in/2015/08/10/mysql-binlog-try/
http://www.cnblogs.com/EasonJim/p/7158466.html
http://blog.5ibc.net/p/122608.html
http://www.jianshu.com/p/2c8325f0d393
2.3 binlog试用
使用mysql bin目录下的mysqlbinlog命令查看bin ./mysqlbinlog /usr/local/var/mysql/mysql-bin.000001文件，如下图所示
2.4 实践
2.4.1 max_binlog_cache_size溢出
http://liuqunying.blog.51cto.com/3984207/1751687
https://my.oschina.net/u/2485991/blog/533123
note：
max_binlog_size 一般设置为512M或1GB，但不能超过1GB。该设置不能严格控制Binlog的大小，尤其是Binlog遇到比较大的事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。这种情况下回出现日志文件大小超过设定值的情况。
binlog_cache_size 这个默认32k,公司里有1m的也有64M的
max_binlog_cache_size 一个线程来了会按照binlog_cache_size分配cache,不够的会用临时文件，对于一个多事务的任务，max_binlog_cache_size会控制所有的事务总的binlog_cache_size的大小。这个值不会超过4G
show variables like '%binlog_size%'; 该命令查出来的大小的值的单位是bytes
http://blog.csdn.net/m48o8gewuc/article/details/72888092
2.4.2 在row模式下查看原生sql
公司的show variables like 'binlog_rows_query_log_events';也是打开的
http://blog.csdn.net/mchdba/article/details/52268615
2.4.3 业务库大表在线修改表结构：
基本原理是生成中间表，复制数据，最后删除中间表。中间的过程并不总是会复制数据，如果复制数据会导致非常耗时，极有可能会导致锁表。
具体过程？？
3 锁
可以从引擎和读/写/行/表 这两个维度来对MySQL的锁进行分析。
redo&undo https://my.oschina.net/guol/blog/156682
4 基本操作
4.1 修改字段列排序
字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项 first|after
column_name，这个选项可以用来修改字段在表中的位置，默认 ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默认都不会改变字段的位置。
alter table emp add birth date after ename; (alter table emp add birth date after ename,add hah after birth,add hab after hah;)可以一次增加多个列指定顺序。
alter table emp modify age int(3) first; 修改字段 age，将它放在最前面
查询顺序? 不带任何限制的查询，
https://www.zhihu.com/question/19726583
这种有很多影响因素，一般结果会是查询顺序，但是不能依赖于这个（并不一定总是查询顺序）。
字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项 first|after
column_name，这个选项可以用来修改字段在表中的位置，默认 ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默认都不会改变字段的位置。
alter table emp add birth date after ename;
alter table emp modify age int(3) first; 修改字段 age，将它放在最前面
4.2 replace into与 insert into ... on duplicate key update
replace into 相当于有重复key的情况下，会先删除旧的记录，然后添加一条新的记录。没有replace的字段则会以默认值填充。
insert into on....则是只会修改涉及的字段，没有涉及修改的字段还是保留原记录的内容。
http://blog.csdn.net/mchdba/article/details/8647560
5 存储
5.1之后就是compact格式，
-
减少了大约20%的空间
-
在某些操作下会增加CPU的占用
-
在典型的应用场景下，比Redundant快
compress用在varchar特别长，或者blob资源比较多的场景。
6. 问题
mysql不支持正则替换功能，
不支持删除分区的非等
不支持窗口函数，但是MySQL有自己的语法来实现窗口功能。
建表语句：auto_increament=xx 表示自增数字的起点
202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
