温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
外表内向,内在开朗
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
select student_quality_id from STUDENT_QUALITY where mark_status=0 and batch_stauts in (2,3)结
果遇到一直找不到符合条件的student_quality_id ，后来才发现没有考虑到null值的问题，修改成 &select
student_quality_id from STUDENT_QUALITY where (mark_status=0 or
mark_status IS NULL) and batch_stauts in (2,3)
就OK了，下面是我从MySql手册上摘录的关于NULL值的处理。NULL值的概念是造成SQL的新手的混淆的普遍原因，他们经常认为NULL是和一个空字符串''的一样的东西。不是这样的！例如，下列语句是完全不同的：mysql& INSERT INTO my_table (phone) VALUES (NULL);mysql& INSERT INTO my_table (phone) VALUES ("");两个语句把值插入到phone列，但是第一个插入一个NULL值而第二个插入一个空字符串。第一个的含义可以认为是“电话号码不知道”，而第二个则可意味着“她没有电话”。&在SQL中，NULL值在于任何其他值甚至NULL值比较时总是假的（FALSE）。包含NULL的一个表达式总是产生一个NULL值，除非在包含在表达式中的运算符和函数的文档中指出。在下列例子，所有的列返回NULL：mysql& SELECT NULL,1+NULL,CONCAT('Invisible',NULL);如果你想要寻找值是NULL的列，你不能使用=NULL测试。下列语句不返回任何行，因为对任何表达式，expr = NULL是假的：mysql& SELECT * FROM my_table WHERE phone = NULL;要想寻找NULL值，你必须使用IS NULL测试。下例显示如何找出NULL电话号码和空的电话号码：mysql& SELECT * FROM my_table WHERE phone IS NULL;mysql& SELECT * FROM my_table WHERE phone = "";在MySQL中，就像很多其他的SQL服务器一样，你不能索引可以有NULL值的列。你必须声明这样的列为NOT NULL，而且，你不能插入NULL到索引的列中。当用LOAD DATA INFILE读取数据时，空列用''更新。如果你想要在一个列中有NULL值，你应该在文本文件中使用N。字面上的词'NULL'也可以在某些情形下使用。见7.16 LOAD DATA INFILE句法。当使用ORDER BY时，首先呈现NULL值。如果你用DESC以降序排序，NULL值最后显示。当使用GROUP BY时，所有的NULL值被认为是相等的。为了有助于NULL的处理，你能使用IS NULL和IS NOT NULL运算符和IFNULL()函数。对某些列类型，NULL值被特殊地处理。如果你将NULL插入表的第一个TIMESTAMP列，则插入当前的日期和时间。如果你将NULL插入一个AUTO_INCREMENT列，则插入顺序中的下一个数字。
阅读(2882)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
在LOFTER的更多文章
loftPermalink:'',
id:'fks_095068',
blogTitle:'mysql null 值查询问题',
blogAbstract:'我在开发公司内部的一个项目时遇到一个问题：select student_quality_id from STUDENT_QUALITY where mark_status=0 and batch_stauts in (2,3)结\n果遇到一直找不到符合条件的student_quality_id ，后来才发现没有考虑到null值的问题，修改成 &select \nstudent_quality_id from STUDENT_QUALITY where (mark_status=0',
blogTag:'mysql,null,值,phone,my_table',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:0,
publishTime:8,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'外表内向,内在开朗',
hmcon:'1',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}一次坑爹的MySQL问题查找 - 简书
一次坑爹的MySQL问题查找
清明假日前一天，我们进行了服务器升级，然后第二天，同事给我来了一个电话，网站访问异常卡顿，在MySQL里面发现大量的slow log。
于是我立刻放弃了休假的打算，连上了服务器，首先查看slow log，发现即使是单条记录的主键查询，也耗时将近1s多，这是完全不可能的事情。然后show processlist，发现大量的语句状态为statistics，根本不能快速的执行完成。
然后查看了机器的CPU，发现MySQL的CPU很高，但IO的负载异常的低，表明MySQL真的在处理很大量的请求。我们不停地show processlist，发现有些select语句查询，row examined竟然有几十万行之多，然后看语句，发现了很蛋疼的问题。类似如下:
SELECT * FROM tbl WHERE group = 123 and parent = 2
我们使用的是邻接表模型在MySQL里面存储树状结构，也就是每条记录需要存储父节点的ID，上面那条语句就是在一个group里面查询某个节点下面的子节点。
然后这个表里面的索引竟然只有group，至于原因，貌似是最开始设计的同学认为一个group里面的数据不可能很多，即使全examined一次也无所谓。可是偏偏，一个用户蛋疼的就往一个group里面放了几十万的数据，然后，任何一次查询都会极大地拉低整个MySQL的性能。经过这一次，再一次让我意识到，组里面的小盆友的MySQL知识还需要提升，如何用好索引，写过高效的查询语句真的不是一件简单的事情。
于是我们立刻更新了索引，然后慢查询马上就没有了。但没有了超时，我们仍然发现，MySQL的CPU异常的高，show processlist的时候出现了很多system lock的情况，表明写入并发量大，一直在争锁。通过日志发现，一个用户不停地往自己的group里面增加文件，而从升级之前到第二天下午，这个用户已经累计上传了50w的文件。
最开始我们怀疑是被攻击了，但通过日志，发现这个用户的文件都是很正常的文件，并且完全像是在正常使用的。但仍不能排除嫌疑，于是我们立刻升级了一台服务器，将LVS的流量全部切过去（幸好放假了，不然一台机器铁定顶不住），但令我们吃惊的是，记录的访问请求压根没有这个用户任何的信息，但是文件仍然在不停地新增。然后通过show processlist查看，MySQL的请求全部是另外几台机器发过来的，但另外几台机器现在已经完全没有流量了。
于是我们立刻想到了异步任务，会不会有某个异步任务死循环导致不停地插入，但监控rabbitmq，却发现仍然没有该用户的任何信息。这时候，一个同事看代码，突然发现，一个导致死循环的操作根本没扔到异步任务里面，而是直接在服务里面go了一个coroutine跑了。于是我们立刻将其他几台机器重启，然后MySQL正常了。
从晚上升级，到第二天真正发现问题并解决，因为我们的代码bug，导致了我们整个服务几乎不可用，不可不说是一个严重的教训。
MySQL索引设计不合理，导致极端情况下面拖垮了整个性能。
代码健壮性不足，对于可能引起死循环的应用没有做更多的检查处理。
日志缺失，很多偷懒不写日志，觉得没啥用，但偏偏遇到问题了才会有用。
统计缺失，没有详细的统计信息，用来监控整个服务。
没有更健壮的限频限容策略，虽然我们开启了，但因为程序内部bug，导致完全没用。
有些时候，只有做好了完全的准备，才能更好的应对对突发情况，毕竟我们是在做产品，要对用户负责，也要对自己的职业负责。
最后，这次的教训再一次说明，节假日前一天晚上升级，后果很严重。
一位爱好文学的资深程序开发工程师。热爱工作又极度顾家的有为社会青年。> MYSQL启用日志，和查看日志mysql有以下几种日志：错误日志：-log-err查询日志：-lo
MYSQL启用日志，和查看日志mysql有以下几种日志：错误日志：-log-err查询日志：-lo
mingdianchen & &
发布时间： & &
浏览：504 & &
回复：0 & &
悬赏：0.0希赛币
MYSQL启用日志，和查看日志
　　mysql有以下几种日志： 　 错误日志：　　 -log-err 　 查询日志：　　 -log 　 慢查询日志:　 -log-slow-queries 　 更新日志:　　 -log-update 　 二进制日志： -log-bin 是否启用了日志 mysql&show variables like 'log_%'; 怎样知道当前的日志 mysql& 顯示二進制日志數目 mysql& 看二进制日志文件用mysqlbinlog shell&mysqlbinlog mail-bin.000001 或者shell&mysqlbinlog mail-bin.000001 | tail 在配置文件中指定log的輸出位置. Windows：Windows 的配置文件为 my.ini，一般在 MySQL 的安装目录下或者 c:\Windows 下。 Linux：Linux 的配置文件为 my.cnf ，一般在 /etc 下。 在linux下： Sql代码 　 1. # 在[mysqld] 中輸入　　 2. #log　　 3. log-error=/usr/local/mysql/log/error.log　　 4. log=/usr/local/mysql/log/mysql.log　　 5. long_query_time=2　　 6. log-slow-queries= /usr/local/mysql/log/slowquery.log　# 在[mysqld] 中輸入 #log log-error=/usr/local/mysql/log/error.log log=/usr/local/mysql/log/mysql.log long_query_time=2 log-slow-queries= /usr/local/mysql/log/slowquery.log windows下: Sql代码 　 1. # 在[mysqld] 中輸入　　 2. #log　　 3. log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"　　 4. log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"　　 5. long_query_time=2　　 6. log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"　# 在[mysqld] 中輸入 #log log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log" log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log" long_query_time=2 log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log" 开启慢查询 long_query_time =2
--是指执行超过多久的sql会被log下来，这里是2秒 log-slow-queries= /usr/local/mysql/log/slowquery.log
--将查询返回较慢的语句进行记录 log-queries-not-using-indexes = nouseindex.log
--就是字面意思，log下来没有使用索引的query log=mylog.log
--对所有执行语句进行记录
本问题标题：
本问题地址：
温馨提示：本问题已经关闭，不能解答。
暂无合适的专家
&&&&&&&&&&&&&&&
希赛网 版权所有 & &&博客访问： 1097013
博文数量： 456
博客积分： 8399
博客等级： 中将
技术积分： 4336
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Mysql/postgreSQL
项目中一条SQL语句有严重的性能问题：
SELECT role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
from role_goods
LEFT JOIN role on role.id =role_goods.roleId
LEFT JOIN goods on goods.id=role_goods.goodsId
WHERE goodsId in (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品' ) ORDER BY number DESC;
一般我们认为mysql应该先执行where子句中的子查询：
SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品'
然后才执行外部的查询，但是mysql却将改SQL语句转换成了相关子查询，我们看到内部子句其实和外部的查询是不相关的！
其执行计划如下：
mysql> explain SELECT role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
&&&&-> from role_goods
&&&&-> LEFT JOIN role on role.id =role_goods.roleId
&&&&-> LEFT JOIN goods on goods.id=role_goods.goodsId
&&&&-> WHERE goodsId in (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品' ) ORDER BY number DESC;
+----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+
| 1 | PRIMARY | role_goods | ALL | NULL | NULL | NULL | NULL | 129104 | Using where; Using filesort |
| 1 | PRIMARY | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
| 1 | PRIMARY | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
| 2 | DEPENDENT SUBQUERY | shop_goods | index_subquery | idx_goodsId | idx_goodsId | 5 | func | 1 | Using where |
+----+--------------------+------------+----------------+---------------+-------------+---------+------------------------+--------+-----------------------------+
受影响的行: 0
时间: 52.469ms
运行时间长达52秒！
select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
from role_goods
LEFT JOIN role on role.id =role_goods.roleId
LEFT JOIN goods on goods.id=role_goods.goodsId
inner join (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品') a on role_goods.goodsId=a.goodsId
ORDER BY number DESC;
执行计划：
mysql> explain select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
&&&&-> from role_goods
&&&&-> LEFT JOIN role on role.id =role_goods.roleId
&&&&-> LEFT JOIN goods on goods.id=role_goods.goodsId
&&&&-> inner join (SELECT shop_goods.goodsId from shop_goods WHERE shop_goods.type!='药品') a on role_goods.goodsId=a.goodsId
&&&&-> ORDER BY number DESC;
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 289 | Using temporary; Using filesort |
| 1 | PRIMARY | role_goods | ref | goodsId | goodsId | 5 | a.goodsId | 465 | Using where |
| 1 | PRIMARY | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
| 1 | PRIMARY | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
| 2 | DERIVED | shop_goods | ALL | NULL | NULL | NULL | NULL | 351 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
运行时间：
受影响的行: 0
时间: 1.125ms
select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
from role_goods
LEFT JOIN role on role.id =role_goods.roleId
LEFT JOIN goods on goods.id=role_goods.goodsId
inner join shop_goods on role_goods.goodsId=shop_goods.goodsId and shop_goods.type!='药品'
ORDER BY number DESC
执行计划：
mysql> explain select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
&&&&-> from role_goods
&&&&-> LEFT JOIN role on role.id =role_goods.roleId
&&&&-> LEFT JOIN goods on goods.id=role_goods.goodsId
&&&&-> inner join shop_goods on role_goods.goodsId=shop_goods.goodsId and shop_goods.type!='药品'
&&&&-> ORDER BY number DESC;
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+
| 1 | SIMPLE | shop_goods | ALL | NULL | NULL | NULL | NULL | 351 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | role_goods | ref | goodsId | goodsId | 5 | xcb.shop_goods.goodsId | 164 | Using where |
| 1 | SIMPLE | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
| 1 | SIMPLE | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+------------------------+------+----------------------------------------------+
运行时间：
受影响的行: 0
时间: 1.171ms
继续优化：alter table shop_goods add index idx_goodsId_type(goodsId,type);
mysql> explain select role_goods.id,role_goods.number,roleId,role.`name` ,goods.`name` as '物品名称'
&&&&-> from role_goods
&&&&-> LEFT JOIN role on role.id =role_goods.roleId
&&&&-> LEFT JOIN goods on goods.id=role_goods.goodsId
&&&&-> inner join shop_goods on role_goods.goodsId=shop_goods.goodsId and shop_goods.type!='药品'
&&&&-> ORDER BY number DESC;
+----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | shop_goods | index | idx_goodsId_type | idx_goodsId_type | 773 | NULL | 239 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | role_goods | ref | goodsId | goodsId | 5 | xcb.shop_goods.goodsId | 184 | Using where |
| 1 | SIMPLE | role | eq_ref | PRIMARY | PRIMARY | 8 | xcb.role_goods.roleId | 1 | |
| 1 | SIMPLE | goods | eq_ref | PRIMARY,id | PRIMARY | 4 | xcb.role_goods.goodsId | 1 | |
+----+-------------+------------+--------+------------------+------------------+---------+------------------------+------+-----------------------------------------------------------+
shop_goods使用了覆盖索引。
优化1和优化2都将where中的子查询转换成了join，性能得到了极大的提高。
总结：mysql where子句中的子查询最好改写成使用join来处理。
阅读(6450) | 评论(1) | 转发(3) |
相关热门文章
给主人留下些什么吧！~~
博主写的不错，不过对&explain的描述更详细点就更好了，哈哈explain的东西没大看懂
请登录后评论。MySQL查询耗时过长有关问题 - MySQL当前位置:& &&&MySQL查询耗时过长有关问题MySQL查询耗时过长有关问题&&网友分享于：&&浏览：55次MySQL查询耗时过长问题一、表基本信息如下无任何外键及索引二、 查询执行时间问题1. 执行语句 SELECT Count(MESSAGE_ID) FROM blog_message 耗时9.484s， 记录结果为 180525条记录2. 执行语句查询最后一条记录 SELECT *
FROM blog_message WHERE MESSAGE_ID='' 耗时0.031s3. 执行语句查询第一条记录 SELECT *
FROM blog_message WHERE MESSAGE_ID='' 耗时不到 0.000s4. 执行语句查询中间记录 SELECT *
FROM blog_message WHERE MESSAGE_ID='' 耗时0.031s问题，总共的数据也才十八万条而已，为什么count的时候耗时将近十秒，而如果在navicat中直接查询表信息的话一下子就可以统计出来总共有多少条记录了。求原因及改进方案刚才重新执行了一下，正常情况下查询速度大概是11秒。执行了一次analyze，速度提升到9秒多。再执行了一次优化，报告无法优化，自动改成 analyze+recreate ，速度提升到8秒左右------解决方案--------------------
主键会自动创建索引的，所以这张表上MESSAGE_ID做为主键，MYSQL已经创建了这个字段的唯一索引。 &SELECT Count(MESSAGE_ID) FROM blog_message
是全表/全索引遍历。 所以会花时间比较长。navicat中直接查询表信息的话一下子就可以统计出来总共有多少条记录了。这个是通过 information_schema.tables 中的记录得到的信息，并不一定准确，你可以直接 select * from
information_schema.tables
也会很快得到相关信息。
------解决方案--------------------
探讨主键会自动创建索引的，所以这张表上MESSAGE_ID做为主键，MYSQL已经创建了这个字段的唯一索引。 SELECT Count(MESSAGE_ID) FROM blog_message
是全表/全索引遍历。 所以会花时间比较长。navicat中直接查询表信息的话一下子就可以统计出来总共有多少条记录了。这个是通过 information_schema.tables 中的……
12345678910
12345678910
12345678910 上一篇：下一篇：文章评论相关解决方案 12345678910 Copyright & &&版权所有