mysql优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 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;
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
SQL 监控 关系型数据库
MySQL优化: CPU高 处理脚本 pt-kill脚本
MySQL优化: CPU高 处理脚本 pt-kill脚本
|
5天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
31 9
|
5天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
24 5
|
10天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
16 1
|
10天前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量(OFFSET)和限制(LIMIT)以及基于游标的分页方法,并分析了它们的优缺点。最后,提出了多种优化策略,包括合理创建索引、优化查询语句和使用数据缓存,帮助提升分页查询的性能和系统稳定性。
|
13天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万数据量的优化实录
【10月更文挑战第6天】 在现代互联网应用中,随着用户量的增加和业务逻辑的复杂化,数据量级迅速增长,这对后端数据库系统提出了严峻的挑战。尤其是当数据量达到百万级别时,传统的数据库解决方案往往会遇到性能瓶颈。本文将分享一次使用MySQL与Redis协同优化大规模数据统计的实战经验。
56 3
|
13天前
|
NoSQL 关系型数据库 BI
记录一次MySQL+Redis实现优化百万数据统计的方式
【10月更文挑战第13天】 在处理百万级数据的统计时,传统的单体数据库往往力不从心,这时结合使用MySQL和Redis可以显著提升性能。以下是一次实际优化案例的详细记录。
67 1
|
20天前
|
SQL 关系型数据库 MySQL
在日常工作中怎么做MySQL优化的?
在日常工作中怎么做MySQL优化的?
|
2月前
|
SQL 缓存 关系型数据库
MySQL高级篇——关联查询和子查询优化
左外连接:优先右表创建索引,连接字段类型要一致、内连接:驱动表由数据量和索引决定、 join语句原理、子查询优化:拆开查询或优化成连接查询
MySQL高级篇——关联查询和子查询优化
|
2月前
|
算法 关系型数据库 MySQL
MySQL高级篇——排序、分组、分页优化
排序优化建议、案例验证、范围查询时索引字段选择、filesort调优、双路排序和单路排序、分组优化、带排序的深分页优化
MySQL高级篇——排序、分组、分页优化