SQL补充（事务、视图、索引、数据库设计）

2021/11/2 19:09:36

编程Tag： 视图 查询 数据库 索引 SQL 事务 主键

本文主要是介绍SQL补充（事务、视图、索引、数据库设计），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

事务

概念

​ 事务:事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体, 如果单元中某条SQL语句一且执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。

MYSQL中的存储引擎

1、概念:在mysql中的数据用各种不同的技术存储在文件(或内存)中

2、通过show engines; 来查看mysql支持的存储引擎。

3、 在mysql中用的最多的存储引擎有: innodb,myisam , memory等。其中innodb支持事务，而.myisam、memory等不支持事务

事务的特点

  1. 原子性(Atomicity)
    原子性是指事务是一一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

  2. 一致性(Consistency )
    事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  3. 隔离性(Isolation )
    事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  4. 持久性(Durability )
    持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的使用

  • 以第一个DML语句的执行作为开始
  • 以下面的其中之一作为结束:
    • COMMIT或ROLLBACK语句
    • DDL或DCL语句(自动提交)
    • 用户会话正常结束
    • 系统异常终了
#正常流程
#设置自动提交关闭
SET AUTOCOMMIT=0;
#更新数据
UPDATE emp SET ename = '张三' WHERE ename = '张佩服';
#提交
COMMIT;



#回滚
SET AUTOCOMMIT=0;
UPDATE emp SET ename = '李四' WHERE ename = '张三';
ROLLBACK;

数据库的隔离级别

  • 对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:.

    • 脏读:对于两个事务T1,T2, T1读取了已经被T2更新但还没有被提交的字段.之后,若T2回滚,T1读取的内容就是临时且无效的..
    • 不可重复读:对于两个事务T1, T2, T1读取了一一个字段,然后T2更新了该字段.之后, T1再次读取同一个字段,值就不同了.
    • 幻读:对于两个事务T1,T2, T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行.之后,如果T1再次读取同一个表,就会多出几行.

  • 数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题.

  • 一个事务与其他事务隔离的程度称为隔离级别.数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高，数据一致性就越好,但并发性越弱.

  • 数据库提供的4种事务隔离级别:

    隔离级别 描述
    READ UNCOMMITTED(读未提交数据) 允许事务读取未被其他事物提交的变更，脏读，不可重复读和幻读的问题都会出现.
    READ COMMITED（读已提交数据） 只允许事务读取已经被其它事务提交的变更可以避免脏读，但不可重复读和幻读问题仍然可能出现
    REPEATABLE READ（可重复读） 确保事务可以多次从一个字段中读取相同的值在这个事务持续期间，禁止其他事物对这个字段进行更新.可以避免脏读和不可重复读，但幻读的问题仍然存在.
    SERIALIZABLE（串行化） 确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作. 所有并发问题都可以避免，但性能十分低下.

  • Oracle支持的2种事务隔离级别: READ COMMITED,SERIALIZABLE。Oracle 默认的事务隔离级别为: READCOMMITED

  • Mysql支持4种事务隔离级别.Mysql默认的事务隔离级别为:REPEATABLE READ

在MySQL中设置隔离级别

●每启动一个mysql程序,就会获得一个单独的数据库连接.每个数据库连接都有一个全局变量@@tx_isolation,表示当前的事务隔离级别.

●查看当前的隔离级别: SELECT @ @tx_ isolation;

●设置当前mySQL连接的隔离级别:

  • set session transaction isolation level read committed;

● 设置数据库系统的全局的隔离级别:，

  • set global transaction isolation level read committed;

视图

视图的概念

  • 视图: MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

    可以从某个查询内部、某个存储过程内部，或者从另一个视图内部来使用视图。通过向视图添加函数、join 等等，我们可以向用户精确地提交我们希望提交的数据。

  • 应用场景:

    • 多个地方用到同样的查询结果
    • 该查询结果使用的sql语句较复杂

  • 示例

    #创建视图表
CREATE VIEW emp_view AS SELECT
a.ename,
a.deptno,
b.dname 
FROM
	emp a
	INNER JOIN dept b ON a.deptno = b.deptno 
