MySQL基础知识

SQL -- MySQL

基本类型
- 字符型，数值型，日期型
- 字符型(VARCHARVS，CHAR，TEXT，TINYTEXT ...)
  - VARCHARVS是可变长度字符串,长度超过255会变成TEXT
  - CHAR是固定字符串,最大长度为255
- 数值型(INT，BIGINT，FLOAT，DOUBLE...)
- 日期型(DATE，TIME...)
常见函数
- 字符函数
  - concat ：连接
  - substr ：截取子串
  - upper / lower ：变大写 / 小写
  - replace ：替换
  - length ：长度
  - trim ：去除首位空格
  - instr ：获取子串第一次出现的位置
- 数学函数
  - round ：四舍五入取较大
  - ceil ：四舍五入向上取整
  - floor ：四舍五入向下取整
  - mod ：取模
  - truncate ：截断
  - rand ：获取0~1的随机数
- 日期函数
  - now ：返回当前日期+时间
  - year/month/day ：年/月/日
  - date_format ：将日期转换成字符串
  - curdate / curtime ：返回当前日期 / 时间
  - str_to_date ：字符转换日期（指定格式）
  - hour / minute / second ：时 / 分 / 秒
  - datediff ：返回两个日期相差的天数
  - monthname ：以英文形式返回月份
- 其它函数
  - version ：返回当前服务器的数据库版本
  - database ：当前打开的数据库
  - user ：当前用户
  - md5 ：返回该字符通过md5加密后的形式
一些常用的SQL语句
- SELECT - 从数据库中提取数据
- UPDATE - 更新数据库中的数据
- DELETE - 从数据库中删除数据
- INSERT INTO - 向数据库中插入新数据
- CREATE DATABASE - 创建新数据库
- ALTER DATABASE - 修改数据库
- CREATE TABLE - 创建新表
- ALTER TABLE - 变更（改变）数据库表
- DROP TABLE - 删除表
- CREATE INDEX - 创建索引（搜索键）
- DROP INDEX - 删除索引
SELECT
1. select 查询列表
2. form 表1
3. 连接类型 join 表2
4. on 连接条件
5. where 筛选条件
6. group by 分组列表
7. having 分组后筛选
8. order by 排序列表
9. limit 偏移，条目数
  
  解析过程：
约束
1. 类型：
  1. not null：非空
  2. unique：唯一
  3. default：默认
  4. check：检查（mysql不支持）
  5. primary key：主键
  6. foreign key：外键
2. 语法
  - 添加：alter table 表名 add 约束类型字段名
  - 删除：alter table 表名 drop 约束类型字段名
3. 主键和唯一的区别
  - 异：一个表最多只能有一个主键，唯一可以多个；主键不能为null，唯一可以
  - 同：都具有唯一性；都支持组合键（不推荐）
4. 外键
  - 用于限制两表的关系，从表引用主表的主键的某个字段，类型需一样，名称无要求；
  - 主表被引用的列要求是一个key（一般就是主键）；
  - 保护数据：插入数据，先插入主表；删除数据，先删除从表（级联删除，级联置空）；
自增长列（auto_increment_increment，auto_increment_offset）
1. 特点：
  - 默认从1开始，步长为1（修改起始值：手动插入值；修改步长：set auto_increment_increment = 值）
  - 一个表最多只能有一个自增长列
  - 自增长列只能支持数值型（整数，浮点数）
  - 自增长列必须为一个key（MySQL）
2. 语法：
  - create table 表(字段名字段类型约束 auto_increment)
  - alter table 表 modify column 字段名字段类型约束 auto_increment
  - alter table 表 modify column 字段名字段类型约束
变量
流程结构
- 顺序结构
- 分支结构
  - if case
- 循环结构
  - while
  - loop
  - repeat
存储过程和函数
- 存储过程 :
  - 作用： 提高代码复用性，简化操作
  - 创建语法：
```
create procedure 存储过程名(参数列表)
begin
  存储过程体(一组合法的sql语句)
end
```
    - 参数列表：参数模式参数名参数类型
    - 参数模式：IN 输入； OUT 作为返回值； INOUT 即是输入也是返回值
    - 存储过程中的每条SQL语句的几位都需要加分号（delimiter可以设置结束标记）
  - 调用 : CALL 存储过程名
  - 查看 : SHOW CREATE PROCEDURE 存储过程名
  - 删除 : DROP PROCEDURE 存储过程名
