MySQL高级

MySQL

MySQL简介

概述：

MySQL是一个关系型数据库管理系统。开源的、支持大型的数据库，可以处理拥有上千万条记录的大型数据库。
使用标准的SQL数据语言形式。

支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

高级MySQL：

MySQL内核
sql优化工程师
MySQL服务器的优化
各种参数常量设定
查询语句优化
主从复制
软硬件升级
容灾备份
sql编程

MySQL Linux版的安装

二进制源码安装yum+RPM安装两种方式

RPM mysql5.5
下载地址：
http://dev.mysql.com/downloads/mysql/




第三方软件包放到opt目录下：

检查当前系统是否安装过MySQL：

rpm -qa|grep -i mysql

安装MySQL服务端（注意提示）：
rpm -ivh
安装速度、日志、进度条

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

MySQL服务的启动和停止：
如何查看系统中是否启动了MySQL
ps -ef|grep mysql

service mysql start
service mysql stop


MySQL服务启动后，开始连接：
首次连接成功

注意这里，因为MySQL默认没有密码，所以这里我们没有输入密码就直接连上了
按照安装server中的提示修改登陆密码

/usr/bin/mysqladmin -u root password 123456
mysql
mysql -uroot -p

自启动MySQL服务：
chkconfig mysql on
chkconfig -list|grep mysql
ntsysv


修改配置文件位置：

cd /usr/share/mysql
cp my-huge.cnf /etc/my.cnf

修改字符集和数据存储路径：

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.修改
cd /etc
vim my.cnf
:set nu
o在光标的下一行输入
esc
:wq!

service mysql stop
service mysql start
mysql -u -root -p
use db01;
select * from user;
原来的库还是不行
再重新建一个库，就行了
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;

MySQL的安装位置：

ps -ef|grep mysql

路径 解释 备注
/var/lib/mysql mysql数据库文件的存储位置
/usr/share/mysql 配置文件目录
/usr/bin 相关命令目录
/etc/init.d/mysql 启停相关脚本

MySQL配置文件

主要配置文件：
二进制日志log-bin：
用于主从复制

错误日志log-error：
默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息

查询日志log：
默认关闭，记录查询的sql语句，如果开启会减低mysql的性能，

数据文件：
windows:D;\devSoft\MySQLServer5.5\data目录下可以挑选很多库
linux:看看当前系统的全部库后再进去ls -lF|grep ^d，默认路径：/var/lib/mysql

frm文件：
存放表结构

myd文件：
存放表数据

myi文件：
存放表索引

如何配置：
windows:

linux:/etc/my.cnf

MySQL逻辑架构介绍

总体概览：
和其他数据库相比，MySQL的吧架构可以在多种不同场景中应用并发挥作用。主要体现在存储引擎的架构上。插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。


1.连接层
最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接代理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接、。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化以及部分内置函数的执行。所有跨库存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建响应的内部解析树，并对其完成响应的优化如确定查询表的顺序，是否利用索引等，最后生成响应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好地提升系统的性能。

3.引擎层
存储引擎层，存储引擎真正的负责了MYSQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

4.存储层
数据存储层，主要将数据存储在运行于裸机设备的文件系统之上，并完成与存储引擎的交互。

查询说明：

MySQL存储引擎

查看命令：

show engines;
show variables like ‘%storage_engine%’;

InnDB：支持主外键；支持事务；行锁，操作时只锁某一行，不对其他行有不影响，适合高并发的操作；不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响；表空间大；关注事务。

索引优化分析

性能下架：SQL慢、执行时间长、等待时间长

查询语句写的烂：

索引失效：
单值索引-create index idx_user_name on user(name)
复合索引-create index idx_user_nameEmail on user(name,email)

关联查询太多join：设计缺陷或不得已的需求

服务器调优及各个参数设置：缓冲、线程数等

常见的通用join查询

SQL执行顺序：
手写

select distinct <select_list>
from
机读：从from读的

总结

join图：

建表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独有的

索引简介

是什么：
1.MySQL官方对索引的定义：索引是帮助MySQL高效获取数据的数据。可以得到索引的本质：索引是数据结构。索引的目的在于提高查询效率，可以类比字典。
2.可以简单理解为“排好序的快速查找数据结构”。在数据本身之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
排序+查找两大功能，解决where后查的快，解决order by后排序快


3.一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
4.我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引，还有哈希索引。

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

劣势：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如：对表进行insert update delete因为更新表时，不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。索引只是提高效率的一个因素，如果有大量数据的表，就需要花时间研究建立最优化的的索引，或者优化查询。

mysql索引分类：
单值索引：即一个索引只包含单个列，一个表中可以有多个单列索引。

唯一索引：索引列的值必须唯一，但允许有空值。

主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引

复合索引：一个索引包含了多个列。

基本语法：
创建：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，属于全文索引。

mysql索引结构：
btree索引：检索原理：

b+tree索引：

hash索引：
聚簇索引与非聚簇索引：



fulltext全文索引：

r-tree索引：

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

哪些情况不要创建索引：
1.表记录太少
2.经常增删改的表（提高了查询速度却会同时降低更新表的速度，不仅要保存数据还需要保存一下索引文件）
3.数据重复且分布平均的字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
4.where条件里用不到的字段不创建索引
5.过滤性不好的不适合建立索引

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建立索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99.一个索引的选择性越接近于1，这个索引的效率就越高。

性能分析

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进行写相应的计算分析，然后再得出最后的执行计划。

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

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

各字段解释：
id select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
三种情况：id相同，执行顺序从上到下。

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在。id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。衍生deriver

关注点：id号每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好。

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

