mysql

数据库

DBMS数据库管理系统(C/S架构)

• 数据库DB1

  • 表1

    • 存储方式: 表的一行称为记录,在java对应一个对象

  • 表2…

• 数据库DB2

• 数据库DB…

• 网络程序在3306监听

客户端

• 用来操作数据库: web程序,用java JDBC操作mysql数据库

SQL

DDL

• 数据定义语句: 创建表,库

  • 字符集: 默认utf8 校对规则(collate),utf8_bin区分大小写,utf8_general_ci不区分大小写, 默认是utf8_general_ci 表默认使用数据库的, 表的列默认使用表的, 这个三个都可以单独设置, 修改不影响之前设置的规则

  • DB

    • 查看DB

      show databases
      show create databases db_name
      show create database db01;
      

    • 创建DB

      1
      2
      create database [if not exists] db_name
      create database if not exists db03 character set utf8 collate utf8mb3_bin
      

    • 删除DB

      1
      drop database if exists db03;
      

  • table

    • 查看表

      1
      2
      3
      show tables //查看数据库所有表
      show create table user;// 查看表定义
      desc table_name
      

    • 创建table

      create table table_name(
      field1 datatype,
       field2 datatype, ...
      ) character set 字符集 collate 校对规则 engine 存储引擎
            
      field:列名
      datatype:数据类型
      character set 字符集 如果不指定默认为数据库的字符集
      collate:如过不指定默认为数据库的字符集
      engin: Innodb支持事物
      

      • 自增长

        • id int primary key auto_increment,

        • 一般自增长配合primary key来使用

        • 自增长也可以单独使用, 但是需要一个unique

        • 自增长默认从1开始, 也可以通过 table tbl_name auto_increment = 新的开始值

        • 如果添加数据指定值, 则以指定值为准, 如果指定了自增长, 就按自增长的规则

    • 修改表

      • 添加列

        alter table table_name ADD (column datatype [default exper] [, column datatype]...);
        

      • 修改列

        alter table table_name MODIFY (column datatype [default exper] [, column datatype]...);
        

      • 修改列名

        alter table employee change column old_name new_name datatype)
        

      • 删除列

        1
        alter table table_name DROP (column);
        

      • 修改表名

        rename table old_table_name to new_table_name
        

      • 修改字符集

        alter table table_name character set charset_name
        

    • 删除表

      drop table tbl_name
      

    • 复制表/常用于备份表

      • 自我复制

      • 复制其他表的数据结构

        create table user_bak like user;
        insert into user_bak select * from user;
        

      • 如何对一张表进行去重

# 去除一张表重复的记录 创建一张新表
create table emp_bak_2025_2_15_13_07 like emp;
# 复制旧表数据到新表时, 用distinct 关键字去重
insert into emp_bak_2025_2_15_13_07 select distinct * from emp;
# 清空又重复数据的表
delete from emp;
# 复制去重的数据到原表
insert into emp select * from emp_bak_2025_2_15_13_07;
# 删除临时表
drop table emp_bak_2025_2_15_13_07;

DML

• 数据操作语句:insert,delete,update

  • insert

    • 指定列insert into table_name(column1,column2,…) values(val1,val2,…)

      或者不指定列,插入所有字段 insert into table_name values(val1,val2,..)

    • 细节

      • 1插入的数据类型应该和字段的数据类型相同

      • 2数据的长度不要超过字段定义的返回

      • 3values的值的列表应该和定义的字段顺序一致

      • 4字符和日期数据类型应该用单引号引起来

      • 5列可以插入空值,只要约束非not null

      • 6添加多条插入 insert into table_name(col1,col2,..) values(val1,val2,..),(val1,va2,..), …, ;

      • 7.如果是给表中的所有字段添加数据,可以不用写前面的字段名称

      • 8默认值的使用, 如果field设置为not null, 插入时确没有设置值, 则会报错 如果field可以为null则,插入时没有赋值,默认值就是null, 一般not null需要给配置一个默认值

  • update

    • update tab_name set column1 = val1, column2 = val2 ,… where id = 1 没有where条件会对所有记录进行修改

  • delete

    • delete from tbl_name where id = 1 没有where条件会删除整张表中记录

DQL

• 数据库查询语句: select

  • SELECT [DISTINCT] *

    {column1, column2, column3..} from tbl_name

    • distinct 查询结果的每个字段都相同才去重

  • where子句中常用运算符

    • 比较运算符

      • > < >= <= = <> !=

      • between a and b 是[a,b]闭区间

      • in (e1,e2,…)

      • like ‘模糊匹配’

        • %匹配任意个字符 _匹配一个字符

      • is null 判空

    • 逻辑运算符

      • and

      • or

      • not

  • order by 子句

    • order by col1 desc, col2 asc, col3 desc, …

    • order by 指定排序的列, 可以是列名, 也可以是查询结果列

    • ASC升序(默认), DESC降序

    • order by位于select语句末尾

  • 统计函数(sum,avg,max/min只对数值起作用)

    • count

      • select count(*)

        count([distinct] 列名) from tbl_name where condition

      • 返回总行数

      • count(*) 和count(列名)区别 : * 返回总记录数 count(列名): 返回总记录数, 排除列的值为null的

    • sum

      • select sum(col1),sum(col2),.. from tbl_name where condition

      • sum仅对数值起作用

    • avg

    • max/min

  • 分组子句group by 和having子句

    • select col1,col2,…from tbl_name group by column having ….

    • group by分组, having对分组的结果过滤

    • 确保 SELECT 子句中的所有非聚合列都包含在 GROUP BY 子句中，或者对这些列进行聚合

  • 分页查询

    • limit 每页显示记录数*(第几页-1), 每页显示记录数

  • sql顺序

    • group by having > order by >limit

