数据库
事务
- 事务的概念:可以看作一个单元或一系列SQL语句的集合
- 事务的四大特性:原子性,一致性、隔离性、持久性
- 原子性:所有的操作要么都被执行,要么都不被执行
- 一致性:每个单独的事务,即不和其他事务发生并发的条件下,必须保证数据库的一致性
- 隔离性:事务都是独立的,它不受其它并发事务的影响
- 持久性:即使在事务存入磁盘之前发生故障,事务处理的结果也能被永久地保持住
- 数据库事务常用的三条语句:
- transaction begin
- transaction commit
- rollback transaction
数据库的隔离级别
-
脏读(读未提交):事务B读取了事务A还没有提交的数据(WR冲突)
事务B读取了事物A已经修改,但还没有提交的数据。
-
不可重复读(读提交):两次事务读取的数据不一致(RW冲突)
-
幻读(可重复读):事务A修改了数据,事务B也修改了数据(WW冲突)
-
严格的两阶段加锁(2PL):
- 如果事务T希望读某个数据,那么应该首先申请该对象的共享锁(如果已经有排他锁也可以读数据)。
- 事务结束时,所有的锁都会被释放掉。
数据库的范式
函数依赖:如果两个元组在属性X上相同,那么它们在属性Y上也相同。
-
第一范式:数据库表中每一列都是不可再分的基本数据列,即同一列中不能有多个值。
- 每个字段只能存放单一值
- 每笔记录都能利用唯一的主键加以识别
-
第二范式:第二范式建立在第一范式的基础上,非主属性完全依赖于码(消除部分依赖),如:
( N a m e , S u b j e c t ) → D e p a r t m e n t (Name, Subject)\rightarrow Department (Name,Subject)→Department,但是 N a m e → D e p a r t m e n t Name\rightarrow Department Name→Department
-
第三范式:如果对 R R R上的每个函数依赖 X → A X \rightarrow A X→A,下面的一个条件成立(消除传递依赖):
- A ∈ X A \in X A∈X,即 X → A X \rightarrow A X→A是平凡依赖
- X X X是超码
- A A A是 R R R的码的一部分
-
BC-范式:对于每个关系模式 R R R上成立的函数依赖 X → A X\rightarrow A X→A,下面的条件中有一个成立
- A ∈ X A \in X A∈X是平凡依赖
- X X X是一个超码
MySQL
-
Explain语句能够定位执行效率差的SQL,同时知道SQL的执行计划(如全表扫描还是索引扫描)
type: join的类型,避免全盘扫描(ALL)
possible_key:可能用到的索引
key:实际用到的索引
rows:估计每次需要扫描的行数
-
profile用来分析sql的分布消耗情况
-
show status语句检查table_locks_waited(值较高则存在比较严重的表级锁争用情况)和table_locks_immediate来查看锁的使用情况。
-
MySQL在写时加锁实现序列化。
悲观锁和乐观锁
- 悲观锁:每次修改数据的时候,都会担心别的线程会修改数据,所有每次在操作的时候都会给数据加锁。
- 乐观锁:默认别的线程不会修改数据,所以不会上锁,每次在修改数据的时候,去判断别的程序在此期间有没有修改数据。
- 在写操作较多的时候,使用悲观锁,在读操作较多的时候,使用乐观锁。
- 排他锁:别的程序既不能修改,也不能读取数据。
- 共享锁:别的程序不能修改数据,但是可以读取数据。
建索引的原则
- 最左前缀匹配原则,如 a = 1 a=1 a=1, b = 1 b=1 b=1, c > 1 c>1 c>1, d = 1 d=1 d=1,MySQL会一直向右检索,直到遇到范围查询,如 d = 1 d=1 d=1是用不到索引的。
- =和in可以乱序,如 a = 1 , b = 1 , c = 1 a=1,b=1,c=1 a=1,b=1,c=1建立索引可以任意顺序,MySQL的查询优化器可以帮助优化为可查询形式。
- 尽量选择区分度高的列作为索引,区分度的计算公式=列中不同元素/总元素。
- 索引列不能参与计算,否则检索时所有元素都需要参与计算,成本很高。
- 尽量扩展索引,不要新建索引。
- 在经常使用where子句的列上建立索引,加快条件判断的效率。
- 复合索引,一个索引里面包含多个字段,但是不一定每次检索都是用所有的索引
索引的优点
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度。
- 可以加快表和表之间的连接。
B树和B+树
- B+树的非叶子结点不保存关键字数据指针,只进行数据索引,层数更浅,效率更高。
- B+树保留了父节点所有关键字记录的指针,数据必须要到叶节点才能访问,检索过程稳定。
- B+树全节点遍历更快。
MySQL的优化
-
MySQL语句优化
- 使用limit对查询结果进行优化
- 避免select *,将需要的字段列出来
- 使用连接来代替子查询
- 拆分大的delete或insert语句
-
选择合适的数据类型
- 使用可存下数据的最小数据类型
- 使用简单的数据类型
- 使用合理的字段长度
- 尽可能使用NOT NULL定义字段
- 尽量少用text
-
选择合适的索引列
- 查询频繁的列,在where,group by,order by, on从句中出现的列
- 长度小的列
- 离散度大的列
-
SHOW [SESSION|GLOBAL] STATUS LIKE ‘%STATUS_NAME%’;
- com_select:查询次数
- connections:连接次数
- uptime:数据库运行时间
- handler_read:索引的使用情况
显示系统变量:SHOW VARIABLES LIKE ‘%Variable_name%’;
显示存储引擎状态:SHOW ENGINE INNODB STATUS;
EXPLAIN :
- type:连接状况
- possible_keys:可能用到的索引
- key:实际使用的索引
PROFILE:
- SET PROFILING=ON;
- SHOW profiles; 查看SQL语句的运行时间
- SHOW processlist; 查看所有用户的当前连接
- PROCEDURE ANALYSE() 对现有表给出优化建议
- OPTIMIZE TABLE table_name; 回收闲置的数据库空间
- REPAIR TABLE table_name; 修复被破坏的表
- CHECK TABLE table_name; 检查表是否有错误
MySQL引擎
MyISAM | InnoDB |
---|---|
不支持事务 | 支持事务 |
不支持外健 | 支持外健 |
用变量保存全表的行数,不需要扫描全表 | 不保存具体的行数,需要扫描全表 |
支持表锁 | 支持表锁和行锁 |
支持全文索引,查询效率较高 | 不支持全文索引 |
扫描全表行数的操作:
select count(*) from table
表锁,每次操作都要锁住整张表,不会发生死锁,但并发性差;
行锁,每次操作锁住一行,有发生死锁的可能,但是发生锁冲突的概率低,并发性好。
InnoDB基于索引来执行行锁,否则执行表锁
MySQL 练习
-
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
SELECT p.FirstName, p.LastName, addr.City, addr.State FROM Person p LEFT JOIN Address addr ON p.PersonId = addr.PersonId
LEFT JOIN 返回左列中所有记录和右列中连结记录,即右列中可能会有NULL
-
编写一个 SQL 查询,获取
Employee
表中第二高的薪水(Salary) 。SELECT MAX(E.Salary) as SecondHighestSalary FROM Employee E WHERE E.Salary < (SELECT MAX(EM.Salary) FROM Employee EM)
-
给定
Employee
表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。SELECT p.Email AS Email FROM Person p GROUP BY p.Email HAVING COUNT(*) > 1
-
某网站包含两个表,
Customers
表和Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。SELECT c.Name as Customers FROM Customers c WHERE c.Id not in (SELECT O.CustomerId FROM Orders O, Customers CC WHERE O.CustomerId = CC.Id)
-
编写一个 SQL 查询,来删除
Person
表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。DELETE P1 FROM Person P1, Person P2 WHERE P1.Email = P2.Email and P1.Id > P2.Id
-
给定一个
Weather
表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。SELECT W1.Id FROM Weather AS W1, Weather AS W2 WHERE DATEDIFF(W1.RecordDate, W2.RecordDate) = 1 AND W1.Temperature > W2.Temperature
-
有一个
courses
表 ,有: student (学生) 和 class (课程)。请列出所有超过或等于5名学生的课。
SELECT C.class FROM Courses C GROUP BY C.class HAVING COUNT(DISTINCT(C.student)) >= 5
-
给定一个 salary 表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。
UPDATE Salary S SET S.sex = ( CASE WHEN S.sex = 'm' THEN 'f' WHEN S.sex = 'f' THEN 'm' END)
如何选择引擎
- 当需要支持事务的时候,选择InnoDB
- 如果表中大部分都是只读查询,选择MyISAM,如果表中有频繁的读写操作,选择InnoDB.
- 系统崩溃以后,MyISAM的恢复比较困难,看接收程度。