执行流程

mysql执行大概分为4层，client客户端发起请求到达service服务器。连接层，服务层，引擎层，存储层。

innodb：锁的范围小，并发高（并发问题少）

myISAM：表锁，开销小，加锁块，无死锁(锁冲突概率大)。myISAM在执行查询时，会自动给涉及的所有的表加读锁。在执行更新操作(DML前)自动加写锁， 读锁，都可以读，但只有有锁的人可以写改。 写锁，有锁就可以读和写，其他人不行。

第一章-优化概述

缓存机制https://www.cnblogs.com/yueyun00/p/10898677.html#1-mysql%E7%BC%93%E5%AD%98%E7%AE%80%E4%BB%8B

1优化的方面

执行慢的原因：性能底、执行时间长、等待时间长、SQL语句繁重(连接查询多)、索引失效、服务器参数设置不当。

MySQL架构总览->查询执行流程->SQL解析顺序https://www.cnblogs.com/annsshadow/p/5037667.html

SQL语句的编写过程和解析过程

编写： select distinct ..from .. hoin ..on ..where ..group by..having..order by ..limit

解析： from ..on ..join..where…group by..having…select distinct..order by..

MySQL进行优化主要就是在于==优化索引==

  • 相当于书的目录，在mysql中英文为index
  • index帮助Mysql高效获取数据的数据结构。
  • index是一种数据结构（主要是树：mysql使用的是B树索引，小左大右）

索引弊端

1、索引本身就很大，可存放在内存/硬盘(通常为硬盘) 2、这几种情况不适用：少量数据、频繁更新字段、很少使用的字段 3、索引可提高查询效率，降低了增删改的效率。(索引页维护需要成本)

索引优点：提高查询效率。

2索引分类创建

索引分类

1 )单值索引:即一个索引只包含单个列,一个表可以有多个单列索引 2 )唯一索引:索引列的值必须唯一 ,但允许有空值 3 )复合索引:即一个索引包含多个列(相当于二级目录，z:zhao zhang)

若一个列是主键，那么这个列自动为主键索引，不能重复，不能为null

是DDL语言，会自动提交。

**索引创建：**方式一 create 索引类型 索引名称 on 表(字段)

1创建单值索引 create index dept_index on staff(dept);

2创建单值索引 create unique name_index on staff(name);

3复合索引 create index dept_name_index on staff(dept,name);

**索引创建：**方式二 after table 表名 add 索引类型 索引名(字段)

1创建单值索引 after table staff add index dept_index(dept) ;

2创建单值索引 after table staff add unique name_index(name);

3复合索引 after table staff add index dept_name_index(dept,name);

索引删除：drop index 索引名 on 表名

查询索引：show index from 表名

3explain分析

explain用法和结果分析https://blog.csdn.net/why15732625998/article/details/80388236

分析执行计划：explain+SQL语句，模拟SQL优化器执行SQL语句

Mysql查询优化可能会干扰我们的优化。 官网的优化说明，在mysql官网文档中optimization(最优化)项

使用explain +SQL查询语句的字段解释：

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra

==id==:执行顺序，Id越大，执行顺序越前。 若id相同，从上往下顺序执行。(id大小和表的行数有关，因为返回结果是笛卡尔乘积5*4*3会被mysql优化为3*4*5)

==select_type==:用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。 SIMPLE 简单的select查询，查询中不包含子查询或者UNIONl(连接查询)

PRIMARY 查询中若有子查询，最外层查询则被标记为PRIMARY

SUBQUERY 查询中若有子查询，子查询被标记为SUBQUERY

DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

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

UNION RESULT 从UNION表获取结果的SELECT

==table==指的是当前执行的表

==type==表示查询使用哪种类型，从最好到最差依次为： system > const > eq_ref > ref > range > index > all 一般来说，要保证查询至少达到range级别。最好达到ref system 表只有一行记录（且是系统表），或衍生表只有一条数据的primary主查询

const 表示通过索引一次就找到了,只用于primary key 或者unique索引。

eq_ref 唯一性索引扫描，对于每个索引键，表中有且只有一条记录与之匹配（不能为0）。常见于主键或唯一索引扫描。

ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。比如查询name为xx

range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。（in有时会失效，比如in中的id不存在时）

index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

all Full Table Scan 将遍历全表以找到匹配的行

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

==key_len==表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。(若数据库使用的utf8编码，那么一个字符为3个字节，char(10)，key_len就是30，该列可为Null情况下为31，MySQL用一个字节表示null,用两个字节标识可变长)

==ref==：指明当前表参照的字段，select …where a.c=b.x;(其中b.x可为常量,const。若显示为Null则可能参照的字段没有索引)

==rows==：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

==filtered==：指返回结果的行占需要读到的行(rows列的值)的百分比

==extra==：包含不适合在其他列中显式但十分重要的额外信息。 useing filesort，使用文件排序，查询后再进行一次额外的查找或排序。（对于单索引，若排序和查找是同一字段，则不会出现。对于复合索引来说，比如该索引由abc三字段组成，不能跨列。可查询a1，order by a2就不会出现filesort）

