MySQL常见数据库引擎
| 特点 | MyISAM | InnoDB | MEMORY | MERGE |
|---|---|---|---|---|
| 存储限制 | 有 | 64TB | 有 | 没有 |
| 事务安全 | 支持 | |||
| 锁机制 | 表锁 | 行锁 | 表锁 | 表锁 |
| B树索引 | ||||
| 哈希索引 | ||||
| 全文索引 | ||||
| 集群索引 | ||||
| 数据可压缩 | ||||
| 空间使用 | 低 | 高 | ||
| 内存使用 | 低 | 高 | ||
| 批量插入速度 | 高 | 低 | ||
| 支持外键 | 支持 |
如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
索引
设计索引的原则
使用唯一索引
-
使用短索引（前缀索引）
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10))); 利用最左前缀
不要过度索引，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表内容时，索引必须进行更新，有时可能需要重构。
对于InnoDB存储引擎的表，记录默认按照一定的顺序保存，如果有明确定义主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，就按照唯一索引的顺序保存。如果既没有主键，又没有唯一索引，那么表会生成一个内部列，按照这个列的顺序来保存。按照主键或者内部列进行访问是最快的。
索引类型
存储方式区分
B树索引
HASH索引
MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。
根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快。
缺点：
- 散列计算是一个比较耗时的操作
- 不能使用HASH索引排序
- HASH索引只支持等值比较
- HASH索引不支持键的部分索引
逻辑区分
普通索引
唯一索引
主键索引
空间索引
全文索引
MYSQL分区
分区的优点
- 和单个磁盘或者文件系统分区相比，可以存储更多数据。
- 优化查询。在where子句包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时涉及SUM()和COUNT()这类聚集函数时，可以容易的在每个分区上并行处理，最终只需要汇总所有的结果。
- 对于已经过期和不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。
- 跨多个磁盘来分散查询数据，以获得更大的查询吞吐量。
分区类型
- Range分区
- List分区
- Hash分区
- key分区
SQL优化
通过show status命令了解各种SQL的执行频率
show [session/global] status
explain分析SQL语句
id:选择标识符
select_type：表示查询的类型
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref还会出现在join操作中。
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys:表示查询时，可能使用的索引 key:表示实际使用的索引 key_len:索引字段的长度 ref:列与索引的比较 rows:扫描出的行数(估算的行数) filtered:按表条件过滤的行百分比 Extra:执行情况的描述和说明
不会使用索引的情况
以%开头的LIKE查询
数据类型出现隐式转换
不满足最左原则不会使用复合索引
如果MYSQL估计使用索引比全表扫描更慢，则不适用索引
用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及到的索引都不会被使用
聚集索引和非聚集索引
正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
一个表只能有一个聚集索引。
主键不一定是聚集索引。
