MySQL基础回顾

1、group by 查询



select 字段名，分组名
from 表名
where 筛选条件  // 分组前筛选
group by 分组名
having 分组后筛选条件  //分组后筛选
order by 排序字段



2、sq99连接查询



select 字段名
from 表名 as 别名
【连接类型】join 表名 as 别名
on 连接条件
where 筛选条件
order by 排序字段



其中【连接类型包括】

  • inner //内连接，等值连接
  • left //外连接，主表有、从表没有；左表是主表
  • right //右边是主表
  • full
  • cross

3、子查询与联合查询



select * 
from 表名
where id in/any/all (子查询)



union [all]：联合查询把不同的查询结果进行并集。

4、delete与truncate区别

delete可以删除部分行，通过where进行筛选。

truncate则不可以，是清楚整张表，相当于格式化。

5、表的复制



create table my_copy like 目标表   //复制结构
create table my_copy 
select * from 目标表               //复制结构和



6、约束

  • 非空
  • 主键 //主键默认非空且唯一，自动会创建索引，可以组合
  • 唯一 //可以为空，可以组合
  • 检查
  • 默认
  • 外键 //作用于表上，而不是列上。会自动创建索引。
【CONSTRAINT 约束名】  约束类型（字段名）



7、视图

  • 多个地方用到同样的查询且SQL相对复杂。
  • 不保存结果，只保存SQL。

优点：重用SQL，保护和封装数据。

可以进行插入或者修改，不建议这样，视图最好只有‘只读’权限。



create view 视图名
as
查询语句；



8、级联

  • 级联删除
  • 级联置空

9、变量

  • 系统变量：全局变量、会话变量
  • 自定义变量：用户变量、局部变量
//查看系统变量
SHOW [GLOBAL|SESSION] VARIABLES;  //不填写默认是会话变量

//查看指定系统变量
SELECT @@GOLOBAL|SESSION.系统变量名；   //不填写默认是会话变量

//设置指定系统变量
SET @@GOLOBAL|SESSION.系统变量名 = 值；   //不填写默认是会话变量

变量修改后重启服务则恢复默认初始值，除非修改配置文件。



10、存储过程和函数

可以理解为一组方法



delimeter $
create procedure 存储过程名（IN|OUT 变量名 变量类型）

begin
    SQL语句
end $

//调用
CALL 存储过程名（参数列表）



函数：有且只有一个返回



create function 函数名（参数类型） returns 返回类型

begin
    函数体
end



第一章 MySQL架构与历史

1、MySQL逻辑架构图




mysql 循环 print mysql 循环嵌套循环_存储引擎


  • 最上层：比较通用的一个设计。
  • 第二层：核心功能处在这里，包括查询解析、分析、优化、缓存和所有内置函数。跨存储引擎的功能也在这一层实现，包括存储过程、视图、触发器等。
  • 最下层：存储引擎，负责数据的存出和提取。不同存储引擎各有自己的优劣势。通过提供存储引擎API，来屏蔽不同存储引擎的差异。

连接与鉴权

每个客户端都会在服务器进程中拥有一个线程，较高版本的MySQL版本提供了线程池插件，实现用较少的线程来支持大量的连接。

连接后，执行某操作还会进行鉴权。

优化与执行

第二层会解析并分析查询语句、优化查询语句、看是否命中缓存等。

2、并发控制

主要讲服务器层面 与 存储引擎层面 的并发控制。

读写锁

  • 共享锁，又称作 读锁。
  • 排它锁，又称作 写锁。

锁粒度

  • 行级锁：开销大、但是并发量高。大部分数据库采用这种形式。在存储引擎层面实现。
  • 表锁：开销小、但是并发量低。在服务器层面实现，需要手动加锁来锁定整张表。

3、事务

  • 原子性：一起成功 or 一起失败。
  • 一致性：数据库会从一个一致性状态 到达 另外一个一致性状态。
  • 隔离性：在一个事务结束之前，其他事务对其修改是看不到的。
  • 持久性：一旦事务提交，其数据会持久的存在数据库中。

