MySQL高级

1 mysql隔离级别

读未提交：一个事务可以读取到另一个事务未提交的修改。这会带来脏读、幻读、不可重复读问题。（基本没用）

读已提交：一个事务只能读取另一个事务已经提交的修改。其避免了脏读，但仍然存在不可重复读和幻读问题。

可重复读：同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但幻读依然存在。

串行化：事务串行执行。避免了以上所有问题

​ 存储数据，建立索引，更新查询数据等技术的实现方式。

​ 基于表，表类型

1.存储引擎：

两引擎特点

InnoDB：

介绍: 用于事务的应用程序,支持外键,对事务的完整性有比较高的要求,在并发条件下要求有数据的一致性,数据操作除了插入和查询以外,还包含很多更新,删除.

**支持事务(**默认开始事务,如果手动开启事务,就要手动提交commit)，锁机制（行锁），支持外键。默认引擎

​ 优点：事务安全，支持外键

​ 缺点：相对于MyISAM，写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引

外键约束：

​ 在创建索引时，可以指定在删除，更新父表时，对子表进行相应的操作。

​ 包括：restrict、cascade、set null、no action

+ restrict、no action：子表有关联纪录时，父表不能更新。（ON DELETE RESTRICT）
+ cascade：父表更新时，子表也更新对应的记录。（ON UPDATE CASCADE）
+ set null：父表更新时，子表对应的字段被 set null

存储方式：（存储表和索引）

• 共享表空间 存储

​ 表结构： .frm 存储文件的后缀名

​ 数据和索引：Innodb_data_home_dir 和 innodb_data_file_path

• 多表空间存储

​ 表结构： .frm

​ 数据和索引：.ibd

MyISAM：

不支持事务，锁机制（表锁），不支持外键。访问速度快,对事务的完整性没有要求或者以select.insert为主的应用基本上都可以使用这个引擎来创建表,

存储方式：（存储表和索引）

.frm （存储表定义）

.MYD（MYData，存储数据）

.MYI（MYIndex，存储索引）

**MEMORY：**存在内存中,数据容易丢失,数据太大也不能存在内存中

.frm （存储表定义）磁盘中

数据文件（存储在内存中，默认使用hash索引） 高效

MERGE：

​ 是一组MyISAM表的组合，这些表的结构完全相同；MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部MyISAM表进行的。

2.优化SQL步骤：

2.2 sql优化原因

因为随着数据的增大,sql的性能也越来越低,所以优化sql语句,成为看整了系统的瓶颈.sql查询的次数一般是最多的

2.1 查看SQL执行频率

2.2 定位低效率执行SQL

+ 慢查询日志：
    + show processlist：show processlist命令查看当前sql在进行的线程（包括线程的状态、是否锁表等），可以实时查看sql执行情况，同时对一些锁表操作进行优化。

2.3 explain 分析执行计划 分析执行效率的问题

**id：**表示是查询中执行select子句或者操作表的顺序。（id 值越大，优先级越高，越先执行,id相同,表示加载顺序从上到下）比如先进行子查询,在进行外边的查询.

select_type：

+ simple：简单的查询，不包括子查询，UNION
    + primary：查询中包含子查询时，最外层为primary
    + subquery：在select或where列表中包含子查询
    + derived：在from列表中包含子查询，临时表
    + union：第二个select出现在union之后，则被标记为union
    + union result：从union表中获取结果的select

**table：**关于哪张表的

**type：**访问类型（重要指标）

+ null：不访问任何表，索引，直接返回结果。select now();
    + system：表只有一行记录（系统表），是const类型的特例，一般不会出现。
    + const：表示通过索引一次就能找到，const用于根据primary key 或者unique索引查询,返回一条数据。----------如将主键置于where列表中，Mysql就能将该查询转换为一个常量。const将“主键”或“唯一”索引的所有部分与常量值进行比较。
    + eq_ref：多表关联查询,结果只有一条记录
    + ref：根据非唯一索引查询，返回匹配某个单独值的所有行。本质上是一种索引访问。
    + range：只检索给返回的行，使用一个索引来选择行。where之后出现between，<，>，in等操作。
    + index：index与All的区别为index类型只是遍历索引树，通常比All快，All是遍历数据文件。
    + All：将遍历全表以找到匹配的行。

