浅谈MySQL中的子查询优化技巧

2016年07月27日 8 阅读

mysql的子查询的优化一直不是很友好，一直有受业界批评比较多,也是我在sql优化中遇到过最多的问题之一，你可以点击这里 ，这里来获得一些信息，mysql在处理子查询的时候，会将子查询改写,通常情况下，我们希望由内到外，也就是先完成子查询的结果，然后在用子查询来驱动外查询的表，完成查询，但是恰恰相反，子查询不会先被执行；今天希望通过介绍一些实际的案例来加深对mysql子查询的理解：案例：用户反馈数据库响应较慢，许多业务动更新被卡住；登录到数据库中观察，发现...

MySQL索引优化的实际案例分析

2016年07月27日 8 阅读

Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景，其优化原理也非常的简单，就是利用索引的有序性，优化器沿着索引的顺序扫描，在扫描到符合条件的M行数据后，停止扫描；看起来非常的简单，但是我经常看到很多性能较差的sql没有利用这个优化规律，下面将结合一些实际的案例来分析说明：案例一：一条sql执行非常的慢，执行时间为：root@test 02:00:44 SELECT * FROM test_order_des...

MySQL在关联复杂情况下所能做出的一些优化

2016年07月27日 8 阅读

昨天处理了一则复杂关联SQL的优化，这类SQL的优化往往考虑以下四点： 第一.查询所返回的结果集，通常查询返回的结果集很少，是有信心进行优化的； 第二.驱动表的选择至关重要，通过查看执行计划，可以看到优化器选择的驱动表,从执行计划中的rows可以大致反映出问题的所在； 第三.理清各表之间的关联关系，注意关联字段上是否有合适的索引； 第四.使用straight_join关键词来强制表之间的关联顺序，可以方便我们验证某些猜想；SQL:执行时间：mysql>...

MySQL的id关联和索引使用的实际优化案例

2016年07月27日 10 阅读

昨晚收到客服MM电话，一用户反馈数据库响应非常慢，手机收到load异常报警，登上主机后发现大量sql执行非常慢，有的执行时间超过了10s优化点一：SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>="2.00′ ORDER BY tran_id DESC LIMIT 10;表结构为：CREATE TABLE `game_shares_buy_list` （`tran_id` in...

对MySQL子查询的简单改写优化

2016年07月27日 10 阅读

使用过oracle或者其他关系数据库的DBA或者开发人员都有这样的经验，在子查询上都认为数据库已经做过优化，能够很好的选择驱动表执行，然后在把该经验移植到mysql数据库上，但是不幸的是，mysql在子查询的处理上有可能会让你大失所望，在我们的生产系统上就由于碰到了这个问题：select i_id, sum（i_sell） as i_sellfrom table_datawhere i_id in （select i_id from table_data ...

分析MySQL中优化distinct的技巧

2016年07月27日 10 阅读

有这样的一个需求：select count（distinct nick） from user_access_xx_xx;这条sql用于统计用户访问的uv，由于单表的数据量在10G以上，即使在user_access_xx_xx上加上nick的索引，通过查看执行计划，也为全索引扫描，sql在执行的时候，会对整个服务器带来抖动；root@db 09:00:12>select count（distinct nick） from user_access;+——...

通过实例认识MySQL中前缀索引的用法

2016年07月27日 7 阅读

今天在测试环境中加一个索引时候发现一警告root@test 07:57:52>alter table article drop index ind_article_url;Query OK, 144384 rows affected （16.29 sec）Records: 144384 Duplicates: 0 Warnings: 0root@test 07:58:40>alter table article add index ind_ar...

Suse Linux 10中MySql安装与配置步骤

2016年07月27日 8 阅读

安装环境：操作系统：suse Linux 10数据库：MySQL 5.0.22希望能认识更多高手，互相学习讨论技术！首先发泄一下再这，网上看了N多安装mysql的文章，大多大同小异，都不能很好的安装mysql；多多少少都有问题；以个人之见大多为一份原文，拷贝到各自的博客或一些网上，纯属盗窃啊！上周末开始筹划安装suse以及mysql，一直到今天下午才终于到了show databases；太晕了！现在我将我安装mysql的每一步骤都放在这，以共需者查看，也以...

简单分析MySQL中的primary key功能

2016年07月27日 9 阅读

在5.1.46中优化器在对primary key的选择上做了一点改动：Performance: While looking for the shortest index for a covering index scan, the optimizer did not consider the full row length for a clustered primary key, as in InnoDB. Secondary covering index...

在大数据情况下MySQL的一种简单分页优化方法

