xplly'Blog

切换主题

标签
数据库
字数
8370 字
阅读时间
34 分钟

一、基础知识 ​

1.1 事务 ​

  • 概念：

    事务是一组要么同时执行成功、要么同时失败的一组对数据库的操作，是数据库操作的执行单元。

  • 事务提交方式

    自动提交和手动提交

    mysql是自动提交方式，oracle默认为手动提交，

    查看提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交

    设置提交方式：set @@autocommit = 0;

  • 事务四个特性（ACID）

    atomicity（原子性）表示一个事务内的所有操作是一个整体，要 么全部成功，要么全失败；
    consistency（一致性）在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
    isolation（隔离性）不同的事务操作之间相互隔离
    durability（持久性）在事务完成以后，该事务对数据库所作的更改便持久化保存在数据库中，不会被回滚

  • 事务隔离四个隔离级别

    读取未提交（Read Uncommitted)未提交读隔离级别也叫读脏，就是事务可以读取其他事务未提交的数据
    可引起 脏读，不可重复读，幻读
    读取已提交(Read Committed)在其它数据库系统比如SQL server，oracle默认的隔离级别就是提交读，该隔离级别就是在事务未提交之前所做的修改其他事务是不可见的
    可引起 不可重复读，幻读
    可重复读（Repeatable Read)保证同一事务中多次相同的查询的结果是一致的，mysql默认级别
    可引起幻读
    序列化（serializable)保证读取的范围内没有新的数据插入

  • 数据库默认处理级别

    sql
    --mysql
--默认的是事务处理级别是repeatable-read  可重复读

--查看当前会话的隔离级别
select  @@tx_isolation;
-- 查看系统当前的隔离级别
select @@global.tx_isolation;
-- 设置当前会话隔离级别
set session transaction isolatin level repeatable read;
-- 设置系统当前隔离级别
 set global transaction isolation level repeatable read;
 
-- Oracle
-- 支持read committed 和serializable 两种事务隔离级别 默认的是read committed

  • 并发访问问题

    丢失数据修改当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题
    脏读事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被除撤消，而此时T1把已修改过的数据又恢复原值，T2读到的数据与数据库的数据不一致，则T2读到的数据就为“脏”数据，即不正确的数据
    不可重复读当事务 A 第一次读取事务后,事务 B 对事务 A 读取的数据进行修改,事务 A 中再次读取的数据和之前读取的数据不一致,
    幻读事务t1按一定条件从数据库中读取了某些记录后，T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录消失。 　　T1按一定条件从数据库中读取某些数据记录后，T2插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录

  • 并发访问控制锁类型

    排它锁（Exclusive Locks，即X锁）当数据对象被加上排它锁时，其他的事务不能对它读取和修改
    共享锁（Share Locks，即S锁）加了共享锁的数据对象可以被其他事务读取，但不能修改

  • 事务死锁

    大型数据库应用系统存在并发机制，也就是说可能同时有多个会话访问同一个数据，这时可能不可避免出现死锁。

    事务A锁定了数据库对象A(表或表中记录)。事务B也锁定了数据库B(表或表中的几条记录)。事务A申请锁定数据库B，而同时事务B申请锁定数据库A。两者互不相让，就产生了死锁

1.2 sql语句分类 ​

DDL（Database Define Language）数据库定义语言 用于创建、修改和删除数据库对象，如CREATE TABLE、ALTER TABLE、DROP TABLE等。DDL语句会自动提交事务
DCL（Database Control Language）数据库控制语言 用于执行授予权限和撤销权限的操作，包括GRANT（授予权限）、REVOKE（撤销权限）DCL语句会自动提交事务
DML（Database Manager Language）数据库操作语言 用于操纵数据库，包括INSERT、UPDATE、DELETE、SELECT等
DQL（Data Query Language）数据库查询语言 如：select
TCL（Transactional Control Language）事务控制语言 用于维护数据的一致性，包括COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）

1.2.1 DDL 数据库定义语言 ​

sql
-------------表操作
--创建表
CREATE TABLE 表名

--查看当前数据库有哪些表
show tables;

