MySQL常用知识

一、数据库优化思路

1.SQL语句优化

1）尽量不要在where子句中使用 != 和 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
2）尽量不要在where子句中对字段进行null值判断，否则引擎将放弃使用索引而进行全表扫描。

解决方法： select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

3）用exists代替in
4）用where子句替换having子句 因为having只会在检索出所有记录之后才对结果集进行过滤

2、数据库结构优化

1）范式优化： 比如消除冗余字段。
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时表可采取此方法。可按月自动建表分区。
4）垂直拆分和水平拆分：
案例： 简单购物系统暂设涉及如下表：
1.产品表（数据量10w，稳定）
2.订单表（数据量200w，且有增长趋势）
3.用户表 （数据量100w，且有增长趋势）
以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万
垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力
方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上
水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺
方案： 用户表通过性别拆分为男用户表和女用户表；订单表通过已完成和完成中拆分为已完成订单和未完成订单； 产品表和未完成订单放一个server上， 已完成订单表和男用户表放一个server上，女用户表放一个server上。

二、常见概念

主键：数据库表中唯一标识一条数据的数据列或属性的组合。一行数据列只能有一个主键，且主键不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。
超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。

1.视图的作用，视图可以更改么？

视图是虚拟的表，与包含数据的表不一样，视图的数据是在使用时动态查询出来的；视图本身不包含任何列或数据。
优点：使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
缺点：视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图：create view XXX as XXXXXXXXXXXXXX;
对于某些视图比如未使用联结子查询、分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

2.drop,delete与truncate的区别

drop：直接删掉整个表（数据和结构），并删除表结构被依赖的约束（constrain)、触发器（trigger)、索引（index)。依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
truncate：删除表中数据，保留结构，删除时直接释放储存表数据使用的数据页，由于删除时不记录每一行的回滚日志，只在事务日志中记录整个数据页的释放记录，所以数据不可恢复，也不会激活与表有关的删除触发器。再插入时自增长id又从1开始 。执行速度快。
delete：删除表中数据，保留结构，可以加where字句来限定删除范围。执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

不同删除方式的对比

–表和索引所占空间：
当表被truncate后，这个表和索引所占用的空间会恢复到初始大小。
而delete操作不会减少表或索引所占用的空间。
drop语句将表所占用的空间全释放掉。

–应用范围：
truncate只能对TABLE（表）
delete可以是TABLE和VIEW（表和视图）

–操作性质：
delete语句为DML（data maintain Language)，这个操作会被放到 rollback segment中，事务提交后才生效。如果有相应的 触发器（tigger）,执行的时候将被触发。
truncate、drop是DDL（data define language)，操作立即生效，原数据不放到 rollback segment中，不能回滚。

–不同条件下的使用情况：
1.在没有备份情况下，谨慎使用 drop 与 truncate。
2.要彻底删除表用drop;
3.若想保留表结构而将表中数据删除，如果于事务无关，用truncate即可实现。
4.如果和事务有关，或是想触发trigger（触发器）,还是用delete。
5.要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。
6.如果想保留自增ID标识计数值，请改用 DELETE。
7.对于由 FOREIGN KEY 约束引用的表，不能使用 truncate TABLE，而应使用不带 WHERE 子句的delete语句。

3.索引的工作原理及其种类

数据库索引：是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及B+树。
数据库中在表数据之外，数据库系统还维护着协助特定查找算法查询数据的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

使用索引的好处：
创建索引可以大大提高数据库的查询\修改性能。
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

使用索引的弊端：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引的创建规则：
索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。
一般来说，应该在这些列上创建索引：
在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改操作频率远远大于查询操作频率时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引的种类：
根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

**唯一索引：**唯一索引是不允许其中任何两行具有相同索引值的索引。
主键索引： 数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
聚集索引： 在聚集索引中，表中行的物理顺序与索引键值的逻辑顺序相同。一个表只能包含一个聚集索引。

如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。
与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比内存慢很多，再加上机械运动耗费，磁盘的存取速度往往是内存的几百分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在内存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

4.数据库连接

1.外连接OUTER JOIN

