mysql优化

索引

索引数据结构

mysql底层使用的数据结构是B+树
以下几种索引不适用的原因:
二叉树:当递增插入的时候,容易形成链表式二叉树,不利于查询
红黑树:特殊的平衡二叉树,但是如果数据量很大,树的高度会很高,查询效率低
哈希索引:很多情况下哈希索引效率不逊B+树,但是不支持范围查找
B+树一个节点的大小默认为16kb,所以可以很好的控制树的高度

---

联合索引

最左前缀原则
假设创建col1、col2、col3三个列的联合索引
查询条件中必须包含col1(无所谓顺序),索引才可以生效
如果查询条件中没有col1,查询不会使用该索引

---

索引优化

---

like %123%优化

由于like前模糊会导致索引失效,所以并不提倡使用前模糊搜索.如果必须要使用,可以采取以下优化:
不要select * ,要查询有限的几个字段.确保查询的字段在联合索引树中全部存在,这样不需要回表操作,就会走联合索引,避免全表扫描.

---

大范围查找优化

当查找范围很大,结果数据非常多的时候,mysql可能会直接走全表扫描,此时可以考虑将范围拆成几段小一些的范围,分几次去查询.这样就会使用索引.

---

Order By && Group By 优化

1、MySQL支持两种方式的排序filesort和index(可以查看explain 查询结果中的Extra字段判断查询语句使用的是那种排序方式), Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
    1) order by语句使用索引最左前列。 (联合索引的最左列或者单字段索引).
    2) 使用where子句与order by子句条件列组合满足索引最左前列。(即排序字段是联合索引的第二个字段)
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引(即只查询联合索引覆盖到的字段)
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化:如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

Using filesort 文件排序的两种方式(了解):
单路排序:将符合条件的数据全部读到内存中排序(跟聚簇索引有些类似)
双路排序:将id和需要排序的字读读到内存中,排完序后再从硬盘中获取其他需要的字段(类似非聚簇索引)

---

分页sql优化

基于偏移量查询

使用前提:根据自增且连续的主键排序
使用方式:基于偏移量分页查询
注意:这个方法的弊端是必须要有自增且连续的字段,假设根据id排序分页,如果id中少了一个,逐页查询不受影响,页码跳转会受影响,缺失的id越多,跳转查询的数据偏差越大

连接查询

select * from employees e inner join (select id from employees order by name limit 9000, 5) ed on e.id = ed.id

解析:
如果直接用limit去select * ,联合索引不能当作覆盖索引,再加上他会先查询出来9005条数据,然后再舍弃前9000条,所以此时mysql会选择全表扫描.
如果使用上面的链接查询,先去查询id,这时联合索引就可以当作覆盖索引,走联合索引查出来5个id,然后再根据id去主键聚簇索引查询对应的5条数据,这条sql的效率会优化很多.如果公司禁止使用连接查询,可以考虑分两次查询(MyBatis支持单次执行多条sql,其他的orm框架不太清楚), 不过分两次查询的效率需要自己衡量相对于单次全表扫描哪个效率更高.

---

连接查询优化

mysql连接查询底层算法
优化方式:

• 被驱动表关联字段加索引,让mysql做join操作时尽量选择NLJ算法
• 小表驱动大表,写多表连接sql时如果明确知道哪张表时小表可以用straight_join写法固定连接驱动方式,省去mysql优化器自己判断的时间.
• straight_join:与join类似,但是能让左边的表驱动右边的表,能改表优化器对于连表查询的执行顺序.(只适用inner join,不适用left/right join)
• 大,小表的定义:参与关联条件的结果集的大小,即表中满足关联条件的数据量,而非表中的总数据量

---

in和exsits语句优化

本质也是连接查询,只不过是将查询条件构建成了一个临时表,所以优化策略基本同上.
in

 select * from A where id in (select id from B)

使用in的场景是查询条件(in 括号里面的数据)的数据量小于要查的表,如上,这样表B可以作为驱动表,此时只需要尽量使用覆盖索引去查询即可
exsits
当A表的数据集小于B表的数据集的时候,使用exsits

select * from A where exists (select 1 from B where B.id = A.id) 2
#等价于:  for(select * from A){ 
                select * from B where B.id = A.id 5 
            } 
#A表与B表的ID字段应建立索引

EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

---

count(*)优化

效率:count()=count(1)>count(id)>count(字段){如果字段有索引,则效率大于count(id))}
阿里开发规范:[强制]不要使用count(列名)或count(常量)来代替count(), count()是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关.(说明:count()会统计值为NULL的行,而count(列名)不会统计此列为NULL值的行)

