• sql优化


    一 、排查sql性能问题的步骤:


    1:数据库总体的性能查询
    show 【session | global 】status like 'Com_%';session是默认值,表示当前连接。global:表示从上次启动到现在。
    Com xxx表示每个xxx语句执行的次数,我们通常比较关心的是以下几个统计参数:

    Com select:执行SELECT操作的次数,一次查询只累加1。

    Com insert:执行INSERT操作的次数,对于批量插入的INSERT操作,只累加一次。
    Com_ update:执行UPDATE操作的次数。
    Com delete:执行DELETE操作的次数。
    上述操作的累积同样也会反应在存储引擎上面:show 【session| global 】status like 'Com_%'
    其他参数查询:
    Connections:试图连接MySQL服务器的次数。
    Uptime:服务器工作时间。
    Slow_ queries:慢查询的次数。

    2:定位执行效率比较低的sql语句。
    a:查看慢查询的日志(但是在出现慢查询的时刻,日志还未记录完毕,不便查看)
    b:show processlist; 查看进程和锁表。
    可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操作进行优化。

    3:通过EXPLAIN分析低效SQL的执行计划。
    能查看sql访问的表,使用的索引,或者分区partitions 。
    explain select * from payment where customer_id =350G;
    explain partitions select * from customer_part where customer_id=130G;

    4:通过show profile分析SQL.(在mysql 5.7之后,mysql推荐使用performance schema 。)
    show profiles;
    show profile for query 4;

    5: MySQL 5.6提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划,帮助我们更好地理解优化器的行为。


    二、优化sql的方案:

    select 的优化:
    2:插入大量数据的时候,先删除索引,导完再把索引添加上。
    3:InnoDB的表按照主键顺序插入会比较快。
    4:先将自动提交修改为手动提交。(AUTOCOMMIT=0时比1时快)

    优化insert语句:
    1:多行数据最好一次性插入,这样还可以用INSERT DELAYED让数据放在内存队列中,快速插入。
    2:户当从一个文本文件装载一个表时,使用LOAD DATA 1NFILE。这通常比使用很多
    INSERT语句快20倍。

    优化ORDER B丫语句:
    通过索引直接返回的已经是有序数据,尽量减少额外的排序。
    以下可用:
    SELECT*FROM fiabname ORDER BY key_partl,key_part2;
    SELECT*FROM tabname WHERE key_partl=1 ORDER BY key_partl DESC, key_part2 DESC;
    SELE*FROM tabname ORDER BY key_partl oESC, key_part2 DESC;
    以下勿用:
    SELECT * FROM tabname ORDER BY key_partl DESC, key_part2 ASC:
    --order by的字段混合ASC和ESC
    SELECT * FROM tabname WHERE key2=constant ORDER BY keyl;
    一用于查询行的关键字与ORDER B丫中所使用的不相同
    SELECT*FROM tabname ORDER BY keyl, key2;
    一对不同的关键字使用ORDER BY

    优化GROUP BY语句:
    默认情况下,MySQL对所有GROUP BY coll,col2,…的字段进行排序。
    例如:select payment_date, sum(amount) from payment group by payment_date;
    优化之后:select payment_date, sum(amount) from payment group by payment_date order by null;禁止了排序。

    MySQL如何优化OR条件:
    对于含有OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引;
    如果没有索引.则应该考虑增加索引。 如果or前后的字段是符合索引,则索引会失效,因为mysql是分别查询之后再UNION在一起的。

    优化分页查询:
    "limit 1000,20" 前1000条记录都会被抛弃,查询和排序的代价非常高。
    先单独查询出分页记录对应的主键,再连表查询出主键加其他字段。(避免了全表扫描)
    优化前:select film_id, description from film order by title limit 50,5;
    优化后:select a.film_id, a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id=b.film_id;

    使用SQL提示:
    索引提示:select count(*) from rental use index (idx_rental date);
    索引忽略:select count(*) from rental ignore index (idx_rental date);
    强制索引:select * from rental force index (idx_fk_inventory_id) where inventory_id>1;


    常用sql技巧:

    2:总体上来说,子查询还是不如连接查询快。
    3:使用正则表达式
    4:巧用RAND()提取随机行:select * from category order by rand() limit 5;随机排序之后去前5条记录。

  • 相关阅读:
    裁剪圆形图片原理
    Scrollview回弹效果自定义控件
    Android热修复、插件化、组件化
    Android 6.0权限
    Android如何缩减APK包大小
    Android MVP理解
    Mat转化为IplImage类型的方法
    【QT】Qaction和触发函数建立连接的方法
    【QT】【OpenCv】初始配置以及测试功能
    【MATLAB】画信号频谱的子函数
  • 原文地址:https://www.cnblogs.com/domi22/p/8530475.html
Copyright © 2020-2023  润新知