imyfan 发布的文章

一.Join语法概述
join 用于多表中字段之间的联系,语法如下:

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1:左表;table2:右表。

JOIN 按照功能大致分为如下三类:

INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。

LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。

RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。

注意:mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.

接下来给出一个列子用于解释下面几种分类。如下两个表(A,B)

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;
idnamename
1PirateRutabaga
2MonkeyPirate
3NinjaDarth Vader
4SpaghettiNinja

4 rows in set (0.00 sec)
二.Inner join
内连接,也叫等值连接,inner join产生同时符合A和B的一组数据。

mysql> select * from A inner join B on A.name = B.name;
idnameidname
1Pirate2Pirate
3Ninja4Ninja

三.Left join
mysql> select * from A left join B on A.name = B.name;

或者:select * from A left outer join B on A.name = B.name;

idnameidname
1Pirate2Pirate
2MonkeyNULLNULL
3Ninja4Ninja
4SpaghettiNULLNULL

4 rows in set (0.00 sec)
left join,(或left outer join:在Mysql中两者等价,推荐使用left join.)左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

如果想只从左表(A)中产生一套记录,但不包含右表(B)的记录,可以通过设置where语句来执行,如下:

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;
idnameidname
2MonkeyNULLNULL
4SpaghettiNULLNULL

2 rows in set (0.00 sec)

同理,还可以模拟inner join. 如下:

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;
idnameidname
1Pirate2Pirate
3Ninja4Ninja

2 rows in set (0.00 sec)
求差集:

根据上面的例子可以求差集,如下:

SELECT * FROM A LEFT JOIN B ON A.name = B.name
WHERE B.id IS NULL
union
SELECT * FROM A right JOIN B ON A.name = B.name
WHERE A.id IS NULL;

结果

+------+-----------+------+-------------+
idnameidname
2MonkeyNULLNULL
4SpaghettiNULLNULL
NULLNULL1Rutabaga
NULLNULL3Darth Vader

四.Right join

mysql> select * from A right join B on A.name = B.name;
idnameidname
NULLNULL1Rutabaga
1Pirate2Pirate
NULLNULL3Darth Vader
3Ninja4Ninja

4 rows in set (0.00 sec)
同left join。

五.Cross join
cross join:交叉连接,得到的结果是两个表的乘积,即笛卡尔积

笛卡尔(Descartes)乘积又叫直积。假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。

mysql> select * from A cross join B;
idnameidname
1Pirate1Rutabaga
2Monkey1Rutabaga
3Ninja1Rutabaga
4Spaghetti1Rutabaga
1Pirate2Pirate
2Monkey2Pirate
3Ninja2Pirate
4Spaghetti2Pirate
1Pirate3Darth Vader
2Monkey3Darth Vader
3Ninja3Darth Vader
4Spaghetti3Darth Vader
1Pirate4Ninja
2Monkey4Ninja
3Ninja4Ninja
4Spaghetti4Ninja

16 rows in set (0.00 sec)

再执行:mysql> select * from A inner join B; 试一试

在执行mysql> select * from A cross join B on A.name = B.name; 试一试

实际上,在 MySQL 中(仅限于 MySQL) CROSS JOIN 与 INNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。
INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字,因此下面的 SQL 效果是一样的:

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2
六.Full join
mysql> select * from A left join B on B.name = A.name

-> union 
-> select * from A right join B on B.name = A.name;
idnameidname
1Pirate2Pirate
2MonkeyNULLNULL
3Ninja4Ninja
4SpaghettiNULLNULL
NULLNULL1Rutabaga
NULLNULL3Darth Vader

6 rows in set (0.00 sec)
全连接产生的所有记录(双方匹配记录)在表A和表B。如果没有匹配,则对面将包含null。

七.性能优化
1.显示(explicit) inner join VS 隐式(implicit) inner join

如:

select * from
table a inner join table b
on a.id = b.id;
VS