外连接分为左外连接（left join 或 left outer join）、右外连接（right join 或 right outer join）和全外连接（full join 或 full outer join）。

左外连接：显示两张表符合连接条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有满足条件的条目则显示NULL。
右外连接：显示两张表符合连接条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有满足条件的条目则显示NULL。
全外连接：显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集。

2.内连接INNER JOIN

返回：两边表同时符合条件的组合，也就是两表满足条件的交集。
如果不指定连接条件：

SELECT * FROM table1 INNER JOIN table2

内连接如果没有指定连接条件的话，和笛卡尔积的交叉连接结果一样，但是不同于笛卡尔积的地方是，没有笛卡尔积那么复杂要先生成行数乘积的数据表，内连接的效率要高于笛卡尔积的交叉连接。
但是通常情况下，使用INNER JOIN需要指定连接条件。

关于等值连接和自然连接
where等值连接(=号应用于连接条件) 不会去除重复的列
自然连接（内连接）会去除重复的列

数据库的连接运算都是自然连接，因为不允许有重复的行（元组）存在。
例如：

SELECT * FROM table1 AS a INNER JOIN table2 AS b on a.column=b.column

3.交叉连接CROSS JOIN

例如：

select * from table1 a cross join table2 b where a.id=b.id (注：cross join后加条件只能用where,不能用on)

如果不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。
查询两个表的数据时，一般不建议使用交叉连接和等值连接，应该使用内连接，因为如果有WHERE子句的话，往往会先生成两个表行数乘积的行的数据表然后才根据WHERE条件从中选择。

5.储存过程和触发器

触发器与存储过程非常相似，两者都是SQL语句集。

触发器：不能用EXECUTE语句调用，只有当用户进行特定的操作时才会执行。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器主要是通过事件执行触发而被执行的，当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，数据库就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

储存过程：可以通过存储过程名称名字而直接调用。

三、数据库事务的概念

1.事务的四个特性

1、原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

2、一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

3、隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

4、持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。

2.并发操作引发的问题

引出问题：为什么要设置隔离级别？
在数据库操作中，在并发的情况下可能出现如下问题：

• 更新丢失（Lost update）

如果多个线程操作，基于同一个查询条件对表中的记录进行修改，那么后修改的记录将会覆盖前面修改的记录，前面的修改就丢失掉了，这就叫做更新丢失。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
例如：
第1类丢失更新：事务A撤销时，把已经提交的事务B的更新数据覆盖了。

第2类丢失更新：事务A提交时覆盖事务B已经提交的数据，造成事务B所做的操作丢失。

解决方法：对行加锁，只允许并发一个更新事务。

• 脏读（Dirty Reads）
  A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。
  
  解决办法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可以避免该问题。

• 不可重复读（Non-repeatable Reads）
  一个事务对同一行数据重复读取两次，但是却得到了不同的结果。因为事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。
  
  解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。

• 幻象读
  指两次执行同一条 select 语句会出现不同的结果（返回的结果行数不同），第二次读会增加一数据行，并没有说这两次执行是在同一个事务中。一般情况下，幻象读应该正是我们所需要的。但有时候却不是，如果打开的游标，在对游标进行操作时，并不希望新增的记录加到游标命中的数据集中来。隔离级别为 游标稳定性 的，可以阻止幻象读。例如：目前工资为1000的员工有10人。那么事务1中读取所有工资为1000的员工，得到了10条记录；这时事务2向员工表插入了一条员工记录，工资也为1000；那么事务1再次读取所有工资为1000的员工共读取到了11条记录。
  
  解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。

正是为了解决以上情况，数据库提供了几种隔离级别。

3.事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(读未提交)、Read committed（读已提交）、Repeatable read（可重复读）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

• Read uncommitted(读未提交)：写的时候可以读
  如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。
• Read committed（读已提交）：读的时候可以读\写，写的时候不能读\写
  读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
• Repeatable read（可重复读）：读的时候可以读，不能写，写的时候不能读\写
  可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这样避免了不可重复读取和脏读，但是有时可能出现幻象读。（读取数据的事务）这可以通过“共享读锁”和“排他写锁”实现。
