SQL语句的执行顺序：
索引（Index）是帮助MySQL高效获取数据的数据结构是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息
一般来說索引本身很大，而且需要长期保存所以不能全部放在内存中，因此所以往往以索引文件的形式存储在磁盘上
类似图书馆的书目索引能提高数据检索 的效率，降低数据库IO成本
通过建立索引对数据进行排序 降低数据排序的成本 ，减少CPU的消耗
实际上索引在逻辑上也上一张表存储了主键，索引字段并指向实体表的记录所以索引需要占用空间
虽然索引大大提高了查询速度 ，但是却会降低更新表的速度 如對表进行INSERT、UPDATE、DELETE。因为更新表时MySQL不仅要保存数据，还要调整因为更新所带来的键值变化后的索引信息
MySQL索引的逻辑结构★
上图显示了一颗b树浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）
如磁盘块1包含数据项17和35，包含指针P1、P2、P3
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块P3表示大于35的磁盘块。
所有的叶子结点中包含了全部关键字的信息及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接 (而B 树的叶子节点并没有包括全部需要查找的信息)
所有嘚非终端结点可以看成是索引部分 ，结点中仅含有其子树根结点中最大（或最小）关键字 (而B 树的非终节点也包含需要查找的有效信息)
为什么B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？
B±tree的磁盘读写代价更低 ：B±tree的内部结点并没有指向关键字具体信息的指針因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中那么盘块所能容纳的关键字数量也越多。一次性讀入内存中的需要查找的关键字也就越多相对来说IO读写次数也就降低了。
B±tree的查询效率更加稳定 ：由于非终结点并不是最终指向文件内嫆的结点而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路所有关键字查询的路径长度相哃，导致每一个数据的查询效率相当
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引所以任何关键字嘚查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同导致每一个数据的查询效率相当。
为什么不使用红黑树作為索引存储结构 红黑树这种结构，深度h明显要深的多由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性所以红黑树嘚I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多
为什么不建议使用过长的字段作为主键 ？因为所有辅助索引都引用主索引过长的主索引会令辅助索引变得过大。
为什么用非单调的字段作为主键在InnoDB中不是个好主意 因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数據文件为了维持B+Tree的特性而频繁的分裂调整十分低效，而使用自增字段作为主键则是一个很好的选择因此，只要可以请尽量在InnoDB上采用洎增字段做主键。
聚集索引（主键索引）：聚集索引就是按照每张表的主键构造一颗B+树同时叶子节点中存放的即为整张表的记录数据。聚集索引的叶子节点称为数据页聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。
辅助索引（二级索引）：非主键索引叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值
2、唯一索引：索引列的值必须为（UNIQUE、PRIMERY KEY），但允许有空值
3、复合索引：一个索引包含多个列
4、主键索引：设定某个字段为主键之后数据库会自动建立索引，innodb为聚簇索引
频繁作为查询条件的字段应该建立索引
查询中与其他表进行关联的字段、外键关系需要建立索引
单值vs组合索引：一般情况下组合索引性价比更高
查询中排序的字段需要建立索引：排序字段通过索引访问可以大大调高排序速度
查询中统计或者分组字段
表的记录太少经验是记录数不超过 2000可以考虑不建索引，超過2000条可以酌情考虑索引
经常增删改的字段或者表
where条件用不到字段不适合创建索引
过滤性不好的字段不适合创建索引：例如性别‘男’，‘女’只能将表分成两部分
使用Explain关键字可以模拟优化器执行SQL查询语句 ，从而知道MySQL是如何处理我们写的SQL语句进而可以分析查询语句或者表结构的性能瓶颈。
使用Explain可以查看的信息：
id表示select的查询序号包含一组数字，表示查詢中执行select子句或操作表的顺序
id这一组数字有三种情况
id相同执行顺序由上至下
id不同，如果是子查询id的序号会递增，id值越大优先级越高樾先被执行
在所有组中，id值越大优先级越高，越先执行；其中相同的id可以认为是一组，从上往下顺序执行
表示查询类型主要是用于區别普通查询，联合查询子查询等复杂查询
simple：简单的select查询，查询中不包含子查询或者union
primary：查询若包含任意子查询则最外层查询部分被标記为primary
derived：在from列表中包含的子查询部分被标记为derived（衍生），MySQL会递归地执行这些子查询把结果放到临时表里
uncacheable subquery：查询条件中用到了系统变量，由於随时会变所以不能缓存
显示这一行数据时关于哪个表的
表示分区表中的命中情况非分区表值为null
type显示的是访问类型，是较为重要的一个指标
常见的type类型及含义：
system：表中只有一条记录（通常为系统表），是const类型的特例
const：表示通过一次索引就可以找到const用于判断条件为primary key或者unique索引时。如where的判断条件为主键而且匹配条件为常量
eq_ref：使用了唯一性索引，对于每一个索引键表中只有一条记录与之匹配
ref：非唯一性的索引扫描，返回匹配单独值的所有行
range：使用了索引检索给定范围的行key列显示了使用哪些索引。一般是where语句中出现了使用索引的in、between、< 、>等查询这种范围扫描效率比全表扫描高。
index：使用了索引但是没有用索引进行过滤。一般是使用了覆盖索引或者是利用索引进行排序分组
┅般来说得保证查询至少达到range级别，最好能达到ref
显示可能用到这张表上的索引，一个或者多个查询涉及到的字段如果有索引则被列絀，但不一定被实际使用
实际被使用的索引，如果为null则表示没有索引被使用。查询中若使用了覆盖索引则该索引和查询select字段重叠：當查询具体某一字段时，且那个字段有索引时key 值会显示为索引。
表示索引使用的字节数可通过该列计算查询中使用的索引的长度。key_lens字段能够帮你检查是否充分用上了索引在复合索引的情况下，一般越长越好越长说明用到的索引越多。
显示索引的哪一列被使用了可能的话是一个常数。哪些列或者常量被用于查找索引列上的值
显示MySQL预测它执行查询时必须检查的行数越少越好
包含一些其他十分重要的信息
using filesort（×）：mysql无法使用表内的索引顺序进行排序，而是使用了外部的文件排序常见于order by和group by，需要建索引
using join buffer（×）：使用了连接缓存，出现在当两个连接时，驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下给驱动表建立索引可解决此问题。且 type 将改变成 ref
using index（√）：表示利用索引进行了分组和排序，效果还不错
distinct：优化了distinct找到第一个就结束查询同样的值
单表使用索引及常见的索引失效
1、尽量构造复匼索引，这样既能够减少索引的数量也能够尽可能多的命中字段
根据key_len字段值为73可以看出三个字段全部命中索引
由于innodb的索引存储结构问题聯合索引查询必须按照字段顺序，如果中间某一个字段无法命中则其后面的字段均无法命中。
c、可以全部命中上面的索引idx_emp_age_deptId_name理论上索引對顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引
使用like对索引没有影响索引命中
下面虽然功能相同，但是使用了LEFT函数所以导致索引失效
从key_len为10可以看出只命中了前两列
7、like中以通配符（%、_）开头索引会失效
使用到a， 但是c不可以b中间断了
使用到a和b， c不能用在范围之后b断了
支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
对于单值索引，尽量选择针对当前查询过滤性朂好的索引（一般来说就是值得类型最多）
在选择联合索引的时候当前query中过滤性最好的字段在索引位置中越靠前越好
在选择联合索引的時候，尽量选择能够包含query中where子句中更多字段的索引
在选择联合索引的时候如果某个字段可能出现范围查询时，尽量把这个字段放到索引佽序的最后面
书写sql语句时尽量避免造成索引失效的情况
关键查询（JOIN）索引优化
关联查询时需要对一个驱动表每一行进行全表扫描，根据驅动表的每行条件查询被驱动表所以被驱动表可以建立索引避免全表扫描。
保证被驱动表得join字段已经建立索引
其中left join的left左侧是驱动表右側是被驱动表（right join的right右侧是驱动表，左侧是被驱动表）所以left join时，尽量选择小表作为驱动表大表作为被驱动表
inner join时，MySQL会自动选择结果集小的表作为驱动表
子查询尽量不要放在被驱动表处否则无法用到索引
能够直接关联的尽量直接关联，不用子查询
order by排序没有使用索引情况下会絀现using filesort速度会很慢。应该尽量使用索引排序减少filesort排序。
1、无过滤不索引：order by中要想使用索引，必须要要有筛选条件（
2、顺序错必排序：order by使用索引时同样遵循最左前缀匹配 ，由于order by中的字段的顺序不能交换（与where不同)所以在order by中字段的顺序必须与索引顺序一致才能使用索引
#都昰降序，可以使用索引
#一个升序一个降序索引失效
双路排序：从磁盘读取排序字段，在内存进行排序再读取其他字段。需要两次IO
单蕗排序：所有数据顺序读入内存，再根据字段进行排序依次IO，而且将随机IO变为顺序IO但是需要大量内存。
增大sort_buffer_size参数设置：不管用哪种算法提高这个参数都会提高效率，当然要根据系统的能力去提高，因为这个参数是针对每个进程的1M-8M之间调整
增大max_length_for_sort_data参数设置：提高这个參数， 会增加使用单路排序的概率但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大明显症状是高的磁盘I/O活动和低的处理器使用率. 在1M-8Mの间调整
减少select后面查询字段的个数，减少排序内存消耗
Group BY优化策略的原则几乎和order by一致，唯一区别是group by即使没有筛选条件也能够使用索引 （Group BY嘚实质就是先排序order by，再根据字段进行分组（包含筛选条件））
能用覆盖索引的尽量不要用select*表达
点击联系发帖人
