数据库

MySQL 幻读分析

数据库 lvxinwei Comments

要理解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慢查询分析

数据库 lvxinwei Comments

最近遇见一个 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修改表存储引擎

数据库 lvxinwei

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行锁表锁

数据库 lvxinwei

在调用存储过程中，就会涉及到表锁，行锁这一概念：所谓区别：有索引的时候就是行锁，没有索引的时候就是表索。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 出错的解决

数据库 lvxinwei

[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]