MySQL数据库

数据类型

浮点数和定点数

浮点数和定点数都可以用类型名称后加“M,D”的方式来表示，表示该值一共显示M位数字（整数位＋小数位），其中D位位于小数点后面，M和Ｄ又称为精度和标度。
MySQL保存值时进行四舍五入。
浮点型（float和double）在不指定精度时，默认会按照实际的精度来显示；如果指定了精度，则会自动四舍五入后插入，系统不会报错；而定点数（decimal）在不指定精度时，默认的整数位为10，默认的小数位为0。如果数据超越了精度和标度值，系统则会报错。

日期时间类型

如果用来表示年月日，通常用DATE来表示；
如果用来表示年月日时分秒，通常用DATETIME表示；
如果只用来表示时分秒，通常用TIME来表示。

字符串类型

CHAR和VARCHAR

都是用来保存较短的字符串，区别在于CHAR列的长度固定为创建表时声明的长度，长度可以为从0~255的任何值；而VARCHAR列中的值为可变长字符串。
检索时，CHAR删除了尾部的空格，而VARCHAR则保留这些空格。

BINARY和VARBINARY

类似于CHAR和VARCHAR类型，不同的是它们包含二进制字符串而不包含非二进制字符串。

ENUM

枚举，它的值范围需要在创建表时通过枚举方式显式指定，最多允许有65535个成员。例如定义gender字段为枚举类型，成员为M和F：

create table t (gender enum('M','F'));

ENUM忽略大小写的。

SET类型

SET和ENUM非常类似，除了存储之外，最主要的区别在于SET类型一次可以选取多个成员，而ENUM则只能选一个。

运算符

流程函数

//如果value是真，返回t，否则返回fif(value,t,f);//如果value1不为空，返回value1，否则返回value2ifnull(value1,value2);//如果value1是真，返回result1，否则返回defaultcase when [value] then [result1] else [default] end;//如果expr等于value1，返回result1，否则返回defaultcase [expr] when [value1] then [result1] else [default] end;

存储引擎

MyISAM

是默认的存储引擎，不支持事务和外键，其优势是访问的速度快，对事务完整性没有要求，或者以select insert为主的应用。
每个MyISAM在磁盘上存储成３个文件，分别存储表定义　数据　和　索引。
表支持３种不同的存储格式，分别是：
静态（固定长度）表；
动态表；
压缩表。
静态表中的字段都是非变长字段，优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。
动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新和删除记录会产生碎片。

InnoDB

提供了具有提交　回滚和崩溃恢复能力的事务安全。但写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

自动增长列

　自动增长列可以手工插入，但是插入的值如果是空或者０，则实际插入的将是自动增长后的值。对于 InnoDB来说，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列。

外键约束

支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动创建对应的索引。、

存储方式

InnoDB存储表和索引有以下两种方式：
共享表空间存储；
多表空间存储。

MEMORY

使用存在于内存中的内容来创建表，每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问地非常快，因为它的数据是放在内存中的，而且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

常用存储引擎的适用环境

MyISAM：默认的存储引擎，如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性　并发性要求不是很高，那么选择这个存储引擎是非常合适的。它是在Web　数据仓储中最常使用的存储引擎之一。

InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多的更新　删除操作，那么InnoDB是比较合适的选择。它还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，都比较合适。

MEMORY：将所有数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。通常用于更新不太频繁的小表。

MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。其优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，有效地改善MERGE表的访问效率。

索引

MyISAM是非聚集索引，叶子节点存放的是数据行的物理地址；
InnoDB是聚集索引，叶子节点存放的，既有索引值（主键），也有数据行本身，而且逻辑上相邻的数据行也相邻存放。

设计索引的原则

• 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，而不是出现在select关键字后的选择列表中的列。
• 使用唯一索引。考虑某列中值的分布，索引的列的基数越大，索引的效果越好。
• 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度。
• 不要过度索引。每个索引都要占用额外的磁盘空间，并降低写操作的性能。

存在索引但不能使用的典型场景

有些时候虽然有索引，但是并不被优化器选择使用：

• 以%开头的like查询不能够利用B-Tree索引；
• 数据类型出现隐式转换不会使用索引；
• 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则，则不会使用复合索引；
• 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引不会被用到。因为后者的查询本身就会走全表扫描的，没有必要多一次索引扫描。

视图

视图是一种虚拟存在的表，并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
相对于普通的表，视图：

• 简单；
• 安全；
• 数据独立。