Using temporary，用到了临时表，性能损耗同样比较大。(尽量查询什么,where什么，就order by 什么)。 解析过程：from ..on ..join…where ..group by…having….select..distinct..order by limit…

**using index,**性能提升，索引覆盖(覆盖索引)，不读取原文件，只从索引中获取数据。 (不需要徽标查询)

using where (需要回表查询)

impossible where ：where字句永远为false，比如select * from where a='a’ and a='b’

4索引优化

复合索引

create index dept_name_age_sex_index on staff(dept,name,age,sex);

where后面的条件要和order by 拼接起来，这样效率更高。(where后条件+order by 尽量和复合索引顺序相同) 推荐写法：select dept,name,age,sex from staff where dept='1’ and name='a’ and age='15’ order by sex 不推荐写法：select dept,name,age,sex from staff where name='张三’ and sex='女’ and age=15 order by dept

小结：最佳左前缀（where,把最可能生效放在where最前面，条件和order尽量和复合索引相匹配，哪怕部分），索引的优化是逐步的，where时把含in的范围查询放在最后,因为In可能失效。

** 连接查询时,数据少的放左边,加索引的话为少的加索引**

select * from teacher t left join teacherCard tc on t.tcid=tc.tcid; t.tcid条数小，就放左边。(类似程序的双重for循环，外层循环越小，性能越高)

5索引失效

避免索引失效的原则：

1复合索引：

  • 复合索引，不要跨列或者无序号使用。(最佳左前缀a,b,c。若a失效，则bc也失效，若c失效，对ab没影响)。
  • 复合索引，尽量使用全索引匹配，而不是部分。
  • 复合索引不能使用不等于(!= <>)或is null(is not null)，否则自身以及右侧索引全部失效。(不是100%出现情况，因为MySQL，sql优化器可能会帮忙优化)
  • 复合索引若有>、<，in范围查询，则之后的索引失效(索引优化，是大部分情况下适用的结论，由于sql优化器，结论不是100%)。

正确做法：尽量使用索引覆盖(using index)，| create a,b,c from xx where a=xx and b=xx ..

2索引使用：

  • 勿再索引上进行任何操作(计算，函数，类型转换)，否则索引会失效。 比如，select …where T.a*3=15
  • 对于复合索引来说，若where 第一个索引条件失效，那么后面的复合索引都将失效。
  • like尽量以"常量"开头，不要以%开头，否则索引将失效， 若一定使用%x%，可以使用索引覆盖挽回一部分性能(索引中直接有数据，不需要再回表查询)
  • 尽量不要使用类型转换(显示，隐式)，都会让索引失效
  • 尽量不要用or，否则索引失效。
  • exist和in 都会使索引失效 主查询数据集大，用in，效率更高。 子查询结果集大，用exists，效率更高。(把主查询结果放到子查询结果中进行校验，看子查询是否有数据，就校验成功。否则失败 )
  • order by优化 using filesort有两种算法：双路排序，单路排序(根据IO次数) 4.1版本默认双路排序，但这样IO两次。单路排序若Buffer不足，则会变为单路排序，可以调整Buffer大小。
  • 避免用select *
  • 保证全部的排序字段，(都是升序或都是降序,order by name asc,age desc这样不好。)
  • SQL排查-慢查询日志：Mysql提供的日志记录，用于记录MySQL响应超过阈值的SQL语句(long_query_time、可以临时改变和永久改变)，默认关闭，建议开发调优时打开，部署上线时关闭。 检查是否开启：show variable like ‘%show_query_log’ 临时开启(常用)：set global slow_query_log=1;——内存中开启 永久开启：/etc/my.cng的mysqld追加 slow_query_log=1 、slow_query_log_file=/xx/xxxx.log

xx

查看详细

set profiling=1; (未来高版本可能消失)

image-20200429225201845

1
2
3
4
5
6
7
8
9

--!如何查看优化的  

--！mysql -u root -p [指定数据库名，或者进入后 use 数据库名;] 
set profiling=1;--！查询后，输入show profiles查看执行时间;或show profile;查看详细情况

show profiles;解释展示上一条sql语句耗时，以及展示近期发送的sql语句
show profile [for query sql语句序号];详细展示发送sql各部分耗时

--!若想下次会话也有效 可以在etc/mysql.cnf添加profiling=1（没测试过。）

第二章-SQL优化

  1. 查询保证列独立(age就是age，不要有其它的)✔️ 否则导致索引失效,函数也会导致索引失效

    1
2

    --错误示范
select * from emp where empno+1=1234567 ;

  2. 左原则 Like：匹配模式必须要左边确定不能以通配符开头。 左边确定，不能是通配符，否则索引失效

    1

    select * from emp where ename 1ike 'abc% '

  3. OR的使用 必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引。(若有一个不存在，则无法使用索引)

  4. 查询字段，用到哪些查询哪些。避免*。