分类 MYSQL 下的文章

---------创建数据库
CREATE DATABASE zhcz_oa DEFAULT CHARACTER SET utf8mb4;
---------查询存在的数据库
show databases;
---------选择数据库
use zhcz_oa;
---------删除数据库
drop database zhcz_oa;
---------创建表
create table emp(
id bigint(20) not null auto_increment comment 'id',
created_by varchar(36) not null comment '创建者',
create_date datetime not null comment '创建时间',
primary key (id) using btree
) engine=innodb auto_increment=140 default charset=utf8 row_format=dynamic comment='用户群组表';
---------查看表定义
desc emp;
---------查看建表语句
show create table emp G
---------删除表
drop table emp;
---------修改表类型
alter table emp modify created_by varchar(20);
---------增加表字段
alter table emp add column age int(3);
---------删除表字段
alter table emp drop column age;
---------修改字段名
alter table emp change age agele int(4);
---------修改表名
alter table emp rename empel;
---------插入数据
insert into emp (id,created_by,create_date,age) values(1,'zzy','2020-08-06','1000');
insert into emp values (2,'zzy','2020-08-06','10001');
insert into emp (created_by,create_date,age) values('zzy','2020-08-06',1002);
---------更新数据
update emp set id=4 where id=140;
update emp set create_date='2020-08-06',age='1003' where id=3;
---------删除数据
delete from emp where id=2,id=3;
---------查询数据
select * from emp;
---------查询不重复数据
select distinct age from emp;
---------条件查询
select * from emp where create_date='2020-08-06';
---------排序查询
select * from emp order by age;
---------限制查询
select * from emp order by age limit 1,3;
---------聚合查询
select age,count(1) from emp group by age having count(1) >1;
---------表连接查询
select created_by,deptname from emp,dept where emp.depton=dept.depton;
select created_by,deptname from emp left join dept on emp.depton=dept.depton;
select created_by,deptname from emp right join dept on emp.depton=dept.depton;
---------子查询
select * from emp where depton in(select depton from dept);
---------联合查询
select depton from emp union all select depton from dept;
select depton from emp union select depton from dept;
---------授权
grant select,insert on zzy.* to 'zzy'@'localhost' identified by '123';
---------收回授权
revoke insert on zzy.* from 'zzy'@'localhost';
---------获取帮助
? contents
? show
---------查询字符长度
select length(v),length(c) from vc;
---------字段追加字符
select concat(v, '+'), concat(c, '+') from vc;
---------查询插入的json数据类型
select json_type('"abc"') js1,json_type('[1,2,"abc"]') js2,json_type('{"k1":"value"}') js3;
---------判断json数据是否合法
select json_valid('null') n1,json_valid('NULL') n2,json_valid('false') f1,json_valid('FALSE') f2;
---------显示当前日期,年、月、日
select curdate();
---------显示当前时间,时、分、秒
select curtime();
---------显示当前日期和时间,包含年、月、日、时、分、秒
select now();
---------计算两个日期之间相差的天数
select datediff('2054-11-08',now());
---------查询当前登录数据库名
select DATABASE();
---------查询当前数据库版本
select VERSION();
---------查询当前登录用户名
select USER();
---------查询默认存储引擎
show variables like 'default_storage_engine';
---------查询表的存储引擎
select table_name,engine from information_schema.tables where table_name='dept';
---------修改表的存储引擎
alter table dept engine='myisam';
---------查询外键约束状态
show variables like 'foreign_key_checks';
---------暂时关闭外键约束
set foreign_key_checks=0;
---------查看数据库所有字符集
show character set;
select * from information_schema.character_sets;
---------查看字符集排序规则
show collation like 'utf8%';
select * from information_schema.collations;
---------查看服务器默认字符集
show variables like 'character_set_server';
---------查看服务器默认排序规则
show variables like 'collation_server';
---------查看数据库默认字符集
show variables like 'character_set_database';
---------查看数据库默认排序规则
show variables like 'collation_database';
---------修改字符集
mysqldump -uroot -p --default_character-set=utf8 -d databasename > createtab.sql ---导出表结构
手工修改createtab.sql表结构定义中的字符集为新字符集
mysldumo -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename > data.sql ---导出表数据
手工修改data.sql表SET NAMES latin1为SET NAMES UTF8
create database databasename default charset utf8 ---创建新数据库
mysql -uroot -p databasename < createtab.sql < createtab.sql ---导入表结构
mysql -uroot -p databasename < data.sql ---导入表数据
----------查询表的索引
show index from dept;
----------创建普通索引
create index index_dept on dept (deptname);
alter table dept add index idx_dept(deptname);
----------删除索引
drop index index_dept on dept;
----------添加不可见索引
create index invisible_idx on emp(age) invisible;
alter table emp add index invisible_idx(age) invisible;
----------修改索引是否可见
alter table emp index invisible_idx invisible;
alter table emp index invisible_idx visible;
----------查看索引使用情况
show status like 'handler_read%';
----------创建视图
create or replace view depton_list_view as select d.depton,d.deptname,e.id,e.create_date,e.depton as e_depton from dept as d,emp as e where d.depton=e.depton;
----------查看视图
show tables;
----------查看视图定义
show create view depton_list_view G;
select * from information_schema.views where table_name='depton_list_view'G;
----------删除视图
drop view depton_list_view;
----------查看事件调度器状态
show variables like 'event_scheduler';
----------创建事件调度器
create event test_event_1 on schedule every 5 second do insert into zzy.test1(create_time) values (now());
----------查看事件调度器
show events G;
----------禁用或删除事件调度器
alter event test_event_1 disable;
drop event trunc_test_1;
----------锁定表 >
lock tables dept read; ---read锁
----------释放表锁
unlock tables;
----------查看表锁
show open tables where in_use > 0;
----------查询表级锁争用情况
show status like 'table%';
----------查询慢查询
pt_query_digest --since '2020-08-27 03:40:00' --until '2020-08-27 04:00:00' --limit 100% prd-138-0020-slow.log > /Data/backup/slow_report.log
----------分析表
analyze table dept;
----------检查表
check table w_dept;
----------优化表
optimize table mt_meeting;