• 多表查询语句

  • 笛卡尔集:多表查询的结果就是笛卡尔集, 要获取正确的结果, 需要利用where语句进行过滤

  • 多表查询条件不能少于表的个数-1,否则会出现笛卡尔积集

  • 自连接

    • 在同一张表上进行连接查询,将同一张表当作两张表来查询

  • 子查询

    • 在其他sql中嵌入其他sql语句中select语句

      • select * from emp where emp_id in (select distinct id from emp where sal > 2000)

    • 可以将子查询当作一张临时表

      # 查询所有商品类别中价格最高商品, 其中利用了子查询作为了一张临时表来提供类别最大价格的数据,然后外层通过连接子查询临时表来获取结果
      select goods_name, goods.shop_price
      from ecs_goods goods,
       (select t.cat_id, max(t.shop_price) as max_price from ecs_goods t group by t.cat_id) temp
      where goods.cat_id = temp.cat_id
      and goods.shop_price = temp.max_price;
      

    • all和any

      # 显示比部门30的所有员工工资都高的员工信息
      select * from emp t2 where t2.sal > all (select t.sal from emp t where t.deptno = 30);
      

      # 显示比部门30的任意一个员工工资都高的员工信息
      select * from emp t2 where t2.sal > any(select t.sal from emp t where t.deptno =30);
      

    • 多列子查询

      • (字段1,字段2,..)=(select 字段1, 字段2,.. from)

        # 查询作弊的学生, 学生各科成绩完全和学生'宋江'一样
        select *from student s 
        where (s.chinese, s.math, s.english) = (select chinese, math, english from student where name = '宋江');
        

      • 每个部门最高工资的人 多列子查询

        select deptno,max(sal) max_sal from emp group by deptno;
        # 方法1
        select * from emp
        where (deptno, sal) in (select deptno, max(sal) max_sal from emp group by deptno);
        # 方法二, 子查询作为临时表
        select * from emp t,
             (select deptno, max(sal) max_sal from emp group by deptno) temp
        where temp.deptno = t.deptno
          and temp.max_sal = t.sal;
        

合并查询

​ union 不包括重复的

mysql连接查询语法

内连接: 驱动表和被驱动表都有记录才显示

​ 等价语法

​ select … from tab1, tab2, …

​ inner join on

​ join on

​ cross join on

外连接

​ 等价语法

​ 左外连接

​ left join

​ left outer join

右外连接/与左一样

驱动表有,被驱动表没有的记录时, 显示驱动表 + 被驱动表null

DCL

• 数据控制语句: 管理数据库, grant, revoke(详见mysql用户与权限)

mysql函数

字符串相关函数

• charset(str)返回字符集

• concat(string,..)连接字符串

• instr(string,substring)返回substring在string出现的位置,没有返回0

• ucase:转换成大写

• lcase转换成小写

• left(string, len)从string的左边取len个字符串

• length(string)string长度,按字节

• replace(str, new_str,old_str),用new_str替换str中的old_str

• strcmp(str1,str2)比较两个字符串大小,字典顺序 substring(str, position,len), 从str的position开始(从1开始计算),去len长度

• ltrim(string),rtrim(string),trim(string) 去除对应的空格

数学函数

• abs(num) 绝对值

• bin(十进制数)将十进制数转换二进制数

• celling(num),向上取整,得到比num大的数

• conv(n1, from_base, to_base) 进制转换

• floor(num)向下取整,得到比比num2小的整数

• format(number,decimal_places)保留小数位数(四舍五入)

• HEX(Decimal_number)转换成十六进制

• least(num1,num2,num3,…)求最小值

• MOD(numberator, denominator), 求余

• RAND([seed]) 返回一个随机浮点值,范围在0-1之间[0,1]闭区间,若他指定一个整数参数N,则他被用作种子值, 用来产生重复序列

日期日期函数

• current_date() 返回当前时间

• current_time()当前时间

• current_timestamp当前时间戳

• date(datetime)返回datetime的日期部分

• date_add(date1, interval_d_value_type) 在date2中加上日期或者时间

• date_sub(date1, interval_d_value_type) 在date2中减去日期或者时间

• datediff(date1,date2) 两个日期相差天数

• Timesdiff(date1,date2)两个时间差, (多少小时,多少分,多少秒)

• now() 当前时间

• Year

  Month

  Date(datetime)年月日

• From_unixTime()

  • 把unix时间戳转换为指定格式的日期

• unix_timestamp()

  • 1970-1-1到现在的秒数

加密和系统函数

• user()查询用户

• database()数据库名称

• MD5()为字符串计算一个32位的字符串,加密

• PASSWORD(str)从原文密码str计算并返回字符串,通常用于对mysql数据库的用户密码加密

流程控制函数

• if(expr1,expr2,expr3)

  • 如果expr1为真,返回expr2,否则返回expr3

    • 类似java中的三元运算符

• ifnull(expr1,expr2)

  • 如果expr1不为为null,则返回expr1,否则返回expr2

• case when expr1 then expr2 when expr3 then expr4 else expr5 end; 类似于多重分支

约束

主键primary key(主键)

• 用于唯一标识记录的数据, 当定义主键后, 该字段不能重复

• 主键不能重复且不能为null

• 一张表只能有一个主键,但可以是复合主键

• 使用方式

  • 直接在字段名后指定 字段名 primary key

  • 在表的定义的最后写 primary key(字段名)

not null

• 插入数据时不能为null, 或者提供了默认值

unique

• 定义了唯一约束后, 该列值不能重复

• 如果给没有指定not null 则unique字段可以有多个null

