MySQL基础入门及设计解析
MySQL安装和基本使用
MySQL下载64位适配系统的免费（社区）版本；
- 安装：windows MSI格式，双击运行，安装 Custom；
- 配置：Detailed配置 —— Developer Machine开发使用 —— MultiFunction多功能数据库使用 —— 安装目录默认 —— 并发链接设置OLAP —— MySQL服务的对外端口默认是3306，并勾选在防火墙上打开该端口 —— 字符集选择支持大多数语言的也就是UTF8 —— 初始化作为一个服务（自动启动可以取消），并勾选将常用sql命令直接加入到PATH环境变量中 —— 设置用户名密码，用户名root即可，并勾选允许远程root访问；
- 验证：
- 已安装的mysql作为一个服务
- 控制面板 查看本地服务 找到MySQL，可以启动和停止；
- 终端命令
> mysql
> mysql -u root -p test # 安装好的mysql会自带一个test的数据库，提示输入密码即可
mysql> show databases # 展示数据库
- 安装图形客户端：navcat
- 可以下载支持多种数据库的 Navicat Premium（试用14天）；
- 下载安装；
- 链接mysql：连接名自定义，主机IP、端口3306、用户名、密码；
数据语句
使用数据库的话 模式语句没那么重要；
select语句
新建查询，即可执行相关sql语句以共测试；
-- ## SELECT：查询
-- 查询所有内容
select * from table_name;
-- 基础数据运算 别名
select a1, a2 * 12 as `a 2` from table_name;
-- 写出来的sql 可以用客户端 进行格式美化
-- 任何null参与的计算 结果都是null 为了将空值替换为指定值 可以这样（也可以设计表的时候 填充有意义的值 ）
select a1, a2 * 12 + (CASE WHEN a3 IS NULL 0 ELSE a3 END) as `a2 a3` from table_name;
IFNULL( vice.`index`, 0 ) AS `index`
-- ## DISTINCT：去重
-- 给 字段或字段的组合去重(注意：是组合！)
select distinct a1,a2 from table_name;
-- ## WHERE：条件过滤
-- 比较 逻辑操作 IN
select * from table_name
where a1>=1000 and a1<=2000 and a2 IS NULL or a3 IN (1,2);
SELECT DISTINCT srs.song_id FROM song_collect_new_ranksong srs
WHERE EXISTS (SELECT 1 FROM song_collect_new_rank sr WHERE sr.top_id = 1001 AND sr.id = srs.top_id);
-- 时间比较
select * from table_name
where date1 > '1990-01-01';
-- 模糊查询like
select * from table_name
where a1 like '%a%';
-- %表示0个或多个字符；
-- _代表一个字符；
-- 使用\标记转义
-- ## 排序
order by 字段名1 desc, 字段名2 asc
-- 默认升序
-- 与where混用 放在其后
-- 多字段排序 逐个字段应用
常用函数
-- 一个示例
SELECT FORMAT(STD(orderCount),2)
FROM (SELECT customerNumber, count(*) orderCount
FROM orders
GROUP BY customerNumber) t;
//原文出自【易百教程】，商业转载请联系作者获得授权，非商业请保留原文链接：https://www.yiibai.com/mysql/standard-deviation.html
-- ## 字符串相关
-- 大小写转换
lower('A')
upper('a')
-- 字符串拼接
concat('a','b','c')
-- char_length 字符长度
char_length('aaaa')
-- substring 截取子串
substring('abcdefg', 0,2)
-- ltrim 去掉首尾空字符
-- rtrim
-- trim
-- ## 数值相关
-- abs 取绝对值
-- ceil 向上取整
-- floor 向下取整
-- round 四舍五入
round(3.14159,3) -- 保留3位小数 最后一位四舍五入
-- truncate 截断
truncate(3.14159,3) -- 保留3位小数 其余删除
-- ## 日期相关
curdate() -- 当前日期
curtime() -- 当前时间
now() -- 当前 日期+时间
month(`date`) -- 当前月份
year(`date`) -- 当前月份
hour(`time`)
minute(`time`)
weekday(`date`)
indexDate = date_sub(current_date,interval 2 day)
组函数
把数据分成一组 进行计算 得到一个结果
常用组函数：
-- max
select max(`sal`) from table_name;
select `name` from table_name
where sal = (select max(`sal`) from table_name); -- 用了一个子查询
-- min
-- avg
avg(`sal`)
-- sum
sum(`sal`)
-- count
count(*) -- 如果参数是字段 其中空值不计数
如何将数据分组：不分组时，是将所有数据当成一组；
-- group by 后面接多个字段时 表示 组合字段分组；
select max(`sal`) from table_name group by field_name1,field_name2;
-- 分组 需要使用对应的分组操作 直接在分组上查询分组中不唯一的字段是不对的
-- 如：查询某分组中 某一字段最大的某个人 需要使用表连接
-- 具体过程如下：
-- 1.先把分组查询的结果单独看作为一张表
select department, max(`sal`) from table_name group by department;
-- 2.把要从表中查询的字段单独写一个sql 查出来 其结果看成另一张表
select `name`, sal, department from table_name;
-- 3.连接
select table_name.`name`, table_name.sal, table_name.department from table_name join
(select department, max(`sal`) max_sal from table_name group by department) t
where table_name.department = t.department and table_name.sal = t.max_sal;
如何对分组进行限制：
-- having 子句（where子句是对某些记录进行限制）用来对分组进行限制
select avg(sal) , department from table_name group_by department
having avg(sal) > 2000;
select语句小结
- select 明确 要从哪张表 查询 哪些字段
- where 对数据记录进行过滤
- group by 对过滤后的数据进行分组
- having 对分组进行限制
- order by 对最终结果进行排序
子查询 和 表连接
把中间用到的select语句的查询结果 当做一张单独的表；
- 连接表 还可以使用
自连接，即使用连接条件 将表自己连接自己； cross join：最简单的表连接 就是什么条件都不加，即全连接（连接之后的表记录数是参与连接的两张表记录数的笛卡尔积）；
表连接的两种语法：
-- 92标准
select * from table1, table2, table3 where `表连接条件` and `记录筛选条件`;
-- 99标准
select * from table1 t1
join table2 t2 on (`表连接条件`)
join table3 t3 on (`表连接条件`)
where `记录筛选条件`;
- 外联接
- left join：左外连接，连接之后会将 左边表 多余的数据取出来；（
以左表记录为准 包括没有连上的数据连接出一张新表） - right join：右外连接，连接之后会将 右边表 多余的数据取出来；
- full join：全外联接，左右表均会有多余记录；
- left join：左外连接，连接之后会将 左边表 多余的数据取出来；（
分页
-- oracle
-- 使用 rownum 字段
-- mysql
-- 使用 limit
select ename,sal from emp limit 5-- 取前五条数据
select ename,sal from emp order by sal limit 5-- 取薪水最高的前五条数据
select ename,sal from emp order by sal limit 5,5-- 第二页 从第5条开始取 再取5条（下标0开始）
DDL 和 DML 语句
DDL：数据定义语句
- 主键、自增：
primary key、auto_increment每张表都要有主键； - 联合主键
- 字段数据类型：
age smallint、sex tinyint、小数 decimal，日期相关 date time datetime timestamp； - 字段约束：非空约束
not null、唯一约束unique、外键约束 - 默认值
-- 插入自增值
insert into table1
values (null,,,,,);-- 自增值 传空 则会赋自增值 而非空值
-- 外键约束
FOREIGN KEY fk_外键名(当前表字段名) REFERENCES `其他表`(其他表的主键字段名)
-- 删除表
drop table 表明;
DML：数据操作语句
CRUD
- select
- insert
- update
- delete
-- 所有字段
insert into table1 values
(,,,,,);
-- 部分字段
insert into table1 (field1,,,,)
values (,,,,,);
-- 更新所有记录（使用where达到只更新部分数据）
update table1 set field1 = curdate(), age = 18;
-- 没有where会删除所有记录
delete from table1 where id = 1;
索引
索引可以帮助加快查询，类似一个目录；
- mysql的索引有两种：BTree（实际是B+树）
适合范围查询和Hash适合精准查询； - 但实际上 InnoDB存储引擎 只支持BTree；
-- 创建索引
create index idx_email on table1(email);
create index idx_email using btree on table1(email);
-- 复合索引：查询时 一般使用 两个字段的等于某值 才会更快
create index idx_email_ename on table1(email,ename);
-- 删除索引
drop index idx_email on table1;
对于 name 这类的字符串字段 添加索引后，使用等于匹配记录 是会变快的的，但是使用like进行匹配却不一定，尤其是以
%开头的匹配模式，基本不会受索引影响；
附录1
一些sql：
-- 每分钟执行任务数统计
SELECT
main.`date`,
IFNULL( vice.`index`, 0 ) AS `index`
FROM
(
SELECT
DATE_SUB( CAST( DATE_FORMAT( NOW( ), '%Y-%m-%d %H:%i' ) AS datetime ), INTERVAL id MINUTE ) AS `date`
FROM
music_biz.sys_ordered_number
WHERE
id <= 20
) main
LEFT JOIN (
SELECT
CAST( DATE_FORMAT( send_time, '%Y-%m-%d %H:%i' ) AS datetime ) AS `date`,
COUNT( 1 ) AS `index`
FROM
music_biz_collect.voice_task_record
WHERE
send_time >= DATE_SUB( CAST( DATE_FORMAT( NOW( ), '%Y-%m-%d %H:%i' ) AS datetime ), INTERVAL 20 MINUTE )
AND send_time < CAST( DATE_FORMAT( NOW( ), '%Y-%m-%d %H:%i' ) AS datetime )
AND collect_type = 13
GROUP BY
CAST( DATE_FORMAT( send_time, '%Y-%m-%d %H:%i' ) AS datetime )
) vice ON main.`date` = vice.`date`
ORDER BY
main.`date`
执行计划
explain语句；
explain select * from table1;
-- id：具体sql语句编号，代表了具体的执行顺序
-- type：查询类型 可以表现当前sql语句的执行效率 ！
-- system
-- const
-- eq_ref
-- ref
-- fulltext
-- ref_or_null
-- index_merge
-- unique_subquery
-- index_subquery
-- range 范围查询（优化至少要达到这个级别）
-- index
-- all 全表扫描
-- 上边的值 越靠上 效率越高，越靠下 效率越低；
-- possible_keys：当前查询中可能用到的索引
-- key：当前查询中实际用到的索引（最好用上索引）！
-- key_len：查看所有长度 尤其在组合索引时
-- rows：行数预估值
-- Extra：额外的补充信息 查看文档 这个也很重要 会介绍 对索引 内存的使用情况 ！
索引
海量数据时 如何创建有效索引？为什么使用B+树；
mysql的数据和索引文件 都是存储在磁盘中的，启动mysql服务后，会将数据和索引文件读入内存，因此数据和索引文件的大小会影响内存的使用；
解决方法就是分块读入内存：会利用操作系统的 磁盘预读 、局部性原理（时间局部性 和 空间局部性）；
- 时间局部性：之前访问的数据，很可能很快被再次访问；
- 空间局部性：数据和程序都有聚集成群的倾向，具备某些特征的数据可以放在一起；
- 磁盘预读：内存和磁盘交互的逻辑单位是页（datapage），大小一般是4k或8k，数据读取时，一般读取的都是页的整数倍；
那么使用什么样的数据结构存储：BTree；每个节点包含三种信息：key值 指针 和 存储的行记录；
- 键值：即表中记录的主键
- 指针：存储子节点地址信息
- 数据：表中记录除主键外的数据
每个节点存储在一个磁盘块之中，存储大小为16kb；
B+Tree相比BTree 每个节点包含了更多的信息，及父结点的信息冗余，这样可以：
- 降低树的高度
- 将数据范围变成多个区间，区间越多，数据检索越快；
特点是：
- 非叶子节点存储key和指针，叶子节点存储key、指针和数据；
- 叶子节点两两指针相互连接（符合磁盘的淤堵特性），顺序查询性能更高；
mysql的索引一般是三层或四层的B+Tree，因为3到4层的B+Tree索引结构足以满足千万级别的数据量存储；key占用存储空间越小 所能存储的数据量越大；
注：在B+Tree上有两个头指针，一个指向根节点，一个指向key值最小的叶子节点，所有叶子节点（即数据节点）之间是一种链式环结构；
Innodb存储引擎的4大特点；
主键是int类型的情况下 要尽量使用自增，因为这涉及到一个索引维护的问题，自增主键更有利于索引维护；
做数据迁移的时候，也最好把索引关掉，因为边迁移数据边创建索引，是一个持续维护索引维护的过程，会频繁进行索引B+Tree的重组，十分耗时；
存储身份证号 是否需要把身份证号 设置为主键？
- 自然主键：充当主键同时，也是构建记录的组成部分；
- 代理主键：不具有业务意义，仅具有主键功能；（推荐）
索引分类：
- 主键索引：创建主键 就是索引列
- 唯一索引：唯一值的列
- 普通索引：非主键 非唯一
- 全文索引：用来作全文检索的，但在数据库层面做这个效率是极低的，一般用solr或es实现；
- 组合索引：多个字段值 共同构成索引
索引是不是越多越好？
- 不会，索引多了 维护会很麻烦，且占用存储空间变大，会导致io增多
- 一般不要超过5个索引，按需求适当创建；
聚簇索引和非聚簇索引：
- 聚簇索引：数据跟索引绑定存储在一起的；
- 非聚簇索引：数据跟索引绑定分开存储的；
InnoDB中 既有聚簇索引、也有非聚簇索引；
- 一个表的多个索引，每个索引会存储为一个B+Tree；
- 多棵B+Tree的数据存储只有一份；其他索引的的叶子节点 只存储 主键（实际可能有以下几种）；
这里
主键的理解：
在Innodb存储引擎中，数据在进行插入的时候需要更某一个索引列绑定在一起，这个索引列如果有主键，就使用主键，没有就使用唯一键，再没有就用6字节的rowid（隐藏主键），可以称之为跟数据绑定存储的索引列的值；
存储引擎：能实现对数据文件在磁盘上的组织；
.frm文件存储表结构.idb文件存储数据和索引文件；（聚簇索引）.MUD文件存储数据文件；（非聚簇索引）.MYI文件存储索引文件；
bool值能做索引吗？
- 能，但是没什么意义，最终并不会加快检索速度；实际建索引时，需要注意索引的选择性；
- 使用命令行
show index from 表名查看表对应的索引，其中有一列Cardinality（基数distinct value），实际创建索引的时候 需要索引列的dv值（其实就是唯一值）的个数 占 总数的80%以上，才适合建索引（注意低于80%不是不能建）；
回表、索引覆盖、最左匹配：
- 回表：查询的时候 如果有索引，会根据索引在B+Tree中找到叶子节点的id值，再根据id到主键索引的B+Tree读取整条记录，这种查询方式就叫
回表，这种方式效率并不算高，实际使用要尽量避免；（如果执行计划 Extra中出现了 using index，则可以确定肯定没有回表） - 索引覆盖：查询的时候 如果有索引，会根据索引在B+Tree中找到叶子节点的id值，这里如果查询的就是索引列和id值，就可以直接返回（此时当前索引的B+Tree叶子节点包含了全部要查询的信息），而无需回表，这种叫
索引覆盖（覆盖了主键索引回表的过程），效率很高，推荐使用；- name索引列的key是name，value是id（即跟数据绑定存储的索引列的值）
- id主键索引的key是id（跟数据绑定存储的索引列的值），value是整条行记录；
- 最左匹配：针对组合索引的情况，要满足优先匹配左边的列；
- 如 组合索引是 name和age
- 那么， where name and age
- where name
- where age and name（mysql优化器组件 会将其优化为符合最左索引的方式）
- 都会使用组合索引；
- 但，where age则不会使用组合索引
优化器 和 mysql架构：
- 架构
- 客户端：jdbc navicat 负责提交sql语句
- Server：mysql服务，包括：
- 连接器：管理链接 验证权限
show processlist显示链接数 - 分析器：词法分析 语法分析
- 优化器：按照成本进行优化
- 执行器：执行具体sql
- 连接器：管理链接 验证权限
- 存储引擎
字段cityName使用varchar存储，字符串长度较长，直接建索引的话可能不太合适，这里可以截取一段用于索引创建；具体取多长，可以统计下：
select * from citys- 全部city统计：
select count(*) as cnt, city from citys group by city order by cnt desc limit 10- 前3个字符的city统计：
select count(*) as cnt, left(city,3) as pref from citys group by pref order by cnt desc limit 10- 逐步去前n个字符对比两个量级，基本一致的n，就是最重要截取的；
alter table citys add key(city(7));
如果某个字段列的值 会频繁的进行正删改 那这个字段是不适合做索引的！
hive和mysql是两个不同的技术栈：
- OLAP 联机分析处理 数据仓库 hive 对历史数据分析
- ALTP 联机事务处理 数据库 mysql 支持业务数据的查询
分布式
-
主从复制：
- 不同数据库之间的数据同步，需要使用数据库的
binlog（默认是关闭的）；当对数据库A进行操作时，会同步的向其binlog中写入日志文件；配置另一个数据库B可以读到指定数据库A的binlog文件，通过IO Thread线程，读取binlog中的数据到relaylog（中继日志）中；继续通过SQLThread线程读取relaylog，将数据写入数据库B； - 这个过程 需要依赖网络传输，同时也存在着延时（读写不同步，读取是随机读，写是顺序写）；多线程操作的过程中，IO的量也很大；
- 为了解决延时，可以使用
MTS（mysql5.7之后）：为了提高效率使用组提交；
- 不同数据库之间的数据同步，需要使用数据库的
-
读写分离：
- 数据库 读写操作 分开放到不同的mysql服务上（或者相同服务的不同数据库），这实际是一个负载均衡的过程；（读写也可以不分离！）
- 一般是读多写少，但是要保证各个数据库之间数据是同步的；
-
分库分表
- 垂直 和 水平切分
- 分片键：用于定位所在子库，查询的时候最好带上，否则会轮询所有子库；
不同事务的数据一致性
- 事务
- ACID：原子性 一致性 隔离性 持久性
- 原子性：要么全成功or全失败，通过
undolog实现，实际是一个逻辑日志，记录的是相反的操作，用户将数据回滚到之前的状态； - 隔离性：MVCC 和 锁；
- 持久性：
redolog；- WAL（write ahead log 预写日志）：实际数据没有写成功，但只要日志存在就可以根据日志来恢复数据（重放操作）；
- 二阶段提交：先在内存里改，先写redolog、再写binlog，最后在磁盘里改；
- 最终目标就是为了保证一致性
- 原子性：要么全成功or全失败，通过
- ACID：原子性 一致性 隔离性 持久性
隔离级别：
- 读未提交
- 读已提交（RC）
- 可重复读（RR，mysql默认）
- 串行化
隔离性越高 效率越低；
MVCC多版本并发控制：解决数据库并发读写问题；
- 快照读：读取的是mysql数据的历史版本，select
- 当前读：读取最新结果，select lock in share mode（update、insert、delete）
- MVCC指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是mysql为了实现MVCC的非阻塞功能而实现的；
隐藏字段：
DB_TRX_ID：最近修改事务ID，记录创建当前记录或者最后一个修改的事物id；DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本DB_ROW_ID：隐藏主键
undolog：回滚日志，记录的是之前数据的历史版本；
- undolog会形成一个链表，链表首存储最新的旧记录，链尾存放最旧的旧记录；
- undolog不会无限膨胀，会通过后台purge线程进行管理，当发现当前记录不需要回滚且不需要参与MVCC的时候，就会把数据清理掉；
readview：
当事务进行快照读（select）时，会生成一个读视图，来进行可见性判断（由可见性算法确定）；- 其他事务里能否读取到当前事务里的数据；
- trx_list：当前系统活跃的事物id列表；
- up_limit_id：活跃事务列表中最小的id；
- low_limit_id： 当前系统尚未分类的下一个事务id；
可见性算法：
- 首先比较 DB_TRX_ID 是否小于 up_limit_id，小于则当前事务能看到 DB_TRX_ID 事务所操作记录，不小于则进入下一判断；
- 接下来判断 DB_TRX_ID 是否大于等于 low_limit_id，大于等于则 表示 DB_TRX_ID 所操作记录是在 readview 生成后才出现的，对当前事务肯定不可见，如果小于，则进入下一判断；
- 判断 DB_TRX_ID 是否在活跃事务中，若在，则代表 readview 生成时，这个事务还在活跃状态，还未 commit，修改的数据，当前事务也看不到；如果不在，则说明这个事务在 readview 生成前 就已经 commit，那么修改的结果是能够看见的；
对于不同的隔离级别：
- RC级别：每次执行快照读 都会生成新的 readview；
- RR级别：只在当前事务的第一次快照读的时候，生成 readview，之后的快照读都会用第一个（
连续多个select 后边的select会沿用 之前的 readview，而不会重新生成）；
幻读问题：
- 如果当前的所有操作都是当前读，就不会产生幻读问题，只有当前读和快照读一起使用时才会有；
- 可以通过加锁解决这个问题；
附录2
锁
-
按粒度划分：
- 行锁：锁住的某一行或多行记录将无法被访问，不影响其他记录，粒度相对小些；
- 间隙锁：行锁的一种
- 临建锁：是InnoDB的行锁默认算法，相当于记录锁和间隙锁的组合；它会把查询出来的记录锁住，同时也会把该范围内的所有间隙空间锁住，还会吧相邻的下一个区间也锁住；
- 表锁：对表加锁，粒度大，加锁简单，也容易冲突；
- 记录锁：只对某一条记录，加了记录锁之后的数据可以避免数据在查询时被修改的重复读问题，也避免了在修改的事物未提交前被其他事务读取的脏读问题；
- 页锁：mysql中介于行锁和表锁之间的一种锁；会出现死锁；
- 行锁：锁住的某一行或多行记录将无法被访问，不影响其他记录，粒度相对小些；
-
按读写划分：
- 共享锁：读锁（S锁），当一个事务加上读锁后，其他事务只能对该数据加读锁，直到所有读锁释放，才能加写锁；主要是为支持并发读数据，读数据时不支持修改，避免出现重复读的问题；
- 排它锁：写锁（X锁），当一个事务加上写锁后，其他请求不能再为数据加任和锁，直到锁释放；主要是为避免数据在修改时，又被其他人修改或读取，防止出现脏数据和脏读的问题；
-
实际是否加锁
- 乐观锁：不加
- 悲观锁：加
字符集的选择：UTF8-MB4
适当的数据冗余：
- 被频繁引用且只能通过join两张或更多大表的方式才能得到的独立小字段；
- 冗余的数据 需要确保数据的一致性不遭到破坏，确保更新的同时冗余字段也被更新；
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