事务需要额外的很大开销，所以说如果不需要事务，可以选择不支持事务的存储引擎。当前不支持事物的存储引擎，也可以通过LOCK TABLES为程序提供一定的保护（服务器层面）。

隔离级别

SQL中定义了四种隔离级别，隔离级别越低，开销越低，并发越高。


mysql 循环 print mysql 循环嵌套循环_存储引擎_02


大多数数据库的隔离级别是 读已提交，但是MySQL采用 可重复读。可重复读 会带来幻读这一问题，MySQL采用多版本并发（MVCC）来解决这一问题。

死锁与日志

两个事务互相等待对方释放资源。InnoDB处理方式，持有最少行级排它锁的事务进行回滚。

事务日志可以提高事务性能，修改数据只需要修改内存中数据，并且把修改记录行为记录在日志中，持久化在硬盘中即可。

4、多版本并发控制MVCC

通过增加两个隐藏列用于解决幻读情况。两列都是全局自增系统版本号。

5、存储引擎

InnoDB: 支持事务。聚簇索引。

MyISAM: 不支持事务（行级锁），可以在服务器层面锁定整张表。

存储引擎选择：

  • 1、是否支持事务。不需要的情况下可以选择MyISAM,比如日志系统。
  • 2、备份
  • 3、崩溃恢复
  • 4、特有特性

常用SQL语句：


ALTER TABLE mytable ENGINE = InnoDB;  //修改表的存储引擎

mysqldump //备份数据库


参考文章：

数据库事务隔离级别 - 分析脏读 & 不可重复读 & 幻读www.cnblogs.com


mysql 循环 print mysql 循环嵌套循环_字段_03


第二章 MySQL基准测试

1、测试目标

  • 吞吐量 ：单位时间内数据库处理的事务量。
  • 响应时间或延迟 : 可以拿95%的相应时间来做参考。
  • 可扩展性：

2、设计基准测试

首先是提出问题并且明确目标

3、基准测试工具

集成测试工具：测试整个Web服务接口

  • ab
  • http_load
  • JMeter

单组键测试工具：直接测试MySQL

  • mysqlslap ：
  • super smack ：一款用于MySQL和PostgreSQL的基准测试工具。
  • sysbench : 一款多线程系统压测工具。

第三章 服务器性能分析

1、问题：

  • 如何确认服务器达到了最佳性能状态。
  • 找出某条语句为什么执行的不够快。
  • 如何解决这些疑难杂症。

常用指令：


SHOW VARIABLES LIKE 'PROFILING';

SET PROFILING = 1;

SHOW PROFILES;

SHOW PROFILE FOR QUERY 1;

SHOW STATUS;

SHOW PROCESSLIST;


第四章 Schema与数据类型优化

1、Schema涉及原则

  • 更小的通常更好。
  • 简单就好： 整形比字符串的性能好，所以日期、时间和IP等，最好用整形存储。
  • 尽量避免NULL： 对建立索引不利，索引列最好不要有NULL。

类型：


整形：tinyint、smallint、mediumint、int、bigint
实数：decimal(精度高、需要存储空间大)、float、double
字符串：varchar(变长、字符串越短，占用空间越小；适合用在修改少、最大长度远远大于平均长度场景下)
       char（定长，适用于经常修改场景，密码这种定长场景、效率高）
BLOB与TEXT: 尽量少使用。
ENUM：效率比较高。
时间与日期类型：DateTime-效率低点，存储时间跨度长；TimeStamp:1970-01-01以来的，不同时区看到值不同
Bit：


2、范式与反范式涉及

  • 平衡存储与查询性能的杠杆
  • 是否引入缓存表与汇总表
  • ALTER TABLE是非常耗时的，要注意

第五章 创建高性能索引

1、索引基础

  • BTree索引
  • Hash索引
  • full-text全文索引
  • R-Tree索引

2、是否创建索引情况

  • 常用查询和排序的字段需要创建。
  • 数据量小，区分度低，常修改的则不要建索引。