存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合。存储过程和函数的区别在于函数必须有返回值，而存储过程没有。存储过程的参数可以使用IN,OUT,INOUT类型，而函数的参数只能是IN类型的。

触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
创建触发器的例子：

delimiter $$create trigger ins_film after insert on film for each row begininsert into film_text(film_id,title,description)values (new.film_id,new.title,new.description);end;$$delimiter ;

意思是：插入film表记录的时候，会向film_text表中也插入相应的记录。

sql优化

优化sql语句的一般步骤：

• 通过show status命令了解各种sql的执行频率；
• 定位执行效率较低的sql语句；
• 通过explain分析低效sql的执行计划；
• 通过show profile分析sql；
• 通过trace分析优化器如何选择执行计划

锁

MyISAM和MEMORY存储引擎采用表级锁；InnoDB既支持行级锁，也支持表级锁；BDB存储引擎采用页面锁。
３种锁的特性可大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM表级锁

表级锁有两种模式：表共享读锁和表独占写锁。
读操作不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；写操作会阻塞其他用户对同一表的读和写操作。
MyISAM在执行查询语句前，会自动给涉及的所有表加读锁；在执行更新操作前，会自动给涉及的表加写锁。

InnoDB行锁

InnoDB实现了共享锁和排他锁两种行锁。并且，行锁是通过给索引上的索引项加锁来实现的。
注意，如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果和表锁一样！

Next-Key锁

当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。InnoDB也会对这个间隙加锁，即所谓的Next-Key锁。
InnoDB使用Next-Key锁的目的，可以防止幻读。

主从复制

mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

mysql支持的复制类型：

1. 基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。
2. 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持。
3. 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

复制如何工作

整体上来说，复制有3个步骤：

1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
2. slave将master的binary log events拷贝到它的中继日志(relay log)；
3. slave重做中继日志中的事件，将改变反映它自己的数据。

首先，master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行地写入binary log，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
下一步，slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
最后，SQL slave thread（SQL从线程）从中继日志读取事件，并重做其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

基于语句和行的复制

MySQL 5.0及之前的版本仅支持基于语句的复制（也叫做逻辑复制，logical replication），这在数据库并不常见。master记录下改变数据的查询，然后，slave从中继日志中读取事件，并执行它，这些SQL语句与master执行的语句一样。
这种方式的优点就是实现简单。此外，基于语句的复制的二进制日志可以很好的进行压缩，而且日志的数据量也较小，占用带宽少——例如，一个更新GB的数据的查询仅需要几十个字节的二进制日志。而mysqlbinlog对于基于语句的日志处理十分方便。

但是，基于语句的复制并不是像它看起来那么简单，因为一些查询语句依赖于master的特定条件，例如，master与slave可能有不同的时间。所以，MySQL的二进制日志的格式不仅仅是查询语句，还包括一些元数据信息，例如，当前的时间戳。即使如此，还是有一些语句，比如，CURRENT USER函数，不能正确的进行复制。此外，存储过程和触发器也是一个问题。
另外一个问题就是基于语句的复制必须是串行化的。这要求大量特殊的代码，配置，例如InnoDB的next-key锁等。并不是所有的存储引擎都支持基于语句的复制。

---

MySQL增加基于记录的复制，在二进制日志中记录下实际数据的改变，这与其它一些DBMS的实现方式类似。这种方式有优点，也有缺点。优点就是可以对任何语句都能正确工作，一些语句的效率更高。主要的缺点就是二进制日志可能会很大，而且不直观，所以，你不能使用mysqlbinlog来查看二进制日志。

复制的常用拓扑结构

复制的体系结构有以下一些基本原则：
(1) 每个slave只能有一个master；
(2) 每个slave只能有一个唯一的服务器ID；
(3) 每个master可以有很多slave；
(4) 如果你设置log_slave_updates，slave可以是其它slave的master，从而扩散master的更新。例如：

分库分表

分库：按照业务把不同的数据放到不同的库中。例如用户的收藏夹的数据和博客的数据库就可以放到两个独立的服务器。这个就叫垂直分库。

分表：把一个表的数据划分到不同的数据库，两个数据库的表结构一样。怎么划分，应该根据一定的规则，可以根据数据的产生者来做引导，上面的数据是由人产生的，可以根据人的id来划分数据库。
以刚才的博客为例，数据可以根据userid的奇偶来确定数据的划分。把id为基数的放到A库，为偶数的放B库。

这样通过userId就可以知道用户的博客的数据在哪个数据库。其实可以根据userId%10来处理。还可以根据著名的HASH算法来处理。