MySQl优化篇

常用语句

show VARIABLES like ‘%char%’; – 查看数据库字符编码

show ENGINES; – 查看数据库支持引擎

show variables like ‘%storage_engine%’ ; – 查看当前使用的引擎

show index from 表名 \G – 查看索引

delimiter $ – 设置语句以 $结尾

show open tables ; – 查看加锁的表

show status like ‘table%’ ; – 分析表锁定的严重程度：

show status like ‘%innodb_row_lock%’ ; – 行锁分析

show profiles ：会记录所有profiling打开之后的全部SQL查询语句所花费的时间。缺点：不够精确，只能看到总共消费的时间，不能看到各个硬件消费的时间（cpu、 io）

show variables like ‘%profiling%’; – 查看是否开始profiles

show variables like ‘%general_log%’; – 全局查询日志

show variables like ‘%slow_query_log%’ ; – 检查是否开启了慢查询日志

show variables like ‘%long_query_time%’ ; – 慢查询阀值：

show global status like ‘%slow_queries%’ ; –查询超过阀值的SQL

show master status; – 查看主数据库的状态

show slave status \G – 检查抽计算机

show variables like ‘server_id’ ;

基础知识

MYSQL逻辑分层 ：连接层服务层引擎层存储层

InnoDB(默认) ：事务优先（适合高并发操作；行锁）
MyISAM ：性能优先（表锁）

编写过程：
select dinstinct …from …join …on …where …group by …having …order by …limit …

解析过程：
from … on… join …where …group by …having …select dinstinct …order by limit …

SQL优化，主要就是在优化索引
索引：相当于书的目录
索引： index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B树(默认)、Hash树…）

索引的弊端：
1.索引本身很大，可以存放在内存/硬盘（通常为硬盘）
2.索引不是所有情况均适用： a.少量数据 b.频繁更新的字段 c.很少使用的字段
3.索引会降低增删改的效率（增删改查）

索引的优势：1提高查询效率（降低IO使用率）
2.降低CPU使用率

索引

分类：
主键索引：不能重复。id 不能是null
唯一索引：不能重复。id 可以是null
单值索引：单列， age ;一个表可以多个单值索引,name。
复合索引：多个列构成的索引（相当于二级目录： z: zhao） (name,age) (a,b,c,d,…,n)

创建索引

方式一：
create 索引类型索引名 on 表(字段)
单值：
create index dept_index on tb(dept);
唯一：
create unique index name_index on tb(name) ;
复合索引
create index dept_name_index on tb(dept,name);

方式二：

alter table 表名索引类型索引名（字段）

单值：
alter table tb add index dept_index(dept) ;
唯一：
alter table tb add unique index name_index(name);
复合索引
alter table tb add index dept_name_index(dept,name);

注意：如果一个字段是primary key，则改字段默认就是主键索引

删除索引：
drop index 索引名 on 表名 ;
drop index name_index on tb ;

查询索引：
show index from 表名 ;
show index from 表名 \G

explain属性

explain可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL状况

使用方式：explain select * from tb ;

属性：

id : 编号

id值越大越优先查询，id值相同，从上往下顺序执行

select_type ：查询类型

PRIMARY:包含子查询SQL中的主查询（最外层）

SUBQUERY：包含子查询SQL中的子查询（非最外层）

simple:简单查询（不包含子查询、union）

derived:衍生查询(使用到了临时表)

UNION：若第二个SELECT出现在UNION之后，则被标记为UNION若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION RESULT：从UNION表获取结果的SELECT

table ：表

type ：类型

system>const>eq_ref>ref>range>index>all （要对type进行优化的前提：有索引）

system（忽略）: 只有一条数据的系统表

const:表最多只有一行匹配 ,用于Primary key 或unique索引（类型与索引类型有关）

eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、也不能没有相匹配）

ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）

range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效，从而转为无索引all)

index：查询全部索引中数据（不用回表）

all：查询全部表中的数据（需回表）

possible_keys：预测用到的索引 （不准）

key：实际使用的索引

key_len：实际使用索引的长度

utf8:1个字符3个字节
gbk:1个字符2个字节
latin:1个字符1个字节

如果索引字段可以为Null,则会使用1个字节用于标识，2个字节标识可变长度

ref:表之间的引用

哪些列或者常量被用于查找索引列上的值

rows：通过索引查询到的数据量

Extra：额外的信息

using filesort ：性能消耗大；需要“额外”的一次回表排序（查询），复合索引不能跨列（最佳左前缀）

using temporary:性能损耗大，用到了临时表。一般出现在group by 语句中。

using where （需要回表查询）。

using index :性能提升; 索引覆盖（覆盖索引）。

impossible where ： where子句永远为false。

SQL优化

索引优化

