数据库

蜗牛君

2019-10-20

数据完整性约束

数据完整性约束指的是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

1) 实体完整性：规定表的每一行在表中是惟一的实体(主键唯一)。

2) 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。

3) 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。

4) 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

什么是sql?

SQL：Structured Query Language 结构化查询语言
DQL：Data Query Language 数据查询语，如select * from 表名
DDL：Data Defintion Language 数据定义语言 create、alter、 drop
DML：Data Manipulation Language 数据操纵语言 updata、instert、delete

mysql数据库的隔离级别

数据读取状态

脏读：事务A读取了事务B更新的数据，然后B执行回滚操作，那么A读到的就是脏数据。
不可重复读：事务A多次读取同一个数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一个数据时，结果不一致。
幻读：比如事务A将所有成绩从具体分数改为了ABCDE等级，但是事务B在这个时候插入了一条具体的分数，最后事务A结束后发现还有一条记录没改过来，好像发生了幻觉。抽象来说是事务A在操作的过程中插入了事务B的操作。

==不可重复读侧重于修改，幻读侧重于新增或者删除。解决不可重复读只需要锁住满足条件的行，解决幻读需要锁表。==

MySql的事务隔离级别就是来解决这个问题的

隔离级别	脏读	不可重复读	幻读
读未提交	是	是	是
读已提交	否	是	是
可重复读	否	否	是
串行化	否	否	否

读未提交，连脏读也避免不了，事务A可以读到事务B更新但还未提交的数据。
读已提交，可以解决脏读的问题，但是如果此时另一个事务修改了数据，就会造成不可重复读的问题。

读提交时写操作会锁住行
可重复读，解决了脏读和不可重复读的问题，用的比较多。

可重复读时，当检索条件有索引时，包括主键索引，默认加锁方式是next-key，如果没有索引，更新数据时会锁住整张表。、
串行化，解决了所有问题，但是是表锁，高并发下性能非常差，在实际开发中很少用到。
一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
串行化时读写数据都会锁住整张表

Mysql引擎

InnoDB,MyinsM,MEMORY

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

索引

锁

事务

事务的特性 ACID

原子性（Atomicity)

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
一致性（Consistency）

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。
隔离性（Isolation)

一个事务所做的修改在最终提交以前，对其它事务是不可见的。
持久性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

使用重做日志来保证持久性。

性能优化

数据库的优化方法有很多种，在应用层来说，主要是基于索引的优化。

1. 优化分析

必须熟悉数据库应用程序中的所有SQL语句，从中统计出常用的可能对性能有影响的部分SQL，分析、归纳出作为Where条件子句的字段及其组合方式；在这一基础上可以初步判断出哪些表的哪些字段应该建立索引。
必须了解哪些表是数据操作频繁的表；哪些表经常与其他表进行连接；哪些表中的数据量可能很大；对于数据量大的表，其中各个字段的数据分布情况如何；

2. 索引建立的原则

表的主键, 外键必须要有索引
数据量超过300的表应该有索引；
经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
索引应该建在选择性高(区分度高)的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； ==维护成本较高==

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
频繁进行数据操作的表，不要建立太多的索引；
删除无用的索引，避免对执行计划造成负面影响；

错误案例

任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。
- select * from record where substrb(CardNo,1,4)=’5378’(13秒)
  
  优化: substrb函数对列操作, 不会使用索引 select * from record where substrb(CardNo,1,4)=’5378’(13秒)
- select * from record where amount/30< 1000（11秒）
  
  *优化: * select * from record where amount < 1000*30（< 1秒）
- select * from record where to_char(ActionTime,’yyyymmdd’)=’19991201’（10秒）
  
  优化: select * from record where ActionTime= to_date (‘19991201’ ,’yyyymmdd’)（< 1秒）

3.避免索引失效

去掉 IN, OR

含有”IN”、”OR”的Where子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
尽量去掉 “<>”
尽量去掉 “<>”，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为”OR”方式。

UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;

以上语句由于其中包含了”<>”，执行计划中用了全表扫描（TABLE ACCESSFULL），没有用到state字段上的索引
去掉 IS NULL和IS NOT NULL

Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where子句中的IS NULL和IS NOT NULL。
like字句尽量前端匹配

因为like参数使用的非常频繁，因此如果能够对like子句使用索引，将很高的提高查询的效率。

select * from city where name like ‘%S%’

以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），如果能够修改为：

select * from city where name like ‘S%’

那么查询的执行计划将会变成（INDEX RANGE SCAN），成功的利用了name字段的索引。

4.避免全表扫描

需要注意的是，尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描。

select col1,col2 from tab1 where col1>10，

应该写为： select col1,col2 from tab1 where col1>’10’。

5.为查询缓存优化sql

大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。

你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。

// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

6. EXPLAIN 你的 SELECT 查询

EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

如: EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序

蜗牛君的NoteBook

数据库

数据完整性约束

什么是sql?