MySQL（2）

最新推荐文章于 2024-10-10 15:31:49 发布
最新推荐文章于 2024-10-10 15:31:49 发布
阅读量1k 收藏 23
点赞数 33
分类专栏： 企业服务 文章标签： mysql oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dreamnn/article/details/135838606
版权

文章目录

一、SQL语句

  • SQL是结构化的查询语句
  • SQL的种类
    • DDL：数据定义语句
    • DCL：数据控制语言
    • DML：数据操作语言
    • DQL：数据查询语言

DDL数据定义语句

  • 对库或者表进行操作的语句
  • 创建数据库
create database db01;
# 创建数据库
create database DB01;
# 数据库名区分大小写（注意windows里面不区分）
show variables like 'lower_case_table_names';
show databases;
# 查看数据库（DQL）
show create database db01;
# 查看创建数据库语句
help create database;
# 查看创建数据库语句帮助
create database db02 charset utf8;
# 创建数据库的时候添加属性
  • 删除数据库
drop database db02;
# 删除数据库db02
  • 修改定义库
alter database db01 charset utf8;
show create database db01;
  • 创建表
help create table;
# 查看创表语句的帮助
create table student(
sid int,
sname varchar(20),
sage tinyint,
sgender enum('m','f'),
comtime datetime    
);
# 创建表，并且定义每一列
  • 数据类型（下面有完整的）
int整数-231~230
tinyint整数-128~127
varchar字符类型（可变长）
char字符类型（定长）
enum枚举类型
datetime时间类型 年月日时分秒
create table student(
sid int not null primary key auto_increment comment '学号',
sname varchar(20) not null comment '学生姓名',
sage tinyint unsigned not null comment '年龄',
sgender enum('m','f') not null default 'm' comment '性别',
comtime datetime not null comment '入学时间'   
)charset utf8 engine innodb;
# 带数据属性创建学生表
show create table student;
# 查看建表语句
show tables;
# 查看表
desc student；
# 查看表中列的定义信息
  • 数据属性
not null不允许是空
primary key主键（唯一且非空）
auto_increment自增，此列必须是primary key 或者unique key
unique key单独的唯一的
default默认值
unsigned非负数
comment注释
  • 删除表
drop table student;
  • 修改表的定义
alter table student rename stu;
# 修改表名
alter table stu add age int;
# 添加列和列数据类型的定义
alter table stu add test varchar(20),add qq int;
# 添加多个列
alter table stu add classid varchar(20) first;
# 指定位置进行添加列（表首）
alter table stu add phone int after age;
# 指定位置进行添加列（指定列）
alter table stu drop qq;
# 删除指定的列及定义
alter table stu modify sid varchar(20);
# 修改列及定义（列属性）
alter table stu change phone telphone char(20);
# 修改列及定义（列名及属性）

DCL数据控制语言

  • DCL是准对权限进行控制
  • 授权
grant all on *.* to root@'192.168.64.%' identified by '123456';
# 授予root@'192.168.64.%'用户所有权限（非超级管理员）
grant all on *.* to root@'192.168.64.%' identified by '123456' with grant option;
# 授权一个超级管理员
with
max_queries_per_hour;一个用户每小时可发出的查询数量
max_updates_per_hour;一个用户每小时可发出的更新数量
max_connections_per_hour;一个用户每小时可连接到服务器的次数
max_user_connections;允许同时连接数量
  • 收回权限
revoke select on *.* to root@'192.168.64.%';
# 收回select权限
show grants for root@'192.168.64.%';
# 查看权限

DML数据操作语言

  • 操作表中的数据
  • 插入数据
insert into stu values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);
# 基础用法，插入数据
insert into stu(classid,birth,sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);
# 规范用法，插入数据
insert into stu(classid,birth,sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456),('linux02',2,NOW(),'lisi',21,'f',NOW(),111,1234567);
# 插入多条数据
  • 更新数据
update student set sgender='f';
# 不规范
update student set sgender='f' where sid=1;
# 规范update修改
update student set sgender='f' where 1=1;
# 如果非要全表修改
update mysql.user set password=PASSWORD('123456') where user='root' and host='localhost';
# 修改密码，需要刷新权限flush privileges
  • 删除数据
delete from student;
# 不规范
delete from student where sid=3;
# 规范删除（危险）
truncate table student;
# DDL清空表中的内容
  • 使用伪删除
    • 有些时候一些重要数据不能直接删除，只能伪删除，因为以后还得使用呢
    • 使用update代替delete，将状态改成删除状态，在查询的时候就可以不显示被标记删除的数据
alter table student add status enum('1','0') default 1;
# 额外添加一个状态列
update student set status='0' where sid=1;
# 使用update
select * from student where status=1;
# 应用查询存在的数据

DQL数据查询语言

  • select：基础用法
  • 演示用的SQL文件下载：https://download.s21i.faiusr.com/23126342/0/0/ABUIABAAGAAgzcXwhQYozuPv2AE?f=world.sql&v=1622942413