• 如果是Myisam存储引擎,直接查询,因为他的数据条数存在磁盘中,不需要计算,但是Innodb存储引擎需要
• show table status 查看表数据中的ROWS字段,效率高但是数据不精确
• 将总数维护到redis中(很难保证表操作和redis操作的事务一致性)
• 增加数据库计数表,插入或删除数据行的时候同时维护计数表,让他们在同一个事务里操作

---

索引设计原则

1、代码先行，索引后上

应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。

2、联合索引尽量覆盖条件

比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

3、不要在小基数字段上建立索引

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段， 其值不是男就是女，那么该字段的基数就是2。 如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没 法进行快速的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查 找的优势来。

4、长字符串我们可以采用前缀索引

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。 对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，
就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。 此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name 字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来 完整的name字段值进行比对。
但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排 序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

5、where与order by冲突时优先where

在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到 底是让where去用上索引，还是让order by用上索引?
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。 因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可 能会小很多。

6、基于慢sql查询做优化

可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。 关于慢sql查询不清楚的可以参考这篇文章：https://blog.csdn.net/qq_40884473/article/details/89455740

---

事务隔离级别与锁机制

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。

一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。

隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

事务隔离级别

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。

隔离级别脏读	(Dirty Read)	不可重复读(NonRepeatable Read)	幻读(Phantom Read)
读未提交(Read uncommitted)	可能	可能	可能
读已提交(Read committed)	不可能	可能	可能
可重复读(Repeatableread)	不可能	不可能	可能
可串行化(Serializable)	不可能	不可能	不可能

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

查看当前数据库的事务隔离级别: show variables like 'tx_isolation';

设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别

读未提交会读取到其他事务没有提交的数据,即脏读
读已提交读取n次数据,该时间段内其他事务对该数据进行修改导致几次读取的数据不一样,即不可重复读
可重复读在一个事务开始时会生成当前数据库的一个快照,其他事务修改数据时对本事务的快照数据不产生影响,并且在本事务中写数据时会使用行锁.
幻读:可重复读时,在本事务中可以更新其他事务新增的数据,然后再查询数据就可以查询出原本快照里不存在的数据

锁

手动加共享锁

SELECT * FROM employees WHERE id = 1 FOR SHARE;

手动加排他锁

SELECT * FROM employees WHERE id = 1 FOR UPDATE;

串行化的和隔离级别就是给sql自动加锁

锁分类

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁
• 从对数据操作的粒度分，分为表锁和行锁
• 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)，还有意向锁

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响，比如：select * from T where id=1 lock in share mode

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁，数据修改操作都会加写锁，查询也可以通过for update加写锁，比如：select * from T where id=1 for update

• 意向锁（Intention Lock）：又称I锁，针对表锁，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。
  意向锁主要分为：

意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。

意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

间隙锁(Gap Lock)

在MySQL的InnoDB存储引擎中，间隙锁是一种特殊的行锁，它锁定了一个范围内的行，而不是单个行。具体来说，间隙锁锁定了一个范围的索引键值之间的间隙（即"间隙"），以防止其他事务在该范围内插入新的行，从而避免了幻读问题.
假设使用

select * from student where id > 17 and id < 30 for update;

这个sql会对17到30范围内的索引加锁,17-30范围内不可以新增数据,id>17且id<=30的数据不可以修改(在另一个事务中).
如果将where后面的条件修改为id >= 17,则会将id=17的数据也锁住.
此时就是一个临键锁.

间隙锁是在可重复读隔离级别下才会生效。

临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个[17,30]的整个区间可以叫做临键锁。

无索引行锁会升级为表锁(RR级别会升级为表锁，RC级别不会升级为表锁)
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
session1 执行：update account set balance = 800 where name = 'lilei';
session2 对该表任一行操作都会阻塞住
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

读写锁之间的阻塞关系

当一个事务给某条数据添加读锁的时候,其他事务可以给该行数据加读锁,但是不可以加写锁,当加写锁的时候会被阻塞.(update语句会自动加写锁,但是select不会自动加读锁);
当一个事务给某条数据添加写锁的时候,其他事务给该事务添加读锁,写锁都会被阻塞.

行锁的锁定对象是索引

锁优化原则

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
• 尽可能低级别事务隔离

MVCC多版本并发控制机制

undo日志版本链与read view机制详解
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

