MySQL基础、主从复制、优化

MySQL

apache公司 开源共享 免费

mysql [-hlocalhost] -uroot -p 以超级管理员的身份登录

use demo; 查看

• DCL（数据库控制语言）:

• DDL（数据库定义语言）:

• DML（数据库操纵语言）:

• DQL（数据库查询语言）:

• TCL（事务控制语言）：

• 数据库锁：

• 主从配置：

命令行链接方式

mysql -u用户名   -p密码  -h服务器IP地址   -P服务器端口MySQL端口号   -D数据库名
mysql -uroot -p9264934.. -hlocalhost -P3306 -Dguanli

数据库控制语言命令（DCL）:

创建本地用户

-- 选择mysql数据库
use mysql;
-- 创建本地用户
create user 'superboy'@'localhost' identified by 'iamsuperboy';
-- 刷新MySQL的系统权限相关表，使添加用户操作生效，以免会出现拒绝访问
flush privileges;

创建远程用户

-- 从192.168.122.12登陆的用户
create user 'superboy'@'192.168.122.12' identified by 'password';
-- 从任意ip登陆的用户
create user 'superboy'@'%' identified by 'password';
-- 不做指定默认为'%'
create user 'superboy' identified by 'password';

对用户的基本操作

 创建用户
 create user 'wang'@'localhost' identified by 'iamsuperboy';
 
 修改用户的权限：
 grant all privileges on *.* to 'wang'@'%';
 
 # all 可以替换为 select,delete,update,create,drop
 -- 赋予部分权限，其中的shopping.*表示对以shopping所有文件操作。
 grant select,delete,update,insert on 数据库.* to 'wang'@'localhost' identified by 
 'superboy';
 
 -- 赋予所有权限
 grant all privileges on 数据库.* to superboy@localhost identified by 'iamsuperboy';
 
 'revoke'
 --撤销权限
 revoke privlieges_type on 权限名 from 用户名
 
 删除用户：
 Delete FROM mysql.user Where user='user_name' and host='localhost' ;
 
 --允许远程链接
 grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;flush privileges;--刷新

修改密码

1、set password for 'wang'@localhost = '123456'
2、update mysql.user set password=password('新密码') where user='root' and         host='localhost' flush privileges;
3、mysqladmin -u用户名 -p旧密码 password 新密码
​
#忘记root密码
1、停掉mysql服务 serve mysql stop
2、cmd中进入安装目录bin 然后 mysqld --console --skip-grant-tables --shared-memory  #跳过权限检查
3、再开启另一个cmd进入mysql    #直接输入mysql
4、flush privileges
5、退出，并重启系统

 

数据库备份

mysqldump -u [username] -p[password] [database_name] > [文件夹路径]
mysqldump -uroot -p9264934.. -hlocalhost wangwei > mysql
​
​
mysqldump -u [username] -p[password] --no-data [database_name] > [文件夹路径] #只要结构，不要数据
mysqldump -uroot -p9264934.. -hlocalhost --no-data wangwei > mysql
​
​
mysqldump -u [username] -p[password] --no-create-info [database_name] > [文件夹路径] #只要数据，不要结构
mysqldump -uroot -p9264934.. -hlocalhost --no-create-info wangwei > mysql
​
​
mysqldump -u [username] -p[password] [dbname1,dbname2] > [文件路径]  #备份多个数据库

 

 

数据库查询

• 数据库查看

- 查看所有的表：show tables
​
- 查看表的信息： show  full tables;
​
- 查看相关列：show columns from teach；
   show columns from teach like 'id'；
   show columns from teach like '%e%'； #查看带有 ’e‘的内容

• 查看用户信息

查看当前用户：select user();
         select current_user();
   
查看当前有多少用户登录
   select user,host,db,command from information_schema.procrsslist:

 

数据库维护

• 分析表

analyze table 表名1 [表名2]

• 优化表

optimize table 表名

• 检查表

check table 表名

• 修复表

repair table 表名

 

 

• 查看表的字段内容

desc t1;         //自增  primary key//主键

• 添加数据

insert into t1 values(1,'tom','man',18);

insert into t1 (id,name,sex,age) values(1,'tom','man',18);