• 一张表可以有多个unique字段

• unique + not null 效果相当于主键

• 会创建一个索引

foreign key

• 使用案例:foreign key (stu_id) references tbl_father(id)

• 子表和父表

  • 如果A表中的某个列或者某些列依赖表B, 那么A表就称为B表的子表

• 外键: 子表中依赖父表的列可以设置外键约束

  • 当向子表插入数据时, 会在父表中检查一下外键在父表中是否存在

• 父表中被子表依赖的列表必须建立索引,unique和主键默认有索引

• innodb引擎才支持外键

• 外键字段要和父表主键字段一致

• 一旦建立外键后数据, 父表数据就不能随便删除了

• 外键字段可以设置not null约束,默认可以为null

check

• 使用例子 gender varchar(8) check ( gender in (‘m’,’f’)), sal double check ( sal > 1000 and sal < 2000 )

• 用于强制进行数据必须满足的条件, 假定在sal列上定义了check约束,并且要求sal值在1000-2000之间如果不再1000-2000之间就会提示出错

• mysql5.7还不支持,只做语法校验, 但不会生效 mysql 8.0生效了

• 其他数据库支持check, oracle, sqlserver

索引

实例

#查看查询的时间 ,太慢了
select * from emp where empno = 1234567; -- 2s
#使用索引来优化
create index empno_index on emp(empno);
select * from emp where empno = 1234567; -- 51ms,提升了400倍

创建索引 emp.ibd 512mb, 创建后 640mb, 创建有空间开销

创建的索引后, 只对创建索引的列有效 emp.ibd 640mb –> 808mb

select * from emp where ename = 'HQfdaS'; -- 2s --> 55ms
create index enameno_index on emp(ename);

删除索引

drop index empno_index on emp;

索引原理

• 没有索引为什么慢? 因为全表扫描,线性搜索

• 索引为什么快? 形成一个索引的数据结构, 比如B+树

• 索引代价

  • 磁盘占用

  • dml(update,insert,delete)变慢

  • 当然select使用率更高

• 索引类型

  • 主键索引 主键自动为主索引

  • 唯一索引 unique

  • 普通索引 index

  • 全文索引 fulltext 适用于MyISAM

  • 一般开发, 不使用mysql自带的全文索引, 而使用全文搜索solr和elasticsearch

使用索引

• 添加索引

  • create [unique] index index_name on table_name(col_name)

    • unique索引等价于唯一约束

  • alter table tbl_name add index index_name (col_name)

  • 添加主键: alter table tbl_name add primary key (col_name)

• 删除索引

  drop index index_name on tbl_name
  alter table tbl_name drop index index_name
  #删除主键:
  alter table tbl_name drop primary key
  

• 查询索引

  show index(es) from tbl_name
  show keys from tbl_name
  desc tbl_name
  

什么时候创建索引

• 使用频繁的作为查询字段应该创建索引 select * from emp where empno = 1

• 唯一性太差的字段不适合创建索引, 即使频繁作为查询条件

• 更新非常频繁的字段不适合创建索引

• 不会出现在where子句中的字段不应该创建索引

mysql事务

事物用于保存数据的一致性, 他有相关dml语句组成, 该组的dml语句要么全部成功, 要么全部失败, 比如转账的例子

事务和锁

• 当执行(事务操作时)dml语句, mysql会在表上加锁, 防止其他用户来修改数据

mysql控制事务的重要操作

• start transaction 开启事务

• save point 设置保存点

  • rollback前面的保存点, 会自动删掉后面的保存点 commit后会删除保存点

• rollback to 保存点名称

  • 回退事务至保存点

• rollback

  • 回退所有事务

• commit

  • 提交事务, 不能回退

    提交事务, 删除保存点,释放锁数据生效,其他会话可以看到事务提交后的数据,数据正式生效

• 事务细节

  • 1.默认情况下, dml操作是自动提交的, 不能回滚

  • 2.如果开始一个事务,没有创建保存点, 可以执行rollback, 默认就是会退到事务开始的状态

  • 3.可以在事务中创建多个保存点

  • 4.在事务没有提交前, 可以选择回退到指定的保存点

  • 5.msyql点事务机制需要innodb的存储引擎才可以使用

  • 6.开始一个事务 start transaction 或者 set autocommit =off

mysql事务的隔离级别

• https://zhuanlan.zhihu.com/p/117476959

• 0.保证事务不受外部并发操作影响的独立环境下运行

• 1.多个连接开启各自的事物操作数据库中数据时, 数据库系统要负责隔离操作, 以保证各个连接在获取数据时的准确性 2.如果不考虑隔离性,可能会引发并发事务问题: 脏读, 不可重复读, 幻读

• 脏读

  • 定义: 一个事务读取了另一个未提交事务修改的数据。

  • 场景:

    事务 A 修改了某行数据（未提交）。

    事务 B 读取了该行数据。 事务 A 回滚了修改，

    导致事务 B 读到了无效的“脏数据”。

  • 解决: READ COMMITTED 及以上

• 不可重复读

  • 定义: 同一事务内，多次读取同一行数据，结果不一致（因其他事务修改并提交了该行数据）

  • 场景:

    事务 A 第一次读取某行数据。

    事务 B 修改该行数据并提交。

    事务 A 再次读取该行数据，结果不同。

  • 解决: REPEATABLE READ 及以上

• 幻读

  • 定义: 同一事务内，多次查询同一范围的数据，结果集的行数不同（因其他事务插入或删除了符合该范围的数据并提交）

  • 场景:

    事务 A 第一次查询某个范围的数据。

    事务 B 插入或删除了符合该范围的数据并提交。

    事务 A 再次查询，发现结果集中多了或少了行（“幻影行”）。

  • 解决: SERIALIZABLE 隔离级别或者MySQL 的 REPEATABLE READ 结合间隙锁（Gap Locks）避免幻读。