mysql

概述

MySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年,SUN又被Oracle收购。MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内。这样就增加了速度并提高了灵活性。MySQL的SQL“结构化查询语言”。SQL是用于访问数据库的最常用标准化语言。MySQL软件采用了GPL(GNU通用公共许可证)。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

体系结构

https://www.processon.com/view/link/60e4f6d65653bb46e90ece0b

数据库的基本概念

事务

事务保证一组原子性的操作,要么全部成功,要么全部失败。一旦失败,回滚之前的所有操作。MySql采用自动提交,如果不是显式的开启一个事务,则每个查询都作为一个事务。

MYSQL 数据库中一个事务具备ACID表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。

多事务并发可能出现的问题

1.脏读
一个事务读到了另一个未提交事务修改过的数据。
(脏读只在读未提交隔离级别才会出现)
2.不可重复读
一个事务只能读到另一个已经提交的事务修改过的数据,并且其他事务每对该数据进行一次修改并提交后,该事务都能查询得到最新值。
(不可重复读在读未提交和读已提交隔离级别都可能会出现)
3.幻读
一个事务先根据某些条件查询出一些记录,之后另一个事务又向表中插入了符合这些条件的记录,原先的事务再次按照该条件查询时,能把另一个事务插入的记录也读出来。
(幻读在读未提交、读已提交、可重复读隔离级别都可能会出现)

未提交读
在读未提交隔离级别下,事务A可以读取到事务B修改过但未提交的数据。
可能发生脏读、不可重复读和幻读问题。
已提交读
在读已提交隔离级别下,事务B只能在事务A修改过并且已提交后才能读取到事务B修改的数据。
读已提交隔离级别解决了脏读的问题,但可能发生不可重复读和幻读问题
可重复读
在可重复读隔离级别下,事务B只能在事务A修改过数据并提交后,自己也提交事务后,才能读取到事务B修改的数据。
可重复读隔离级别解决了脏读和不可重复读的问题,但可能发生幻读问题。
串行化
最高隔离级别,强制事务串行执行