--查看表的结构
desc 表名；

--删除表
drop table 表名

--添加一列
alter table 表名 add 列名 类型;

--修改列的类型
alter（修改） table 表名 modify (修改)列名 类型;

--修改列的名称
alter table 表名 change 旧列名 新列名 数据类型;

--删除某列
alter table 表名 drop 列名；

--修改表的名称
rename table 旧表名 to 新表名;

-----------数据库操作
--创建数据库
create database 库名称;

--显示mysql中有哪些库
show databases;

--删除一个数据库
drop database 数据库名称;

--进入要使用的库（重要）
use 库名;

--查看当前选择的数据库
select database();

1.2.2 DCL 数据库控制语言 ​

sql
-- 用户管理
-- 添加用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 删除用户：
DROP USER '用户名'@'主机名';
-- 修改用户密码：
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
-- 查询用户：
-- 1. 切换到mysql数据库
USE myql;
-- 2. 查询user表
SELECT * FROM USER;


--权限管理：
-- 查询权限：
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';
-- 授予权限：
-- * 通配符： % 表示可以在任意主机使用用户登录数据库
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
-- 撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

1.2.3 DML 数据库操作语言 ​

sql
-- 插入数据
--注意: 数据类型为字符串类型的.需要使用单引号包裹.
insert into 表名[(列名1,列名2...)] values (值1,值2...);

-- 修改数据
update 表名 set 列名1 = 值 , 列名2 = 值 ....[where 条件1 条件连接符 条件2...]

--删除记录
DELETE FROM 表名 [WHERE 条件];

--删除表中所有记录。
DELETE FROM employee ;

1.2.4 DQL数据库查询语言 ​

sql
-- 列名可为多个，中间用,号隔开。查询所有为*,使用多列名效率更高。*还需再进行运算
select 列名，列名... from 表名;
--完整写法是 字段前面的表名单表情况下可以省略 ，数据库名一般省略
select 表名.列名1,表名.列名2 from 数据库名.表名;

查询语言格式为上述，更复杂的查询在后还有各种拼接的条件。

  • WHERE （分组前条件查询）

    sql
    --条件查询即在查询后拼接条件 可使用运算符、数值区间判断、集合索引判断
-- 运算符 = != <> <= < > >=
-- 数值区间判断 between...and ...	在...和...之间
-- IN(SET)/NOT IN(SET)  （不在这个集合） not在集合内需要加列明限定，not in在集合外限定即可
-- 多个条件判断之间连接符 AND && 和、 OR ||	或者、 NOT ! 非
-- 判断是否为null需要使用is判断  is null / is not null
-- 示例
select * from 表名 where 列名1 = ‘’ and 列名2 is not null

  • GROUP BY (对结果进行分组)

    sql
    -- sql 即对查询的结果按某一列的值进行分组并可以对每一组的结果进行聚合运算（求和、求差、统计数量等...），不进行分组且不进行聚合的列无法查询到
--如 根据用户性别进行分组，并统计各自数量 
select gender,count(1) from user group gender;

--可按多个列名进行分组，分组时，需多列名都相同才能分在一个组中
--如 根据用户地区和性别进行分组，并统计各自数量 
select gender,area,count(1) from user group gender,area;

  • HAVING （分组后条件查询）

    sql
    -- 对分组后的结果再进行条件查询，如：获取单个性别超过50人的地区，性别，人数信息
select gender,area,count(1) from user group gender,area having count(1) > 50;

  • where和having区别

    sql
    --where是在分组之前进行条件查询
-- having是在分组之后对数据进行条件查询
-- where的效率远高于having，分组消耗资源

  • ORDER BY（排序）

sql
-- order by 用来指定数据的排序方式。有升序和降序两种。desc表示降序，asc为升序，默认为升序，asc可省略。
-- order by 要写在where、group by后面，limit前面。
-- 如： 用户按照年龄排序
select * from user order by age;

  • LIMIT、ROWNUM(结果限定)

    sql
    --LIMIT为mysql 结果限定关键字， oracle使用的是 ROWNUM
