MySQL高级总结_mysql ` change column-程序员宅基地

技术标签: 数据库  

Mysql/SQL/JDBC/Redis
一、MySQL
一、数据库的好处
1、可以持久化数据到本地
2、结构化查询

二、数据库的常见概念 ★
1、DB:数据库,存储数据的容器
2、DBMS:数据库管理系统,又称为数据库软件或数据库产品,用于创建或管理DB
3、SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言

三、数据库存储数据的特点
1、数据存放到表中,然后表再放到库中
2、一个库中可以有多张表,每张表具有唯一的表名用来标识自己
3、表中有一个或多个列,列又称为“字段”,相当于java中“属性”
4、表中的每一行数据,相当于java中“对象”

四、常见的数据库管理系统
mysql、oracle、db2、sqlserver

一、MySQL的背景
前身属于瑞典的一家公司,MySQL AB
08年被sun公司收购
09年sun被oracle收购

二、MySQL的优点
1、开源、免费、成本低
2、性能高、移植性也好
3、体积小,便于安装

三、MySQL的安装
属于c/s架构的软件,一般来讲安装服务端
企业版
社区版

5.5
5.6
5.7
8.0

四、MySQL服务的启动和停止
方式一:通过命令行
net start 服务名
net stop 服务名
方式二:计算机——右击——管理——服务

五、MySQL服务的登录和退出
登录:mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码
退出:exit或ctrl+C

二、DQL
基础查询
条件查询
排序查询
常见函数
分组查询
连接查询
子查询
分页查询
联合查询
查询总结

1.基础查询
一、语法
select 查询列表
from 表名;
二、特点
1、查询列表可以是字段、常量、表达式、函数,也可以是多个
2、查询结果是一个虚拟表
三、示例
1、查询单个字段
select 字段名 from 表名;
2、查询多个字段
select 字段名,字段名 from 表名;
3、查询所有字段
select * from 表名
4、查询常量
select 常量值;
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
5、查询函数
select 函数名(实参列表);
6、查询表达式
select 100/1234;
7、起别名
①as
②空格
8、去重
select distinct 字段名 from 表名;
9、+
作用:做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
select null+值;结果都为null
10、【补充】concat函数
功能:拼接字符
select concat(字符1,字符2,字符3,…);
11、【补充】ifnull函数
功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
select ifnull(commission_pct,0) from employees;
12、【补充】isnull函数
功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0

2.条件查询
一、语法
select 查询列表
from 表名
where 筛选条件
二、筛选条件的分类
1、简单条件运算符>< = <> != >= <= <=>安全等于
2、逻辑运算符
&& and
|| or
! not
3、模糊查询
like:一般搭配通配符使用,可以判断字符型或数值型
通配符:%任意多个字符,_任意单个字符
between and/not between and
in含义:判断某字段的值是否属于in列表中的某一项。特点:①使用in提高语句简洁度②in列表的值类型必须一致或兼容③in列表中不支持通配符。
=或<>不能用于判断null值 is null /is not null:用于判断null值
IS NULL:仅仅可以判断NULL值,可读性较高,建议使用
<=> :既可以判断NULL值,又可以判断普通的数值,可读性较低

3.排序查询
一、语法
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc}desc】
二、特点
1、asc :升序,如果不写默认升序
desc:降序
2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后(除limit语句之外)

4.常见函数
一、概述
功能:类似于java中的方法
好处:提高重用性和隐藏实现细节
调用:select 函数名(实参列表);
二、单行函数
1、字符函数
concat:连接
substr:截取子串()索引从1开始/截取从指定索引处后面所有字符/截取从指定索引处指定字符长度的字符
upper:变大写
lower:变小写
replace:替换 replace(字符串,要替换的字符串,替换后的字符串)
length:获取字节长度
trim:去前后空格(或指定字符串,trim(字符串 from ))
lpad:左填充,用指定的字符实现左填充指定长度
rpad:右填充,用指定的字符实现右填充指定长度
instr:获取子串第一次出现的索引,如果找不到返回0
2、数学函数
ceil:向上取整,返回>=该参数的最小整数
round:四舍五入
mod:取模
floor:向下取整,返回<=该参数的最大整数
truncate:截断
rand:获取随机数,返回0-1之间的小数
3、日期函数
now:返回当前日期+时间
year:返回年
month:返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期,,不包含时间
str_to_date:将字符转换成日期
curtime:返回当前时间,,不包含日期
hour:小时
minute:分钟
second:秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月
4、其他函数
version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password(‘字符’):返回该字符的密码形式
md5(‘字符’):返回该字符的md5加密形式
5、流程控制函数
①if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
②case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2

else 值n
end
③case情况2
case
when 条件1 then 值1
when 条件2 then 值2

else 值n
end
三、分组函数,做统计使用,又称为统计函数、聚合函数、组函数
1、分类
max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数
2、特点
①语法
select max(字段) from 表名;
②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
③以上分组函数都忽略null,除了count()
④都可以搭配distinct使用,实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count(字段):统计该字段非空值的个数
count(
):统计结果集的行数
效率上:
MyISAM存储引擎,count()最高
InnoDB存储引擎,count(
)和count(1)效率>count(字段)
⑥ 和分组函数一同查询的字段,要求是group by后出现的字段

5.分组查询
一、语法
select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
二、特点
使用关键字 筛选的表 位置
分组前筛选 where 原始表 group by的前面
分组后筛选 having 分组后的结果 group by 的后面
1、和分组函数一同查询的字段必须是group by后出现的字段
2、分组可以按单个字段也可以按多个字段,多个字段之间用逗号隔开;也可以按按表达式或函数分组
3、分组函数做条件肯定是放在having子句中的
一般来讲,能用分组前筛选的,尽量使用分组前筛选,提高效率
4、可以搭配着排序使用,放在整个分组查询的最后
6、having后可以支持别名

6.连接查询
一、含义
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2
from 表1,表2,…;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
如何解决:添加有效的连接条件
二、分类
按年代分类:
sql92:
等值
非等值
自连接
也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
sql99【推荐使用】
内连接
等值
非等值
自连接
外连接
左外
右外
全外(mysql不支持)
交叉连接
三、SQL92语法
1、等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点:
① 一般为表起别名
②多表的顺序可以调换
③可以连接多个表,n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
2、非等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3、自连接
语法:
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
四、SQL99语法
1、内连接
语法:
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
分类:
1.等值连接
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
2.非等值连接
3.自连接

2、外连接
语法:
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
应用场景:用于查询一个表中有,另一个表没有的记录
特点:
①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
②left join 左边的就是主表,right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行
3、左外和右外交换两个表的顺序,可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
5.外连接加上筛选条件后is null则会取出交集部分只剩下自己独有的(查询哪个城市没有部门WHERE d.department_id IS NULL;)

3、交叉连接
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
特点:
类似于笛卡尔乘积
sql92和 sql99pk区别:
功能:sql99支持的较多
可读性:sql99实现连接条件和筛选条件的分离,可读性较高

7.子查询
一、含义
嵌套在其他语句内部的select语句称为子查询或内查询,
外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多
外面如果为select语句,则此语句称为外查询或主查询
特点:
1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面,但一般放在条件的右侧
3、子查询优先于主查询执行,主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类:
① 单行子查询
结果集只有一行
一般搭配单行操作符使用:> < = <> >= <=
非法使用子查询的情况:
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集有多行
一般搭配多行操作符使用:any、all、in、not in
in: 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替(<any == min)
二、分类
1、按出现位置
select后面:
仅仅支持标量子查询
from后面:
表子查询,将子查询结果充当一张表,要求必须起别名
where或having后面:
标量子查询
列子查询
行子查询
exists后面:相关子查询
标量子查询
列子查询
行子查询
表子查询
2、按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为一行多列
表子查询:结果集为多行多列
三、示例
where或having后面
1、标量子查询
案例:查询最低工资的员工姓名和工资
①最低工资
select min(salary) from employees
②查询员工的姓名和工资,要求工资=①
select last_name,salary
from employees
where salary=(
select min(salary) from employees
);
2、列子查询
案例:查询所有是领导的员工姓名
①查询所有员工的 manager_id
select manager_id
from employees
②查询姓名,employee_id属于①列表的一个
select last_name
from employees
where employee_id in(
select manager_id
from employees
);

8.分页查询
一、应用场景
当要查询的条目数太多,一页显示不全
二、语法
select 查询列表
from 表
limit 【offset,】size;
注意:
offset代表的是起始的条目索引,默认从0开始
size代表的是显示的条目数
公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

select 字段|表达式,… 7
from 表 1
连接类型 join 表2 2
on 连接条件 3
【where 条件】 4
【group by 分组字段】 5
【having 条件】 6
【order by 排序的字段】 8
limit 【起始的条目索引,】条目数; 9

9.联合查询
一、含义
union:合并、联合,将多次查询结果合并成一个结果
二、语法
查询语句1
union 【all】
查询语句2
union 【all】

三、意义
1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候,查询的列基本是一致
四、特点
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重,union all包含重复项

10.查询总结
语法:
select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引,条目数; ⑨

三、DML
1.插入
一、方式一
语法:
insert into 表名(字段名,…) values(值,…);
特点:
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段,注意可以通过以下两种方式插入null值
①字段和值都省略
②字段写上,值使用null
4、字段和值的个数必须一致
5、字段名可以省略,默认所有列
二、方式二
语法:
insert into 表名 set 字段=值,字段=值,…;
两种方式 的区别:
1.方式一支持一次插入多行,语法如下:
insert into 表名【(字段名,…)】 values(值,…),(值,…),…;
2.方式一支持子查询,语法如下:
insert into 表名
查询语句;

2.修改
一、修改单表的记录 ★
语法:update 表名 set 字段=值,字段=值 【where 筛选条件】;
二、修改多表的记录【补充】级联更新
语法:
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;

3.删除
方式一:使用delete
一、删除单表的记录★
语法:delete from 表名 【where 筛选条件】【limit 条目数】
一删就是整行
二、级联删除[补充]
语法:
delete 别名1,别名2 from 表1 别名
inner|left|right join 表2 别名
on 连接条件
【where 筛选条件】
方式二:使用truncate
语法:truncate table 表名
两种方式的区别【面试题】★
1.delete 可以加where 条件,truncate不能加
2.truncate删除效率高
3.假如要删除的表中有自增长列,
如果用delete删除后,再插入数据,自增长列的值从断点开始,
而truncate删除后,再插入数据,自增长列的值从1开始。
4.truncate删除没有返回值,delete删除有返回值
5.truncate删除不能回滚,delete删除可以回滚.

四、DDL
1.库的管理
一、创建库
create database 【if not exists】 库名【 character set 字符集名】;
二、修改库
alter database 库名 character set 字符集名;
三、删除库
drop database 【if exists】 库名;

2.表的管理
一、创建表 ★
create table 【if not exists】 表名(
字段名 字段类型 【约束】,
字段名 字段类型 【约束】,
。。。
字段名 字段类型 【约束】

)
二、修改表
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
1.添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;
三、删除表
drop table【if exists】 表名;
show tables;

#通用的写法:
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
四、复制表
1、仅仅复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧库.旧表【where 筛选】;《仅仅复制部分数据或者仅仅复制某些字段都可以实现》《可以实现跨库复制》

3.数据类型
一、数值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特点:
①都可以设置无符号和有符号,默认有符号,通过unsigned设置无符号
②如果超出了范围,会报out or range异常,插入临界值
③长度可以不指定,默认会有一个长度,int默认是11
长度代表显示的最大宽度,如果不够则左边用0填充,但需要搭配zerofill,并且默认变为无符号整型
2、浮点型
定点数:dec(M,D) decimal(M,D)
浮点数:
float(M,D) 4
double(M,D) 8
特点:
①M代表整数部位+小数部位的个数,D代表小数部位
②如果超出范围,则报out or range异常,并且插入临界值
③M和D都可以省略,但对于定点数,M默认为10,D默认为0
如果是float和double,则会根据插入的数值的精度来决定精度
④如果精度要求较高,则优先考虑使用定点数
二、字符型
较短的文本:char、varchar、binary、varbinary、enum、set、
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本:text、blob(较大的二进制)
char:固定长度的字符,写法为char(M),最大长度不能超过M,其中M可以省略,默认为1。比较耗费空间、但效率高 存储性别
varchar:可变长度的字符,写法为varchar(M),最大长度不能超过M,其中M不可以省略。比较节省空间、效率低 存储姓名
三、日期型
year年
date日期
time时间
datetime 日期+时间 8 范围大、只能反映出插入时的地区时区
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响,更能反映当前时区的真实时间
改时区
SHOW VARIABLES LIKE ‘time_zone’;
SET time_zone=’+9:00’;

4.常见的约束
一、常见的约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
NOT NULL:非空,该字段的值不能为空,必填
UNIQUE:唯一,该字段的值不可重复,可以为空
DEFAULT:默认,该字段的值不用手动插入有默认值
CHECK:检查约束,mysql不支持
PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null
FOREIGN KEY:外键,该字段的值引用了另外的表的字段。用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束,用于引用主表中某列的值。
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束:
除了非空、默认,其他的都支持
主键和唯一
1、区别:
①、一个表至多有一个主键,但可以有多个唯一
②、主键不允许为空,唯一可以为空
2、相同点
①、都具有唯一性
②、都支持组合键,但不推荐(两个列组合成一个主键或者唯一键)
外键:
1、用于限制两个表的关系,从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致或兼容,意义一样,名称无要求
3、主表的被引用列要求是一个key(一般就是主键或者唯一键)
4、插入数据,先插入主表
删除数据,先删除从表
可以通过以下两种方式来删除主表的记录
#方式一:级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
#方式二:级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
二、创建表时添加约束
1.添加列级约束 直接在字段名和类型后面追加 约束类型即可。只支持:默认、非空、主键、唯一。因为外键加了也不起作用
2.添加表级约束 在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
create table 表名(
字段名 字段类型 not null,#非空
字段名 字段类型 primary key,#主键
字段名 字段类型 unique,#唯一
字段名 字段类型 default 值,#默认
constraint 约束名 foreign key(字段名) references 主表(被引用列)
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender =‘男’ OR gender = ‘女’),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
)
注意:
支持类型 可以起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以,但对主键无效

列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求
#查看stuinfo中的所有索引,包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
三、修改表时添加或删除约束
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;
四、自增长列
标识列:又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1
auto_increment_increment
如果要更改起始值:手动插入值
如果要更改步长:更改系统变量
set auto_increment_increment=值;
SHOW VARIABLES LIKE ‘%auto_increment%’;
SET auto_increment_increment=3;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key。标识列必须和主键搭配吗?不一定,但要求是一个key
一、创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
)
二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment
三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束

五、TCL
一、含义
Transaction Control Language 事务控制语言
事务:一条或多条sql语句组成一个执行单位,一组sql语句要么都执行要么都不执行。如果某条语句一旦执行失败或者产生错误,整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态。
存储引擎innodb支持事务,myisam memory不支持事务
二、特点(ACID)
A 原子性:一个事务是不可再分割的整体,要么都执行要么都不执行
C 一致性:一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性:一个事务不受其他事务的干扰,多个事务互相隔离的
D 持久性:一个事务一旦提交了,则永久的持久化到本地
三、事务的使用步骤 ★
了解:
隐式(自动)事务:没有明显的开启和结束,本身就是一条事务可以自动提交,比如insert、update、delete
显式事务:具有明显的开启和结束。前提:必须先设置自动提交功能为禁用。
使用显式事务:
①开启事务
set autocommit=0;
start transaction;#可以省略
②编写一组逻辑sql语句
注意:sql语句支持的是insert、update、delete
设置回滚点:
savepoint 回滚点名;
③结束事务
提交:commit;
回滚:rollback;
回滚到指定的地方:rollback to 回滚点名;
总结:
set autocommit=0;
start transaction;
commit;
rollback;
savepoint 断点
commit to 断点
rollback to 断点
四、并发事务
1、事务的并发问题是如何发生的?
多个事务 同时 操作 同一个数据库的相同数据时
2、并发问题都有哪些?
脏读:一个事务读取了其他事务还没有提交的数据,读到的是其他事务“更新”的数据
不可重复读:一个事务多次读取,结果不一样
幻读:一个事务读取了其他事务还没有提交的数据,只是读到的是 其他事务“插入”的数据。幻读是针对于插入的!!
一个事务要对原有的2条记录进行修改,缓了一会没有执行,然后另一个事务插入了一条记录进行了提交;这时候第一个事务执行后发现自己改了3条记录。
3、如何解决并发问题
通过设置隔离级别来解决并发问题
通过设置事务的隔离级别
1、READ UNCOMMITTED允许事务读取未被其他事务提交的变更
2、READ COMMITTED 只允许事务读取已经被其他事务提交的变更 可以避免脏读
3、REPEATABLE READ 确保事务可以多次从一个字段中读取相同的值,这个事务持续期间,禁止其他事务对这个字段进行更新 可以避免脏读、不可重复读和一部分幻读
4、SERIALIZABLE 确保事务可以从一个表中读取相同的行,这个事务持续期间,禁止其他事务对该表进行插入、更新和删除操作,所有并发问题都可以避免,但性能低下 可以避免脏读、不可重复读和幻读

设置隔离级别:
set session|global transaction isolation level 隔离级别名;
查看当前隔离级别:
select @@tx_isolation;
4、隔离级别
脏读 不可重复读 幻读
read uncommitted:读未提交 × × ×
read committed:读已提交 √ × ×这个事务内只能读到别的事务已经提交了的
repeatable read:可重复读 √ √ ×这个事务内读到的都是一样的,除非这个事务结束再开一个,才能读到新的。
serializable:串行化 √ √ √第一个事务要进行修改还没有执行,这时候发现,第二个事务要进行插入操作就阻塞了!
mysql中默认是第三个隔离级别
truncate不支持回滚

六、视图、变量、存储过程和函数、流程控制结构
1.视图
一、含义
mysql5.1版本出现的新特性,本身是一个虚拟表,它的数据来自于表,通过执行时动态生成。
好处:
1、简化sql语句
2、提高了sql的重用性
3、保护基表的数据,提高了安全性
二、创建
create view 视图名
as
查询语句;
使用时就把视图当做表
三、修改
方式一:
create or replace view 视图名
as
查询语句;
方式二:
alter view 视图名
as
查询语句
四、删除
drop view 视图1,视图2,…;
五、查看
desc 视图名;
show create view 视图名;
六、使用
更新:
1.插入
insert
2.修改
update
3.删除
delete
4.查看
select
注意:视图一般用于查询的,而不是更新的,所以具备以下特点的视图都不允许更新
①包含以下关键字的sql语句:分组函数、group by、distinct、having、union、
②join
③对常量视图
④where后的子查询用到了from中的表
⑤from一个不可更新的视图
⑥Select中包含子查询
七、视图和表的对比
视图 create view 只是保存了sql逻辑 增删改查,只是一般不能增删改
表 create table 保存了数据 增删改查

	关键字		是否占用物理空间			使用

视图 view 占用较小,只保存sql逻辑 一般用于查询
表 table 保存实际的数据 增删改查

2.变量
分类
一、系统变量
说明:变量由系统提供的,不用自定义,属于服务器层面
注意:全局变量需要添加global关键字,会话变量需要添加session关键字,如果不写,默认会话级别
语法:
①查看系统变量
查看所有的系统变量
show 【global|session 】variables;
查看满足条件的部分系统变量
show 【global|session 】variables like ‘’; 如果没有显式声明global还是session,则默认是session
②查看指定的系统变量的值
select @@【global|session】.变量名; 如果没有显式声明global还是session,则默认是session
③为系统变量赋值
方式一:
set 【global|session 】 变量名=值; 如果没有显式声明global还是session,则默认是session
方式二:
set @@global.变量名=值;
set @@变量名=值;
1、全局变量
服务器层面上的,必须拥有super权限才能为系统变量赋值,作用域为整个服务器,也就是针对于所有连接(会话)有效
作用域:针对于所有会话(连接)有效,但不能跨重启
2、会话变量
服务器为每一个连接的客户端都提供了系统变量,作用域为当前的连接(会话)
作用域:针对于当前会话(连接)有效

二、自定义变量
说明:变量由用户自定义,而不是系统提供的
使用步骤:
1、声明
2、赋值
3、使用(查看、比较、运算等)
1、用户变量
作用域:针对于当前连接(会话)生效,作用域同于会话变量
位置:begin end里面,也可以放在外面
使用:
①声明并初始化:赋值操作符:=或:=
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;
②更新值
方式一:
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;
方式二:
select xx into @变量名 from 表;
③使用(查看变量的值)
select @变量名;
2、局部变量
作用域:仅仅在定义它的begin end中有效
位置:只能放在begin end中,而且只能放在第一句
使用:
①声明
declare 变量名 类型;
declare 变量名 类型 【default 值】;
②赋值或更新
方式一:
set 变量名=值;或
set 变量名:=值;或
select @变量名:=值;
方式二:
select xx into 变量名 from 表;
③使用
select 变量名;

#案例:声明两个变量,求和并打印
#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
用户变量和局部变量的对比
作用域 定义位置 语法
用户变量 当前会话 会话的任何地方 加@符号,不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

3.存储过程
说明:都类似于java中的方法,将一组完成特定功能的逻辑语句包装起来,对外暴露名字
好处:
1、提高重用性
2、sql语句简单
3、减少了和数据库服务器连接的次数,提高了效率

存储过程和函数:类似于java中的方法
好处:
1、提高代码的重用性
2、简化操作

存储过程
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
分类:
1、无返回无参
2、仅仅带in类型,无返回有参
3、仅仅带out类型,有返回无参
4、既带in又带out,有返回有参
5、带inout,有返回有参
注意:in、out、inout都可以在一个存储过程中带多个
一、创建 ★
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
存储过程体
end
类似于方法:
修饰符 返回类型 方法名(参数类型 参数名,…){
方法体;
}
注意:
1、参数列表包含三部分
参数模式 参数名 参数类型
举例:
in stuname varchar(20)
参数模式:in、out、inout,其中in可以省略
in:该参数可以作为输入,也就是该参数需要调用方传入值
out:该参数可以作为输出,也就是该参数可以作为返回值
inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
2、如果存储过程体仅仅只有一句话,begin end可以省略
3、存储过程体中的每条sql语句的结尾要求必须加分号。
4、存储过程的结尾可以使用 delimiter 重新设置
语法:
delimiter 结束标记
案例:
delimiter $
总结:
delimiter $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,IN|OUT|INOUT 参数名 参数类型,…)
BEGIN
sql语句1;
sql语句2;
END $
二、调用
call 存储过程名(实参列表)
举例:
空参列表: call spl() ; 调 用 i n 模 式 的 参 数 : c a l l s p 1 ( ‘ 值 ’ ) ; 调用in模式的参数:call sp1(‘值’) ;incallsp1;
调用out模式的参数:
set @name ; c a l l s p 1 ( @ n a m e ) ; call sp1(@name) ;callsp1(@name);
select @name$;
调用inout模式的参数:
set @name=值;
call sp1(@name);
select @name;
案例 :
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化

SELECT COUNT(*) INTO result#赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;

SELECT IF(result>0,'成功','失败');#使用

END $

CALL myp3(‘张飞’,‘8888’)$

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;

END $

CALL myp7(‘小昭’,@name,@cp)$
SELECT @name,@cp$

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a2;#局部变量的赋值
SET b=b
2;
END $

SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
三、查看
show create procedure 存储过程名;
四、删除
drop procedure 存储过程名;
查看存储过程的信息
DESC myp2;×
SHOW CREATE PROCEDURE myp2;

4.函数
函数
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1 个返回,适合做处理数据后返回一个结果
一、创建
create function 函数名(参数名 参数类型) returns 返回类型
begin
函数体
end
1.参数列表 包含两部分:
参数名 参数类型
2.函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
return 值;
3.函数体中仅有一句话,则可以省略begin end
4.使用 delimiter语句设置结束标记
二、调用
select 函数名(实参列表);
1.无参有返回
2.有参有返回
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_fun1(1,2)$
三、查看
show create function 函数名;
四、删除
drop function 函数名;

5.流程控制结构
说明:
顺序结构:程序从上往下依次执行
分支结构:程序按条件进行选择执行,从两条或多条路径中选择一条执行
循环结构:程序满足一定条件下,重复执行一组语句

分支结构
特点:
1、if函数
功能:实现简单双分支
语法:if(条件,值1,值2)
如果条件成立,则函数返回值1,否则返回值2
位置:可以作为表达式放在任何位置
2、case结构
功能:实现多分支
语法1:-类似于java中的switch,一般用于实现等值判断
case 表达式或字段
when 值1 then 语句1;
when 值2 then 语句2;

else 语句n;
end [case];
语法2:类似于java中的多重if语句,一般用于实现区间判断
case
when 条件1 then 语句1;
when 条件2 then 语句2;

else 语句n;
end [case];
位置:
可以放在任何位置,
如果放在begin end 外面,作为表达式结合着其他语句使用
如果放在begin end 里面,一般作为独立的语句使用
3、if elseif 结构
功能:实现多分支
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;

else 语句n;
end if;
位置:
只能放在begin end中
比较:
应用场合
if函数 简单双分支
case结构 等值判断 的多分支
if结构 区间判断 的多分支

循环结构
位置:
只能放在begin end中
特点:都能实现循环结构
对比:
①这三种循环都可以省略名称,但如果循环中添加了循环控制语句(leave或iterate)则必须添加名称
②loop 一般用于实现简单的死循环
while 先判断后执行
repeat 先执行后判断,无条件至少执行一次
1、while先判断后执行
语法:
【名称:】while 循环条件 do
循环体
end while 【名称】;
2、loop先执行后判断
语法:
【名称:】loop
循环体
until end_condition
end loop 【名称】;
可以用来模拟简单的死循环
3、repeat没有条件的死循环
语法:
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;
二、循环控制语句
leave:类似于break,用于跳出所在的循环
iterate:类似于continue,用于结束本次循环,继续下一次

MySQL高级
一、MySql的架构介绍二、索引优化分析三、查询截取分析四、MySql锁机制五、主从复制

一、MySql的架构介绍
1.MySQL简介
概述:MySQL是一个关系型数据库管理系统。开源的、支持大型的数据库,可以处理拥有上千万条记录的大型数据库。使用标准的SQL数据语言形式。支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
2.高级MySQL:(1.MySQL内核2.sql优化工程师3.MySQL服务器的优化4.各种参数常量设定5查询语句优化6.主从复制7.软硬件升级8.容灾备份9.sql编程)
数据库内部结构和原理、数据库建模优化、数据库索引建立、SQL语句优化、SQL编程(自定义函数、存储过程、触发器、定时器)、MYSQL服务器的安装配置、数据库的性能监控分析与系统优化、各种参数常量设定、主从复制、分布式架构搭建垂直切割和水平切割、数据迁移、容灾备份和恢复、shell和python等脚本语言开发
3.MySQL Linux版的安装
二进制源码安装yum+RPM安装两种方式
RPM mysql5.5下载地址:http://dev.mysql.com/downloads/mysql/
第三方软件包放到opt目录下
MySQL-server-5.5.48-1.linux2.6.1386.rpm、MySQL-client-5.5.48-1.linux2.6.1386.rpm
检查当前系统是否安装过MySQL:
rpm -qa|grep -i mysql
已经装过,删除命令rpm –e RPM软件包名
没有装过,则安装
安装MySQL服务端(注意提示):
ivh分别显示:安装速度、日志、进度条
安装MySQL服务端:rpm -ivh MySQL-server-5.5.48-1.linux2.6.1386.rpm
提示已经为你创建了root用户,需要你自己添加密码
安装MySQL客户端:rpm -ivh MySQL-client-5.5.48-1.linux2.6.1386.rpm
查看MySQL安装时创建的MySQL用户和MySQL组:cat /etc/passwd|grep mysql cat /etc/group|grep mysql或者可以执行mysqladmin –version,命令,打出消息即为成功。
4.MySQL服务的启动和停止:
如何查看系统中是否启动了MySQL:ps -ef|grep mysql
service mysql start service mysql stop
MySQL服务启动后,开始连接:
首次连接成功:mysql
注意这里,因为MySQL默认没有密码,所以这里我们没有输入密码就直接连上了
按照安装server中的提示修改登陆密码
/usr/bin/mysqladmin -u root password 123456
mysql
mysql -uroot -p

5.自启动MySQL服务:
chkconfig mysql on设置开机自启动mysql
chkconfig -list|grep mysql
ntsysv 看到[*]mysql这一行,表示开机后会自动启动mysql

6.修改配置文件位置:
拷贝
cd /usr/share/mysql
cp my-huge.cnf /etc/my.cnf

7.修改字符集和数据存储路径:
1.查看字符集
(show databases;
create database db01;
use db01;
show tables;
create table user(id int not null,name varchar(20));
show tables;
insert into user values(1,“z3”);
select * from user;
insert into user values(2,“张三”);
select * from user; )
show variables like ‘character%’
show variables like ‘%char%’
默认的是客户端和服务器都用了latin1,所以会乱码
2.修改
[client]
#password=your_password
Port=3306
Socket=/var/lib/mysql/mysql.sock
Default-character-set=utf8
#The MySQL server
[mysqld]
Port=3306
Character_set_server=utf8
Character_set_client=utf8
Collation-server=utf8_general_ci
Socket=/var/lib/mysql/mysql.sock

[mysql]
Default-character-set=utf8

cd /etc
vim my.cnf
:set nu
o在光标的下一行输入
esc
:wq!
3.重启mysql
service mysql stop
service mysql start
mysql -u -root -p
use db01;
select * from user;
原来的库还是不行
4.重新连接后再重新建一个库,然后再重新建表试试
create database db02;
use db02;
create table user(id int not null,name varchar(20));
show tables;
insert into user values(1,“z3”);
select * from user;
insert into user values(2,“张三”);
select * from user;

8.MySQL的安装位置:
在linux下查看安装目录:ps -ef|grep mysql
路径 解释 备注
/var/lib/mysql mysql数据库文件的存储位置 /var/lub/mysql/…
/usr/share/mysql 配置文件目录 mysql.server命令及配置文件
/usr/bin 相关命令目录 mysqladmin mysqldump等命令
/etc/init.d/mysql 启停相关脚本

9.MySQL配置文件
主要配置文件:
1.二进制日志log-bin:用于主从复制
2.错误日志log-error:默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息
3.查询日志log:默认关闭,记录查询的sql语句,如果开启会减低mysql的性能,
4.数据文件:windows:D;\devSoft\MySQLServer5.5\data目录下可以挑选很多库
linux:看看当前系统的全部库后再进去ls -lF|grep ^d,默认路径:/var/lib/mysql
5.frm文件:存放表结构
6.myd文件:存放表数据
7.myi文件:存放表索引
如何配置:
windows:my.ini
linux:/etc/my.cnf

10.MySQL逻辑架构介绍
总体概览:
和其他数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥作用。主要体现在存储引擎的架构上。插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
mysql的架构从上到下分成四层:
1)、连接层:连接处理,授权认证,相关的安全方案。
2)、服务层:完成sql的优化,分析,缓存等功能。
3)、引擎层:负责存储和提取数据。
4)、存储层:数据存储层,主要将数据存储在文件系统之中,并完成与引擎的交互。
1.连接层
最上层是一些客户端和连接服务,包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接代理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2.服务层
第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化以及部分内置函数的执行。所有跨库存储引擎的功能也在这一层实现,如过程、函数等。在该层,服务器会解析查询并创建响应的内部解析树,并对其完成响应的优化如确定查询表的顺序,是否利用索引等,最后生成响应的执行操作。如果是select语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读操作的环境中能够很好地提升系统的性能。
3.引擎层
存储引擎层,存储引擎真正的负责了MYSQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。
4.存储层
数据存储层,主要将数据存储在运行于裸机设备的文件系统之上,并完成与存储引擎的交互。

11.MySQL存储引擎
查看命令:如何用命令查看你的mysql现在已提供什么存储引擎
show engines;
看你的mysql当前默认的存储引擎
show variables like ‘%storage_engine%’;

12.MYISAM和INNODB的比较
不支持主外键值对;支持
不支持事务;支持
表锁,不适合高并发;行锁,适合高并发
只缓存索引,不缓存真实数据;缓存索引和数据,对内存要求更高
表空间小;表空间大
关注点在性能,更多情况下适合查询多的情况;关注点事务。
InnDB:支持主外键;支持事务;行锁,操作时只锁某一行,不对其他行有不影响,适合高并发的操作;不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响;表空间大;关注事务。

13.阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。AliSql+AliRedis

二、索引优化分析
1.性能下架:SQL慢、执行时间长、等待时间长
1)查询语句写的烂:
2)索引失效:
单值索引-create index idx_user_name on user(name)
复合索引-create index idx_user_nameEmail on user(name,email)
3)关联查询太多join:设计缺陷或不得已的需求
4)服务器调优及各个参数设置:缓冲、线程数等

2.常见的通用join查询
SQL执行顺序:
手写:
select distinct <select_list>
from <left_table> <join_type>join<right_table> on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
order by <order_by_condition>
limit
机读:从from读的
from <left_table> <join_type>join<right_table> on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
select distinct <select_list>
order by <order_by_condition>
limit
1)、内连接
select *from tableA A inner join tableB B on A.key=B.key
2)、左外连接
select *from tableA A left join tableB B on A.key= B.key
当b没有的,用null补全。
3)、右外连接
select *from tableA A right join tableB B on A.key=B.key
当a没有时,用null补全
4)、左外连接,去除右表部分
select *from tableA A left join tableB B on A.key=B.key where B.key is null
a的独有,这此时b的用null补齐,所以此时b是为空的。
5)、右外连接,去除左表部分
select *from tableA A right join tableB B on A.key = B.key where A.key is null
6)、全连接(可以采用两者相加)union 可以组合并去重
select *from tableA A left join tableB B on A.key=B.key union
(select *from tableA A right join tableB B )
7)、中心空出来
select *from tableA A left join tableB B on A.key=B.key where B.key is null union
(select *from tableA A right join tableB B on A.key=B.key where A.key is null)

建表sql:
create database db0629;
engine=innodb auto_increment default charset=“utf-8”
7种sql:
select * from tbl_emp;
select * from tbl_dept;
select * from tbl_emp a inner join tbl_dept b on a.deptId=b.id;
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id;
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id=null;#a独有的
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId=null;#b独有的
select * from tbl_emp a full outer join tbl_dept b on a.deptId=b.id;#mysql不支持外连接
左连接a独有、右连接b独有、公有
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id;
union
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;
a\b各自的独有
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id=null;#a独有的
union
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId=null;#b独有的
3.索引简介
是什么:
1.MySQL官方对索引的定义:索引是帮助MySQL高效获取数据的数据。可以得到索引的本质:索引是数据结构。索引的目的在于提高查询效率,可以类比字典。
2.可以简单理解为“排好序的快速查找数据结构”。在数据本身之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
排序+查找两大功能,解决where后查的快,解决order by后排序快
3.一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。
4.我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引,还有哈希索引。

优势:类似大学图书馆建立书目索引,提高数据检索的效率,降低数据库的IO成本。通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗。

劣势:实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。虽然索引大大提高了查询速度,同时却会降低更新表的速度,如:对表进行insert update delete因为更新表时,不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。索引只是提高效率的一个因素,如果有大量数据的表,就需要花时间研究建立最优化的的索引,或者优化查询。
4.mysql索引分类:
1)单值索引:即一个索引只包含单个列,一个表中可以有多个单列索引。
create table customer(
id int(10) unsigned auto_increment,
customer_no varchar(200),
customer_name varchar(200),
key(customer_name)
);
create index idx_customer_name on customer(customer_name);
drop index idx_customer_name on customer;
2)唯一索引:索引列的值必须唯一,但允许有空值。
create table customer(
id int(10) unsigned auto_increment,
customer_no varchar(200),
customer_name varchar(200),
key(customer_name),
unique(customer_no)
);
create unique index idx_customer_no on customer(customer_no);
drop index idx_customer_no on customer;

3)主键索引:设定为主键后数据库会自动建立索引,innodb为聚簇索引
create table customer(
id int(10) unsigned auto_increment,
customer_no varchar(200),
customer_name varchar(200),
key(customer_name),
primary key(id)
);
alter table customer add primary key customer(customer_no);
alter table customer drop primary key;

4)复合索引:一个索引包含了多个列。
create table customer(
id int(10) unsigned auto_increment,
customer_no varchar(200),
customer_name varchar(200),
key(customer_name),
key(customer_no,customer_name)
);
create index idx_no_name on customer(customer_no,customer_name);
drop index idx_no_name on customer;

5.基本语法:
创建:create [unique] index indexname on mytable(columnname(length));
alter mytable add [unique] index [indexname] on (columnname(length));
删除:drop index [indexname] on mytable;
查看:show index from table_name\G

使用alter命令:有四种方式来添加数据表的索引
alter table tbl_name add primary key(column_list):该语句添加了一个主键,这意味着索引值必须是唯一的,且不能为null
alter table tbl_name add unique index_name(column_list):这条语句创建索引的值必须是唯一的,除了null之外,null可能会出现多次。
alter table tbl_name add index index_name(column_list):添加普通索引,索引值可出现多次。
alter table tbl_name add fulltext index_name(column_list):该语句指定了索引为fulltext,属于全文索引。

6.mysql索引结构:
btree索引:检索原理:
b+tree索引:
btree与b+tree的区别:
btree:key 指向下个节点的指针 指向数据块的指针
b+tree:key 指向下个节点的指针
1) btree的关键字和记录是放在一起的,叶子节点可以看做外部节点,不包含任何信息;
b+tree的非叶子节点中只有关键字和指向下一个节点的索引,(不存储真实的数据,只存储指引搜索方向的数据项)记录值放在叶子节点中。
2) btree中,越靠近根节点的记录查找时间越快,只要找到关键字即可确定记录的存在;
b+tree中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。
b+tree的非叶子节点中不存放实际的数据,这样每个节点可以容纳的元素个数比btree多,树高比btree小,这样带来的好处是减少磁盘访问次数。
尽管b+tree找到一个记录所需的比较次数要比btree多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中b+tree的性能可能还会好些,而且b+tree的叶子节点使用指针链接在一起,方便顺序遍历,这也是很多数据库和文件系统使用b+tree的原因。

为什么说b+tree比btree更适合实际应用中操作系统的文件索引和数据库索引?

  1. b+tree的磁盘读写代价更低。b+tree的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对于btree更小。如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

hash索引:
聚簇索引与非聚簇索引:聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。聚簇表示数据行和相邻的键值进错的存储在一起。聚簇索引:数据行在磁盘的排列和索引排序保持一致。
聚簇索引的好处: 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量IO操作。
限制:对mysql数据库目前只有innodb数据引擎支持聚簇索引,myisam并不支持聚簇索引。由于数据物理存储排列方式只能有一种,所以每个mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的如uuid这种。

fulltext全文索引:也称为全文检索,是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。Fulltext key ‘title’ (‘title’,’contene’)
不同于like的方式的查询select * from article where content like ‘%查询字符串%’;
全文索引用match+against方式查询select * from article where match(title,content) against(‘查询字符串’)明显的提高查询效率。
限制:mysql5.6.4以前只有myisam支持,5.6.4版本以后innodb才支持,但是官方版本不支持中文分词,需要第三方分词插件。5.7以后官方支持中文分词。
随着大数据时代的到来,关系型数据库应对全文索引的需求已经力不从心,逐渐被solr、elasticSearch等专门的搜索引擎所代替。

r-tree索引:

7.哪些情况需要创建索引:
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段,外键关系建立索引
4.频繁更新的字段不适合创建索引-因为每次更新不单单是更新了记录还会更新索引
5.where条件中用不到的字段不创建索引
6.单键/组合索引的选择问题?在高并发下倾向创建组合索引。
7.查询中排序的字段,排序字段若通过索引去访问大大提高排序速度。
8.查询中统计或者分组字段。

8.哪些情况不要创建索引:
1.表记录太少
2.经常增删改的表(提高了查询速度却会同时降低更新表的速度,不仅要保存数据还需要保存一下索引文件)
3.数据重复且分布平均的字段,因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
4.where条件里用不到的字段不创建索引
5.过滤性不好的不适合建立索引
假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建立索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99.一个索引的选择性越接近于1,这个索引的效率就越高。

9.性能分析
MySQL Query Optimizer:
1.MySQL 中有专门负责优化select语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的query提供他认为最优的执行计划(它认为最优的数据检索方式,但不见得是DBA认为最优的,这部分最耗费时间)。
2.当客户端向MySQL 请求一条query,命令解析器模块完成请求分类,区别出是select并转发给MySQL Query Optimizer时,MySQL Query Optimizer首先会对整条query进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对query中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、结构调整等。然后分析query中的hint信息如果有,看显示hint信息是否可以完全确定该query的执行计划。如果没有hint或hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据query进行写相应的计算分析,然后再得出最后的执行计划。

10.MySQL的常见瓶颈:
CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO:磁盘I/O瓶颈发生在装入数据大于内存容量的时候
服务器硬件的性能瓶颈:top,free,iostst,vmstst来查看系统的性能状态

11.Explain:
是什么:查看执行计划。使用Explain关键字你可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
官网介绍:
能干嘛:
1.表的读取顺序
2.数据读取操作的操作类型
3.哪些索引可以使用
4.哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询
怎么玩:
Explain+SQL语句
执行计划包含的信息:id select_type table type possible_keys key key_len ref rows extra
各字段解释:
12.id select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
三种情况:
id相同,执行顺序从上到下。
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
id相同不同,同时存在。id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行。衍生=derived。
关注点:id号每个号码,表示一趟独立的查询。一个sql的查询趟数越少越好。

13.select_type 常用值:simple 、primary、 subquery、 derived、 union、 union result查询的类别,主要是用于区分普通查询、联合查询、子查询等的复杂查询。
simple 、简单的select查询,查询中不包含子查询或者union
primary、 查询中若包含任何复杂的子部分,最外层查询则被标记为。最外层的!鸡蛋壳!最后加载的那个
subquery、在select或where列表中包含了子查询
derived、 在from列表中包含的子查询被标记为。mysql会递归执行这些子查询,把结果放在临时表里。
union、 若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为deriveds。
union result、从union表中获取结果的select
14.table 显示这一行的数据是关于哪张表的
15.type 八种值,all、index、range、ref、eq_ref、const、system、null
访问类型排列,type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
一般来说,得保证查询至少达到range,最好达到ref。
显示查询使用了何种类型,从最好到最差依次是:system>const>eq_ref>ref>range>index>all
system>表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计
const>表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如:将主键置于where列表中,mysql就能将该查询转换成一个常量。

eq_ref>唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。

ref>非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。

range>只检索给定范围的行,使用一个索引来选择行。
key列显示使用了哪个索引。一般就是你的where语句中出现了between < > in 等的查询。这种范围扫描索引扫描比全表扫描好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。

index>full index scan,index与all的区别为:index类型只遍历索引树。这通常比all快,因为索引文件通常比数据文件小。也就是说虽然all和index都是读全表,但是index是从索引中读取的,而all是从硬盘中读取的。
出现index是sql使用了索引但是没用通过索引进行过滤你,一般是使用了覆盖索引或者是利用索引进行

all>full table scan,将遍历全表以找到匹配的行

index_merge在查询过程中需要多个索引组合使用,通常出现在有or的关键字的sql中

ref_or_null对于某个字段即需要关联条件,也需要null值的情况下。查询优化器会选择用它连接

index_subquery利用索引来关联子查询,不再全表扫描

unique_subquery该连接类型类似于index_subquery。子查询中的唯一索引
备注:一般来说,得保证查询至少达到range级别,最好能达到ref。

16.possible_keys 显示可能应用在这张表的索引,一个或多个。查询涉及到的字段上若存在索引,则将该索引将被列出,但不一定被查询实际使用。

17.key 实际使用的索引。如果为null,则没有使用索引。
查询中若使用了覆盖索引,则该索引仅出现在key列表中,不会出现在posssible_keys中。
Create index idx_col1_col2 on t2(col1,col2);
Explain select col1,col2 from t1;
18.覆盖索引:select后面的字段刚好和建的索引的字段个数顺序一样
19.key_len 表示索引中使用的字节数,可以通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。
显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
20.ref 显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
查询中与其他表关联的字段,外键关系建立索引
21.rows 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
22.extra 包含不适合在其他列中显示但十分重要的额外信息
1.using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作成为文件排序。
2.using temporary 使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
3.using index表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错!如果同时出现using where表明索引被用来执行索引键值的查找;如果没有同时出现using where表明索引用来读取数据而非执行查找动作。
覆盖索引:索引覆盖。
理解方式一:就是select的数据列只用从索引中就能够取得,不必读取数据行,mysql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解方式二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
注意:如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select*,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。
4.using where 表明使用了where过滤
5.using join buffer 使用了连接缓存
6.impossible where where子句的值总是false,不能用来获取任何元组
7.select tables optimized away 在没有groupby子句的情况下,基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化
8.distinct 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

23.热身case:
索引优化,索引分析:
1)单表(建表SQL、案例)
Create table if not exists ‘article’(
‘id’ int(10) unsigned not null primary key auto_increment,
‘author_id’ int(10) unsigned not null,
‘category_id’ int(10) unsigned not null,
‘views’ int(10) unsigned not null,
‘comments’ int(10) unsigned not null,
‘title’ varbinary(255) not null,
‘content’ text not null
);
Insert into article(author_id,category_id,views,comments,title,content) values(1,1,1,1,’1’,’1’,)
Explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;
Type=all,extra=using filesort没有索引,所以查询效率不高。
对ccv建立了索引,create index idx_article_ccv on article(category_id,comments,views);
Type=range,extra=using filesort.我们已经建立了索引为什么没用呢?这是因为按照btree索引的工作原理,先排序category_id,如果遇到相同的category_id则再排序comments,如果遇到相同的comments则再排序views。当comments字段在联合索引里处于中间位置时,因为comments>1条件是一个范围值,mysql无法利用索引再对后面的views部分进行检索,即range类型查询字段后面的索引无效。
但是出现了filesort,所以索引不合适,删掉drop index idx_article_ccv on article;
建立cv,create index idx_article_cv on article(category_id ,views);
Type=ref,using filesort消失。
2)两表
Create table if not exists calss(
Id int(10) unsigned not null auto_increment,
Card int(10) unsigned not null,
Primary key(‘id’)
);
Create table if not exists book(
bookid int(10) unsigned not null auto_increment,
Card int(10) unsigned not null,
Primary key(‘bookid’)
);
Explain select * from class left join book on class.card=book.card;
Typr=all
添加索引优化,alter table book add index y(card);
Explain,type=ref ,rows也优化了。这是由左连接特性决定的,left join条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。
删除旧索引,drop index y on book;
建立新索引,alter table class add index x(card);
expalin
所以左连接还是给右边添加索引效果比较好,我们再改回去
所以说我们可以通过交换表的位置来,而不是非要改索引。
再来看一个右连接,所以右连接应该建在左表。
3) 三表
Create table if not exists phone(
Phoneid int(10)unsigned not null auto_increment,
Card int(10) unsigned not null,
Primary key(phoneid),
);
Alter table phone add index z(card)
Alter table book add index y(card)
Explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card
Type=ref rows优化不错。使用了连接缓存
因此索引最好设置在需要经常查询的字段中。
结论:
Join语句的优化
尽可能减少join语句中的nestedloop的循环总次数“永远用小结果集驱动大的结果集”。优先优化nestedloop的内层循环;保证join语句中被驱动表上join条件字段已经被索引;当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝惜joinbuffer的设置。

实际的案例知识总结:
1)建立复合索引后A_B_C,B字段范围以后的索引会失效,此时C进行了orderby的操作。
如果不合理,则删除索引。这次直接建立A_C索引,可以起作用。
2)两表之间的索引问题
左外连接要将索引建立在右表中,右外连接要将索引建立在左表中。
3)三表之间的问题
跟2)中一样,但是要注意:永远用小表驱动大表
优先优化内层的循环。
保证join条件已被索引。

24.查询优化包括:批量数据脚本、使用索引、关联查询优化、子查询优化、oeder by关键字优化、group by关键字优化、索引的选择、最后使用索引的手段覆盖索引。

25.使用索引:索引失效(应该避免):不让索引失效就是优化索引
Create table staffs(
Id int primary key auto_increment,
Name varchar(24) not null default comment姓名,
Age int not null default 0 comment 年龄,
Pos carchar(20) not null default comment 职位,
Add_time timestamp not null default current_timestamp comment 入职时间
)charset utf8 comment 员工记录表;
Alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
1.全值匹配我最爱:建立索引
精度越来越高,代价也越来越大
name,age,position
如果索引没有name,只有单独的age、单独的pos,或者age position索引失效
2.最佳左前缀法则:如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左列开始并且不跳过索引中的列。
带头大哥不能死!一楼二楼三楼,一楼没了,二楼三楼没办法上了。第一个索引字段不能丢失。违背了,中间兄弟不能断!一楼二楼三楼,二楼没了。中间索引字段不能断。
3.不在索引列上做任何操作(计算、函数、(自动或手动)类型转换),会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
范围本身用到了!但是之后用不到了
一楼二楼、三楼用不到了!
范围之后全失效!
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*。
尽量使用覆盖索引,此时中间的某个索引如果使用范围的花化,type也为ref,key_len变小。(如果查询的字段被复合索引包含)
用什么取什么!extra中出现了using index比较好,说明使用了覆盖索引!
ref比range好
6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
7. is not null也无法使用索引,但是is null时可以使用索引的
8.like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
问题:解决like’%字符串%'时索引不被使用的方法
like 中如果开头为“%xxx",会导致索引失效,%放在右边较好,但是type应该为range,写like查询一般只在右边写%。
如果硬是要两边加%怎么办:使用覆盖索引来解决。
如果两边非要用!创建索引:使用覆盖索引!
要么全部吻合、要么半个沾边。
所以说非要用两边都用%的,可以使用覆盖索引:建的索引和查的字段个数、顺序上最好一致。
9.字符串不加单引号索引失效。varchar类型不能失去单引号。
因为发生了隐式的自动类型转换!
10.少用or用它连接时会索引失效
11. 建立了idx_abcd 则查询的时候,不管是a、b、c、d还是其他顺序,mysql会进行相关的优化。
select * from user where a=1 and b=1 and d=1 order by c(没有filesort)
(c也是用到了,但不是用于查询,而是用于排序,但没有统计到explain中)
select *from user where a=1 and b=1 order by d(这里排序是会出现file sort)
select *from user where a=1 and d=1 order by b,c(查询只用到一个索引,b、c用于排序,所以没有出现filesort)
select *from user where a=1 and d=1 order by c、b(出现了filesort)
select *from user where a=1 and b=1 and d=1 order by c、b(查询用到两个索引,order by c b=1),因为b=1是固定的,所以不用进行排序。
select *from user where a=1 and d=1 group by c、b(分组之前必排序)出现filesort temporary(临时表)
select *from user where a=1 and b=1 and c like “1%” and d 用到所有
12、小表驱动大表
1)跑一天,查看查询状况,开启满查询日志,抓取对应的sql语句。
2)explain + sql
3) show profile(查看执行周期和细节)
4)mysql数据库的参数调优。
select *from a where id in (select id from b)
当b表小于a表时,用in要优于exists
select *from a exists (select 1 from where b.id=a.id)
13、使用order by
select *from a where age >20 order by age
不会出现filesort情况
select *from a where age > 20 order by birth
会出现filesort情况
select *from a where age > 20 order by brith,age
会出现filesort的情况
select *from a where age >20 order by age asc,birth desc
filesort: 单路排序:一次磁盘的扫描(现在某认的选项)sort_buffer中,如果不够,效率反而比较低和两路排序:对磁盘有两次扫描
oderby中三大方案:
避免使用select *(容易把sort_buffer_size用满)
如果超过了,会产生临时文件自行合并,导致多次io
尝试提高sort_buffer_size
尝试提高max_length_sort_data
orderby :后面要么全部升,要么全部降
order b y a = const order b ,c
order by a=const and b>const order by b,c(可以使用)

group by 是先排序再分组,where高于having,能在where中完成的就不要在having中
14、慢查询日志
慢查询日志是mysql提供的一种日志记录,超过long_query_time会被记录到日志中。默认为10秒
默认情况下,是没有开启的,需要手动开启。查看:show variables like “%slow_query_log%”;
set global slow_query_log=1
show varables like “%slow_query%”
mysqldownslow是分析的一个工具。
15、批量插入
log_bin_trust_function_creator参数的开启
随即产生字符串的函数:
delimiter c r e a t e f u n c t i o n r a n d o m ( n i n t ) r e t u r n s v a r c h a r ( 255 ) b e g i n d e c l a r e c h a r s t r v a r c h a r ( 100 ) d e f a u l t ′ ′ c j h c j h c j h ; r e t u r n c h a r s t r ; e n d create function random(n int) returns varchar(255) begin declare char_str varchar(100) default &#x27;&#x27;cjhcjhcjh; return char_str; end createfunctionrandom(nint)returnsvarchar(255)begindeclarecharstrvarchar(100)defaultcjhcjhcjh;returncharstr;end
存储过程:
16、show profile
可以用来分析当前语句执行的资源消耗情况
show global varables like “”%profiling;
有三个属性:
query_id:查询的id
duration:持续的时间
query:查询的语句
show profile cpu,block io for query 3
对某条语句进行解析。如果出现:
converting heap to myisam:查询结果太大,内存不够
create temp table:创建临时表
copying temp to the disk:将临时表拷贝到硬盘中,危险
locked:出现锁的状况。
17、全局查询日志(只允许在测试环境中用)
show varables like “general_log”;
set general_log=1;
set global log_output=“table”:以表的形式
则会把查询语句存入到mysql数据库的general_log表中,系统自带的数据库。、
18、mysql的锁:行锁、表锁(读锁、写锁)
show open tables(查看是否加了锁)
上锁:lock table a read,b write
unlock tables;解锁所有的表
加了读锁的表的session不可以写自己,不可以操作别人。(加了锁要清帐,才可以取操作其他的)
在别的session,可以读被锁的表和其他表,不可以写被锁的表,此时会处于阻塞状态。
加了写锁后,不可以操作其他表,可以对锁的表进行读和写
在别的session中只能操作其他表,对写锁的表,都处于阻塞状态。
show status like “table%”
table_locks_wait:锁等待的次数。
myisam 比较适合读为主的项目,写锁太多会造成阻塞。
行锁:
支持更高的并发量
innodb:支持事务,支持行锁。
事务的隔离级别:
读未提交
读已提交
可重复读
串行化
show variables like “%tx_isolation”:
读己之所写,
强一致型(现实可能不太理想)c,高可用(a表示高可用)
某一行被update没有提交,本session可以看到更新;
其他session看不到更新,如果更新该行将会被阻塞。
当对应的session自动提交被禁止后,要想看到别的session的提交结果,要先commit下才可以看到
索引失效:会导致行锁变成表锁。
update user set username=100 where id=1;
则此时在另外一个session中该表会被锁主。
update tableA set name=2000(name为varchar类型,这样 会导致索引失效)
间隙锁:用范围的条件时,会对范围的记录上锁。尽管范围了没有某一条记录,也会加上锁。
会导致阻塞。
比如:update user set age=12 where id >1 and id <10
则另外一个session将无法对id为2到9的行进行操作,不管该行是否存在。
面试题目:锁定某一行
select * from user where id=8 for update;
show status like ‘innodb_row_lock%’;

26.小总结
带头大哥不能死!
中间兄弟不能断!永远要符合最佳左前缀原则!
索引列上无计算!
like%加右边!
范围之后全失效!
字符上面有引号!

例子:
Create index idx_tes03_c1234 on test03(c1,c2,c3,c4);
1.有查询优化器!所以1243 4321都会经过优化,所以它们也使用到了索引。
2.用到了c1c2c3。c3用于排序,勉强用了一部分。C3作用在排序而不是查找。
3.用到了c1c2。因为C3断了。
4.c2已经是常量了,所以不会出现filesort
5. 出现filesort,我们建立的索引是1234,他没有按照顺序来,32颠倒了
5.groupby分组之前必排序
定值、范围还是排序,一般order by是给个范围
group by基本上都需要进行排序,会有临时表产生

27.一般性建议:
1.对于单键索引,尽量选择针对当前query过滤性更好的索引
2.在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
3.在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引
4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

28.优化总结口诀:
全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分写最右,覆盖索引不写星
不等空值还有or,索引失效要少用
var引号不可丢,Sql高级也不难!

29.批量数据脚本

30.关联查询优化
建议;
1.保证被驱动表的join字段已经被索引
2.left join时,选择小表作为驱动表,大表作为被驱动表
3.inner join时,mysql会自己帮你把小结果集的表选为驱动表
4.子查询尽量不要放在被驱动表,有可能使用不到索引
5.能够直接多表关联的尽量直接关联,不用子查询。

31.子查询优化:尽量不要使用not in或者not exists。用left outer join on

32.order by关键字优化
尽可能在索引列上完成排序操作,遵照索引建的最佳最前缀。
Order by子句,尽量使用index方式排序,避免使用filesort方式排序。
如果不在索引列上,filesort有两种算法:mysql就要启动双路排序和单路排序。
双路排序:取一批数据,要对磁盘进行了两次扫描:
单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它。它的效率更快一些,避免了第二次读取数据。并且把随机,因为它把每一行都保存在内存中。
结论及引申出的问题:;由于单路是后出的,总体而言好过双路;但是用单路有问题。
优化策略:增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置、减少select后面的查询的字段、why。

33.group by关键字优化
Group by实质是先排序后进行分组,遵照索引建的最佳左前缀
当无法使用索引时,增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的
where高于having,能写在where限定的条件就不要去having限定了
group by使用索引的原则几乎跟order一致,唯一区别是group by即使没有过滤条件用到索引,也可以直接使用索引。

34.索引的选择:执行案例前先清除emp上的索引,只留主键。索引的选择。

35.最后使用索引的手段:覆盖索引。

三、查询截取分析
1.explain
1.观察,至少跑一天,看看生产的慢SQL情况
2.开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并将它抓取出来
3.explain+慢SQL分析
4.show profile
5.DBA进行SQL数据库服务器的参数调优

2.总结:
0.
1.慢查询的开启并捕获
2.explain+慢SQL分析
3.show profile查询SQL在mysql服务器里面的额执行细节和生命周期情况
4.SQL数据库服务器的参数调优

3.查询优化
优化原则:永远小表驱动大表,类似嵌套循环nested loop。即小的数据集驱动大的数据集。
select * from A where id in (select id from B)等价于for select id from B for select * from A where A.id=B.id当B表的数据集必须小于A表的数据集时,用in优于exists。
Select * from A where exists (select 1 from B where B.id=A.id)等价于for select * from A for select * from B.id=A.id当A表的数据集系小于B表的数据集,用exists优于in。
注意:A表与B表的ID字段应该建立索引。
Exists:select … from table where exists (subquery)该语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(true或者false)来决定主查询的数据结果是否得以保留。
提示:1.exists(subquery)只返回true或false,因此子查询中的select也可以是select也可以是select 1或其他,官方说法是实际执行时会忽略select清单,因此没有区别2.exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比,如果担忧效率问题,可进行实际检验以确定是否有效率问题。3.exists子查询往往也可以用条件表达式、其他子查询或者join来替代,何种最优需要具体问题具体分析。

4.order by关键字优化:
order by子句,尽量使用index方式排序,避免使用filesort方式排序
什么时候会产生fielsort什么时候不会产生filesort?
mysql支持两种方式的排序:filesort和index,index效率高,它指mysql扫描索引本身完成排序,filesort方式效率较低。

order by满足两情况,会使用index方式排序:order by语句使用索引最左前列;使用where子句和order by子句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作,遵照索引建立的最佳左前缀

如果不在索引列上,filesort有两种算法:mysql就要启动双路排序和单路排序
双路排序:mysql4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和orderby列,对它们进行排序,然后扫描已经排好序的列表,按照列表中的值重新从列表中读取对应的数据输出。

取一批数据,要对磁盘进行了两次扫描,众所周知,I|O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序。

单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了。

结论及引申出的问题:由于单路是后出的,总体而言好过双路;但是用单路有问题。在sort_buffer中,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取取sort_buffer容量大小,再排…从而多次I/O。本来想省一次I/O操作,反而导致了大量的I/O操作,反而得不偿失。

优化策略:
1.增大sort_buffer_size参数的设置
2.增大max_length_for sort_data参数的设置
3.why
提高order by的速度:
1.order by时select*是一个大忌,只query需要的字段,这点非常重要。在这里的影响是
1.1当query的字段大小总和小于max_length_for_sort_data而且排序字段不是text/blob类型时,会用改进后的算法–单路排序,否则用老算法----多路排序。
1.2两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
2.尝试提高sort_buffer_size
不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的
3.尝试提高max_length_for_sort_data
提高这个参数,会增加用改进算法的概率,但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。

小总结:
为排序使用索引
mysql两种排序方式:文件排序或扫描有序索引排序
mysql能为排序与查询使用相同的索引
key a_b_c(a,b,c)
orderby能使用索引最左前缀
如果where使用索引的最左前缀定义为常量,则order by能使用索引
不能使用索引进行排序

5.group by关键字优化:
group by实质是先排序后进行分组,遵照索引建的最佳左前缀
当无法使用索引列,增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having,能写在where限定的条件就不要去having限定了

6.慢查询日志
是什么:mysql的慢查询日志是mysql提供的一种日志记录,它用来记录在mysql中响应时间超过阈值的语句,具体是指运行时间超过long_query_time值的sql,则会被记录到慢查询日志中
具体值运行时间超过long_query_time值的sql,则会被记录到慢查询日志中。long_query_time值的默认值为10,意思是运行10秒以上的语句。
由他来查看哪些sql超出了我们的最大忍耐时间值,比如一条sql执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的sql,结合之前的explain进行全面分析。

怎么玩:
说明:默认情况下,mysql数据库没有开启慢查询日志,需要我们手动来设置这个参数。当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看是否开启及如何开启:默认show variables like ‘%show_query_log%’;开启set global slow_query_log=1;开启慢查询日志只对当前数据库生效,如果mysql重启后则会失效。

如果要永久生效,就必须修改配置文件my.cnf(其他系统变量也是如此)
修改配置文件my.cnf,[mysqld]下增加或修改参数
slow_query_log和slow_query_log_file后,然后重启mysql服务器。也即将如下两行配置进my.cnf文件。slow_query_log=1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log
关于慢查询的参数slow_query_log_file,它指定慢查询日志文件的存放路径,系统默认会给一个缺省的文件host_name-slow.log(如果没有指定参数slow_query_log_file的话)。

使用set global slow_query_log=1;开启慢查询日志只对当前数据库生效,如果mysql重启后则会失效。

那么开启了慢查询日志后,什么样的sql才会记录到慢查询日志里面呢?
这个是由long_query_time控制,默认情况下long_query_time的值为10秒
命令:show variables like '‘long_query_time%’
可以使用命令修改,也可以在my.cnf参数里面修改。
加入运行时间正好等于long_query_time的情况,并不会被记录下来。也就是说,在mysql源码里是判断大于long_query_time,而非大于等于。

case:
1.查看当前多少秒算慢show variables like '‘long_query_time%’
2.设置慢的阈值时间 set global long_query_time=3;
3.为什么设置后看不出变化?
需要重新连接或新开一个会话才能看到修改值。show variables like '‘long_query_time%’;show global variables like ‘long_query_time’;
4.记录慢sql并后续分析
5.查询当前系统中有多少条慢查询记录。
show global status like ‘%slow_queries’;

配置版:
mysql下配置
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE

7.日志分析工具mysqldumpslow:
在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,mysql提供了日志分析工具mysqldumpslow。
查看mysqldumpslow的帮助信息
s:是表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后面搭配一个正则匹配模式,大小写不敏感的

工作常用参考:
得到返回记录集最多的10个sql:mysqldumpslow –s r –t 10 /var/lib/mysql/atguigu-slow.log
得到访问次数最多的10个sql:mysqldumpslow –s c –t 10 /var/lib/mysql/atguigu-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句:mysqldumpslow –s t –t 10 –g “left join” /var/lib/mysql/atguigu-slow.log
另外建议在使用这些命令时结合|和more使用,否则有可能出现爆屏情况mysqldumpslow –s r –t 10 /var/lib/mysql/atguigu-slow.log|more

8.批量数据脚本
往表里插入1000W数据

  1. 建表
    Create database bigData;
    Use bigData;
    Create table dept(
    Id int unsigned primary key auto_increment,
    Deptno mediumint unsigned not null default 0,
    Dname varchar(20) not null default “”,
    Ioc varchar(13) not null default “”,
    );engine=innodb default charset=GBK

Create table emp(
Id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
ename varchar(20) not null default “”,
job varchar(9) not null default “”,
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm. Decimal(7,2) not null,
Deptno mediumint unsigned not null default 0,
);engine=innodb default charset=GBK
2. 设置参数log_bin_trust_function_creators
创建函数,假如报错:this function has none of deterministic
由于开启过慢查询日志,因为我们开启了bin-log,我们就必须为我们的fuction指定一个参数。
Show variables like ‘log_bin_trust_function_creators’;
Set global log_bin_trust_function_creators=1;
这样添加了参数以后,如果mysqld重启,上述参数又会消失,永久方法;
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

  1. 创建函数,保证每条数据都不同。随机产生字符串、随机产生部门编号。
    随机产生字符串
    Delimiter C r e a t e f u n c t i o n r a n d s t r i n g ( n i n t ) r e t u r n s v a r c h a r ( 255 ) B e g i n D e c l a r e c h a r s s t r v a r c h a r ( 100 ) d e f a u l t ‘ a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I G K L M N O P Q R S T U V W X Y Z ’ D e c l a r e r e t u r n s t r v a r c h a r ( 255 ) d e f a u l t ’ ’ ; D e c l a r e I i n t d e f a u l t 0 ; W h i l e i &lt; n d o S e t r e t u r n s t r = c o n c a t ( r e t u r n s t r , s u b s t r i n g ( c h a r s s t r , f l o o r ( 1 + r a n d ( ) ∗ 52 ) , 1 ) ) ; S e t i = i + 1 ; E n d w h i l e ; R e t u r n r e t u r n s t r ; E n d Create function rand_string(n int) returns varchar(255) Begin Declare chars_str varchar(100) default ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ’ Declare return_str varchar(255) default’’; Declare I int default 0; While i&lt;n do Set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1)); Set i=i+1; End while; Return return_str; End Createfunctionrandstring(nint)returnsvarchar(255)BeginDeclarecharsstrvarchar(100)defaultabcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZDeclarereturnstrvarchar(255)default;DeclareIintdefault0;Whilei<ndoSetreturnstr=concat(returnstr,substring(charsstr,floor(1+rand()52),1));Seti=i+1;Endwhile;Returnreturnstr;End
    随机产生部门编号
    Delimiter C r e a t e f u n c t i o n r a n d n u m ( ) R e t u r n i n t ( 5 ) B e g i n D e c l a r e I i n t d e f a u l t 0 ; S e t i = f l o o r ( 100 + r a n d ( ) ∗ 10 ) ; R e t u r n I ; E n d Create function rand_num() Return int(5) Begin Declare I int default 0; Set i=floor(100+rand()*10); Return I; End Createfunctionrandnum()Returnint(5)BeginDeclareIintdefault0;Seti=floor(100+rand()10);ReturnI;End

假如要删除drop function rand_num;
4. 创建存储过程。创建往emp表中插入数据的存储过程、创建往dept表中插入数据的存储过程。
Delimiter KaTeX parse error: Expected 'EOF', got '#' at position 98: …int default 0; #̲set autocommit=…

Delimiter KaTeX parse error: Expected 'EOF', got '#' at position 99: …int default 0; #̲set autocommit=…

5.调用存储过程。dept、emp。
Delimiter;
Call insert_dept(100,10);

往emp表添加50万条数据
Delimiter;
Call insert_emp(100001,500000);

9.show profile
是什么:是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql的调优的测量。
默认情况下,参数处于关闭状态,并保存最近15次的运行结果。

分析步骤:

  1. 是否支持,看看当前的mysql版本是否支持
    Show variables like ‘profiling’;默认是关闭,使用前需要开启
  2. 开启功能,默认是关闭,使用前需要开启
    set profiling =on;
    3.运行sql
    select * from emp group by id%10 limit 150000;
    select * from emp group by id%20 order by 5;
    4.查看结果show profiles;
    5.诊断sql,show profile cpu,block io for query 上一步的前面的问题sql数字号码;
    参数备注
    type:
    all 显示所有的开销信息
    block io 显示块io相关开销
    context switches 上下文切换相关开销
    cpu 显示CPU相关开销信息
    ipc 显示发送和接收相关开销信息
    memory 显示内存相关开销信息
    page faults 显示页面错误相关开销信息
    source 显示和sorce_function,source_file,source_ling相关的开销信息
    swaps 显示交换次数相关开销的信息
    6.日常开发需要注意的结论
    Converting help to myisam查询结果太大,内存都不够用了往磁盘上搬了
    Creating tmp table创建临时表,拷贝数据到临时表,用完再删除
    Copying to tmp table on disk把内存中临时表复制到磁盘,危险
    Locked

10.全局查询日志
只允许测试环境使用
配置启用
在mysql的my.cnf中,设置如下:
General_log=1
记录日志文件的路径
General_log_file=/path/logfile
输出格式
Log_output=file

编码启用
Set global general_log=1
Set global log_output=’tabel’;
此后你所编写的sql语句,将会记录到mysql库里的genetal+log表,可以用下面的额命令查看
Select * from mysql.general_log
永远不要在生产环境开启这个功能

四、MySql锁机制
1.数据库锁概述
定义:锁是计算机协调多个进程或线程并发访问某一资源的机制
在数据库中,除传统的计算资源(如:cpu ram i/o等)的争用之外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。

2.锁的分类:
从对数据操作的类型(读\写)分 :
读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁

从对数据操作的粒度分:表锁、行锁

三锁
3.表锁 (偏读)
特点:偏向myisam存储引擎,开销小、加锁快;无死锁;锁定粒度大、发生锁冲突的概率最高,并发度最低
Create table mylock(
Id int not null primary key auto_increment,
Name varchar(20)
)engine myisam;
手动增加表锁lock table 表名字 read(write),表名字2read(write),其他;
Lock table mylock read,book write;
查看表上加过的锁show open tables;
释放表锁unlock tables;

加读锁
Session_1
获得表mylock的读锁定lock table mylock read;
Session_2
连接终端
Session_1
当前session可以查询该表记录select * from mylock;
Session_2
其他session也可以查询该表的记录select * from mylock;
Session_1
当前session不能查询其他没有锁定的表select * from book会报错
Session_2
其他session可以查询或者更新未锁定的表update staffs set name=’z2’ where id=1;
Session_1
当前session中插入或者更新锁定的表都会提示错误insert into mylock(name) values(‘e’)报错
Update mylock set name=’k’ where id=1报错
Session_2
其他session插入或者更新锁定表会一直等待获得锁insert into mylock(name)values(‘e’)阻塞
Session_1
释放锁unlock tables;
Session_2
获得锁,插入操作完成insert into mylock(name)values(‘e’)完成

加写锁
Session_1
获得表mylock的write锁定lock tables mylock write
Session_2
待session1开启写锁后,session2再连接终端
Session_1
当前session对锁定表的查询、更新、插入操作都可以执行select * from mylock; Update mylock set name=’a2’ where id=1; insert into mylock(name) values(‘f’)
Session_2
其他session对锁定的表的查询被阻塞,需要等待锁被释放select * from mylock;阻塞
Session_1
释放锁unlock tables;
Session_2
获得锁,查询返回select * from mylock;完成

案例结论:
myisam在执行查询语句select前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。mysql的表级锁有两种模式:表共享读锁table read lock、表独占写锁table write lock。
对myisam表进行操作,会有以下情况:

  1. 对myisam表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
  2. 对myisam表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。
  3. 简而言之:读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞。

表锁分析
看看哪些表被加锁了:show open tables;
如何分析表锁定:可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定:sql:show status like ‘table%’;
这里有两个状态变量记录mysql内部表级锁定的情况,两个变量说明如下:
table_locks_ immediate产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1
table_locks_ waited出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着较严重的表级锁争用情况。

此外,myisam的读写锁调度是写优先,这也是myisam不适合做写为主表的引擎。因为写锁后,其他线程不能做任何操作,更新会使查询很难得到锁,从而造成永远阻塞。

4.行锁(偏写)
特点:偏向innoDB引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
innoDB与MyISAM的最大不同有两点:一是支持事务(transaction);二是采用了行级锁

由于行锁支持事务,复习老知识
事务及其ACID属性
事务是由一组sql语句组成的逻辑处理单元,事务有以下4种属性
Atomicity原子性:事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
Consistent一致性:在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如b树索引或者双向链表)也都必须是正确的。
Isolation隔离性:数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的独立环境执行。这意味着事务处理过程的中间状态对外部是不可见的。
Durable持久性:事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

并发事务处理带来的问题:更新丢失、脏读、不可重复读、幻读
更新丢失:当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。例如:两个程序员修改同一个java文件,每个程序员独立的更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。如果在一个程序员完成并提交事务之前,另一个程序员不能访问同一个文件,可以避免此问题。

脏读:一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致的状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些脏数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象的叫做脏读。一句话:事务A读取到了事务B已修改但尚未提交的数据,还在这个数据基础上做了操作,此时,如果B事务回滚,A读取的数据无效,不符合一致性要求。

不可重复读:一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或者某些记录已经被删除了,这种现象就叫做不可重复读。一句话:事务A读取到了事务B已经提交的修改数据,不符合隔离性。

幻读:一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据。一句话:事务A读取到了事务B提交的新增数据,不符合隔离性。幻读和脏读有点类似:脏读是事务B里面修改了数据,幻读是事务B里面新增了数据。

事务隔离级别:脏读、不可重复读、幻读,其实都是数据读一致性问题,必须由由数据库提供一定的事务隔离机制来解决。
读未提交:最低级别,只能保证不读取物理上损坏的数据
读已提交:语句级
可重复读:事务级
可序列化:最高级别,事务级

案例分析
建表sql
Create table test_innodb_lock(a int(11),b varchar(16))engine=innodb;
Create index test_innodb_a_ind on test_innodb_lock(a);
Create index test_innodb_lock_b_ind on test_innodb_lock(b);
单值索引
行锁定基本演示
Session_1:
Set autocommit=0;
Session_2:
Set autocommit=0;

Session_1:
更新但是不提交,没有手写commit;update test_innodb_lock set b=’b1’ where a=1;
Session_2:
被阻塞,只能等待。update test_innodb_lock set b=’b1’ where a=1;阻塞

Session_1:
提交更新commit;
Session_2:
接触阻塞,更新正常进行update test_innodb_lock set b=’b1’ where a=1;完成

Session_1:
Session_2:
Commit;

下面试试1号会话更新a=1,试试2号会话更新a=9。

无索引行锁升级为表锁

5.间隙锁危害
间隙锁带来的插入问题
Session_1:
Update test_innodb_lock set b=a*20 where a>1 and a<5;
Session_2:
阻塞产生,暂时不能插入insert into test_innodb_lock values(2,’200’);阻塞
Session_1:
Commit;
Session_2:
阻塞解除,完成插入insert into test_innodb_lock values(2,’200’);完成

什么是间隙锁:
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,innodb会给符合条件的已有数据记录的索引项加锁,对于键值在条件范围内但并不存在的记录,叫做间隙。
innodb也会对这个间隙加锁,这种锁机制就是所谓的间隙锁next-key锁。
宁可错杀,不可放过!

危害:
因为query执行过程中通过对范围查找的话,它会锁定整个范围内所有的索引键值,即使这个键值并不存在。间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

6.面试题:常考如何锁定一行?
Session_1:
Begin;
Select * from test_innodb_lock where a=8 for update;
Session_2:
Update test_innodb_lock set b=’xxx’ where a=8;阻塞
Session_1:
Commit;
Session_2:
Update test_innodb_lock set b=’xxx’ where a=8;完成
select xxx for update;锁定某一行后,其他操作会被阻塞,直到锁定行会话提交commit

7.案例结论
innodb存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远低于myisam的表级锁定的。当系统并发量较高时,innodb的整体性能和myisam相比就会有比较明显的优势了。
但是,innodb的行级锁定同样也有其脆弱的一面的,当我们使用不当的时候,可能会让innodb的整体性能表现不仅不能比myisam高,甚至可能会更差。

8.行锁分析
如何分析行锁定
通过检查innodb_row_lock状态变量来分析系统上的行锁的争夺情况show status like ‘innodb_row_lock%’;各个状态量的说明如下:
Innodb_row_lock_current_waits:当前正在等待锁定的数量
Innodb_row_lock_time:从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg:每次等待所花平均时间
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数
对于这五个状态变量,比较重要的主要是:
Innodb_row_lock_time_avg:等待平均时长
Innodb_row_lock_waits:等待总次数
Innodb_row_lock_time:等待总时长
尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手制定优化计划。

9.优化建议
尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁;
合理设计索引,尽量缩小锁的范围;
尽可能减少检索条件,避免间隙锁;
尽量控制事务大小,减少锁定资源量和时间长度;
尽可能低级别事务隔离。

10.页锁
开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般。

11.八个sql语句
CALL proc_drop_index (‘mydb’,‘emp’);
CALL proc_drop_index (‘mydb’,‘dept’);
1、列出自己的掌门比自己年龄小的人员

SELECT a.name,a.age ,c.name ceoname,c.age ceoage FROM t_emp a
LEFT OUTER JOIN t_dept b ON a.deptid=b.id
LEFT JOIN t_emp c ON b.ceo=c.id
WHERE a.age>c.age

##优化后
EXPLAIN SELECT SQL_NO_CACHE a.name,a.age ,c.name ceoname,c.age ceoage FROM emp a
LEFT OUTER JOIN dept b ON a.deptid=b.id
LEFT JOIN emp c ON b.ceo=c.id
WHERE a.age>c.age

TRUNCATE TABLE t_emp

DELETE FROM xxx

2、列出所有年龄低于自己门派平均年龄的人员

SELECT c.name ,c.age ,aa.age avgage FROM t_emp c
INNER JOIN
( SELECT a.deptid,AVG(age) age FROM t_emp a
WHERE a.deptid IS NOT NULL
GROUP BY a.deptid
)aa ON c.deptid=aa.deptid
WHERE c.age<aa.age

##优化后
EXPLAIN SELECT SQL_NO_CACHE c.name ,c.age ,aa.age avgage FROM emp c #1.3 0.17
INNER JOIN
( SELECT a.deptid,AVG(age) age FROM emp a
WHERE a.deptid IS NOT NULL
GROUP BY a.deptid
)aa ON c.deptid=aa.deptid
WHERE c.age<aa.age

CREATE INDEX idx_deptid ON emp(deptid)

CREATE INDEX idx_deptid_age ON emp(deptid,age)

3、列出至少有2个年龄大于40岁的成员的门派

SELECT b.deptname,COUNT() FROM t_emp a
INNER JOIN t_dept b ON a.deptid=b.id
WHERE a.age >40
GROUP BY b.deptname
HAVING COUNT()>=2

EXPLAIN SELECT SQL_NO_CACHE b.deptname,COUNT() FROM emp a #0.76
INNER JOIN dept b ON a.deptid=b.id
WHERE a.age >40
GROUP BY b.deptname
HAVING COUNT()>=2

##优化后
EXPLAIN SELECT SQL_NO_CACHE b.deptname,COUNT() FROM dept b #0.76 0.026
STRAIGHT_JOIN emp a ON a.deptid=b.id
WHERE a.age >40
GROUP BY b.deptname
HAVING COUNT()>=2

CREATE INDEX idx_deptid_age ON emp(deptid,age)

CREATE INDEX idx_deptname ON dept(deptname)

4、至少有2位非掌门人成员的门派

SELECT * FROM t_emp WHERE id NOT IN (
SELECT ceo FROM t_dept WHERE ceo IS NOT NULL
)

NOT IN -> LEFT JOIN … WHERE xxx IS NULL

SELECT c.deptname ,COUNT() FROM t_emp a
INNER JOIN t_dept c ON a.deptid=c.id
LEFT JOIN t_dept b ON a.id =b.ceo WHERE b.id IS NULL
AND a.deptid IS NOT NULL
GROUP BY c.deptname
HAVING COUNT()>=2

##优化后 #0.1
EXPLAIN SELECT SQL_NO_CACHE c.deptname ,COUNT() FROM dept c ##2.3 #0.1
STRAIGHT_JOIN emp a ON a.deptid=c.id
LEFT JOIN dept b ON a.id =b.ceo WHERE b.id IS NULL
GROUP BY c.id
HAVING COUNT()>=2

SELECT * FROM emp a INNER JOIN dept b ON a.deptid=b.id
GROUP BY a.deptid

CREATE INDEX idx_ceo ON dept(ceo);

CREATE INDEX idx_deptid ON emp(deptid);

CREATE INDEX idx_deptname ON dept(deptname);

EXPLAIN SELECT SQL_NO_CACHE a.deptname
FROM(SELECT b.deptid
FROM dept a
INNER JOIN emp b ON a.id = b.deptid
WHERE a.ceo != b.id
GROUP BY b.deptid
HAVING COUNT(*)>1
) c
INNER JOIN dept a ON a.id = c.deptId ;

EXPLAIN SELECT * FROM emp GROUP BY deptid ,NAME
1
5、列出全部人员,并增加一列备注“是否为掌门”,如果是掌门人显示是,不是掌门人显示否

SELECT a.name , (CASE WHEN b.id IS NULL THEN ‘否’ ELSE ‘是’ END) ‘是否为掌门’ FROM t_emp a LEFT JOIN t_dept b ON a.id=b.ceo

6、列出全部门派,并增加一列备注“老鸟or菜鸟”,若门派的平均值年龄>50 显示“老鸟”,否则显示“菜鸟”

SELECT b.deptname, IF( AVG(age)>50,‘老鸟’,‘菜鸟’) ‘老鸟or菜鸟’ FROM t_emp a INNER JOIN t_dept b ON a.deptid =b.id
GROUP BY b.deptname

7、显示每个门派年龄最大的人

SELECT NAME, MAX(age) FROM t_emp GROUP BY deptid 这个不对

UPDATE t_emp SET age=150 WHERE NAME =‘周芷若’

SELECT aa.name ,aa.age FROM t_emp aa
INNER JOIN
(
SELECT a.deptid, MAX(a.age) age FROM t_emp a
WHERE a.deptid IS NOT NULL
GROUP BY a.deptid
)ab ON aa.deptid=ab.deptid AND aa.age =ab.age

##优化后
EXPLAIN SELECT SQL_NO_CACHE aa.name ,aa.age FROM emp aa # 0.06
INNER JOIN
(
SELECT a.deptid, MAX(a.age) age FROM emp a
WHERE a.deptid IS NOT NULL
GROUP BY a.deptid
)ab ON aa.deptid=ab.deptid AND aa.age =ab.age

CREATE INDEX idx_deptid_age ON emp(deptid,age)

8、显示每个门派年龄第二大的人

##扩展性不好 ,需要取更多名次 无法实现
EXPLAIN SELECT SQL_NO_CACHE emp.name,dept.deptname
FROM (SELECT MAX(age) MAX,a.deptid
FROM (SELECT MAX(age) MAX,deptid FROM emp GROUP BY deptid) a
INNER JOIN emp b ON a.deptid = b.deptid
WHERE b.age !=a.max
GROUP BY a.deptid) d
INNER JOIN emp emp ON d.deptid = emp.deptid
INNER JOIN dept dept ON emp.deptid = dept.id
WHERE age = d.max;

#分组排名
oralce :rank()over()

#mysql

SET @rank=0;
SET @last_deptid=0;
SELECT NAME FROM (
SELECT
t.*,
IF (@last_deptid=deptid,@rank:=@rank+1,@rank:=1) AS rk,
@last_deptid:=deptid AS last_deptid
FROM emp t
ORDER BY deptid ,age DESC
) a WHERE a.rk=2;

SELECT a.name,a.age,a.ceoname newceo, c.name ceoname FROM t_emp a
LEFT OUTER JOIN t_dept b ON a.deptid=b.id
LEFT JOIN t_emp c ON b.ceo=c.id

SELECT a.name,a.age,a.ceoname FROM t_emp a

#冗余字段

SELECT a.name,a.age,a.ceoname newceo FROM t_emp a

UPDATE t_emp a
LEFT OUTER JOIN t_dept b ON a.deptid=b.id
LEFT JOIN t_emp c ON b.ceo=c.id
SET a.ceoname=c.name

UPDATE t_dept SET ceo=5 WHERE id=2

#跨表更新

ALTER TABLE t_emp ADD ceoname VARCHAR(200)

DELIMITER $$

##触发器更新冗余
CREATE TRIGGER trig_update_dept
AFTER UPDATE ON t_dept
FOR EACH ROW
BEGIN
UPDATE t_emp a
LEFT OUTER JOIN t_dept b ON a.deptid=b.id
LEFT JOIN t_emp c ON b.ceo=c.id
SET a.ceoname=c.name
WHERE a.deptid=NEW.id;
END $$

##不要自己触发自己
CREATE TRIGGER trig_update_dept
AFTER UPDATE ON t_emp
FOR EACH ROW
BEGIN
UPDATE t_emp a
LEFT OUTER JOIN t_dept b ON a.deptid=b.id
LEFT JOIN t_emp c ON b.ceo=c.id
SET a.ceoname=c.name
WHERE a.deptid=NEW.id;
END $$

UPDATE t_emp SET NAME =‘萧峰’ WHERE id =5

12.视图 view
是什么:将一段查询sql封装为一个虚拟的表。这个虚拟的表只保存了sql逻辑,不会保存任何查询结果。
作用:封装复杂的sql语句,提高复用性。 更新不需要发布程序,面对频繁的需求变更更灵活。
适用场景:很多地方可以共用的一组查询结果,报表。
语法:创建: 使用:
注意事项:mysql的视图中不允许有from后面的子查询,但orcale可以。

13.触发器
某张表发生的事件比如说insert update delete,来触发执行某些处理。
使用场景:冗余数据的同步
语法:
Delimiter KaTeX parse error: Double subscript at position 33: …r_afterupdate_t_̲dept After upda…
Delimiter;
注意事项:1.触发器虽然方便但是不利于维护2.影响数据库性能3.这种非程序触发的写凑操作很难被日志追踪4.触发了某事件的表,不能利用触发器对自己做操作

14.事件
作用:用来做定时任务
语法:开启定时事件的开关
Create
[definer={user|current_user}]
Event
[if not exists]
Event_name
On schedule schedule
[on completion [not] preserve]
[enable|disable|disable on slave]
[comment comment]
Do event_body;

Schedule:
At timestamp[+interval interval]…
[starts timestamp[+interval interval]…
[ends timestamp[+interval interval]…

Interval:
Quantity{year|quarter|month|day|hour|minute|week|second|year_month|day_our|day_minute|day_second|hour_minute|hour_second|minute_secons}

15.定时备份
需求:每日凌晨2点备份mydb数据库脚本,并以年月日为目录,以年月日为文件名,放到/backup/mysql下
备份:导出语句
mysqldumo明林导出sql文件
格式:mysqldump –u 用户名 –p 密码要导出的数据库>导出的文件.sql
shell脚本:

定时任务:crond和crontab

五、主从复制
主从复制
redis也有主从复制

1.复制的基本原理:slave会从master读取binlog来进行数据同步
三步骤+原理图
mysql复制过程分成三步:
1master将改变记录到二进制日志binary log。这些记录过程叫做二进制日志事件。
2slave将master的binary log event拷贝到它的中继日志relay log
3slave重做中继日志中的事件,将改变应用到自己的数据库中。mysql复制是异步的且串行化的。

2.复制的基本原则:每个slave只有一个master;每个slave只能有一个唯一的服务器id;每个master可以有多个slave

3.复制的最大问题:延时

4.一主一从常见配置:
mysql版本一直且后台以服务运行
主从都配置在mysqld节点下,都是小写

5.主机修改my.ini配置文件
1.必须,主服务器唯一id server-id=1
2.必须。启用二进制日志 log-bin=自己本地的路径/mysqlbin log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
3.可选。启用错误日志 log-err=自己本地的路径/mysqlerr log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
4.可选。根目录 basedir=“自己本地的路径” basedir=“D:/devSoft/MySQLServer5.5/”
5.可选。临时目录 tmpdir=“自己本地路径” tempdir=“D:/devSoft/MySQLServer5.5/”
6.可选。数据目录 datadir=“自己本地路径/Data/” datadir=“D:/devSoft/MySQLServer5.5/Data/”
7.read-only=0 主机,读写都可以
8.可选。设置不要复制的数据库。binlog-ignore-db=mysql
9.可选。设置需要复制的数据库。binlog-do-db=需要复制的主数据库名字

6.从机修改my.cnf配置文件
必须。从服务器唯一id
可选。启用二进制日志

因修改过配置文件请主机+从机都重启后台mysql服务。
主机从机都关闭防火墙。
windows手动关闭。关闭虚拟机linux防火墙,service iptables stop

在windows主机上建立账户并授权slave。
GRANT REPLICATION SLAVE ON . TO ‘zhangsan’@‘从机器数据库IP’ IDENTIFIED BY ‘123456’;
flush privileges;
查询master的状态:show master status; 记录下file和position的值。

执行完此步骤后不要再操作主服务器mysql,防止主服务器状态值发生变化。

在linux从机上配置需要复制的主机。
CHANGE MASTER TO MASTER_HOST=‘主机IP’,MASTER_USER=‘zhangsan’,MASTER_PASSWORD=‘123456’,MASTER_LG_FILE=‘file名字’,MASTER_LOG_POS=position数字;

启动从服务器复制功能 start slave;
show slave status\G 下面两个参数都是yes,则说明主从配置成功!Slave-IO_Running:Yes Slave_SQl_Running:Yes

主机新建库、新建表、insert记录,从机复制。

如何停止从服务复制功能。
stop slave;

六、mycat
是什么:数据库中间件读写分离
干什么的:1.读写分离 2.数据分片(垂直拆分、水平拆分、垂直+水平拆分) 3.多数据源整合
原理:拦截,这种方式把数据的分布式从代码中解耦出来,程序员察觉不出来后台使用mycat还是mysql。
mycat的原理中最重要的一个动词是拦截,它拦截了用户发送过来的sql语句,首先对sql语句做了一些特定的分析:如分片分析、路由分析、读写分离分析、缓存分析等,然后将此sql发送后端的真实数据库,并将返回的结果做适当的处理,最终再返回给用户。

安装启动:
1.解压缩文件拷贝到linux下/usr/local
2.三个文件
Schema.xml定义逻辑
Rule.xml定义分片规则
Server.xml定义用户以
4. 启动前先修改schema.xml<schema <dataNode <dataHost
5. 再修改server.xml
6. 启动程序。控制台启动 后台启动
7. 启动时可能出现报错 域名解析失败
8. 登陆 后台管理窗口 数据窗口

主从复制:
Schema.xml balance
负载均衡类型,目前的数值有三种:

  1. balance=0不开启读写分离机制,所有读操作都发送到当前可用的writerhost上
  2. balance=1全部的readhost与stand by writehost参与select语句的负载均衡,简单的说,当双主双从模式(M1-S1,M2-S2,并且M1与M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。
  3. balance=2所有读操作都随机的在writehost\readhost上分发
  4. balance=3所有读请求随机的分发到writerhost对应的rreadhost执行,writehost不负担读压力
    读写分离,创建表。分别在两个库下插入insert into t_replica(name) values (@@hostname)
    然后在mycat下执行select * from t_replica能够

分库:
创建表
Create table customer
Create table orders
Create table orders_detail
Create table dict_order_type

Schema.xml

水平分表:
Schema.xml
Rule.xml
全局序列:三种方式,本地文件、数据库方式、时间戳方式、自主生成。
跨库join:
全局表:设定为全局的表,会直接复制给每个数据库一份,所有写操作也会同步给多个库。
ER表:为了相关联的表尽量分在一个库下
federated引擎

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/AthlenaA/article/details/98901451

智能推荐

HTML5基本标签_radio标签-程序员宅基地

文章浏览阅读5.3k次,点赞10次,收藏16次。目录HTML简介:网页基本信息:网页基本标签:图像和超链接标签块元素和行内元素列表标签表格标签:媒体元素(视频和音频)iframe内联框架表单语法:初始表单post和get提交文本框和单选框表单的应用:表单初级验证:HTML简介:HTML (HyperTextMarkupLanguage)超文本标记语言超文本包括:文字,图片,音频,视频,动画等W3CWorldWideWebConsortium (万维网联盟)..._radio标签

(转)FastJson 解决JSON转嵌套Map出现的顺序乱序问题_map序列化json为什么key的顺序变化了-程序员宅基地

文章浏览阅读597次。2018年08月02日 16:54:15 FD-Do 阅读数:999弄了一天的问题,感谢大神转自:http://inlhx.iteye.com/blog/2312512 最近项目中用到了fastjson(1.2.15)需要将前端多层嵌套json转换为map,由于map的无序性,想了很多办法,最终找到使用 Map m= JSONArray.parseObject(json, Linke..._map序列化json为什么key的顺序变化了

用Python做一个变态版的《超级玛丽》游戏_爬虫版超级玛丽-程序员宅基地

文章浏览阅读6.7k次,点赞6次,收藏33次。大家好,我是一行Python可以做市面上90%的2D游戏,但是最有意思的还是拿来熟知的游戏亲手修改里面的元素这样的游戏不仅可以自己娱乐,还可以做成视频利用反差感来分享在各个短视频平台,可以称作是一种流量密码像下面这样离谱的超级玛丽就是通过调节游戏里面的参数来达到的效果 class="video_iframe rich_pages" data-vidtype="2" data-mpvid="wxv_2125779370458791937" data-cover="http%3A%2F%2Fmmbiz.qpic_爬虫版超级玛丽

windows安装fasttext_window 安裝fasttext-程序员宅基地

文章浏览阅读720次。windows版fasttext一、环境:Windows10Python3.7Pycharm二、fasttext安装1.windows安装fasttext请参照博客:https://blog.csdn.net/yyy430/article/details/885343192.安装好之后,请在pycharm中安装fasttext3.若安装之后依然不能imp..._window 安裝fasttext

【Python数据科学手册】Pandas——三、数据取值与选择_series 对象与一维 numpy 数组和标准 python 字典在许多方面都一样。所以,seri-程序员宅基地

文章浏览阅读1.5k次。文章目录三、数值取值与选择1.Series数据选择方法1). 将Series看作字典2). 将Series看作一维数组3). 索引器: loc、 iloc和ix2.DataFrame数据选择方法三、数值取值与选择1.Series数据选择方法Series 对象与一维 NumPy 数组和标准 Python 字典在许多方面都一样。1). 将Series看作字典和字典一样, Series 对象提..._series 对象与一维 numpy 数组和标准 python 字典在许多方面都一样。所以,series

gblfy_IDEA常用快捷键技巧_keyboard输入cat-程序员宅基地

文章浏览阅读237次。IDEA常用快捷键快捷键说明idea中对应的名称CTRL+G查看类和变量在哪里被调用了Main menu -> Edit -> Find -> Find UsagesALT+SHIFT+F全局搜索,所有文件中Main menu -> Edit -> Find -> Find in Path…ALT+SHIFT+R全局..._keyboard输入cat

随便推点

小程序调试webview_小程序 webview调试-程序员宅基地

文章浏览阅读4.4k次。在百度上搜,小程序调试webview, 都说用什么vconsole。看了官方文档, 发现在 webview 右键 最左上角 有个 调试, 这样就可以调试 webview了。 (不知道腾讯为什么把这个按钮搞得这么小, 不仔细看 我还找不到。)..._小程序 webview调试

flask学习之flask-migrate 迁移数据库 (以及错误:Target database is not up to date的解决)_error [flask_migrate] error: target database is no-程序员宅基地

文章浏览阅读2.5k次。在实际的开发中,经常会发生数据库修改的行为,一般我们修改数据库不会直接手动的去修改,而是去修改ORM对应的模型,然后再把模型映射到数据库中,这时候如果有一个工具专门做这种事情,就显得非常有用了,而flask-migrate就是做这个事情的。flask-migrate是基于alembic进行的一个封装,并且继承到flask中,而所有的迁移操作其实都是alembic做的,他能跟踪模型的变化,并且将变化映射到数据库中。..._error [flask_migrate] error: target database is not up to date.

安卓通过SQLite实现登录注册功能(小白式教程)_安卓使用sqlite实现登录注册-程序员宅基地

文章浏览阅读2w次,点赞280次,收藏614次。安卓通过SQLlite实现登录注册功能前面基本操作看图片第一个xml文件是:round_bg.xml,后面界面布局要用到<?xml version="1.0" encoding="utf-8"?><shape ="http://schemas.android.com/apk/res/android"> <solid android:color="@color/colorPrimary"/> <c_安卓使用sqlite实现登录注册

基于gitlab+docker+k8s打造自动化构建部署流程_gitlab + kubernetes + docker-程序员宅基地

文章浏览阅读3.5w次,点赞7次,收藏35次。工程自动化构建部署前言​ 随着需求越来越多,以及相关代码分支权限的流程规范化,团队内部的项目上线部署复杂度提升,基于现有的环境和市场上主流的软件,打造了一套基于gitlab+docker+harbor+kubernetes的自动化构建部署流程(CI/CD),目前团队内部流程基本走通,整理了一份相关文档分享出来。写的糙的地方,轻喷。概念GitLab CI/CD​ GitLab 是一个用于仓库管理系统的开源项目,使用Git作为代码管理工具,并在此基础上搭建起来的Web服务。与之对应的有GitHub和_gitlab + kubernetes + docker

Navicat连接MySQL8.0亲测有效-程序员宅基地

文章浏览阅读5.1k次,点赞2次,收藏5次。今天下了个 MySQL8.0,发现Navicat连接不上,总是报错1251;原因是MySQL8.0版本的加密方式和MySQL5.0的不一样,连接会报错。试了很多种方法,终于找到一种可以实现的:  更改加密方式  1.先通过命令行进入mysql的root账户:PS C:\Windows\system32&gt; mysql -uroot -p  再输入root的密码:..._navicat连接mysql8.0亲测有效

k8s集群证书延期_有没有不重启kubelet 延长证书时间的方式-程序员宅基地

文章浏览阅读2.2k次。k8s集群证书延期延期前有几个前提条件1、kubeadm需要调整,修改证书过期时间,把时间延长到100年2、ca.crt我这里是调整后的100年,就没有调整补充;kubelet延期为涉及本次我的集群证书延期,是在这2个前提条件完全满足的情况下进行的具体操作如下:1、备份k8s全目录,/etc/kubernetescp -r /etc/kubernetes /etc/kubernetes-bak2、备份kubelet的目录(当时备份时,由于是第一次搞,考虑到万一kubelet受到影响,崩_有没有不重启kubelet 延长证书时间的方式

推荐文章

热门文章

相关标签