MySQL 幻读分析

要理解MySQL幻读问题,就要先搞清楚InnoDB的锁的机制和隔离级别。

MySQL InnoDB事务的隔离级别有四级,默认是“可重复读”(REPEATABLE READ)。
– 未提交读(READ UNCOMMITTED)。另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据(脏读)。
– 提交读(READ COMMITTED)。本事务读取到的是最新的数据(其他事务提交后的)。问题是,在同一个事务里,前后两次相同的SELECT会读到不同的结果(不重复读)。
– 可重复读(REPEATABLE READ)。在同一个事务里,SELECT的结果是事务开始时时间点的状态,因此,同样的SELECT操作读到的结果会是一致的。但是,会有幻读现象(稍后解释)。
– 串行化(SERIALIZABLE)。读操作会隐式获取共享锁,可以保证不同事务间的互斥。

四个级别逐渐增强,每个级别解决一个问题。

  • 脏读,最容易理解。另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据。
  • 不重复读。解决了脏读后,会遇到,同一个事务执行过程中,另外一个事务提交了新数据,因此本事务先后两次读到的数据结果会不一致。
  • 幻读。解决了不重复读,保证了同一个事务里,查询的结果都是事务开始时的状态(一致性)。但是,如果另一个事务同时提交了新数据,本事务再更新时,就会“惊奇的”发现了这些新数据,貌似之前读到的数据是“鬼影”一样的幻觉。
    下面开始实验,首先看你数据库的隔离级别
    75F8E42D-4898-487F-B253-34BB58259294
    下面开始重复读实验
    1E1AD02F-E846-48C3-AEED-EC6687FEDAA5
    可以看到,左边的事物,查不到ID为5的行,却插不进去,因为这个事物开始的时候,ID为5是不存在的。这就产生了幻读。
    怎么解决呢?
    3E007E0A-8380-4901-ABBE-58AA99F10BCA
    用了for update,这样右边的插入就卡住了,需要等待左边的提交成功后才行。
    但是这种会锁表,不知道有没有更好的方法。
    参考资料
    http://tech.meituan.com/innodb-lock.html

MySQL慢查询分析

最近遇见一个 MySQL 的慢查问题,于是排查了下,这里把相关的过程做个总结。
====== 定位原因 ======
我首先查看了 MySQL 的慢查询日志,发现有这样一条 query 耗时非常长(大概在 1 秒多),而且扫描的行数很大(10 多万条数据,差不多是全表了):

    SELECT * FROM tgdemand_demand t1
    WHERE
      (
        t1.id IN
        (
          SELECT t2.demand_id
          FROM tgdemand_job t2
          WHERE (t2.state = 'working' AND t2.wangwang = 'abc')
        )
        AND
        NOT (t1.state = 'needConfirm')
      )
    ORDER BY t1.create_date DESC

这个查询不是很复杂,首先执行一个子查询,取到任务的状态(state)是 ‘working’ 并且任务的关联人(wangwang)是’abc’的所有需求 id(这个设计师进行中的任务对应的需求 id),然后再到主表 tgdemand_demand 中带入刚才的 id 集合,查询出需求状态(state)不是 ‘needConfirm’ 的所有需求,最后进行一个排序。
按道理子查询筛选出 id 后到主表过滤是直接使用到主键,应该是很快的啊。而且,我检查了子查询的 tgdemand_job 表的索引,where中用到的查询条件都已经增加了索引。怎么会这样呢?于是,我对这个 query 执行了一个 explain(输出 sql 语句的执行计划),看看 MySQL 的执行计划是怎样的。输出如下:
我们看到,第一行是 t1 表,type 是 ALL(全表扫描),rows(影响行数)是 157089,没有用到任何索引;第二行是 t2 表,用到了索引。和我之前理解的执行顺序完全不一样!
为什么 MySQL 不是先执行子查询,而是对 t1 表进行了全表扫描呢?我们仔细看第二行的 select_type,发现它的值是 DEPENDENT_SUBQUERY,意思是这个子查询的查询方式依赖外层的查询。这是什么意思?
实际上,MySQL 对于这种子查询会进行改写,上面的 SQL 会被改写成下面的形式:

SELECT * FROM tgdemand_demand t1 WHERE EXISTS (
  SELECT * FROM tgdemand_job t2 WHERE t1.id = t2.demand_id AND (t2.state = 'working' AND t2.wangwang = 'abc')
) AND NOT (t1.state = 'needConfirm')
ORDER BY t1.create_date DESC;