--用来限定查询结果的起始行，以及总行数。
-- 查询10条用户数据，从第3行开始
-- mysql
select * from user limit 3,10;
--oracle
select * from (select * from user where rownum > 3) where rownum < 10

  • Like(模糊查询)

    sql
    -- like 模糊查询条件，与通配符结合可查询字段中包含某字符串的数据。
-- _匹配单个任意字符 %匹配任意字符
-- 如 查询名字第二个字是三的用户
select * from user where name like '_三%';
-- 查询名字中包含三的用户
select * from user where name like '%三%';

  • distinct （去重）

    sql
    -- 去重可对一个或多个列去重
-- 查询所有用户名称（相同的只显示一次）
select distinct name from user;

  • 多列求和

    sql
    --注意 null与任何数字计算结果都是null. 应使用IFNULL(参数1,参数2) 函数，当参数1为null时，返回参数2的值
select 列名+ifnull(列名,0) from 表名;

  • 别名

    sql
    -- 当名称过长或多表查询列名重复时，可为某一列字符修改查询结果显示的名称。
select 列名 as 别名 from 表名; 
-- 多表查询时，表名过长也可为表起别名，简化sql拼写
select 别名.列名 from 表名 别名；

1.2.5 聚合函数 ​

sql
--用来做纵向计算的函数
--统计指定列不为null的记录行数
COUNT(列名)
-- 计算制定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MAX(列名)
-- 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN(列名)
-- 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
SUM(列名)
-- 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
AVG(列名)

1.3 view视图 ​

  • 概述：view视图就是一张虚拟表，是表通过某种运算得到的一个投影。可简化查询、实现更精确的权限配置。

  • 使用

    sql
    -- 创建视图
create view 视图名(view_表名) as select 语句;
--注意：视图包含 聚合函数、distinct、group by、having、union、union all等时，不能进行增删改操作

--删除视图
drop view 视图名；

1.4 列的约束 ​

作用：

  • 保证数据的完整性

    非空约束（not null)指定非空约束，在插入数据时，必须包含值
    唯一约束（unique)该列的内容在表中，值是唯一的
    主键约束(primary key)当想把某一列的值作为该列的唯一标识符，可以指定主键约束（包含 非空和唯一约束），一个表中只能有一个主键约束列
    外键约束（foreign key）表之间产生关系，保证数据关联的正确性

  • 主键自动增长

    可以使数字类型的主键实现自动增长，但必须是主键约束的列。可能造成主键断层。

  • 约束创建

    sql
    -- 非空约束
--创建表时添加约束
CREATE TABLE stu(id INT,NAME VARCHAR(20) NOT NULL);-- name为非空
--创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
--删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);

-- 唯一约束
--创建表时，添加唯一约束
-- * 注意mysql中，唯一约束限定的列的值可以有多个null
CREATE TABLE stu(id INT,phone_number VARCHAR(20) UNIQUE); -- phone_number 添加了唯一约束
--在创建表后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
-- 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;

-- 主键约束
-- 在创建表时，添加主键约束
create table stu(id int primary key,name varchar(20));-- 给id添加主键约束
--创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
--删除主键
-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;

-- 自动增长
-- 在创建表时，添加主键约束，并且完成主键自增长
create table stu(id int primary key auto_increment,name varchar(20));-- 给id添加主键约束并自动增长
-- 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
-- 删除自动增长
ALTER TABLE stu MODIFY id INT;

-- 外键约束
--在创建表时，可以添加外键
create table 表名(....外键列constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称));
-- 创建表之后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

-- 级联操作
--添加级联操作
-- 包含两个分类：级联更新：ON UPDATE CASCADE 级联删除：ON DELETE CASCADE 
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;

1.5 多表查询 ​

  • 等值连接（内连接）

    sql
    -- 显示AB中符合条件的记录
select a.,b. from 表名A a ,表名B b where a.name=b.name;

  • 左连接 （外连接）

    -- 显示A中所有记录以及B 中符合条件的记录
select a.,b. from 表名A a left join 表名B b on a.name=b.name;

  • 右连接（外连接）

    sql
    --显示B中所有记录以及A中符合条件的记录