• msyql的四种隔离级别

  • 读未提交

    • 存在问题: 脏读,不可重复读,幻读

      • 不加锁

  • 读已提交

    • 存在问题:不可重复读,幻读

      • 不加锁

  • 可重复读(默认)

    • 存在问题:幻读

      • 不加锁

  • 可串行化

    • 无

      • 加锁

• 事务隔离级别越高, 性能就越低, 一般采用默认的可重复读即可(综合安全和性能考虑的结果)

• 查看事务隔离级别

   select @@transaction_isolation
  

• 设置事务隔离级别

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  

mysql的锁

保证事务的隔离性: 排它锁(如一个事务获取了一个数据行的排他锁, 其他事务就不能再获取该行的其他锁)

1.SELECT … FOR UPDATE 是显式加锁机制，用于保护现有行的修改权

2.其他事务无法对被锁定的行执行修改（如 UPDATE、DELETE）或加其他排他锁。

3.必须配合事务使用

4.若查询未使用索引，可能升级为 表锁（InnoDB 会退化为锁全表)

BEGIN;
SELECT * FROM orders WHERE user_id = 100 FOR UPDATE; -- 锁定 user_id=100 的订单行
UPDATE orders SET status = 'paid' WHERE user_id = 100;
COMMIT;

间隙锁: 间隙锁 是 InnoDB 在 REPEATABLE READ 下自动加的锁，用于防止幻读

mvvc

Multi version concurrency control 多版本并发控制, 指的是维护一个数据的多个版本, 使得读写操作没有冲突

mvcc的具体实现, 主要依赖于数据库记录中的隐藏字段, undo log日志, readView

undo log

回滚日志, 在insert, update, delete的时候生产便于回滚的日志

当insert的时候, 产生的undo log日志只在回滚时需要, 在事务提交后, 可被立即删除

而update,delete的时候, 产生的undo log日志不仅在回滚时需要, mvvc搬空访问时也需要, 不会被立即删除

Undo log 版本链

不同的事务和相同事务对同一条记录进行修改, 会导致该记录的undo log生成一条版本记录链表, 链表头部是最新的旧记录, 链表尾部是最早的旧记录.

readview

readview(读视图) 是快照读SQL执行时MVCC提取数据的依据, 记录并维护系统当前活跃的事务(未提交的事务) id

当前读

读取的是记录的最新版本, 读取时还要保证其他并发事务不能修改当前记录, 会对读取的记录进行加锁. 对于我们日常的操作, 如: select … lock in share mode(共享锁), select … for update, update ,insert, delete(排它锁)都是一种当前读

快照读

简单select(不加锁)就是快照读, 快照读读取的是记录数据的可见版本, 有可能是历史数据, 不加锁, 是非阻塞读.

Read committed: 每次select, 都生成一个快照读

Repeatable read: 开启事务后第一个select语句才是快照读的地方

总结:

mvvc是mysql中多版本并发控制, 维护数据的多个版本, 使得读写操作没有冲突

隐藏字段:

trx_id(事务id), 记录每一次操作的事务id, 是自增的

Roll_pointer(回滚指针), 指向上一个版本的事务版本记录地址

Undo log:

回滚日志, 存储老版本数据

版本链: 多个事务并行操作某一行记录, 记录不同事务修改数据的版本, 通过roll_pointer指针形成一个链表

readView解决的是一个事务查询选择版本的问题

​ 根据readView的匹配规则和当前事务id判断该访问那个版本的数据

​ 不同的隔离级别快照读是不一样的, 最终的访问结果不一样

RC(读已提交): 每执行一次快照读时生成readView

RR(可重复读): 仅在事务中第一次执行快照时生成readView, 后续复用

事务ACID

• Atomicity原子性

  • 要么同时成功, 要么同时失败, dml操作是一个整体

• Isolation隔离性

  • 多个用户并发访问数据库时, 数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰, 多个并发事务之间要相互隔离

• durability持久性

  • 事务一旦提交, 对数据改变就是永久的

• Consistency一致性(前三种特性的结果)

  • 事务必须是数据从一个一致性状态转移到另一个一致性状态, 比如: 转账时, 钱的总数保持不变

Undo log和redo log

缓冲池(buffer pool): 主内存中一个区域, 在进行curd操作时, 先操作缓存中的数据, 以一定的频率刷新到磁盘,从而减少IO操作

数据页(page): 是innodb的存储引擎的最小单元, 每个页大小默认为16KB, 页中存储的是行数据

redo log日志

重做日志, 记录的是事务提交时数据页的物理修改, 是用来实现事务的持久性.

该日志文件由两部分组成: 重做日志缓冲(redo log buffer)和重做日志文件(redo log file)前者是在内存中, 后者是在磁盘中. 当事务提交后, 会把所有修改信息都保存到改日志文件中, 用于在刷新脏页到磁盘, 发生错误时, 进行数据恢复使用

undo log

回滚日志, 用于记录数据被修改前的信息, 作用包括两个: 提供回滚和MVCC(多版本并发控制)

Undo log和redo log记录物理之日不一样, 它是逻辑日志

可以认为当delete一条记录时, undo log 中会记录一条对应的insert记录, 反之亦然

当update一条记录时, 他会记录一条对应的相反的update记录, 当执行rollback时, 就可以从undo log中逻辑记录中读取相应内容, 并进行回滚

undo log可以实现事务的一致性和原子性

总结:

redo log: 记录的是数据页的物理变化, 服务器宕机后可以用来同步数据

