说明：[]表示可有可无，|表示逻辑或

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字节 年月日时分秒

字符型

CHAR(M) 最多包含255个字符，固定长度，M个字符，浪费空间

VARCHAR(M) 最多包含65535个字符，可变长度，实际分配空间小于等于M个字符，容易导致碎片，除了数据本身外还包括一到两个前缀表明数据的长度。此外，对于定义长度不同的类型，即使实际分配空间相同，消耗的内存页不同，所以不能任意定义长度。

另外，对于CHAR和VARCHAR所能存储最大字母数和汉字数相同

UTF-8编码一个汉字占3个字节，GBK编码一个汉字占2个字节

2、事务

定义

事务由一组SQL语句组成，且具有原子性，事务中的语句要么全部执行成功，要么全部执行失败。

特点

事务具有四个特性，即ACID，包括原子性、一致性、隔离性、持久性。

●原子性(atom)

事务中的所有操作，要么全部执行成功，要么全部失败回滚。

●一致性(consistency)

数据库总是从一个一致性状态转移到另一个一致性状态，不存在中间态，事务只有在提交后，所做的修改才会保存到数据库。

●隔离性(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语句无法成功执行

AFTER触发器执行失败，则SQL语句回滚

SQL语句执行失败，AFTER触发器不会触发

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、索引

概念

索引可以搞查询速度，但也会增加时间和空间上的开销，主要分为普通索引、唯一索引、全文索引、空间索引，另外在创建主键约束、唯一约束时，存储引擎也会创建相应索引。

特点

索引是在存储引擎层实现的，而不是服务器层

在同一个字段上创建重复索引，会影响性能，但可以创建多个不同类型的索引

通常应该扩充已有索引，而不是创建新索引，除非已有索引太长

索引越多，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=”mysql”;可以在表中增加url_crc用于存储哈希值,使用SELECT id FROM url WHERE url=”mysql” AND url_crc=CRC32(“mysql“);可以使用触发器创建维护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会扫描并锁住所有行，情况更糟。

参考

Innodb中的事务隔离级别和锁的关系

MySQL 四种事务隔离级的说明

【mysql】关于悲观锁

深入理解乐观锁与悲观锁

MySQL的学习–触发器

mysql视图学习总结

mysql之游标

高性能MySQL