select a.,b. from 表名A a right join 表名B b on a.name=b.name;

  • 自查询

    sql
    -- 特殊的多表查询
select a.* ,b.* from 表名A a，表名A b where a.id>b.id;

  • 子查询

    sql
    -- 以一次sql的查询结果当作另一个sql的条件
-->=,<=, < ,!=,=,<> 连接后边的子查询，要求子查询返回结果为单一值
select * from 表名A where id>(select id from 表名B where 列名 = ‘’)；
--用 in ，not in俩连接后面的子查询（子查询的返回结果可为多行）
select * from 表名A where id in (select id from 表名B where 列名 = ‘’)；

1.6 索引 ​

1.6.1 概述 ​

  • 索引是一种与表相关的数据库逻辑存储结构 如果将表看成一本书，则索引的作用类似于书中的目录

1.6.2 使用基本原则 ​

  • 合理安排索引列

    • 在create index语句中，列的排序会影响通过索引进行查询的性能，我们通常把最常用的列放在前面。

  • 限制表中索引的数量

    • 虽然Oracle对与索引的数量没有限制，但是我们不能滥用索引。如果索引过多，修改表中的数据时对索引的更改的工作量会很大，效率也很低。

  • 指定索引数据块空间的使用

    • 创建索引时，索引的数据块是用表中现存的值进行填充，其最大值由PCTFREE进行设置。因此，如果需要在创建索引的表中插入较多数据时，就需要把PCTFREE的值设置得大一些

  • 根据索引大小设置存储参数

    • 创建索引前应该预先估计索引的大小，以便更好的规划磁盘空间

1.6.3 分类 ​

  • 单列索引与复合索引 一般来说, 一个表建立索引不要超过5个

    • 一个索引可以由一个或多个列组成，用于创建索引的列被称为“索引列” 单列索引是基于单个列所创建的索引，复合索引是基于多列所创建的索引

  • 唯一索引与非唯一索引

    • 唯一索引是索引列值不能重复的索引，非唯一索引是索引列值可以重复的索引 无论是唯一索引还是非唯一索引，索引列都允许取NULL值

  • 标准（B-tree index，B树）索引

    • 在使用CREATE INDEX语句创建索引时，默认创建的就是B树索引

  • 位图索引

    • 基数 : 是指某个列可能拥有的不重复值的个数。例如，Sex列的基数为2（性别只能是男或女），MaritalStatus列的基数为3（婚姻状况只能是未婚、已婚、离异） 对于一些基数很小的列，B树索引处理方式的效率比较低 对于基数很小、只存在有限的几个固定值的列（如性别、婚姻状态、行政区、职称），为了加快查询效率，应该在这些列上创建位图索引 当某列的基数与表的总行数的比例小于1%时，建议在列上创建位图索引

  • 函数索引

    • 在Oracle中，不仅能够对表中的列创建索引，还可以对包含有列的函数或表达式创建索引，这种索引被称为“函数索引” 根据函数或表达式的结果的基数情况，函数索引既可以采用普通的B树索引，也可采用位图索引

1.6.4 创建索引 ​

  • 语法格式

    sql
    CREATE [UNIQUE] INDEX [SCHEMA.]index_name ON table_name (col_name)
[TABLESPACE ts]  --表示索引存储的表空间
[STORAGE s]	--表示存储参数
[PCTFREE pf]	--表示索引数据块空闲空间的百分比
[NOSORT ns]	--表示不再排序
SCHEMA：表示Oracle模式，缺省默认为当前账户

-- 查看索引
show index from 表名
-- 删除索引
drop index[索引名称] on 表名
-- 更改索引
alter 表名 add [unque] index[索引名称] on(字段(长度)) 
-- 添加一个主键,索引必须是唯一索引,不能为NULL 
alter table tab_name add primary key(column_list) 
-- 创建的索引是唯一索引,可以为NULL 
alter table tab_name add unque index_name(column_list) 
-- 普通索引,索引值可出现多次 
alter table tab_name add index index_name(column_list) 
-- 全文索引
alter table tab_name add fulltext index_name(column_list)

  • 唯一索引

    sql
    --在emp表的ename列上创建一个唯一索引idx_emp_ename，创建之后该表中ename列就不允许出现重复值
