MySQL基本概念

说明：[]表示可有可无，|表示逻辑或
1、数据类型
整型及浮点型
TINYINT 1字节
SMALLINT 2字节
MEDIUM 3字节
INT 4字节
BIGINT 8字节
FLOAT 4字节
DOUBLE 8字节

日期时间型
YEAR 1字节 年份
DATE 3字节 年月日
TIME 3字节 时分秒
DATETIME 8字节 年月日时分秒
TIMESTAMP 8字节 年月日时分秒

字符串
字符串使用单引号括起来，并且MySQl能够识别字符串中的转义序列。字符串分为二进制字符串和非二进制字符串两种类型。二进制字符串包括：BINARY、VARBINARY、BLOB，分别对应非二进制字符串中：CHAR、VARCHAR、TEXT类型。字符串的长度可以使用字节的个数或字符的个数来衡量
二进制字符串是一些字节序列，对字符串的解释不涉及字符集；没有特殊的比较和排序规则，比较操作根据各字节的数值逐字节比较，所有字节都要进行比较，包括末尾的空格。每个字符占用一个字节。字符串不区分大小写

非二进制字符串是一些字符序列，每个字符串涉及一种字符集，比如UTF8、GBK。字符集决定了哪些字符可以表示数据以及MySQL如何解释字符串的内容。每种字符集都有一种或多种排序方式。字符串末尾的空格不参加比较，但TEXT类型除外。使用不同的字符集，每个字符所占用的存储空间不同。比如UTF-8字符集一个汉字字符占3个字节，GBK字符集一个汉字字符占2个字节。

CHAR（M） 固定长度，M个字符，M最大为255
VARCHAR（M） 可变长度，最多为M个字符，M最大为65535。除数据本身外还使用两个字节存储字符串的长度。
如何选择CHAR和VARCHAR类型
如果数据长度相同，使用CHAR比VARCHAR节省空间，选择CHAR，如果不同则使用VARCHAR
如果数据长度接近，尤其是使用MyISAM引擎，使用CHAR更好，因为MyISAM引擎对固定长度数据处理效率更高
2、事务
定义
事务由一组SQL语句组成，且具有原子性，事务中的语句要么全部执行成功，要么全部执行失败。

特点
事务具有四个特性，即ACID，包括原子性、一致性、隔离性、持久性。
●原子性（atom）
事务中的所有操作，要么全部执行成功，要么全部失败回滚。
通过将更新操作写入日志，对事务中部分执行成功的操作执行undo操作，可以保证事务的一致性，对事务中执行成功但未将数据写入磁盘的操作执行redo操作，可以保证事务的持久性。
●一致性（consistency）
数据库总是从一个一致性状态转移到另一个一致性状态，不存在中间态。
比如支付宝A账户转账给支付宝B账户，结果是A账户少的钱等于B账户增加的钱，不会存在A账户少了，B账户没多的情况。将两个操作放在一个事务中即可保证一致性，事务的原子性保证了一致性。
●隔离性（isolation）
通常来说，事务在提交之前，对数据所做的修改对其他事务是不可见的。
●持久性（durabilty）
事务一旦提交，所做的修改则会永久保存在数据库中

使用
START TRANSCATION; //开启事务
SQL语句
COMMIT;//提交事务

MySQL中默认采用自动提交模式，即：如果不是显式的开始一个事务，则每个查询都会被当做一个事务执行提交操作。可以通过SHOW VARIABLES LIKE ‘AUTOCOMMIT’;查询自动提交是否开启。通过SET AUTOCOMMIT=1|0开启或关闭自动提交。当关闭自动提交后，只有显式的执行COMMIT或ROLLBACK，事务才结束。

