高性能MySQL第六章笔记

1.不要查询不需要的列，不要用select *（减少内存占用和网络带宽占用，减少表结构变化带来的影响）
2.EXPLAIN语句结果中的rows是预估的要扫描的行数，Extra：Using Where表示MySQL将通过WHERE条件来筛选存储引擎（注意是存储引擎）返回的记录
    一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏：
    在索引中使用WHERE条件来过滤，在存储引擎层
    使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层返回的，但无须再回表查询记录。
    从数据表中返回数据，然后过滤不满足条件的记录（Extra：Using Where）。这是在MySQL服务层完成，MySQL需要从数据表读出记录然后过滤。

3.使用多个简单查询来代替一个复杂查询
    3.1如每个月删除旧的数据，为了防止一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小的但重要的查询：
    将DELETE FROM message WHERE created < DATE_SUB(NOW,INTERVAL 3 MONTH);
    换为：
    rows_affected = 0
    do{
        rows_affected = do_query(
        "DELETE FROM message WHERE created < DATE_SUB(NOW,INTERVAL 3 MONTH)
        LIMIT 10000"
        )    
    }while rows_affected>0
    
    3.2分解关联查询
    优势：
    让缓存的效率更高;减少锁的竞争;在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展;减少冗余记录的查询，减少内存和网络的消耗。

4.查询执行的基础
    内容比较多，这里只记录几点：

    4.1 查询执行路径

    4.2 查询状态：可用SHOW FULL PROCESSLIST命令查看查询状态
    4.3 MySQL如何执行关联查询：嵌套循环
    4.4 MySQL的临时表是没有索引的，在编写复杂的子查询和关联查询的时候需要注意这一点

5.MySQL查询优化器的缺陷
    5.1 避免用IN(子查询)
    如SELECT * FROM sakila.film WHERE file_id IN(
        SELECT film_id FROM sakila.film_actor WHERE actor_id = 1;
        )
    我们会认为上面的查询会这样执行：
    --SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
    --Result:1,23,25,106,140,166,277,361,436,499,506,509,605,635,749,832,939,970,980
    SELECT * FROM sakila.film
    WHERE film_id
    IN(1,23,25,106,140,166,277,361,436,499,506,509,605,635,749,832,939,970,980)
    
    但MySQL实际会将查询改写成下面这样的关联子查询：
    SELECT * FROM sakila.film
    WHERE EXISTS(
        SELECT * FROM sakila.film_actor WHERE actor_id = 1
        AND film_actor.film_id = film.film_id);
    性能非常差

    可用如下语句改写：
    SELECT film.* FROM sakila.film
    INNER JOIN sakila.film_actor USING(film_id)
    WHERE actor_id = 1;‘
    另一个优化的方法是使用GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表
    注1：关联子查询：在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。
    注2：可用EXPLAIN EXTENDED来查看这个查询被改写成什么样子
    注3：前面提到关联子查询效率低，可以用join代替，但其实不是所有关联子查询效率都低，应当测试
    注4：可用SHOW STATUS 查看扫描的行数
    5.2 如果要取出UNION ALL的两个子句的前N条可以在子句里面和外面都加上LIMIT N;
    5.3 MIN()和MAX()：
    看这个语句：SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';
    first_name上没有索引所以会全表扫描，因为主键是按照从小到大排列，理论上扫描到第一个满足条件的记录就是了，可是MySQL却只会全表扫表，重写：
    SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
    WHERE first_name = 'PENELOPE' LIMIT 1;
6. 优化特定类型的查询    
    6.1 COUNT()或COUNT(*)统计行数，COUNT(exp)统计exp有值的结果数
    条件反转优化：
    将SELECT COUNT(*) FROM world.City WHERE ID > 5;
    优化为：
    SELECT （SELECT COUNT（*） FROM world.City）-COUNT(*)
    WHERE world.City WHERE ID < 5;
    近似值优化，有些业务不需要精确值，可以用EXPLAIN出来的rows近似值代替：
    
    6.2 优化关联查询：
    确保ON或者USING子句中的列上有索引
    确保GROUP BY和ORDER BY中的表达式只涉及一个表中的列，这样MySQL才能使用索引来优化这个过程
    
    6.3 优化子查询
    尽可能使用关联查询代替（MySQL5.6后可忽略这条）
    
    6.4优化LIMIT分页
    大偏移量时LIMIT效率很差，因为他会扫描很多然后丢弃他们
    SELECR film_id,description FROM sakila.film ORDER BY title LIMIT 50,5
    "延迟关联"优化为：
    SELECT film.film_id , film.description     
    FROM sakila.film
    INNER JOIN(
        SELECT film_id FROM sakila.film
        ORDER BY title LIMIT 50,5    
    )AS lim USING(film_id)

    另一种优化是，预先存储排名信息，然后优化为：
    SELECT film_id，description FROM sakila.film
    WHERE position BETWEEN 50 AND 54 ORDER BY position
    
    6.5 优化UNION查询
    MySQL总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION中都没法很好地使用，经常需要手动的将WHERE，LIMIT，ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器利用这些条件进行优化。
    除非确实需要消除重复的行，否则就一定要使用UNION ALL，如果没有ALL，MySQL就会给临时表加上DISTINCT选项，代价很高
7.其他注意点:
    7.1不要用前置%和前后双%，这会使索引失效
    7.2 索引的最左前缀匹配
    7.3 使用left join或not exists来优化not in（会使索引失效）
    7.4 避免隐式转换，这会使索引失效
    7.5 使用in代替or，in可利用索引    
    7.6 不要join太多表
    7.7 程序中使用预编译语句：防止SQL注入;同步地使用执行计划;一次解析，多次利用
    7.8 程序连接不同数据库使用不同的帐号，禁止跨库查询：降低业务耦合度;安全;为了以后的分库分表和数据库迁移
    7.9 禁止使用不含字段列表的INSERT语句
    7.10 减少同数据库的交互次数
    7.11 禁止用ORDER BY rand()进行随机排序：这会把表中所有符合条件的数据装载到内存中;推荐在程序中获取随机值到数据库中取相应的数据
    7.12 禁止在WHERE中对列进行函数转换和计算，这回导致索引失效