《高性能MySQL》第三版，第六章学习笔记

一、优化数据的访问

        查询性能低下最基本的原因是访问的数据太多。可以通过下列两个步骤进行分析：

1、确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但雨哦时候也可能访问了太多的列。

2、确认MySQL服务器是否在分析大量超过需要的数据行。

1、是否请求了不需要的数据

        有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃，这会带来额外的负担和网络开销。
        一些案例：

        1、查询不需要的记录
             如果在页面展示中只需要10条记录，可以把分页操作直接在后端进行，在查询语句后面
        添加limit，就只返回需要的10条数据，而不是把数据全部返回到前端，在前端进行分页操作，
        在丢弃其他不需要的数据。

        2、多表关联时返回全部列
              在关联查询是最终返回的列尽量不要使用“ * "代替，这会返回所有关联表的列。尽量返回
        需要的列

        3、总是取出全部的列
             与第二点类似，单表查询时在写” * “时，需要考虑是否真的需要全部列。

        4、重复查询相同的数据
             对于一些不经常更改的数据，可以在第一次查询出来后就缓存到前端(浏览器)或者后端，
        当在其他地方引用此数据时，可以直接到缓存中取就行，就不用再执行SQL查询、发送接口
        等不必要的重复操作。

2、是否在扫描额外的记录

        对于MySQL最简单的衡量查询开销的三个指标：响应时间、扫描的行数、返回的行数。这三个指标中没有那个能完美的衡量查询的开销。
        响应时间
        响应时间只是一个表面的值。它分为两部分：服务时间、排队时间。服务时间是指数据库处理这个查询真正花费的时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间(等待I/O操作，等待行锁...)。
        扫描的行数和返回的行数
        扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间。理想情况下扫描行数和返回的行数应该是相同的，但一般扫描行会大于返回的行。比如做关联查询时，服务器必须要扫描多行才能生成结果集中的一行。
        扫描的行数和访问类型
        MySQL有好几种访问方式可以查询并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行，也有些访问方式无需扫描就能返回结果。
        在explain中的type列就是访问类型。访问类型有：全表扫描、索引扫描、范围扫描、唯一索引扫描等。这些类型的速度从慢到快，扫描的行数从小到大。
        如果查询没有找到合适的访问类型，那么一般就是增加一个合适索引。

        

二、重构查询

1、一个复杂查询还是多个简单查询

        MySQL从设计上让链接和断开都很轻量级，在返回一个小的查询结果方面很高效。可以根据实际情况将一个复杂的查询分为几个小的查询。

2、切分查询

        对于大查询(数据量大)，可以将其切分为小查询。每个查询只查询大结果的一小部分。
        例子：删除数据，可以一年删一次，也可以一个月删一次。但是一年的数据量相较于一个月的数据量是非常庞大的，一次执行可能会占满系统资源，影响其他功能。对于每月运行的查询就没有那么大的影响，而且也可以让这一次性的压力再次分散开，使得对服务器的影响再次降低。

3、分解关联查询

        对于关联查询，可以对每一个表进行一次单表查询，然后将结果集在应用程序中关联。
        分解查询的优势：

• 让缓存的效率更高。许多应用程序可以方便的缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争
• 在应用层做关联，可以更容易对数据进行拆分，更容易做到高性能、可扩展
• 查询效率也可能会有所提升
• 可以减少冗余记录的查询

        

三、查询执行基础

        

        1、客户端发送一条查询给服务器
        2、服务器先检查查询缓存，如果命中缓存，则直接返回存储在缓存中的结果。否则进入下一
              阶段。
        3、服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
        4、MySQL根据优化器生成执行计划，调用存储引擎的API执行查询。
        5、将结果返回给客户端。

