mysql（自用）

数据库特点

持久化存储
读写速度极高
保证数据的有效性
对程序支持非常好，容易扩展

数据库分类

关系型数据库：可以保存现实生活中的各种关系数据，数据库中存储数据以表为单位；主流关系型数据库：MySQL，Oracle，SQLServer等
非关系型数据库：通常用来解决某些特定的需求，比如高并发访问。主流非关系型数据库：Redis，Mongodb，memacache等

SQL介绍

SQL是结构化查询语言，是一种用来操作RDBMS(关系型数据库管理系统)的数据库语言，当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作oracle，sql server,mysql等关系型数据库。

SQL语句主要分为

• DDL语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。
• DML语句：数据操纵语句，用于添加、删除、更新、和查询数据库记录，并检查数据完整性
• DCL语句：数据控制语句，用于控制不同数据段直接许可和访问级别的语句。

数据类型
常用数据类型
• 数值类型

tinyint	TINYINT(size)	-128~127	0-255
smallint	SMALLINT(size)	-32768~32767	0~65535
mediumint	MEDIUMINT(size)	-8388608~8388607	0~16777215
int	INT(size)	-2147483648~2147483647	0~4294967295
bigint	BIGINT(size)	-9223372036854775808~9223372036854775807	0~18446744073709551615

小数类型	描述
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。(定点数类型)

• 日期时间类型
DATE()
日期。格式：YYYY-MM-DD
注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME()
日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS
注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIME()
时间。格式：HH:MM:SS 注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR()
2 位或 4 位格式的年。
4 位格式：1901 到 2155。2 位格式：70 到 69，表示从 1970 到 2069
TIMESTAMP()
时间戳。格式：YYYY-MM-DD HH:MM:SS
注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC

• 字符串类型

CHAR(size)	0-255	char(3)输入’ab’，实际存储为’ab ‘，输入’abcd’，实际存储为’abc’
VARCHAR(size)	0-65535	varchar(3)输入’ab’，实际存储为’ab’，输入’abcd’，实际存储为’abc’

枚举类型
枚举类型英文为ENUM，对1-255个成员的枚举需要1个字节存储；对于255~65535个成员，需要2个字节存储。最多允许65535个成员。创建方式：enum(“M”,“F”)；
枚举在指定默认值时必须是枚举选项中的
在取值是可以通过下标取值，从1开始

注意
• decimal表示定点小数，如decimal(5,2)表示共存5位数，小数占2位。不写则默认为decimal(10,0)
• char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为’ab ’
• varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’
• 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
• 枚举类型不区分大小写

详细数据类型可参考：http://blog.csdn.net/anxpp/article/details/51284106

约束
• 主键约束(primary key)：它能够唯一确定表中的一条记录，也就是我们通过给某个字段添加约束，就可以使得字段不重复且不为空。
• 自增约束(auto_increment)
• 惟一约束(unique)：此字段的值不允许重复
• 非空约束(not Null)：此字段不允许填写空值
• 默认约束(default)：当不填写此值时会使用默认值，如果填写时以填写为准
• 外键约束(foreign key)：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

SQL语句

注意
• mysql不严格区分大小写
• 分号作为结束符，必须写

操作数据库（DDL）

连接数据库
在mysql文件目录路径下
mysql -u用户名 -p
enter password: 密码

退出数据库
exit
quit

查看已经创建的数据库
SHOW DATABASES; （show databases;）

查看数据库版本
select version();

创建数据库
CREATE DATABASE 数据库名; (create databaes 数据库名;)
CREATE DATABASE 数据库名 charset=utf8;
注意
• charset：指定编码为utf8

查看创建数据库的命令
SHOW CREATE DATABASE 数据库名; (show create database 数据库名;)