Undo log: 记录的是逻辑日志, 当回滚事务时, 通过逆操作恢复原来的数据

Redo log保证了事务的持久性, undo log保证事务的原子性和一致性

mysql备份/恢复数据库

备份

1
2
#在终端执行
mysqldump -u username -p  数据库1 数据库2 数据库n > 文件名.sql

恢复

在mysql命令行内执行: source 备份的sql文件名.sql

mysql数据类型

• 数值类型(默认有符号,可以指定无符号的)

create table t4(
    id tinyint unsigned
);

• 整数类型

• bit(bit_len)位类型,指定位数,默认1,范围1-64, 只表示正数

• tinyint 1byte

• smallint 2byte

• mediumint 3byte

• int 4byte

• bigint 8个字节

• 浮点类型

  • float 单精度4byte

  • double 双精度4byte

  • decimal[M, D] 大小不确定, 由你指定

    • M（精度）：总位数，范围是 1 到 65。 D（标度）：小数位数，范围是 0 到 30，且必须小于或等于 M。 不指定M,D默认是 DECIMAL(10, 0)

字符串类型

• char 0-255字符

  固定大小,字符不够尾部补充空格,默认大小是char(1),范围是char(0-255)

  缺点: 空间浪费

• varchar 0-65536字节 utf8最大65536-3/-3字符(预留字节用来记录字符串长度) 我使用的utf8mb4,所以varchar最多保留(65536-4)/4=16383个字符 gbk 65535-3/2字符

  • varchar是变长的, 按照实际存储的字符串长度来分配空间, varchar需要额外1-3个字节来记录长度

• text 0-65535

• longtext 0-2^32-1

• 最佳实践

  如果数据定长,使用char md5编码, 手机号, 身份证 如果字段长度不确定,使用varchar 查询速度 char > varchar

  存放文本时,也可以使用text数据类型,可以将text视为varchar,注意text不能有默认值,大小为0-65536 如果希望存更多的字符, 可以使用mediumtext 0-2^24, 或者longtext 0-2^ 32

二进制类型

• blob 65535 longblog 2^32

日期类型

• data 年月日 对应java.sql.Date

• time 时分秒 java.sql.time

• datetime 年月日时分秒:YYYY-DD-MM HH:MM:SS 对应 java.sql.Timestamp

• timestamp(时间戳) 可用在在insert,update自动更新时间

  create table t14(
  birthday date,
  job_time datetime,
  login_time timestamp not null default current_timestamp on update current_timestamp
  );
  

• year

• datetime和时间戳(timestamp)的区别

  字节数不同, timestamp更短, 但是支持范围为2038年, 会存在千年虫问题, 所有尽量使用datetime, 他也支持自动更新为当前时间戳

满足需求的情况下, 尽量选择占用空间小的

mysql数据类型和java数据类映射

常见问题

• int, int(10)和int(11)的区别

  • 正常来说，没有区别。后面带的数字，并不是长度的概念。Int 类型就是占用4个字节（32位）， Int(1), int(10) 都一样是4个字节/32位，数据范围也还是 -2^31 ~ 2^31-1 为了做编号什么的，保持一致长度，不够位数就前面补0的这种情况，就可以带数字来指定“长度”了。但这种场景下，要配合 zerofill 一起使用才可以

mysql安装

参考官网

命令行连接

1
mysql -uroot -pPASSWORD

安装mysql community server, 默认服务端口是3306

安装连接客户端

• navicat

• sqlyog

• datagrip

存储引擎

mysql的表类型是由存储引擎决定的主要包括MyISAM,innoDB,Memory

MySQL数据库表主要支持六种类型分别是CSV,Memory,ARCHIVE,MRG,MYISAM,InnoDB

这几种只有InnoDB是事务安全性的

MyISAM

• 不支持事务,外键,但是访问速度快,对事务完整性没有要求

InnoDB

• 支持事务,外键,行级锁
• InnoDB存储引擎支持事务: commit,rollback,但是比起MyISAM,innodb的写的效率差一点, 并且会占用更多的内存,来保留数据和索引

Memory

• 在内存中数据库, 访问快, 使用hash索引, 但是mysql关闭, 数据就会丢失, 但表的结构还在

修改存储引擎

alter table tbl_name engine = egine_name

查看存储引擎

show engines 

如何选择存储引擎

• 如果不需要事务,只处理基本的crud,那么使用MyISAM引擎, 速度快,

• 需要事务,只能选InnoDB

• memory, 内存数据库,速度极快, 服务器重启后数据消失(经典用法, 用户在线状态), 对于数据量大频繁修改的数据可以放在缓存数据库中

视图

视图是一个虚拟表, 其内容由查询定义. 同真实表一样, 视图包含列, 其数据来自对应的真实表(基表)

通过视图可以修改基表的数据, 基表的改变,也会影响到视图的数据

可以在视图上再创建视图

视图更新的条件

• 如果一个视图是可更新的==>那么这个视图中每一条记录必须与底层表中每一条记录是一一对应的, 否则该视图不可更新

• 如果生成视图的语句包含了汇总函数(count,min,max,avg), distinct, group by having, union, union all,放在查询列表的子查询, 那么视图不可更新

视图的使用

• 创建视图

  create view view_name AS select语句
  

• 查看视图

  -- (可见视图相当一张表)
  show tables
  -- (创建视图时的sql)
  show create view emp_view
  

• 删除视图

  drop view view_name
  

mysql用户与权限

用户

• mysql所有用户都放在mysql库中user表中

  • user表重要字段 host :允许登陆的位置,localhost表示本机,也可以指定ip地址 user 用户名 host + user形成组合主键 authentication_string 密码