这表示,SQL 会去扫描 tgdemand_demand 表的所有数据,每条数据再传入到子查询中与表 tgdemand_job 进行关联,执行子查询,子查询根本不会先执行,而且子查询会执行 157089 次(外层表的记录数量)。还好我们的子查询加了必要的索引,不然结果会更加惨不忍睹。


既然子查询会被改写,那最简单的解决方案就是不用子查询,将内层获取需求 id 的 SQL 单独拿出来执行,取到结果后再执行一条 SQL 去获取实际的数据。大概像这样(下面的语句是不合法的,只是示意):

ids = SELECT t2.demand_id
FROM tgdemand_job t2
WHERE (t2.state = 'working' AND t2.wangwang = 'abc');
SELECT * FROM tgdemand_demand t1
WHERE
  (
    t1.id IN ids
    AND
    NOT (t1.state = 'needConfirm')
  )
ORDER BY t1.create_date DESC;

实际上,我们也可以对 SQL 进行改写来解决问题:

select * from tgdemand_demand t1, (select t.demand_id from tgdemand_job t where t.state = 'working' and t.wangwang = 'abc') t2
where t1.id=t2.demand_id and not (t1.state = 'needConfirm')
order by t1.create_date DESC

也可以用Join


SELECT t1.* FROM tgdemand_demand t1 INNER JOIN tgdemand_job t2 ON t1.id = t2.demand_id AND t2.state = 'working' AND t2.wangwang = 'abc' WHERE NOT (t1.state = 'needConfirm') ORDER BY t1.create_date DESC

MySQL修改表存储引擎

MySQL作为最常用的数据库,经常遇到各种各样的问题。今天要说的就是表存储引擎的修改。有三种方式,列表如下。

1.真接修改。在数据多的时候比较慢,而且在修改时会影响读取性能。my_table是操作的表,innoDB是新的存储引擎。

[code]ALTER TABLE my_table ENGINE=InnoDB[/code]

2.导出,导入。这个比较容易操作,直接把导出来的sql文件给改了,然后再导回去。用mysqldump ,
3.创建,插入。这个比第一种速度快, 安全性比第二种高,推荐。分2步操作
a.创建表,先创建一个和要操作表一样的表,然后更改存储引擎为目标引擎。
[code]CREATE TABLE my_tmp_table  LIKE my_table;[/code]
[code]ALTER TABLE my_tmp_table ENGINE=InnoDB;[/code]
b.插入。为了安全和速度,最好加上事务,并限制id(主键)范围。
[code]INSERT INTO my_tmp_table  SELECT *  FROM my_table;[/code]
修改数据库字符集:
[code]ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE …];[/code]
把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集:
[code]ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE …][/code]
如:[code]ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;[/code]
只是修改表的默认字符集:
[code]
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE…];
如:ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
[/code]

修改字段的字符集:
[code]ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE …];
如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;[/code]
查看数据库编码:
[code]SHOW CREATE DATABASE db_name;[/code]
查看表编码:
[code]SHOW CREATE TABLE tbl_name;[/code]
查看字段编码:
[code]SHOW FULL COLUMNS FROM tbl_name;[/code]

MySQL行锁表锁

在调用存储过程中,就会涉及到表锁,行锁这一概念:所谓区别:有索引的时候就是行锁,没有索引的时候就是表索。innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的。
表锁演示(无索引)
Session1:

set autocommit=0;
select * from innodb_test;

+——+————-+
| id   | name        |
+——+————-+
|    1 | woshiceshi  |
|    2 | woshiceshi2 |
|    3 | woshiceshi3 |
+——+————-+

 select * from innodb_test where id = 2 for update;

+——+————+
| id   | name       |
+——+————+
|    2 | woshiceshi2 |
+——+————+
Session2:

 update innodb_test set name='sjis' where id = 1 ;

处于等待状态….
再回到session1 commit以后,session2就出来结果了(锁定了8秒,过了8秒左右才去session1提交)。

