重新过了一遍MySQL优化,记录了这些笔记

重新过了一遍MySQL优化,记录了这些笔记

目录

1、原理：2、查询数据库引擎：3、索引:

分类：创建索引： 4、SQL性能问题：

查询执行计划： 5、优化案例

单表优化俩表优化多表优化 6、常见的SQL优化

（1）exist和in（2）order by优化 7、SQL排查：

1、慢查询日志2、慢查询阈值： 8、分析海量数据9、锁机制

分类：

（1）表锁 ：（2）行锁（InnoDB）

1、原理：

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

InnoDB(默认)：事务优先(适合高并发 *** 作：行锁)

MyISAM：性能优先（表锁）

2、查询数据库引擎：

支持那些引擎？show endines；

查看当前使用的引擎

show variables like ‘%storage_engine%’

3、索引: 分类：

主键索引：不能重复，不能为NULL

唯一索引：不能重复，可以为NULL

单值索引：单列值，一个表可以有多个单列索引

复合索引：多个列构成的索引

创建索引：

方式一：create 索引类型 索引名 on 表（字段）

方式二：alter table 表名 索引类型 索引名（字段）

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

4、SQL性能问题：

a.分析SQL的执行计划：explain ，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL性能

b.MySQL查询优化其会干扰我们的优化

查询执行计划：

explain + SQL语句

explain select * from tb；

ID：编号

ID值不同，id值越大越优先查询，（本质：在嵌套子查询时，先查内层，再查外层）

id值相同从上往下，顺序执行

**select_type：**查询类型

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

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

simple：简单查询（不包含子查询，union）

derived：衍生查询（使用到了临时表）

union ：

union result：告知开发人员，哪些表之间存在union查询。

table：表

**type：**索引类型，类型

system>const>eq_ref>ref>range>index>all（效率越往左越高）

其中，system,const只是理想状态，实际上能达到ref>range

system(忽略)：只有一条数据的系统表；或者衍生表只有一条数据的主查询

const：仅仅能查到一条数据的SQL,用于Primary key 或者unique索引（该类型与使用的索引类型有关，如果不是这俩个类型也没用）

eq_ref：唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（结果是多条，但是每条数据是唯一的）

常见于唯一索引和主键索引

ref：非唯一性索引，对于每个索引键的拆线，返回匹配的所有行（结果多条，但是每条数据是0或者多条）

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

index：查询全部索引中的数据，只需要扫描索引表，不需要所有表中的所有数据

all：查询表中的全部数据，需要全表扫描，即需要表中的所有数据

**possible_keys：**预测用到的索引

可能用到的索引，是一种预测，不太准确。

如果possible_keys/key为NULL，则说明没用索引

**key：**实际使用的索引

**key_len：**实际使用索引的长度

作用：用于判断复合索引是否被完全使用

注意：如果索引字段可以为NULL，则会使用一个字节用于标识。

**ref：**表之间的引用

注意和type中ref值进行区分，它是指当前表所参照的字段

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

被索引优化查询的数据个数（实际通过索引而查询到的数据个数）

**Extra：**额外的信息

using filesort：性能消耗大，需要额外的一次’排序’（查询）

using temporary：一般出现在group by 语句中；表示已经有表了，但是不适用，必须再来一张表。

using index：性能提升，索引覆盖

原因：不读取原文件，只从索引文件中获取数据，只要使用到的列，全部都在索引中，就是索引覆盖using index

如果用到了using index（索引覆盖），会对possible_keys和key造成影响：

a.如果没有where，则索引只出现在key中

b.如果有where，则索引出现在key和possible_keys中

5、优化案例 单表优化

优化：加索引

索引一旦进行升级优化，需要将之前废弃的索引删掉，防止干扰。

根据SQL实际解析的顺序，调整索引的顺序

小结：

a：索引不能跨列使用（最佳左前缀）

b：索引需要逐步优化

c：将含in的范围查询，放到where条件的最后，防止失效。

其中using where （需要回原表）；using index（不需要回原表），通过key_len可以证明索引是否失效

俩表优化

索引往哪里加？

–小表驱动大表

–索引建立在经常使用的字段上

using join buffer：extra 中的一个选项，（MySQL引擎使用了连接缓存）

多表优化

避免索引失效的一些原则

（1）复合索引

a.复合索引，不要跨列或无序使用（最佳左前缀）

b.复合索引，尽量使用全索引匹配

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

对于复合索引，如果左边失效，右边全部失效。

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

（4）补救，尽量使用索引覆盖（using index）

（5）like尽量以‘常量’开头，不要以‘% ’开头，否则索引失效。

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

（7）尽量不要使用or，否则索引失效。

6、常见的SQL优化

SQL优化是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。

体验概率情况（<>=）：原因是服务层中有SQL优化器，可能会影响我们的优化。

（1）exist和in

如果主查询的数据集大，则使用IN，效率高

如果子查询的数据集大，则使用EXIST，效率高

（2）order by优化

using filesort有俩种算法：双路排序，单路排序（根据IO的次数—IO较消耗性能）

双路：扫描2次磁盘（

1：从磁盘读取排序字段 —在buffer中进行的排序

2：扫描其他字段

）

MySQL4.1之前默认使用双路排序

MySQL4.1之后默认使用单路排序

在buffer中进行排序，但是单路排序会有一定的隐患（不一定真的是“单路|一次IO”，有可能是多次IO）

将所有字段，一次性读取完毕，因此会进行“分片读取，多次读取”

注意：单路排序会比双路排序会占用更多的buffer