• 创建用户

  • 创建用户和密码

    create user '用户名'@‘登陆ip’ identified by '密码‘
    

• 删除用户

  drop user ‘用户名’ @ ‘允许登陆ip’;
  

• 修改用户密码

  • 修改自己密码 set password = password(‘密码’) 修改他人密码 set password for ‘用户名’@‘登陆ip’ = password(‘密码’) 或者 alter USER ‘用户名’@‘登陆ip’ identified by ‘新密码’

• 细节

  • 创建用户时,如果不指定host, 则为%,表示所有ip都有连接权限

  • 也可以创建用户只能在特定ip段登录

      create user ‘xxx'@’192.168.1.%‘ 表示在192.168.1.*ip段可以登陆mysql
  

  • 如果在删除用户时,host不是%,则需要明确指定 用户@host

权限

• 权限名称

  • all alter, 修改表,库 create, 创建表库, insert, delete,select,update, …一大堆

• 授权语法

  • 授权

      grant 权限列表 on 库.表名 to  '用户名'@‘登陆ip’ [identified by ‘密码’]
  

• 例子: grant select on … grant select,delete,create on .. grant all [privileges] on … // 表示该用户在该对象的所有权限 .表示系统中所有数据库对象,表,视图,存储过程 库.*表示该库内所有数据对象(表,视图,存储过程) identified可以省略, 也可以加上, 如果用户存在就是修改密码, 如果不存在就是创建用户

  • 撤销权限

    revoke  权限列表 on 库.对象 from 用户名'@‘登陆ip’
    

  • 刷新权限:flush privileges ;

允许远程登录

• 登录语法

  mysql -h 192.168.5.116 -P 3306 -u root -p123456 
  

  在mysql库的user表中中修改root的host为’%’==允许root远程登录,

  修改后刷新权限: flush privileges ;

存储程序(批处理)

存储例程

stored routine

存储函数stored function

• 变量

  • 用户自定变量(命令行中)语法

    @varname = 值
    

  • 在会话关闭后自动销毁或者置为NULL

  • 用户变量也可以在函数体中去调用修改,也可以在函数体中去创建用户变量,然后可以在函数体外访问定义变量

    set @a = 10;
    set @b = 10.5;
    

赋值

set @b = @a;

用into将查询结果赋值给变量

select count(*) from emp into @a;
select max(sal),min(sal) from emp into @a,@b;

查看变量值

select @a,@b;

函数体内的局部变量

declare var1,var2,...  数据类型 [default value]

变量未设值时,默认值为null 在存储函数体中,declare语句必须放在其他语句的前面

#声明变量
declare var1,var2 int default 0;
#定义函数
create function fun_name([参数列表])
returns 返回值类型
begin
     函数体内容
end
# 设置分割符为$$, 替代分号;默认分割符
delimiter $$

定义计算平均数学成绩的函数

create function avg_math_score()
returns float
begin
    return (select avg(math) from student );
end $$

重新改回分割符为;

delimiter ;

调用函数

select avg_math_score();

查看函数

# 查看函数定义了多少函数
show function status [like 需要匹配的函数名];

查看函数的定义

show create function avg_math_score;

删除函数

drop function avg_math_score;

选择语句

# if语句
create function condition_demo(i int)
    returns varchar(20)
begin
    declare result varchar(20);
    if i = 1 then
        set result = 'result is 1';
    elseif i = 2 then
        set result = 'result is 2';
    elseif i = 3 then
        set result = 'result is 3';
    else
        set result = 'invalid!';
    end if;
    return result;
end;

循环语句

#while循环
# 计算 1~n的累加和
create function sum_all(n int unsigned)
    returns int
begin
    # 声明sum变量,初始值0
    declare sum int default 0;
    # 声明累加变量i, 初始值1
    declare i int unsigned default 1;
    while i <= n
        do
            set sum = sum + i;
            set i = i + 1;
    end while;
    return sum;
end;

# repeat-unitl(do-while) 类似do-while