查看当前使用的数据库
SELECT DATABASE(); (select database();）

使用数据库
USE 数据库名; (use 数据库名;)

删除数据库
DROP DATABASE 数据库名; (drop database 数据库名;)

注意
• SQL语句不区分大小写
• 每行必须以英文状态下分号作为结束
• 当数据库名称有特殊字符时，使用``键，esc下面，英文包裹。如：Logic--edu

操作数据表（DML）

查看当前数据库中所有表
必须在使用数据库下
SHOW TABLES; （show tables;）

创建表
CREATE TABLE 数据表名(字段 类型 约束[, 字段 类型 约束]) (creat table 数据表名)

当有中文时要指定编码如：create table student(gender enum(“男”,“女”))engine=innodb defoult charset=utf8;

删除表
DROP TABLE 数据表名; (drop table 数据表名;)

查看表的创建语句
SHOW CREATE TABLE 数据表名; （show create table 数据表名）

查看表描述信息
DESC 数据表名; (desc 数据表名)

添加表字段
ALTER TABLE 数据表名 ADD 字段 类型; (alter table 数据表名 add 字段 类型;)

修改表字段
ALTER TABLE 数据表名 MODIFY 字段 类型; (alter table 数据表名 modify 字段 类型) – 不重命名
ALTER TABLE 数据表名 CHANGE 原字段名 新字段名 类型及约束; （alter table 数据表名 change 原字段名 新字段名 类型及约束） – 将字段重命名

删除表字段
ALTER TABLE 数据表名 DROP 字段; （alter table 数据表名 drop 子段;）

修改表名
rename table 表名 to 新表名

操作数据

整行插入
INSERT INTO 数据表名 VALUES(值1,值2,值3…); (insrt into 时间表名 values(值1,值2…))

指定列中插入数据
INSERT INTO 数据表名(字段1, 字段2,…) VALUES (值1,值2,…); (insert into tablename (字段1, 字段2,…) values (值1,值2,…)

指定列中插入多条数据
INSERT INTO 数据表名 (字段1, 字段2,…) VALUES (值1,值2,…),(值1,值2,…); (insert into 数据表名 (字段1, 字段2,…) values (值1,值2,…),(值1,值2,…)😉

修改数据
UPDATE 数据表名 SET 字段1=新值，字段2=新值 [WHERE 条件]; (update 数据表名 set 字段1=新值，字段2=新值 [where 条件])

删除数据
DELETE FROM 数据表名 [WHERE 条件]; (delete from 数据表名 [where 条件]) 属于dml语句，一条记录一条记录删除
truncate table 数据表明 属于ddl语句，全删，删表再重新创建一个结构一样的表

查询数据

查询整个表数据
SELECT * FROM 数据表名 [where 条件]; (select * from 数据表名)

查询指定字段数据
SELECT 字段1,字段2 FROM 数据表名 [where 条件]; (select 字段1，字段2 from 数据表名)

查询指定字段数据，并给字段起别名（显示重命名，没有改变表）
SELECT 字段1 as 别名,字段2 as 别名 FROM 数据表名 [where 条件]; (select 字段1 as 别名,字段2 as 别名 from 数据表名)

查询指定字段并去重
SELECT DISTINCT 字段1 FROM 数据表名 [where 条件]; (select distinct 字段1 from 数据表名;)
SELECT DISTINCT 字段1,字段2… FROM 数据表名;(多个字段去重，那么都重复才去重)

where子句
where子句通常结合增删改查使用，用于做筛选的条件。不仅如此，经常结合运算符使用。

模糊查询

like关键字用来进行模糊查询，并且结合%以及_使用。
• % 表示任意多个任意字符
• _ 表示一个任意字符

如：如查询classes表中以z开头的名字

select * from classes where name like "z%";

范围查询

in
表示在一个非连续的范围内
如：查询id是9，10，14

select * from classes where id in (9,10,14);

between…and…
表示在一个连续的范围内

select * from classes where id between 9 and 13;

空判断

is null
判断空

is not null
判断非空

常用聚合函数

count 总数
max() 最大值
min() 最小值
sum 求和
avg 平均值

分组与分组之后的筛选

分组
在数据库中，通过 group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组。
select … from students group by 需要分组字段;
group_concat(…)
我们通过group_concat(…)查看每组的详细信息

分组后的筛选

排序

order by 字段 默认升序
order by 字段 asc 指定升序
order by 字段 desc 指定降序

限制

limit start,count
• start 为偏移量，默认起始0
• count 为条数
注意：
• limit 不能写数学公式
• limit只能写在末尾

数据库备份和还原

在管理员命令行输入mysqldunp -u 用户-p 数据库名 >备份地址

还原：mysqldunp -u  用户-p 数据库名 <备份地址
第二：use 数据库；
source 地址

表连接

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回mysql。这时我们就需要使用表连接。

内连接

内连接仅选出两张表中互相匹配的记录
select * from 表1 inner join 表2 [on 条件];

左连接(必须有条件)

查询的结果为两个表匹配到的数据，左表持有的数据，对于右表中不存的数据使用null填充
select * from 表1 left join 表2 on 表1.列 = 表2.列;

右连接(必须有条件)

查询结果为两个表匹配到的数据，右表持有的数据，对于左表中不存在的数据使用null填充
select * from 表1 right join 表2 on 表1.列 = 表2.列;

子查询

某些情况下，当进行查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。

自关联

可以简单的理解为自己与自己进行连接查询。

外键

外键介绍
MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关联关系的表而言，相关联字段中的主键所在表就是主表(父表)，外键所在的表就是从表(子表)。

注意：
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
创建表时设置外键约束

语法：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]
(constraint <外键名>] foreign key （字段名 [，字段名2，…]） references <主表名>（主键列1 [，主键列2，…] ）)
注意：

• 从表的外键关联必须是主表的主键，并且主键与外键的类型必须保持一致。
外键中的数据要属于主表中的主键

在修改表时添加外键约束

alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);