3、隔离级别
READ UNCOMMITTED(未提交读）
事务中对记录做的修改，即使没有提交，对其他事务也是可见的。事务可以读取未提交的修改会导致脏读。实际中很少用到。

READ COMMITED(提交读)
事务只有在提交后，所做的修改才对其他事务可见，会导致不可重复读。在一次事务中，多次读取同一数据，由于其他事务对数据进行更新操作，读到的结果可能不同，这就是不可重复读*。

REPEATABLE READ(可重复读）
在可重复读级别，一次事务中，多次读取同一数据，读到的结果相同，但不能解决幻读的问题，因为在读取数据后数据加锁，虽然其他操作无法update和delete数据，但是无法阻止insert记录。幻读即：当某个事务查询某一范围的记录时，由于另一个事务同时也在向此范围insert记录，导致事务中查询到了不存在的记录，出现幻觉。在可串行化级别，可以使用悲观锁来解决幻读问题。但实际中MySQl使用了以乐观锁为基础的多版本并发控制(MVCC)。可重复读是MySQL的默认隔离级别，多版本并发控制使用Next-Key锁避免了幻读的出现。在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，保证会读取到正确的行，并且只需锁住必要行。

SERIALIZABLE(可串行化)
可串行化是最高隔离级别，通过在读取每行数据时加锁，读用读锁，写用写锁，读写互斥，强制事务串行执行，解决了幻读的问题，但同时会影响数据库的并发性能。
总结

隔离级别 脏读 不可重复读 幻读 加锁读 READ UNCOMMITTED 是 是 是 否 READ COMMITED 否 是 是 否 REPEATABLE READ 否 否 是 否 SERIALIZABLE 否 否 否 是

SELECT @@global.tx_isolation;或SELECT @@session.tx_isolation;分别查询全局和当前会话隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};或通过修改my.ini配置文件中
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
修改隔离级别
4、乐观锁与悲观锁
悲观锁
定义：在数据处理过程中，使数据处于锁定状态，读取数据时给数据加锁，其他事务无法修改数据，修改删除数据时给数据加锁，其他事务无法读取数据。悲观锁的实现需要依靠数据库提供锁机制，即使本系统提供了锁机制，也无法保证外部系统不会修改这些数据。

流程
●在修改任意记录之前，为记录加上排它锁,在读数据之前，为记录加上共享锁。
●如果加锁失败，则进入等待状态或剖出异常。
●如果加锁成功，则可以对记录进行修改，事务完成后释放锁。
●如果期间有其他事务修改记录，则需要等待解锁或抛出异常。

优点
增加数据处理安全性
缺点
●处理加锁机制，增加系统开销，提高死锁的几率
●一旦锁定了某条记录，其他相应事务必须等待，降低并发
●无论是行锁还是表锁，加锁时间长，降低并发

乐观锁
定义：修改数据前不会加锁，只有在事务提交时检查修改是否发生冲突，如果发生冲突，则会提示错误信息，此时再读写数据加锁处理，如果没有发生冲突，则成功提交。乐观锁不会使用数据库提供的锁机制，通过系统实现，通过记录版本号的方式实现。

流程
读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与修改之前的版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

优点
并发性能好
5、死锁
定义
当多个事务运行时，事务占用彼此需要的资源，导致所有事务无法完成。
处理方法
InnoDB引擎会选择持有最少行级排它锁的事务进行部分或完全回滚。

数据库中事务分为加锁阶段和解锁阶段。
加锁阶段：在对数据进行读操作之前获取共享锁（其他事务可以继续加共享锁，但不能加排它锁），在写操作之前获取排它锁（其他事务不能在获得任何锁）。如果加锁不成功，事务则进入等待状态，直到获得锁才继续执行。
解锁阶段：在事务提交时，释放占用的锁。
6、日志分类
mysql中主要包含四中日志，分别是：错误日志、慢查询日志、二进制日志、查询日志。
错误日志：记录数据库发生错误的信息
慢查询日志：记录执行时间超过指定阈值的SQL语句
二进制日志：记录数据发生更改的信息
查询日志：记录对数据库执行的一切操作
事务日志：存储引擎修改数据时只需要修改内存中数据拷贝，然后把修改过程写到磁盘中的事务日志，日志持久化以后，内存中修改的数据在写入磁盘。由于事务日志采用的是追加的方式，所以写事务日志非常快。
7、触发器
定义：当触发器所在的表发生指定事件时，触发触发器执行相应的操作。

创建触发器
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

trigger_name:触发器的名字
trigger_time：何时触发触发器，取值为 BEFORE 或 AFTER；
trigger_event：触发器所在的表发生什么事件时，触发触发器，值可以是INSERT、UPDATE、DELETE
tbl_name：在该表上建立触发器
trigger_stmt:触发器具体执行的语句，其中常常使用NEW和OLD关键字
NEW和OLD表示触发器所在表中触发了触发器的那一行数据，NEW表示新数据，OLD表示旧数据
在INSERT型触发器中，NEW表示新数据
在UPDATE型数据表中，NEW表示新数据，OLD表示旧数据
在DELETE数据表中，OLD表示删除的旧数据
使用方法：NEW.column_name
查看触发器：SHOW TRIGGERS FROM database_name;
删除触发器：DROP TRIGGER trigger_name;

触发器执行顺序
BEFORE触发器执行失败，则相应SQL语句无法成功执行
SQL语句执行失败，AFTER触发器不会触发
AFTER触发器执行失败，则SQL语句回滚
8、视图
定义：视图是基于sql语句查询结果的虚拟表，本身并不存储数据，而是引用其他数据表中的数据。当使用视图时，视图通过引用基本表中的数据提供查询所需数据。视图数据被修改时，基本表中的数据相应也会修改，基本表中的数据被修改后，视图也会发生变化。

视图种类：MERGE、TEMPLATE、UNDEFINED.
对于此查询语句：select * from view_name;对于不同种类视图，具体执行过程不同
MERGE
查询语句会先与视图声明语句合并，然后执行合并后的语句。
TEMPLATE
视图的结果存放在一张临时表中，查询语句使用临时表中的数据。
UNDEFINED
创建视图时，如果没有指定视图类型，mysql优先使用MERGE类型，其次使用TEMPLATE类型

优点
●视图通过隐藏数据，可以增加数据安全性。
●将视图与用户权限绑定，可以增加数据安全性
●简化复杂的操作，比如经常用到某个子查询，这时可以创建一个视图，将视图用到查询语句中

注意
●不能将视图与触发器关联在一起。
●存储过程中不能修改视图
●视图所对应的表不能是临时表
●不能给视图添加索引
●创建视图时，不能使用用户变量
●TEMPLATE型视图无法进行更新
●视图中的数据必须和基本表中的数据时一一对应时，视图才可以更新

创建视图
CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];
视图也是一种表，所以创建时不能与已有表重名！
WITH CHECK OPTION 表示如果更新视图数据后，更新后的数据不再满足视图定义，即不存在在视图中，那么不允许更新视图。
eg
CREATE VIEW teams(TEAMNO,PLAYERNO,DIVISION) AS SELECT DISTINCT TEAMNO,CAPTAIN,DIVISION FROM result