select a., b.
from table a, table b
where a.id = b.id;
我在数据库中比较(10w数据)得之,它们用时几乎相同,第一个是显示的inner join,后一个是隐式的inner join。

参照:Explicit vs implicit SQL joins

2.left join/right join VS inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.

在使用left join(或right join)时,应该清楚的知道以下几点:

(1). on与 where的执行顺序

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。如:

PS, 这部分有些不妥,感谢 wxweven 指正:

这部分的内容,博主写的有些欠妥当,不知道博主有没有实际运行测试过,下面说说我的看法:

(1)首先关于on和where的用法,如果直接把where里面的条件拿到on里面去,结果是跟原来的不一致的,所以博主说的“在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行”是不成立的,因为筛选条件放在on或者where,产生的是不同的结果,不能说为了性能就把where中的条件放到on中。

可参考sql语句中join on和where用法的区别和联系
PASS

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;
Great

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1
从上面例子可以看出,尽可能满足ON的条件,而少用Where的条件。从执行性能来看第二个显然更加省时。

(2).注意ON 子句和 WHERE 子句的不同

如作者举了一个列子:

mysql> SELECT * FROM product LEFT JOIN product_details

   ON (product.id = product_details.id)
   AND product_details.id=2;
idamountidweightexist
1100NULLNULLNULL
22002220
3300NULLNULLNULL
4400NULLNULLNULL

4 rows in set (0.00 sec)

mysql> SELECT * FROM product LEFT JOIN product_details

   ON (product.id = product_details.id)
   WHERE product_details.id=2;
idamountidweightexist
22002220

1 row in set (0.01 sec)
从上可知,第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

(3).尽量避免子查询,而用join

往往性能这玩意儿,更多时候体现在数据量比较大的时候,此时,我们应该避免复杂的子查询。如下:

PASS

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);
Great

insert into t1(a1)
select b1 from t2
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id
where t1.id is null;
这个可以参考mysql的exists与inner join 和 not exists与 left join 性能差别惊人

补充:MySQL STRAIGHT_JOIN 与 NATURAL JOIN的使用

感谢网友 折翼的鸟 给出的提醒。

长话短说:straight_join实现强制多表的载入顺序,从左到右,如:

...A straight_join B on A.name = B.name
straight_join完全等同于inner join 只不过,join语法是根据“哪个表的结果集小,就以哪个表为驱动表”来决定谁先载入的,而straight_join 会强制选择其左边的表先载入。

往往我们在分析mysql处理性能时,如(Explain),如果发现mysql在载入顺序不合理的情况下,可以使用这个语句,但往往mysql能够自动的分析并处理好。

[sql] view plain copy
--查询
SELECT tp.tp_id, tp.tpmc, tp.leveid, tp.tpdz, tp.jgm, tp.scsj, tp.pbzyid, tp.ksbfsj, tp.jsbfsj, tp.status, tp.tpbz FROM qdgl_tupian tp INNER JOIN qdgl_pqb pq
ON tp.tp_id=pq.tpid WHERE pq.bfjgm='27010825' AND ps_bfsj >= '2013-01' AND ps_bfsj< 2013-05-31 ;
[sql] view plain copy
-- 修改
UPDATE jx_lsjl jx INNER JOIN isa_gtxx gt ON jx.ckid=gt.gtbh set jx.ckid=gt.gtid WHERE gt.jgm='27010825' AND jx.jgm='27010825' ;
[sql] view plain copy
--删除
DELETE jx FROM jx_lsjl jx INNER JOIN isa_gtxx gt ON jx.ckid=gt.gtbh WHERE gt.jgm='27010825' AND jx.jgm='27010825' ;

MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。

MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

通过索引优化来实现MySQL的ORDER BY语句优化:

1、ORDER BY的索引优化。如果一个SQL语句形如:
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、WHERE + ORDER BY的索引优化,形如:
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。

注意:如果columnX对应多个值,如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3、WHERE+ 多个字段ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