• Serializable（序列化）：事务一个接一个执行，没有并发
  提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。MySQL的默认隔离级别就是Repeatable read。

4.悲观锁和乐观锁

虽然数据库的隔离级别可以解决大多数问题，但是灵活度较差，为此又提出了悲观锁和乐观锁的概念。

1、悲观锁
悲观锁，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统的数据访问层中实现了加锁机制，也无法保证外部系统不会修改数据。

使用场景举例：以MySQL InnoDB为例
商品t_items表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单（此时该商品无法再次下单），那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。
如果不采用锁，那么操作方法如下：

//1.查询出商品信息
select status from  t_items where id=1;
//2.根据商品信息生成订单,并插入订单表 t_orders 
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_items set status=2;

但是上面这种场景在高并发访问的情况下很可能会出现问题。例如当第一步操作中，查询出来的商品status为1。但是当我们执行第三步Update操作的时候，有可能出现其他人先一步对商品下单把t_items中的status修改为2了，但是我们并不知道数据已经被修改了，这样就可能造成同一个商品被下单2次，使得数据不一致。所以说这种方式是不安全的。

使用悲观锁来解决问题

步骤总结：
1.取消自动提交：set autocommit=0;
2.使用select…for update查询，此时查询出的数据会被锁定，其他事务不能修改该条数据。

在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程，使用悲观锁的原理就是，当我们在查询出t_items信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为t_items被锁定了，就不会出现有第三者来对其进行修改了。需要注意的是，要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。
我们可以使用命令设置MySQL为非autocommit模式：set autocommit=0;
设置完autocommit后，我们就可以执行我们的正常业务了。
具体如下：

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_items where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_items set status=2;
//4.提交事务
commit;/commit work;

上面的begin/commit为事务的开始和结束，因为在前一步我们关闭了mysql的autocommit，所以需要手动控制事务的提交。
上面的第一步我们执行了一次查询操作：select status from t_items where id=1 for update;与普通查询不一样的是，我们使用了select…for update的方式，这样就通过数据库实现了悲观锁。此时在t_items表中，id为1的那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。
需要注意的是，在事务中，只有SELECT … FOR UPDATE 或LOCK IN SHARE MODE 操作同一个数据时才会等待其它事务结束后才执行，而SELECT … 则不受此影响。拿上面的实例来说，当我执行select status from t_items where id=1 for update;后。我在另外的事务中如果再次执行select status from t_items where id=1 for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select status from t_items where id=1;则能正常查询出数据，不会受第一个事务的影响。

