数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

笛卡尔积

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

• 有两个集合A，B 取这两个集合的所有组成情况
• 要完成多表查询，需要消除无用的数据

数据库范式

• 第一范式 1NF
  • 没一列都是不可分割的原子数据
• 第二范式 2NF
  • 在1NF的基础上非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
  • 函数依赖
    • A —> B 如果通过A属性（属性组）的值，可以确定唯一B属性的值 则称B依赖于A
    • 例如：学号 —> 名称 （学号，课程）—> 分数
  • 完全函数依赖
    • A —> B 如果A是一个属性组，则B属性值得确定需要依赖A属性组中的所有的属性值
    • 例如：（学号，课程）—> 分数
  • 部分函数依赖
    • A —> B 如果A是一个属性组，则B属性值得确定只需要依赖A属性组中的某一些值
    • 例如：（学号，课程）—> 分数
  • 传递函数依赖
    • A —> B ， B —> C 如果通过A属性（属性组）的值，可以确定唯一值B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称为C传递函数依赖于A
    • 例如： 学号 —> 系名 系名 —> 系主任
  • 码
    • 如果在一张表中，一个属性或属性组被其他所有属性完全依赖，则称这个属性（属性组）为该表的码
    • 例如：该表中码为：（学号，课程名称）
    • 主属性：码属性组中的所有属性
    • 非主属性：除码属性组之外的属性
• 第三范式 3NF
  • 在2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

事务

事务的四大特征

• 事务特征（ACID）
  • 原子性（Atomicity）指事务是一个不可分割的整体，其中的操作要么全执行或全不执行
  • 一致性（Consistency）事务前后属性的完整性必须保持一致
  • 隔离性（Isolation）事务的隔离性是多个事务并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
  • 持久性（Durability）指一个事务一旦被提交，他对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务提交方式

• 自动提交
  • mysql数据库默认自动提交
  • 一条DML语句会自动提交一次事务
• 手动提交
  • Oracle数据库默认手动提交
  • 需要先开启事务再提交 或者回滚

修改事务的默认提交方式

• 1 代表自动提交
• 0 代表手动提交 ```plsql — 查看默认提交方式
  select @@autocommit;

— 修改默认提交方式 set @@autocommit = 0;

<a name="UTXlp"></a>
## 事务隔离级别
**隔离级别从小到大安全性越高，但是效率越来越低**
- **read uncommitted        读未提交 **
   - 产生问题：脏读、不可重复读、幻读
- **read committed        读已提交 **
   - 产生问题：不可重复读、幻读
- **repeateable read           可重复读** 
   - 产生问题：幻读
   - 默认事务隔离级别
- **serializable                **可序列化的
   - 可以解决所有问题
多个事务之间相互独立，但是多个事务操作同一数据则会引发一些问题，设置不同的隔离基本就可以解决这些问题。
1. 脏读：一个事务读取到另一个事务中没有提交的数据
1. 不可重复读（虚读）：在同一个事务中，两次读取的数据不一致
1. 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
```plsql
-- 设置隔离级别需要重新打开mysql窗口
-- 查询数据库隔离级别
select @@tx_isolation; 
-- 设置数据库隔离级别
set global transaction isolation level 级别;

索引

InnoDB

InnoDB索引实现

• 主键索引
  • InnoDB存储引擎的主键索引，叶子节点中索引关键字和数据是在一起存放的，非叶子节点存存储主键信息
• 辅助索引
  • InnoDB的辅助索引，叶子节点上存放的是索引关键字和对应的主键

辅助索引的B+树，先根据关键字找到对应的主键，再取主键索引树上找到对应的行记录数据。
从索引树上可以看到，InnoDB的索引关键字和数据都是在一起存放的，体现在磁盘存储上。

例如创建一个user表，会在磁盘上只存储两种结构：user.frm（存储表的结构）、user.idb（存储索引和数据）
InnoDB的索引树叶子节点包含了完整的数据记录，这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按照主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有指定显示，则MyISAM系统会自动选择一个可以唯一标识的数据记录列作为主键，如果不存在这种列，则Mysql会自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

索引设计原则

• NORMAL：普通索引。
  • 索引列没有任何限制
• PRIMARY：主键索引。
  • 索引列唯一且不能为空；一张表只能有一个主键索引（主键索引通常在建表的时候就指定）
• UNIQUE：唯一索引。
  • 索引列的值必须是唯一的，但允许有空；
• FULLTEXT：全文搜索的索引。
  • FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

索引的分类

按照功能分类

• 普通索引：最基本的索引，没有任何的限制
• 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一
• 主键索引：一种特殊的唯一索引，不允许有空值。在建表时有主键列同时创建主键索引
• 联合索引：将单列索引进行组合
• 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作
• 全文索引：快速匹配全部文档的方式，InnoDB引擎5.6版本以后才支持全文索引。Memory引擎不支持
• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：一个索引包含多个列

索引提示

• FORCE INDEX
  • 强制使用索引
• USE INDEX
  • 使用索引
• IGNORE INDEX
  • 忽略索引

    SELECT * FROM table_name FORCE INDEX (`index_name`)

索引失效的几种情况

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
2. 联合索引，不是使用的第一部分，则不会使用索引
3. like查询以%开头
4. 字符串不加单引号
5. 范围查询右边的字段

避免索引失效

1. 全值匹配，对索引中索引的列都指定具体值 该情况下，索引生效，执行效率高
2. 最左前缀法则 如果索引包含了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列
3. 范围查询右边的字段，不能使用索引
4. 不要在索引列上进行运算操作，否则索引失效
5. 字符串不加单引号，索引将失效

优化

-- 查询以Com开头的数据的操作状态
show status like 'Com____';
-- 查询innodb数据库的操作状态
show global status lick 'Innodb_rows_%';
-- 查询当前连接中执行的sql(慢连接)
show processlist

explain

• id：id的值不同，则代表查询的顺序不同 可以得到表结构的加载顺序
• select_type： 表示select的类型
  • SIMPLE：简单的select查询，查询中不包含子查询或者UNION
  • PRIMARY：查询中若包含任何复杂的子查询，最外层查询标记为该标识
  • SUBQUERY：在select或where列表中包含了子查询
  • DERIVED：在from列表中包含子查询，被标记为DERIVED（衍生）mysql会递归执行这些子查询，把结果放在临时表中
  • UNION：若第二个select出现在union之后，则标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
  • UNION RESULT：从union表获取结果的select
• table:输出结果集的表
• partitions:匹配的分区
• type:表示表的连接类型
• possible_keys:表示查询时，可能使用的索引
• key:表示实际使用的索引
• key_len:索引字段的长度
• ref:列与索引的比较
• rows:扫描出的行数(估算的行数)
• filtered:按表条件过滤的行百分比
• Extra:执行情况的描述和说明

profile

查看每条sql语句的耗时时间

-- 查询是否支持profile
select @@have_profiling;
-- 查询是否打开profile
select @@profiling;
-- 当前会话开启profile
set profiling=1;
-- 查询每个操作的耗时
show profiles;
-- 查看查询语句执行阶段的耗时时间
show profile frq query 查询语句id;

FAQ

InnoDB 与 MyISAM 的区别

• 对比MyISAM，InnoDB写效率差，更加耗费资源
• 相比之下，InnoDB适合大量数据操作，MyISAM适合简单业务处理
• 除增删外还需要更新、删除操作应选择InnoDB
  • 行锁的缘故
• MyISAM崩溃后数据无法恢复，与不支持事务相关
• 无特殊原因不推荐MyISAM存储引擎