MySQL Order By不能使用索引来优化排序的情况

  • 对不同的索引键做 ORDER BY :(key1,key2分别建立索引)
    SELECT * FROM t1 ORDER BY key1, key2;
  • 在非连续的索引键部分上做 ORDER BY:(key_part1,key_part2建立联合索引;key2建立索引)
    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
  • 同时使用了 ASC 和 DESC:(key_part1,key_part2建立联合索引)
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
  • 用于搜索记录的索引键和做 ORDER BY 的不是同一个:(key1,key2分别建立索引)
    SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
  • 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时,则无法利用索引来实现order by的优化
    SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

特别提示:
1>mysql一次查询只能使用一个索引。如果要对多个字段使用索引,建立复合索引。
2>在ORDER BY操作中,MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。

数据库索引是什么?

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.

数据库索引的本质是数据结构,这种数据结构能够帮助我们快速的获取数据库中的数据。

数据库为什么要使用索引?

从定义中我们就知道,使用索引就是为了提高效率。下面的例子能让我们直观的看到索引的作用。

我们有下面一张employees表,大概三十万行。

emp_nobirth_datefirst_namelast_namegenderhire_date
100011953-09-02GeorgiFacelloM1986-06-26
100021964-06-02BezalelSimmelF1985-11-21
100031959-12-03PartoBamfordM1986-08-28
100041954-05-01ChirstianKoblickM1986-12-01
100051955-01-21KyoichiMaliniakM1989-09-12
100061953-04-20AnnekePreusigF1989-06-02
100071957-05-23TzvetanZielinskiF1989-02-10
100081958-02-19SaniyaKalloufiM1994-09-15
100091952-04-19SumantPeacF1985-02-18
100101963-06-01DuangkaewPiveteauF1989-08-24
......
......
......
4999991958-05-01SachinTsukudaM1997-11-30
4999981956-09-05PatriciaBreugelM1993-10-13
4999971961-08-03BerhardLenartM1986-04-21
4999961953-03-07ZitoBaazM1990-09-27
4999951958-09-24DekangLichtnerF1993-01-12
4999941952-02-26NavinArgenceF1990-04-24
4999931963-06-04DeForestMullainathanM1997-04-07
4999921960-10-12SiamakSalverdaF1987-05-10
4999911962-02-26PohuaSichmanF1989-01-12
4999901963-11-03KhaledKohlingM1985-10-10

我们想找到first_name为Chirstian的结果,下面比较一下加上索引前后查询所耗费的时间,从结果我们可以看出使用索引可以使查询效率提高20倍。

mysql> select * from employees where first_name = 'Chirstian';
mysql> alter table employees add index first_name (first_name);
mysql> select * from employees where first_name = 'Chirstian';

mysql> SHOW PROFILES;
Query_IDDurationQuery
10.17415400select * from employees where first_name = 'Chirstian'
21.03130100alter table employees add index first_name (first_name)
30.00869100select * from employees where first_name = 'Chirstian'

既然索引的作用这么大,那我们给每列都加上索引不就可以使效率最大化了吗?

答案是否定的,具体原因要我们了解索引的实现原理才能明白。

Mysql索引实现

写在前面

我们了解Mysql索引是什么以及如何实现的目的是为了更高效的使用Mysql,而不是为了去真的实现一个数据库。所以我们这里只介绍Mysql的两个常用引擎Myisam以及Innodb的实现原理。

Myisam和Innodb引擎都是使用B+树作为索引结构,实现上略有差别,所以我们先看看什么是B+树。选择B+树而不是其他数据结构的原因主要是因为数据是保存在硬盘上而不是内存中,所以减少磁盘IO次数才是提升效率的关键。如果对具体原理感兴趣,可以自行google,这对我们如何优化作用有限所以就不展开说了。

B+树索引

一图胜万言,下面就是一个B+树。

