sql优化及底层分析

mysql的数据引擎

主流数据引擎（MyISAM、InnoDB）的对比

性能下降SQL慢分析

1. 导致执行时间过长
2. 导致等待时间过长

原因

1. sql语句写的不合理
2. 没有建索引或者索引已经失效
3. 出现大量的关联查询和子查询，也就是很多join字段
4. 服务器调优及各个参数设置(缓冲\线程数等)

sql执行顺序

索引

索引（Index）是帮助MySQL高效获取数据排好序的数据结构，本质就是：索引是数据结构，目的在于提高查询效率，类比于字典。

本质：排好序的快速查找数据结构，BTree结构
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘中，我们平常说的索引，如果没有指明一般都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，唯一索引默认都是使用B+树索引。当然，除了B+树这种类型的索引之外，还有hash索引（hash index）等

优势

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

劣势

1. 实际也是一张表，保存了主键与索引的字段，也会占据磁盘的空间
2. 降低更新表的速度，执行update的操作的时候，也需要更新索引

索引的分类

1. 单指索引：即一个索引只包含单个列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，但允许有空值
3. 复合索引：即一个索引包含了多个列
4. 基本语法

索引的选择

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，处理关系建立索引
4. 频繁更新的字段不适合创建索引
   • 因为每次更新不单单是更新了记录还会更新索引
5. where条件里用不到的字段不创建索引
6. 单键/复合索引的选择问题？在高并发的下倾向于创建复合索引
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
8. 查询中统计或者分组字段

explain 分析sql语句

字段

1. id - id如果相同，可以认为是一组，从上往下顺序执行，在所有组中id值越大，优先级越高，越先执行。决定表的读取顺序和加载顺序

2. 

3. table 显示这一行的数据是关于哪张表的

4. type 显示查询使用了何种类型，从最好到最差依次是system>const>eq_ref>ref>range>index>ALL

5. possible_keys 显示可能应用在这张表中的索引,一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

6. key 实际使用的索引。如果为null则没有使用索引 ;查询中若使用了覆盖索引，则索引和查询的select字段重叠

7. key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

8. ref 显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值

9. rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

10. Extra 包含不适合在其他列中显示但十分重要的额外信息

    • 1.Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序
    • 2.Using temporary 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by
    • 3.USING index 表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；
      如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。