• sql注入

" or 1=1;-- 

 

• 查看数据是否添加成功

select * from t1;

• 删除一个表

drop t1;

• 删除表中的一行数据

delete from stu where id=1

• 替换某字段内容的update语句

update t1 set id=replace(id,1,2);

update t1 set sex='woman' where c_id=1;

 

数据库定义语言（DDL）

新建数据库

create database [if not exists] database_name

删除数据库

drop database [if exists] database_name

创建表

create table ceshi1(
id int(10) not null auto_increment primary key,
name varchar(10),
age varchar(10),
sex enum('男','女') NOT NULL,
phone varchar(11) unique,
habbit set("游泳","健身"),
pasword varchar(32))default charset=utf8;    comment='示例';

 

修改表

alter table 表名 add|drop|modify|change|alter|rename

• alter 增加列

alter table user add sex enum("男","女") not null [after name]

• alter 增加在table最后一列 的一个属性

alter table t2 add hobby char(20);

• alter 在table的第一列添加新字段属性

alter table t2 add project int first;

• alter 在sex字段之后添加新字段num属性

alter table t2 add num int after sex;

• alter 删除表中的字段

alter table t2 drop class_id; //删除class_id字段

• alter 添加主键

alert table t2 primary key(id)

• alter (设置默认值)

ALTER TABLE student ALTER class_id SET DEFAULT 0;//设置默认值为0
ALTER TABLE student ALTER class_id DROP DEFAULT;//删除默认值

*为什么有了MODIFY和CHANGE还要来个ALTER呢？这是因为另外两个在修改的时候会把字段之前旧的属性全部覆盖掉

举个例子：现在需要修改class_id DEFAULT的值，我们需要这么写

ALTER TABLE student MODIFY class_id VARCHAR(20) DEFAULT 10;

而采用ALTER则不需要则可以只需要设置默认值

 

• modify && change (修改字段)

区别：change必须指定新的字段名而modify则不需要

如果需要修改字段名只能用CHANGE，否则用哪个都可以

alter table t1 modify num char(10);
//modify 修改num 的数据类型int(10)=>char(10)

 

ALTER TABLE t2 CHANGE class_id c_id VARCHAR(20);
//将class_id字段名改为c_id并修改数据类型为VARCHAR(20)
ALTER TABLE t2 CHANGE class_id class_id VARCHAR(20);
//修改class_id数据类型为VARCHAR(20)不修改字段名
ALTER TABLE t2 MODIFY class_id VARCHAR(20);
//将class_id数据类型修改为VARCHAR(20)

 

