开发者社区> 晓生寒> 正文

mysql优化

简介: sql优化 最近面试,在sql优化这块,一直都不是很熟悉,于是有了以下的总结,主要是在鲁玉成先生的笔记基础上,加入自己的理解。原文链接如下:http://www.cnblogs.com/luyucheng/p/6323477.
+关注继续查看

sql优化

最近面试,在sql优化这块,一直都不是很熟悉,于是有了以下的总结,主要是在鲁玉成先生的笔记基础上,加入自己的理解。原文链接如下:
http://www.cnblogs.com/luyucheng/p/6323477.html

1.sql语句的优化

a.使用limit对查询结果的记录进行限定
b.避免select * ,将需要查找的字段列出来
c.使用连接(join)来代替子查询
d.拆分大的delete或者insert语句

1.1子查询
1.1.1mysql的五种查询子句:
    where:条件查询
    group by:按照属性名指定的字段进行分组
        其他字段如果想出现在select中,则必须包含在聚合函数中:
        min(),max(),sum(),avg(),count()
    having:有group by才能有having,满足条件在group by的属性名中输出
    order by:按照属性名指定的字段进行排序。排序方式有“asc”,"desc"
    limit:限制结果集
1.1.2子查询:
    where 子查询
        查询每个类别下id最大的商品
        select goods_id,goods_name,cat_id,shop_price from goods 
            where goods_id In
            (select max(goods_id) from goods group by cat_id);
    from型子查询
        select goods_id,goods_name,cat_id,shop_price from
            (select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,goods_id desc)as temp 
            group by cat_id;
    exists子查询
    从类别表中取出其类别下有商品的类别(如果该类别下没有商品,则不取出)[使用exists子查询]
     SELECT c.cat_id,c.cat_name FROM category c WHERE EXISTS (SELECT 1 FROM goods g WHERE g.cat_id = c.cat_id);
1.2连接查询
1、全相乘,做笛卡儿积。(效率低)
2、左连接查询 left join ... on ... 
3、右连接查询 right join ... on ...
4、内连接查询 inner join ... on ...
5、全连接查询 full join ... on ...
6、联合查询 
    6.1、将两张表的数据合并查询出来
        SELECT id, content, user FROM comment UNION (SELECT id, msg AS content, user FROM feedback);
    6.2、union查询,列名不一致时,以第一条sql语句的列名对齐
    6.3、使用union会将重复的行过滤掉
    6.4、使用union all 查询所有,重复的行不会被过滤
        SELECT content,user FROM comment UNION ALL (SELECT msg, user FROM feedback);
    6.5、union查询,如果列数不相等,会报列数不相等错误
    6.6、union后的结果集还可以再筛选
        SELECT id,content,user FROM comment UNION ALL (SELECT id, msg, user FROM feedback) ORDER BY id DESC;
        注意order by放在排序放在内层sql是不起作用的,因为union查出来的结果会自动排序。但是内层,order by 和limit一起使用,就不会被优化掉
2.选择合适的数据类型
(1)使用可存下数据的最小的数据类型,整型 < date,time < char,varchar < blob
(2)使用简单的数据类型,整型比字符处理开销更小,因为字符串的比较更复杂。如,int类型存储时间类型,bigint类型转ip函数
(3)使用合理的字段属性长度,固定长度的表会更快。使用enum、char而不是varchar
(4)尽可能使用not null定义字段
(5)尽量少用text,非用不可最好分表
3.选择合适的索引列
(1)查询频繁的列,在where,group by,order by,on从句中出现的列
(2)where条件中<,<=,=,>,>=,between,in,以及like 字符串+通配符(%)出现的列
(3)长度小的列,索引字段越小越好,因为数据库的存储单位是页,一页中能存下的数据越多越好
(4)离散度大(不同的值多)的列,放在联合索引前面。查看离散度,通过统计不同的列值来实现,count越大,离散程度越高:
SELECT COUNT(DISTINCT column_name) FROM table_name;
4.使用命令分析
(1)SHOW查看状态
    1.显示状态信息
        mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';
        session(默认):取出当前窗口的执行
        global:从mysql启动到现在
    (a)查看查询次数(插入次数com_insert、修改次数com_insert、删除次数com_delete)
        mysql> SHOW STATUS LIKE 'com_select';
    (b)查看连接数(登录次数)
        mysql> SHOW STATUS LIKE 'connections';
    (c)数据库运行时间
        mysql> SHOW STATUS LIKE 'uptime';
    (d)查看慢查询次数
        mysql> SHOW STATUS LIKE 'slow_queries';
    (e)查看索引使用的情况:
        mysql> SHOW STATUS LIKE 'handler_read%';
            handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
            handler_read_rnd_next:这个值越高,说明查询低效。
    2.显示系统变量
        mysql> SHOW VARIABLES LIKE '%Variables_name%';
    3.显示InnoDB存储引擎的状态
        mysql> SHOW ENGINE INNODB STATUS;
    (2)EXPLAIN分析查询
        mysql> EXPLAIN SELECT column_name FROM table_name;
        explain查询sql执行计划,各列含义:
            table:表名;
            type:连接的类型
                -const:主键、索引;
                -eq_reg:主键、索引的范围查找;
                -ref:连接的查找(join)
                -range:索引的范围查找;
                -index:索引的扫描;
                -all:全表扫描;
            possible_keys:可能用到的索引;
            key:实际使用的索引;
            key_len:索引的长度,越短越好;
            ref:索引的哪一列被使用了,常数较好;
            rows:mysql认为必须检查的用来返回请求数据的行数;
            extra:using filesort、using temporary(常出现在使用order by时)时需要优化。
                -Using filesort  额外排序。看到这个的时候,查询就需要优化了
                -Using temporary 使用了临时表。看到这个的时候,也需要优化
    (3)PROFILING分析SQL语句
        1.开启profile。查看当前SQL执行时间
            mysql> SET PROFILING=ON; 
            mysql> SHOW profiles;
        2.查看所有用户的当前连接。包括执行状态、是否锁表等
            mysql> SHOW processlist;
    (4)PROCEDURE ANALYSE()取得建议
        通过分析select查询结果对现有的表的每一列给出优化的建议
        mysql> SELECT column_name FROM table_name PROCEDURE ANALYSE();
    (5)OPTIMIZE TABLE回收闲置的数据库空间
        mysql> OPTIMIZE TABLE table_name;
        对于MyISAM表,当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上,而非数据库)。
        对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。
        只需在批量删除数据行之后,或定期(每周一次或每月一次)进行一次数据表优化操作即可,只对那些特定的表运行。
    (6)REPAIR TABLE修复被破坏的表
           mysql> REPAIR TABLE table_name;
    (7)CHECK TABLE检查表是否有错误
        mysql> CHECK TABLE table_name;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
28350 0
mysql优化一
1.show global status      可以列出MySQL服务器运行各种状态值 2.show variables            查询MySQL服务器配置信息     一、慢查询 mysql> show variables like ‘%slow%‘; +-----...
566 0
mysql优化三
1. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。
791 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
20298 0
Mysql优化策略
Mysql优化策略总的来说:1、数据库设计和表创建时就要考虑性能 2、sql的编写需要注意优化 3、分区、分表、分库 设计表的时候: 1、字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null。
1700 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23542 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
20889 0
+关注
晓生寒
大数据开发与数据分析
11
文章
100
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载