MySQL索引，SQL优化一网打尽

2-3W字长文警告！！！！

索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

就拿这张图来说，如果查找3这个节点，如果没有索引就需要遍历整张表，但是有了索引，我们只需要遍历三次，然后根据这个节点找到对应的数据。

索引的优劣

优势：

• 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。

• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：

• 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

• 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引的结构

MySQL中的索引

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

2-3树

首先来介绍一种2-3树，这种结构是B树（多路查找树）一种特例。

直接给出定义：2-3树的数字代表一个节点有2到3个子树。它也满足二分搜索树的基本性质，但它不属于二分搜索树。

一颗2-3树或为一颗空树，或有以下节点组成：

• 2-节点，含有一个元素和两个子树（左右子树），左子树所有元素的值均小于它父节点，右子树所有元素的值均大于它父节点；
• 3-节点，还有两个元素和三个子树（左中右子树），左子树所有元素的值均小于它父节点，中子树所有元素的值都位于父节点两个元素之间，右子树所有元素的值均大于它父节点；
• 子树也是空树、2-节点或者3-节点；
• 没有元素相等的节点。

这里有很多都会搞混，2 3 指的是子树的个数，而不是当前节点元素的个数。

这样查找元素的时候，根据元素的大小来决定查找的方向。要判断一个元素是否存在，我们先将待查找元素和根节点比较，如果它和其中任意一个相等，那查找命中；否则根据比较的结果来选择查找的方向。

就比如说查找5这个元素，和根节点比较，然后发现，比根节点小，就到左子树再次比较。然后发现介于4 6之间，再到中子树查找，最后得到这个结果。

这就是简单的2-3树

BTREE 结构

BTree又叫多路平衡搜索树（注意平衡二字），一颗m叉的BTree特性如下：（光看这些特点压根不知道好处在哪）

• 树中每个节点最多包含m个孩子。
• 除根节点与叶子节点外，每个节点至少有[(m/2)]个孩子。
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由n个key与n+1个指针组成，其中[(m/2)-1] <= n <= m-1

以5叉BTree为例，key的数量：公式推导(m/2)-1 <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点分裂到父节点，两边节点分裂。

插入数据详解

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。（按字典排序）

1). 插入前4个字母 C N G A

2). 插入H，n>4，中间元素G字母向上分裂到新的节点(因为H大于G小于N，这个时候插入G处于中间)

3). 插入E，K，Q不需要分裂

4). 插入M，中间元素M字母向上分裂到父节点G

这里跳过几个插入，假设现在的结构如下：

5). 最后插入S，N P Q R节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

到此，该BTREE树就已经构建完成了， BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

B+TREE结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

• n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

• B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。

• 所有的非叶子节点都可以看作是key的索引部分。

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

这里需要区分一下，假设是B树，我们找一个节点，每一个节点都会存储对应的这个节点的数据，这个数据。所以这么一来，这个节点再同等大小情况下，B+树可以存放更多的KEY用来检索，这样也可以用来减少树高。

然后就是叶子节点，每个叶子节点都存放数据，并且这个叶子节点会引用下一个节点的地址，这样可以更加方便进行范围查找。

为什么数据库为什么使用B+树而不是B树

判断一种数据结构作为索引的优劣主要是看在查询过程中的磁盘IO渐进复杂度，一个好的索引应该是尽量减少磁盘IO操作次数

1. B树只适合随机检索，而B+树同时支持随机检索和顺序检索
2. B+树空间利用率更高。因为内部节点不存储数据，只存储索引值，因为相比较B树来说，B+树一个节点可以存储更多的索引值，从而使整颗B+树变得更矮，减少了I/O次数，磁盘读写代价更低，I/O读写次数是影响索引检索效率的最大因素
3. B+树的查询效率更加稳定。B树搜索有可能会在非叶子节点阶数，约靠近根节点的记录查找时间越短，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径相同，导致每一个关键字的查询效率相当
4. B树在在基于范围查询的操作上的性能没有B+树好，因为B+树的叶子节点使用了指针顺序的链接在一起，只要遍历叶子节点就可以实现整棵树的遍历，相比较B+树来说，由于B树的叶子节点是相互独立的，所以对于范围查询，需要从根节点再次出发查询，增加了磁盘I/O操作次数
5. 增删文件（节点）时，效率更高，因为B+树的叶子节点包含了所有关键字，并以有序的链表结构存储，这样可很好提高增删效率