- 函数 :
  - 创建 :
```
create function 函数名(参数列表 -> 参数名, 参数类型)
returns 返回类型
begin
	函数体(return 值)
end
```
  - 调用 : select 函数名(参数列表)
  - 查看 : show create function 函数名
  - 删除 : drop function 函数名
- 存储过程与函数的区别 :
  - 存储过程：可以有0个或多个返回值，适合做批量插入，更新
  - 函数：有且仅有一个返回值，适合做处理数据后返回一个结果
视图（虚拟表）
- 优点：
  1. 重用sql语句
  2. 简化复杂的sql操作，不必找到查询细节
  3. 保护数据，提高安全性
- 创建视图：
```
create view 视图名
as 查询语句
```
- 修改：
```
create or replace view 视图名(存在则修改，否则则创建)
as 查询语句
#或者
alter view 视图名
as 查询语句
```
- 删除：drop view 视图名1，视图名2...
- 查看：show create view 视图名 / desc 视图名
- 注意点：
  - 视图一般用于查询，不用于修改
  - 以下情况不允许修改：
    1. 包含分组函数：group by，distinct，having，union
    2. join
    3. 常量视图
    4. where后的子查询用到了from中的表
    5. 用到了不可更新的视图
- 视图与表的关系：
  
  关键字是否占用物理空间使用
  
  视图 view 占用较小，只保存sql逻辑一般用于查询
  
  表 table 保存实际数据增删改查
事务：
- 特性：（ACID）
  1. 原子性：一个事务不可再分割，要么都执行，要么都不执行
  2. 一致性：一个事务执行会使数据从一个一致的状态切换到另一个状态
  3. 隔离性：一个事务的执行不受其它事务的干扰
  4. 持久性：一个事务一旦提交，则会永久的改变数据库数
- 事务的创建：
  - 隐式事务：事务没有明显的开启和关闭的标记
    - 如：insert，update，delete语句
    - 原因：事务的自动提交功能默认为开启状态（show variables like 'autocommit'; -> Value : ON）
    - 关闭方法：set autocommit = 0 -> Value : OFF
  - 显式事务：有明显的开启和结束标记
    1. 开启事务
      - (set autcommit = 0)
      - start transaction; (可选)
    2. 编写sql语句
      - (select，insert，update，delete) 可以有多个语句
    3. 结束事务
      - commit：提交事务 -- 全部正常执行
      - rollback：回滚事务 -- 出现异常（delete能回滚，而truncate不能）
        
        savepoint a ：设置保存点a
        
        rollback to a ：回滚到保存点a

	关键字	是否占用物理空间	使用
视图	view	占用较小，只保存sql逻辑	一般用于查询
表	table	保存实际数据	增删改查

数据库的隔离级别：

对于同时运行多个事务，当这些事务访问数据库中相同的数据时，如果没有采用必要的隔离机制，就会导致各种并发问题
并发问题：
1. 脏读：对于两个事务T1，T2；T1读取了已经被T2更新但还没提交的字段，之后若T2回滚，T1读到的数据内容就是临时且无效的
2. 不可重复读：对于两个事务T1，T2；T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同一个字段，值就不同了
3. 幻读：对于两个事务T1，T2；T1从一个表读取了一个字段，然后T2在该表中插入（或删除）了一些新的行，之后，T1再次读取同一个表就会多出（少）几行

隔离级别：

隔离级别	说明	解决的并发问题
读未提交（Read uncommitted）	允许事务读取未被其他事务提交的变更	都有可能发生
读已提交（Read committed）	只允许事务读取已经被其他事务提交的变更	可以避免脏读
可重复读（Repeatable read）	在这个事务期间，禁止其它事务对此字段进行更新	可以避免脏读和不可重复读
串行化（Serializable ）	在这个事务期间，禁止其它事务对该表的操作	可以解决以上三种问题

MySQL默认隔离级别：可重复读 -- REPEATABLE-READ（查询方法：select @@tx_isolation;）
MySQL支持以上四种隔离级别，Oracle支持：读已提交（默认）和串行化

设置隔离级别：

set tx_isolation='隔离级别'; #read-uncommitted, read-committed, repeatable-read, serializable
set transaction isolation level '级别' #当前数据库
set global transaction isolation level '级别' #全局