WHERE
	a.deptno = 30;
	
#查看
SELECT * FROM emp_view;

#删除视图
DROP VIEW emp_view;

#更新视图
CREATE OR REPLACE VIEW emp_view AS SELECT
a.ename,
a.deptno,
b.dname,
b.loc
FROM
	emp a
	INNER JOIN dept b ON a.deptno = b.deptno 
WHERE
	a.deptno = 30;

#可以通过视图修改原表的值
UPDATE emp_view SET ename = 'JACK' WHERE ename = 'WARD';

视图的优点

  • 重用sql语句
  • 简化复杂的sql操作，不必知道它的查询细节
  • 保护数据，提高安全性

注意点

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。
包含以下关键字的sql语句:分组函数、distinct、 group by、having、 union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表

索引

概念

​ 索引是帮助MySQL高效检索数据的数据结构，包含了对数据表中数据记录的引用指针。

​ SQL索引在数据库优化中占有一个非常大的比例， 一个好的索引的设计，可以让你的效率提高几十甚至几百倍。

​ 查询索引

show index from 表名

分类

存储引擎的分类

（1） 主键索引，在创建主键的时候默认创建的主键索引。

（2） 普通索引，在mysql中创建的索引默认就是普通索引。

CREATE INDEX idx_索引名 ON 表名(添加索引的列)

（3）复合索引，在多列上创建的索引。

CREATE INDEX idx_索引名 ON 表名(添加多个索引的列)

（4）唯一索引，列的值不能重复，主键索引不能null，唯一索引可以为null，主键可以与外键关联，但是唯一索引不能。

create unique index 索引名称 on 表名(列名)

​ (5) 全文索引，全文索引将有关重要的词及其位置的信息存储在数据库表的一列或多列中。全文索引是一种特殊类型的基于标记的功能性索引。

CREATE FULLTEXT INDEX 索引 ON 表名(列名_可以多行)

​ 文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.

​ 如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column lick '%xxxx%' 这样做会让索引失效

存储机制的分类

​ SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间

​ 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致（有序），聚集索引就是在数据库被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针，其实就有点和堆栈差不多的感觉了

1、在InnoDB中，索引和数据是一起存放的（.ibd文件）；InnoDB存储分为两个文件，.frm文件存储表结构文件，.ibd存储的是数据和索引的文件。
InnoDB主键采用的是聚集索引，主键索引叶子节点存储的是整个数据行的所有数据，非主键索引的叶子节点存储的是主键的值。
2、聚集索引：聚簇索引数据的物理存放顺序与索引顺序是一致的，一个表只能有一个聚集索引
3、非聚集索引：是指数据行的物理顺序与索引的顺序不同，一个表可以有多个非聚集索引
4、在InnoDB中，会将主键作为聚集索引
5、除聚集索引之外的所有索引都称为辅助索引
6、聚集索引B+树的叶子节点中存放了行数据，可以直接在聚集索引中查找到想要的数据
7、辅助索引B+树中的叶子节点中存放了主键信息，需要先在辅助索引中查找到主键，然后再根据主键在聚集索引中查找想要的数据（如果使用了覆盖索引，则不需要回表，直接通过辅助索引就可以查找到想要的数据）
8、覆盖索引：是指select查询的数据只需要在索引中就能取得，而不必读取数据行，换句话说就是，查询列要被所建的索引覆盖




1、在MyISAM中，索引（.MYI 文件）和数据（.MYD 文件）是分开存放的；MyISAM在磁盘上会存储为3个文件,.frm文件为存储表结构文件，.myd存储数据文件，.myi存储索引文件。
MyISAM的索引是非聚集索引，索引和数据是分开存储的，MyISAM的叶子节点存储的是数据所在的地址，而不是数据。
2、主键索引和辅助索引B+树的节点结构完全相同，只是存储的内容不同
3、主键索引B+树的叶子节点中存放了主键信息以及指向数据的地址，可以直接通过主键索引查找到想要的数据
4、辅助索引B+树的叶子节点中存放了辅助键信息以及指向数据的地址，可以直接通过辅助索引查找到想要的数据，无须访问主键的索引树