MySQL中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针(有木有像双指针循环链表)，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的 B+Tree 索引结构示意图:

MySQL中的索引语法

索引分类

• 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引

• 唯一索引 ：索引列的值必须唯一，但允许有空值

• 复合索引 ：即一个索引包含多个列

索引语法

索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。

环境的准备：

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

• 创建索引语法：

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

例如给city这张表中的city_name这个字段创建索引：CREATE INDEX index_name_city ON city(city_name);

• 查看索引语法：

查看所有（整张表的索引）：SHOW INDEX FROM city;

• 删除索引

语法：DROP INDEX index_name_city ON city;

ALTER命令

1). alter  table  tb_name  add  primary  key(column_list); 
	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
	
2). alter  table  tb_name  add  unique index_name(column_list);
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);
	添加普通索引， 索引值可以出现多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	该语句指定了索引为FULLTEXT， 用于全文索引

索引设计的原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

• 对查询频次较高，且数据量比较大的表建立索引。
• 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
• 使用唯一索引，区分度越高，使用索引的效率越高。
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

MySQL存储引擎

体系结构概述

英文看不懂，我也看不明白，来个中文的：

然后你会发现这些生硬的翻译你还是看不懂。。。

整个MySQL Server由以下组成

• Connection Pool : 连接池组件
• Management Services & Utilities : 管理服务和工具组件
• SQL Interface : SQL接口组件
• Parser : 查询分析器组件
• Optimizer : 优化器组件
• Caches & Buffers : 缓冲池组件
• Pluggable Storage Engines : 存储引擎
• File System : 文件系统

整个系统大致分为四层：连接层（Connection Pool），服务层，引擎层，存储层。

连接层

最上面是一些客户端和连接服务，包含本地sock通信和大多数客户端/服务端工具实现的类。类似TCP/IP的通信。主要完成一些类似的连接处理，授权认证相关的安全方案。

在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。

所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。

如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取（读写），服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

存储层

数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

存储引擎概述

和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。

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

Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

可以通过指定 show engines ， 来查询当前数据库支持的存储引擎：

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

查看MySQL数据库默认的存储引擎，指令 ：show variables like '%storage_engine%'；

各种存储引擎特性

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁(适合高并发)	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

InnoDB

InnoDB存储引擎是MySQL的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点 ：

• 提供事务的控制，数据更加的安全可靠。
• 提供外键的约束。

[ PS ]：MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候， 要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

存储方式

InnoDB 存储表和索引有以下两种方式 ：

• 使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

• 使用多表空间存储，这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。

MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点：不支持事务

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：

• .frm (存储表定义)
• .MYD(MYData , 存储数据)
• .MYI(MYIndex , 存储索引)

MEMORY和MEREG

Memory

Memory存储引擎将表的数据存放在内存中。

每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。

MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引，但是服务一旦关闭，表中的数据就会丢失。

Mereg

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

以下是几种常用的存储引擎的使用环境：

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

  InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

• MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

• ~~MERGE：~~用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

SQL优化的步骤

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。

当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。

查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。

show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

比如查看当前Session所有统计参数的值：

• show status like 'Com_______'(后面有7个_)

• SHOW STATUS LIKE 'Innodb_rows_%';

我们通常比较关心的是以下几个统计参数：

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

• Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

• Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

定位低效率执行SQL

上面的操作可以查看每种SQL执行的次数，这样方便我们定向的直到哪些地方可以优化。这个定位低效率执行SQL就可以知道是哪些SQL有问题。

可以通过以下两种方式定位执行效率较低的 SQL 语句：

• 慢查询日志：通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

• show processlist：慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

每一列的意思：

1）id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

4）db列，显示这个进程目前连接的是哪个数据库

5）command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

6）time列，显示这个状态持续的时间，单位是秒

7）state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句&