Mysql

一、基础

数据库选型

SQL和NoSQL

1. SQL

数据结构化存储在二维表中
支持事务
使用场景: 数据之间存在一定关系, 需要事务支持的业务场景

2. NoSQL

存储结构灵活, 没有固定的结构
对事务支持比较弱, 但对数据的并发处理性能高
使用场景: 数据结构不固定的场景, 对事务要求不高, 但读写并发比较大的场景

数据库结构设计

1. 分析

2. 逻辑设计

1. 宽表模式

所有属性都存储在一张表中
优点: 设计简单
缺点: 存在数据冗余, 数据插入, 更新, 删除异常
使用场景: 数据报表应用

2. 数据库设计范式
   1. 第一范式

   表中的所有字段不可再分
   即属性最小化, 不能再划分

   2. 第二范式

   必须存在业务主键, 并且非主键依赖于全部业务主键,
   即存在一个唯一主键, 找到某行数据

   3. 第三范式

   表中非主键列之间不能相互依赖
   即所有非主键都依赖于主键, 比如, 一张学生信息表, 主键是学号, 如果属性有学院, 院长, 就违反了第三范式, 因为院长依赖于学院, 而不是学号. 需要把学院和院长单独成一张表.

3. 反范式化设计

空间换时间
越严格的范式化设计, 表就会越多, 需要用到的关联查询就越多, 效率就会较低

3. 物理设计

1. 存储引擎

   1. MyISAM

   5.6版本以前的默认引擎, 最常用的非事务型存储引擎

   2. CSV

   以CSV格式存储的非事务型存储引擎

   3. Archive

   只允许查询和新增, 而不允许修改的非事务型存储引擎

   4. Memory

   数据存储在内存的存储引擎

   5. InnoDB

   5.6之后的默认引擎, 最常用的事务型存储引擎
   数据按主键聚集存储
   支持行级锁及MVCC
   支持Btree和自适应Hash索引
   5.7之后支持全文和空间索引

2. 数据类型

   1. 整数(signed/unsigned)

   tinyint 1
   smallint 2
   mediumint 3
   int 4
   bigint 8

   2. 实数

   float 4 非精确
   double 8 非精确
   decimal 每4个字节存储9个数字, 小数点占一个字节 精确
   decimal(p, d) p表示有效数字, d表示小数点后有几位

   3. 时间

   date 3
   time 3-6
   year 1
   datetime 5-8
   timestamp 4-7 按时区存储

   4. 字符串

   char 固定长度 最大255
   varchar 可边长 65535
   tinytext 255
   text 65535
   MediumText 16777215
   LongText 4294967295
   Enum 65525

   5. 二进制

   tinyblob 0-255
   blob 0-65535
   mediumblob
   longblob

3. 为数据选择合适的数据类型

   1. 优先选择符合的最小类型
   2. 避免使用enum, text
   3. 财务类型必须使用decimal

4. 命名

   1. 所有数据库对象名称用小写字母, 下划线分割
   2. 不能使用保留关键字
   3. 见名识意
   4. 临时表以tmp为前缀, 日期为后缀
   5. 备份表以bak为前缀, 日期为后缀

SQL语句

1. DCL(数据控制语句)

1. create user

CREATE USER username@‘ip’ IDENTIFIED WITH ‘mysql_native_password’ by ‘password’

2. grant

GRANT select ON mysql.user TO username@‘ip’
GRANT select(col1, col2) ON mysql.user to username@‘ip’

4. revoke

REVOKE select ON mysql.user from username@‘ip’

2. DDL(数据定义语句)

1. create
   1. create database
   2. create table
   3. create index
   4. create view
2. alter
   1. alter database
   2. alter table
   3. alter view
3. drop
   1. drop database
   2. drop table
   3. drop index
   4. drop view
4. truncate table(先drop 再create)
5. rename table

3. DML(数据处理语句)

1. insert

INSERT INTO 表名 字段 values(值)

2. delete

delete from 表名 where

3. update

update 表名 set 字段=值

4. select

SELECT * FROM JOIN ON WHERE GROUP BY HAVING ORDER BY LIMIT
WHERE 字段 LIKE ‘xxx’ % _ [] [^]
WHERE 字段 is not null…is not…between a and b
GROUP BY 字段 asc/desc
关联查询: inner join…outer join
内关联, 返回两边都有的数据
左关联, 返回左表的在限制条件下所有数据
可以查询左表有而右表没有的数据 LEFT JOIN 右表名 ON a.id = b.id WHERE b.id IS NULL
GROUP 不能用where 要用having
distinct