节点 与二叉树不同的是,B+中的节点可以有多个元素及多个子节点。在B+索引树中,非叶子节点由索引元素和指向子节点的指针组成,他们的作用就是找到叶子节点,因为只有叶子节点中有最终要找的数据信息。从图中可以看出每个节点中指针的数量比索引元素数量多一个,在叶子节点中,因为没有子节点,多出的那个指针指向下一个叶子节点,这样把所有叶子节点串联起来,这对于范围搜索很有用。在实际应用中一个节点的大小是固定的通常等于磁盘一个页的大小,这样存取一个节点只需要一次磁盘IO,一般节点可存上百个元素,所以索引几百万数据B+树高不会超过3。

搜索 搜索类似于二叉查找树,从根节点开始自顶向下遍历,直到叶子节点,在节点内部典型的是使用二分查找来确定位置。如果是范围查找,对于B+树而言是非常方便的,因为叶子节点是有序且连续的。

插入 首先对树进行搜索找到应该存入的叶子节点。之前我们提到节点的大小是固定的,如果节点内还没放满,则直接插入。如果节点满了,则创建新节点把原节点插入新元素后的一半放入新节点,然后把新节点最小的元素插入父节点。如果父节点满了,进行同样的操作,根节点也不例外。

删除 首先对树进行搜索找到叶子节点并删除元素。当删除后的叶子节点不满一半时:如果兄弟节点过半数则借一个过来,并更新父节点中子节点的分界值;如果等于半数则合并,因为父节点中有两个指针指向这两个兄弟节点,所以需要删除多余的一个来更新父节点,如果删除后父节点不满一半,继续递归以上步骤,直到根节点。

从B+树的特点可以看出,虽然B+树索引能够让我们在有限次磁盘IO次数内快速的查询到数据,但是在插入和删除时也要付出维护B+树的代价,这也是为什么在开始说的不能把每列都加上索引的原因之一。

为了帮助大家更好的理解,这个网站通过动画展示了B+树的查询、插入、删除操作的实现过程。这个视频分3集讲解了B+树和在索引中的实现,不过因为在youtube上,翻墙才能看。

Myisam与Innodb索引的区别

虽然它们都是用B+树实现的索引,但是实现上略有差别,主要有两点。

叶子节点存储数据内容的差别 Myisam存储的是存放真实数据的地址,而Innodb存储的是真实的数据。其实Innodb存储的数据本身就是主键的B+树索引,因为索引内存储着真实的数据。

辅助索引叶子节点存储数据内容的差别 Mysiam存储的还是真实数据的地址,与主索引一样。Innodb存储的是主键的值。

题外话:在一般后端工程师的面试中都会有关于Mysql的问题,其中比较Myisam和Innodb引擎就是常问的一个。网上的答案也都比较陈旧而且只有结论没有验证。比如比较中有一项是否支持Full-text索引,其实Innodb在Mysql5.6中就已经支持了,不过大多数答案都没有更新。还有完全不考虑实际情况就说Myisam在查、Innodb在增删改上更快,我在网上很久也没有找到支撑这些结论的理论或者实验基础。 就我个人而言我更推荐使用Innodb引擎,所以前面我没有深入的比较它们索引的差别。推荐Innodb不单单是因为它在5.5版本之后成为Mysql的默认引擎,还有它拥有支持事务、外键、MVCC、行锁这些更先进的引擎技术。为了类似于不带where的count时Myisam速度更快这样模棱两可的性能差别,而抛弃这些先进的技术,很不值得。而且即使真的像上面所说的Innodb在查时性能稍差于Myisam,我也不认为会因此成为整个架构性能的瓶颈所在,如果真的需要更换引擎来提升性能了,那在架构其他地方的问题可能更大。

Mysql索引类型及优化策略

索引类型

唯一索引(unique index) 看见名字我们就知道,唯一索引列中的值必须是唯一的。不过有一个例外,可以有且可以有多个Null。

普通索引(index) 普通索引可以包括不止一列,一般把多个列组成的普通索引叫组合索引,也有把普通索引看成是只有一列的组合索引的。此外,在索引字符串时,可以只把前几位作为索引来提升效率。