修改视图
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];

删除视图
DROP VIEW view_name;
9、游标
定义：游标是由sql语句查询得到的结果集，通过fetch可以访问结果集中的每行数据。
注意：游标只能用于存储过程；创建游标后，必须先打开游标后，才能使用游标；使用完游标后必须关闭游标

创建游标
CREATE PROCEDURE procedure_name
BEGIN
DECLARE cursor_name CURSOR FOR select_statament
END

打开游标：OPEN cursor_name;
关闭游标：CLOSE cursor_name;
访问数据：FETCH cursor_name INTO variable_name;FETCH语句访问每一行数据，并自动移动游标中的指针，下一条FETCH语句则访问下一行数据。
eg：
CREATE PROCEDURE procedure_name
BEGIN
DECLARE variable_name variable_class；
DECLARE cursor_name CURSOR FOR select_statament
OPEN cursor_name;
FETCH cursor_name INTO variable_name;
CLOSE cursor_name;
END
10、存储引擎

存储引擎 支持事务 支持B树索引 支持外键 锁颗粒 存储限制 支持数据压缩 支持MVCC InnoDB 是 是 是 行锁 64TB 是 是 MyISAM 否 是 否 表锁 256TB 是 否 MEMORY 否 是 否 表锁 RAM大小 否 否 ARCHIVE 否 否 否 行锁 无限制 是 否

11、约束
主键约束(PRIMARY KEY)
●每张数据表只能有一个主键约束
●主键保证记录的唯一性，此字段的值唯一
●主键自动为NOT NULL
●自动编号（AUTO_INCREMENT）的字段必须为主键，但主键不必须为自动编号
●存在主键约束的字段会自动创建索引
●主键约束是通过索引实现的，创建主键约束相当于创建了索引

唯一约束(UNIQUE KEY)
●每张数据表可以有多个唯一约束
●唯一约束保证记录的唯一性，此字段的值唯一
●存在唯一约束的字段可以NULL，即一张表的多个记录的此字段值可以为空
●唯一约束是通过索引实现的，创建唯一约束相当于创建了索引

默认约束(DEFAULT)
●当插入记录时，如果没有明确为此字段赋值，则为默认值

非空约束(NOT NULL)
●字段值不能为空