在事务读取数据的那一刻,会生成一个read view(一致性视图),这个视图记录的是这一刻的事务状态,所有的未提交的事务会放到一个数组中,然后会取数组中最小的事务id作为min_id,取这一刻最大的事务id作为max_id.当读数据的时候会从版本链的末端逐个向前遍历,

• 如果发现遍历到的事务id比max_id大,说明生成快照那一刻,该事务还未开始,所以该事务对于读取语句来说是不可见的.
• 如果该事务id小于max_id,大于min_id,此时查看该事务id是否在read view视图数组中,如果不存在,则生成视图的时候该事务已经提交,数据可见.反之,当时事务尚未提交,数据不可见.
• 如果该事务id小于min_id,说明该事务已经提交,数据可见.

对于可重复读隔离级别,该read view视图在一个读取事务中始终不变,所以该隔离级别可以保证可重复读,如果是提交读隔离级别,该视图会一直更新,所以可以保证实时读取到最新commit的数据

mysql innodb引擎日志机制

here

---

mysql命令速查

查看SQL语句执行信息

--sql语句前加explain关键字,例如:
explain select * from student where address like "北京市%";
/*
  1、 type 反应查询语句的性能
  我们主需要注意一个最重要的的 type 的信息很明显地体现出是否用到了索引：
   type 结果值从好到坏依次是：
  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL
  eq_ref:主键连接查询
  ref:普通索引或者唯一索引的部分前缀
  range:走索引的范围查询
  一般来说，得保证查询至少达到 range 级别，最好能达到 ref 级别，否则就可能出现性能问题。
  2、possible_keys: SQL查询时用到的索引。
  3、 key 显示SQL实际决定查询结果使用的键(索引)。如果没有使用索引，值为NULL
  4、rows 显示MySQL认为它执行查询时必须检查的行数
  5、selete_type 表示对应行是简单查询还是复杂查询
      simple:简单查询
      primary:复杂查询
*/

查看建表语句

show create table `table_name`;

创建索引

--创建单个索引：
create index 索引名 on 表名（字段名）
--创建联合索引：
create index 索引名 on 表名（字段名1，字段名2）

操作表锁

--手动增加表锁
lock table 表名称 read(write),表名称2 read(write);

--查看表上加过的锁
show open tables;

--删除表锁
unlock tables;

查看行锁争夺情况

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: 系统启动后到现在总共等待的次数(重要)
*/

示例:

查看事务和锁的情况

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

-- 查看锁等待详细信息
show engine innodb status\G; 

示例:

死锁情况

show engine innodb status\G; 
--大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

数据库备份与恢复

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

mysql -u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test

mysql全局优化和8.0新特性

here
概览:
1、新增降序索引

2、group by 不再隐式排序
mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。

3、增加隐藏索引

4、新增函数索引
之前我们知道，如果在查询中加入了函数，索引不生效，所以MySQL 8引入了函数索引，MySQL 8.0.13开始支持在索引中使用函数(表达式)的值。

5、innodb存储引擎select for update跳过锁等待

对于select ... for share(8.0新增加查询共享锁的语法)或 select ... for update， 在语句后面添加NOWAIT、SKIP LOCKED语法可以跳过锁等待，或者跳过锁定。
在5.7及之前的版本，select...for update，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。

在8.0版本，通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。

应用场景比如查询余票记录，如果某些记录已经被锁定，用skip locked可以跳过被锁定的记录，只返回没有锁定的记录，提高系统性能。

6、新增innodb_dedicated_server自适应参数
能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size等参数，会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题，前提是服务器是专用来给MySQL数据库的，如果还有其他软件或者资源或者多实例MySQL使用，不建议开启该参数，不然会影响其它程序。
7、死锁检查控制
MySQL 8.0 （MySQL 5.7.15）增加了一个新的动态变量 innodb_deadlock_detect，用于控制系统是否执行 InnoDB 死锁检查，默认是打开的。死锁检测会耗费数据库性能的，对于高并发的系统，我们可以关闭死锁检测功能，提高系统性能。但是我们要确保系统极少情况会发生死锁，同时要将锁等待超时参数调小一点，以防出现死锁等待过久的情况。

8、undo文件不再使用系统表空间
9、 binlog日志过期时间精确到秒
之前是天，并且参数名称发生变化. 在8.0版本之前，binlog日志过期时间设置都是设置expire_logs_days参数，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。
10、窗口函数(Window Functions)：也称分析函数
11、默认字符集由latin1变为utf8mb4
在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。
12、MyISAM系统表全部换成InnoDB表