提问:为什么上了写锁(写操作),别的事务还可以读操作?
因为InnoDB有MVCC机制(多版本并发控制),可以使用快照读,而不会被阻塞。

提问:为什么innodb默认使用可重复读?
因为,binlog和sql执行顺序问题。

数据库通过锁机制来解决并发场景-共享锁(读锁)和排他锁(写锁)。读锁是不阻塞的,多个客户端可以在同一时刻读取同一个资源。写锁是排他的,并且会阻塞其他的读锁和写锁。
要锁定数据需要一定的锁策略来配合。
表锁,锁定整张表,开销最小,但是会加剧锁竞争。
行锁,锁定行级别,开销最大,但是可以最大程度的支持并发。

mysql 存储引擎
InnoDB引擎,最重要,使用最广泛的存储引擎。被用来设计处理大量短期事务,具有高性能和自动崩溃恢复的特性。5.5之后的默认设置
MyISAM引擎,不支持事务和行级锁,崩溃后无法安全恢复。

mysql的优化主要分为

数据备份优化

用户权限优化

部署环境优化

运行性能优化

监控管理优化

数据库设计优化

  1. 数据类型优化

尽量使用对应的数据类型。比如,不要用字符串类型保存时间,用整型保存IP
选择更小的数据类型。能用TinyInt不用Int。
标识列(identifier column),建议使用整型,不推荐字符串类型,占用更多空间,而且计算速度比整型慢。
不推荐ORM系统自动生成的Schema,通常具有不注重数据类型,使用很大的VarChar类型,索引利用不合理等问题。
创建完全的独立的汇总表缓存表,定时生成数据,用于用户耗时时间长的操作。对于精确度要求高的汇总操作,可以采用 历史结果+最新记录的结果 来达到快速查询的目的。

查询优化
响应时间 (服务时间,排队时间)
扫描的行
返回的行

避免查询无关的列,如使用Select * 返回所有的列。
避免查询无关的行
切分查询。将一个对服务器压力较大的任务,分解到一个较长的时间中,并分多次执行。如要删除一万条数据,可以分10次执行,每次执行完成后暂停一段时间,再继续执行。过程中可以释放服务器资源给其他任务。
分解关联查询。将多表关联查询的一次查询,分解成对单表的多次查询。可以减少锁竞争,查询本身的查询效率也比较高。因为MySql的连接和断开都是轻量级的操作,不会由于查询拆分为多次,造成效率问题。
注意count的操作只能统计不为null的列,所以统计总的行数使用count(*)
关联查询延迟关联,可以根据查询条件先缩小各自要查询的范围,再关联
Union查询默认去重,如果不是业务必须,建议使用效率更高的Union All
Limit分页优化。可以根据索引覆盖扫描,再根据索引列关联自身查询其他列。

SELECT
 id,
 NAME,
 age
WHERE
 student s1
INNER JOIN (
 SELECT
   id
 FROM
   student
 ORDER BY
   age
 LIMIT 50,5
) AS s2 ON s1.id = s2.id

一、基础环境

备:192.168.1.242
主:192.168.1.241
pmoopr/密码
root/密码
mysql:密码

二、安装步骤

开始安装:

[root@yycgggdb Data]# rpm -ivh Percona-Server-server-57-5.7.26-29.1.el7.x86_64.rpm
warning: Percona-Server-server-57-5.7.26-29.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
Percona-Server-client-57 is needed by Percona-Server-server-57-5.7.26-29.1.el7.x86_64
Percona-Server-shared-57 is needed by Percona-Server-server-57-5.7.26-29.1.el7.x86_64
net-tools is needed by Percona-Server-server-57-5.7.26-29.1.el7.x86_64

- 阅读剩余部分 -