5. 函数

   1. 聚合函数

   count(*)
   sum(col)
   avg(col)
   max(col)
   min(col)

   2. 时间函数

   curdate()
   curtime()
   now()
   date_format(date, fmt) %Y%m%d %H%i%s
   sec_to_time(seconds)
   time_to_sec(time)
   datediff(date1, date2)
   date_add(date, interval expr unit)
   extract(unit from date)
   unix_timestamp()
   from_unixtime()

   3. 字符串函数

   concat(str1, str2,…)
   concat_ws(sep, str1, str2, …)
   char_length(str)字符
   length(str)字节
   format(x, d[,locale])
   left(str,len)
   right(str, len)
   substring(str, pos [,len])
   substring_index(str, delim, count)
   locate(substr, str)
   trim([remstr from] str)

   4. 其他函数

   round(x, d)
   rand()
   case when then else end
   mdt(str)

   5. 窗口函数

   row_number() 按顺序
   rank() 同分数, 同名, 有间隙
   dense_rank() 无间隙
   select a, b, c, ROW_NUMBER() OVER(partition by x order by x )

6.公共表表达式CTE

8.0版本之后的新特性
cte生成一个命令临时表, 只在查询期间有效
with cte_name as (query)

4. TCL(事务控制语句)

注意事项

使用slect … limit 1 判断是否存在数据
使用row_count()判断修改行数
不要在on中过滤条件, 在where中过滤
使用join代替子查询

SQL优化

对慢查询的语句, 进行执行计划分析, 如果没有用索引, 就建立索引, 如果用了索引还是慢, 就改写SQL语句

1. 发现问题

1. 用户上报应用性能问题
2. 慢查询日志

配置MySQLman查询日志
set global slow_query_log = [ON|OFF]
set global slow_query_log_file = path
set global long_query_time = second
set global log_queries_not_using_indexes = [ON|OFF]
慢查询分析工具
mysqldumpslow
pt-query-digest

4. 数据库实时监控长时间运行的SQL

select *
from information_schema.PROCESSLIST (这个表记录执行信息)
where TIME >= 60

2. 分析执行计划

1. 获取执行计划

explain SQL
explain select * from table;
id: 相同, 由上往下, 不同, 大的优先
select_type:
table:
partitions:
type:
possible_keys:
key:
ke_len:
ref:
rows:
filtered:
extra:

3. 优化索引

优化SQL查询所涉及到的表中的索引
索引:告诉存储引擎如何快速的查找到所需要的数据, 类似书的目录, 可以直接找到想要的内容的位置

1. Innodb支持的索引类型
   1. Btree索引

   适用于全值匹配的查询
   使用与范围查询
   从索引的最左侧列开始匹配查找列

   2. 自适应hash索引
   3. 全文索引
   4. 空间索引
2. 在哪些列上建立索引
   1. where子句中的列, 一列中的数据基本不重复, 如果重复量多, 就不适合建立索引.
   2. order by, group by, distinct中的字段,
   3. join查询的关联列
3. 选择符合索引键的顺序
   1. 区分度最高的列放在联合索引的最左侧
   2. 使用最频繁的列放在联合索引的最左侧
   3. 尽量把字段长度小的列放在联合索引列的最左侧

4. 改写SQL

改写SQL以达到更好的利用索引的目的

1. 使用外关联代替not in
2. 使用CTE代替子查询
3. 拆分复杂的大SQL为多个简单的小SQL
4. 用计算列优化查询

5. 数据库切分

6. 事务

事务是数据库执行操作的最小逻辑单元
事务可以由一个SQL组成, 也可以由多个SQL组成
组成事务的SQL要么全部执行成功要么全部执行失败
start transaction/begin
commit/rollback

1. 特性
   1.原子性(A)

   一个事务中的所有操作, 要么全部成功, 要么全部失败

   2.一致性©

   事务开始之前和事务结束之后, 数据库的完整性没有被破坏, 比如银行转账A有100, B有100, 合起来200, A给B转50, 之后A有50, B有150, 合起来还是200

   3 .隔离性(I)

   要求每个读写事务的对象与其他事务的操作对象能相互隔离, 即该事务提交前对其他事务都是不可见的.

   4 永久性(D)

   事务一旦提交, 其结果就是永久的了, 即使发生宕机等事故, 数据库也能将数据恢复

2. 并发带来的问题

   脏读是读出无效数据, 而不可重复读, 是读出有效数据.

   1. 脏读(读已提交)

   一个事务读取了另一个事务未提交的数据
   事务1修改了一个数据, 还未提交, 事务2读取这个数据
   之后事务1rollback, 事务2再读取这个数据, 和之前的数据不一样, 就是脏读

   2. 不可重复读(可重复读)

   一个事务前后两次读取的同一数据不一致
   事务1读取一个数据, 未commit, 然后此时事务2修改该数据, 并且commit
   事务2再读取该数据, 前后值不一样, 就是不可重复读.

   3. 幻读(串行化)

   一个事务两次查询的结果集记录数不一致
   .> 事务1第一次读, 有5行数据, 事务2添加, 或者修改了某条数据, 并commit
   事务1再读, 不再是5行数据, 而是其他数量

3. 事务隔离级别

   1. 读未提交
   2. 读已提交
   3. 可重复读
   4. 串行化/顺序读

4. 修改事务隔离级别

set transaction isolation level
persist 当前连接, 之后所有连接, 永久修改
global 之后所有连接, 重启无效
session 只有当前连接

5. 事务带来的阻塞

由于不同锁之间的兼容关系, 造成的一事务需要等待另一个事务释放其所占用的资源的现象

二、原理

三、