一般来说，需要保证查询至少达到range级别，最好达到ref。

key:

+ possible_keys：可能使用的索引。
    + key：实际使用的索引，如果为null，则没有使用索引。
    + key_len：表示索引中使用的字节数该值为索引字段最大可能长度，并非实际使用长度，在不损失精确度的前提下，长度越短越好。

rows：

​ 扫描行的数量。

extra：

​ 其他的额外的执行计划信息，在该列展示。

+ using filesort：mysql对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”。效率低,创建索引字段
    + using temporary：使用临时表（查询结果排序时使用临时表），常见于order by 和group by。效率低,创建索引字段
    + using index：使用了覆盖索引。

2.4 show profile 分析SQL

​ show profile能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

• show profile指令，查看SQL语句执行的耗时。

• show profile for query query_id指令，查看SQL执行过程中每个线程的状态和消耗时间。

  Sending data 状态表示mysql线程开始访问数据行并把结果返回给客户端。通常整个查询过程中耗时最长。

2.5 trace 分析优化器执行计划

作用:进一步了解优化器为什么选择A,而不是选择B

optimizer_trace=“enable=on”,end_markers_in_json=on;开始trace,返回json格式

3.索引的使用：

​ 索引是数据库优化最常用也是最重要的手段之一。

3.1验证索引提升查询效率

创建索引命令：

create index idx_seller_name_sta_addr on tb_seller(name, status,address)

3.2避免索引失效

• 全局匹配，对索引中所有列都指定具体值

• 最左前缀法则 查询字段在最左列

  ​ 查询从索引的最左列开始，并且不跳过索引中的列。(如果查询字段中间跳过某列,只执行最左边的索引字段)

• 范围查询右边的列，不能走索引
• 不要在索引列上进行运算操作，索引将失效 type为null,比如用subsring(name,2,3)截取字段名字查询
• 字符串不加单引号，造成索引失效
• 尽量使用覆盖索引（只访问索引字段查询,索引列完全包含查询列），避免select*,如果查询字段没有索引,会进行索引回表查询(再次用索引,在去表中查询)

extra：

​ using index：使用覆盖索引时会出现。

​ using where：未使用索引，回表查询所需数据。

​ using index condition：查找使用索引，回表查询数据。

​ using index；using where：使用索引，所需数据在索引中能够找到，不需回表查询。

• 用or 连接的条件（or前有索引，or后无索引），整个索引失效

• 以%查询,%在前面模糊查询，索引失效

  解决方案：通过覆盖索引来解决(查询字段有索引), 不管%的位置

• 如果MySQL评估使用索引比全表查询更慢，则不使用索引,跟数据库的数据有关,比如查询数据的比例比较大
• is NULL，is NOT NULL 有时索引失效,根据数据库中的数据mysql底层去评估,看查询字段null占主要还是not null 占主要
• in 走索引，not in 索引失效
• 单列索引和复合索引（尽量使用复合索引）,多个单列索引,mysql会根据最优的辨识度去选择合适的索引.

3.3查看索引使用情况

命令：

show status like 'Handler_read%';
show global status like 'Handler_read%';

4.SQL优化：

4.1大批量插入数据

​ 当使用load命令导入数据的时候，适当的设置可以提高导入的效率。

​ 对innodb类型的表，有以下几种方式可以提高导入的效率：

• 主键顺序插入,最好是有序的数据

  因为innoDB的类型表单是按照主键的有顺序排列,可以调高查询效率

• 关闭唯一性校验, 在load命令之前关闭唯一,load命令结束后再打开唯一

  set unique_checks=0;
  set unique_checks=1;

• 手动提交事务 在load命令之前关闭自动提交事务,load命令结束后再自动提交事务

  set autoconmmit=0;
  set autoconmmit=1;

4.2优化insert语句

• 同时对一张表插入多行数据时，尽量使用多个值表的insert语句

  insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');

• 在事务改为手动提交事务, 先start transaction 插入数据 最好 在commit,如果插入数据过大,可以每隔多少在提交一次
• 数据有序插入