外键约束(FOREIGN KEY)
●保证数据的一致性和完整性
●实现一对一或一对多的关系
●父表和字表必须使用Innodb存储引擎。（父表即被参照的表，字表即参照的表）
●外键列和参照列必须创建索引。如果外键列不存在索引的话，外键列会自动创建索引。如果参照列不存在索引的话，参照列不会自动创建索引。（外键列即子表中的列，参照列即父表中的列）
●外键列和参照列必须具有相似的数据类型。字符型的长度可以不同。其他类型必须完全相同。
●必须先向父表中插入数据再向字表中插入数据，因为字表需要参考父表
外键约束的参数设置
CASCADE:从父表中删除或更新时且自动删除或更新字表中对应记录的外键列
SETNULL：从父表中删除或更新时设置字表中对应记录的外键列为NULL。字表中外键列不能设置为NOT NULL
RESTRICT:拒绝对父表的删除和更新操作
NO ACTION:与RESTRICT作用相同
eg： CREATE TABLE parent1(id SMALLINT UNSIGNED ,name VARCHAR(10) PRIMARY KEY);
CREATE TABLE child(cid SMALLINT UNSIGNED ,cname VARCHAR(10),FOREIGN KEY(cname) REFERENCES parent1(name)ON DELETE|UPDATE CASCADE | SETNULL | RESTRICT | NO ACTION);
12、索引
概念
索引是对记录按照字段进行排序的一种方式。对字段建立索引会创建一种数据结构，其中保存着字段的值，每个值又指向与它相关的记录。索引可以提高查询速度，但也会增加时间和空间上的开销，主要分为普通索引、唯一索引、全文索引、空间索引，另外在创建主键约束、唯一约束时，存储引擎也会创建相应索引。

特点
索引是在存储引擎层实现的，而不是服务器层
在同一个字段上创建重复索引，会影响性能，但可以创建多个不同类型的索引
通常应该扩充已有索引，而不是创建新索引，除非已有索引太长
索引越多，UPDATE、DELETE 、INSERT操作越慢
索引的选择性越高，查询效率越高
索引大大减少了服务器需要扫描的数据
索引可以帮助服务器避免排序

何时会使用索引
●使用比较运算符> >= < <= =
●使用BETWEEN……..AND
●使用IN匹配
●使用LIKE进行后导模糊查询，比如查询以M开头的名字
何时不会使用索引
●使用不等于操作
●索引列是表达式的一部分或是函数的参数
●含有前导模糊查询的LIKE语句
●如果查询条件中第一个字段不能使用索引，整个查询不会使用索引

创建索引需要注意的地方
●为经常被查找、分类、排序的数据建立索引
●选择短小的字段建立索引，比如可以选择字符串类型左边部分值建立索引
●选择区分度高的字段建立索引
●不要过多的创建索引，因为索引会占用磁盘空间，并且降低写入性能

BTREE索引
概念
BTREE索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，而是从索引的根节点开始进行搜索，节点中存放了指向数据的指针。BTREE对索引列是按顺序存储的，所以很适合查找一定范围的数据。

BTREE索引对以下类型查询有效
●全值匹配
全值匹配指的是和索引中所有列进行匹配
●匹配最左前缀
只匹配索引列的左边部分列
●匹配列前缀
只匹配某一列的值的开头部分，比如匹配以s开头的姓
●匹配范围值
匹配某一个范围的值
●精确匹配某一列并范围匹配另外一列
●只访问索引的查询
查询只需要访问索引即可，不需要访问数据表，即覆盖索引。
BTREE索引限制
●如果不是按照索引的最左列开始查找，则无法使用索引，即必须是最左前缀列
●不能跳过索引中的列
●如果WHERE条件中有某个的范围查询，那么右边所有列都无法使用索引优化查找，本列及左边列可以
●BTREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引

哈希索引
概念
哈希索引基于哈希表实现，MEMORY和NDB存储引擎支持哈希索引。MEMORY存储引擎并且支持非唯一哈希索引，如果多个列的哈希值相同，索引会以链表的形式存放多个记录的指针到哈希表中。
对于不支持哈希索引的存储引擎，可以创建自定义哈希索引，可以使用CRC32、FNV64等哈希函数生成哈希值，比如对于查询：SELECT id FROM url WHERE url=”http://www.mysql.com”;可以在表中增加url_crc用于存储哈希值,使用SELECT id FROM url WHERE url=”http://www.mysql.com” AND url_crc=CRC32(“http://www.mysql.com“);可以使用触发器创建维护url_crc列，创建一个BEFORE类型触发器，使用SET NEW.url_crc=CRC32(NEW.url);完成赋值