mysql -uroot -p < world.sql
# 常用用法
desc city;
select countrycode,district from city;
# 常用用法
select countrycode from city;
# 查询单列
select countrycode,district from city limit 2;
select id,countrycode,district from city limit 2,2;
# 行级查询，从第2个开始，往后数2个
select name,population from city where countrycode='CHN' and district='heilongjiang';
# 多条件查询
select name,population,countrycode from city where countrycode like '%H%' limit 10;
# 模糊查询
select id,name,population,countrycode from city order by countrycode limit 10;
# 排序查询（顺序），按照国家代码进行排序
select id,name,population,countrycode from city order by countrycode desc limit 10;
# 排序查询（倒序）
select * from city where population>=1410000;
# 范围查询（>,<,>=,<=,<>）
select * from city where countrycode='CHN' or countrycode='USA';
# 范围查询or语句
select * from city where countrycode in ('CHN','USA');
# 范围查询in语句
select country.name,city.name,city.population,country.code from city,country where city.countrycode=country.code and city.population < 100;
# 多表查询

二、字符集定义

  • 什么是字符集（Charset）
  • 字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。
    a的ascall码值 - 互联网科技 - 亿速云
  • MySQL数据库的字符集
    • 字符集（CHARACTER）
    • 校对规则（COLLATION）
      mysql字符集（character sets）是指一系列符号以及符号对应的编码的集合，比如英文字母可以用ASCII编码，中文可运用GBK或者UTF8编码。校对规则（collations）则是指一种比较字符的规则，这种比较规则决定了mysql如何进行排序以及如何对字符比较大小。
  • MySQL中常见的字符集
    • UTF8
    • LATIN1
    • GBK
  • 常见校对规则
    • ci：大小写不敏感
    • cs或bin：大小写敏感
  • 我们可以使用以下命令查看
show charset;
show collation;

字符集设置

  • 操作系统级别
echo $LANG
  • Mysql实例级别