3、执行报告的字段类型

  • id 相同则按序加载，不同则id越大，优先级越高。
  • select_type 查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询。
  • SIMPLE
  • PRIMARY //带有子查询的最外层
  • SUBQUERY
  • DERIVED //派生出来的
  • UNION
  • UNION RESULT
  • table 哪张表
  • type 访问类型
  • system // 一张表且只有一条数据
  • const // 唯一索引，查询一条数据
  • eq_ref // 外键
  • ref // 非唯一性索引扫描，返回匹配某个单独值的所有行。
  • range // 一个索引的范围 30-60之间等
  • index // 索引读全部
  • all //直接全表，没有走索引
  • possible_keys //显示可能应用在这张表上的索引，一个或多个。
  • key // 实际用到的索引。覆盖索引（查询的col1,col2...与联合索引的大小和顺序相同）
  • key_len // 索引字段的最大可能长度。越短越好。
  • ref // 索引中用于筛选的值
  • rows // 每张表有多少行被优化器查询
  • extra // 额外重要的信息
  • using filesort // 排序时，索引没用上，需要内存内单独排序。
  • using temporary // 使用了临时表保存中间结果，对查询结果排序时常用到和group by。
  • using index // 表明使用到了覆盖索引。若同时出现using where,代表索引被用来执行值的查找；若没有出现，代表索引用来读取数据而非执行查找动作（即不去数据文件中）。
  • using where //表示使用到了where筛选
  • using join buffer //使用了连接缓存
  • impossible where // 筛选条件总是false
  • select tables optimized away //
  • distinct // 匹配一个值后，不再查找同样的值

4、索引案例分析


最左匹配原则
索引列不要设计计算、类型转换（手动或自动）等操作
范围之后的索引全部失效
尽量使用覆盖索引进行查询
如果左边是通配符，最好采用覆盖索引查，type级别是index。
字符串不加单引号索引失效（相当于在索引字段上进行类型转换）
少用or，用它链接时，索引失效。
不等于、IS NULL、IS NOT NULL会使索引失效，尽量避免。

左连接给右表加索引
小结果集驱动大结果集
优先优化嵌套循环的内层循环


小表驱动大表，使用in
大表驱动小表，使用exist     //要深刻理解in与exist的区别


5、Order By的优化

排序方式：

  • 文件排序 using filesort
  • 索引排序 using index

文件排序两种算法：

  • 双路算法、两次I/O
  • 单路算法，一次I/O
单路算法，一次I/O，但是耗内存，是把所有查询的字段加载到buffer，按照排序列进行排序。
所以要把握好buffer的大小，避免多次I/O加载。


调优步骤

  • select * 是大忌讳，需要哪些字段就查哪些字段。
  • Query的字段大小总和小于max_length_for_sort_data时，且不是TEXT|BLOB类型时，会采用优化后的排序方法，即单路排序算法。
  • 两种算法都可能超过sort_buffer的值，导致产生合并排序。
  • 尝试提高sort_buffer_size的值。
  • 尝试提高max_length_for_sort_data的值。但是设置的太高，又可能触发sort_buffer。。。

Group By的优化与Order By基本相同。不同在于

  • 先排序再分组
  • 用不上索引时，适当调大系统参数
  • where是分组前筛选，having是分组后筛选

慢查询日志

  • 开启慢查询日志
  • 设置阈值、慢查询日志文件位置
  • 查看相关信息
  • 工具：mysqldumpslow

6、show profile


show profiles;

show profile cpu, block io for query id;


7、读写锁

MyISAM（偏读）：给表加读锁后； show open tables ：查看表的锁状态。

  • 当前session可以读。
  • 当前session写自己表出错。
  • 当前session读其他表出错。
  • 其他session可以读。
  • 其他session若修改，则会一直阻塞。

给表加写锁

  • 当前session可以写。
  • 当前session可以读。
  • 当前session读其他表出错。
  • 其他session读则阻塞。
  • 其他session写则阻塞。

InnoDB：支持事务、行级锁。

间隙锁：修改字段的条件是一个范围，那么会对这个范围的所有行加锁，包括不存在的行。若这时有其他session进行增加数据，则会阻塞。


// 锁定一行
select * from table where xxx  for update;


8、主从复制