哈希索引限制
●哈希索引只存储哈希值和行指针，不存储字段值，无法实现覆盖索引
●哈希索引并不是按照索引值顺序存储的，无法用于排序
●哈希索引不支持使用索引列的部分列进行查找，因为索引是根据所有列计算哈希值的
●哈希索引只支持等值比较查询，不支持任何范围查询
●访问哈希索引的数据非常快，除非有多个哈希冲突
●如果有很多哈希冲突，索引维护的代价会很高

覆盖索引
概念：如果索引中含有查询所需要的所有字段的值，就称为覆盖索引。
特点
●查询只需找到对应索引，即可读取到所需查找的数据，无需读取数据表
●索引条目远小于数据行，只读取索引会极大减少数据访问
●索引是按照列值顺序存储的，对于范围查询优势明显
●只有BTREE索引可以成为覆盖索引，其他索引不能成为覆盖索引
●Memory引擎不支持覆盖索引
●能在索引中匹配做最左前缀匹配的LIKE比较，但不能是通配符开头的LIKE查询

重复索引：在同一字段上创建多个相同类型的索引
冗余索引：选取已有多列索引的最左前缀列创建新的索引

索引和锁
InnoDB只有在访问行的时候才会锁定行，而索引可以减少访问的行数，那么就会减少锁的数量，从而增加并发，但是只有在存储引擎层过滤掉所有不需要的行时才有效。如果索引无法过滤掉所有不需要的行，那么存储引擎在检索到数据并返回服务器层后，服务器才能应用WHERE字句
eg
SELECT * FROM test WHERE id<5 AND id>1;
服务器并没有告诉存储引擎过滤id=1的行，存储引擎会返回给服务器id为2、3、4的行，然后服务器再应用id>1的过滤条件。即使使用了索引，存储引擎也会锁住一些不必要的行，但如果不使用索引，MySQL会扫描并锁住所有行，情况更糟。

13、复制
概念
复制指的是让一台服务器的数据和其他服务器中的数据保持同步。一台主库上数据可以同步到多台从库上，从库本身同时也可以作为其他服务器的主库。

复制过程
●主库在提交事务完成数据更新之前，将更新操作写入到二进制文件中，写入顺序与事务的提交顺序有关
●从库启动一个I/O线程，与主库建立连接，从主库读取二进制文件，将文件中的操作写入自己的中继日志文件中
●从库启动一个SQL线程，执行中继日志文件中的操作

复制的作用
●在不同位置存储数据，实现备份
●将读操作分配到多个服务器，实现负载均衡
●避免单机数据出现问题，影响应用的正常运行

复制方式
●基于语句的复制
主库会将更改数据的操作写入二进制日志，从库读取这些操作并记录到中继日志中，从库执行中继日志中操作。基于语句的复制实现简单，但也存在一些问题：数据的更新除了与语句有关外，可能还与时间有关，比如与当前用户有关的语句，可能会因为执行时间不同，造成主从数据库数据不一致；为了使主从数据库更新顺序相同，更新必须串行化，导致需要更多的锁，阻塞许多操作
●基于行的复制
主库将更新后的数据写入二进制文件，而不是操作，从库读取二进制文件中的数据并写入自己的数据库。基于行的复制从库不需要执行SQL语句，占用更少的CPU；在某些情况下效率很高，比如需要从一张数据表中进行大量查询，将得到的少量结果写入另一张数据表；在某些情况下效率很低，比如对一张大表进行全表更新，二进制文件会很大；另外，无法知道数据库执行了哪些操作

复制可能产生的问题
●复制存在延迟
●二进制日志、中继日志占用磁盘空间
●主库意外关闭，二进制文件没有写入磁盘，造成数据丢失
●从库意外关闭，maste.info文件没有写入磁盘，从库恢复后，不知道从哪里开始复制
●使用不支持事务的存储引擎，可能造成主从库数据不一致，比如主库使用不支持事务的存储引擎，原本打算更新100条数据，结果出现意外，只更新了50条，但从库会更新100条数据，造成数据 不一致
●数据不一致会在表之间传播，比如主从库两张表数据不一致，从这两张表中选取数据构成一张新表
●如果主库上有从库上不存在的表，或从库上有主库上不存在的表，复制会意外中断
●进行读操作时，需要加锁，从而保证主从库上执行顺序一致，会阻塞其他操作
●如果主库上含有从库上不存在的记录，导致更新或删除失败

参考
Innodb中的事务隔离级别和锁的关系
MySQL 四种事务隔离级的说明
【mysql】关于悲观锁
深入理解乐观锁与悲观锁
MySQL的学习–触发器
mysql视图学习总结
mysql之游标
高性能MySQL