MySQL

Basis
Basis
Catalogue
  1. 1. 设计
    1. 1.1. 三大范式
    2. 1.2. 存储引擎
      1. 1.2.1. 简介
      2. 1.2.2. 数据库锁
      3. 1.2.3. 事务操作
  2. 2. 常规表操作
    1. 2.1. 读操作
    2. 2.2. 表复制
    3. 2.3. 临时表
    4. 2.4. 内存表
    5. 2.5. 关联查询
      1. 2.5.1. full [outer] join
      2. 2.5.2. union
      3. 2.5.3. 总结
    6. 2.6. 子查询
      1. 2.6.1. where字句常用方式
      2. 2.6.2. 分组函数与having
    7. 2.7. JDBC
  3. 3. 索引
    1. 3.1. 先快速看几篇文章
    2. 3.2. 索引的作用
    3. 3.3. 使用索引需要注意
    4. 3.4. 什么情况下设置了索引但无法使用
    5. 3.5. 索引的一种分法
  4. 4. 事务
    1. 4.1. 数据库事务的四大特性(简称ACID)
    2. 4.2. 解决高并发

设计

三大范式

范式越高，数据冗余越少，但是增加查询复杂度，表链接时效率更低。范式低，数据冗余高，但是查询会更快。

  • 第一范式，确保属性的原子性，如“地址”属性应该再细分为国家，省，市，区等；
  • 第二范式，如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式；
  • 第三范式，表中每列都与主键直接相关，如订单记录中，虽说顾客编号和顾客名称都与主键（订单编号）相关，满足第二范式，但顾客名称不是直接相关；

存储引擎

简介

  • MyISAM
    这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：
    • 静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
    • 动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
    • 压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。
      但是，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。
  • MyISAM Merge引擎
    这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
  • InnoDB
    InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。
  • Memory(heap)
    这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。
  • archive
    这种类型只支持select和insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。

MySql支持的表类型不止上面几种。不同的引擎对于索引有不同的支持，如Innodb和MyISAM默认的索引是Btree索引（之后版本B+树）；而Memory默认的索引是Hash索引（哈希值可能重复，所以检索效率未必就高）

数据库锁

  • 锁粒度
    MySQL有三种锁的级别：页级、表级、行级。
    表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
    页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
    行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

  • 常见存储引擎采用锁机制
    MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
    BDB(BerkeleyDB)存储引擎采用的是页面锁（page-level locking），但也支持表级锁；
    InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

  • 锁机制

    • 共享锁
      锁粒度是行或者元组（多个行），读操作
    • 排它锁
      锁粒度是行或者元组（多个行），写操作
    • 说明 e.g.
      如果事务t1获取了一个元组的共享锁，事务t2还可以立即获取这个元组的共享锁，但不能立即获取这个元组的排它锁（必须等到t1释放共享锁之后）。
      如果事务t1获取了一个元组的排它锁，事务t2不能立即获取这个元组的共享锁，也不能立即获取这个元组的排它锁（必须等到t1释放排它锁之后）。

  • 锁的应用
    共享锁、排它锁、意向共享锁、意向排它锁相互之前都是有兼容/互斥关系的，可以用一个兼容性矩阵表示(y表示兼容，n表示不兼容):

    1
    2
    3
    4
    5
            X    S    IX    IS
    X       n     n    n     n
    S       n     y    n     y
    IX      n     n    y     y
    IS      n     y    y     y

兼容性矩阵为什么是这个样子的？
X和S的相互关系在上文中解释过了，IX和IS的相互关系全部是兼容，这也很好理解，因为它们都只是“有意”，还处于YY阶段，没有真干，所以是可以兼容的；

事务操作

  • InnoDB
    支持事务处理，InnoDB写操作快，update快，外键操作效率高
  • MyISAM
    不支持事务操作，读取快，count操作快（因为保存了行数）。

比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

1
2
3
delete from userinfo where ~~~
delete from mail where ~~
delete from article where~~