update innodb_test set name='sjis' where id = 1 ;
Query OK, 1 row affected (8.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

实验结果是:我在session1的for update 操作看似只锁定ID为2的行其实锁定了全表,以至于后面session2的对ID为1的行update 需要等待Session1锁的释放。

行锁演示(索引为ID)
Session1:

alter table innodb_test add index idx_id(id);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
select * from innodb_test where id = 2 for update;

+——+————+
| id   | name       |
+——+————+
|    2 | woshiceshi2 |
+——+————+
Session2:

update innodb_test set name='wohaishiceshi' where id = 1 ;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
select * from innodb_test where id = 1;

+——+—————+
| id   | name          |
+——+—————+
|    1 | wohaishiceshi |
+——+—————+
1 row in set (0.00 sec)

实验结果:这次的锁定是锁定的行,所以没有被锁定的行(ID不为2的行)可以进行update..

安装MySQLdb 出错的解决

[code]

pip install mysql-python

[/code]

显示如下错误

[code]

EnvironmentError: mysql_config not found

[/code]

然后我查找这个文件是否存在

[code]

find / -name mysql_config

[/code]

显示不存在,然后我在网上找到原因说是mysql的一些开发包没有安装,我安装一下就行了

[code]
apt-get install python-dev libmysqlclient-dev
[/code]

Python串口通信和连接Mysql数据库

串口通信:
http://pypi.python.org/pypi/pyserial
应用如下(程序有点问题,每次关闭串口再打开会出现乱码现象)
[python]
import serial
from time import sleep
ser = serial.Serial(‘/dev/ttyUSB0’, 2400, timeout=1)
def recv(serial):
data, quit = None, False
while 1:
data =serial.read(1)
if data == ”:
continue
sleep(0.02) # data is this interval will be merged
while 1:
n = serial.inWaiting()
if n > 0:
data = "%s%s" % (data,serial.read(n))
else:
quit = True
break
if quit:
break

return data
while 1:
data =recv(ser)
if data== ‘c’ :
ser.close()
break
ser.write(data)
[/python]
Python连接mysql
链接http://sourceforge.net/projects/mysql-python/files/mysql-python/
我没有按照这种方法,因为没安装成功,我用的debian系统,采取包的安装方法
apt-get install python-mysqldb

无限级分类的动态显示

用到的知识Ajax +php

我以商品销售区域的动态选择为例

数据库设计:

[sql]
CREATE TABLE `think_area` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`pid` int(20) NOT NULL DEFAULT ‘0’,
`area_name` char(100) NOT NULL,
`area_path` char(50) NOT NULL,
`ban_id` int(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;</pre>
[/sql]

其中pid是父级分类的ID值,area_name 是当前区域名称呢 area_path是父级area_path名加“-”+父级ID,例如“0-19-22”,o是顶级区域ID,19是父级的父级区域ID,22是父级区域ID。

ban_id是板块ID,不同的板块销售不同的商品可能送货区域不同,所以绑定下板块ID

实验数据

[sql]
<pre>INSERT INTO `think_area` VALUES (‘2’, ‘0’, ‘测试1’, ‘0’, ”, ‘2’);
INSERT INTO `think_area` VALUES (‘3’, ‘2’, ‘测试1-1’, ‘0-2’, ”, ‘2’);
INSERT INTO `think_area` VALUES (‘4’, ‘0’, ‘南京’, ‘0’, ”, ‘1’);
INSERT INTO `think_area` VALUES (‘5’, ‘4’, ‘江宁’, ‘0-4’, ”, ‘1’);
INSERT INTO `think_area` VALUES (‘6’, ‘5’, ‘河海大学’, ‘0-4-5’, ”, ‘1’);
INSERT INTO `think_area` VALUES (‘8’, ‘4’, ‘仙林’, ‘0-4’, ”, ‘1’);
INSERT INTO `think_area` VALUES (‘9’, ‘8’, ‘南京大学’, ‘0-4-8’, ”, ‘1’);
INSERT INTO `think_area` VALUES (’10’, ‘4’, ‘鼓楼’, ‘0-4’, ”, ‘1’);
[/sql]

选择原理如下:

1.如果用户曾经没选择过任何区域,取得该板块的所有顶级分类传过去供选择,如果曾经选择了某个区域则不管是不是选择到了最底下,都调用函数显示已经选择过的区域并加上下一级的区域供选择(如果有下一级的话)

2.你可以把选择ID传递在cookie或querystring中,这问题不大,我选择的是在这两个中都传递、

3.监听select的值变化,变化了触发刚才的显示函数

HTML:

[html]
<div id="select1"rel="{$Think.get.banid}" aid="{$Think.get.aid}" ></div>
<select class="span2 area " onchange="area_select(this.value,true)"id="select_start">
<option value="0" >选择区域</option>
<volist name="area_data" id="li">
<option value="{$li.id}">{$li.area_name}</option>
</volist>
</select>
<span id="area_append">
</span>
</div>
[/html]
很明显我把板块id $banid 区域ID $aid隐藏在HTML中,供JS读取数据,选择框数值变化激发area_select()函数
需要jquery.js和jquery.cookies.2.2.0.min.js 两个文件
[js]
$("document").ready(function(){
var ban_id=$("#select1").attr("rel");
aid=$.cookies.get("area_id"+ban_id);
if(aid){
area_select(aid);

}
})
function area_select(area_id,r){
var ban_id=$("#select1").attr("rel");
if(area_id==0)
exit;
url=ROOT+"Ajax/select_area?area_id="+area_id;//ROOT="http://localhost/index.php/";

$.get(url,function(data){

$("#select_start").remove();
$("#area_append").html(data)

})
$.cookies.setOptions(cookieOptions);
$.cookies.set("area_id"+ban_id,area_id);
if(r){
n_url=ROOT+"Shop/index?banid="+ban_id+"&aid="+area_id;
window.location.href=n_url;
}

}
[/js]
上面的自己分析
下面是PHP代码,用的thinkphp框架,简化了查询操作、、、不懂的查手册,三分钟就能看明白什么回事
[php]
function select_area() {
$Area = D("Area");
$id = (int) $_GET["area_id"];
$data = $Area->find($id);
$area_path = $data["area_path"];
$ban_id = $data["ban_id"];
$level_num = count($level_data = explode("-", $area_path)); //计算层级
$result = "";
for ($i = 0; $i < $level_num; $i++) {
$area_path = "0";
for ($j = 1; $j < $i + 1; $j++) {
$area_path.="-" . $level_data[$j];
}
$row = $Area->where("area_path=" . "’$area_path’ and ban_id=" . $ban_id)->select();

$str = ‘<select class="span2 area" onchange="area_select(this.value,true)" id="select’ . $i . ‘">’;
foreach ($row as $key => $value) {
$selected = "";//注意为什么要引入这个值!!是为了记住刚才选择过的项。
if ($value["id"] == $level_data[$i + 1] || $value["id"] == $id)
$selected = "selected";
$str.="<option value=" . $value[‘id’] . " " . $selected . " >" . $value["area_name"] . "</option>";
}
$str.="</select>";
$result.=$str;
}
/* * ******
* 下面是判断有无子级元素并取出来
*/
$data = $Area->where("pid=" . $id)->select();
if ($data) {
$str = ‘<select class="span2 area" onchange="area_select(this.value,true)" id="select’ . $level_num . ‘">’;
foreach ($data as $key => $value) {
$str.="<option value=" . $value[‘id’] . " " . $selected . " >" . $value["area_name"] . "</option>";
}
$str.="</select>";
$result.=$str;
}
echo $result;
}
[/php]

MySQL 三种关联查询的方式: ON vs USING vs 传统风格

看看下面三个关联查询的 SQL 语句有何区别?
[sql]
SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id)
SELECT * FROM film JOIN film_actor USING (film_id)
SELECT * FROM film, film_actor WHERE film.film_id = film_actor.fi
[/sql]
最大的不同更多是语法糖,但有一些有意思的东西值得关注。

为了方便区别,我们将前两种写法称作是 ANSI 风格,第三种称为 Theta 风格。

Theta 风格

在 FROM 短语中列出了关联的表名,而 WHERE 短语则指定如何关联。

这种写法被认为是古老的方式,有些时候比较难以理解,请看下面查询:
[sql]
SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id AND actor_id = 17 AND film.length > 120
[/sql]
上述查询列出片长超过 120 分钟的电影,其中包括演员编号是 17 的条件。别在意查询结果,查询本身如何呢?WHERE 表达式中包含三个条件,要看出哪个条件是关联,哪个条件是过滤还是稍费点事的。不过还是相对简单的,但如果是 5 个表,20 多个条件呢?

ANSI 风格: ON

使用 JOIN … ON 可以将表关联的条件和记录过滤条件分开,将上面的语句重写后的结果如下:
[sql]
SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) WHERE actor_id = 17 AND film.length > 120
[/sql]
看起来清晰许多。