4.3优化order by语句

• 两种排序方式

  1. 通过对返回数据进行排序，也就是filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。(返回全部数据或者包含没有索引的字段时候,用的是filesort排序)
  2. 通过有序索引顺序扫描直接返回有数据，这种情况即为using index，不需要额外排序，操作效率高。(返回只含有索引的字段),用的是using index 效率更加高
  3. 多字段排序.如果排序规则相同(都是升序),还需要和索引的顺序保持一致,用的是using index,否则是filesort,

​ 尽量减少额外排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。

• Filesort 的优化

​ 通过创建合适的索引，能够减少Filesort的出现。加快Filesort排序操作的两种排序算法：

1. 两次扫描法：mysql4.1之前使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能导致大量随机I/O操作。
2. 一次扫描法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销大，但是排序效率高。

mysql通过比较系统变量max_length_for_sort_data的大小和Query语句取出字段总大小，来判断使用哪种排序算法。如果max_length_for_sort_data更大，则使用第二种；否则使用第一种。

可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的效率。

4.4优化group by语句

group by操作实际上也同样会进行排序操作，与order by相比，group by主要只是多了排序之后的分组操作。

如果查询包含group by但是用户想要避免排序结果的消耗，则可以执行order by null禁止排序,和创建索引

4.5优化嵌套查询

多表连接查询比子查询更加的高效

-- 优化前
select * from t_user where id in (select user_id from user_role); 
-- 优化后
select * from t_user u,user_role ur where u.id = ur.user_id;

连接（JOIN）查询之所以更有效率一些，是因为mysql不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

4.6优化OR条件

不能使用到复合索引,只能用单列索引,or之间的每个条件列都必须用到索引

建议使用union替换or。 访问类型会从range 变为const

4.7优化分页查询

因为如果数据比较大,mysql会对分页开始数据查询的数据进行排序,然后丢弃,查询代价比较大

• 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

(先进行索引上完成排序的操作,然后根据返回值,在进行多表,关联回原来的表查询需要的内容)

• 该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询。

  select * from tb_item where id > 1000000 limit 10;

4.8使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

• use index (索引名字)

  在查询语句表名的后面，添加use index来提供希望mysql去参考的索引列表。

• ignore index (索引名字)

  忽略一个或者多个索引。

• force index

  强制mysql使用一个特定的索引。

5.MySQL-应用优化：

实际生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化。

• 使用数据库连接池，来提高访问的性能。

• 减少对MySQL的访问

  • 避免对数据进行重复检索

  • 增加cache层

    在应用中，可以增加缓存层来达到减轻数据库负担的目的。

    • 部分数据从数据库中抽取出来放到应用端以文本方式存储
    • 使用框架（Mybatis，Hibernate）提供的一级/二级缓存
    • 使用redis数据库来缓存数据

• 负载均衡

  负载均衡是应用中使用非常普遍的一种优化方法，它的机制是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来降低单台服务器的负载，达到优化的效果。

  • 利用MySQL复制分流查询

    通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。

  • 采用分布式数据库架构

6.查询缓存优化：

流程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Az9Rp0QE-1573721807486)(image/查询缓存流程.PNG)]

• 查看查询缓存的状态变量

  show status like 'Qcache%';

  • Qcache_hits 查询缓存命中数
  • Qcache_inserts 添加到查询缓存的查询数
  • Qcache_not_cached 非缓存查询的数量（由于query_cache_type设置而无法缓存或为缓存）

6.4 开始查询缓存

query_cache_type= on/1 打开查询缓存

​ off/0 关闭查询缓存

​ DEMAND/2查询缓存的功能需要制定SQL_CACHE,才会查询缓存,否则不缓存

6.5查询缓存select选项

• SQL_CACHE：缓存查询结果。
• SQL_NO_CACHE：不查询缓存，也不缓存查询结果。

6.6查询缓存失效的情况(可以从Qcache_not_cached ,和Qcache_hits 来判断查询缓存失效**)