table 显示这一行的数据是关于哪张表的

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是从硬盘中读取的。

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

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

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

key 实际使用的索引。如果为null，则没有使用索引。
查询中若使用了覆盖索引，则该索引仅出现在key列表中
覆盖索引：select后面的字段刚好和建的索引的字段个数顺序一样

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

ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
查询中与其他表关联的字段，外键关系建立索引

rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

extra 包含不适合在其他列中显示但十分重要的额外信息
1.using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作成为文件排序。

2.using temporary 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by

3.using index表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现using where表明索引被用来执行索引键值的查找；如果没有同时出现using where表明索引用来读取数据而非执行查找动作。

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操作，在找到第一匹配的元组后即停止找同样值的动作

热身case：

查询优化

索引优化

索引分析：
单表（建表SQL、案例）

没有索引，所以查询效率 不高



对ccv建立了索引，一个是范围、一个是常量
但是出现了filesort，所以索引不合适，删掉

建立cv

两表

所以左连接还是给右边添加索引效果比较好，我们再改回去



所以说我们可以通过交换表的位置来，而不是非要改索引

再来看一个右连接


所以右连接应该建在左表

三表

使用了连接缓存

索引失效（应该避免）：

建表


不让索引失效就是优化索引

1.全值匹配我最爱

精度越来越高，代价也越来越大

name,age,position
如果索引没有name，只有单独的age，或者age position索引失效

2.最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左列开始并且不跳过索引中的列。

带头大哥不能死！
一楼二楼三楼，一楼没了
第一个索引字段不能丢失

违背了，中间兄弟不能断！
一楼二楼三楼，二楼没了
中间索引字段不能断

3.不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

范围本身用到了！但是之后用不到了
一楼二楼、三楼用不到了！
范围之后全失效！

5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select

用什么取什么！using index比较好！

ref比range好

6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

7.is nul,is not null也无法使用索引

8.like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作

问题：解决like’%字符串%'时索引不被使用的方法


写like查询一般只在右边写%

如果两边非要用！

创建索引：

使用覆盖索引！
要么全部吻合、要么半个沾边


所以说非要用两边都用%的，可以使用覆盖索引：建的索引和查的字段个数、顺序上最好一致

9.字符串不加单引号索引失效

因为发生了隐式的自动类型转换！

10.少用or用它连接时会索引失效

11.小总结

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

有查询优化器！所以1243 4321都会经过优化

用到了3个
c3用于排序，勉强用了一部分

c2已经是常量了，所以不会出现filesort

groupby分组之前必排序

定值、范围还是排序，一般order by是给个范围
group by基本上都需要进行排序，会有临时表产生

一般性建议：

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

全值匹配我最爱，最左欠窀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
like百分写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不可丢，Sql高级也不难！

查询截取分析

explain

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

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

查询优化

永远小表驱动大表，类似嵌套循环nested loop：

order by关键字优化：
order by子句，尽量使用index方式排序，避免使用filesort方式排序



什么时候会产生fielsort什么时候不会产生filesort

mysql支持两种方式的排序：filesort和index,index效率高，它指mysql扫描索引本身完成排序，filesort方式效率较低。
order by满足两情况，会使用index方式排序：order by语句使用索引最左前列；使用where子句和oeder 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)

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

慢查询日志

是什么：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;

如果要永久生效，就必须修改配置文件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

使用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

日志分析工具mysqldumpslow：
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，mysql提供了日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息
s:是表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后面搭配一个正则匹配模式，大小写不敏感的

工作常用参考：

批量数据脚本

往表里插入1000W数据


1.建表

2.设置参数log_bin_trust_function_creators

3.创建函数，保证每条数据都不同。随机产生字符串、随机产生部门编号。


4.创建存储过程。创建往emp表中插入数据的存储过程、创建往dept表中插入数据的存储过程。

5.调用存储过程。dept、emp。

show profile

是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql的调优的测量。

默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

分析步骤：
1.是否支持，看看当前的mysql版本是否支持

2.开启功能，默认是关闭，使用前需要开启

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:

6.日常开发需要注意的结论

全局查询日志

只允许测试环境使用

配置启用

编码启用

永远不要在生产环境开启这个功能

mysql锁机制

数据库锁概述

定义：锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库中，除传统的计算资源（如：cpu ram i/o等）的争用之外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类：

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

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

三锁

表锁 （偏读）
特点：偏向myisam存储引擎，开销小、加锁快；无死锁；锁定粒度大、发生锁冲突的概率最高，并发度最低

加读锁

阻塞了！！

加写锁

阻塞

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

由于行锁支持事务，复习老知识
事务及其ACID属性

并发事务处理带来的问题：更新丢失、脏读、不可重复读、幻读




事务隔离级别：

案例分析
建表sql

单值索引

行锁定基本演示

无索引行锁升级为表锁

间隙锁危害




宁可错杀，不可放过！

面试题：常考如何锁定一行？

select xxx for update;锁定某一行后，其他操作会被阻塞，直到锁定行会话提交commit

案例结论

innodb存储引擎由于实现了行级锁定，虽然在锁定

行锁分析

优化建议

页锁

八个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

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

视图 view

触发器

事件

定时备份

主从复制

redis也有主从复制

复制的基本原理：slave会从master读取binlog来进行数据同步
三步骤+原理图

mysql复制过程

复制的基本原则：每个slave只有一个master；每个slave只能有一个唯一的服务器id；每个master可以有多个slave

复制的最大问题：延时

一主一从常见配置：
mysql版本一直且后台以服务运行
主从都配置在mysqld节点下，都是小写
主机修改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=需要复制的主数据库名字

从机修改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