如果没有事务处理，在你删除的过程中，假设出错了，只执行了第一句，那么其后果是难以想象的！
但用事务处理，像这样类似的一系列操作，要么全部正确执行，要么全部不执行。事务处理就是来做这个事情的。
参考：http://blog.csdn.net/z702143700/article/details/46048495
具体例子：https://segmentfault.com/q/1010000002952450

Tips

  • 使用表锁：
  1. 当事务需要更新大部分数据时，表又比较大，如果使用默认的行锁，不仅效率低，而且还容易造成其他事务长时间等待和锁冲突。
  2. 事务比较复杂，很可能引起死锁导致回滚。
  • 关于死锁：
    • MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。
    • 避免死锁
  1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
  2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
  3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
  4. 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

常规表操作

读操作

  • 脏读（Dirty Read)
    脏读意味着一个事务读取了另一个事务未提交的数据,而这个数据是有可能回滚。
  • 不可重复读(Unrepeatable Read)
    也称为虚读，不可重复读意味着，在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。
    例如：事务B中对某个查询执行两次，当第一次执行完时，事务A对其数据进行了修改。事务B中再次查询时，数据发生了改变
  • 幻读(phantom read)
    幻读,是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

表复制

  • 类型1,table2->table1，前提：table1需存在

    1
    insert into table1(field1,field2) select field1,field2 from table2;

  • 类型2,table2+table3->table1，前提：table1需存在

    1
    insert into table1(field1,field2) select * from(select field1,field2 from table2 join table3) as table23;

  • 类型3，table2->table1，前提：table1不能存在

    1
    select field1,field2 into table1 from table2;

临时表

作用：专用性和私有性，速度上和效率上更高，使用时需先建表，在从大表中抽取数据，在连接关闭后会自动销毁。

1
2
3
4
5
create temporary table table1(
    id Integer Not null Auto increasement,
    name varchar(255) not null,
    primary key(id)
);

内存表

作用：数据在内存中，表结构持久化到磁盘，show tables时可见，其执行引擎为memory，一般较少用。

关联查询

mysql默认的join是inner join。


此处输入图片的描述

### cross join
交叉连接，以例子说明：
1
select * from table1 cross join table2;


笛卡尔乘积，从table1循环取出每一条记录，每条都与table2匹配，都保留，实际场景中较少用。
### inner [outer] join
(有无outer都一样，下同)
内连接，根据on匹配，相当于求交集（多个匹配则相乘），没有on则求并集，与交叉连接一样；
1
SELECT * FROM TableA INNER JOIN TableB ON TableA.name=TableB.name;



取图中结果,一行有四个属性

### left [outer] join
以左表为主表，从右表中寻找匹配的记录进行拼接，没有匹配的为null，right join同理。
1
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name；



此处输入图片的描述

full [outer] join

产生A和B的并集。对于没有匹配的记录，则会以null做为值。

1
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name;


此处输入图片的描述

可以通过is NULL将没有匹配的值找出来：

1
2
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableA.id IS null OR TableB.id IS null

union

联合查询

1
2
3
4
# 求并集
select * from table1 union select * from table2;
# 求叠加（记录数是二者的总和）
select * from table1 union all select * from table2;

总结


此处输入图片的描述

子查询

1
2
3
4
select * from table1 where f1=(select * from table2 where f2="xxx");
# 等价于
select * from table1 where f1 in (select * from table2 where f2="xxx");
select * from table1 where exists (select * from table2 f1>=200);

where字句常用方式

  • where age in(13,14);
  • where date betweent “21” and “24”;
  • where studentName like ‘小明%’；

分组函数与having

一般而言，where能用，having也能用，但having效率低，一般只用在where不能用的场合，诸如聚合操作(sum,min,max,avg,count)作用后产生的列，where不能用，现在就只能使用having，如下场景只能用having:

1
2
3
4
select field1,count(*) from table1 having count(*) > 4;
select filed1,count(*) as num from table1 having num > 4;
# 当然这样是不可以的
select goods_name,goods_number from sw_goods having goods_price > 100 //报错！！！因为前面并没有筛选出goods_price 字段

JDBC

JDBC提供了Statement、PreparedStatement 和 CallableStatement三种方式来执行查询语句，其中:

  • Statement 用于通用查询
  • PreparedStatement 用于执行参数化查询
  • CallableStatement则是用于存储过程。

继承关系图

索引

先快速看几篇文章

http://www.cnblogs.com/cy163/archive/2008/10/27/1320798.html

Mysql有BTree索引和Hash索引，而oracle除了这两种选择，还有Bitmap位图索引。

索引的作用

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行，还需要考虑每次读入数据页的IO开销。
而如果采取索引，则可以根据索引指向的页以及记录在页中的位置，迅速地读取目标页进而获取目标记录。

使用索引需要注意

  • 只对WHERE和ORDER BY需要查询的字段设置索引，避免无意义的硬盘开销；
  • 组合索引支持前缀索引；
  • 更新表的时候，如增删记录，MySQL会自动更新索引，保持树的平衡；因此更多的索引意味着更多的维护成本;
  • 可通过explain语句查看SQL语句的分析结果;

什么情况下设置了索引但无法使用

① 以“%”开头的LIKE语句，模糊匹配,如:

1
2
3
like %keyword    #索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。
like keyword%    #索引有效
like %keyword%   #索引失效，也无法使用反向索引。

② OR语句前后没有同时使用索引，需要将where语句的字段也加上order，如：
假设已经建立以下索引：

1
2
CREATE INDEX mytable_categoryid_userid_adddate
  ON mytable (category_id,user_id,adddate);

进行查找时应：

1
2
3
SELECT * FROM mytable
  WHERE category_id=1 AND user_id=2
　　ORDER BY category_id DESC,user_id DESC,adddate DESC;

③ 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；

1
2
SELECT * FROM `mytable` WHERE `a`='12345' #正确版本
SELECT * FROM `mytable` WHERE `a`=12345 #错误版本，虽可查到，但变慢

索引的一种分法

  • index —-普通的索引,数据可以重复
  • fulltext—-全文索引，用来对大表的文本域(char，varchar，text)进行索引。语法和普通索引一样。
  • unique —-唯一索引,唯一索引,要求所有记录都唯一
  • primary key —-主键索引,也就是在唯一索引的基础上相应的列必须为主键

事务

数据库事务的四大特性(简称ACID)

  • 原子性(Atomicity)
    事务的原子性指的是，事务中包含的程序作为数据库的逻辑工作单位，它所做的对数据修改操作要么全部执行，要么完全不执行。这种特性称为原子性。
    例如银行取款事务分为2个步骤(1)存折减款(2)提取现金。不可能存折减款，却没有提取现金。2个步骤必须同时完成或者都不完成。
  • 一致性(Consistency)
    事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。
    例如完整性约束a+b=10，一个事务改变了a，那么b也应随之改变。
  • 分离性(亦称独立性Isolation)
    分离性指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来，不被其它企图进行修改的事务看到。假如并发交叉执行的事务没有任何控制，操纵相同的共享对象的多个并发事务的执行可能引起异常情况。
  • 持久性(Durability)
    持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。即一旦一个事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，即对已提交事务的更新能恢复。持久性通过数据库备份和恢复来保证。

解决高并发

  • 分库，分表，分布式，增加二级缓存等；
    • 水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力。
    • 读写分离策略：极大限度提高应用中Read数据的速度和并发量。无法解决高写入压力。
  • 解决高并发锁的争用
    • 行锁，但似乎高并发情况下效率会下降较多；
    • 换用Redis，有个watch命令（乐观锁），监听key是否被改变，而一般真实环境下Redis又是单进程的，command是one by one的。但是watch用在分布式操作上却是有用的。
Comments