mysql查询过程优化--理论及实践过程总结

首先推荐一篇写的特别详细的帖子，感觉写的太好了。全看懂了，就不用看我下面的废话了。

https://blog.52ipc.top/archives/149.html

然后记录点自己解决的经验

正式开始写一下我的优化过程：

问题：MySQL查询count（）from( 括号里有七八个left join )，导致查询速度特别慢，结果大概是40s+

1、首先在做多表链接的过程中，应该始终铭记小表驱动大表的原则，也就是说我们连接的主表应该尽量选择数据量比较小的表，在想清楚这一点后

我开始给7、8张表的连接顺序进行调整，原则就是先连接小表、简单表（连接后查询结果总数据条数不会改变），后连接复杂表（连接后查询结果总数据条数会增加，会产生交叉乘积的现象）。经过表的顺续调整以后，查询速度突然变成了10s，可把我高兴坏了，可惜依然不符合生产环境要求。。。

2、然后查到了一些编写查询语句时，应该遵循一些优化原则，具体见下：

1.能写在on里的条件，不要写在where里，能写在where里的条件不写在having里
    先执行on    再执行where      最后执行haing，所以条件越靠前越有利于sql的执行。
2.能使用left join 或者right join 的不使用inner join 或者 cross join
    其实我们在inner join 或者cross join中使用on其实就是使用where，因为mysql的执行优化器最后都回将on解析成where
3.应该使用INNER JOIN代替WHERE子句，因为INNER JOIN可以减少返回的数据量。
4.应该避免在WHERE子句中使用函数，因为这会导致MySQL无法使用索引进行优化。

3. 再要继续优化就需要从慢查询入手了。

针对一个复杂的查询，如果表比较大，连接的表比较多，一般会导致查询时间比较长，这个时候先开慢查询看一下sql的具体的查询过程。

方法：explain + sql语句

首先看第一次慢查询结果

 

 当时对慢查询的参数还不太了解，故也没找到问题所在，其详细参数解释可以看置顶的帖子，也可以去B站看视频。也可以看这篇帖子下面那三个链接：https://www.cnblogs.com/cpl9412290130/p/10781717.html

（a）然后看上面慢查询的结果，可以看到查询type下与很多（All）这意味着在连接时要进行全表扫描，这个太低级了

　　所以这个时候就开始考虑给表增加索引，尽量使left join 后on 的条件字段是索引字段，where后的条件字段是索引字段，这样就可以通过索引进行　　　　       查找，从而避免全表扫描。type从优至劣的顺序： null > system > const > eq_ref > ref > range > index > all

 (b) 然后现在看extra里竟然还用了好几个distinct，简直是造孽啊

引用视频里的话： using Filesort:(九死一生)、 Using temporary:（十死无生）还有distinct 总之这几个能不用就不用，尽量去优化

然后就开始了修改sql语句之旅。终于在一个扬沙漫天的下午，把查询速度提升到了1.4s,虽然还有点慢，但至少能用了。

（c）主要提升速度的操作其实就是：修改表的连接顺序、添加索引、修改连接语句的查询过程（尽量不产生多余数据）

再附一张现在慢查询的结果图，做对比：

 

 

 

备注：sql count(*) 优化过程的其他方法，还可以参考：mysql count（*）关联多表怎么优化_博问_博客园 (cnblogs.com)

顺便感谢提出宝贵意见的各位博主。

最后是我自己关于慢查询的一点笔记（没学完，待补充）

 

-- 14 索引 04/11
 
