MySQL的SQL优化

分手后的思念是犯贱 2022-02-27 21:18 175阅读 0赞

# 索引的类型： #

1.  聚集索引
    
     *  聚集索引决定数据再磁盘上的物理排序
     *  一个表只能有一个聚集索引
2.  非聚集索引
    
     *  非聚集索引并不决定数据再磁盘上的物理排序
     *  索引上只包含被建立索引的数据，以及一个行定位符 row-locator,这个行定位符，可以理解为一个指向对应物理地址的指针，通过该指针，可以找到行数据。

# **MySQL索引优化规则** #

1.  **前导模糊查询不能使用索引。**
    
        SELECT * FROM doc WHERE title LIKE '%XX';
    
    这个sql因为使用了前导模糊，所以无法使用索引。
    
        SELECT * FROM doc WHERE title LIKE 'XX%';
    
    这个sql使用了并没有前导模糊，是可以使用索引的,页面搜索严禁左模糊或全模糊，如果需要可以用搜索引擎来解决
2.  uninon, in, or都能够命中索引，建议使用 in。
    
     *  union : 能够命中索引.
    
        SELECT * FROM doc 
        WHERE status = 1 UNION ALL 
        (SELECT * FROM doc WHERE status = 2)
    
    > 直接告诉 MySQL 怎么做，MySQL 耗费的 CPU 最少，但是一般不这么写 SQL。
    
     *  in : 能够命中索引
        
            SELECT * FROM doc WHERE status IN (1,2);
        
        > 查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in
        > 
        > 在很多情况下都不被推荐，但如果是确定数量的情况下可以使用，如IN(0,3 , 4);
        > 
        > 通常情况下NOT EXISTS 的效率要高于NOT IN
     *  or : 新版的MySQL 能够命中索引。
        
            SELECT * FROM doc WHERE status = 1 or status = 2;
        
        > 查询优化耗费的 CPU 比 in 多，不建议频繁使用 or；
3.  负向条件插件不能使用索引，可以优化为 in 查询
    
     *  负向条件 : '!= ', ‘<>’. ‘not in’, 'not exists ', ‘not like’;
        
            select * from doc where status != 1 and status != 2
        
        可以优化为 in 查询，（共五种状态）
        
            SELECT * from doc where status in (0,3,4)
        
        一旦使用负向条件，数据库将选择全表扫描，而不再使用索引。