MySQL架构

MySQL的架构使得它可以在不同的场景下应用并发挥良好的作用，插件的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取分离（可以根据业务的需求和实际选择合适的存储引擎）
MySQL架构

MyISAM 和 InnoDB

	MyISAM	InnoDB（默认）
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发	行锁，操作时只锁一行，不对其它行有影响，适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，也缓存真实数据，对内存要求较高（内存大小对性能有直接关系）
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

JOINS：
MySQL索引：
- 索引是什么：
  1. 索引是一种数据结构 -> 排好序的快速查找数据结构
  2. 由于索引本身也很大，不可能全部存储在内存中，因此索引往往以文件的格式存储在磁盘上
  3. 一般是指B树（多路搜索树）结构组织的索引；其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引（除了B+树外，还有哈希索引等等）
- 优势：
  1. 提高数据检索的效率，降低数据库的IO成本
  2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗
- 劣势：
  1. 实际索引也是一张表，该表保存了主键和索引字段，并指向实体表（占用空间）
  2. 提高查询速度的同时，会降低更新表的速度（不仅要保存数据，也需要修改索引）
  3. 索引只是提高效率的一个因素，有大量数据量表时，需要不断建立最优索引
- 索引分类：
  1. 单值索引：一个索引只包含单个列，一个表可以有多个单列索引（最好不要超过5个）
  2. 唯一索引：索引列的值必须唯一，但允许空值
  3. 复合索引：即一个索引包含多个列
- 语法：
  - 创建：
```
create [unique] index 索引名 on 表名(列名/字段)
alter 表名 add [unique] index [索引名] on (列名/字段)
```
  - 删除：
```
drop index [索引名] on 表名
```
  - 查看：
```
show index from 表名
```
  - ALTER；
- 索引失效情况：
  1. 全值匹配最爱
  2. 最佳左前缀法则（索引必须依次使用，从左到右（MySQL会自动优化顺序），中间索引不能断，否则后面的索引会失效）
  3. 不在索引列上做任何操作（计算，函数，（自动或手动）类型转换）
  4. 存储引擎不能使用使用中范围右边的列（中间索引不能断）
  5. 尽量使用覆盖所有（即所有列与查询列一致，加少select *）
  6. MySQL在使用不等于（!= 或 <>）时，会导致索引失效
  7. is null，is not null 也无法使用索引
  8. like 以通配符开头（"%aa..."）会导致索引失效，而（"aa%"）不会；如果一定要使用（"%aa..."）建议使用覆盖索引（即查询列与索引列对应）
  9. 字符串不加单引号会导致索引失效（会发生自动隐式类型转换）
  10. 少用or，用它来连接时会导致索引失效
- 索引结构：
  1. BTree 索引：真实的数据存在叶子节点，非叶子节点不存储真实的数据，只存储指引搜索方向的数据项
  2. Hash 索引
  3. full-text 全文索引
  4. R-Tree 索引
- 索引的适用情况：
  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该建立索引
  3. 查询中与其它表关联的字段，外键关系建立索引
  4. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  5. 查询中统计或分组字段
  6. 高并发下偏向于建立联合索引
- 不适合建立索引的情况：
  1. 表记录较少
  2. 经常增删改的表
  3. 数据重复且分布均匀的字段（比如：性别）