索引的使用

explain命令用于查看sql执行时是否使用了索引，是优化SQL语句的一个非常常用而且非常重要的一个命令, key字段表示查询使用到的索引

（1）in走索引

  • in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内

（2）范围查询走索引，但是范围列后面的列无法用到索引

（3）模糊查询只有左前缀使用索引

a% %A% %A

（4）反向条件不走索引 != 、 <> 、 NOT IN、IS NOT NULL

（5）对条件计算(使用函数或者算数表达式)不走索引

（6）查询时必须使用正确的数据类型

  • 如果索引字段是字符串类型，那么查询条件的值必须使用引号，否则不走索引

（7）or 使用索引和不使用索引的情况

  • or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的

（8）最左匹配

  • 例如，我们有这样一个索引
name  age：组合索引

必须要先匹配name，才能匹配到age。这个我们就被称为最左匹配

例如下面的几条SQL语句，那些语句不会使用组合索引

where name = ? and age = ?where name = ?where age = ?where age = ? and name = ?

根据最左匹配原则，我们的 3 不会使用组合索引的。

那为什么4的顺序不一样，也会使用组合索引呢？

其实内部的优化器会进行调整，例如下面的一个连表操作

select * from tb1 join tb2 on tb1.id = tb2.id

其实在加载表的时候，并不一定是先加载tb1，在加载tb2，而是可能根据表的大小决定的，小的表优先加载进内存中。

以下是最左前缀使用索引的情况： （a,b,c)

语句 索引是否发挥作用
where a=3 是，只使用了a
where a=3 and b = 5 是，使用了a、b
where a=3 and b = 5 and c = 10 是，使用了a、b、c
where b=3 and c = 10 否
where a=3 and c=4 是，仅使用了a
where a=3 and b>10 and c=7 是，使用了a，b
where a=3 and b like ‘%XXX%’ and c = 7 使用了a

建立索引的原则：

  1. 定义主键的数据列一定要建立索引。

  2. 定义有外键的数据列一定要建立索引。

  3. 对于经常查询的数据列最好建立索引。

  4. 对于需要在指定范围内的快速或频繁查询的数据列;

  5. 经常用在WHERE子句中的数据列。

  6. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

  7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

  8. 对于定义为text、image和bit的数据类型的列不要建立索引。

  9. 对于经常存取的列避免建立索引

  10. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

  11. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

练习

#为学生表和成绩表添加适合的索引，并查看索引
CREATE INDEX studentNo_INDEX ON student(studentNo);
CREATE INDEX studentID_INDEX ON studenttifo(studentID);
SHOW INDEX FROM student;
SHOW INDEX FROM studenttifo;
#按学生名和年级编号组合查询
CREATE INDEX studenName_gradeID_INDEX ON student(studentName,gradeID);
#学生身份证号是唯一的
CREATE UNIQUE INDEX IDcard_INDEX ON student(IDcard);

数据库设计

软件开发周期

需求分析阶段：分析客户的业务和数据处理需求
概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整
详细设计阶段：应用三大范式审核数据库结构
代码编写阶段：物理实现数据库，编码实现应用
软件测试阶段：……
安装部署：……

ER图

Entity-Relation

image-20211101193515529

实体之间的关系：

一对一

一对多

多对一

多对多

通过pd工具，来设计数据库模型图。

数据库设计三范式：

第一范式，每列具备原子性，不能分割。

第二范式，在满足第一个方式基础上，非主键字段必须完全依赖主键字段，不能存在部分依赖。（针对复合主键而言）

第三方式，在满足第二范式的基础上，非主键字段与主键字段不能存在传递依赖，必须直接依赖。

规范化与性能的关系

为满足某种商业目标，数据库性能比规范化数据库更重要
通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列（如成绩总分），以方便查询
在数据规范化同时，要综合考虑数据库的性能



这篇关于SQL补充（事务、视图、索引、数据库设计）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程