Mysql高级详细梳理

Mysql8和Mysql5创建表的时候默认字符集不一样

mysql8是utf-8

mysql5是latin1

修改已经创建的字符集

alter database dbtest1 character set ‘utf-8’;

字符集的相关操作

5.2各个级别的字符集：

• 服务器级别

• 数据库级别

• 表级别

• 列级别

• 查看编码设置：show variables like ‘character%’;

SQL大小写规范

6.1Windows Linux 平台区别

• Mysql在LInux下数据库名、表明、别名大小写规则：
  • 数据库名、表名、表的别名、变量名是严格区分打消此额的。
  • 关键字、函数名称在SQL中不区分大小写
  • 列名与列的别名再所有情况下都忽略大小写
  • Mysql在windows的环境下全部不区分大小写。

6.2Linux下大小写规则设置

在My.cnf配置加入lower_case_table_names=1然后重启服务器 支持5.7

如果创建的表已经大写，要现改成小写，在重启服务器。否则找不到数据库名

创建、修改、删除

create user ‘zhang3’ identified by ‘abc123’

update mysql.user set user=‘li4’ where user=‘wangwu5’

更新完要刷新权限，要不会出现问题

flush privileges

drop user lisi，wangwu

修改密码:

alert user uesr() identified by ‘abcabc’

set password=‘abc’

通过root用户修改其他用户的密码

set password for ‘wangwu’@‘%’ =‘hello123’;

用root给用户下分权限:grant select,update on dbtest1.* to’zhang3’@‘%’;

查看权限：show grants;

或者 show grants for current_user

或者show grants for current_user()

查看某用户的全局权限:show grants for ‘user’ @ ‘主机地址’ (->local);

回收权限 revoke all privileges on . from zhang3@‘%’;

​

创建角色 create role ‘manager’ @ ‘local’;

回收角色权限 revoke update on dbtest1.* from manager;

删除 drop role admin;

查看是否激活角色 select current_role();

赋予角色权限:grant manager @‘%’ to sunli;

激活角色： set default role manager @‘%’ to sunli @‘%’;

回收权限:revoke manager from sunli;

逻辑架构

Mysql是典型的C/S架构，

Client/Server架构，服务器程序使用的mysqld，

不论客户端进程和服务器进程是采用哪种方式i进行通信，最后实现的效果都是：

客户端进程向服务器进程发送一段文本（SQL语句），服务器进程处理后在想客户端进程发送一段文本（处理结果）

连接层、服务层、引擎层

连接层：验证用户吗对不对 不匹配报错，查询账号查出账号拥有的权限

选取-投影-连接

SQL语句->

解析器：在解析器中对SQL语句进行语法分析、语义分析->语法分析树

优化器：在优化器中会确定SQL语句的执行路径，比如是根据全表检索，还是根据索引检索等，->查询计划（查询树）

执行器：现在没有真正读写真实的表，仅仅产出了执行计划，于是进入了执行器阶段，

<-查询结果

vim/etc/my.cnf Mysql配置文件

query_chache_type=1

1代表开启ON, 2代表关闭（DEMAND)

systemctl restart mysqld 重启mysql服务

​

引擎介绍

5.5之前默认myisam

• 应用场景：只读应用或者以读为主的业务

5.5之后innodb

• Innodb引擎：具备外键支持功能的事务存储引擎
• MySQL从3.23.34a开始就包含Innodb存储引擎

索引的数据结构

• 索引的优点

  • 提高数据库检索，降低数据库的IO成本
  • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
  • 在实现数据的参考完整性方面，可以加速表和表之间的连接
  • 可以显著减少查询中分组和排序的时间，降低CPU的消耗

• 索引的缺点：

  • 创建索引和维护索引要耗费时间，并且随着数据量增加，所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间之外，每个索引还要占据一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能会比数据文件更快达到最大文件尺寸。
  • 虽然索引提高了查询速度，但也同时降低了表的更新速度，当对表进行修改时，索引也要
  • 动态维护，降低了数据的维 护速度。

• 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点上）也就是索引即数据，数据即索引