删除外键约束

ALTER TABLE 从表 DROP FOREIGN KEY fk_cid;
(alter table 从表 drop foreign key 外键名)

视图

视图介绍

视图(View)是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

定义视图

create view 视图名称 as select 语句

SELECT * FROM provinces;
CREATE VIEW v_pro AS SELECT * FROM provinces;
SELECT * FROM v_pro;

查看视图

查看表的时候会把视图表也列出来
show tables;

使用视图

select * from v_pro;

视图的作用

• 简单：提高了重用性，就像一个函数。
• 安全：提高了安全性能，可以针对不同的用户，设定不同的视图。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

视图的修改(不建议修改)
有下列内容之一，视图不能做修改
• select子句中包含distinct
• select字句中包含组函数
• select语句中包含group by子句
• selecy语句红包含order by子句
• where子句中包含相关子查询
• from字句中包含多个表
• 如果视图中有计算列，则不能更新
• 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作。

事务

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位

事务四大特性(简称ACID)

• 原子性(Atomicity)

要么全部提交成功，要么全部失败回滚,不可能只执行其中的一部分操作

• 一致性(Consistency)

要么完成事务，要么没完成。数据库总是从一个一致性的状态转换到另一个一致性的状态。

• 隔离性(Isolation)

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

• 持久性(Durability)

一旦事务提交，则其所做的修改会永久保存到数据库。

事务的状态

• 活动的（active）

• 部分提交的（partially committed）
  所造成的影响并没有刷新到磁盘时

• 失败的（failed）
  当事务处在活动的或者部分提交的状态时，可能遇到了某些错误

• 中止的（aborted）
  当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态

• 提交的（committed）

事务命令
表的引擎类型必须是innodb类型才可以使用事务

开启事务

begin;
或者
start transaction;

提交事务

commit;

回滚事务

rollback;

保存点
我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。

添加保存点
asvepoint 保存点名称;

回到保存点
rollback to 保存点名称;

删除保存点
release saverpoint 保持点名称

注意

• 修改数据的命令会自动的触发事务，包括insert、update、delete
• 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

事务隔离级别

较低级别的隔离通常来说能承受更高的并发，系统的开销也会更小。

查看当前事物级别
SELECT @@tx_isolation;

set session transaction isolation level 设置事务隔离级别

• READ UNCOMMITTED（未提交读）
  事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。
• READ COMMITTED（读已提交）
  大多数数据库系统默认的隔离级别都是READ COMMITTED(但MySQL不是)。一个事务只能看见已经提交的事务的修改结果。
• REPEATABLE READ（可重复读）
  "可重复读"是MySQL的默认事务隔离级别。该级别保证了在同一次事务中多次查询相同的语句结果是一致的。但是"可重复读"隔离级别无法避免产生幻行（Phantom Row）的问题
• SERIALIZABLE（可串行化）
  SERIALIZABLE是最高的隔离级别，它通常通过强制事务串行，避免了前面说的幻读问题。只有在非常需要确保数据的一致性切可以接受没有并发的情况下，才会考虑使用这个隔离级别。

隔离级别	脏读	不可重复读	幻影读
未提交读	有	有	有
提交读	没有	有	有
可重复读	没有	没有	有
可串行读	没有	没有	没有

好的事务习惯

• 循环写入的情况，如果循环次数不是太多，建议在循环前开启一个事务，循环结束后统一提交。
• 优化事务里的语句顺序，减少锁时间。
• 创建事务之前，关注事务隔离级别。
• 不在事务中混合使用存储引擎。