1、查询状态

        对于一个MySQL连接，任何时刻都有一个状态。该状态表示了MySQL当前在做什么。可以使用show full processlist命令来查询当前状态。
        状态解释：
                sleep：线程正在等待客户端发送新的请求。
                query：线程正在执行查询或正在将结果返回给客户端。
                locked：在服务层，该线程正在等待表锁。
                analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
                copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中。
                sorting result：线程正在对结果集进行排序。
                sending data：这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果
                        集，或者在向客户端返回数据。

 2、查询优化处理

        MySQL会将SQL转换成一个执行计划，再按照这个执行计划和存储引擎进行交互。这个包括多个子阶段：解析SQL、预处理、优化SQL执行计划。

 语法解析和预处理
        MySQL通过关键字将SQL语句进行解析，并生成一棵对应的”解析树“。MySQL解析器将使用MySQL语法规则验证和解析查询。
        预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如检查表和数据列是否存在，解析名字和别名是否有歧义。

查询优化器
        一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划。
        MySQL使用基于成本的优化器，他将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在执行查询语句后，可以使用show status like ”last_query_cost“来查看计算成本。得出的值是根据一些信息计算的出来的：每个表或索引的页面个数、索引基数、索引和数据行长度、索引分布情况。

        

四、查询优化器的局限性

1、关联子查询

        MySQL子查询的实现非常糟糕。MySQL对IN()列表中的选项有专门的优化策略，一般会认为MySQL会执行in里面的子查询，但MySQL不是这样做的。MySQL会将相关的外层表压到子查询中。

select name

from user

where id in (select id from part where user_name="abc")

上述语句在MySQL优化后会转化成下列方式

select name

from user

where exists (select * from part where user_name="abc" and user.id=part.id)

       子查询需要根据id来关联外部表part，因为需要id字段，所以MySQL认为无法先执行这个子查询。
        优化方式一：使用inner join进行关联查询
        优化方式二：直接使用优化后的SQL形式。使用exists。

2、UNION的限制

        有时MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
        例子：如果想UNION个子句能够根据LIMIT只取部分结果集，或者希望能够先排序在合并结果集，就需要在各子句中提前分页。

(select id,name from user order by name limit 0,10)
union

(select id,name from other_user order by name limit 0,10)

order by name 

limit 0,10    

3、在同一个表上查询和更新

        MySQL不允许对同一张表同时进行查询和更新。为了绕过这一限制见下列例子。

update user

set name = (select name from user where id=1)

修改后

update user

left join(select type,name from user where id=1) a using (type)

set user.name=a.name

五、优化特定类型的查询

1、优化count()查询

        count函数的作用：可以统计某个列值的数量；可以统计行数。统计列值时不会计算NULL值。统计行数时他会忽略列直接统计所有的行数。
        对于MyISAM的count速度非常快，但有个前提不能再where条件，因为这样无须实际计算表的行数。
        对于大量的数据，count需要访问大量数据才能获取精确数据，所以可以使用汇总表统计数据。

2、优化关联查询

        确保on或者using子句的列上有索引。在创建索引时候就要考虑关联顺序。当A和B用列C关联的时候，如果优化器关联的顺序是B、A，那就不需要再B表上建立索引。一般情况，只需要在关联顺序中的第二个表的相对应列上建立索引。（这么做的原因：加速关联操作、减少索引存储空间。第二张表作为被驱动表，可以使用索引快速定位对应数据，而且建立索引使用的存储空间没有驱动表中使用的空间大）
        在group by和order by中的列尽量使用一个表中的列。这样MySQL才可能使用索引去优化。

3、优化LIMIT分页

        尽可能使用索引覆盖扫描
        使用“延时关联”，先查询带有索引的列，得出结果在去关联主表查询其他需要字段。

select id,name,age from user order by name limit 100,10

转换后

select id,name,age from user

inner join (select id from user order by name limit 100,10) a using (id)       

        使用id过滤查询的数据       

        对于顺序id，在分页时可以在条件上加上id。比如返回了上一页最后一个id为10000，然后一页数据为20条，可以使用下列方式进行查询后一页的数据。

select id,name from user where id>10000 order by id limit  20

4、优化UNION查询

        可以将where、limit、order by等子句放到子查询中，便于优化器充分利用这些条件进行优化
        除非确实需要去重操作，否则一定使用UNION ALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT，这会导致整个临时表做数据唯一性检查。