注意: ON 语句中的括号不是必须的,我个人喜欢这样写而已。

ANSI 风格: USING

有一种特殊情况,当两个要关联表的字段名是一样的,我们可以使用  USING ,可减少 SQL 语句的长度:
[sql]
SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120
[/sql]
这个时候括号就是必须的了。这种写法很好,输入更少的单词,查询的性能也非常棒,但还需要注意一些差异。

USING 和 ON

下面语句是可行的:
[sql]
SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120;
[/sql]
但下面这个就不行:
[sql]
SELECT film.title, film_id FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) WHERE actor_id = 17 AND film.length > 120;ERROR 1052 (23000): Column ‘film_id’ in field list is ambiguous
[/sql]
因为 USING “知道” film_id 字段在两个表中都有,所以没有指定确切的表都没关系,两个值必须一致就是。

ON 就没那么智能,你必须指明要关联的表和字段名。

上面两个实际的结果是比较有趣的,当使用 USING 时,字段只在结果中出现一次:
[sql]
SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120 LIMIT 1G
*************************** 1. row ***************************
film_id: 96
title: BREAKING HOME
description: A Beautiful Display of a Secret Agent And a Monkey who must Battle a Sumo Wrestler in An Abandoned Mine Shaft
release_year: 2006
language_id: 1
original_language_id: NULL
rental_duration: 4
rental_rate: 2.99
length: 169
replacement_cost: 21.99
rating: PG-13
special_features: Trailers,Commentaries
last_update: 2006-02-15 05:03:42
actor_id: 17
last_update: 2006-02-15 05:05:03
[/sql]
而使用 ON 时,字段就会出现两次:
[sql]
SELECT * FROM film JOIN film_actor ON film.film_id = film_actor.film_id WHERE actor_id = 17 AND film.length > 120 LIMIT 1G
*************************** 1. row ***************************
film_id: 96
title: BREAKING HOME
description: A Beautiful Display of a Secret Agent And a Monkey who must Battle a Sumo Wrestler in An Abandoned Mine Shaft
release_year: 2006
language_id: 1
original_language_id: NULL
rental_duration: 4
rental_rate: 2.99
length: 169
replacement_cost: 21.99
rating: PG-13
special_features: Trailers,Commentaries
last_update: 2006-02-15 05:03:42
actor_id: 17
film_id: 96
last_update: 2006-02-15 05:05:03
[/sql]
幕后
MySQL 对两者的处理方式是相同的,使用 EXPLAIN EXTENDED 我们可以看到:
[sql]
EXPLAIN EXTENDED SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120G
*************************** 1. row ***************************