索引

• 查看索引
show index from 表名;

• 创建索引
• 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
• 字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))

• 删除索引
drop index 索引名称 on 表名;

不适合建立索引的情况
• 频繁更新的字段不适合建立索引
• where条件里面用不到的字段不创建索引
• 表记录太少,当表中数据量超过三百万条数据,可以考虑建立索引
• 数据重复且平均的表字段,比如性别,国籍

账户管理

创建用户
命令：crate user 'username'@'host' identified by 'password';
说明：
• username：登录的用户名
• password：是登录的密码
• host：指定可以登录的主机，其中localhost表示本机，%表示所有主机

修改用户
MySQL修改用户的信息使用ALTER USER语句，比如我们要修改当前用户的密码，可以这样
mysql> ALTER USER USER() IDENTIFIED BY 'test123457';
alter user user() identified by 'test123457';

删除用户,要用root用户进行删除
drop USER juran;

查看所有用户
• 所有用户及权限信息存储在mysql数据库的user表中
• 查看user表的结构
desc user;

创建账户&授权
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

使用grant进行授权时，如果该用户不存在，可以在grant语句后面跟上identified by直接创建该用户

MySQL引擎之MyISAM

MyISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成

MyISAM存储引擎特性

1.并发性与锁级别

表级锁

2.表损坏修复

查看状态
check table 表名
修复
repair table 表名

3.MyISAM表支持数据压缩

压缩后不支持修改，查询快

压缩文件（linux下）
myisampack -b -f myIsam.MYI

适合场景

1.非事务型应用
2.只读类应用

MySQL引擎之Innodb

MySQL5.5 及之后版本默认存储引擎,支持事务的ACID特性
Innodb使用表空间进行数据存储

show variables like 'innodb_file_per_table';

ON:独立表空间,tablename.ibd
OFF:系统表空间:ibdataX X是一个数字

Innodb存储引擎的特性

1.支持事务的ACID特性
2.Innodb支持行级锁,可以最大程度的支持并发

MySQL引擎之CSV

创建表注意：
不支持主键
不支持自增
所有必须非空

数据以文本方式存储在文件中
.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息

当在表中改变数据，而没有通过数据库改变。想在数据库中体现需要输入

flush tables;

使用场景
适合做为数据交换的中间表

MySQL引擎之Memory

也称HEAP存储引擎,所以数据保存在内存中,如果MySQL服务重启数据会丢失,但是表结构会保存下来

功能特点
• 支持HASH索引和BTree索引
• 所有字段都为固定长度 varchar(10)=char(10)
• 不支持BLOB和TEXT等大字段
• Memory存储引擎使用表级锁

explain分析SQL语句

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。

explain + SQL语句

id表的读取顺序

1.id相同,执行顺序由上至下
2.id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

table

显示这一行的数据时关于那张表的

partitions

查询访问的分区

possible_keys

显示可能应用在这张表中的索引,一个或多个。

key

实际使用的索引。如果为null,则没有使用索引
查询中若使用了覆盖索引,则该索引仅出现在key列表中

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

Extra

包含不适合在其他列中显示但十分重要的额外信息
• Using filesort,说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为"文件排序"
• Using temporary,使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。
• Using index,使用了索引,避免了全表扫描
• Using where,使用了where过滤
• Using join buffer,使用了连接缓存
• impossible where,不可能的条件,where子句的值总是false

Show Profile进行SQL分析

是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量
默认情况下,参数处于关闭状态,并保持最近15次的运行结果。

开启命令

set global general_log = 1;

将SQL语句写到表中

set global log_output = 'TABLE';

你所编写的SQL语句,会记录到MySQL库里的genral_log表

select * from mysql.general_log;

批量插入数据

函数和存储过程

创建函数

创建函数，假如报错：this function has none of DETERMINISTIC… 查看参数

set global log_bin_trust_function_creators=1;

创建存储函数
语法结构如下：

CREATE FUNCTION  函数名([ 参数名 数据类型 [, …]]) RETURNS返回类型
BEGIN
　　过程体
END
-- 调用存储函数
select 函数名(参数)

创建存储过程
语法结构如下：

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...]
BEGIN
    过程体
END
-- 调用存储过程
call 函数名(参数)

索引优化

回表

如果执行的sql语句是非主键索引

select * from person where age = 18