最佳左前缀，保持索引的定义和使用的顺序一致性，将含In的范围查询放到where条件的最后，防止失效。

小表驱动大表，一般情况对于左外连接，给左表加索引；右外连接，给右表加索引

复合索引使用的顺序全部一致，where和order by 拼起来，不要跨列使用

不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。

复合索引中如果有>，则自身和右侧索引全部失效。

like尽量以“常量”开头，不要以’%’开头，否则索引失效，可以使用索引覆盖挽救一部分。

尽量不要使用类型转换（显示、隐式），否则索引失效

尽量不要使用or，否则索引失效

order by 优化

using filesort 有两种算法：双路排序、单路排序 （根据IO的次数）
MySQL4.1之前默认使用 双路排序；扫描2次磁盘，IO较消耗性能

1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序）

2：扫描其他字段

MySQL4.1之后默认使用 单路排序 ：只读取一次（全部字段），

在buffer中进行排序。但种单路排序会有一定的隐患（不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取、多次读取”。

注意：单路排序比双路排序会占用更多的buffer。
单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小： set max_length_for_sort_data = 1024 单位byte

如果max_length_for_sort_data值太低，则mysql会自动从单路->双路（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

**提高order by查询的策略：**除了b条建议，其他和之前基本一样

a.选择使用单路、双路；调整buffer的容量大小；

b.避免select * …

c.复合索引不要跨列使用，避免using filesort

d.保证全部的排序字段排序的一致性（都是升序或降序）

慢查询日志

慢查询日志:MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句（long_query_time，默认10秒）

慢查询日志默认是关闭的；建议：开发调优是打开，而最终部署时关闭。

检查是否开启了慢查询日志： show variables like ‘%slow_query_log%’ ;

检查是否开启了慢查询日志 ： show variables like ‘%slow_query_log%’ ;

临时开启：
set global slow_query_log = 1 ; –在内存种开启
exit
service mysql restart

永久开启：
/etc/my.cnf 中追加配置：
vi /etc/my.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log

慢查询阀值：show variables like ‘%long_query_time%’ ;

临时设置阀值：
set global long_query_time = 5 ; –设置完毕后，重新登陆后起效（不需要重启服务）

永久设置阀值：
/etc/my.cnf 中追加配置：
vi /etc/my.cnf
[mysqld]
long_query_time=3

select sleep(4);
select sleep(5);
select sleep(3);
select sleep(3);
查询超过阀值的SQL： show global status like ‘%slow_queries%’ ;

(1)慢查询的sql被记录在了日志中，因此可以通过日志查看具体的慢SQL。
cat /var/lib/mysql/localhost-slow.log

(2)通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件快速查找出需要定位的慢SQL
mysqldumpslow –help
s：排序方式
r:逆序
l:锁定时间
g:正则匹配模式

–获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

–获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

–按照时间排序，前10条包含left join查询语句的SQL
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/localhost-slow.log

语法：
mysqldumpslow 各种参数慢查询日志的文件

分析海量数据

分析海量数据:
（1）profiles
show profiles ; –默认关闭
show variables like ‘%profiling%’;
set profiling = on ;
show profiles ：会记录所有profiling打开之后的全部SQL查询语句所花费的时间。缺点：不够精确，只能看到总共消费的时间，不能看到各个硬件消费的时间（cpu io ）

(2)–精确分析:sql诊断
show profile all for query 上一步查询的的Query_Id
show profile cpu,block io for query 上一步查询的的Query_Id

(3)全局查询日志 ：记录开启之后的全部SQL语句。（这次全局的记录操作仅仅在调优、开发过程中打开即可，在最终的部署实施时一定关闭）
show variables like ‘%general_log%’;
–1、执行的所有SQL记录在表中
set global general_log = 1 ;–开启全局日志
set global log_output=‘table’ ; –设置将全部的SQL 记录在表中

–2、执行的所有SQL记录在文件中
set global log_output=‘file’ ;
set global general_log = on ;
set global general_log_file=’/tmp/general.log’ ;

开启后，会记录所有SQL ：会被记录 mysql.general_log表中。
select * from mysql.general_log ;

锁机制

解决因资源共享而造成的并发问题。

分类：
操作类型：
a.读锁（共享锁）：对同一个数据（衣服），多个读操作可以同时进行，互不干扰。
b.写锁（互斥锁）：如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作

操作范围：
a.表锁：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。
b.行锁：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。
c.页锁

增加锁：locak table 表1 read/write ,表2 read/write ,… ；或通过for update对query语句进行加锁。

释放锁: unlock tables ;

查看加锁的表：show open tables ;

分析表锁定的严重程度： show status like ‘table%’ ;
Table_locks_immediate :即可能获取到的锁数
Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)
一般建议：
Table_locks_immediate/Table_locks_waited > 5000，建议采用InnoDB引擎，否则MyISAM引擎