create function sum_all2(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    repeat
        set sum = i + sum;
        set i = i + 1;
    until i > n end repeat;
    return sum;
end;

loop(死循环型) 通过return结束循环

create function sum_all3(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    loop
        if i > n then
            return sum;
        end if;
        set sum = i + sum;
        set i = i + 1;
    end loop;
end;

通过leave 标签结束循环
create function sum_all4(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    flag:
    loop
        if i > n then
            leave flag;
        end if;
        set sum = i + sum;
        set i = i + 1;
    end loop;
    return sum;
end;

存储过程stored procedure

• 存储过程与存储函数的区别是, 存储过程没有返回值

• 定义存储过程

  create procedure tbl_emp_operation(
  eno mediumint unsigned,
  ena varchar(20)             )
  begin
  select * from emp;
  insert into emp(empno,ename,hiredate,sal) values (eno,ena,CURRENT_DATE(),0);
  select * from emp;
  end;
  

• 调用存储过程

  call tbl_emp_operation(10086,'motherfather');
  

• 查看存储过程

  show procedure status [like 需要匹配的存储过程名];
  

• 查看存储过程定义

  show create procedure tbl_emp_operation;
  

• 删除存储过程

drop procedure tbl_emp_operation;

存储过程参数前缀(局部变量的前缀) in(默认) 类似java中值传递, 方法内修改变量, 在外面变量不会改变 out out不可读, 但是可以写入数据,而且写入的值可以被调用者看到 inout 结合in和out,相当于指针, 在函数里面修改外面可以看到

• 存储函数与存储过程的区别

  • 1.存储函数中必须显式的使用returns语句表明返回的类型, 而且必须使用return语句来显示指定返回的值
• 2.存储函数不支持in, out,inout参数前缀, 存储过程支持
  • 3.存储函数在执行中产生的结果集并不会被显示到客户端, 存储过程执行过程中产生的结果集会被显示到客户端
• 1. 存储函数以函数调用的方式进行调用, 存储过程只能通过call语句来调用

触发器trigger

• 应用场景

  • 在向表插入/删除/修改前/后,让mysql服务自动额外的执行一些语句

• 创建触发器

  create trigger trigger_name
  {before|after}
  {insert|delete|update}
  on tbl_name
  for each row
  begin
   触发器内容
  end
  

  例子: 在插入学生表时,校验英语成绩

  create trigger trigger_for_stu
  before insert
  on student
  for each row
  begin
  if NEW.english < 60 then
    set NEW.english = 60;
  elseif NEW.english < 80 then
    set NEW.english = 80;
  else
    set NEW.english = 99;
  end if;
  end;
  

  • NEW/OLD

    mysql提供两个用来访问修改前后的内容变量 对于insert操作 NEW代表插入的内容, OLD无效 delete: OLD代表删除前的记录, NEW无效 update: NEW代表修改后的内容, OLD代表修改前的记录

• 查看触发器

  # 查看当前数据库定义的所有触发器
  show triggers ;
  

查看触发器定义

show create trigger trigger_for_stu;

• 删除触发器

  drop trigger trigger_for_stu;
  

• 注意事项

  • 触发器内容中不能有输出结果集的语句

  • 触发器内容NEW代表的记录可以修改,NEW影响insert和update的结果, OLD代表修改或删除之前的记录,无法被修改

  • 在before中可以使用set NEW.val = val来修值, 但是在after触发器中不能使用, 因为after时已经插入完成了

事件evnet(定时执行任务)

• 创建事件

  create event event_name on schedule
  {
   at  某个确定时间 |
   every 期望的时间间隔 [starts 开始日期和时间] [ end 结束日期和时间]
   }
  do
  begin
  insert into student(id, name, chinese, english, math) values (12,'caiqingsong',100,0,100);
  end;
  

  实例

  create event insert_student_event on schedule
  at '2025-02-16 15:20:00'
  do
  begin
  insert into student(id, name, chinese, english, math) values (12,'caiqingsong',100,0,100);
  end;
  

• 查看事件

  show events
  show create event event_name
  

• 删除事件

  drop event event_name
  

• 注意

  • 使用事件要把事件功能开启

      set global event_scheduler = on
  

  • 事件过期后会自动删除

游标

• 创建游标

  create procedure cursor_demo()
  begin
  declare temp_empno mediumint unsigned;
  declare temp_ename varchar(30);
  declare record_len int;
  declare i int default 1;
  

  创建游标

  declare emp_record_cursor cursor for select empno,ename from emp;
  

  打开游标 –> 关联结果集

  open emp_record_cursor;
  set record_len = (select count(*) from emp);
  while i <= record_len do
      # 通过游标遍历结果集
     fetch emp_record_cursor into  temp_empno, temp_ename;
     select temp_ename,temp_empno;
     set  i = i + 1;
  end while;
  

  关闭游标 –> 释放游标相关资源, 存储过程结束后也会自动关闭

  close emp_record_cursor;
  end;
  

• 打开游标

  declare emp_record_cursor cursor for select empno,ename from emp;
  open emp_record_cursor;
  

• 通过游标获取记录

  fetch emp_record_cursor into  temp_empno, temp_ename;
  

• 关闭游标

  close emp_record_cursor;
  

sql优化

定位慢查询

方式一: 使用arthas, trace 查看接口耗时

或者使用运维工具Prometheus, Skywalking查看

方式二: mysql自带的慢查询日志(测试环境可以使用, 生成环境使用会消耗性能)

# /etc/my.cnf
#开启慢查询
slow_query_log=1
#设置慢查日志时间为2s,SQL执行时间超过2s就会记录这个慢查询
long_query_time=2

配置完成后需要重启mysql服务

日志位置: /var/lib/mysql/localhost-slow.log

分析慢的原因

执行计划-explanin或desc获取mysql如何执行select语句

desc select * from emp where empno = 100002;

possible_keys: 当前sql可能用到的索引

key: 当前sql实际用到的索引

Key_len: 索引占用的大小

Extra: 额外的优化建议

​ 比如: using where; using index: 使用了索引,需要数据在索引列中都能找到, 不用回表查数据

​ using index condition: 查找使用了索引, 但是需要回表查数据

type 这条sql的连接类型, 性能由好到差为: NULL, system, const, eq_ref, ref, range, index, all

system: 查询mysql系统中自带的表

const: 根据主键查询

eq_ref: 主键查询或者唯一索引查询(查询结果只有一条数据)

ref: 索引查询,但是数据不止一条

range: 使用了索引, 范围查询

index: 索引树扫描 (效率不高)

all: 全盘扫描(效率不高)

如果执行效率是range一下, 就需要考虑优化了

如何分析:

通过key 和key_len来检查是否命中索引(检查索引是否失效)

通过type字段查看sql是否有进一步的优化空间, 是否存在全索引扫描或全盘扫描

通过extra建议判断,是否出现了回表情况, 如果出现了,可以尝试添加索引或者修改返回字段来修复

索引

索引的数据结构

索引是帮助mysql提高查找效率的数据结构, 如果不使用索引, 那就是线性查找, 效率比较低

mysql使用的B+树

为啥使用b+树

二叉树的缺点: 最坏的情况成为链表

红黑树: 解决了二叉树的缺点, 但是数据量大的话, 层级就特别深, 就降低了查找效率

B-Tree: 多叉路平衡查找树, 相对与二叉树,B树的节点可以多个分支: 矮胖型, 数据放在节点上

B+Tree(InnoDB默认使用): B-Tree优化, 只在叶子节点上存储数据, 叶子节点使用双向链表连接, 普通节点只存放指针, (mysql把链表进行收尾相连)

磁盘读写代价比B+树更低: 只在叶子节点查询数据, 减少不必要的数据查询

查询效率B+树更加稳定: 只在叶子节点查询数据

B+Tree便于扫库和区间查询

聚集索引和二级索引

聚集索引: 将数据存储与索引放到了一起, 索引结果的叶子节点保存了行数据, 必须有, 而且只有一个

二级索引: 将数据与索引分开存储, 索引结构的叶子节点关联的是对应的主键, 可以存在多个

聚集索引选权规则

如果有主键, 主键就是聚集索引

如果没有主键, 使用第一个唯一unique索引作为聚集索引

没有主键和唯一索引, 则innodb会自动生成一个rowid作为隐藏的聚集索引

回表操作: 先通过二级索引去找到主键值, 然后通过主键值找到目标结果行(聚簇索引)

我们单独创建的索引就是二级索引

覆盖索引

查询使用了索引, 并且返回的列, 在该索引中已经全部能够找到

# id是主键, name是普通索引
# 覆盖索引,因为主键是聚集索引
select * from tbl_user where id = 1 
# 覆盖索引 (name是二级索引, 数据有name(索引)和主键id)
select id,name from tbl_user where name = 'alice'
#非覆盖索引, 要在聚集索引中再次查找-->回表操作
select id,name gender from tbl_user where name = 'alice'

覆盖索引的效率更高

mysql超大分页优化

# 耗时2s
select * from emp limit 7000000,10; 
# 耗时5ms
select * from emp limit 0,10;

通过创建覆盖索引+子查询优化

先通过主键去查出limit对应的id, 再去查询id对应的数据

select e.*
from emp e,
     (select empno from emp order by empno limit 7000000,10) a
where e.empno = a.empno;

创建索引的原则

1. 数据量大, 查询频繁. 单表超过10w数据

2. 针对常作为查询条件(where), 排序(order by) 分组(group by) 操作的字段建立索引

3. 尽量选取区分度高度列(重复数据少) ,尽量建立唯一索引, 效率高
4. 如果是字符串类型字段, 字段的长度较长, 可以针对字段的特点, 建立前缀索引
5. 尽量使用联合索引,减少单列索引, 查询时,联合索引很多时候可以覆盖索引, 节省内存空间, 避免回表操作,提高查询效率
6. 控制索引的数量, 索引越多, 影响增删改效率
7. 如果索引不能存储null值, 在建表时,使用not null约束

索引失效的情况

联合索引:

1.违反最左前缀法则, 指的是查询从最左前列开始, 不能跳过索引中的列, 匹配最左前缀法则.

2.查询范围右边的列不能使用索引的

3.不要在索引列上进行运算, 否则索引将会失效

4.字符串不加单引号,造成索引失效: mysql优化器会自动进行类型转换,造成索引失效

5.以%开头的模糊查询, 索引失效. 如果仅仅是尾部模糊匹配, 索引不会失效, 如果是头部模糊匹配, 索引会失效

sql优化总结

表设计优化

设置合适的数值tinyint,int bigint

字符串: char定长效率高, varhcar长度可变,效率稍低

索引优化(前面已经有了)

sql语句优化

select语句指明字段名称, 好使用覆盖索引

sql语句要避免造成索引失效的写法

尽量使用union all代替 union (union 没有重复的, 但是多一次过滤,效率较低)

避免在where子句对字段进行表达式操作(可能会造成索引失效)

join优化, 能用inner join 就不用left join/right join, 如果必须使用一定要使用小表作为驱动(性能更好)

内连接会对两个表进行优化, 优先把小表放到外面, 把大表放到里面, left/right join不会重写调整顺序

主从复制, 读写分离

如果数据库的使用场景的读操作比较多时候, 使用读写分离架构: 写操作使用主库, 读操作使用slave

主从复制原理

二进制日志binlog

二进制日志记录了所有ddl和dml,但不包括查询语句

主库在提交事务时, 把数据记录在binlog中,

从库读取主库的二进制日志文件binlog, 写入到从库的中继日志relay log

从库从中继日志中读取数据, 然后写入到自己的数据库中

master会把数据同步到slave上

分库分表

1.如果业务数据逐渐增多, 可以使用分库分表, 单表数据达到1000w或者20G以后

2.此时优化解决不了性能问题

3.IO瓶颈,磁盘IO,网络IO

垂直拆分

垂直分库

以表为依据, 根据业务不同, 将不同的表拆分到不同库中

典型: 商品库, 订单库, 用户库等等(微服务架构就是这样的)

可以提高IO访问效率,从而提高并发

垂直分表

以字段分依据, 根据字段属性将不同的字段拆分到不同的表中,

拆分规则: 把不常用的字段单独放在一张表, 把text, blob等大字段拆分出来放在附表中

特点: 冷热数据分离, 减少IO过渡争抢, 两表互不影响

水平拆分

水平分库

将一个库的数据拆分到多个库中, 所有分库数据互斥,加来来才是所有数据

路由规则: 根据id节点取模, 按id范围录用, 节点1(1-100w),节点2(100w-200w)

水平分表

将一个表的数据拆分到多个表中(可以在同一个库中)

多个表之间数据是互斥的, 比如是按照userId%5进行5个订单表的分表, 总数据是5个表的数据加起来的数据

中间件: 使用mycat或者sharding-sphere

• Previous
  javaweb
• Next
  Hello 2024