4.  联合索引最左前缀原则（又叫最左侧查询）
    
     *  如果再(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。
        
            SELECT uid,login_timme 
            FROM user 
            WHERE login_name = ? AND password=?
        
        对于上述查询，我们可以通过建立idx\_login(login\_name,passwd)的联合索引加快速度，因为业务上几乎没有passwd单独查询的需求u，但是有很多login\_name的但条件查询需求，所以可以建立(login\_name,passwd)的联合索引，而不是(passwd,login\_name)的顺序.
     *  建联合索引的时候，区分度最高的字段在最左边。
     *  如果建立了(a,b)联合索引，就不必再单独建立 a 索引。同理，如果建立了(a,b,c)联合索引，就不必再单独建立 a、(a,b) 索引。
     *  存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如 where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
     *  最左侧查询需求，并不是指 SQL 语句的 where 顺序要和联合索引一致。
        
        下面这个SQL也可以命中idx\_login(login\_name,passwd)这个索引.
        
            select uid ,login_time 
            from user 
            WHERE passwd = ? AND login_name=?
        
        WHERE后面的顺序不必和联合索引相同，但是强烈建议和联合索引的顺序一致，养成好习惯。
5.  范围列可以用到索引(联合索引必须时最左前缀)
    
     *  范围条件有: <, <= , > , > = ，between等.
     *  范围列可以用到索引(联合索引必须时最左前缀)，但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
     *  加入由联合索引(emp\_no,title,from\_date),那么下面的 SQL 中 emp\_no 可以用到索引，而 title 和 from\_date 则用不到索引：
        
            SELECT * FROM employees.titles 
            WHERE emp_no < '10010' 
            AND title = 'Senior Engineer'
            AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
6.  **把计算放到业务层而不是数据库层。**
    
     *  再字段上进行计算,进行函数运算都不能命中索引
        
        例如
        
            SELECT * FROM doc WHERE YEAR(create_time) <= '2016';
        
        即使create\_time上建立了索引，也会全表所秒，可优化为值计算，如下:
        
            SELECT * FROM doc WHERE create_time <= '2016-01-01';
     *  把计算放到业务层。
        
        这样做不仅可以节省数据库的CPU，还可以起到查询缓存优化效果。
        
        比如：
        
            SELECT * FROM doc WHERE date <= CURDATE()
        
        最好在业务层将一个固定的值(今天)直接传递过来,也就是如下：
        
            select * from doc where date < = '2019-3-23 00:00:00';
        
        优化后的 SQL 释放了数据库的 CPU 多次调用，传入的 SQL 相同，才可以利用查询缓存。
7.  强制类型转化会全表扫描
    
    如果 phone 字段时 varchar 类型，则下面的 SQL不能命中索引。
    
        SELECT * FROM user WHERE phone=13800001234
    
    可以将其优化为:
    
        SELECT * FROM user WHERE phone='13800001234'
8.  更新十分频繁、数据区分度不高的字段上不宜建立索引。
    
     *  更新会变更B + 树，更新频繁的字段建立索引会大大降低数据库性能.
     *  ‘性别’ 这种区分度不大的属性，建立索引没有意义，MySQL查询优化器会自动调用全表扫描.
     *  一般区分度在80% 以上的时候就可以建立索引，区分度可以用下面这个SQL来计算
        
            count(distinct(列名))/count(*)
9.  利用覆盖索引来进行查询操作，避免回表
    
    被查询的列，数据能从索引中取得，而不用通过定位符row-locator 再到 row 上获取，即"被查询列要被所建的索引覆盖",这样能够加速查询速度.
10. 如果由order by, group by 的场景，请注意利用索引的有序性.
    
     *  order by 最后的字段时组的索引的一部分，并且放在索引组合顺序的最后，避免出现 file\_sort 的情况，影响查询性能.
        
        例如：
        
            select * from doc WHERE a = ? and b = ? order by c;
        
        可以建立联合索引(a,b,c)；
     *  如果索引中由范围查找，那么索引有序性将无法利用
        
        例如：
        
            WHERE a > 10 ORDER BY b
        
        此时索引(a,b)无法排序.
        
        > tip: 首先应该考虑在WHERE 或 ORDER BY 设计的列上建立索引
11. 使用短索引(前缀索引)，来优化索引
    
     *  前缀索引：即用这一列的前多少个字段作为索引，比如有一列属性值为：abc,bac,cab，那么前缀索引为clo(1)，那么将会使用 a,b,c 作为索引内容，如果clo(2)作为索引，那么ab,ba,ca为索引内容，可以得知前缀索引过长会使索引体积变大，前缀索引过短会使区分度降低。
     *  前缀索引兼顾索引大小和查询速度，但是其缺点时不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引,很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
        
        例如：
        
            SELEC * FROM employees.employees 
            WHERE first_name='Eric'AND last_name='Anido';
        
        我们可以建立索引：(first\_name, last\_name(4))。
12. 建立索引的列，不允许为 null；
    
    单列索引不存 null 值，复合索引不存全为 null 的值，如果列允许为 null，可能会得到“不符合预期”的结果集，所以，请使用 not null 约束以及默认值。
    
    此外，如果建立索引的列上出现了 IS NULL或者 IS NOT NULL 子句的话，将会导致引擎放弃使用索引而改用全表扫描。
13. 利用延迟关联或者子查询优化超多分页场景.
    
    MySQL 并不是跳过offset行，而是取offset + N行，然后只返回最后的 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过阈值的页数进行SQL改写.
    
        select a.* from a, 
        (select id from a where id > 0 limit 100000,20 ) b 
        where a.id=b.id
14. 业务上具有唯一特性的字段，即使时多个字段的组合，也必须建成唯一索引.
    
    唯一索引会降低 insert 速度，但是对于提高查找速度时显然的，而且即使应用层的校验很完善，也有可能出现脏数据，必须用唯一索引确保。
15. 超过三个表最好不要 join。
    
    需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。
16. 如果明确知道只有一条结果返回，limit 1 能够提高效率。
    
    比如登陆校验的时候
    
        select * from user where login_name=? and pwd = ?
    
    可以优化为
    
        select * from user where login_name=? and pwd = ? limit 1;
    
    这是明确告诉数据库，让其查找到一条数据后立刻结束游标移动。
17. 单表索引不应该过大(以5个为界限)
18. 但索引字段书不应该过多(以5个位界限)，当超过五个时，索引已经无用了。
19. 创建索引时避免一下错误观念
    
    1.  索引越多越好，认为一个查询就需要一个索引–》错误原因：索引会影响到插入，删除数据的效率。
    2.  宁缺毋滥，认为索引会消耗空间，严重拖慢更新和新增速度----》根据上一条错误可以得出，我们只要不滥用索引，那么一般并不会超过五个索引，并不会**严重**拖慢更新和新增速度。
    3.  地址唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决-----》就算不说多线程下的情况，业务代码也不可能做到完美，索引唯一索引将是系统的最后一道底线。
    4.  过早优化， 在不了解系统的情况下就开始优化。—》改疯你。

# 其他常见的SQL优化 #

1.  应尽可能的避免更新 聚集索引数据列，因为 聚集索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新聚集索引数据列，那么需要考虑是否应将该索引建为 聚集索引。
2.  建议类型位数字型，尽可能不要用字符型。对于同类型选择，内存占用越小越好，如我们更喜欢选择varchar,而不是char;
3.  任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段。
4.  尽量避免大事务操作，提高系统并发能力。
5.  尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理