• rename (重命名数据表）

alter table t2 rename to t2s;

修改存储引擎

• 修改引擎

#先删除再添加
alter table t2s engine=myisam;   //将存储引擎修改为myisam

• 删除引擎

alter table t2 drop index column_name

• 删除引擎

alter table t2 engine=InnDB
show engines
show create table 表名

• 修改自增值（开始值）

alter table t2 auto_increment = 1

 

MyISAM

不支持事务，不支持外键，访问速度特别快（主要的基本应用为insert，select），创建成功后，有以下三个文件，扩展名分别为（二进制）

• .frm(存储表定义，表结构)

• MYD（MYData存储数据）

• MYI（MYIndex，存储索引）

InnoDB

• 健壮的事务型存储引擎

• 更新密集的表

• 自动灾难恢复

• 外键约束

• 需要事务支持

 

索引类型

• 主键索引 primary

  • 一个表中唯一的，在数据的查询，写入，读出能够按照一定的顺序，一定排列进行有序的操作，并且除主键外的其他的字段都会收到其影响

  • 主键的值只能是唯一的，不能重复，auto_increment

• 唯一的键 unique

  • 一个表中能够给多个字段设置唯一的键，他会在查询本字段，形成一定的顺序，分组查询

  • 在本字段中，不能出现相同的内容，除了NULL外

• 普通索引 index

  • 能给多个字段设置普通索引，会在查询本字段

• 文本索引 fulltext mysql 5.7版本后有效

  • 文本编辑器

  • 帮助我们在大批文本中有序查找内容

 

外键

• 查看外键

show create table student;

 

• 创建外键

alter table student
add foreign key(cid)
references classes(cid) on delete cascade;

• 删除外键

alter table student 
drop key aa;  #删除约束
alter table student 
drop foreign key aa;   #删除键

 

1、create table if not exists classes(     #创建主表 ：班级表
cid int(10) auto_increment primary key,
cnaem varchar(20))default charset=utf8
​
​
2、create table if not exists student(     #创建副表 ：学生表
sid int(10) auto_increment primary key,
snaem varchar(20),
cid int(10),
constraint aa foreign key(cid) references classes(cid) # ******加外键
)default charset=utf8

• 外键链接下，删除主表内容提示

on delete
district（默认）     cascade（同时删除）   no action（什么都不做）   set null(设置为空)
​
on update
district（默认）     cascade（同时删除）   no action（什么都不做）   set null(设置为空)
​
​
create table if not exists student(     #没附表的情况下   创建副表
sid int(10) auto_increment primary key,
snaem varchar(20),
cid int(10),
constraint aa foreign key(cid) references classes(cid) on delete cascade
)default charset=utf8
​
#或
​
alter table student     #有副表没外键的情况下
add foreign key(cid)
references classes(cid) on delete cascade;

 

数据库操纵语言（DML）

insert

• 插入的内容与原unique的id等唯一值冲突的时候

insert into student (sid,sname,cid) values (3,ai,3) on duplicate key update sid=sid+1     #student 内有sid=3的数据

• 快速复制一个表的结构

create table aaa like stu

• 快速复制一个表的内容

insert into aaa select * from stu

 

 

• replace

可以置换现有的主键或unique

replace into aaa (sname, cid) value("zhangsan",3)

 

update

update low_priority t1 #low_priority 延迟更新，等没人查询在更新 set column_name1 = expr1 column_name2 = expr2

where
    condition
show full tables

• 带有select子句的更新

update stu set sname='111' where id=5;
select tname from teach order by id asc;#随机取 desc倒序
select tname from teach order by rand() limit 1;
#两个表之间的更新
update stu set tname=(select tname from teach order by rand() limit 1) where tname is null;
#关联更新
update table1,table2,...
set table1.attr=val,table2.attr=val,...
where condition
​
update table1 join table2 on...
set table1.attr=val,table2.attr=val,...
where condition

 

update classes，student where classes.cname=student.sname set classes.cname="aa",student.sname=""  

 

delete

• 带有limit的删除语句

delete from student order by id desc limit 1  #删除id从后致前的第一个

 

• 关联删除

delete classes,student from classes,student where classes.cname="allj" 

 

 

清空数据

delete fro student     # 逐条删除，主键自增不会从1开始，而是继续，效率低

truncate [table] student     #自增从1开始  效率高

 

日志管理

记录服务器运行信息，通过日志文件可以监视服务器的运行状态和性能，还能对服务器进行排错与故障处理

• MySQL有六种不同类型的日志：

  • 错误日志：记录启动，运行或停止时出现的问题，一般也记录警告信息 一般开启

  • 一般查询日志：记录客户端的链接和执行的语句 一般关闭

  • 慢查询日志：记录所有执行时间超过long_time的所有不适用索引的

  • 二进制日志：数据库信息有任何改变，都会放到二进制日志中 （需要指定）

  • 中继日志：

  • 事务日志：

• 查询变量

  
   show global variables [like '%log%']

• 修改变量

  
  set global variables_name=val  

1. 错误日志

   1. 查看错误日志地址

      
      show global variables like  ”log_error"    

   2. 警告信息开关

      
       show global variables like "log_warnings"; #查看是否开启 开启为1，关闭为0
       set global log_warnings=0   #关闭

2. 一般查询日志

   
   1. 启用开关：general_log=(ON|OFF)     #set global general_log=ON   一般时候都关闭
   2. 记录类型：log_output    #show global variables like 
   3. 查看存储位置：general_log_file       #show global variables like 

   1. 查看错误日志地址

      
      show global variables like  ”log_error"   警告信息开关2.

      2.错误警告开关

   
   show global variables like "log_warnings"; #查看是否开启 开启为1，关闭为0
   set global log_warnings=0   #关闭

3. 慢查询日志

   放到配置文件中

   
   查询超时时间： long_query_time=3
   查询慢查询 ： log_slow_querys={YES|NO}
   启动慢查日志： log_query_log=1 (on|off)
   日志记录文件： slow_query_log_file[=file_name]

数据库查询语言（DQL）

* 通配符 所有

函数	描述
escape ‘$’	把 $ 规定为转义字符
cast(1982-3-1 as data)	把1928-3-1转换成data型
count(cname)	cname的条数
sqlfind_in_set(needle,place)	needle:查询内容 place：所在字段

select

select count(cname) as num from classes

 

select
​
column_1 , column_2,...
​
from a
​
 table_1

• 模糊查询

  内容	描述
  %老师	以老师结尾的数据
  老师%	以老师开头的数据
  _老师	老师前面只有一个可变量
  老师_	老师后面只有一个可变量
  like	精确查询
  =	精确查询

• where

• 语句顺序

where 
​
 conditions
​
group by column_1
​
having group_conditions
​
order by column_
​
limit offset, length

操作符                                    描述
=                    #等于，几乎任何数据类型都可以用
<> !=                #不等于
<
>
<=
>=
​
逻辑运算符
or                   #或者
and                  #并且
not     #非

操作/逻辑运算符	描述
=	等于
<>!=	不等于
<	小于
>	大于
<=
>=
or	或
not	非
and	和

• between 包含两端

  (cast(1982-3-1 as data) 把1928-3-1转换成data型)

  select * from student where birth between cast(1982-3-1 as data) and cast(1988-6-10 as data) ;

• in

select * from classes where sqlfind_in_set(l,cname)   #一个字段中含多个内容

 

 

• group by

select
    c1,c2,c3,....
from
 table
where
 where_conditions
group by t1,t2,t3...;

 

having

分组后进行筛选 where是分组前筛选

having后可跟条件（函数）

 

函数	描述
avg()	计算一组值
count()
instr()
sum()
min()
max()

1、通过时间分类 看一时间段内进货 额

2、通过类别

3、时间、类

 

• order by

对单列或多列的查询结果 进行升序或降序排序

select column1,column2,...
from t2
order by num desc，price asc  #降序排列   asc升序   以前一列为基础，再排后一列
​
select column1,column2,...
from t2
order by field（name,"商品3"，"商品2","商品1"）desc / asc    #自定义排序

 

• limit

约束查询结果的行数 ，一般跟order by 一起使用

select column1,column2,...
from tablename
limit offset , count     #offset：偏移量     count：条数
      #只有一个参数n：从头开始取n条
​
select * from goods where cid=1 group by cid desc limit

 

 

关联查询

表与表之间有关系，通过关系去查询

MySQL支持一下连接：

• 交叉链接

select cname,gname
from category cross join goods;
​

 

• 内链接

select
name
from
t1
inner join
t2 on t1.id=t2.id

 

• 左连接

select              #以左面为基础
name
from
t1
left join
t2 on t1.id=t2.id

 

• 右连接

select              #以右面为基础
name
from
t1
right join
t2 on t1.id=t2.id

• 联合查询

union    #可以去掉重复项    union all  包括重复项
​
select cname from category union select gname from goods;

 

子查询

把一个查询嵌套在另一个查询，叫内部查询

• 分类

  1. 标量子查询：返回但一直的标量，最简单的形式

  2. 列子查询：返回结果是N行一列

  3. 行子查询：返回结果为一行N列

  4. 表子查询：返回结果为N行N列

 

1. 标量子查询(一个值)

select * from article where uid = (select uid from user where status=1 order by uid desc limit 1)                  in     

• 关键字 any all

  any ：< > = <>条件中的任何一个就可以

  all： < > = <> 条件中的所有条件才可以

  
  select * from goods where cid < any (select id from category where id=3 or id=2) 
                                 #all

2.列子查询

​

 

函数

聚合函数

总值，平均值，最大值，最小值，求和 除count外会忽略null

• count（）

  
  select count(*) from category group by cid;

• avg 平均数

  
  ​

• num

• max(),min()

• group_conatan()

  把分组后的结果连接起来

  
  select group_concat(gname) from goods group by cid;   

• concat() 字符串链接函数

  
  select concat("first","last") from names
  ​
  #concat_wg()   指定连接符号
  select concat_ws("-","first","last") from names

 

• left（） 指定从左至右取的内容长度

  select left ("abcdef",3)
  "abc"

 

• replace（） 替换，更新

  select replace（"this is firts","firts","first"）  #把""中的 firts 换成first

 

• substring() 截取 可以从任意位置取

  select substring("abcdefg"2,2)   #从第二位开始，取两个

 

• trim() 删除不必要的前导或尾随字符

  select trim({both|leading|trailing} from str)
  
  select trim("   abcd   ")  #默认去掉前后的空格
  select trim(leading from    "abcd"   )  #之去掉前面的
  select trim(leading "a" from    "abcd"   )  #去掉前面的 a
  select trim(both "a" from    "abcd"   )   #去掉所有的 a

 

 

• format 保留有效数字位数

  默认en_US

  select format(1001.353535,2)   #保留三位
  select format(1001.353535,2，"de_DE")   #默认en_US 改成 de_DE

 

时间函数

• curdate() 返回当前的日期

select curdate()

• now()

select now()
select now(),sleep(5),now()

• sysdate()

  返回指定日期函数

select sysdate()
select sysdate(),sleep(5),sysdate()

• day() 获取今天几号

• month()

select month(now());

• year()

• weekday(now()) 获取星期几

• dayname() 获取星期几英文名称

set @@lc_time_names = 'zh_CN';     #改中文
select dayname(time) from goods;

 

时间计算函数

• datediff（）

select datediff("2017-08-03","2018-08-03")   #相差几天

 

• timediff()

select timediff("2017-08-03 12:00:00","2018-08-03 12:00:00")    #相差得时间  

 

• timestampdiff( unit, begin,end)

  unit:microsecond second minute hour day week month quarter year

#unit:microsecond,second,minute,hour,day,week,month,quarter,year
select timestampdiff(day,"2017-08-03 12:00:00","2018-08-03 12:00:00")    #相差得时间用什                   么单位表示  

 

• date_add(start_date,interval expr unit)

  在开始时间上加一个时间后得到的时间

select date_add("2018-12-31 23:59:59",interval "1:1" minute_second)

 

视图

mysql中有一些复杂的语句，对于复杂的查询，每一次查询都是对性能的消耗，而视图就是把第一次所查询出来的内容做成一个表

• 查看所有视图

show full view

• 查看视图创建过程

show create view viewname

 

• 创建视图

create view viewname as select ...

• 修改视图

alter view viewname as select ...

• 创建或替换视图

没有就创建，有就修改

creat or replace view viewname as select ...     #viewname：视图名

• 删除视图

drop view [databases.name].[viewname]

 

临时表

使用频率低，不想每一次都查询，又不想创建视图，临时保存数据，生命周期为数据库使用期间，自动删除

• 创建

create temporary table tempname select ...   #没有as   tempname：临时表表名

 

事务

只有InnoDB支持事务

数据库处理操作，执行就好像它是一个单一的一组有序的工作单元，在组内每个单独的操作是成功的，那么一个事务才是完整的，如果事务中任何操作失败，整个事务将失败。

事务性质

• 原子性：确保了工作单位中的所有操作都成功完成：否则，事务被终止，在失败时会被回滚到事务操作以前的状态。

• 一致性：可确保数据库在正确的更改状态在一个成功的结果提交事务。

• 隔离：使事务相互独立地操作。

• 持久性：确保了提交事务的结果或系统故障情况下仍然存在作用。

 

事务控制语句

• BEGIN或START TRANSACTION;显示地开启一个事务；

• COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的

• ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

• SET AUTOCOMMIT=0 禁止自动提交

• SET AUTOCOMMIT=1 开启自动提交 variable

 

锁

不同的存储引擎支持不同的锁机制 mysql中锁的大分类分为：1、表级锁，2、行级锁，3、页面锁

• 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

• 行级锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，并发度高。

 

表级锁

共享锁：读锁，所有人（包括自己）能查看，不能修改（修改会提示错误），锁期间不能操作别的表

独占锁：写锁，只有 自己可见可修改，锁期间不能操作别的表

• mysql表级锁存储引擎：

  • MyISAM 引擎

  • MEMORY 引擎

• 加锁（用到MySQL时候，MySQL本身已经给加锁了）

演示事务操作的时候 操作多条命令时，不希望被其他用户修改

lock tables t1 [t2] read [local],      lock tables t1 [t2] write [local]

• 释放锁

unlock tables;

• 查看表锁征用情况：

  • table_locks_immediate

  • table_locks_waited

  
  show status like "table%"  
  show status like "%lock%"
  show processlist   #查看那些当前是哪些命令在等待，从而进行优化
  show open tables   #当前倍速欧珠的表以及锁的次数

 

 

• 表锁优化

• optimize table 表名

• set concurrent_insert=2 允许并发插入

• 是否设置写的优先级，（登录）

• 是否设置写内存，解决批量插入数据（新闻系统更新）

• 解决并发问题

  • 并发插入（只能插入，不能修改和删除）

    MyISAM存储引擎有一个系统变量 concurrent_insert ,专门控制并发插入行为 值可为 0，1，2

    1. cuncurrent_insert 为0时：不允许并发插入

    2. cuncurrent_insert 为1：如果MyISAM没有空洞：即使有锁，也会从尾部插入 有空洞：不插入 （空洞：id=1,2,4 少3）

    3. cuncurrent_insert 为2：加锁的时候把 [local] 加上 allways 总是可以队尾插入信息（锁的情况下）********很重要，用mysql就改成2 set cuncurrent_insert=2

• 读写锁的优先级

  • 修改写锁的最大次数

  
  set global max_write_lock_count=1      #写一次之后暂停写操作，给读操作机会

  • 降低写锁的优先级

  
  set global low_priority_updates=1

• 设置写内存

  可以根据具体的业务设置读写内存

  
  max_allowed_packet=1M      # 限制接受的数据包大小，大的插入和更新会被简直掉
  net_buffer_length=2k       #insert 语句的缓存值 (),(),() 2k-16M
  bulk_insert_buffer_size=8M   #一次性insert语句插入的大小

间隙锁

数据库里id有 1，3，4，5 锁是id>1 再操作id=2的时候也会被锁。所以确定条件的时候一定要有范围

行级锁

引擎：InnoDB

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之如果两者不兼容，该事务就要等待锁释放

 

• 请求锁是否兼容当前锁模式

	X（排他锁）	IX（意向排他锁）	S（共享锁）	IS（意向共享锁）
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

• 特点

  • 想让InnoDB 上行锁，当前行的操作字段必须要有索引。

  • 如果操作行的操作字段没有索引，行锁会自动升级为表锁。

  • 即使有索引，但用字段的时候修改了类型，索引失效 eg:（规定类型：name varchar(255） 命令输入：name=0;）

  • 意向锁是InnoDB自己加的，不用干预；对于update,delete和insert，InnoDB会自动加排他锁（X）；对于select不会自动加共享锁（S）。

  • 当对一行上了排他锁（update，delete，insert），其他用户对这一行数据没有任何权限，但并不影响其他用户修改其他数据。

  • 当对一行上了排他锁（update，delete，insert）后，其他用户可以对当前行进行读操作：修改前的值（InnoDB默认隔离方式）。

  • 研究行锁时，需要将自动提交关闭，

    
    set autocommit = 0
    #注：如果有多个客户端，每个都要设置

• 加锁

  
  共享锁：select 后需要加  lock in share mode
  排他锁：select 后需要加  for update

• 释放锁

  
  commit;
  rollback;

• 查询行锁征用

show status like 'innodb_row_lock%';

• 隔离

• 查看隔离级别

select @@session.tx_isolation

• 设置隔离级别

set session transaction isolation level read uncommitted(读未提交)  #脏读
              #read committed（不可重复度）
              #read （可重复读）          
    #read用到mvcc技术（多版本并发控制）Multiversion Currency control

• 隔离级别

  • 可读取未确认（Read uncommitted）

    写事务阻止其他写事务，避免了更新遗失。但是没有阻止其他读事务。

    存在的问题：脏读。即读取到不正确的数据，因为另一个事务可能还没提交最终数据，这个读事务就读取了中途的数据，这个数据可能是不正确的。

    解决办法就是下面的“可读取确认”。

  • 可读取确认（Read committed）

    写事务会阻止其他读写事务。读事务不会阻止其他任何事务。

    大部分数据库使用的默认隔离级别，兼顾速度和正确性。

    存在的问题：不可重复读。即在一次事务之间，进行了两次读取，但是结果不一样，可能第一次id为1的人叫“李三”，第二次读id为1的人就叫了“李四”。因为读取操作不会阻止其他事务。

    解决办法就是下面的“可重复读”。

  • 可重复读（Repeatable read）

    读事务会阻止其他写事务，但是不会阻止其他读事务。

    存在的问题：幻读。可重复读阻止的写事务包括update和delete（只给存在的表加上了锁），但是不包括insert（新行不存在，所以没有办法加锁），所以一个事务第一次读取可能读取到了10条记录，但是第二次可能读取到11条，这就是幻读。

    解决办法就是下面的“串行化”。

  • 可串行化（Serializable）

    读加共享锁，写加排他锁。这样读取事务可以并发，但是读写，写写事务之间都是互斥的，基本上就是一个个执行事务，所以叫串行化。

 

• 脏读：

  事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。

• 不可重复读：

  在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。

• 幻读：

  在同一个事务中，同一个查询多次返回的结果不一致。通常是因为在事务A中进行了一次全局操作，如新增了一条记录；事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。

 

• 优化行级锁

1、精心设计索引，尽量使用索引访问数据
2、选择合理的事务，小事务发生锁冲突的几率小
3、给记录集手动加锁，最好一次性请求足够级别的锁（写锁级别>读锁级别），不要先申请共享再申请排他。
4、尽量用相等的条件访问数据，这样可以避免间隙锁对并发插入的影响
5、对于一些特定事务，可以使用表锁来提高处理速度或减少死锁的可能。（购物，三表同时修改 库存，销售记录，购 物车）

 

 

• 练习

课程表    id    课程名   老师id
​
老师表     id   姓名    简介id
​
简介表     id   信息      
​
查看 带python老师的信息 3种

1、select iname from info where id=(select iid from teacher where id=(select tid from class where cid=1));

2、 SELECT iname FROM info,class,teacher where info.id=teacher.iid and class.tid=teacher.id and class.cid=1;

3、 select iname FROM info INNER join teacher on info.id=teacher.iid INNER JOIN class on teacher.id=class.tid where class.cid=1;

4、select iname from info join teacher on info.id=teacher.iid where teacher.id=(select tid from class where cid=1);

• explain+语句 测速率 分析语句合理性

主从复制

实际生产中，有单台MySQL数据库是完全不满足实际需求，无论安全性，高可用性以及高并发性等各方面要求。而主从mysql会实现mysql的实时备份，高可用，读写分离场景。

• 原理

 

• 配置过程

  • 两台服务器

  • 双方mysql版本一致，如果不一致，从结点高于主节点

  • 服务器防火墙要关闭掉

    sudo ufw status ubuntu系统

  • 上方数据库用户需要具有远程访问的权限

• 修改主服务器的MySQL的配置文件 window 放到（my.ini）的mysqld下面 linux(my.cnf)

  
  #mysql 唯一id
  server-id=1
  #二进制文件，此项为必填项，否则不能同步数据 （可以加路径）
  log-bin="mysql-bin"
  #指定二进制错误文件所放的位置
  log-error="mysql-error"
  #需同步的数据库 如果需要同步多个
  binlog-do-db=wangwei
  #binlog-do-db="表名"
  ​
  #不需要同步的数据库
  binlog-ignore=mysql
  ​
  read_only   #只读  

• 给从数据库授权（可以从主服务器取数据）

grant replication slave on *.* to 'root'@'192.168.2.155' identified by '9264934.。' flush privileges;

• 重启服务器（数据库服务）

service mysqld restart   #

• 查看主服务器状态

  
  show master status;

• 从服务器配置

  1.修改从服务器的mysql配置文件，注意ID没有被别的mysql服务占用

  
  server-id = 2
  log-bin = "mysql-bin"
  replicate-do-db =wangwei
  replicate-ignore-db = mysql 

  2.重启mysql服务

  3.执行同步sql语句

  change master to
  master_host='192.168.217.135', #设置要连接的主服务器IP地址master_user='root', #设置要连接的主服务器用户名
  master_password='123456', #设置要连接的主服务器密码
  master_log_file='mysql-bin.000002', #设置要连接的主服务器bin日志的日志名称
  master_log_pos=1041; #设置要连接的主服务器bin日志的记录位置
  
  
  change master to
  master_host = '192.168.217.135',
  master_user = 'root',
  master_password = '123456',
  master_log_file = 'mysql-bin.000008',
  master_log_pos = 107;
  
  
  

  4.启动slave同步进程

  
  start slave
  #查看状态
  show slave status\G   # 查看同步状态
  #其中Slave_IO_Running Slave_SQL_Running值都是YES，表示状态正常
  #如果之前从服务器启动过需要先停止在运行
  stop slave

• 查看二进制信息

  
  show binary logs;   #二进制文件
  show master logs;   #当前使用的
  ​

• 删除二进制文件

  
  purge binary logs to "mysql-bin.000002";     #删除此文件之前的文件

  • 自动清理

  
  show variable like "expire_logs_day"
  ​
  set expire_logs_day=7   #7天自动清理一次

优化

• 表级优化（锁）

• 系统优化

  • 主从复制

  • 负载均衡

  • 读写分离

 

多表查询，小表在前，大表在后 （where 小表 .x = 大表.y）

left join 给左边的表加索引，right 给右边的表加索引

• explain

1. id:

   • 相同时，由上至下

   • 不同时，从大到小

• 查询语句

  
   select * fgroup by
  distanc+-1
  ​

• 派生表

  在查询内容再查询

  
  select aa..cname from (select cname,tid from class) as aa;

• type

  从表中找到自己想要的数据的方式

  ALL<index<range<ref<eq_ref<const<system<NULL

  
  ALL:遍历全表找到匹配的行,并且查找的内容不带索引
  index：只遍历索引树，查找索引的列     #加索引
  range: 检索给定的范围，查找的内容不带索引，选择的行带索引   #id<10 尽量不用in in会破坏掉索引
  ref:确定的一个值    #where name="zhangsan"
  eq_ref:类似ref，区别就在于使用的是唯一索引 unique 
  const:主键关联，主键查询
  system：

• key_len

  索引所占的内存空间 utf8 英文占3字节 中文占4字节

• Extra

  额外信息：

  1、Using：temporary: 需要额外的内存存储信息
  
  2、Impossible whe:条件有问题
  
  3、Using filesort:  多次排序，没有索引
  
  
  

  复合索引：最佳左前缀；

 

筛选需优化的内容

用慢查询日志筛选  自己指定一个时间，超出long_time的语句会存到慢查询的日志中，然后在日志中找到那条语句用explain检测。

优化索引

可以把要查找的内容加上索引来补救

• 不能将索引作为表达式的一部分，也不能作为参数，否则索引失效

  
  explain select * from class where id+1=1
  explain select tname from teacher where left(tname,3)="";  #没有索引

• 索引不能做类型转化

  
  explain select iid from teacher where tname=111   #tname varchar（） "1111"

• 符合索引遵循左前缀策略

• 索引不能跟or 否则全部失效；

• 复合索引不能有不等号 <> != 或者is null

• 不能用in 可以用between

• 及时删除不用的索引

• like 查询时尽量不要出现左边的“%”

  
  explain select iid from teacher where tname like "%xx";

其他优化

1. 把NULL改成NOT NULL

2. 根据业务，尽可能选择小的存储数据类型

3. unsigned 表示不允许复制 -127~128 = 0~255

4. timestamp（时间戳） 使用4个字节， 而 datetime 使用8个字节

5. 基本没有使用enum（枚举）

6. 尽量少列 不大于10列

7. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

8. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

9. 很多时候用 exists 代替 in 是一个好的选择

10. 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

转载于:https://www.cnblogs.com/weigaojie/p/10491265.html