2016年07月27日 9 阅读

通常应用需要对表中的数据进行翻页，如果数据量很大，往往会带来性能上的问题：root@sns 07:16:25>select count（*） from reply_0004 where thread_id = 5616385 and deleted = 0;+———-+| count（*） |+———-+| 1236795 |+———-+1 row in set （0.44 sec）root@sns 07:16:30>select idfrom...

MySQL中对于not in和minus使用的优化

2016年07月27日 9 阅读

优化前：select count（t.id） from test t where t.status = 1and t.id not in （select distinct a.app_id from test2 a where a.type = 1and a.rule_id in （152, 153, 154）） 17:20:57 laojiu>@planPLAN_TABLE_OUTPUT———————————————————————————————...

使用sysbench来测试MySQL性能的详细教程

2016年07月27日 9 阅读

sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。目前sysbench代码托管在launchpad上，项目地址：https://launchpad.net/sysbench（原来的官网 http://sysbench.sourceforge.net 已经不可用），源码采用bazaar管理。一、 下载源码包安装epel包后以便安装bzr客户端：rpm -Uvh http://dl.fedorapr...

提高MySQL中InnoDB表BLOB列的存储效率的教程

2016年07月27日 9 阅读

首先，介绍下关于InnoDB引擎存储格式的几个要点：1、InnoDB可以选择使用共享表空间或者是独立表空间方式，建议使用独立表空间，便于管理、维护。启用 innodb_file_per_table 选项，5.5以后可以在线动态修改生效，并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间，早于5.5的版本，修改完这个选项后，需要重启才能生效；2、InnoDB的data page默认16KB，5.6版本以后，新增选项...

探究MySQL中索引和提交频率对InnoDB表写入速度的影响

2016年07月27日 17 阅读

本次，我们来看看索引、提交频率对InnoDB表写入速度的影响，了解有哪些需要注意的。先直接说几个结论吧：1、关于索引对写入速度的影响：a、如果有自增列做主键，相对完全没索引的情况，写入速度约提升 3.11%；b、如果有自增列做主键，并且二级索引，相对完全没索引的情况，写入速度约降低 27.37%；因此，InnoDB表最好总是有一个自增列做主键。2、关于提交频率对写入速度的影响（以表中只有自增列做主键的场景，一次写入数据30万行数据为例）：a、等待全部数据写...

关于MySQL中savepoint语句使用时所出现的错误

2016年07月27日 14 阅读

前几天帮同事解决一个案例，在主从复制环境下，从库上的MySQL版本号是5.5.5，遇到下面的错误：#其他非相关信息我都隐藏掉了 [（yejr@imysql.com）]> show slave status G; Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1064 Last_Error: Error "You have an error in your SQL syntax; che...

MySQL中slave监控的延迟情况分析

2016年07月27日 7 阅读

在MySQL复制环境中，我们通常只根据 Seconds_Behind_Master 的值来判断SLAVE的延迟。这么做大部分情况下尚可接受，但并不够准确，而应该考虑更多因素。首先，我们先看下SLAVE的状态：复制代码 代码如下:yejr@imysql.com [（none）]> show slave statusG*************************** 1. row ***************************Slave_I...

批量杀死MySQL连接的四种方法详解

2016年07月27日 7 阅读

方法一 通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令。复制代码 代码如下:mysql> select concat（"KILL ",id,";"） from information_schema.processlist where user="root";+------------------------+| concat（"KILL ",i...

MySQL中分页优化的实例详解

2016年07月27日 9 阅读

通常，我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL：SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;或者像下面这个不带任何条件的分页SQL：SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;一般而言，分页SQL的耗时随着 start 值的增加而急剧增加，我们来看下面...

MySQL下的RAND（）优化案例分析

2016年07月27日 6 阅读

众所周知，在MySQL中，如果直接 ORDER BY RAND（） 的话，效率非常差，因为会多次执行。事实上，如果等值查询也是用 RAND（） 的话也如此，我们先来看看下面这几个SQL的不同执行计划和执行耗时。首先，看下建表DDL，这是一个没有显式自增主键的InnoDB表：[yejr@imysql]> show create table t_innodb_randomG*************************** 1. row ******...

在MySQLDump中使用-w语句进行备份的方法

2016年07月27日 7 阅读

我们在用mysqldump备份数据时，有个选项是 –where / -w，可以指定备份条件，这个选项的解释是：-w, --where=nameDump only selected records. Quotes are mandatory我们可以做个测试，例如：mysqldump --single-transaction -w " id < 10000 " mydb mytable > mydump.sql这时候就可以备份出mytable表中 ...