SQL优化基础知识

一、观察SQL运行情况及定位慢SQL-使用alibaba druid

盘古数据库druid监控开启说明：

注意：监控仅限于测试或者开发调试阶段使用，正式部署需要关闭，开启监控后会对性能有一定影响。

在配置中心

第一：找到 application-prod.properties文件将文件 openMonitoring 属性修改true

第二：找到数据库配置文件configdb.properties将属性filter改为stat即为开启数据监控

第三：重启tomcat服务

第四：登录http://ip:7000/***/druid/ 用户名：admin 密码：****

可以在该页面观察并发现慢SQL，从而可以进行SQL分析优化，解决问题；

二、分析低效SQL

2.1 通过EXPLAIN分析低效sql执行计划

 

select_type: 标识SELECT的类型，常见取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等；

table：输出结果集的表

rows：需要扫描的行数

Extra：看到Using temporary 代表使用了临时表

           Using Filesort 证明使用了系效率的排序

type：标识MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如下（从上至下，性能有最差到最好）：

（1）type=ALL，全表扫描

（2）type=index，索引全扫描

（3）type=range，索引范围扫描，常见于<、>、between等操作符

（4）type=ref，使用非唯一索引扫描，返回匹配某个单独值的记录行

（5）type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配

2.2 存在索引但不能使用索引的情况

（1）列类型是字符串，查询条件未加引号

（2）未使用该列作为查询条件

（3）使用like时通配符在前

（4）在查询条件中使用OR，但是OR前后的条件列并不是全部有索引

（5）对索引列进行函数运算

（6）联合索引ABC问题，匹配最左前缀，即查询条件只包含了BC的时候，无法使用联合索引

三、常见SQL优化

3.1 优化ORDER BY语句

（1）MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

        如果order by的条件不在索引列上，就会产生Using filesort。

        在使用order by时select尽量不要使用*，而是只查询自己需要的字段

（2）where条件和order by使用相同的索引

（3) order by的字段都是升序或者都是降序

（4）order by多个字段时，建立联合索引，并order by后面字段的顺序跟联合索引顺序一致

3.2 优化GROUP BY语句

（1）要尽量避免临时表，或者减少临时表数据行数；

       如果GROUP BY 的列没有索引,产生临时表.
　   如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
　   如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
　   如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
　   如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
　   如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.

（2）在无法避免临时表情况下，要尽量学会用子查询将group by和where逻辑执行的表尽量少；

       举例：上百万级数据

       优化前：300秒

 

  优化后：0.28秒

（3）mysql会默认给group by后面的分组字段进行排序，若不需要排序，可在后面加上order by null来避免掉排序的性能消耗；

3.3 多使用连接（JOIN）来替代子查询，JOIN的效率更高，在符合一定规范情况下，JOIN的操作不会使得mysql产生临时表

3.4 如何优化OR条件

   对OR条件左右所有列都要添加索引，才能使得索引生效

3.5 优化分页查询

    （1）limit的性能限制

        limit语句的查询时间与起始记录的位置成正比

        mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用

    （2）优化limit

        利用覆盖索引，即select的所有关键字列都是索引列

        实际应用其实可以灵活为拆成子查询形式，如下例：

       优化前：select * from product limit 866613, 20；

       优化后：SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id;

       

        也可以利用order by快速定位分页起始位置

       优化前：select id,title from collect limit 90000,10;

       优化后：select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

       如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键

       select id from collect where vtype=1 limit 90000,10;    增加复合索引：search(vtype,id)

3.6 避免复杂sql，学会拆分sql

可以将排序与查询详细信息操作分成两条sql语句，有where条件和排序的sql，select列只查询必要主键，遵循上面的优化策略完成排序sql；

再select详细信息，where条件用in的方式查询包含分组排序后的主键的详细信息排序

举例：

优化前：

 优化后：

四、达到一定数量或者满足一定条件，通过拆分提高表的访问效率

4.1 垂直拆分

把主码和一些列放到一个表上，把主码和另外的列放到另一个表中

4.2水平拆分

根据一列或多列数据的值把数据行放到两个独立的表中