• 聚簇索引：

  • 优点
    • 数据访问更快，因为聚簇索引将索引数据保存在同一个B+树种，因此从局促索引种获取数据比非聚簇索引更快
    • 局促索引对于主键的排序查找和范围查找速度非常快
    • 按照局促索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块种提取数据，所以节省了io操作
  • 缺点：
    • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则会出现页分裂，严重影响性能，因此，对于，innodb表，我们一般都会定义一个自增的ID 列为主键
    • 更新主键的代价很高，因为将会导致被更新的行移动，因此，对于innodb表，一般定义为主键不可更新
    • 二级索引访问需要两次索引查找，第一次查主键值，第二次根据主键值找到行数据

• 二级索引（辅助索引、非聚簇索引）

• 聚簇索引：

  • 生成聚簇索引的时候，先有根节点（顶层）随后向表中插入用户记录时，先把用户记录存储到这个跟节点中，
  • 每当跟节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页A，然后对这个新的页进行页分裂，得到另一个新列，页B，这时插入的记录根据键值的大小会被分配到A或者B中，而根节点升级为存储目录记录的页。

• 为了减少IO，索引树会一次性加载吗？

  • 不会，数据库索引是存储在磁盘上的，如果数据量很大，必然会导致索引很大，超过几个G
  • 每当我们利用索引查询的时候，是不可能将全部几个G的索引都加载进内存的，只能逐一加载每一个磁盘也，因为磁盘也对应着索引树的节点。

• B+树的存储能力如何？为何说一般查找行记录，最多只需要1-3次磁盘IO

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4PjWVOmC-1652321290128)(D:\Mysql高级\B树.png)]

• 为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

  • B+树磁盘读写代价更低（减少磁盘IO）矮胖
  • B+树查询效率稳定

• Hash索引与B+树索引的而区别：

  • Hash索引不能进行范围查询，而B+树可以，这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表
  • Hash索引不支持联合索引的最左侧原则，（即联合索引的部分索引无法使用），而
  • B+树可以，对于联合索引来说，Hash索引在计算Hash值的时候将索引键和并在一起计算hash值，所以不会针对每个索引单独计算hash值，因此如果用到联合索引的一个或几个索引时，联合索引无法利用。
  • Hash索引不知此和Order By 排序，因为Hash索引是无序的，无法排序优化，而B+树索引是有序的，可以用Order By排序，同理页无法用Hash索引进行模糊查询，而B+树可以用LIKE进行模糊查询，%后结尾的话就可以起到优化作用。
  • innodb不支持hash索引

• hash索引与B+树索引在建索引的时候需要手动指定吗？

  • innodb不支持hash索引，
  • memory/ndb支持hash索引
  • innodb有一个自适应，热点数据进行hash索引

• 页结构概述

  • 页a、b、c这些页可以不在物理结构上相连，只要通过双向链表相关联即可，每个数据页中的记录会按照主键值从大到小的顺序组成一个单向链表，每个数据页都会为存储在他里边的记录生成一个页目录，在通过主键快速找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后在遍历对应分组的记录就可快速找到记录。
  • 页是双向链表，页里面的数据是单项链表，有序的。

• 什么是回表：

  • 假设，现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

    但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2; 原因是通过 k 这个普通索引查询方式，则需要先搜索 k 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

  • 回表：查询的不是主键，查询到指定值之后，返回主键ID，在到这个ID索引树搜索一次，称为回表

• 页的内部结构：

  • 页如果按照类型划分的话，常见的有数据页(保存B+树节点)、系统页、undo页、和事务数据页等，数据页最常用
  • 页默认16KB大小

• 前四个字节代表页的校验和

  • 这个部分是和File Header中的校验和相对应的。