表锁：

===加读锁：

1、如果给A表加了读锁，则当前会话只能对A表进行读操作

2、如果给A表加了读锁，则其他会话能对A表进行读操作，写-需要等待释放锁

3、如果给A表加了读锁，则其他会话可以对其他表（A表以外的表）进行读、写操作

===加读锁：
	会话0：
		lock table  tablelock read ;
		select * from tablelock; --读（查），可以
		delete from tablelock where id =1 ; --写（增删改），不可以

		select * from emp ; --读，不可以
		delete from emp where eid = 1; --写，不可以
	会话1（其他会话）：
		select * from tablelock;   --读（查），可以
		delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放
	会话1（其他会话）：
		select * from emp ;  --读（查），可以
		delete from emp where eno = 1; --写，可以
		结论2：

===加写锁：

1、如果给A表加了写锁，则当前会话进行任何操作（增删改查）；但是不能操作（增删改查）其他表。

2、如果给A表加了写锁，则其他会话进行任何操作（增删改查），等待会话0释放写锁。

行表（InnoDB）
自动commit关闭; set autocommit =0；

1、如果会话x对某条数据a进行 DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后才能对数据a进行操作

2、区别于表锁是通过unlock tables，也可以通过事务解锁 ;行锁是通过事务解锁。

3、缺点：比表锁性能损耗大。优点：并发能力强，效率高。

注意：如果没有索引，则行锁会转为表锁

间隙锁： Mysql会自动给间隙（值在范围内，但却不存在）加索。即本题会自动给id=7的数据加间隙锁（行锁）。

例：update linelock set name =‘x’ where id >1 and id<9 ;

行锁分析：
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 ：等待次数。从系统启到现在一共等待的次数

主从复制

实现主从同步（主从复制）

master将改变的数记录在本地的二进制日志中（binary log)；该过程称之为：二进制日志件事

slave将master的binary log拷贝通过IO线程到自己的 relay log（中继日志文件）中，

中继日志事件，将数据读取到自己的数据库之中

MYSQL主从复制是异步的，串行化的，有延迟的，1:N关系。

配置： 
 	windows(mysql: my.ini)
  	linux(mysql: my.cnf)

配置前，为了无误，先将权限(远程访问)、防火墙等处理：
	关闭windows/linux防火墙： windows：右键“网络”   ,linux: service iptables stop
	Mysql允许远程连接(windowos/linux)：
		GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
		FLUSH PRIVILEGES;

主机（以下代码和操作全部在主机windows中操作）：
[mysqld]
server-id=1 #id

log-bin="D:/MySQL/MySQL Server 5.5/data/mysql-bin" 		#二进制日志文件（注意是/  不是\）
log-error="D:/MySQL/MySQL Server 5.5/data/mysql-error"  #错误记录文件

binlog-ignore-db=mysql 		#主从同步时 忽略的数据库
binlog-do-db=test 			#(可选)指定主从同步时，同步哪些数据库
查看主数据库的状态（每次在左主从同步前，需要观察主机状态的最新值）
show master status; （mysql-bin.000001、 107）

windows中的数据库授权哪台计算机中的数据库是自己的从数据库：
GRANT REPLICATION slave,reload,super ON . TO ‘root’@‘192.168.2.%’ IDENTIFIED BY ‘root’;
flush privileges ;

从机（以下代码和操作全部在从机linux中操作）：
my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=test

linux中的数据 授权哪台计算机中的数控 是自己的主计算机
CHANGE MASTER TO 
MASTER_HOST = '192.168.2.2', 
MASTER_USER = 'root', 
MASTER_PASSWORD = 'root', 
MASTER_PORT = 3306,
master_log_file='mysql-bin.000001',
master_log_pos=107;
如果报错：This operation cannot be performed with a running slave; run STOP SLAVE first
解决：STOP SLAVE ;再次执行上条授权语句

开启主从同步：
从机linux:
start slave ;
检验 show slave status \G

主要观察： Slave_IO_Running和 Slave_SQL_Running，确保二者都是yes；如果不都是yes，则看下方的 Last_IO_Error。
本次通过 Last_IO_Error发现错误的原因是主从使用了相同的server-id，检查:在主从中分别查看serverid:

show variables like ‘server_id’ ;
在Linux中的my.cnf中设置了server-id=2，但实际执行时确实server-id=1，

原因：可能是 linux版Mysql的一个bug，也可能是 windows和Linux版本不一致造成的兼容性问题。
解决改bug： set global server_id =2 ;
stop slave ;
 set global server_id =2 ;
start slave ;
 show slave status \G
演示：
主windows =>从

windows:
将表，插入数据  
观察从数据库中该表的数据