create unique index idx_emp_ename on emp(ename);
insert into emp(empno,ename,job) values(7900,'jack','cleark');

  • 复合索引

    sql
    --如果SELECT语句中的WHERE子句引用了复合索引中的所有列或大多数列，则使用复合索引可以显著地提高查询速度
--创建此类索引时，应该注意定义中使用的列的顺序，通常，最频繁访问的列应该放置在列表的最前面
create  index idx_emp_ename_job on emp(ename,job);

  • 位图索引

    sql
    --由于emp表的job列、deptno列的取值范围有限，并且经常需要基于这些列进行查询、统计、汇总工作，所以应该基于这些列创建位图索引
create bitmap index idx_bm_job on emp(job);

  • 原则

    • 一般不需要为数据量很小的表创建索引
    • 对于数据量比较大的表，如果经常需要查询的记录数小于表中所有记录数的10%，则可以考虑为该表创建索引
    • 应该为大部分列值不重复的列创建索引
    • 对于取值范围较大的列（如ename列），应该创建B树索引；对于取值范围较小的列（如sex列），应该创建位图索引
    • 对于包含很多个NULL值，但是经常需要查询所有非NULL值记录的列，应当创建索引
    • 不能在CLOB或BLOB等大对象数据类型的列上创建索引
    • 如果在大部分情况下只需要对表执行只读操作，就可以为该表创建更多的索引以提高查询速度
    • 如果在大部分情况下需要对表执行更新操作，则应该为少创建一些索引，以提高更新速度

1.6.5 监视索引 ​

sql
-- 在city字段创建位图索引index_test_city;
-- 在age字段创建位图索引index_test_age;
-- 设置查看索引使用情况：
 set autotrace on explain
-- 在oracle SQL Developer上按F6
create bitmap index index_test_city on tb_index_test(city);
create bitmap index index_test_age on tb_index_test(age);

1.7 数据库设计 ​

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

  • 第一范式（1NF）

每一列都是不可分割的原子数据项

  • 第二范式（2NF）

    在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

    1. 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A。例如：学号-->姓名。 （学号，课程名称） --> 分数
    2. 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。例如：（学号，课程名称） --> 分数
    3. 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。例如：（学号，课程名称） -- > 姓名
    4. 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A。例如：学号-->系名，系名-->系主任
    5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码 例如：该表中码为：（学号，课程名称）* 主属性：码属性组中的所有属性* 非主属性：除过码属性组的属性

  • 第三范式（3NF）

    在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

1.8 sql优化 ​

  • 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

  • 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

    sql
    select id from t where num is null 
--可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： 
select id from t where num=0

  • 应尽量避免在 where 子句中使用!=或<>操作符，否则将进行全表扫描。

  • 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致全表扫描，如：

    sql
    select id from t where num=10 or num=20 
--可以替换为 
select id from t where num=10 
union all 
select id from t where num=20

  • in 和 not in 要慎用，否则会导致全表扫描，如：

    sql
    select id from t where num in(1,2,3) 
--对于连续的数值，能用 between 就不要用 in 了： 
select id from t where num between 1 and 3

  • 尽量避免在where子句中对字段进行函数操作，这将导致全表扫描。如：

    sql
    select id from t where substr(name,1,3)='abc'--name以abc开头的id 
select id from t where datediff(MM,createdate,'2005-11-30')=0--‘2005-11-30’生成的id 
-- 应改为: 
select id from t where name like 'abc%' 
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

  • 全模糊查询也将导致全表扫描：

    sql
    select id from t where name like '%abc%' 
--若要提高效率，可以考虑全文检索。

  • 应尽量避免在 where 子句中对字段进行表达式操作，这将进行全表扫描。如：

    sql
    select id from t where num/2=100 
-- 应改为: 
select id from t where num=100*2

  • 很多时候用 exists 代替 in 是一个好的选择：

    sql
    select num from a where num in(select num from b) 