单路排序如果数据量大的情况下，可以考虑调大buffer的容量大小：set max_length_for_sort_data =1

如果max_length_for_sort_data值太低，则MySQL会自动从单路->双路

提高order by 查询的策略：

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

b：避免select * …

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

d：保证全部的排序字段，排序的一致性（升序或降序）

7、SQL排查： 1、慢查询日志

慢查询日志：MySQL提供的一种日志记录，用于记录MySQL响应时间超过阈值的SQL语句

慢查询日志默认是关闭的（建议调休时开启，最终部署时关闭）

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

临时开启：

set global slow_query_log=1；—在内存中开启

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

2、慢查询阈值：

show variables like ‘%long_query_time%’;

临时设置阈值：

set global long_query_time=5;—设置完毕后，重新登录后起效（不需要重启服务）

永久设置阙值：

/etc/my.cnf 中追加配置：

vi /etc/my.cnf

[mysqld]

long_query_time=3

（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个次数
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

8、分析海量数据

分析海量数据：

（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%';
	
	--执行的所有SQL记录在表中
	set global general_log = 1 ;--开启全局日志
	set global log_output='table' ; --设置 将全部的SQL 记录在表中

	--执行的所有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 ;

9、锁机制

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

分类：

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

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

（1）表锁 ：

增加锁：

locak table 表1  read/write  ,表2  read/write   ,...

查看加锁的表：
show open tables ;

会话：session :每一个访问数据的dos命令行、数据库客户端工具  都是一个会话

加读锁：

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

		select * from emp ; --读，不可以
		delete from emp where eid = 1; --写，不可以
结论1：
		--如果某一个会话 对A表加了read锁，则 该会话 可以对A表进行读 *** 作、不能进行写 *** 作； 且 该会话不能对其他表进行读、写 *** 作。
		--即如果给A表加了读锁，则当前会话只能对A表进行读 *** 作。

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

会话1（其他会话）：
		select * from emp ;  --读（查），可以
		delete from emp where eno = 1; --写，可以
结论2：
总结：
会话0给A表加了锁；其他会话的 *** 作：a.可以对其他表（A表以外的表）进行读、写 *** 作
							b.对A表：读-可以；  写-需要等待释放锁。
释放锁: unlock tables ;

加写锁：

会话0：
		lock table tablelock write ;

当前会话（会话0） 可以对加了写锁的表  进行任何 *** 作（增删改查）；但是不能  *** 作（增删改查）其他表
其他会话：
		对会话0中加写锁的表 可以进行增删改查的前提是：等待会话0释放写锁

MySQL表级锁的锁模式

MyISAM在执行查询语句（SELECt）前，会自动给涉及的所有表加读锁，

在执行更新 *** 作（DML）前，会自动给涉及的表加写锁。

所以对MyISAM表进行 *** 作，会有以下情况：

a、对MyISAM表的读 *** 作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，

但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写 *** 作。

b、对MyISAM表的写 *** 作（加写锁），会阻塞其他进程（会话）对同一表的读和写 *** 作，

只有当写锁释放后，才会执行其它进程的读写 *** 作。

分析表锁定：

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

（2）行锁（InnoDB）

mysql默认自动commit; oracle默认不会自动commit ;

为了研究行锁，暂时将自动commit关闭; set autocommit =0 ; 以后需要通过commit

会话0： 写 *** 作
insert into linelock values(	'a6') ;

会话1： 写 *** 作 同样的数据
	update linelock set name='ax' where id = 6;

对行锁情况：
	1.如果会话x对某条数据a进行 DML *** 作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后  才能对数据a进行 *** 作。
	2.表锁 是通过unlock tables，也可以通过事务解锁 ; 行锁 是通过事务解锁。

行锁， *** 作不同数据：
会话0： 写 *** 作

	insert into linelock values(8,'a8') ;
会话1： 写 *** 作， 不同的数据
	update linelock set name='ax' where id = 5;
	行锁，一次锁一行数据；因此 如果 *** 作的是不同数据，则不干扰。

行锁的注意事项：

a.如果没有索引，则行锁会转为表锁
show index from linelock ;
alter table linelock add index idx_linelock_name(name);

会话0： 写 *** 作
	update linelock set name = 'ai' where name = '3' ;
	
会话1： 写 *** 作， 不同的数据
	update linelock set name = 'aiX' where name = '4' ;

会话0： 写 *** 作
	update linelock set name = 'ai' where name = 3 ;
	
会话1： 写 *** 作， 不同的数据
	update linelock set name = 'aiX' where name = 4 ;
	
--可以发现，数据被阻塞了（加锁）
-- 原因：如果索引类 发生了类型转换，则索引失效。 因此 此次 *** 作，会从行锁 转为表锁。

b.行锁的一种特殊情况：间隙锁：值在范围内，但却不存在
 --此时linelock表中 没有id=7的数据
 update linelock set name ='x' where id >1 and id<9 ;   --即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。
间隙：Mysql会自动给 间隙 加索 ->间隙锁。即 本题 会自动给id=7的数据加 间隙锁（行锁）。
行锁：如果有where，则实际加索的范围 就是where后面的范围（不是实际的值）

如何仅仅是查询数据，能否加锁？ 可以   for update 
研究学习时，将自动提交关闭：
	set autocommit =0 ;
	start transaction ;
	begin ;
 select * from linelock where id =2 for update ;

通过for update对query语句进行加锁。

行锁：
InnoDB默认采用行锁；
缺点： 比表锁性能损耗大。
优点：并发能力强，效率高。
因此建议，高并发用InnoDB，否则用MyISAM。

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