索引相关
索引类型
主键索引：数据列不允许重复，不允许为NULL。一个表只能有一个主键索引。InnoDB的主键索引为聚簇索引，而MyISAM的主键索引为非聚簇索引。
创建：ALTER TABLE table_name ADD PRIMARY KEY (column);
唯一索引：数据列不允许重复，允许为NULL，一个表中允许创建多个唯一索引。唯一索引可以用作业务防重。
创建：ALTER TABLE table_name ADD UNIQUE (column1, column2);
普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
创建：ALTER TABLE table_name ADD KEY (column1, column2)
全文索引：是目前搜索引擎使用的一种关键技术，InnoDB不支持全文索引，MyISAM支持全文索引。
创建：ALTER TABLE table_name ADD FULLTEXT (column);
最左前缀原则
联合索引从左边的列到右边的列依次匹配，联合索引的最左前缀原则如下：
对于联合索引：index(co1, col2, col3)
-
where col1=1，where col1=1 and col2=2，where col1=1 and col2=2 and col3=3都会走这个索引。第一个语句使用 col1，第二个语句使用 col1,col2，第三个使用 col1,col2,col3； -
where col2=2，where col2=2 and col3=3不会走索引; -
where col3=3 and col2=2 and col1=1未经查询优化的话不会走索引，但是经过查询优化后会走索引...； -
where col1=1 and col3=3仅使用 col1； -
where col1=1 and col2>2 and col3=3仅使用 col1,col2； -
where col1=1 and col2 like 'xx%'使用 col1,col2； -
where col1=1 and col2 like '%xx''仅使用 col1。
索引算法（B-Tree 和 HASH）
B-Tree索引：
B-Tree索引是一棵多路查找平衡树，InnoDB 和 MyISAM 存储引擎都支持 B-Tree 索引。InnoDB 支持聚簇索引，InnoDB 默认会为主键创建聚簇索引，聚簇索引的非叶子节点不保存数据，只有叶子节点会保存数据，聚簇索引的叶子节点还会保存相邻的后一个节点的指针。非聚簇索引叶子节点不保存数据，只保存主键索引。
HASH索引：
Hash 索引检索效率高，只需要一次定位，不像 B-Tree 索引需要从根节点到叶子节点，最后才能访问到页节点这样多次的IO访问。
Hash索引虽然检索效率高，但是也有很多的不足之处：
-
Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。 -
Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且 Hash 值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算； -
Hash 索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。 -
Hash 索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。 -
Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。
B-Tree vs. HASH索引
| 索引名 | HASH | B-Tree |
|---|---|---|
| 是否支持最左前缀匹配原则 | 不支持，只有索引的全部字段都用上才会匹配到 | 支持，用上索引的第一个字段就可以匹配索引 |
| MyISAM和InnoDB是否支持 | 不支持（只有Memory和NDB引擎索引支持） | 支持 |
| 范围查询能否命中索引 | 不可以，只有“=”,“IN”,“<=>”（等价于的意思）查询能命中 | 可以 |
| 是否一定会全表扫描 | 是 | 否 |
索引设计原则
- 为经常需要排序、分组和联合的字段建立索引
- 为经常作为 where 条件查询的字段建立索引
- 在选择性好的列上建立索引，所谓选择性好是指筛选出来的数据少，例如性别的选择性就比较差，而姓名的选择性就高
- 联合索引需要注意索引的顺序，将选择性高的放在前面
- 索引虽然能加快查询，但是索引也是有代价的，首先索引会占用存储空间。其次索引的维护也是有消耗的。因此，索引不是越多越好。
数据类型
数字类型
整数: tinyint、smallint、mediumint、int、bigint
| type | Storage | Minumun Value | Maximum Value |
|---|---|---|---|
| (Bytes) | (Signed/Unsigned) | (Signed/Unsigned) | |
| TINYINT | 1 | -128 | 127 |
| 0 | 255 | ||
| SMALLINT | 2 | -32768 | 32767 |
| 0 | 65535 | ||
| MEDIUMINT | 3 | -8388608 | 8388607 |
| 0 | 16777215 | ||
| INT | 4 | -2147483648 | 2147483647 |
| 0 | 4294967295 | ||
| BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
| 0 | 18446744073709551615 |
浮点数: float、double、real、decimal
| 属性 | 存储空间 | 精度 | 精确性 | 说明 |
|---|---|---|---|---|
| FLOAT(M, D) | 4 bytes | 单精度 | 非精确 | 单精度浮点型，m总个数，d小数位 |
| DOUBLE(M, D) | 8 bytes | 双精度 | 比Float精度高 | 双精度浮点型，m总个数，d小数位 |
FLOAT容易造成精度丢失
定点数DECIMAL
- 高精度的数据类型，常用来存储交易相关的数据
- DECIMAL(M,N).M代表总精度，N代表小数点右侧的位数（标度）1 < M < 254, 0 < N < 60;
- 存储空间变长
日期和时间
date、time、datetime、timestamp、year
| 类型 | 字节 | 例 | 精确性 |
|---|---|---|---|
| DATE | 三字节 | 2015-05-01 | 精确到年月日 |
| TIME | 三字节 | 11:12:00 | 精确到时分秒 |
| DATETIME | 八字节 | 2015-05-01 11::12:00 | 精确到年月日时分秒 |
| TIMESTAMP | 2015-05-01 11::12:00 | 精确到年月日时分秒 |
- MySQL在
5.6.4版本之后，TIMESTAMP和DATETIME支持到微秒。 -
TIMESTAMP会根据系统时区进行转换，DATETIME则不会 - 存储范围的区别
-
TIMESTAMP存储范围：1970-01-01 00::00:01 to 2038-01-19 03:14:07 -
DATETIME的存储范围：1000-01-01 00:00:00 to 9999-12-31 23:59:59
-
- 一般使用
TIMESTAMP国际化 - 如存时间戳使用数字类型
BIGINT
字符串类型
字符串: char、varchar
文本: tinytext、text、mediumtext、longtext
| 类型 | 单位 | 最大 | 特性 |
|---|---|---|---|
| CHAR | 字符 | 最大为255字符 | 存储定长，容易造成空间的浪费 |
| VARCHAR | 字符 | 可以超过255个字符 | 存储变长，节省存储空间 |
| TEXT | 字节 | 总大小为65535字节，约为64KB | - |
- TEXT在MySQL内部大多存储格式为溢出页，效率不如CHAR
- Mysql默认为utf-8，那么在英文模式下1个字符=1个字节，在中文模式下1个字符=3个字节。
二进制(可用来存储图片、音乐等)
tinyblob、blob、mediumblob、longblob
InnoDB和MyISAM的区别
- InnoDB支持事务，MyISAM不支持。
- MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。
- InnoDB支持外键，MyISAM不支持。
- 从MySQL5.5.5以后，InnoDB是默认引擎。
- InnoDB不支持FULLTEXT类型的索引。
- InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。主要的是，当count()语句包含where条件时MyISAM也需要扫描整个表。
- 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。
- 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
- InnoDB支持行锁（某些情况下还是会锁整个表，如update table set a = 1 where user like '%lee%'）
MVCC
MVCC的实现，是通过保存数据在某个时间点的快照来实现的，根据事务开始时间的不同，每个事务对于同一张表，同一时刻看到的数据可能是不同的。
InnoDB的 MVCC ，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间）， 当然存储的并不是实际的时间值，而是系统版本号。
可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
在MVCC协议下，每个读操作会看到一个一致性的 snapshot，并且可以实现非阻塞的读。MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的。
- select：满足以下两个条件innodb会返回该行数据：（1）该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地。（2）该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
- insert：将新插入的行的创建版本号设置为当前系统的版本号。
- delete：将要删除的行的删除版本号设置为当前系统的版本号。
- update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。
其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。
由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。
通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。
MVCC只在READ COMMITED 和 REPEATABLE READ 两个隔离级别下工作。READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。
InnoDB间隙锁
Record lock
单条索引记录上加锁，InnoDB 的行锁是通过索引实现的，如没有索引，则锁住的不是记录行而是整个表。
Gap lock
间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
Next-key lock
Next-key lock 就是 Record lock + Gap lock 的组合，它锁定的是一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
间隙锁（Next-key lock）的条件
mysql的事务隔离级别是可重复读（RR，Repeatable Read），并且 innodb_locks_unsafe_for_binlog 参数设置为0
事务ACID
- 原子性（Atomicity）：不可分割的操作单元，事务中所有操作，要么全部成功；要么撤回到执行事务之前的状态
- 一致性（Consistency）：如果在执行事务之前数据库是一致的，那么在执行事务之后数据库也还是一致的；
- 隔离性（Isolation）：事务操作之间彼此独立和透明互不影响。事务独立运行。这通常使用锁来实现。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
- 持久性（Durability）：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。
delete drop truncate区别
truncate 和 delete只删除数据，不删除表结构 ,drop删除表结构，并且释放所占的空间。
删除数据的速度，一般来说: drop> truncate > delete
delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚
使用场合：
- 当你不再需要该表时，用 drop;
- 当你仍要保留该表，但要删除所有记录时， 用 truncate;
- 当你要删除部分记录时（always with a where clause), 用 delete.
Mysql explain
explain 用于查看 SQL 语句执行计划，其结果主要包含以下几个重要参数：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释。
select_type
表示查询中每个select子句的类型
- SIMPLE(简单SELECT,不使用UNION或子查询等)
- PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
- UNION(UNION中的第二个或后面的SELECT语句)
- DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
- UNION RESULT(UNION的结果)
- SUBQUERY(子查询中的第一个SELECT)
- DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
- DERIVED(派生表的SELECT, FROM子句的子查询)
- UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
type
表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
- ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
- index: Full Index Scan，index与ALL区别为index类型只遍历索引树
- range:只检索给定范围的行，使用一个索引来选择行
- ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
- eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
- const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
- NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
Key
key列显示MySQL实际决定使用的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
explain 总结
- EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- EXPLAIN不考虑各种Cache
- EXPLAIN不能显示MySQL在执行查询时所作的优化工作
- 部分统计信息是估算的，并非精确值
- EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。