一、存储引擎

MySQL体系结构：

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称之为表类型。

我们来查询一下建表语句

  show create table message;

我们可以发现默认的存储引擎是InnoDB

查询当前数据库支持的存储引擎

  show engines;

创建表时，指定存储引擎

我们来试着创建张表，指定存储引擎为MyISAM

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎
特点：
1.DML操作遵循ACID模型，支持事务;
2.行级锁，提高并发访问性能;
3.支持外键FOREIGN KEY约束，保证数据的完整性和正确性

文件：
xxx.ibd：xxx代表的是表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表结构(frm,sdi)、数据和索引

逻辑存储结构：

一个区的大小为1M，一个页的大小为16K，一个区可以包含64个页

MyISAM 与 Memory

MyISAM：是MySQL早期默认支持的存储引擎
特点：
1.不支持事务，不支持外键
2.支持表锁，不支持行锁
3.访问速度快
文件：
xxx.sdi：存储表结构信息
xxx.MYD：存储数据
xxx.MYI：存储索引

Memory：Memory引擎的表数据是在内存中的，由于收到硬件问题、断电问题的影响，这些表只能作为临时表或缓存使用
特点：
1.内存存放
2.hash索引(默认)
文件：
xxx.sdi：存储表结构信息

存储引擎对比

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	5.6之后支持	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入	速度	低	高
支持外键	支持	-	-

存储引擎没有好坏之分，我们应该根据应用系统的特点选择合适的存储引擎，对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合

InnoDB： MySQL默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据一致性，数据除了查询和插入之外，还包含很多更新、删除操作，那么InnoDB是比较合适的选择

MyISAM： 如果应用是以读和插入操作为主，很少的更新和删除，并且对事务的完整性、并发性要求不高，那么MyISAM比较合适

Memory： 将数据保存在内存中，访问速度快，常用于临时表及缓存。缺陷是对表的大小有限制，太大的表无法缓存在内存中，无法保障数据的安全性

二、索引

索引(index)是帮助MySQL高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以实现高级查找算法，这种数据结构就是索引

select * from user where age = 45;

我们无索引的情况下，虽然我们现在已经找到45的数据，但是还会向下扫描，因为不知道下面是否还有数据，这种我们称之为全表扫描

而我们有了索引之后，查询效率会大幅度提高，但我们实际并不是使用的这种数据结果

索引的优缺点：
优点：
1.提高数据检索的效率，降低数据库IO的成本
2.通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：
1.索引列要占用空间
2.索引大大提高了查询效率，同时也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时效率降低

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要有以下几种：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分存储引擎都支持B+树索引
Hash索引	底层使用哈希表实现，只有精确匹配索引列查询才有效，不知道范围查询
R-tree(空间索引)	空间索引是MyISAM引擎中的一个特殊索引类型，主要用于地理空间数据类型
Full-text(全文索引)	是一种建立倒排索引，快速匹配文档的方式

索引	InnoDB	MyISAM	Memory
B+Tree索引支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree	不支持	支持	不支持
Full-text	5.6后支持	支持	不支持

我们日常所提及的索引，如果没有特殊指明，一般都是指B+树组织的索引

Btree

二叉树：当我们的元素是顺序时，回形成一个链表，查询速度大大降低。数据量特别大的情况下，层级太深，检索速度慢
红黑树：虽然不会出现链表的情况，但是数据量大的情况下，层级较深，检索速度慢

B-Tree(多路平衡查找树)：

B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能
B+Tree与B-Tree的区别：
1.非叶子节点只保存索引值，叶子节点保存了完整的索引和数据，因此它的查询时间固定为log(n)
2.叶子节点有指向下一个叶子节点的指针，叶子节点类似有一个链表
3.叶子节点保存了完整的数据以及有指针连接，B+树增加了区间访问性，提高了范围查询，而B树范围查询相对较差
4.B+树更适合外部存储。因为非叶子不存储数据只存索引

对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，同样保存大量数据，只能增加树的高度，性能下降

Hash索引

哈希索引就是采用一定的哈希算法，将键值换算成新的hash值，映射到对应的位置，然后存储在hash表上

如果两个或多个键值，映射到了同一位置，他们就产生了hash冲突，也称之为hash碰撞，可用链表来解决

特点：
1.Hash索引只能用于对等比较(=,in)，不支持范围查询(between，>，<…)
2.无法利用索引进行排序
3.查询效率高，通常只需要一次检索即可，效率高于B+Tree索引

存储引擎支持：
在MySQL中，支持hash索引的是Memory，而InnoDB具有适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下构建的

索引分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只有一个	PRIMARY
唯一索引	避免同一个表中某数据列中值重复	可以有多个	UNIQUE
常规索引	快速定位特定元素	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中，根据索引存储类型，可以分为以下两类：

分类	含义	特点
聚集索引	将索引与数据放一块，索引结构的叶子节点保存了行数据	必须有，而且只包含一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以包含多个

聚集索引规则：
1.如果存在主键，主键索引就是聚集索引
2.如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
3.如果没有主键，也没用合适的唯一索引，InnoDB会默认生成一个rowid作为隐藏的聚集索引

索引语法

创建索引：

  create [UNIQUE|FULLTEXT] index index_name on table_name(index_col_name,...);

我们的索引是可以加在单列或者多列的，也就是单列索引和多列索引

查看索引：

show index from table_name;

删除索引：

Drop index index_name on table_name;

SQL性能分析

SQL执行频率：MySQL客户端连接成功后，可执行show [session|global] status命令查看服务器状态信息，通过下述指令，可以查看当前数据库INSERT、UPDATE、DELETE、SELECT的访问频次：

 show [session|global] status 'Com_______';

我们通过上述命令可以确定那些操作执行的次数比较多，但我们并不知道是那些具体的语句比较慢，我们就可以使用以下操作。

慢查询日志： 慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位: 秒，默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置以下信息：

#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志时间为2s,SQL语句执行时间超过2s，就会被视为慢查询，进行记录
slow_query_time=2

配置完毕以后需要重启MySQL服务器

profile详情: show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作:

select @@have_profiling;

默认我们的profiling是关闭的，我们通过set语句开启profiling

set [session|global] profiling = 1;

可以通过以下执行查看SQL的执行耗时

#查看每一条SQL的耗时基本情况
show profiles;

#查看指定query_id的SQL语句各阶段的耗时情况
show profile for query query_id;

#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

然后我们可以指定query_id去查看详细信息

explain执行计划
explain或者desc命令获取MySQL如何执行select语句的信息，包括select执行过程中表如何连接以及连接的顺序

#直接在select之前加上关键字explain/desc
explain select 字段 from 表名 where 条件;

各字段含义：
id：select查询的序列号，表示查询中执行select语句操作表的顺序(id不同值越大先执行，id相同，从上到下执行)

select_type：表示select的类型，常见的有simple(简单表，不适应表连接或者子查询)、primary(主查询，外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(select/where之后包含了子查询)

type：表示连接类型，性能由好到差的连接类型：NULL、system、const、eq_ref、ref、range、index、all

possible_key：显示可能应用在这张表上的索引，一个或多个

key：实际用到的索引，如果为NULL，则没有用索引

key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，再不损失精度下，越短越好

rows：MySQL认为必须要执行查询的行数，在InnoDB引擎中，是一个估计值，可能不是准确的

filtered：表示返回结果的行数占需读取行数的百分比，fitered的值越大越好