上述语句会走age的普通索引，索引先根据age搜索等于18的索引记录，找到ID=10的记录，然后再到主键索引搜索一次，然后拿出需要查询的数据。
从普通索引查出主键索引，然后查询出数据的过程叫做回表。由于回表需要多执行一次查询，这也是为什么主键索引要比普通索引要快的原因，所以，我们要尽量使用主键查询。

覆盖索引

我们通常创建索引的依据都是根据查询的where条件，但是这只是我们通常的做法，我们根据上面的分析可以知道，如果要想查询效率高，第一，使用主键索引，第二，避免回表，也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段，那么我们就叫做"覆盖索引"

如要查询name,age
可以建立覆盖name,age的复合索引
crate index idx_col1_col2 on tablename(name,age);

键表SQL

• 建立的索引全部用上，不需要优化
• 遵循创建索引时的顺序查询
• 不能跳过创建的索引
• 范围之后的索引会失效
• like百分号写最右
• 不写*
• varchar引号不可丢

假设index(a,b,c)
where语句
索引是否被使用到
where a = 3
Y,使用到a
where a = 3 and b = 5
Y,使用到a,b
where a = 3 and b = 5 and c = 4
Y,使用到a,b,c
where b = 3 或 where b = 3 and c = 4 或 where c = 4
N
where a = 3 and c = 5
使用到a,c没有被使用,b中间断了
where a = 3 and b > 4 and c = 5
使用到了a,b
where a = 3 and b like 'kk%' and c = 4
使用到了a,b,c
where a = 3 and b like '%kk' and c = 4
使用到了a
where a = 3 and b like '%kk%' and c = 4
使用到了a

join语句优化

左连接
给右表添加索引

右连接
给坐标添加

内连接
两表都可以

Nested-Loop Join 算法

一个简单的 Nested-Loop Join(NLJ) 算法一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

Block Nested-Loop Join 算法

Block Nested-Loop Join(BNL) 算法的思想是：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，如果满足 join 条件，则返回结果给客户端。

explain 分析 join 语句时，在第一行的就是驱动表；如果没固定连接方式优化器会优先选择小表做驱动表。所以使用 inner join 时，前面的表并不一定就是驱动表。
一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer （***）；则表示使用的 join 算法是 NLJ。

关联字段添加索引
通过上面的内容，我们知道了 BNL、NLJ的原理，因此让 BNL变成 NLJ ，可以提高 join 的效率。我们来看下面的例子

小表做驱动表
前面说到，Index Nested-Loop Join 算法会读取驱动表的所有数据，首先扫描的行数是驱动表的总行数（假设为 n），然后遍历这 n 行数据中关联字段的值，根据驱动表中关联字段的值索引扫描被驱动表中的对应行，这里又会扫描 n 行，因此整个过程扫描了 2n 行。当使用 Index Nested-Loop Join 算法时，扫描行数跟驱动表的数据量成正比。所以在写 SQL 时，如果确定被关联字段有索引的情况下，建议用小表做驱动表。

select * from t2 straight_join t1 on t2.a = t1.a;
这里使用 straight_join 可以固定连接方式，让前面的表为驱动表

临时表
多数情况我们可以通过在被驱动表的关联字段上加索引来让 join 使用 NLJ 或者 BKA，但有时因为某条关联查询只是临时查一次，如果再去添加索引可能会浪费资源

• 首先创建临时表 t1_tmp，表结构与表 t1 一致，只是在关联字段 b 上添加了索引。
• 把 t1 表中的数据写入临时表 t1_tmp 中：

insert into t1_tmp select * from t1;

• 执行 join 语句：

select * from t1_tmp join t2 on t1_tmp.b= t2.b;

排序优化

mysql的排序方式

• 有序排序直接返回数据extra:using index
• 通过Filesort进行排序extra:using filesort
  MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。
  • 如果 “排序的数据大小” < sort_buffer_size: 内存排序
  • 如果 “排序的数据大小” > sort_buffer_size: 磁盘排序

优化

1. 排序字段添加索引
2. 多个字段建立复合索引，但排序字段的顺序需要与复合索引一致
3. 去掉不必要的返回字段
4. 增加sort_buffer_size参数设置
5. 增大max_lenght_for_sort_data参数的设置

无法优化

1. 使用范围查询再排序

   如有index(a,b)
   select id,a,b from t1 where a>9000 order by b;
   因为a有多个，不能保证b有序，所有b重排序
   

