mysql基础

一、基本数据类型

先来介绍一下基本数据类型，如果熟悉的话可以跳过：

整型

UNSIGNED和ZEROFILL。选择UNSIGNED的列不允许有负数，选择了ZEROFILL的列会为数值添加零。

• TINYINT——一个微小的整数，支持 -128到127(SIGNED)，0到255(UNSIGNED)，需要1个字节存储。0值代表false，1值代表true。TINYINT（1）常被映射成boolean类型。

• SMALLINT——一个小整数，支持 -32768到32767(SIGNED)，0到65535(UNSIGNED)，需要2个字节存储。

• MEDIUMINT——一个中等整数，支持 -8388608到8388607(SIGNED)，0到16777215(UNSIGNED)，需要3个字节存储。

• INT——一个整数，支持 -2147493648到2147493647(SIGNED)，0到4294967295(UNSIGNED)，需要4个字节存储，一般可用来存储财务上的数据。

• BIGINT——一个大整数，支持 -9223372036854775808到9223372036854775807(SIGNED)，0到18446744073709551615(UNSIGNED)，需要8个字节存储 

浮点型

• FLOAT(precision)——一个浮点数。precision<=24用于单精度浮点数；precision在25和53之间，用于又精度浮点数。FLOAT(X)与相诮的FLOAT和DOUBLE类型有差相同的范围，但是没有定义显示尺寸和小数位数。支持 -3.402823466E+38到-1.175494351E-38，0和1.175494351E-38 to 3.402823466E+38，需要4个字节存储。如果是UNSIGNED，正数的范围保持不变，但负数是不允许的。 

• DOUBLE——一个双精度浮点数。支持 -1.7976931348623157E+308到-2.2250738585072014E-308，0和2.2250738585072014E-308到1.7976931348623157E+308。如果是FLOAT，UNSIGNED不会改变正数范围，但负数是不允许的。 

• DECIMAL（M，D）——将一个数像字符串那样存储，每个字符占一个字节 ，精度是有效数字存储的值的数量,和代表的数量规模可以存储小数点后的位数。如果D省略,默认是0。如果M省略,默认是10。

FLOAT和DOUBLE都是估计值会丢失精度，为了得到准确值官方建议使用DECIMAL。

字符串

如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长 度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索 引效率。varchar的L+1代表存储此类型数据时会留有1字节存储此字段放的字符串长度

日期和时间列类型 

　　日期和时间列类型用于处理时间数据，可以存储当日的时间或出生日期这样的数据。格式的规定：Y表示年、M（前M）表示月、D表示日、H表示小时、M（后M）表示分钟、S表示秒。下面是MySQL中可用的日期和时间列类型 

• DATETIME——格式：'YYYY-MM-DD HH:MM:SS'，范围：'1000-01-01 00:00:00'到'9999-12-31 23:59:59'   8字节

• DATE——格式：'YYYY-MM-DD'，范围：'1000-01-01'到'9999-12-31'  3字节

• TIMESTAMP——格式：'YYYYMMDDHHMMSS'、'YYMMDDHHMMSS'、'YYYYMMDD'、'YYMMDD'，范围：'1970-01-01 00:00:01'到'2038-01-19 03:14:07'   4字节

• TIME——格式：'HH:MM:SS'  3字节

• YEAR——格式：'YYYY，范围：'1901'到'2155'  1字节

二、如何高效建立索引

索引是b+树的数据结构方式存储的。（详细介绍）

索引有哪几种类型：

1.联合索引（遵从左匹配原则，非聚簇索引）

如果explain的时候出现索引合并（index merge），表示耗费⼤量CPU和内存资源在排序和合并上。就要重新考虑下将选择性高的索引放在前面。

2.聚簇索引

一般指定是主键，若不指定主键，InnoDB⽤唯⼀且⾮空值索引代替。聚簇索引在物理上是连续的。

传统分⻚： Select * from table limit 10000,10; 问题：全表扫描，偏移量越⼤，就越慢 

推荐分⻚： Select id from t where id > 10000 limit 10;

3.唯一索引

索引的好处：

索引⼤⼤减少了服务器需要扫描的数据量 

索引可以帮助服务器避免排序和临时表 

索引可以将随机I/O变成顺序I/O

索引对于良好的性能⾮常关键

索引优化是对查询性能优化最有效的⼿段

三、内部原理

数据库索引底层存储是一个b+树，真实的数据存储在叶子节点上，每一个磁盘块加载到内存会发生一次io，io是很花费时间和内存的。如图 找一个数据，最多只会发生3次io，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

综上所述，io的次数取决于树的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

四、高并发

提到高并发就要说起下面这些基础的概念，保证了并发的安全和高效。

ACID特性（四种）：

原子性：保证事务内的操作要么全部提交成功，要么全部失败回滚。

一致性：事务的状态是一致性的。

隔离性：一个事务所做的修改，在未提交之前对其他事务是不可见的。

持久性：一旦事务提交，就会永久存储到数据库中。

行锁：

读锁和写锁：读锁也就是查询是共享锁（快照读不属于共享锁，只有当前读才会上共享锁），写锁是对数据库中存储的修改是排它锁。看名字就能猜到只要有排它锁存在，无论是排他锁还是共享锁都会被阻塞。

        范围锁：

间隙锁(左开右开)

        隔离级别（四种）：可串行化（全部是当前读取，读写全部加锁）、不可重复读（利用间隙锁保证）、可重复读（幻读，innodb使用的就是这种隔离级别，但是有MVCC模式中的快照读避免此问题发生，快照读）、未提交读（脏读）。这四个级别对数据有严到宽。严格的保证数据准确性的时候，就会对数据库性能产生一定的影响。相反对数据准确性要求不高的，具有更好的性能，更好的支持并发。

MVCC：基于每行记录添加两行隐藏列，一个是创建时间（其实就是事务版本号，根据版本号递增），另外一个是删除时间。

死锁：死锁是指两个或以上的事务互相等待对方的资源不释放（即循环等待），产生的一种数据库错误。

如何避免死锁：

1.避免使用的事务中操作过长。

2.避免更新或删除的时候使用的条件是不同的索引交叉。

3.增加死锁判断，重新提交事务。

推荐播客：http://hedengcheng.com/?p=771#_Toc374698306