Mysql
一、基础篇
1、Mysql中重要的学习点：
基础：MySQL概述 SQL 函数 约束 多表查询 事物 数据库存储引擎 锁 事物 SQL优化
进阶：存储引擎 索引 sql优化 视图 存储过程 触发器 锁 innoDb核心 MYSQL管理
运维：日志 主从复制 读写分离 分库分表
2、SQL分类：
DDL:数据库定义语言，用于定义数据库、表、字段
DML:数据库操作语言，用于增删改数据
DQL:数据库查询语言，用于查询数据库中表记录
DCL:数据库控制语言，用于用户创建，数据库角色权限控制
2.1、DDL语句
查询：show databases;
创建 create database if not exists 数据库名 default charset 字符集 （mysql默认utf8umb4）
删除 drop database if exists 数据库名
使用 user 数据库名
PS:mysql默认字符集和默认排序规则
utf8mb4:可以存储4个字符集，这样方便存储更多的字符集
utf8mb4_general_ci：大小写不敏感
查询当前数据库所有的表 show tables;
查询表结构：desc 表名
查询建表语句：show create table xxx
删除表：drop table xxx
修改表名 alter table xxx rename to xxx2
2.1.1、Mysql中的数据类型
mysql中数据类型主要分为三大类：
数值类型：tinyint(1byte)、smallint(2byte)、midelInt(3byte)、int/integer(4byte) bigint(8byte)、float(4byte)、double(8byte)、decimal
字符串类型（char varchar text longtext 二级制数据（不推荐，一般文件上传在服务器））
时间类型：DATE(YYYY-MM-DD) TIME(HH:MM:SS) YEAR(YYYY)
DATETIME(YYYY-MM-DD HH:MM:SS) 年的范围是1000~9999
TIMESTAMP(YYYY-MM-DD HH:MM:SS)年的范围是1970~2038
2.2、DQL(数据库查询语言)
2.2.1、聚合函数
max min avg count sum
注意数据库列中null值是不参与聚合函数的
2.2.2、分组查询GROUP BY
select gender,count(*) from emp group by gender
select gender,avg(*) from emp group by gender
select add,count(*) addr_count from emp where age > 30 group by addr having count(*) > 3
2.2.3、排序查询order by desc /asc(默认)
2.2.4、分页查询limit
其实索引=（页码-1）*每页数据size
2.3、DCL(数据库控制语言)
2.3.1、DCL主要是用于数据库用户管理 权限管理
2.3.2、DCL 语法
创建用户
create user 'itcast'@'localhost' identified by '123456'; 本机权限 只能访问mysql
create user 'zcq'@'%' identified by '123456' 任意主机权限
drop user 'zcq'@'%'
查看权限
show grant for 'user'@'主机名'
授予权限
grant all on dbname.tableName to 'user'@'主机名'
取消权限
revoke all on dbName.tableName from 'user'@'主机名'
3、函数
3.1什么是函数？
函数式mysql内置的sql固定算法程序
3.2、函数的类型
（字符串函数、数字函数、日期函数）
字符串函数：
concat select CONCAT('mysql',"hello") 拼接字符串，可以是多个
select LOWER("Low") 转小写
select UPPER("low") 转大写
select LPAD(str,len,'填充的字符串')
select RPAD(str,len,'填充的字符串')
select TRIM(str) 去掉头部和尾部的空格，不包含中间
select SUBSTRING(index,start,end)
数字函数：
select ceil() 向上取整
select floor() 向下取整
select rand() 0-1之间的随机数
select mod(3,4) 取模运算
select round(3.123,2)四舍五入（保留2位有效数字）
日期函数：
select +
CURDATE() YYYY-MM-DD
CURTIME() HH:MM:SS
NOW() YYYY-MM-DD HH:MM:SS
YEAR(NOW())
MONTH(NOW())
DAY(NOW())
DATE_ADD(NOW(),INTERVAL 70 YEAR)
datediff()
示例：select name,DATEDIFF(curentdate(),entrdate) from emp;
流程控制函数
if(true,'a','b') 满足条件第一个
ifnull('a','b') 不为空时第一个，为空第二个值
case when xxx then xxx when xxx2 then xxx2 end
4、约束
4.1、什么是约束？
用于限制数据库中表字段的规则约束
约束主要有：非空约束、主键约束、唯一约束、默认约束、外键约束、默认约束
4.2、约束的分类
1、主键约束 primary key
2、唯一约束 unique
3、默认约束 default
4、非空约束 not null
5、检查约束 check
6、外键约束
4.3、外键约束
1、外键约束的主要作用是，保证数据的完整性和一致性
比如有员工表部门id和部门表id是有关联关系的，在不建立外键的时候，仅仅是逻辑上的关联，建立外键之后，数据之间可以保证完整性和一致性
语法：
alter table 表名 add constraint 外键名称 foreign key (字段名) reference 主表（主表列名）
alter table 表名 drop foreign key 外键名称
4.4、外键删除、更新行为
1、不可修改，删除
2、级联casedel，子表中对应的数据，如果有，也要删除、更新
3、setNull
5、多表查询
5.1、多表之间的关系
1对多 多对多 一对一
5.2、多表查询类型
5.2.1、内连接
内连接主要是两个集合的交集 select a.*,b.* from a inner join b where a.id = b.deptId
内连接分为隐式内连接和显示内连接
>= and <= 相当于 between and
5.2.2、外连接
外连接主要是左外和右外，左外是以左边的表为基础，关联右边的表 右外相反
5.2.3、自连接
自连接主要是自己表关联自己表
比如员工和领导都在员工表里面，要同时查询员工信息和他的领导信息，就要用自连接
select a.name '员工',b.name '领导' from emp a left join emp b where a.managerId = b.id;
自连接一定要给表取别名，其次，自连接可以是内连接或者外连接
5.2.4、联合查询
联合查询是将两个查询结果一样的数据合并在一起
比如：
select * from emp where age > 30
union all
select * from emp where salary < 5000
select * from emp where age > 30
union
select * from emp where salary < 5000
union all 和 union 区别是 前面没有去重直接合并，后面去重合并
5.2.5、子查询
子查询是sql中嵌套select语句,也叫嵌套查询
5.2.5.1、标量子查询
概念：子查询返回的结果是单个值
select * from emp where deptId = (select id from dept where deptName = '销售部')
select * from emp where entryDate > (select entrydate from emp where name = 'xxx')
5.2.5.2、列子查询
概念：子查询返回的结果是一列数据
常用操作符： in not in any some all
5.2.5.3、行子查询
子查询返回的数据是一行多列
5.2.5.4、表子查询
表子查询是指查询结果是多行多列
select * from emp where (grade,salary) in (select grade,salary from emp whre name = 'xxx' or name = 'xxx2')
6、事物
6.1、事物的概述
事物就是一些操作的集合，这些操作是一个整体，要嘛全部成功，要嘛全部失败
例如,转账的案例 小何转给我1000元分以下几步
1、先查询小何银行卡里面的余额是否大于1000
2、小何余额-1000
3、我的余额+1000
如果其中任何一个环境出问题，要进行回滚，恢复操作之前的步骤
6.2、事物的操作
mysql默认事物是自动提交
select @@autocommit; 默认是1
可以设定为0，非默认提交
这样，每次执行完一条sql，必须要执行commit;才可以生效
如果中途出现异常，执行rollback;回滚
还有一中方法，不用修改自动提交参数
1、start transaction;/begin
sql 操作。。。。
2.commit;(无异常,正常提交)
3.rollback;(如果发生异常,回滚)
6.3、事物的4大特性
ACID:
原子性、隔离性、持久性、一致性
原子性：事物是一个整体，要嘛全部成功，要嘛全部失败，没有中间状态 底层原理是undolog,我们操作数据库的时候会先将数据操作步骤记录到undoLog中，如果出现错误，会按照undolog的执行顺序做相反的操作，将数据进行恢复，所以就叫原子性操作。
隔离性：事物之间的操作相互隔离，不受影响
一致性：数据库操作之前和之后数据状态一致
持久性：数据库一旦执行完，不做修改的话数据不会发生改变，已经持久化到磁盘了 原理：redolog,数据写入后马上通过顺序id写入到redolog日志中，然后持久化到磁盘，再写入到buffer中，buffer再刷盘，这样就保证数据一定能够持久化，如果buffer异常，会从redolog中读取数据之后写入到磁盘
6.4、事物的并发事物问题
并发事物问题的原因是有多个事物操作数据库产生的问题，主要有以下三个问题：
1、脏读：读取到了别的事物没有提交的数据，比如a读取完b事物修改但是没有提交的数据，读取完后b事物提交，数据发生改变，此时b事物读取到的数据是错误数据
2、不可重复读：事物a读取到了一条数据，此时b事物修改了数据，a继续读取发现数据变了
3、幻读：a事物读取表中一行数据不存在，读取完有 别的线程插入了一条数据， 此时a插入该id的数据失败，再次读取却没有该行数据，产生了“幻觉” ,称为幻读
6.5、事物的隔离级别
二、进阶篇
1、存储引擎
1.1、Mysql体系结构
1.2、mysql的默认存储引擎Innodb
mysql默认存储引擎是innodb ,可以在sql执行窗口执行 show create table xxx 来查看建表语句，发现默认engine=innodb
也可以用show engines;来查看mysql主要的存储引擎，其中innodb是默认存储引擎（mysql5.5之后）
###1.3、Innodb的逻辑区域
1、表空间
2、段
3、区1k
4、页16kb
5、行
以上区域都是自上而下存在包含关
1.4、Mysiam存储引擎
myisam存储引擎是早期的存储引擎，响应速度快，支持表锁，不支持行锁，不支持外键，不支持事物
1.5、存储引擎之memory
数据存储在内存中的数据库，用于存放临时表和缓存，很高效
缺点：数据容易丢失，发生宕机、断电等。。。。
表结构存储在本地，其他在内存
1.6、Innodb和Myisam区别
innodb支持事物、支持表锁、行锁、支持外键
mysisam不支持事物，只支持表锁，不支持外键，响应速度快（早期的默认存储引擎）
1.4、存储引擎的选择
对于存储引擎，考虑到不同场景可以做如下选择：
1、对于更新和删除比较频繁，且在高并发场景下的，优先选择innodb作为存储引擎，因为innodb支持事物，表锁，行锁，外键能够更好的保证数据的一致性、安全性
2、对于新增删除修改比较少，但是查询比较多的场景，推荐myisam作为存储引擎，查询效率高一点
3、对应查询响应速度要求比较高，且对数据一致性，安全性要求比较低的场景下，推荐memory,因为memory是在内存中存储数据，性能很高，和redis一样
2、索引
2.1、索引的概述
索引是一种可以提高数据库查询效率的数据结构
优点：索引可以提高查询效率
缺点：建立索引也需要消耗性能，增删改的时候效率会变低，并不是一定要建立索引
2.2、索引的数据结构
2.2.1、B树
B树也叫平衡树，有阶的概念，当同一级结构汇总数据达到阶的数量后会发生改变，重新组件平衡树的结构
2.2.2、B+树
B+树相比于B树优点在于所有的数据都存放在叶子节点，非叶子节点存放索引，这样数据存储量更大一页有16kb大小，相邻的叶子节点之间有指针相互连接，提高了区间访问查询效率
2.2.3、hash索引
Hash索引是基于哈希表实现的，对索引列进行哈希计算，得到一个hash值，然后将数据维护到索引中去
如果出现hash冲突的情况下，扩展结构成链表就可以延生
优点：高效，查询时间复杂度O（1）（hash冲突）
缺点：不能排序和范围查询
2.2.4、二叉树
一个父节点最多有两字子节点
顺序插入的时候会退化成链表
2.2.3、为什么要用B+树作为mysql索引的数据结构
1、首先二叉树每个父节点下有两个子节点，在顺序查询的情况下会演变为链表，查询效率低下，且不支持范围查询
2、对于B树，每个节点存放数据和指针，如果数据量比较大的情况下，树的层高会比较高，查询效率低下
3、对于哈希索引，不支持范围查询和排序，不推荐
4、B+树，只有叶子节点存放数据，其他节点存放key和指针，指向叶子结点，查询效率稳定，且存放的数据比B树要多（同样一页16kb，b+树非存放指针和key存放的数据就比B树多，因为B树非叶子节点不仅存放指针还存放数据）
所以最终，推荐B+树作为mysql的索引的数据结构
2.3、索引的分类
索引按照功能可以分为：主键索引，唯一索引，普通索引，全文索引
按照查询方式可以分为：聚簇索引和二级索引（非聚簇索引）
聚簇索引是值索引建立在主键或者唯一字段上面，查询到主键之后，就可以直接获取数据
二级索引是值不在主键和唯一字段上面，先通过普通索引查询到指针指向唯一索引的地址，然后再根据唯一索引地址寻找到数据，这个过程叫做回表查询
2.3.1、聚集索引选取规则
1、如果表里面有主键，选择主键索引作为聚集索引
2、如果没有主键、选择第一个唯一索引作为聚集索引
3、如果表里面既没有主键索引，也没有唯一索引，那innodb就默认按照数据行生成一个rowId作为聚集索引
2.3.2、计算B+树数据量
1页的大小为16kb,索引主键bigint大小8个字节、指针6个字节，指针比主键+1
所以 8n+(n+1)*6=16*1024 n = 1170
所以如果是2层的结构的话，叶子节点就有1171*16（一行数据大概1kb,可以存放16行数据）= 18736
如果是3层的话上面结果乘以1171 = 21939856
B+树索引下存储数据量的计算方法：
一个节点下面有多少个叶子节点呢？
首先一页是16kb，一页中假设有n个索引，每个索引中主键数量有n个，指针就有n+1个，主键的大小为8个字节、指针的大小为6字节，因此计算公式为：
8n+6(n+1)=16*64 n就是1170
如果一个二层的b+树，数据量就是 1171 * 16（一个叶子中16k,每一行数据是1k）= 18736
三层的话就是 1171 * 1171 * 16 = 21939856（200+w）
PS:在 叶子节点 中，指针比主键多 1（1 个指向下一个叶子节点的指针）。
在 内部节点 中，指针比主键多 1（指向子节点的指针）。
所以，通常情况下，无论是叶子节点还是内部节点，指针的数量总是比主键的数量多 1。
2.4、索引的语法
2.4.1、创建索引
2.4.1、创建索引
create index idxname on tablename(column...) 可以是多个字段，创建联合索引
create unique index idx_phone on app.login_info(phone)
2.4.2、查看索引
show index from tablename;
show index from app.login_info
2.4.3、删除索引
drop index idxname on tabname;
drop index idx_phone on app.login_info
2.5、索引-性能分析
2.5.1、查询数据库服务器的执行频次
show global|session status like 'Com______';
2.5.2、慢查询日志
查询慢查询日志是否开启
show variable like 'slow_query_log'
如果没有开启的话需要到/etc/my.cnf里面添加配置开启
slow_query_log = 1 开启
慢查询sql的时间可以设置，比如,超过2s就是慢查询
log_query_time=2
设置完成之后，如果有慢查询，sql会在/var/lib/mysql/localhost.slow.log记录
2.5.3、show profiles(了解)
用于查询数据库中所有查询语句的耗时
show profiling;
如果是0就没有开启，如果是1是开启
设置自动开启prifile监控
set profiling = 1
show profiles;
show profile for query query_id
show profile cpu for query query_id
2.5.4、索引-explain性能分析
2.5.4、索引-explain性能分析
id代表mysql连接顺序，id越大，先执行，id相同，从上往下执行
2.5.4.1、explain关键字解析
id:表示sql中表的执行顺序，如果数字相同，说明执行顺序从上往下
如果id值不一样，id越大执行顺序越优先
select_type:表示查询的select 类型 simple primary union subquery
type:访问表的类型 null(不访问表) system(系统表) const(主键或者唯一索引) eq_ref(外键连接) ref(非唯一索引)
2.5.5、索引的使用
加索引之前，在一个100w条数据的表里面进行查询
select * from tb_sku where sku_id = "3234350945u903" 耗时20s+
加入索引： create index sku_id_idx on tb_sku(sku_id);
2.5.6、索引的最左原则
索引最左原则指的是索引是联合索引，a,b,c 入过查询的时候没有a，有bc字段，不会走索引
如果有a字段，没有b,索引部分失效（后面的字段不会走索引）
注意：最左前缀法则字段如果都用在查询中了，顺序无所谓 例如：select * from tablea where b = xx and a = ss and c = mm
范围查询如果出现>,后面的字段不走索引 >=会走索引
show index from dbxxx;
2.5.7、索引失效的7种情况
加了索引的字段
模：模糊查询百分号在前，索引失效
型：数据类型不一致，不走索引，比如字符串不加单引号
数：对索引函数运算操作，不走索引
空：字段有很多空值，用is null 或者字段没有空值 用not null,说白了全表扫描更快
运：数据运算，索引失效
最：没有遵循最左原则，索引失效
快：全表扫描更快，索引失效，比如数据量小的表里面（数据分布影响）
2.5.8、建议使用的索引
2.5.9、回表查询
回表查询就是先查询辅助索引之后，发现还有字段没有在辅助索引里面出现，只能通过辅助索引中id值，回来查询剧集索引树，查询到对应的数据，这个过程叫做回表
2.5.10、前缀索引
创建前缀索引的目的是为了解决字符串数据索引长度过长、占用磁盘IO过大的问题
单列索引是将索引建在单个字段
联合索引是多个字段
联合索引要考虑字段的顺序，优先将有非空约束的字段放在前面
2.5.12、索引的设计原则
1、对于表数据量很大的情况，建立索引，一般超过100w条数据的表，就要建立索引
2、对于经常出现在where条件，order by /group by 语句后面的字段，优先考虑建立索引
3、对于辨识度高，唯一性的字段，优先考虑建立唯一索引，可以很大程度提高查询效率
4、对于字段值是字符串的，且字符串长度比较长的，建立前缀索引
5、尽可能联合索引，查询的时候覆盖索引，避免回表，可以有效降低查询次数，降低io,提高性能
6、尽量控制索引的数量，索引并不是越多越好，索引越多，就要维护，也要牺牲很多性能，影响增删改的效率
7、如果索引不是非null,在建立索引的時候加上not null ,这样查询的时候，优化器可以确定哪个索引更有效的查询
2.6、索引总结
1、索引概述：索引是一种可以提高查询效率的数据结构
2、索引语法：create (unique)index on xxx
3、索引的数据结构 hash/b/b+
4、索引的分类 主键索引、唯一索引、普通索引 全文索引 / 按照查询方式可以分为 聚集索引 二级索引
5、索引的性能分析 执行频次 慢查询日志 profile explain
6、索引的使用
7、索引的设计原则
3、SQL优化
3.1、insert 优化
1、批量插入比单独插入性能高
2、顺序插入比乱序插入性能高
3、load指令性能很高 执行方法如下：
3.2、主键优化
3.2.1、Innodb引擎数据组织方式
innodb中，数据都是根据主键顺序组织存放的，这种存储数据方式的表称为索引数据表
3.2.2、页分裂
页分裂，参照数据存放原则，在innodb引擎当中，数据都是根据主键组织顺序存放的（如下图1），且每一页至少有2条数据（不然会变成链表） ，当数据插入的时候，不是顺序存放的时候，乱序插入，比如页1 存放主键是 1 5 9 23 47的数据 页2存放 主键是55 67 89 101 107的数据，这个时候，如果插入一条主键是50。首先页1在50%的位置分裂 然后是产生一个页3，然后将23,47（后面50%）的数据放到页3，然后要重新计算页与页之间的双向指针，根据主键顺序插入原则，页2和也3互换，页1和也3建立双向指针，页3和页2建立双向指针.如下图2：
页分裂会导致页和页之间的指针要重新计算，原先的页要分裂，也会降低性能
3.2.3、页合并
页合并是因为页中被删除的数据达到设定的阈值，就会触发一个查询，查找相邻页的数据是否有合并的可能，比如50%,那么删除数据达到50%,恰好相邻的页数据里面的数据小于等于50，就可以合并为1页
参数为：MERGE_THRES
3.2.4、主键优化的原则：
1、在满足业务需求的情况下，尽量使用比较短的主键
2、插入数据的时候，尽量使用顺序插入，主键自增
3、尽量不要使用uuid,身份证号码这些作为主键，没有规律，而且比较长，不好维护
4、尽量不要对主键做修改
3.3、order by 优化
3.4、group by 优化
3.5、limit优化
limit在查询中，越靠后的数据，耗时越长
优化方法：覆盖索引+子查询
3.6、count优化
explain select count(*) from tb_user;
mysql中，myisam引擎会将表数据量总数记录到磁盘中，因此执行count(*) 的时候会直接返回这个数，效率很高
innodb就比较麻烦，要进行全表扫描，执行count(*) 的时候会将数据一行一行从引擎里面读取出来，然后累计计数
count(主键),遍历全表，innodb引擎会把每一行的主键id都查询出来，返回给服务层直接加一
count(字段) 遍历全表，innodb会返回每一行字段的值，判断是否为null，不为null 返回给服务层直接加一
count(1) 遍历全表，查询，不取值，每一行放了一个数字1进去 每扫描一行，数据加一
count(*):遍历全表 ，每扫描一行 ，专门做了优化，不取值，每扫描一行数量加一
性能从高到低排序为： count(*) = count(1) > count(主键) > count(字段)
3.7、update优化
a 窗口：
begin
update course set name = 'javaee' where id = 1
b窗口：
begin
update course set name = 'es' where id = 4
执行成功
commit;
a 窗口：
begin
update course set name = 'javaee' where name = 'php' //name字段没有加索引
b窗口：
begin
update course set name = 'es' where name = 'kalfa'
卡住
a 窗口：
commit;
b窗口才执行成功
说明不加索引的字段作为更新条件，容易升级为表锁（加了索引就不会卡主）
update在做更新操作时，where条件后面的字段要加索引，这样就不会将行锁升级为表锁
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