• 后四个字节代表页面被最后修改时对应的日志序列位置(LSN)

  • 这个部分也是为了校验页的完整性，如果首部和尾部的LSN值校验不成功的话，就说明同步过程出现了问题
  • User Records中的这些记录按照指定的行格式一条条摆在User Records部分，相互之间形成单链表
  • record_type
    • 0表示普通记录
    • 1表示B+树非叶节点记录
    • 2表示最小记录
    • 3表示最大记录
  • heap_no
    • 为什么没有0和1
      • Mysql会自动给每个页加两个记录，这两个不是自己插入的，索引也成为伪记录|虚拟记录，这两个伪记录一个代表最小记录，一个代表最大记录，最小记录和最大记录的heap_no分别是0和1，他们的位置最靠前。
  • infimum+supremum 最大最小记录
  • n_owned
    • 页目录中每个组最后一条记录的头信息中会存储该组一共有多少条记录，作为n_owned字段
  • next_record
    • 记录头信息里该属性非常重要，他表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。
    • –偏移量，往下找？？个字节，就找到第二个字节

• 索引的声明与使用

  • 索引的分类
    • 从功能逻辑上说：有四种：普通索引、唯一索引、 主键索引、全文索引
    • 物理实现方式，聚簇索引 非聚簇索引
    • 作用字段个数 单列索引 联合索引

• 通过命令查看索引

• 适合添加索引的情况

  • 频繁作为where查询条件的字段
  • 经常group by和order by的列
  • update、delete的where条件列
  • distinct字段需要创建索引
  • 多表join连接操作时，创建索引
    • 连接表的数量尽量不要超过3张，
    • 对where条件创建索引
    • 对于用连接的字段创建索引，类型必须一致。
    • 使用列的类型小的创建索引
    • 使用字符串前缀创建索引
    • 区分高度（散列性高）的适合作为索引
    • 使用最频繁的列放到联合索引的左侧
    • 在多个字段都要创建索引的情况下，联合索引优于单值索引

• 不适合索引的情况

  • 在where中使用不到的字段
  • 数据量小的情况
  • 有大量重复数据列上不要建立索引
  • 避免对经常更新的表创建过多的索引
  • 不建议用无序的值作为索引
  • 删除不再使用或者很少使用的索引
  • 不要定义冗余或重复的索引

• 分析查询语句EXPLAIN

  • 定位了查询慢的SQL之后，我们就可以使用EXPALANIN或DESCRIBE工具做
  • 针对性的分析查询语句

• table 表名

  • 查询的每一行记录都对应着一个单表
    • explanin select * from s1;

• s1驱动表 s2被驱动表

  • explain select * from s1 inner join s2;

• id如果相同，可以认为是一组，从上往下顺序执行

• 在所在组中，id值越大，优先级越高，越先执行

• 关注点：id号每个号码，表示一趟堵路的查询，一个sql的查询趟越少越好

• 子查询优化

  • 子查询效率不高消耗过多的CPU和IO
  • 子查询优化
    • 查询班长信息

• 排序优化

  • 在where条件字段上加索引，但是为什么在order by字段上还要加索引
    • 在mysql中，支持两种排序方式，分别是filesort和index排序
    • index排序中，索引可以保证数据的有序性，不需要在进行排序，效率高高
      • filesort排序则一般在内存中进行排序，占用cpu较多，排序结果较大，
      • 会产生临时文件io到磁盘进行排序的情况，效率低
    • 优化建议：
    • SQL中，可以在where自居和orderby中使用索引，避免出现全表扫描，
    • orderby避免使用filesort排序
    • 尽量使用index完成orderby，
    • 无法使用index时，需要对filesort方式进行调优

• 什么是覆盖索引？ 理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它 不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数 据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。 理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。 简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

• 自增ID的问题

  • 可靠性不高
  • 安全性不高
  • 性能差
  • 交互多
  • 局部唯一性

• 1范式：字段是原子性的，不能拆分

• 2范式：一张表就是一个独立的对象，只表达一个意思

• 反范式化：

  • 结合实际考虑到底使用什么范式（反范式）
  • 适当增加冗余字段提高数据库性能
  • 当经常使用编号查询某些东西的时候，如果要使用第三范式，反而会降低效率，因为需要使用多表查询
  • 这时就可以反范式化，把常用的放在一张表上，灵活使用

• 一个实体对应一个数据表

• 多对多关系 对应一个表

• 1对1 或者1对多，通过外键

• 属性就是字段

数据表的设计原则

• 数据表的个数越少越好
• 数据表的字段个数越少越好
• 数据表中联合主键的字段你个数越少越好
• 使用主键和外键越多越好

事务基础