2 rows in set, 1 warning (0.00 sec)

root@mysql-5.1.51> SHOW WARNINGSG
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `sakila`.`film`.`title` AS `title`,`sakila`.`film`.`film_id` AS `film_id`
from `sakila`.`film` join `sakila`.`film_actor`
where (
(`sakila`.`film`.`film_id` = `sakila`.`film_actor`.`film_id`)
and (`sakila`.`film_actor`.`actor_id` = 17)
and (`sakila`.`film`.`length` > 120)
)
[/sql]
最终所有的查询都被转成了 Theta 风格。

SQL语句

[sql]
#c 取消命令
#-A –skip-auto-rehash 自动补全
#mysql –print-defaults
#my_print_defaults client
#mysql learn-database < learn.sql 执行命令
#source learn.sql 同上
#mysql test 使用test数据库
#mysql -e "select * from city limit 0,10" test 直接执行一条命令
#mysqldump test |mysql test1 备份数据
#mysql test < select.sql > q.txt 重定向
#mysql -t test < select.sql > q.txt 重定向(格式输出)
#mysql -H -e "select * from city limit 0,10" test > 1.html 重定向生成html
#mysql -X -e "select * from city limit 0,10" test > 1.xml 重定向生成XML
#mysql -ss -e "select * from city limit 0,10" test 忽略头部 –skip-column-names
#mysql >show full columns from city G; 垂直输出表的内容
#mysql –tee=tmp.out test 记录回话信息 T开启 t关闭
#select @max :=max(cityid) from city; 变量 max 使用的时候用@max 大小写不敏感 且为Mysql特有
#set @max=1; 设置max值为1
# set @ln=0;select @ln:=@ln+1 as ln ,cityid from city limit 0,20; 结果中输出行号
#show status like "uptime"; 已经运行时间
#status
#mysql test <<MYSQL_INPUT
/*输入的文档或数据 */
#MYSQL_INPUT

[/sql]