主键(primary key) 主键必须唯一,不同的是不能有Null。而且一个表只能有一个主键。有很多人认为主键是唯一索引的一种,其实是不准确的。主键也可以是组合索引,只要组合的每条结果是唯一的。这在某些场景非常实用,比如一个多对多关系中的枢纽表就非常适合使用复合主键。下图就是一个典型的用户权限功能的实现,用户和角色、角色和权限都是多对多的关系,需要枢纽表来记录他们之间的对应关系,而这些关系都是唯一的,所以这种枢纽表用复合主键非常合适。

优化策略

最左前缀匹配原则

还拿前面的employees表举例,比如我们建立一个(birth_date, first_name, last_name ) 的组合索引。

mysql> alter table employees add index bd_fn_ln (birth_date, first_name, last_name);

下面的查询是用到索引的: mysql> select from employees where birth_date = '1954-05-01' and first_name = 'Chirstian' and last_name = 'Koblick'; mysql> select from employees where birth_date = '1954-05-01' and first_name = 'Chirstian'; mysql> select * from employees where birth_date = '1954-05-01' and last_name = 'Koblick'; 下面是这三个查询explain结果。

mysql> explain select * from employees where birth_date = '1954-05-01' and first_name = 'Chirstian' and last_name = 'Koblick';
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEemployeesrefbd_fn_lnbd_fn_ln97const,const,const1Using index condition

1 row in set (0.00 sec)

mysql> explain select * from employees where birth_date = '1954-05-01' and first_name = 'Chirstian' ;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEemployeesrefbd_fn_lnbd_fn_ln47const,const1Using index condition

1 row in set (0.01 sec)

mysql> explain select * from employees where birth_date = '1954-05-01' and last_name = 'Koblick';
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEemployeesrefbd_fn_lnbd_fn_ln3const60Using index condition

1 row in set (0.00 sec)

虽然结果都是一条,不过前两个查询都用到了联合索引。最后一个只用到了birth_date这一个索引,所以会在birth_date = 1954-05-01 的60结果中遍历last_name来找到等于Koblick的结果。还有, 如果where中都是精确匹配(使用’=’号),那它们的顺序不会影响索引的使用。

而下面这个查询因为没用到组合索引的最左列,所以不会用到索引而是遍历了所有的数据,这就是最左前缀匹配:

mysql> select * from employees where first_name = 'Chirstian' and last_name = 'Koblick';
emp_nobirth_datefirst_namelast_namegenderhire_date
100041954-05-01ChirstianKoblickM1986-12-01

1 row in set (0.18 sec)

mysql> explain select * from employees where first_name = 'Chirstian' and last_name = 'Koblick';
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEemployeesALLNULLNULLNULLNULL299468Using where

1 row in set (0.00 sec)

选择区分度高的列作为索引

区分度:count(distinct col)/count(*)。 区分度是一个介于0和1之间的小数,越接近1区分度越高,越适合做索引。 原因很容易理解,比如一个辞典中全是以a和b开头的单词,那么按照首字母简历一个目录(索引),那么目录上一共就两条,每条的范围对应差不多半本辞典,那这个目录(索引)毫无用处。相反,一个班级的学生信息以学号做索引,那么区分度为1,只要找到学号就能直接找到相对应的学生信息,这个索引就非常有效。

不要在比较运算符左侧使用函数或进行计算

在sql语句的比较运算符左侧使用函数或进行计算会使索引失效。

mysql> explain select * from employees where emp_no - 1 = 10005;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEemployeesALLNULLNULLNULLNULL299468Using where

1 row in set (0.11 sec)

mysql> explain select * from employees where emp_no = 10005-1;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEemployeesconstPRIMARYPRIMARY4const1NULL

1 row in set (0.00 sec)

事例数据库

以上用做事例的employees表来自Mysql官方事例数据库-employees,里面有安装说明,数据库大小合适,非常适合练习。

1、什么是事务

事务是一条或多条数据库操作语句的组合,具备ACID,4个特点。

