SQL优化（一）

一、MySQL的查询过程

        请求过来后，在mysql中有一个网络交互模块，会监听网络请求，之后会调用连接管理模块，然后在调用连接进程模块，连接进程模块会有一个线程池，会开辟一个线程去处理这个请求，然后再调用用户模块，检测用户是否有权限访问数据库，如果有的话，会接着调用命令分发器，区分是sql语句调用不同的模块，（select：查询优化器，dml：表变更模块，ddl：表维护模块），分发给对应的模块后，接下来会访问控制模块，此时会判断是否有权限访问表，有的话才会调用储存引擎操作表；        

二、常用引擎介绍

        InnoDB、MyISAM

        两者的区别：

1- 存储文件的不同： 两个都有.frm表定义文件 Innodb的数据文件和索引文件都是放在一起的.ibd     MyISAM的是分开的，数据文件在.myd，索引文件在.myi；
2- InnoDB有表和行锁，MyISAM只有表锁；
3- InnoDB支持事务，MyISAM不支持事务；
4- InnoDB读写，MyISAM读比较多；
5- 都是 B+ Tree的索引结构；
6- 索引的实现方式不同：
    InnoDB的主键索引是聚簇索引，在通过主键索引的查询的时候，效率更快一些；他的辅助索引的值存放的是主键的值；
    MyISAM的主键索引个辅助索引差不多，都是用过索引文件去数据文件中查询相对应的数据；

三、索引

        如果没有索引，表中有多少条数据就需要查询几次； ​
        如果有索引，根据索引类型判断（B+ Tree的话就是树的高度）；

1、索引类型分类：

        B+Tree、Hash（MySQL内部使用的索引，不给用户使用）、FullTest、R-Tree；

B+Tree

        非叶子节点是不存放数据的 一叶约是16k 存放主键和指针，指针是指向我们叶子节点去寻找数据的主键8b，指针6b；

        一叶可以放1170个主键和指针，数据存放在叶子节点中，一个数据最大也就1k，一页就能放16个数据（16 * 1024 / 14）；

        假设数的高度有两层，最多可以放多少数据 1170 * 16 = 18720；
        假设数的高度有三层，最多可以放多少数据 1170 * 1170 * 16 = 21902400；

B-Tree （不是B减Tree！）

        非叶子节点是存放数据，会让树变得更高 执行的时候先做一次IO操作查询非叶子节点的数据，其次再通过主键和指针去找到下一个非叶子节点或叶子节点，此时又是一个IO操作，然后再IO操作获取里面的数据信息；

2、MySQL索引实现

        主索引（主键索引），辅助索引（不是主键，多个字段组成的索引）；

        根据主键索引查询的时候，InnoDB效率更高，索引文件和数据文件都存放在一起；

        只有InnoDB的主键索引才是聚簇索引，数据跟索引结构放在一起，可以直接通过索引查询带数据；

3、索引的利弊

好处：

        1- 提高表数据的检索效率；
        2- 如果排序的列是索引列，大大降低排序成本；
        3- 在分组操作中如果分组条件是索引列，也会提高效率；

坏处：

        需要维护树的结构，索引的问题：索引需要额外的维护成本；

        比如：有10个请求，有1个是索引查询，提升了0.2秒，但其他9个都是增删改，每次维护需要0.1秒，从而整体效率就下降了；

        索引的问题：索引需要额外的维护成本；

4、如何创建索引

        1- 较频繁的作为查询条件的字段应该创建索引；
        2- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；（性别，区分性不大）
        3- 更新非常频繁的字段不适合创建索引；
        4- 不会出现在WHERE 子句中的字段不该创建索引；(创建了索引但用不到）；

5、单值索引和联合索引

        单值索引即某一列作为索引； 组合索引即多列创建为一个索引；

 四、性能分析命令

1、explain

EXPLAIN SELECT * FROM 表名;

 2、profiling

select @@profiling;   #  查看状态；

set profiling = 1;  # 0 表示关闭状态，1 表示开启；

在连接关闭后，profiling 状态自动设置为关闭状态。

show profiles;         #  查看执行的 SQL 列表；

该命令执行之前，需要执行其他 SQL 语句才有记录。
可以帮我们观测出来每条SQL语句的执行时间，排查耗时久的原因