sql优化

1.概述

1.1性能下降的表现

        执行时间长

        等待时间长

1.2性能下降的原因

        sql语句写的差（索引无法使用，索引失效）

        系统配置不合理

        数据库表结构设计不合理

        系统架构问题

        硬件差

1.3通用的优化方案

        

        sql优化，索引优化，表结构设计合理，，系统架构优化，系统配置合理，硬件好

2.索引原理 

2.1 索引，io概念

        索引：一种数据结构，可以大大提高mysql查询效率

        I/O：查找数据

        读取数据优化，最大的优化即减少I/O

2.2 索引的底层实现

        mysql的底层数据结构主要基于Hash，B+tree；

2.2.1底层数据库结构分析

        二叉树又称二叉查找树，每个节点包含键值和指向数据物理地址的指针，左子树的键值小于右子树。

        二叉树的高度称为度（degree），节点的数据存储个数，树越高，I/O次数就多。

        数据库的中数据按页存储，每页存储的数据量默认为16KB（主键索引8b，指针域6，假设数据大小为1KB，则节点存储元素个数约为（16*1024/（6+8+1024））15个，1000w的数据量则树高约为6。

        一般使用I/O次数评价索引结构的优劣

2.2.1.2 BTree

        多路平衡搜索树（自平衡多叉查找树）

        叶子节点具有相同的深度

        叶子节点中的key从左到右依次递增

        节点中直接存储数据

        非叶子节点有指针域，叶子节点则无指针域

2.2.1.3 B+Tree（BTree的变体，基本与BTree相同）

        只在叶子节点中存储数据，增大叶子节点的元素存储个数，且通过双向链表提高查询效率

        非叶子节点只保存索引，指针域相关信息，，进一步降低树的高度

        将根节点和已加载的叶子节点进行缓存，进一步提高查询效率

        2.2.1.4 Hash（用的少）

        有点：单行查询快

        缺点：无法进行排序，只能用于等值查找，存在哈希冲突

2.2.2 索引的实现

2.2.2.1 存储引擎的概念

        存储引擎：数据库中存储数据的技术以及配套的相关功能（curd，索引创建等，运用了插拔式的设计思想）

                MyiSAM:不支持事务，不支持分布式事务强一致性一致，不支持回滚点，不支持外键约束，支持索引缓存，索引实现为非聚簇索引（数据），数据和索引不在一起，存在大量的随机IO，因为要根据索引获取数据的磁盘位，如何再去获取数据（回表查询，回表查询过多会降低查询性能，回表查询如何避免？通过联合索引进行索引覆盖）

                innoDB:支持事务，支持分布式事务强一致性一致，支持回滚点，支持索引缓存，索引实现为聚簇索引，数据和索引聚合在一起，存在大量的顺序I/O，可提高查询效率，但也会导致存储数据的文件过大。在生产环境中，基于InnoDB构建索引的表如果存在复杂查询操作，数据量达到600~800w就要考虑优化。

2.3 索引的分类

        主键索引、普通索引、唯一索引、联合索引（组合索引—联合主键索引、联合唯一索引、联合普通索引）、全文索引（fulltext）

2.4 索引的操作

2.5 联合索引 —  最左匹配原则

        为什么order by 、group by走索引查询会加快？

        — 联合主键索引构建原则决定的

2.6 索引的优劣势

2.6.1 索引的优势

        大大提高查询效率

2.6.2索引的劣势

        占用空间、增加维护成本

2.7 索引字段的选择

2.7.1 适合作为索引的字段

        字段辨识度高，唯一性高（75%左右）

        唯一性：select count（distincte name）/count（name）from emp   越接近1，则唯一性越高

       频繁作为条件查询的字段优先选择，where on

        order by，group by 后的字段 可考虑加索引

        对于回表查询，可构建联合索引（唯一性强）

        多表查询中，关联多张表的字段可加索引

2.7.2 不适合作为索引的字段

        记录少的字段

        经常进行增删改的字段

        字段特别大

        条件查询用不到

        辨识度、唯一性低

2.8 索引失效的原因

        条件查询没有用到索引

        对所有字段进行了处理（算数运算，函数运算，类型转换等）

        左侧模糊匹配查询

        频繁进行增删操作

        or关键字有可能让索引失效

        联合索引不遵循最左匹配原则

        查询的列为空

3.SQL性能优化

3.1 问题

如何定位性能差的sql语句？

        用户反馈进行层层追踪

        利用可视化工具定位（例如druid提供的可视化工具可定位接口的调用，sql语句的执行时间等）

        开发人员做一个aop进行记录

        开启 mysql的慢查询日志

3.2 慢查询日志

        慢查询日志是mysql中用来记录响应时间超过阀值的sql语句的日志

日志操作

        查看是否开启慢查询日志（global）(只对当前数据库生效，并且重启数据库后失效)

        show variables like '%slow_query_log%'

配置永久生效(修改配置文件)

生产环境不建议一直打开，影响性能。测试的时候或者线上排查可以打开

        slow_query_log=1

        slow_query_log_file='...'

查看慢日志的阀值

        show variables like 'long_query_time'

开启慢日志

        set (grobal) long_query_time=1

        设置慢日志文件位置

        set global slow_query_log_file='...'

        设置慢查询的阀值(默认10s)

        show variables like 'long_query_time'

        查询当前系统有多少条慢查询记录

        show gloable status like '%slow_queries%'

        模拟慢查询sql

        select slepp(4)

3.1.2 Explain（执行计划）

3.1.2.1 概念作用

        explain：模拟优化器执行sql语句

        作用：分析sql语句和表结构性能

        如：查看表的读取顺序、数据读取操作的操作类型、查看哪些索引可以被使用、查看哪些索引被实际引用、查看表之间的引用、查看每张表有多少行被执行

3.1.2.1 参数详解

        ① id — 表的执行顺序

        id相同 从上到下执行

        id不同 id越大，优先级越高，优先被执行

        ②select_type — 查询类型

        ③table — 表

        ④type — 访问类型（要求：至少达到range级别）

                好——>差

                system > const > rq_ref > ref > range > index > all

        system: 单表单行

        const：走primary或者unique索引进行等值常量查询，查询一次获取单条记录

        eq_ref:走parimary或者unique索引进行等职查询，表中只有一条数据与之匹配

        ref:走非唯一索引进行等值查询，表中可能有一条或多条数据与之匹配

        range:走索引进行范围查询

        index:走所有索引查询

        all:全表查询，没有走索引

        ⑤possible_key  可能用到的索引

        ⑥key 实际调用的索引

        ⑦key_len  key的字节长度

        ⑧ref 引用索引

        ⑨rows 查询的行数

        ⑩filtered 满足查询结果的数量比例

        extra

        Using filesort    排序没有走索引直接获取，而是先经过条件查询，在对查询结果进行排序

        Using temporary    创建临时表用以保存中间结果

        impossible where  条件永不成立