MySQL常见数据库引擎

特点	MyISAM	InnoDB	MEMORY	MERGE
存储限制	有	64TB	有	没有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁
B树索引
哈希索引
全文索引
集群索引
数据可压缩
空间使用	低	高
内存使用	低	高
批量插入速度	高	低
支持外键		支持

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

索引

设计索引的原则

1. 使用唯一索引

2. 使用短索引（前缀索引）

   CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
   

3. 利用最左前缀

4. 不要过度索引，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表内容时，索引必须进行更新，有时可能需要重构。

5. 对于InnoDB存储引擎的表，记录默认按照一定的顺序保存，如果有明确定义主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，就按照唯一索引的顺序保存。如果既没有主键，又没有唯一索引，那么表会生成一个内部列，按照这个列的顺序来保存。按照主键或者内部列进行访问是最快的。

索引类型

存储方式区分

B树索引

HASH索引

MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快。

缺点：

1. 散列计算是一个比较耗时的操作
2. 不能使用HASH索引排序
3. HASH索引只支持等值比较
4. 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:执行情况的描述和说明

不会使用索引的情况

1. 以%开头的LIKE查询

2. 数据类型出现隐式转换

3. 不满足最左原则不会使用复合索引

4. 如果MYSQL估计使用索引比全表扫描更慢，则不适用索引

5. 用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及到的索引都不会被使用

聚集索引和非聚集索引

正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

一个表只能有一个聚集索引。

主键不一定是聚集索引。

参考：https://blog.csdn.net/riemann_/article/details/90324846