• SQL语句不一致
• 当查询语句中有一些不确定的是，则不会缓存，如：now(), current_data(), curtime(), database()
• 不使用任何表查询语句 select ‘A’
• 查询mysql中原有的数据库时候,如mysql，information——schema或performance_schema数据库中的表时，不会走查询缓存
• 在存储的函数，触发器或者事件的主体内执行的查询
• 如果表更改(执行更新,增加,删除)，则使用该表的所有高速查询缓存都变为无效并从缓存中删除。

7.Mysql内存管理及优化：

7.1内存优化原则

• 将尽量多的内存分配给mysql做缓存，但要给操作系统和其他程序预留足够的内存。
• MyISAM存储引擎的数据文件读取依赖于操作系统自身的I/O缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做I/O缓存。
• 排序区，连接区等缓存是分配给每个数据库会话（session）客户端的每一个连接专用的，需要根据最大的连接数来合理分配。

7.2MyISAM的内存优化

mysiam存储引擎使用key_buffer缓存索引块，数据块并没有特别的缓存机制，完全依赖操作系统的I/O缓存。

key_buffer_size            (索引块缓存大小,一般内存的1/4)   
read_buffer_size        如果经常扫描的myisam表,可以增大该参数,但是这个参数是每个session独占的,如果设置值太大,会造成内存的浪费.
read_rnd_buffer_size    需要做排序操作时候,但是也是没有参数独占的session,会造成内存浪费

7.3InnoDB内存优化

InnoDB用一块内存区做IO缓存池，缓存 索引块和数据块。

Innodb_buffer_pool_size  设置缓存大小,在条件允许的情况下,越大越好,缓存命中率也越高,同时访问的磁盘IO也就越少,性能也就越高(默认128M)
Innnodb log_buffer_size  存储日志缓存大小

8.Mysql并发参数调整

在mysql中，控制并发连接和线程的主要参数包括：

• max_connections 控制允许连接到MySQL数据库的最大数量 默认151,根据cpu,内存调优
• back_log 控制mysql监听TCP端口时设置的积压请求栈大小,达到最大连接数后,就会存在堆栈中.默认80
• table_open_cache 用来控制sql语句执行线程可打开表缓存数量,每一个sql语句至少要打开一个缓存,它需要根据max_connections 来设置
• thread_cache_size 线程缓存大小,mysql会开始一个线程池,下次来直接从线程池中拿
• innodb_lock_wait_timeout 设置innodb事务等待行锁的时间 默认50s,比如需要快速响应时间,可以把时间调小,不需要一直等待

9.锁

9.1锁概述

锁是计算机协调多个线程或进程并发访问某一资源的机制（避免争抢）。

9.2锁的分类

对数据操作的粒度分：

• 表锁：操作时，会锁整个表。
• 行锁：操作时，会锁定当前操作行。

对数据操作的类型分：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。阻塞其他线程的写操作,不阻塞读操作

  同一客户端如果加读锁,必须释放,才能查询其他表数据,但是不同客户端可以查看.

  同一 客户端加读锁也不能更新,其他客户端去查询该表会处阻塞状态

  命令: 加读锁 lock table 表名 read 解锁: unlock tables

• 写锁（排它锁）：当前操作未完成之前，它会阻断其他写锁和读锁。

如果对表加写锁,其他客户端不能读,也不能写,都会处于阻塞状态

总结:读锁 会阻塞写,但不会阻塞读

​ 写锁 会阻塞读,也会阻塞写

mysql锁

mysql三种锁的特性：

• 表级锁：偏向myisam存储引擎，开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：偏向innodb存储引擎，开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度最高。
• 页面锁：开锁和加锁时间介于表锁和行锁之间；会出现死锁；锁粒度介于表锁和行锁之间，并发度一般。

9.3MyISAM表锁

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁。

• 读锁会阻塞写，但不会阻塞读。

• 写锁，既会阻塞读，又会阻塞写。

  MyISAM的读写锁调度是写优先。,不适合作以写为主的表的存储引擎的原因.因为写锁后,其他线程不能作为任何操作,大量更新会使查询很难得到锁,从而造成永久的阻塞.

9.4 锁问题