- SQL性能分析：
  - 语法：Explain + SQL语句
  - 包含的信息
    
    id select_type table partitions type possible_keys key key_len ref rows filtered Extra
  - id：select查询的序列号，表示查询中执行select子句或操作表的顺序
    - 三种情况：
      - id 相同：表示顺序由上至下
      - id 不同：如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行
      - 同时存在相同的id和不同的id：
  - select_type：查询的类型，主要用于区别普通查询，联合查询，指查询等复杂查询（6种类型）
    1. SIMPLE：简单的select查询，不包含子查询和UNION
    2. PRIMARY：查询中若包含任何复杂的子部分，最外层将用此标记
    3. SUBQUERY：在select 或 where 列表中包含了子查询
    4. DERIVED：在From列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，并将结果放于临时表中
    5. UNION：若第二个select出现在UNION之后，则被标记为UNION。若UNION包含在FROM子查询中，外层select将被标记为DERIVED
    6. UNION RESULT：从UNION表获取结果的select
  - table：表名
  - type：访问类型排列（从好到差：system > const > eq_ref > ref > range > index > ALL）
    1. system：表中只有一条记录（等于系统表）
    2. const：表示通过一次索引就找到了，用于比较Primary key 或 union
    3. eq_ref：唯一性索引扫描，对于每个索引列，表中只有一条记录与之匹配，1对1
    4. ref：非唯一索引扫描，返回匹配某个单独值的所有列，1对n ---> 佳
    5. range：只检索给定的范围的行，使用一个索引来选择行（如 > , < , between in）---> 至少
    6. index：Full Index Scan 遍历索引树
    7. ALL：全表扫描
  - possible_keys，key：判断索引的使用情况
    1. possible_keys（理论上）：显示可能应用在这张表中的索引，一个或多个（实际不一定使用到）
    2. key（实际上）：实际使用的索引，如果为null，则没有使用索引（索引失效），若使用了覆盖索引，则该索引仅出现在key列表中
  - key_len：表索引中使用的字节数，在不损失精度的情况下，长度越短越好（最大可能的长度，并非实际使用的长度）
  - ref：显示索引的哪一列被使用了，如果可能话，是一个常数
  - rows：根据表统计的信息及索引选用的情况，大致估算出找到所需的记录所需要读取的行数
  - Extra：包含不适合在其它列显示但十分重要的信息
    1. Using filesort：说明MySQL会使用外部的索引排序，而不是按表内的索引顺序进行读取（多了排序）尽量不出现，降低效率
    2. Using temporary：使用临时表保存中间结果（伤性能），常见于order by 和 group by；尽量避免
    3. Using Index：表示使用了覆盖索引（所需的数据在索引中就能得到，不必再读取数据文件，查询的列被索引覆盖），性能不错，避免了访问表的数据行，如果同时出现Using where则代表索引被用来执行键值的查找，如果没有则代表索引用来读取数据而非查找操作
    4. Using where：
    5. Using join Buffer：
    6. impossible where：
    7. select tables optimized away：
    8. distinct：

数据库锁机制：
- 分类：
  - 读锁（共享锁）：针对同一份数据，多个读操作互不影响
  - 写锁（排它锁）：当前写操作未完成前，会阻断其它的写锁和读锁
- MySQL的三种锁：
  - 表锁（偏读）：
    - 特点：偏向于MyISAM存储引擎，开销小，加锁快，无死锁；但锁的粒度大，发生锁冲突的概率最高，并发度低
    - 两种表级锁：
      - 表共享读锁：阻塞所有的写操作，但不影响读操作
      - 表独占写锁：阻塞其它进程的读和写操作
  - 行锁（偏写）：
    - 特点：偏向于InnoDB存储引擎，开销大，加锁慢，会可能出现死锁，但锁的粒度小，发生锁的冲突概率小，并发度高
    - InnoDB 与 MyISAM 最大不同：1.支持事务，2.采用行级锁
    - 若索引失效，行锁会升级为表锁
    - 行级锁：
      - 共享锁
      - 排它锁
    - 表级锁：
      - 意向共享锁
      - 意向排他锁
    - 间隙锁：
      - 通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在
      - 作用：
        
        防止幻读，以满足相关隔离级别的要求。
        
        为了数据恢复和复制的需要。
      - 缺点：
        
        间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
        
        当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
        
        当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
        
        当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定
    - 优化：InnoDB行级锁锁的是索引
      1. 尽可能让所有的数据检索都通过索引的方式完成，避免无索引行锁升级为表锁
      2. 合理设计索引，尽量缩小锁的范围
      3. 尽可能较少检索条件，避免间隙锁
      4. 尽量控制事务的大小，减少锁定资源和时间长度
      5. 尽可能低级别事务隔离
    - 死锁：
      - 发现死锁：在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在
      - 解决方法：
        
        回滚较小的那个事务（判断大小：事务各自插入、更新或者删除的数据量）
        
        在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
      - 避免的方法：
        
        如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
        
        在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
        
        对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
  - 页锁：介于表锁和行锁之间，会出现死锁，并发度一般，较少使用

posted @ 2021-06-16 22:07 hehell 阅读(85) 评论(0) 收藏举报

刷新页面返回顶部

hehell

MySQL基础知识

SQL -- MySQL

公告