cmake . 
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \ 
-DWITH_EXTRA_CHARSETS=all \
# 在编译的时候指定
[mysqld]
character-set-server=utf8
# 在配置文件中指定
mysql> create database db01 charset utf8 default collate = utf8_general_ci;
# 建库的时候指定
mysql> CREATE TABLE `test`(
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMNET=13 DEFAULT CHARSET=UTF8;
# 建表的时候
mysql> alter database db01 CHARACTER SET utf8 collate utf8_general_ci;
mysql> alter table t1 CHARACTER SET utf8;
# 修改字符集

三、select高级用法

  • 多表连接查询（连表查询）
create table t1(id int primary key auto_increment,name varchar(20)) ENGINE=InnoDB CHARSET=utf8;

create table t2(id int primary key auto_increment,score int) ENGINE=InnoDB CHARSET=utf8;

insert into t1(name) values('cs'),('tj'),('lz');

insert into t2(score) values('30'),('80'),('82');
select * from t1;
select * from t2;
  • 传统连接（只能内连接，只能取交集）
select t1.name,t2.score from t1,t2 where t1.id=t2.id and t2.score > 60;
# 查出及格
# 世界上小于100人的人口城市是哪个国家的？
select city.name,city.countrycode,country.name from city,country where city.countrycode=country.code and city.population < 100;
# 世界上小于100人的人口城市是哪个国家，说的什么语言
国家人口数量			城市名		 国家名			语言
country.population, city.name, country.name, countrylanguage.Language
select country.population,city.name,country.name,countrylanguage.Language from city,country,countrylanguage where city.countrycode=country.code and countrylanguage.countrycode=country.code and city.population < 100;
  • NATURAL JOIN(自连接的表要有共同的列名字)
SELECT city.name,city.countrycode,countrylanguage.language, city.population FROM city natural join countrylanguage WHERE population > 1000000
ORDER BY population;
  • 企业中多表连接查询（内连接）
    类比交集
select city.name,city.countrycode,country.name 
from city join country on city.countrycode=country.code 
where city.population < 100;

建议：使用join语句时，小表在前，大表在后。

  • 外连接
    类比并集
select city.name,city.countrycode,country.name 
from city left join country 
on city.countrycode=country.code 
and city.population < 100;
  • UNION(合并查询)
mysql> select * from city where countrycode='CHN' or countrycode='USA';
# 范围查询or语句
mysql> select * from city where countrycode in ('CHN','USA');
# 范围查询in语句
mysql> select * from city where countrycode='CHN' 
union all 
select * from city where countrycode='USA' limit 10;
  • union：去重复合并
  • union all：不去重复
  • 使用情况：union < union all

四、MySQL数据类型

数据类型介绍

  • 四种主要类别
    • 数值
    • 字符
    • 二进制
    • 时间
  • 数据类型的ABC要素
    • Appropriate（适当）
    • Brief（简洁）
    • Complete（完整）
  • 数值数据类型
    • 使用数值数据类型时的注意事项
      • 数据类型所标识的值的范围
      • 列值所需的空间量
      • 列精度和范围（浮点数和定点数）
    • 数值数据类型的类

      • 整数：整数

      • 浮点数：小数

      • 定点数：精确值数值

      • BIT：位字段值

        类类型说明
        整数tinyint极小整数数据类型（0-255）
        整数smallint较小整数数据类型（-215到215-1）
        整数mediumint中型整数数据类型
        整数int常规（平均）大小的整数数据类型（-231到231-1）
        整数bigint较大整数数据类型
        浮点数float小型单精度（四个字节）浮点数
        浮点数double唱歌双精度（八个字节）浮点数
        定点数decimal包含整数部分、小数部分或同时包括二者的精确值数值
        BITBIT位字段值
  • 字符串数据类型
    • 表示给定字符集中的一个字母数字字符序列
    • 用于存储文本或二进制数据
    • 几乎在每种编程语言中都有实现
    • 支持字符集和整数
    • 属于以下其中一类
      类类型说明
      文本char固定长度字符串，最多为255个字符
      文本varchar可变长度字符串，最多为65535个字符
      文本tinychar可变长字符串，最多为255个字符
      文本text可变长字符串，最多为65535个字符
      文本mediumtext可变长字符串，最多为16777215个字符
      文本longtext可变长字符串，最多为4294967295个字符
      整数enum由一组固定的合法值组成的枚举
      整数set由一组固定的合法值组成的集合
    • 文本：真实的非结构化字符串数据类型
    • 整数：结构化字符串类型
  • 二进制字符串数据类型
    • 字节序列
      • 二进制按八位分组
      • 存储二进制值
      • 编译的计算机程序和应用程序
      • 图像和声音文件
    • 字符二进制数据类型的类
      • 二进制：固定长度和可变长度的二进制字符串
      • BLOB：二进制数据的可变长度非结构化集合
        类类型说明
        二进制binary类似于char（固定长度）类型，但存储的是二进制字节字符串，而不是非二进制字符串
        二进制varbinary类似于varchar（可变长度）类型
        BLOBtinyblob最大长度为255个字节的BLOB列
        BLOBblob最大长度为65535个字节的BLOB列
        BLOBMEDIUDMBLOB最大长度为16777215个字节的BLOB列
        BLOBlongblob最大长度为4294967295个字节的blob列
  • 时间数据类型

列属性介绍

  • 列属性的类别
    • 数值：适用于数值数据类型（BIT除外）
    • 字符串：适用于非二进制字符串数据类型
    • 常规：适用于所有数据类型
      数据类型属性说明
      数值unsigned禁止使用负值
      仅整数auto_increment生成包含连续唯一整数值的序列
      字符串character set指定要使用的字符集
      字符串collate指定字符集整理
      字符串binary指定二进制整理
      全部*Null或not null指定列是否可以包含NULL值
      全部Default如果未为新纪录指定值，则为其提供默认值

五、索引介绍

  • 索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
  • 让获取的数据更有目的性，从而提高数据库检索数据的性能。

索引类型介绍

  • BTREE：B+树索引
  • HASH：HASH索引
  • FULLTEXT：全文索引
  • RTREE：R树索引
    • B+树
      B+树原理以及Java代码实现_暗夜猎手-大魔王的博客-CSDN博客_java 中树的代码
  • B*树
    一文详解 B-树，B+树，B*树 - 知乎

索引管理

  • 索引建立在表的列上（字段）的。
  • 在where后面的列建立索引才会加快查询速度。
  • pages<-----索引（属性）<----查数据
  • 索引分类
    • 主键索引 primary key，不能为空
    • 普通索引***** index ，一般就添加普通索引
    • 唯一索引 unique key，可以为空
  • 添加索引
alter table test add index index_name(name);
# 创建索引
create index index_name on test(name);
# 创建索引
desc table;
# 查看索引
show index from table;
# 查看索引
alter table test drop key index_name;
# 删除索引
alter table student add unique key uni_xxx(xxx);
# 添加主键索引（略）
# 添加唯一性索引
select count(*) from city;
# 查看表中数据行数
select count(distinct(name)) from city;
# 查看去重数据行数

前缀索引和联合索引

对比较长的字段创建索引，比如邮箱xxxxx@gmail.com xxxxxx@qq.com，给整条数据创建索引是会浪费一定空间的

前缀索引

  • 根据字段的前N个字符建立索引
alter table test add index idx_name(name(N));
  • 避免对大列建索引
  • 如果有，就使用前缀索引

联合索引

  • 多个字段建立一个索引
  • 原则：把最常用来做为条件查询的列放在最前面
create table people (id int,name varchar(20),age tinyint,money int,gender enum('m','f'));
# 创建people表
alter table people add index idx_gam(gender,age,money);
# 创建联合索引，同时针对三个判断条件的

explain详解

用explain可以查看添加索引之后的执行效率如何

  • explain命令使用方法
mysql> explain select name,country from city where id=1;
  • MySQL查询数据的方式
    • 全表扫描（在explain语句结果中type为ALL）
      • 业务确实要获取所有数据
      • 不走索引导致的全表扫描
        • 没有索引
        • 索引创建有问题
        • 语句有问题
      • 生产中，mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描
    • 索引扫描
      • 常见的索引扫描类型
        • index
        • range
        • ref
        • eq_ref
        • const
        • system
        • null
      • 从上到下，性能从最差到最好，我们认为至少要达到range级别

index

  • Full Index Scan，index与ALL区别为index类型值遍历索引树

range

  • 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
mysql> alter table city add index idx_pop(population);
mysql> explain select * from city where population > 30000000;

ref

  • 用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
mysql> explain select * from city where countrycode='chn';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

eq_ref

  • 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique key作为关联条件A
join B
on A.sid=B.sid

const、system

  • 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
  • 如将主键置于where列表中，MySQL就能将该查询转换为一个常量
mysql> explain select * from city where id=1000;

NULL

  • MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
mysql> explain select * from city where id=1000000000000000000;

Extra（扩展）

  • Using temporary
  • Using filesort使用了默认的文件排序（如果使用了索引，会避免这类排序）
  • Using join buffer
  • 如果出现Using filesort请检查order by，group by，distinct，join条件列上没有索引
mysql> explain select * from city where countrycode='CHN' order by population;
  • 当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现
mysql> explain select * from city where population > 30000000 order by population;
mysql> explain select * from city where population=2870300 order by population;
* key_len:越小越好
* 前缀索引去控制，rows：越小越好

建立索引的原则（规范）

  • 为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
  • 选择唯一性索引
    • 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录

例如：

学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名的现象，从而降低查询速度。主键索引和唯一键索引，在查询中使用是效率最高的。

  • 为经常需要排序、分组和联合操作的字段建立索引
    • 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
    • 如果为其建立索引、可以有效地避免排序操作
  • 为常作为查询条件的字段建立索引
    • 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
    • 因此，为这样的字段建立索引，可以提高整个表的查询速度
    • 如果经常作为条件的列，重复值特别多，可以建立联合索引
  • 尽量使用前缀来索引
    • 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
  • 限制索引的数目
    • 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
    • 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得浪费时间。
  • 删除不再使用或者很少使用的索引
    • 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

重点关注：

  • 没有查询条件，或者查询条件没有建立索引
select * from table;
select * from table where 1=1;
# 全表扫描
  • 在业务数据库中，特别是数据量比较大的表，是没有全表扫描这种需求。
    • 对用户查看是非常痛苦的
    • 对服务器来讲是毁灭性的
    • SQL语句改写成以下语句
# 情况1
select * from table;
# 全表扫描
select * from tab order by price limit 10;
# 需要在price列上建立索引
# 情况2
select * from table where name='zhangsan';
# name列没有索引
1、换成有索引的列作为查询条件
2、将name列建立索引
  • 查询结构集是原表中的大部分数据，应该是25%以上
mysql> explain select * from city where population > 3000 order by population;
* 如果业务允许，可以使用limit控制
* 结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。
  • 索引本身失效，统计数据不真实
    • 索引有自我维护的能力
    • 对于表内容变化比较频繁的情况下，有可能会出现索引失效。
    • 重建索引就可以解决
  • 查询条件使用函数在索引列上或者对索引列进行运算，运算包括（+，-，*等）
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
  • 隐式转换导致索引失效，这一点应当引起重视，也是开发中经常会犯的错误
mysql> create table test (id int, name varchar(20),telnum varchar(20));
mysql> insert into tets values(1,'zs','110'),(2,'lisi',120),(3,'w5',130),(4,'z4','140');
mysql> explain select * from test where telnum=120;
mysql> alter table test add index idx_tel(telnum);
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum='120';
  • <>，not in不走索引
mysql> select * from tab where telnum <> '15555555';
mysql> explain select * from tab where telnum <> '15555555';
  • 单独的>，<，in有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
  • or或in尽量改成union
explain select * from teltab where telnum in ('110','120')
# 改写成
explain select * from teltab where telnum='110' union all select * from teltab where telnum='120';
  • like "%_"百分号在最前面不走索引
# 走range索引扫描
explain select * from teltab where telnum like '31%';
#不走索引
explain select * from teltab where telnum like '%110’;

%linux%类的搜索需求，可以使用Elasticsearch-------->ELK

  • 单独引用联合索引里非第一位置的索引列
create table t1 (id int,name varchar(20),age int,sex ENUM('m','f'),money int);
alter table t1 add index t1_idx(money,age,sex);
desc t1;
show index from t1;
#走索引的情况测试
explain select name,age,sex,money from t1 where money=30 and age=30 and sex='m';
# 部分走索引
explain select name,age,sex,money from t1 where money=30 and age=30;
explain select name,age,sex,money from t1 where money=30 and sex='m';
# 不走索引
explain select name,age,sex,money from t1 where age=20;
explain select name,age,sex,money from t1 where age=30 and sex='m';
explain select name,age,sex,money from t1 where sex='m';

六、MySQL的存储引擎

存储引擎简介

MySQL的存储引擎 - HelloWorld开发者社区

  • 文件系统：
    • 操作系统组织和存取数据的一种机制。
    • 文件系统是一种软件
  • 文件系统类型：ext2 3 4，xfs数据
    • 不管使用什么文件系统，数据内容不会变化
    • 不同的是，存储空间、大小、速度
  • MySQL引擎：
    • 可以理解为，MySQL的”文件系统“，只不过功能更加强大。
  • MySQL引擎功能：
    • 除了可以提供基本的存取功能，还有更多功能，事务功能，锁定，备份和恢复、优化以及特殊功能
    • 总之，存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。

MySQL自带的存储引擎类型

  • MySQL提供以下存储引擎：
    • InnoDB
    • MyISAM
    • MEMORY
    • ARCHIVE
    • FEDERATED
    • EXAMPLE
    • BLACKHOLE
    • MERGE
    • NDBCLUSTER
    • CSV
  • 还可以使用第三方存储引擎：
    • MySQL当中插件式的存储引擎类型
    • MySQL的两个分支
    • perconaDB
    • mariaDB
mysql> show engines;
# 查看当前MySQL支持的存储引擎类型
mysql> select table_schema, table_name,engine from information_Schema.tables where engine='innodb';
# 查看使用innodb的表有哪些
mysql> select table_schema, table_name,engine from information_Schema.tables where engine='myisam';
# 查看myisam的表有哪些
  • innodb和myisam的区别
存储文件不同，innodb只用两个文件，myisam要用三个文件
#进入mysql目录
[root@localhost~l]# cd /application/mysql/data/mysql
#查看所有user的文件
[root@localhost mysql]# ll user.*
-rw-rw---- 1 mysql mysql 10684 Mar 6 2017 user.frm
-rw-rw---- 1 mysql mysql 960 Aug 14 01:15 user.MYD
-rw-rw---- 1 mysql mysql 2048 Aug 14 01:15 user.MYI
#进入word目录
[root@localhost world]# cd /application/mysql/data/world/
#查看所有city的文件
[root@localhost world]# ll city.*
-rw-rw---- 1 mysql mysql 8710 Aug 14 16:23 city.frm
-rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd

innodb存储引擎的简介

  • 在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
  • 优点：
    • 事务安全（遵从ACID）
    • MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
    • InnoDB行级锁定
    • Oracle样式一致非锁定读取
    • 表数据进行整理来优化基于主键的查询
    • 支持外键引用完整性约束
    • 大型数据卷上的最大性能
    • 将对表的查询与不同存储引擎混合
    • 出现故障后快速自动恢复
    • 用于在内存中缓存数据和索引的缓冲区池
      功能支持功能支持
      存储限制64TB索引高速缓存是
      MVCC是数据高速缓存是
      B树索引是自适应散列列表是
      群集索引是复制是
      压缩数据是更新数据字典是
      加密数据是地理空间数据类型是
      查询高速缓存是地理空间索引是
      事务是全文搜索索引是
      锁定粒度行集群数据库是
      外键是备份和恢复是
      文件格式管理是快速索引创建是
      多个缓冲区池是performance_schema是
      更改缓冲是自动故障恢复是
  • innodb核心特性
    • MVCC
    • 事务
    • 行级锁
    • 热备份
    • Crash Safe Recovery（自动故障恢复）
  • 查看存储引擎
    • 使用SELECT确认会话存储引擎
select @@default_storage_engine;
# 查询默认存储引擎
  • 使用show确认每个表的存储引擎
show create table city\G
show table status like 'countrylanguage'\G
# 查看表的存储引擎
  • 使用information_schema确认每个表的存储引擎
select table_name, engine from information_schema.tables where table_name='city' and table_shcema = 'world'\G
# 查看表的存储引擎
  • 存储引擎的设置
    • 在启动配置文件中设置服务器存储引擎
[mysqld]
default-storage-engine=<Storage Engine>
# 在配置文件的[mysqld]标签下添加
  • 使用set命令为当前客户机会话设置
set @@default_storage_engine=<Storage Engine>;
# 在MySQL命令行中临时设置
  • 在CREATE table语句指定
create table t (i int) engine = <Storage Engine>;

【实战】存储引擎切换

  • 项目背景
    • 公司原有的架构：一个展示型的网站，LAMP，MySQL5.1.77版本（MYISAM），50M数据量
  • 小问题不断：
    • 表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
    • 不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失问题
  • 解决方案：
    • 提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
    • 如果使用MYISAM会产生”小问题“，性能安全不能得到保证，使用innodb可以解决这个问题。
    • 5.1.77版本对innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。
  • 实施过程和注意要素
    • 备份生产库数据（mysqldump）
#mysqldump -uroot -p123456 -A --triggers -R --master-data=2 >/tmp/full.sql
  • 准备一个5.6.38版本的新数据库
    • 对备份数据进行处理（将engine字段替换）
sed -i 's=#ENGINE=MYISAM#ENGINE=INNODB#g' /tmp/full.sql
  • 将修改后的备份恢复到新库
  • 应用测试环境连接新库，测试所有功能
  • 停应用，将备份之后的生产库发生的新变化，补偿到新库
  • 应用割接到新数据库

表空间介绍

在这里插入图片描述

  • 5.5版本以后出现共享表空间概念
  • 表空间的管理模式的出现是为了数据库的存储更容易扩展
  • 5.6版本中默认的是独立表空间
    • 共享表空间
  • 5.6版本中默认存储
    • 系统数据
    • undo
    • 临时表
    • 5.7版本中默认会将undo和临时表独立出来，5.6版本也可以独立，只不过需要在初始化的时候进行配置
  • 共享表空间扩展配置方法
vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdate1:50M;ibdata2:50M:autoextend(报错起不来)
  • 独立表空间
    • 对于用户自主创建的表，会采用此种模式，每个表由一个独立的表空间进行管理

【实战】数据库服务损坏

  • 在没有备份数据的情况下，突然断电导致表损坏，打不开数据库

拷贝库目录到新库中

cp -r /application/mysql/data/world /data/3307/data

启动新数据库

mysqld_safe --defaults-file=/data/3307/my.cnf

登录数据库查看

show databases;
use world
show tables; 查看不了，修改world这个目录的属主和属组

查询表中的数据

mysql> select * from city;
ERROR

找到以前的表结构在新库中创建表

mysql> create table `city_new`(
	`ID` int(11) NOT NULL AUTO_INCREMENT,
    `NAME` char(35) NOT NULL DEFAULT '',
    `CountryCode` char(3) NOT NULL DEFAULT '',
    `District` char(20) NOT NULL DEFAULT '',
    `Population` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`),
    KEY `CountryCode` (`CountryCode`),
    KEY `idx_city` (`Population`,`CountryCode`),
)ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;.

删除独立表空间文件

mysql> alter table city_new discard tablespace;

拷贝旧独立空间文件

cp /data/3307/data/world/city.ibd	/data/3307/data/world/city_new.ibd

授权

chown -R mysql.mysql city_new.ibd

导入表空间

mysql> alter table city_new import tablespace;

事务

  • 事务的定义

    • 主要针对DML语句（update、delete、insert）一组数据操作执行步骤，这些步骤被视为一个工作单元

      这些操作会对数据进行更改，如果使用了事务，出现错误可以进行回滚，使数据恢复正常

      • 用于对多个语句进行分组
      • 可以在多个客户机并发访问同一个表中的数据时使用

    • 所有步骤都成功或都失败

      • 如果所有步骤正常，则执行
      • 如果步骤出现错误或不完整，则取消

    • 交易的概念

      • 物与物的交换（古代）
      • 货币现金与实物的交换（现代1）
      • 虚拟货币与实物的交换（现代2）
      • 虚拟货币与虚拟实物交换（现代3）
  • 事务ACID特性
    • Atomic（原子性）
      • 所有语句作为一个单元全部成功执行或全部取消
    • Consistent（一致性）
      • 如果数据库在事务开始时处于一致状态，则在执行期间将保留一致状态。
        • 在事务内看到的数据状态都是一样的。
    • Isolated（隔离性）
      • 事务之间不相互影响。
    • Durable（持久性）
      • 事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
  • 事务的控制语句
START TRANSACTION（或 BEGIN）：显示开始一个新事务
SAVEPOINT：分配事务过程中的一个位置，以供将来引用或回滚
COMMIT：永久记录当前事务所做的更改
ROLLBACK：取消当前事务所做的更改
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT：删除 savepoint 标识符
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式
  • 一个成功事务的生命周期
begin:
sql1
sql2
sql3
...
commit;
  • 一个失败事务的生命周期
begin:
sql1
sql2
sql3
...
rollback;
  • 自动提交
mysql> show variables like 'autocommit';
# 查看自动提交
mysql> set autocommit=0;
# 临时关闭
vim /etc/my.cnf
[mysqld]
autocommit=0
# 永久关闭
  • 事务演示
    • 成功事务
mysql> create table stu(id int,name varchar(20),sex enum('f','m'),money int);
mysql> begin;
mysql> insert into stu(id,name,sex,money) values(1,'zhang3','m',130),(2,'li4','f',140);
mysql> commit;

*事务回滚，在提交之前回滚，
mysql> begin;
mysql> update stu set name='zhang3';
mysql> delete from stu;
mysql> rollback;
  • 事务隐式提交情况
    • 现在版本在开启事务时，不需要手工begin，只要你输入的是DML语句，就会自动开启事务。
    • 有些情况下事务会被隐式提交
      • 在事务运行期间，手工执行begin的时候会自动提交上个事务
      • 在事务运行期间，加入DDL、DCL操作会自动提交上个事务
      • 在事务运行期间，执行锁定语句（lock tables、unlock tables）
      • load data infile/source
      • select for update
      • 在autocommit=1的时候

事务日志redo

在mysql中，如果修改了数据，那么事务提交前，首先会被记录成redo日志写入磁盘，**等到事务提交时，再把新数据写入磁盘。**这也就是经常说的WAL（Write-Ahead Log）。

  • redo，顾名思义“重做日志”，是事务日志的一种
  • 在事务ACID过程中，实现的是“D”持久化的作用
  • 特性：WAL（Write-Ahead Log）日志优先写
  • REDO：记录的是，内存数据页的变化过程
  • 工作原理
    在这里插入图片描述
    在这里插入图片描述
update t1 set num=2 where num=1;
# 执行步骤
* 首先将t1表中num=1的行所在数据也加载到内存中buffer page
* MySQL实例在内存中将num=1的数据页改成num=2
* num=1变成num=2的变化过程会记录到，redo内存区域，也就是redo buffer page中
commit;
# 提交事务执行步骤
* 当敲下commit命令的瞬间，MySQL会将redo buffer page写入磁盘区域redo log
* 当写入成功之后，commit返回ok

事务日志undo

  • undo，顾名思义“回滚日志”，是事务日志的一种。
  • 在事务ACID过程中，实现的是“A”原子性的作用。当然C和I的特性也和undo有关
  • redo和undo的存储位置
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 50331648 Aug 15 06:34 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 6 2021 ib_logfile1
# redo位置
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata1
-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata2
# undo位置
这两个都是共享表空间，无论修改的是哪张表都会将日志记录在这两个表上
  • 在MySQL5.6版本中undo是在ibdata文件中，在MySQL5.7版本会独立出来。
    在这里插入图片描述

事务中的锁

  • “锁”顾名思义就是锁定的意思
  • 在事务ACID特性中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。
  • 排他锁：保证在多事务操作时，数据的一致性。一般是写锁
  • 共享锁：保证在多事务工作期间，数据查询时不会被阻塞。一般是读锁
  • 多版本并发控制（MVCC）
    • 只阻塞修改类操作，不阻塞查询类操作
    • 乐观锁的机制（谁先提交谁为准）
  • 锁的粒度
    • MyIsam：低并发锁（表级锁）
    • Innodb：高并发锁（行级锁）
  • 事务的隔离级别
  • 四种隔离级别
    • READ UNCOMMITTED（读未提交）
      • 允许事务查看其他事务所进行未提交更改
    • READ COMMITTED
      • 允许事务查看其他事务所进行的已提交更改
    • REPEATABLE READ
      • 确保每个事务的SELECT输出一致
      • Innodb的默认级别
    • SERIALIZABLE
      • 将一个事务的结果与其他事务完全隔离，可串行化
mysql> show variables like '%iso%';
# 查看隔离级别
[mysqld]
transaction_isolation=read-uncommit
# 修改隔离级别为RU
mysql> use test;
mysql> select * from stu;
mysql> begin;
mysql> insert into stu(id,name,sex,money) values(2,'lisi','f',234);
在新开的会话列表里能查询到stu表新增了一条数据，但是实际上该条数据还未提交
mysql> rollback；
回滚之后，在新开的会话中，查询不到该条数据，出现幻读的情况

[mysqld]
transaction_isolation=read-commit
# 修改隔离级别为RC
Dream visit
关注
专栏目录
docker（2）快速部署mysql
陌上花开的博客
08-16 3005
快速、极简，部署mysql
Nginx 代理MySQL
勿忘初心
04-28 8608
docker-compose.yml version: '2.0' services: nginx: restart: always image: nginx:1.13.9-alpine ports: - 63307:63306 volumes: - ./nginx.conf:/etc/nginx/nginx.conf nginx配置文件 user nginx; worker_processes 1; error_log /var/lo
windows卸载mysql
iswleii的博客
03-12 5270
windows下卸载mysql与这个四个位置有关系。你平时真的卸载对了吗？
MySQL 8.1.0安装
m0_75111244的博客
08-05 5533
点击链接点击链接。
MySQL 8.0.19安装教程(windows 64位)
热门推荐
日日言谧
08-15 98万+
话不多说直接开干 目录 1-先去官网下载点击的MySQL的下载​ 2-配置初始化的my.ini文件的文件 3-初始化MySQL 4-安装MySQL服务 + 启动MySQL 服务 5-连接MySQL + 修改密码 先去官网下载点击的MySQL的下载 下载完成后解压 解压完是这个样子 配置初始化的my.ini文件的文件 ...
Mac 上如何安装Mysql？ 如何配置 Mysql？以及如何开启并使用MySQL
hirezy
09-20 3万+
有许多开发的小伙伴，使用的是mac，那么在mac上如何安装，配置Mysql，以及使用Mysql了，今天来一个系统的教程。
MySQL的安装（详细教程）
xhmico的博客
06-09 14万+
本文的主要目的是在 Windwos 上安装 MySQL，以下内容是源于 B站 - MySQL数据库入门到精通 整理而来。如果想要在 Linux 下安装 MySQL ，详见 Linux-安装MySQL（详细教程）我不生产知识，我只是知识的搬运工~~MySQL是一种关系型数据库管理系统，所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站
安装mysql步骤
Darker的博客
03-28 4532
一、安装mysql的repo源 # 查看系统是否安装mysql软件 rpm -qa|grep -i mysql # 卸载历史软件 yum remove '软件名' # centos7中yum源默认没有mysql，下载安装mysql的repo源 wget http://repo.mysql.com//mysql57-community-release-el7-7.noarch.rpm # 安装mysql的repo源 rpm -ivh mysql57-community-release-el7-7.noarch
Mysql之字符串拼接
caijibaindashen的博客
05-04 1万+
mysql字符串拼接 两种方式，第一种，使用 “+” 进行拼接（错误的方法）， 第二种使用Mysql函数CONCAT()等函数。 使用 “+” 使用“+”进行对数据是加减。不能进行拼接 拼接用法： 数据表： 错误写法： -- 不能用于多列进行拼接 SELECT last_name + ' ' + first_name AS Name FROM employees; -- 不能用于单列 SELECT last_name + '___P' AS Name FROM employees; "+"的使
Linux下载Mysql
越努力越幸运的博客
01-21 1万+
Linux下载Mysql5.x或者8.0详细教程 注意：如果已经有Mysql但是想要换版本号，切记不要直接下载，要先把旧版本的Mysql删除干净再重新下载。（本文并未提供删除的步骤，请自行百度） 废话少说，我们直接给出详细步骤： 这里，我们将mysql下载到/usr/local/目录下。 cd /usr/local mkdir mysql cd mysql wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm 然
Flink MySQL CDC 使用总结
主要分享大数据相关的知识，如Spark、Hudi
04-03 3612
学习总结Flink MySQL CDC，主要目的是同步MySQL数据至其他数据源如Hudi、MySQL等，本文主要以 MySQL2Hudi、MySQL2MySQL两个场景进行示例验证。
Datax从mysql同步数据到mysql
一杯咖啡半杯糖的博客
11-09 4924
在实际使用Datax的时候，可能会使用Datax同步mysq数据到mysql，实现数据库跨库同步或者多个mysql之间相互同步，那么怎么实现呢？我们一步步来实现（基于Datax 3.0.0）
db2和mysql的区别是什么
WziH_CSDN的博客
12-15 4657
IBM DB2 是美国IBM公司开发的一套关系型数据库管理系统，它主要的运行环境为UNIX（包括IBM自家的AIX）、Linux、IBM i（旧称OS/400）、z/OS，以及Windows服务器版本。DB2主要应用于大型应用系统，具有较好的可伸缩性，可支持从大型机到单用户环境，应用于所有常见的服务器操作系统平台下。 MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的 SQL 语言是用于访问数据库的.
NodeJS mysql2 使用心得
u010379324的博客
04-23 7714
NodeJS mysql2 使用一、mysql2的安装之前有用过 mysql 的模块，但是感觉并不太好用。至少语义替换这块并没有很好的解决办法， 看到mysql2有这个功能，果断就替换。使用下列命令就可以很轻松安装。npm install mysql2 --save二、mysql2的使用首先自己建一个新文件名字为 mysql_pool.js 。然后，废话不多说，直接上代码。var mysql
MySQL安装配置教程（超级详细、保姆级）
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
MySql - 数据库操作
Golemon的博客
10-08 318
【代码】MySql - 数据库操作。
【MySQL】MySQL表的操作
2301_80224556的博客
10-07 1312
这个语句创建了一个 users 表，其中 id 是自动递增的主键，username 和 email 是非空字段，并且每个字段都有相应的注释。综上所述，这些文件的存在和它们的内容反映了MySQL数据库db1中使用MyISAM存储引擎的user表的数据和索引存储情况。假设你有一个名为 employees 的表，其中有一个名为 salary 的列，数据类型为 INT。：这表示字段的默认值。这些文件的存在和它们的内容与MySQL的存储引擎有关，特别是与MyISAM存储引擎相关。这是你要创建的表的名称。
【MySQL】入门篇—数据库基础：数据库的定义与用途
最新发布
thinking_chou的专栏
10-10 1009
数据库（Database）是一个有组织的数据集合，通常以电子形式存储在计算机系统中。数据库管理系统（DBMS）是用于创建、管理和操作数据库的软件。数据库可以支持多种数据类型，如文本、数字、图像等，并允许用户通过查询语言（如SQL）对数据进行操作。二、数据库的用途数据库在社会中扮演着至关重要的角色，几乎每个行业都依赖于数据库来管理和分析数据。通过高效的数据存储和管理，企业和组织能够更好地理解客户需求、优化运营流程、提高决策能力，并在竞争中保持优势。
MySQL之复合查询与内外连接
zdlynj的博客
09-30 1453
前面我们讲解的mysql表的查询都是对一张表进行查询，即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中，我们往往还需要对多个表同时进行查询。我们这里使用的测试表，为雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。
db2迁移至MySQL：关键步骤与函数调整总结
本文档主要介绍了如何将DB2数据库迁移到MySQL数据库的过程，以及在迁移过程中遇到的一些关键问题的处理方法。首先，提到的"withur处理"可能是指处理DB2中的游标（with ur）操作，由于MySQL不支持类似的特性，可能...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值