• 事务 是一组逻辑操作单元，使数据从一种状态变换到另一种状态
• 事务的原则：所有保证事务都作为一个工作单元来执行，即使出现了故障，都不能改变，要么一起生，要么一起四
• 原子性：
  • 要么全部提交，要么全部失败回滚 要么一起成功，要么一起失败
• 一致性：
  • 数据从一个合法性状态 变换到另外一个合法性状态 这种是语义上的，不是语法上的
• 隔离性:
  • 一个事务不能被其他事务干扰，一个事务内部的操作及使用的数据对并发的其他事务时隔离的，并发执行
  • 的食物之间不能互相干扰
• 持久性：
  • 一个事务一旦被提交，他对数据库种数据的改变就是永久的，接下来的操作或故障都不对其产生影响

事务的状态

• 活动的
• 部分提交的
• 失败的
• 中止的

事务完整过程

• 开始事务

• DML操作

• 结束的状态:提交(commit)、终止(rollback)

• 显示事务：

  • start transaction 或者 begin
    • start transaction 可以跟 read only / read write / with consistent snapshot
      • ​ 只读事务 可读可写 开启一致性读
  • 保存点（savepoint)

• 隐式事务：

  • set autocommit =false 关闭自动提交
  • set autocommit =true 开启自动提交

• 在autocommit为true的情况下，使用start transaction或begin开启事务，就不会自动提交

• 数据并发问题：

  • 脏写：
  • 对于两个事务A、B，如果事务A修改了另一个为提交事务B修改过的数据，那就发生了脏写。
  • 脏读：
    • 对于两个事务A、B，A读取了已经被B更新但还没有提交的数据，之后若B回滚，A读取的内容就是临时，且无效的
  • 不可重复读
    • 对于两个事务A、B读取了一个字段，然后B更新了字段，之后A再次读取同一个字段，值就不同了
  • 幻读：
    • 对于两个事务A、B从一个表中读取了一个字段，然后B在该表中插入新的行，之后，如果A再次读取同一个表，就会多出几行

• Oracle 的隔离级别是脏读

• Mysql隔离级别是可重复读

• GLOABL关键字 在全局范围影响

• Session关键字 在会话范围影响

• 事务的隔离性由锁机制实现

• 而事务的原子性、一致性、和持久性由事物的redo日志和undo日志来保证

• redo log称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性

• undo log称为回滚日志 回滚行记录到某个特定版本

• redo log好处：

  • rodo日志降低了刷盘频率
  • redo日志占用的空间非常小
  • 特点：
    • redo日志是顺序写入磁盘的
    • 事务执行过程中redo log不断记录

• undo log是逻辑日志 对事务回滚时，只是将数据库逻辑恢复到原来的样子

• redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程

• 不加锁：

  • 不需要在内存中生成对应的锁结构 可以直接执行

• 获取锁成功 或者枷锁成功

  • 在内存中生成了对应的锁结构，而且所结构的is_waiting属性为false，也就是事务可以继续执行操作
  • 获取锁失败或者加锁失败，或者没有获取到锁
    • 在内存中生成了对应的锁结构，不过所结构is_watting属性为true，也就是事务需要等待，不可以
    • 继续执行操作

• 写写情况

  • 排队执行

• 读写 写读

• 添加锁

  • x锁 select … for update 排他锁
  • s锁 select … for lock in share mode; 共享锁

• 写操作：

  • read共享锁
  • write 排他锁

• show open tables是否有锁

  • unlock tables 释放锁

• 意向锁：

  • 共享锁

• gap锁的提出仅仅是为了防止插入幻影记录而提出的

• 插入意向锁

• 悲观锁：

  • 对数据被其他事务的修改保持保守态度，共享资源每次只给一个线程使用，其他线程阻塞，用完后再把资源转让给
  • 其他线程。
  • select for update 语句执行过程中所有扫描的行都会被锁上，因此在Msyql中用悲观锁必须确定使用索引，而不是全表扫描
  • 否则将锁住整个表
  • 适合写操作比较多的，具有排他性，并发性差一点。

• 乐观锁：

  • 读操作比较多，改操作少一点，不存在死锁问题