Sql优化

Sql优化策略

第一：主从复制、读写分离

数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

复制方式：

• 默认采用异步复制方式：
  从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

应用场景

• 读写分离：
  在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
• 高可用
  数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
• 负载均衡
  随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

主从复制的种类

• 一主多从，提高系统的读性能
  
• 多主一从 ：从5.7开始支持
  
  说明
  详细请参考：https://zhuanlan.zhihu.com/p/50597960

第二 Sql查询优化

1、 子查询的优化

通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据（使用distinct去重）。

• 优化前 ：耗时：21.18秒

select count(*) from (select 
            schedule_id, schedule_code ,resource_code, schedule_type, schedule.oper_id, schedule.oper_time, 
            start_date, end_date, start_time, end_time, img_id, video_id, display_time, 
            schedule_color, terrace_code, stb_types, district_codes, user_group_codes, 
            igroup_code, schedule_status, schedule_description, step_id, owner_id, aud.description, so.oper_name
        from schedule_record as schedule 
            left join auditing_desc_record as aud 
            on schedule.schedule_code = aud.code 
            and aud.is_last_auditing = 1
            left join system_oper as so
            on owner_id = so.oper_id
        where 1=1  and schedule_status = 7  
        order by schedule.schedule_code desc) myCount ;

• 优化后：耗时6.67秒

select 
            count(schedule_code)
        from schedule_record as schedule 
            left join auditing_desc_record as aud 
            on schedule.schedule_code = aud.code 
            and aud.is_last_auditing = 1
            left join system_oper as so
            on owner_id = so.oper_id
        where 1=1  and schedule_status = 7  
        order by schedule.schedule_code desc;

2 、优化group by查询

• 优化前：

explain select actor.first_name, actor.last_name, count(*) 
from skila.film_actor inner join sakila.actor using(actor_id) 
group by film_actor.actor_id;

• 优化后：

explain select actor.first_name, actor.last_name, c.cnt from sakila.actor 
inner join ( select actor_id, count(*) as cnt 
from sakila.film_actor group by actor_id ) as c using(actor_id);

3、长难Sql语句优化

• 优化前

//树形查询语句
//该SQL在优化前，执行30分钟才能出结果
select rownum, adn, zdn, 'cable'
  from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
          from AGGR_1 t
         where t.tdl_operation <> 2
           and exists (select 1
                  from CABLE_1 a
                 where a.tdl_operation <> 2
                   and a.tdl_dn = t.tdl_z_dn)
         start with exists (select 1
                       from RESOURCE_FACING_SERVICE1_1 b
                      where b.tdl_operation <> 2
                        and t.tdl_a_dn = b.tdl_dn)
        connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn)

• 优化后

(select
   tdl_a_dn, tdl_z_dn, tdl_operation
    from AGGR_1 t)
select rownum, adn, zdn, 'cable'
  from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
          from t
         where t.tdl_operation <> 2
           and exists (select 1 from a where a.tdl_dn = t.tdl_z_dn)
         start with exists (select 1 from b where t.tdl_a_dn = b.tdl_dn)
        connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn)

4、分解关联查询

• 分解前

select * from tag
	join tag_post on tag_post.tag.tag_id = tag.id
	join post on tag_post.post_id = post.id
	where tag.tag = "mysql";

• 分解后

select * from tag where tag = "mysql";
select * from tag_post where tag_id = 123;
select * from post where post.id in (123,456,789);

表面看，这种分解方式似乎并没有什么好处，一条sql语句能执行的，被拆成三条，而且查询结果一样。
实际上： 用分解关联查询的方式重构查询：

• 让缓存的效率更高；
• 减少锁的竞争
• 查询性能得到提升
• 减少冗余查询

第三 如何择合适的列建立索引

• 在where从句，group by从句，order by从句，on从句中出现的列
• 索引字段越小越好（原因：MySQL的每次读取都以页为单位，如果页中存储的数量越大，则一次IO操作获取的数据量就越大，查询的效率就越高）
• 离散度大的列放到联合索引的前面（离散度越大的列的可选择性越高，因为放在联全索引的前面效率就越好）

select * from payment where staff_id = 2 and customer_id = 584;

– 选择index(staff_id,customer_id)还是index(customer_id,staff_id)？ 由于customer_id的离散度更大，所以应该使用Index(customer_id,staff_id)

• 判断列的离散程度：

 select count ( distinct customer_id ), count ( distinct staff_id ) 
 from payment;  // 唯一值越多则离散度越大

注意：
若个索引包含了查询中的所有列，则称该索引为覆盖索引。当我们查询的执行频率非常高，并且查询中所包含的列比较少时，可使用覆盖索引对SQL进行优化。

索引的维护及优化—查找/去除 重复及冗余索引（使用工具更为方便）

 select a.table_schema as '数据名', a.table_name as '表名',
    a.index_name as '索引1', b.index_name as '索引2', 
    a.column_name as '重复列名' 
    from statistics a join statistics b 
    on a.table_schema=b.table_schema and a.table_name=b.table_name 
    and a.seq_in_index=b.seq_in_index
    and a.column_name=b.column_name 
    where a.seq_in_index=1 and a.index_name<>b.index_name

分析方法

explain分析

例如：

explain
select count(*) from (select 
            schedule_id, schedule_code ,resource_code, schedule_type, schedule.oper_id, schedule.oper_time, 
            start_date, end_date, start_time, end_time, img_id, video_id, display_time, 
            schedule_color, terrace_code, stb_types, district_codes, user_group_codes, 
            igroup_code, schedule_status, schedule_description, step_id, owner_id, aud.description, so.oper_name
        from schedule_record as schedule 
            left join auditing_desc_record as aud 
            on schedule.schedule_code = aud.code 
            and aud.is_last_auditing = 1
            left join system_oper as so
            on owner_id = so.oper_id
        where 1=1  and schedule_status = 7  
        order by schedule.schedule_code desc) myCount ;

参考：
https://www.cnblogs.com/zishengY/p/9070725.html
https://www.cnblogs.com/williamjie/p/9389530.html
https://download.csdn.net/download/linchangsheng/2642516?utm_medium=distribute.pc_relevant_t0.none-task-download-BlogCommendFromMachineLearnPai2-1.control&dist_request_id=&depth_1-utm_source=distribute.pc_relevant_t0.none-task-download-BlogCommendFromMachineLearnPai2-1.control