show open tables   查看表的锁使用情况 属性有:  in_use 表当前被查询使用的次数  Name_locked:表名称是否被锁定
show status like "table_locks%" 查看
属性:Tables_locks_iMmediate:能够获得立即表级锁的次数,每次立即获取锁加1
Tables_locks_waited:不能立即获取表级锁需要等待的次数每次等待加1,此值高说明存在比较严重的表级锁的争用情况

9.4InooDB行锁(默认支持行锁)

事务:

原子性,一致性,隔离性,持久性

InonDB

默认支持:共享锁和排他锁

1. 对与更新操作语句，InnoDB会自动给涉及数据集(当前操作的那一行数据)加排它锁。首先关闭自动,操作完在去提交
2. 对于普通查询语句，不会加任何锁。

无索引锁升级,行锁升级为表锁

:where 后加的条件 没有 索引或者索引失效

间隙锁:

**介绍:**当我们用范围条件,而不是相当的条件进行数据的更新操作的时候,mysql会对在范围内,但是不存在的记录加锁,间歇锁(Next-key) 当id<10,但是在id<10的当中,有数据不存在的情况

优化:尽量缩小范围条件

总结

InnoDB实现行锁锁定,虽然性能会损耗一些,但是整体的并发能力远大于Myisam表锁的,当系统的并发量比价高的时候,InnoDB的性能会高于Myisam,但是当我们使用不当的时候,可能会变得更差所以优化

优化建议：

• 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围。
• 尽可能减少索引条件，及索引范围，避免间隙锁。
• 尽量控制事务大小，减少锁定资源量和时间长度。
• 尽可能使用低级别事务隔离（但是需要业务层面满足需求）。

10常用SQL技巧

编写顺序

select from jion where group by having order by limit

正则表达式

regexp

select * from emp where name regexp ‘^ T’ 查询以T开头的记录

select * from emp where name regexp ’ T$’ 查询以T结束的记录

select * from emp where name regexp ‘[UVX] ’ 查询包含有UVX的记录

聚合函数

select count (id) from ta_user

11索引

11.1索引概述：

• 索引是帮助MySQL高效获取数据的数据结构（有一定顺序）。
• 储存在文件中
• 索引结构 B+tree(默认)

每一个索引节点 有一个指针指向主表中的数据

平衡二叉树存在问题:

• 太深了 数据的高度决定了他的io次数多,操作耗时,
• 它太小了 (每次携带的数据过小,因为每一次数据传输至少携带4k的值),
• 没有充分利用磁盘io的预读能力,交互数据能力

总结为何采用B+tree:

• B+树拥有B树的优势
• B+树扫库,表能力更加强
• B+树磁盘阅读能力更加强(磁盘内存连续,不用去增加寻道时间)
• B+树有更加好的排序能力(数据在子结点中数据是排序的)
• B+树查询效率更加稳定(因为数据全部在子节点中)

11.2索引的优势劣势：

优势：

• 提高数据检索的效率，降低数据库IO成本。
• 通过索引对数据进行排序，降低数据排序的成本。

劣势：

• 实际索引也是一张表，也要占用空间。
• 降低更新表的效率，在保存数据的同时，也要维护索引的数据结构。

11.3索引的结构InnoDB默认是BTREE索引

• 索引是在存储引擎中实现的。
• BTREE索引：最常见的索引类型，大部分索引都支持B树索引。
• BTREE树和二叉树相比，查询数据效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小。
• B+Tree的查询效率更加稳定
• MySQL索引数据结构是对B+Tree进行了优化

11.4索引的分类

1. 单值索引：一个值只包含单个列。
2. 唯一索引：索引列的值必须唯一，但允许有空值。
3. 复合索引：一个索引包含多个列。

11.5索引的设计原则

• 查询频次高，数据量大的表建立索引。
• 索引字段的最佳选择为where子句的条件中提取。
• 使用唯一索引。区分度越高,使用索引的效率也越高
• 合理建立索引，不是数量越多越好。
• 使用短索引。因为索引占用磁盘空间,降低访问效率

• 利用最左前缀。n个列组合而成的组合索引

  create index idx_name_email_status on tb_seller (name,email,status)
  相当于对建了三个索引
  name    创建索引
  name,email    创建索引
  name,email,status     创建索引