'''
索引是一种数据结构--提高查找效率
排好序的快速查找数据结构
    影响：
        where 后的筛选条件
        order by 后的排列顺序
    BTREE:
    索引一般以索引文件的形式存储在磁盘上
    优势：
        提高数据检索的效率。降低数据库的IO成本
        通过索引列对数据进行排序，降低数据排序成本，降低CPU消耗
    劣势：
        实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
        虽然索引大大提高了查询速度，同时却会降低更新表的速度，
            如对表进行INSERT、UPDATE和DELETE因为更新表时，MySQL不仅要保存数据，
            还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

    分类：
        单值索引：一张表可以有多个单列索引（不超过5个）
        唯一索引：索引的值必须唯一，允许有控制
        复合索引：一个索引包含多个列

        语法：
            创建：
                CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
                ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))
            删除：
                DROP INDEX [indexName] ON mytable;
            查看：
                SHOW INDEX FROM table_name
            使用alter命令--有四种方式来添加数据表的索引:
                ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)该语句添加一个主键，这意味着索值必须是唯一的，且不能为NULL.
                ALTER TABLE bl_name ADD UNIQUE index_name (column_list): 这条语创建索引的值必须是-的(除了NULL外，NULL可能会出现多次).
                ALTER TABLE tbl_name ADD INDEX index_name(column_list): 添加普通索引，索引值可出现多次。
                ALTER TABLE tbl_name ADD FULLTEX(column_list):该语句指定了索引为 FULLTEXT，用于全文索素引
        索引结构：
            BTree索引
            Hash索引
            full-text全文索引
            R-Tree索引
    哪些情况需要创建索引：
        1.主键自动建立唯一索引
        2.频繁作为查询条件的字段应该创建索引
        3.查询中与其它表关联的字段，外键关系建立索引
        4. 频繁更新的字段不适合创建索引 因为每次更新不单单是更新了记录还会更新
        5.Where条件里用不到的字段不创建索引
        6.单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
        7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
        8.查询中统计或者分组的字段
    哪些情况不需要创建索引：
        1、表太小
        2、经常增删改的表
        3、重复太多（国籍-中国）

MySql Query Optimizer--mysql自带的优化器
MySQL常见瓶颈
    CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
    IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
Explain 
    使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SOL语句的。分析你的查询语句或是表结构的性能瓶颈
    能干嘛：
        表的读取顺序--id\table
        数据读取操作的操作类型-- select_type
        哪些索引可以使用---possible_keys:
        哪些索引被实际使用--keys
        表之间的引用
        每张表有多少行被优化器查询

    explain+sql语句
        字段解释:
            id
                1、id相同，执行顺序由上至下
                2、id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
                3、id相同，不同同时存在，id如果相同，可以认为是一组，从上往下顺序执行;在所有组中，id值越大，优先级越高，越先执行
            
            select_type
                SIMPLE:简单的select，不包含union和子查询
                PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
                SUBQUERY:在SELECT或WHERE列表中包含了子查询
                DERIVED:FROM列表中包含的子查询被标记为DERIVED(衍生)
                        mysql会递归执行这些子查询，把结果放在临时表中
                UNION:若第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为: DEVED
                UNION RESULT:

            table:

            type:
                 system ：表只有一行记录
                 const ：常出现主键索引或者唯一（where id =1）
                 eq_ref :唯一性索引扫描--只有一条匹配结果——对于每个索引建，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描
                 ref 至少达到这个要求 ：非唯一性扫描，返回匹配某个单独值的所有行
                 range  至少 给定范围（where 后 >,< in between）
                 index  :从索引中读取数据
                 all    :检索全表扫描--从硬盘中读取数据
            
            possible_keys:
                显示可能应用在该张表的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定查询实际使用
            key:
                查询中实际使用到的索引
                    null:没有用到该索引
                查询中若使用了覆盖索引：查的字段和所建索引的数量和名称一致
            
            key_len：表示索引中所使用的字节数，越小越好（条件越多，精度越高）

            ref:显示索引的哪一列被使用了，如果可能的话，是一个常数
                库.表.字段/const
        
            rows:
                根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
            
            extra:
                using Filesort:(九死一生)
                Using temporary:（十死无生）
                    使了用临时表保存中间结果,MyQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
                using index：
                    如果同时出现 using where，表明索引被用来执行索引键值的查找
                    如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
                    覆盖索引 (Covering Index),一说为索引覆盖。
                    理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
                using where：
                using join buffer
                distinct

                    
'''
-- 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这种数据结构以某种方式指向数据
-- 这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。