数据库知识点

hushnow_hy

已于 2023-02-10 09:37:18 修改

阅读量119

点赞数 1

文章标签：数据库

于 2023-02-10 09:31:47 首次发布

本文链接：https://blog.csdn.net/weixin_44430847/article/details/128963922

版权

1.事务:

原子性、一致性、隔离性、持久性

2.基础语法

1.DDL：数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

create(创建)
               create table 表名 <create table p (pno char(6), pname char(30) primary key (pno));>
               primary key<主键>
               foreign key() references 表名(字段)<关联外键>
           drop(删除)
              drop table 表名
           alter(修改)
              alter table 表名 <alter table s add c char(5);>
              add 添加字段
              modify 修改字段
              drop 删除字段
              change 改变字段名称 <alter table s change a a1 int(5);>
                 change 和 modify 都可以修改表的定义，不同的是 change 后面需要写两次列名，不方便。但是change 的优点是可以修改列名称，modify 则不能。
               rename 修改表名

2.DML：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）
           insert 插入语句 <insert into 表名(字段1,字段2,字段3,......) values ('值1','值2','值3',......)>
               也可以不指定字段,但是values后面的顺序要和表的字段顺序和个数一致,对于含可空字段非空但是含有默认值的字段自增字段,可以不用在insert后的字段列表中出现
           delete 删除语句 <delete from 表名 where 条件>
           update 修改语句 <update 表名 set 字段名 = '新值' where 字段名 = '旧值'>
           select 查询语句

3.DCL：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

           1.可以为不存在的数据库授权。
           2.如果grant命令标识的用户不存在，它将被创建。
           3.表级grant只支持如下权限类型：alter、create、createview、delete、drop、grant、index、insert、references、select、show view、update.
           4.列级grant只支持以下权限类型：insert、select、update.
           5.在grant命令中引用数据库名和主机名时，支持_和%通配符。因为_字符在Mysql数据库名中也是合法字符，所以如果在grant中用到，需要用反斜线进行转义。

3.存储引擎

1.MyISAM：不支持事务, 不支持外键约束, 只支持全文索引,数据文件和索引文件是分开保存的, 访问速度快,对事物完整性没有要求, 适合查询、插入为主的应用场景,表级锁定形式，数据在更新时锁定整个表,数据库在读写过程中相互阻塞：串行操作，按照顺序操作，每次在读或写的时候会把全表锁起来
               会在数据写入的过程阻塞用户数据的读取（读或写无法同时进行）
               也会在数据读取的过程中阻塞用户的数据写入
               特性：数据单独写入或读取，速度过程较快且占用资源相对少
           支持3种不同的存储格式
               静态(固定长度)表：静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。
               动态表：动态表包含可变字段(varchar) 记录不是固定长度的,这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。
               压缩表：压缩表由 myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。
           适用场景
               公司业务不需要事务的支持
               单方面读取或写入数据比较多的业务
               使用读写并发访问相对较低的业务
               数据修改相对较少的业务
               对数据业务一致性要求不是非常高的业务
               服务器硬件资源相对比较差

2.InnoDB: 支持事务, 支持4个事务隔离级别
               MySQL 从5.5.5版本开始,默认的存储引擎为 InnoDB 5.5以前是 MySIAM
               读写阻塞与事务隔离级别相关
               能非常高效的缓存索引和数据
               表与主键以簇的方式存储
               支持分区、表空间，类似 oracle 数据库
               支持外键约束，5.5 前不支持全文索引，5.5 后支持全文索引
               适合对硬件资源要求还是比较高的场合
               行级锁定，但是全表扫描仍然会是表级锁定（select ）update table set a=1 where user like '%lic%';
InnoDB中不保存表的行数,如执行select count(*) from table时,InnoDB需要扫描一遍整个表来计算有多少行,但是MySIAM知识需要简单的读出保存好的行数即可,需要注意的是,当 count(*) 语句包含where条件时,MySIAM也需要扫描整个表
               对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立组合索引
               清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表（truncate）
           适用场景
               业务需要事务的支持
               行级锁定对高并发有很好的适应能力，但需要确保查询时通过索引来完成
               业务数据更新较为频繁的场景，如论坛、微博等
               业务数据一致性要求较高，例如银行业务
               硬件设备内存较大，利用 InnoDB 较好的缓存能力来提高内存利用率，减少磁盘 IO 的压力

4.企业选择存储引擎的依据

        MyISAM（表级锁）：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
       InnoDB（行级锁）：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
       如何选择搜索引擎
           1. 支持的字段和数据类型
               所有引擎都支持通用的数据类型
               但不是所有的引擎都支持其它的字段类型,如二进制对象
           2. 锁定类型
               表锁定:MyISAM支持
               行锁定:InnoDB
           3. 索引的支持
               建立索引在搜索和恢复数据库中的数据时能显著提高性能
               不同的存储引擎提供不同的制作索引的技术
               有些存储引擎根本不支持索引
           4. 事务处理的支持
               提高在向表中更新和插入信息期间的可靠性
               可根据企业业务是否要支持事务选择存储引擎
       总的来说，业务场景如果并发量大，建议使用 InnoDB；如果单独写入、插入或者读取操作较多，建议使用 MyISAM。

5.存储引擎的查看与修改

        1.查看系统支持的存储引擎
           show engines;
       2.查看表使用的存储引擎
           1. show table status from 库名 where name = '表名'
           2. use 库名
           show create table 表名
       3.修改存储引擎
           use 库名
           alter table 表名 engine = MyISAM;

6.MySQL 死锁及解决方案

锁类型
           MySQL常用存储引擎的锁机制
               MyISAM：表级锁定
               InnoDB：行级锁定
          锁特点
               表级锁：开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
               行级锁：开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高
          各种锁的使用场景
               表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用
               行级锁则更适合于有大量按索引条件并发更新数据,同时又有并发查询的应用,如一些在线事务处理系统。
  产生死锁的原因
           所谓死锁<DeadLock>：是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。
           死锁的关键在于:两个或以上Session加锁的顺序不一致
           那么对应的解决死锁的问题的关键就是让不同的session加锁有次序