2. 升序，降序混合使用

分页查询优化

根据自增且连续主键排序的分页查询

select a,b,c from t1 limit 1000,10;
先读取1010条，然后舍弃1000

根据主键查询，但不适用，如果主键有缺失，结果不对

explain select * from t1 id>99000 limiit 2;

根据非主键字段排序的分页查询

select * from t1 order by a limit 99000,2;
explain select * from t1 order by a limit 99000,2;

让排序时返回的字段

1. 排序和分页先查出主键

select id from t1 order by a limit 9900,2;

2. 根据主键查找到对应类容

select * from t1 f join (select id from t1 order by a limit 99000,2) g on f.id = g.id;
explain select * from t1 f inner join (select id from t1 order by a limit 99000,2) g on f.id = g.id;

数据库锁

表锁(偏读)

偏向MyISAM存储引擎,开销小,加锁快;无死锁,锁定粒度大,发送锁冲突的概率最高,并发度低。

查看是否加锁

show open tables;

手动增加表锁

lock table 表名字 read(write),表名字2 read(write);

释放表锁

unlock tables;

在客户端1中加读锁，可读。不能修改数据，不能插入数据，不能访问其它表，报错。
在客户端2中，可读，可以查看其它表。不能加数据，不能修改数据，阻塞。

在客户端1中加写锁。可读，可改。不可查看其它表，报错。
在客户端2中，可查看其它表。不可查看加锁表，阻塞。

展示表的状态

show status like "tables%";

行锁(偏写)

偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁。锁定粒度最小,发生锁冲突的概率最低,并发度也最高
InnoDB与MyISAM的最大不同点,支持事务,采用了行级锁

添加行锁

关闭自动提交
set autocommit=0;
需要手动提交
conmmit;

在客户端1中没有提交，在客户端2中修改会阻塞，当verchar类型不写引号会引起行锁变成表锁

如何分析行锁定

通过检查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会给符合条件的已有数据记录的索引项加锁, 对于键值在条件范围内但并不存在的记录,叫做"间隙"

如在表中存在key字段1，3，4，5
将key>1 and key<6 的值改为6666
在期间用客户端2添加key为2的数据，会阻塞

如何锁定一行

select * from test_innodb_lock where a = 8 for update;

MySQL分区表

分区表的特点

在逻辑上为一个表,在物理上存储在多个文件中

分区类型
• RANGE分区
• LIST分区
• HASH分区

无论那种分区类型,要么分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包括分区键,也就是说不能使用主键/唯一字段之外的其他字段分区

RANGE分区

RANGE分区特点

• 根据分区键值的范围把数据行存储到表的不同分区中
• 多个分区的范围要连续,但是不能重叠
• 分区不包括上限,取不到上限值

create table `login_log_range`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb 
partition by range(login_id)(
partition p0 values less than(10000),   # 实际范围0-9999
partition p1 values less than(20000),   # 实际范围10000-19999
partition p2 values less than(30000),
partition p3 values less than maxvalue  # 存储大于30000的数据
);

HASH分区

HASH分区的特点

• 根据MOD(分区键,分区值)的值把数据行存储到表的不同分区内
• 数据可以平均的分布在各个分区中
• HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型

create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(login_id) partitions 4;

create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(UNIX_TIMESTAMP(login_time)) partitions 分区数量;

LIST分区

LIST分区特点

• 按分区键取值的列表进行分区
• 同范围分区一样,各分区的列表值不能重复
• 每一行数据必须能找到对应的分区列表,否则数据插入失败

create table `login_log_list`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip',
    login_type int(10) not null
)engine=innodb 
partition by list(login_type)(
partition p0 values in(1,3,5,7,9),    
partition p1 values in(2,4,6,8)   
);

查询表

select table_name,partition_name,partition_description,table_rows from 
information_schema.`partitions` where table_name = 'login_log_range'

修改分区-添加分区

alter table login_log_range add partition (partition p4 values less than(2018))

分区删除

alter table login_log_range drop partition p0;

数据归档

把分区中的数据迁移到另一个表中

alter table 分区表 exchange partition 表分区(p1) with table 迁移表;

条件

• mysql大于5.7
• 迁移到的表是一个非分区表
• 迁移表与分区表结构相同

使用分区表的注意事项

• 结合业务场景选择分区键,避免跨分区查询
• 对分区表进行查询最好在where从句中包含分区键
• 具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分