Row Lock与Table Lock
使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键或者索引，MySQL 才会执行Row lock(只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。
举例如下：

1、select * from t_items where id=1 for update; 
这条语句明确指定主键（id=1），并且有此数据（id=1的数据存在），则采用row lock。只锁定当前这条数据。 

2、select * from t_items where id=3 for update; 
这条语句明确指定主键，但是却查无此数据，此时不会产生lock（没有元数据，又去lock谁呢？）。 

3、select * from t_items where name='手机' for update; 
这条语句没有指定数据的主键，那么此时产生table lock，即在当前事务提交前整张数据表的所有字段将无法被查询。 

4、select * from t_items where id>0 for update; 或者select * from t_items where id<>1 for update;（注：<>在SQL中表示不等于） 
上述两条语句的主键都不明确，也会产生table lock。 

5、select * from t_items where status=1 for update;（假设为status字段添加了索引） 
这条语句明确指定了索引，并且有此数据，则产生row lock。 

6、select * from t_items where status=3 for update;（假设为status字段添加了索引） 
这条语句明确指定索引，但是根据索引查无此数据，也就不会产生lock。

悲观锁小结
悲观锁并不是适用于任何场景，它也有它存在的一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受。所以与悲观锁相对的，我们有了乐观锁。
2、乐观锁
乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以只会在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做。实现乐观锁一般来说有以下2种方式：

使用版本号
使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
使用时间戳
乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

四、数据库的结构和设计原则

数据库三范式：
第一范式(确保每列保持原子性)；
第二范式(确保表中的每列都和主键相关)；
第三范式(确保每列都和主键列直接相关,而不是间接相关)。

数据库表设计原则

规则1:表必须要有主键。
规则2:一个字段只表示一个含义。
规则3:总是包含两个日期字段:gmt_create(创建日期),gmt_modified(修改日期)，且这两个字段不应该包含有额外的业务逻辑。
规则4:MySQL中，gmt_create、gmt_modified使用DATETIME类型。
规则5:禁止使用复杂数据类型（数组，自定义类型等）。
规则6: MySQL中，拆分表后，附属表id与主表id保持一致。不允许在附属表新增主键字段。
规则7: MySQL中，存在过期概念的表，在其设计之初就必须有过期机制，且有明确的过期时间。过期数据必须迁移至历史表中。
规则8: MySQL中，不再使用的表，必须通知DBA予以更名归档。
规则9: MySQL中，线上表中若有不再使用的字段，为保证数据完整，禁止删除。

定义表的数据类型原则

1.尽可能的要把field定义为Not NULL， mysql比较难优化使用了可空列的查询，它会使索引，索引统计更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理，当可空列被索引时，每条记录都需要一个格外的字节。 即使要在表中存储"没有值"的字段，考虑使用0，特殊字段或者空字符串来代替。

2.datetime与timestamp能保存同样的数据：精确度为秒，但是timestamp使用的空间只有datetime的一半，还能保存时区，拥有特殊的自动更新能力。但是timestamp保存的时间范围要比datetime要小得多。mysql能存储的最细的时间粒度为秒

3.mysql支持很多种别名，如bool,integer,nummeric.

4.float与double类型支持使用标准的浮点运算进行近似计算。 Decimal类型保存精确的小数，在>=mysql5.0，mysql服务器自身进行了decimal的运算，因为CPU不支持直接对它进行运算，所以慢一点。

5.mysql会把text与blob类型的列当成有实体的对象来进行保存。他们有各自的数据类型家族(tinytext,smalltext,text,mediumtext,longtext; blob类似)； mysql对blob与text列排序方式和其他类型有所不同，它不会按照字符串的完整长度来排序。而只是按照max_sort_length规定的若干个字节来进行排序。

6.ip地址，一般会采用varchar(15)列来保存。事实上，IP地址是个无符号的32位整数，而不是字符串。mysql提供了inet_aton()和inet_nota()函数在证书与ip地址之间进行转换。

索引的一些特点

1.聚集索引不仅仅是一种单独的索引类型，而且是一种存储数据的方式。Innodb引擎的聚集索引实际上在同样的结构中保存了B-Tree索引和数据行。当表有聚集索引时，它的数据行实际上保存在索引的叶子上。注意是存储引擎来实现索引。

2.myisam与innodb数据布局：myisam索引树（无论是主键索引还是非主键索引）叶子节点都是指向的数据行，而innodb中聚集索引，主键索引树叶子节点就带得有数据的内容，而非主键索引树中叶子节点指向主键值，而不是数据的位置。

3.mysql有两种产生排序结果的方式：使用文件排序，或者扫描有序的索引。目前只有myisam支持全文索引。

4.myisam表有表级锁；myisam表不支持事务，实际上，myisam并不保证单条命令完成；myisam只缓存了mysql进程内部的索引，并保存在键缓存区内。OS缓存了表的数据；行被紧密的保存在一起，磁盘上的数据有很小的磁盘占用和快速的全表扫描。

5.innodb支持事务和四种事务隔离级别；在mysql5.0中，只有innodb支持外鍵；支持行级锁与mvcc；所有的innodb表都是按照主键聚集的；所有索引（出开主键）都是按主键引用行；索引没有使用前缀压缩，因此索引可能比myisam大很多；数据转载缓慢；阻塞auto_increment，也就是用表级锁来产生每个auto_increment。

MYSQL性能分析

1.mysql提供了一个benchmark(int 循环次数，char* 表达式)； 可以分析表达式执行所花时间。 例如：

select BENCHMARK(10000,SHA1('SQL语句'))

2.mysql有两种查询日志：普通日志和慢速日志。