MYSQL优化学习笔记-CSDN博客

本文链接：https://blog.csdn.net/qq_53202786/article/details/115854897

文章目录

前言
一、索引
二、查询截取优化
三、 MYSQL锁
- 1. 从对数据操作的类型:
- 2. 对数据操作的粒度划分:

前言

Mysql优化学习笔记

一、索引

1.索引是什么

索引是一种对一列或多列数据进行排序并能实现快速查找的一种数据结构.数据结构以某种方式指向数据,这样可以在数据结构中实现高级查找算法.

2.索引的优缺点

优点:提高了数据检索的效率,降低了数据库的IO成本.
通过索引对数据进行排序,降低了数据排序的成本,降低了CPU的消耗.
缺点:索引也是一张表,保存了主键和索引字段,并指向原表的记录,所以索引表也占有一定空间.
虽然索引提高了表的检索效率,但是也同时降低了表的更新速度,每次增删改会调整因为更新所影响的索引信息.

3.索引分类

单值索引:一个索引只包含单个列，一个表可以有多个单列索引
唯一索引:索引列的值必须唯一，但允许有空值
复合索引:一个索引包含多个列
基本语法:

	创建:
		CREATE [UNIQUE ] INDEX 索引名 ON 表名(字段名);’
		ALTER 表名 ADD[UNIQUE ] INDEX [索引名] ON (字段名)
	删除:
		DROP INDEX [索引名] ON 表名;
	查看:
		SHOW INDEX FROM 表名

4.索引结构

 BTree索引
 Hash索引
 full-text索引
 R-Tree索引

5.那些情况需要建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该建立索引
3. 查询中与其他表关联的字段,外键关系建立索引
4. 频繁更新的字段不要建立索引
5. where条件用不到的字段不建立索引
6. 排序字段,排序字段通过索引去访问提高排序速度
7. 查询中统计或者分组的字段

6.那些情况不需要建索引

 1.表的内容太少
 2.经常增删改的表
 3.数据重复且分布均匀的字段

7.explain

1.是什么 :模拟优化器执行SQL查询语句,从而知道MYSQL如何处理你的SQL语句
2.怎么用:explain+SQL语句
3.字段:

	 	id相同执行顺序从上到下
	 	id不同id越大优先级越高

select_type:

	 	SIMPLE:简单简单的SELECT语句（不包括UNION操作或子查询操作）
	    PRIMARY：查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表			  操作
	          			  为PRIMARY，内层的操作为UNION）
	    UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT
	    				语句没有依赖关系）    
	    DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）
	    SUBQUERY：子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）

table:表名
type :
all: 全表扫描
const:将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。

ref:找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_req:知道这种类型的查找结果集只有一个,使用了主键或者唯一性索引进行查找的情况
system: 单表单行.
range:划定范围,between and in < >.
index:全索引扫描.
prossible_keys 可能会选择的索引
key 实际选择的索引
key_len 索引的长度
ref 与索引作比较的列
rows 要检索的行数(估算值),这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.
Extra 额外信息:
Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.一般指无法使用索引进行排序

当加了where字段以后得到好几行相同的值,不需要排序.所以报Using filesort

去掉where或者用between and 就没有
当排序字段有索引,但是查询过程中并没有用到该索引.可以用组合索引来解决.
Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。建立索引时一定要按照个数顺序建立.
Using index:使用索引来直接获取列的数据，而不需回表,避免了访问数据行.效率高.

8.索引优化

单表查询优化

查询category_id=1 and comments > 1 view最多的数据可以看出这是全表查询并且有Using filesort 存在需要优化.
建立组合索引进行优化,发现全表扫描被避免了.但是Using filesort 还存在.这是因为comments>1的存在使索引失效.需要把它剔除.

优化成功.
双标联查
两个表全是全表查询.需要优化

当左外连接给左表建立索引时一个Index 一个全表查

当给右表建立索引时一个ref一个全表查

因此得出结论左外连接给右边建,右外连接给左表建.
三表联查

跟双表差不多左连接建右索引.

9.索引失效

最佳左前缀法则:如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。如果直接用后面的列会使索引失效.如果用到最左列,但是中间跳过一列会使从跳过的那列开始的索引失效.
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描.
存储引擎不能使用索引中范围条件右边的列,范围后面全失效.
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *.
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描.
is null ,is not null也无法使用索引.
like以通配符开头(’%abc…’ )mysql索引失效会变成全表扫描的操作.这个时候尽量用覆盖索引使全表扫描变成index.即查询的内容都在索引列中.
字符串不加单引号索引失效.会造成类型转换参照2.
少用or,用它来连接时会索引失效.

二、查询截取优化

1.慢查询优化

小表驱动大表.

	select * from A where id in (select id from B);
	当B小于A时,用IN优于exists.
	select * from A where id exists(select 1 from B where A.id=B.id);
	当B大于A时,用exists.
	EXISTS
	
	SELECT ...FROM table WHERE EXISTS (subquery)
	该语法可以理解为:将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE)
	来决定主查询的数据结果是否得以保留。

order by关键字的排序优化.
order by 使用索引最左前列.
使用Where子句与Order BY子句条件列组合满足索引最左前列.
尽量避免使用select* 当查询字段过大时会产生多次IO
尝试提高sort_buffer_size参数
提高max_length_for_sort_data2提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.
group by关键字优化.
能写where去限制的不要写having. 其他和order by一致.

2.慢查询日志

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启
SHOW VARIABLES LIKE ‘%slow_query_log%’;
使用set global slow_query_log=1;开启.
查看慢查询时间SHOW VARIABLES LIKE’long_query_time%’;大于这个时间的查询会被记录.
set global long_query_time=3; 设置慢查询时间.
show global status like ‘SLow_queries%’;查询慢查询条数

3.show profiles

用show profiles cpu block io for query 查看诊断日志
故障:
converting HEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table创建临时表
Copying to tmp table on disk把内存中临时表复制到磁盘.
locked
出现四种情况需要优化.

三、 MYSQL锁

1. 从对数据操作的类型:

读锁(共享锁):针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁):当前写操作没有完成前，它会阻断其他写锁和读锁。
资源

2. 对数据操作的粒度划分:

行锁(偏写):innodb 当加了行锁时修改某一行时,未提交时自己可以读到,别的读的时原来的数据.提交以后可以读到新数据.解决了脏读.修改某一行时不影响另外一行的读和写.
索引失效时会使行锁变表锁.
当用范围作为条件而不是相当条件查询时,如果范围中有不存在的值,可能会造成间隙锁,
这个时候再去插入不存在的那个值会发生阻塞.
如何锁定一行:

 	   begin ;
 	   select ....for update;
 	   commit;

表锁(偏读): 偏向MyISAM存储引擎，开销小，加锁快;无死锁;锁定粒度大，发生锁冲突的概率最高,并发度最低。
表锁加了读锁以后,自己只能查询被锁住的表,其他什么都不能干处于阻塞状态,而别的MySQL可以读被锁住的表,但是当修改时会进入到阻塞状态,当被锁住的表被释放锁之后.会立即执行修改操作.
表锁加了写锁以后,自己可以访问修改被锁住的表,但是不能访问其他表.别的MYSQL不能访问被锁住的表,当表锁被释放后,立即执行查询操作.
页锁
查看锁的等待情况
mysq1> 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:系统启动后到现在总共等待的次数;
行锁优化建议:尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围尽可能较少检索条件，避免间隙锁.
尽量控制事务大小，减少锁定资源量和时间长度.
尽可能低级别事务隔离