--用下面的语句替换： 
select num from a where exists(select 1 from b where b.num=a.num)

  • 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，

    • 一个表的索引数最好不要超过6个
    • 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
    • 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
    • 联合索引，要遵守最左前缀法则,指的就是从索引的最左列开始 并且不跳过索引中的列，跳过第一个会导致索引失效，跳过第二个，只有第一个生效。
    • mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
    • 字符串不加引号索引失效
    • 尽量使用覆盖索引

1.9 数据库三大范式 ​

什么是范式

创建表的规则，指导我们后期如何去设计自己的表。

数据库表规范化的好处：

减少数据的冗余。

减少后期Java代码的工作量。

范式特点
第一范式原子性每列不可再拆分
第二范式不产生局部依赖，每列都完全依赖于主键 , 一张表只描述一件事情
第三范式不产生传递依赖，所有的列都直接依赖于主键,使用外键关联，外键都来源与其他表的主键

反3NF：为了提高数据的性能，增加冗余字段，以便提高查询性能

二、集群架构 ​

2.1 读写分离架构 ​

般应用对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，有一个思路就是说采用数据库集群的方案： 其中一个是主库，负责写入数据，称之为：写库； 其它都是从库，负责读取数据，称之为：读库；

  1. 读库和写库的数据一致；
  2. 写数据必须写到写库；
  3. 读数据必须到读库；

2.1.1 架构 ​

image-20210124230530934

从该系统架构中，可以看出：

  • 数据库从之前的单节点变为多节点提供服务
  • 主节点数据，同步到从节点数据
  • 应用程序需要连接到2个数据库节点，并且在程序内部实现判断读写操作

这种架构存在2个问题：

  • 应用程序需要连接到多个节点，对应用程序而言开发变得复杂
  • 这个问题，可以通过中间件解决
  • 如果在程序内部实现，可使用Spring的AOP功能实现

image-20210124230643036

  • 主从之间的同步，是异步完成，也就意味着这是 弱一致性
    • 可能会导致，数据写入主库后，应用程序读取从库获取不到数据，或者可能会丢失数据，对于数据安全性要求比较高的应用是不合适的
    • 该问题可以通过PXC集群解决

2.2 中间件 ​

通过上面的架构，可以看出，应用程序会连接到多个节点，使得应用程序的复杂度会提升，可以通过中间件方式解决，如下：

image-20210124230930943

从架构中，可以看出：

  • 应用程序只需要连接到中间件即可，无需连接多个数据库节点
  • 应用程序无需区分读写操作，对中间件直接进行读写操作即可
  • 在中间件中进行区分读写操作，读发送到从节点，写发送到主节点

该架构也存在问题，中间件的性能成为了系统的瓶颈，那么架构可以改造成这样：

image-20210124231052589

这样的话，中间件的可靠性得到了保证，但是也带来了新的问题，应用系统依然是需要连接到2个中间件，又为应用系统带来了复杂度。

2.3 负载均衡 ​

为了解决以上问题，我们将继续优化架构，在应用程序和中间件之间增加proxy代理，由代理来完成负载均衡的功能，应用程序只需要对接到proxy即可。·

image-20210124231152929

至此，主从复制架构的高可用架构才算是搭建完成。

2.4 PXC集群架构 ​

在前面的架构中，都是基于MySQL主从的架构，那么在主从架构中，弱一致性问题依然没有解决，如果在需要强一致性的需求中，显然这种架构是不能应对的，比如：交易数据。

PXC提供了读写强一致性的功能，可以保证数据在任何一个节点写入的同时可以同步到其它节点，也就意味着可以存其它的任何节点进行读取操作，无延迟。

架构如下：

image-20210124231258143

2.5 混合架构 ​

在前面的PXC架构中，虽然可以实现了事务的强一致性，但是它是通过牺牲了性能换来的一致性，如果在某些业务场景下，如果没有强一致性的需求，那么使用PXC就不合适了。所以，在我们的系统架构中，需要将这两种方式综合起来，这样才是一个较为完善的架构。

image-20210124231353765