MySQL数据库

最新推荐文章于 2024-04-22 23:31:26 发布

呜呜你好特别

最新推荐文章于 2024-04-22 23:31:26 发布

阅读量1k

点赞数

文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/weixin_46216804/article/details/127307740

版权

一、数据库引擎

1、Archive引擎

存储限制none，啥权限也没有

2、InnoDB引擎

存储限制64TB，支持事物，支持数索引，支持数据缓存，支持外键

3、Memory引擎

存储限制RAM，支持数索引，支持数据缓存，支持外键

4、MyISAM引擎

存储限制256TB，支持全文索引，支持数索引

二、`sql`调优

使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，

在group by 后面增加 order by null 就可以防止排序.
最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用 NULL。
首先应考虑在 where 及 order by 涉及的列上建立索引
减少 IO 次数
少排序、分组、查重：降低 CPU 计算（order by, group by,distinct，排序）
尽量用连接（join）代替子查询（不需要在内存创建临时表），尽量少 join
不要 select *，要写具体
尽量少 or,使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好
避免类型转换
优先优化高并发的 SQL
从全局出发优化，充分考虑系统中所有的 SQL

三、事务四大特性

1、原子性A

是指一个事务的所有操作，要么全部成功，要么全部失败

2、一致性C

执行前和执行后必须一致。不能一个加了钱，一个没加钱

3、隔离性I

每个用户访问数据库不会互相干扰

4、持久性D

事务提交后永久性保存

四、事务隔离级别

1、为什么有隔离级别

（1）脏读

一个事务读取了另一个还未提价的事务中的内容

（2）不可重复读update/delete

查询间隔，查询内容被另一个事务修改并提交了。导致多次查询返回了不同的值

（3）幻读（虚读）insert

期间事务B修改一行数据111变为2222，此时事务B插入新数据为111，事务A再读取发现了事务B 新插入的数据，会发现还有一行没有修改，就好像产生幻觉一样，这就是发生了幻读。

2、事务的隔离级别

（1）读未提交Read uncommitted

解决更新丢失问题，却可能出现脏读

一个事务写数据的时候，其他事务全部不允许写数据，但是允许读。

这种级别可以通过"排它写锁"和不排斥读线程实现。

（2）读已提交Read committed

避免了脏读和更新丢失，但是可能出现不可重复读

读事务的时候，允许其他事务读写；

写事务的时候，禁止其他事务访问该行数据

（3）可重复读取Repeatable read

MySQL默认的隔离级别是可重复读。

（保证多次查询结果相同）

事务A读取这个数据（不可写），在在这个事务还没结束时，其他事务不能访问该数据(包括了读写)