原子性:要不全部成功,要不全部撤销

隔离性:事务之间相互独立,互不干扰

一致性:数据库正确地改变状态后,数据库的一致性约束没有被破坏

持久性:事务的提交结果,将持久保存在数据库中

2、事务并发会产生什么问题

1)第一类丢失更新:在没有事务隔离的情况下,两个事务都同时更新一行数据,但是第二个事务却中途失败退出, 导致对数据的两个修改都失效了。

例如:

张三的工资为5000,事务A中获取工资为5000,事务B获取工资为5000,汇入100,并提交数据库,工资变为5100,

随后

事务A发生异常,回滚了,恢复张三的工资为5000,这样就导致事务B的更新丢失了。

2)脏读:脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
例如:
  张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
  与此同时,
  事务B正在读取张三的工资,读取到张三的工资为8000。
  随后,
  事务A发生异常,而回滚了事务。张三的工资又回滚为5000。
  最后,
  事务B读取到的张三工资为8000的数据即为脏数据,事务B做了一次脏读。

3)不可重复读:是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。
例如:
  在事务A中,读取到张三的工资为5000,操作没有完成,事务还没提交。
  与此同时,
  事务B把张三的工资改为8000,并提交了事务。
  随后,
  在事务A中,再次读取张三的工资,此时工资变为8000。在一个事务中前后两次读取的结果并不致,导致了不可重复读。

4)第二类丢失更新:不可重复读的特例。
有两个并发事务同时读取同一行数据,然后其中一个对它进行修改提交,而另一个也进行了修改提交。这就会造成第一次写操作失效。

例如:

在事务A中,读取到张三的存款为5000,操作没有完成,事务还没提交。
  与此同时,
  事务B,存储1000,把张三的存款改为6000,并提交了事务。
  随后,
  在事务A中,存储500,把张三的存款改为5500,并提交了事务,这样事务A的更新覆盖了事务B的更新。

5)幻读:是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。
例如:
  目前工资为5000的员工有10人,事务A读取所有工资为5000的人数为10人。
  此时,
  事务B插入一条工资也为5000的记录。
  这是,事务A再次读取工资为5000的员工,记录为11人。此时产生了幻读。

提醒:
不可重复读的重点是修改,同样的条件,你读取过的数据,再次读取出来发现值不一样了
幻读的重点在于新增或者删除,同样的条件,第 1 次和第 2 次读出来的记录数不一样

3、事务隔离级别,解决什么并发问题,以及存在什么并发问题

(1)READ_UNCOMMITTED
  这是事务最低的隔离级别,它充许另外一个事务可以看到这个事务未提交的数据。
  解决第一类丢失更新的问题,但是会出现脏读、不可重复读、第二类丢失更新的问题,幻读 。
(2)READ_COMMITTED
  保证一个事务修改的数据提交后才能被另外一个事务读取,即另外一个事务不能读取该事务未提交的数据。
  解决第一类丢失更新和脏读的问题,但会出现不可重复读、第二类丢失更新的问题,幻读问题
(3)REPEATABLE_READ
  保证一个事务相同条件下前后两次获取的数据是一致的

解决第一类丢失更新,脏读、不可重复读、第二类丢失更新的问题,但会出幻读。
(4)SERIALIZABLE
  事务被处理为顺序执行。
  解决所有问题

提醒:

Mysql默认的事务隔离级别为repeatable_read

4、InnoDB引擎的锁机制

(之所以以InnoDB为主介绍锁,是因为InnoDB支持事务,支持行锁和表锁用的比较多,Myisam不支持事务,只支持表锁)

共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

说明:

1)共享锁和排他锁都是行锁,意向锁都是表锁,应用中我们只会使用到共享锁和排他锁,意向锁是mysql内部使用的,不需要用户干预。

2)对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁,事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。

3)InnoDB行锁是通过给索引上的索引项加锁来实现的,因此InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!。

http://www.cnblogs.com/fidelQuan/p/4549068.html