(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。

可重复读会产生间隙锁：

使用普通索引锁定；
使用组合索引；
使用唯一索引锁定多行记录。

解决了更新丢失、脏读、不可重复读、但是还会出现幻读

（4）可序化Serializable

事务只能一个接着一个地执行（不可并发）

仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。

它以锁表的方式(类似于Java多线程中的锁)使得其他线程只能在锁外等待

序列化是最高的事务隔离级别，性能很低，一般很少使用

不仅可以避免脏读、不可重复读，还避免了幻读

五、缓存

1、缓存处理流程

取数据的时候先在缓存中找，没有的话再到数据库中查询，返回结果

2、缓存穿透

缓存和数据库都没有数据，但是用户一直请求。这时候用户很可能是攻击者

解决方案

接口层增加效验，用于鉴定，id<0直接拦截
如果缓存和数据库中都无数据，这时候可以把key-value写为key-null，缓存有效时间设置短一点：30秒，太长的话用户也用不好。可以防止攻击用户反复用同一个id暴力攻击

3、缓存击穿

缓存中没有数据，刚刚过期了，数据库中有，这时：并发用户特别多，同时读取缓存没读到数据又跑到数据库取数据，导致数据库压力瞬间扩大。

解决方法

设置热点数据永不过期

加互斥锁

说明

缓存中有数据，直接走上述代码13行后就返回结果了
缓存中没有数据，第1个进入的线程，获取锁并从数据库去取数据，没释放锁之前，其他并行进入的线程会等待100ms，再重新去缓存取数据（自旋）。防止都去数据库重复取数据，重复往缓存中更新数据情况出现。
当然这是简化处理，理论上如果能根据key值加锁就更好了，就是线程A从数据库取key1的数据并不妨碍线程B取key2的数据，上面代码明显做不到这点。

4、缓存雪崩

缓存中大量数据都过期了，这时候查询量巨大甚至死机

缓存击穿指的是并发查询一条，但是缓存雪崩是很多条数据都过期导致很多条都要查数据库

解决方案

缓存数据过期时间设置随机，防止同一时间大量数据过期
如果缓存数据库是分布式部署，那就将热点数据均匀分布在个个缓存数据库中
设置那些热点数据永不过期

六、索引

2、MySql中索引类型

聚簇索引

聚簇索引就是主键索引
聚簇索引的叶子节点存储的是整行数据

非聚簇索引(辅助索引)

非聚簇索引就是普通索引
非聚簇索引的叶子节点存储的是索引值和主键值
一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引

1、`innoDB`的索引类型

（1）主键索引

一个表只能一个主键，一个主键索引
默认的都有，primary设置后，自动创建主键索引
InnoDB通过主键来实现聚簇索引，如果没有主键的话，它会选择一个唯一非空的索引来实现，如果再没有的话，它会隐式生成一个主键实现聚簇索引
alert table user add **primary key**(id)

（2）普通索引

也叫 单列索引，单值索引
随便一个字段都可以建立索引，可以重复
alert table user add index idx_name(name)

（3）唯一索引

一个表中可以有多个唯一索引。
值必须唯一，可以为空，但是只能有一个空
alert table user add unique idx_id_card(id_card)

（4）复合索引

多个列组合索引，
最左原则添加了age,name时，可以用age或者age和name，但是不能单独用name
alert table user add index idx_age_name(age,name)

2、Full Text全文索引

因为全文索引会对文本进行分词，所以只能用于char，varchar，text

允许在索引列中插入重复值和空值

MyIsam(权限最低的那个)存储引擎支持全文索引

3、什么情况无法用索引

1.查询语句中使用LIKE关键字

如果第一个字符为%，索引不会被使用。
只要%不在第一个位置，索引就会被使用。

2.查询语句中使用多列索引

如果查询使用了组合索引，那必须要使用了左侧第一个字段，才会调用索引

3.查询语句中使用or关键字

如果or的时候其中有字段不是索引，就不能使用索引

username = '123' or password='121'

4、什么时候要用索引

在需要排序的列上创建索引
在经常使用在where子句中的列上面创建索引
在经常需要搜索的列上加索引，可以很快搜索到
在外键上面建立索引

5、什么时候不用索引

使用不了索引的时候（模糊查询%在左边，组合查询的时候不遵循最左原则，or的时候有一侧不是索引）
查询少的列不用索引
数据值范围很少的列也不用索引，比如说性别
imge、url这种的别加索引
如果哪一项需要经常修改的话，也最好不要加，不然索引的自动维护过程会很慢

七、MySQL基础

1、创建数据库

create database  ry  default character set utf8 COLLATE utf8_general_ci;

2、创建表

create table sys_dept (
  dept_id           bigint(20)      not null auto_increment    comment '部门id',
  parent_id         bigint(20)      default 0                  comment '父部门id',
  ancestors         varchar(50)     default ''                 comment '祖级列表',
  dept_name         varchar(30)     default ''                 comment '部门名称',
  order_num         int(4)          default 0                  comment '显示顺序',
  leader            varchar(20)     default null               comment '负责人',
  phone             varchar(11)     default null               comment '联系电话',
  email             varchar(50)     default null               comment '邮箱',
  status            char(1)         default '0'                comment '部门状态（0正常 1停用）',
  del_flag          char(1)         default '0'                comment '删除标志（0代表存在 2代表删除）',
  create_by         varchar(64)     default ''                 comment '创建者',
  create_time      datetime                                   comment '创建时间',
  update_by         varchar(64)     default ''                 comment '更新者',
  update_time       datetime                                   comment '更新时间',
  primary key (dept_id)
) engine=innodb auto_increment=200 comment = '部门表';

3、增加

insert into stu(id,name) values('1','sss')

4、修改

update stu set nicname='jjj' where id='1'

5、删除

delete from stu where id =1

八、安装

1、Windows下安装

（1）安装包下载。

下载地址：https://dev.mysql.com/downloads/mysql/

点击下载之后，可以选择注册Oracle账号，也可以跳过直接下载。

下载完成后，选择一个磁盘内放置并解压。

2020年2月14日，mysql官网进不去了，好吧_{那就来个镜像，总没问题了吧。如果官网龟速下载，建议使用下面镜像巨快。相对的}

Mysql国内镜像：http://mirrors.sohu.com/mysql/MySQL-8.0/

下载完成之后，解压即可！

（2）安装教程

（1）配置环境变量

变量名：MYSQL_HOME

变量值：E:\mysql-5.7.20-winx64

（2）生成data文件

以管理员身份运行cmd

进入E:\python\mysql\mysql-8.0.12-winx64\bin>下(这里在说一下：这里的路径需要根据自己的实际安装位置进行调整，总之就是刚刚解压的文件放在那，就在哪打开~)

执行命令：mysqld --initialize-insecure --user=mysql 在E:\python\mysql\mysql-8.0.12-winx64目录下生成data目录

（3）安装MySQL

继续执行命令：mysqld -install

（4）启动服务

继续执行命令：net start MySQL

（5）登录MySQL

登录mysql:(因为之前没设置密码，所以密码为空，不用输入密码，直接回车即可）

E:\python\mysql\mysql-8.0.12-winx64\bin>mysql -u root -p

（6）查询用户密码

查询用户密码命令：mysql> select host,user,authentication_string from mysql.user;

（7）设置（或修改）root用户密码

alter user user() identified by "XXXXXX";

2、Linux下安装

九、批量

1、批量插入

insert into t_place  (select * from t_place_ex_h2)

2、批量修改

update t_place set city = concat(city,county) where county !='213213';

十、方法

1、concat字符串合并

concat(city,county)
select concat(city,county,town) from t_place

2、split_part字符串分割

split_part([原字符串], [分割字符], [取第几坨])

注意：分割之后的字符串是不包括分割字符的

select split_part(tp.name,'镇',3) from t_place

3、substring字符串截取

substring([原字符串], [从第n个开始], [数m-1个数]) n从0开始

比如

--place_code=123456
substring(place_code, 0, 3)    --数两位： 12

4、cast when then end

case：语句开始符
when：当什么什么的时候
then：否则
end：语句结束符

select  (case
             when he.ttt like '%镇%'  //if
             	then (concat(split_part(he.ttt,'镇',1),'镇')) //为真
             when tp.name like '%镇%'  //else if
                then (split_part(tp.name,'镇',2))//为真
             else (concat(split_part(he.ttt,'乡',1),'乡')) //为假
             end)   as name
from he

4、case when判断

select (case when stu.score > 90
				then '优秀'
			when stu.score > 80
				then '良好'
			else '不合格'
            end) as '等级'
from stu;

十一、mysql常见日志

1、重做日志（redo log）

事务的持久性性是通过 redo log 来实现的物理日志

在重启mysql服务的时候，根据redo log进行重新读取，从而达到事务的持久性这一特性。

2、回滚日志（undo log）

事务的原子性是通过 undo log 来实现的逻辑日志根据sql生成逆向回滚sql

保存了事务发生之前的数据的一个版本，可以用于回滚

3、二进制日志（binlog）

用于复制（主从复制）

4、其他日志

错误日志（errorlog）

慢查询日志（slow query log）

一般查询日志（general log）

中继日志（relay log）

5、三种日志总结

首先 InnoDB 完成一次更新操作的具体步骤：

开启事务
查询待更新的记录到内存，并加 X写锁
记录 undo log 到内存 buffer
记录 redo log 到内存 buffer
更改内存中的数据记录
提交事务，触发 redo log 刷盘
记录 bin log
事务结束

十二、postgresql

1、json_array_elements函数

解析json数组转成多行

#这个JSON数据是 ds_code = 'tsoc_alarm_type'的ds_rsp_content字段内容
[
    {
        "id": "a5cc1a52-b591-4d5b-8670-66bb2abfb4de",
        "ruleTypeName": "未分类情报命中",
        "parentRuleTypeId": "2dc02001-b3de-4e53-a1b2-78bdb879af4c",
        "parentRuleTypeName": "威胁情报命中"
    },
    ...
]

select
        json_array_elements(stu.ds_rsp_content::json) ->> 'id'  as id,
        json_array_elements(stu.ds_rsp_content::json) ->> 'ruleTypeName'  as name,
        json_array_elements(stu.ds_rsp_content::json) ->> 'parentRuleTypeId'  as parent,
        json_array_elements(stu.ds_rsp_content::json) ->> 'parentRuleTypeName'  as parentname
from stu   where ds_code = 'tsoc_alarm_type'

查询结果：

2、to_number函数

把字符串转为数字，并指定保留几位小数

格式值	含义
9	代表一个数字
0	强迫0显示
$	显示美元符号
L	强制显示一个当地的货币符号
.	显示一个小数点
,	显示一个千位分隔符号

select to_number('1655.33','999.0');  //报错，有小数位的时候 整数位必须相等

select to_number('16955.33','999');  //169

select to_number('16955','9999');   //1695   保留4位整数

select to_number('16955','999');  //169		保留3位整数

select to_number('qwe123.333', '99999') ;  //12

select to_number('qwe123.333', '999') ;  //报错 保留的3位整数中无数字

select to_number('qw3123.333', '999') ;  //3

3、to_timestamp函数

4、interval函数与current_timestamp变量

current_timestamp: 返回当前UTC（GMT+0）时间戳，时间戳单位为毫秒。(中国UTC（GMT+8）)

返回中国当前时间: select current_timestamp + interval '8' hour;

//获取当前时间的8小时之前 
select current_timestamp;   //2022-06-24 07:40:14.893949 +00:00

select current_timestamp - interval '1' year; //当前时间的一年零8小时之前

select current_timestamp - interval '2' month;
select current_timestamp - interval '3' day;
select current_timestamp - interval '4' hour;
select current_timestamp - interval '5' minute;
select current_timestamp - interval '600' second;