MySQL性能优化

在数据库优化上有两个主要方向：

即安全与性能。

安全 ---> 数据安全性

性能 ---> 数据的高性能访问

性能优化方向进行介绍：

数据库优化分为四个纬度：

硬件，系统配置，数据库表结构，SQL及索引

硬件：可能是CPU不行，好的服务器和差的服务器性能肯定不一样

系统配置：服务器系统、数据库服务参数、给内存的大小

数据库表结构：分库，分表，表设计（表的字段不能太多，字段多的表尽可能的拆分成多个表可以提高性能）

sql的优化：索引；引擎；语句

数据库优化思路：

1：应急调优思路（项目已经在跑了，不能关服务器）

针对突然的业务办理卡顿，无法进行正常的业务处理，需要立马解决的场景

首先：1.show processlist（查看已经连接到数据库的所有连接对象）

发现有阻塞的执行时间比较长的就可以查看一下

第二步：2：通过explain #查看session状态（分享查询的语句有什么问题）

第三步：根据查询出的信息判断是不是索引的问题还是语句本身的问题

第四步：show status like '%lock%'; # 查询锁状态（查看是不是锁有问题，可能出现死锁等 ）

第五步： SESSION_ID; # 杀掉有问题的session

2：常规调优思路（治本的）

针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。

1：查看slowlog（慢查询日志--记录超过查询阈值的sql，可以设置它的timeout=3（如果sql的执行时间大于三秒就记录到这个日志里）），分析slowlog，分析出查询慢的语句。

2：找到之后，进行一个一个的排查所有慢语句。

3：通过explain进行调试，查看语句执行时间。

4：最后再调整索引或语句本身。

sql优化：（索引；引擎；语句）

索引分类：

主键索引：唯一且不能为null，没有实际含义代表着数据的排序和唯一标识，索引最快

UNIQUE唯一索引：唯一且不能为null，比如身份证，名字等字段

NORMAL普通索引：最基本的索引，没有任何限制，一般查询多的字段可以给它构建索引

SPATIAL空间索引：mysql 5.7 版本后引入的新索引类型（以图形记录数据）

FULLTEXT全文索引：可以做全文检索，搜索引擎的一种，目前es做搜索引擎会更好，所有它基本不用

联合索引：把几个列一起做索引（不是索引类型，它是索引的使用情况）

索引的存储结构：

为什么添加索引后，查询效率变的很快？

因为索引就是建立一种存储结构，它默认是B+树（BTree）

查询速度快，更适合范围查询

默认是btree，而不是hash，hash主要用在内存储存引擎，通过hash算法计算将要存入那个索引

hash只能做等值匹配，在模糊索引很慢（哈希值不同，它只能一个个去比对），做范围查询也不行

索引无法排序

索引的使用可以增加查询的速度，但是损失写的速度

面试小问题：怎么决定那些列需要使用索引？

1：主键自带索引

2：经常作为查询条件的要建立索引（where后面的或order by后的）

3：作为排序的列

4：高并发的情况下，建立组合索引

5：用于聚合函数的列（count）

不建议使用索引的情况：

1：有大量重复的列不单独建立索引

2：表记录很少

3：不用于查询的列（增删改较多的列）

存储引擎优化：

把数据从mysql存入硬盘靠的就是存储引擎

 

InnoDB：支持事务的；支持行锁（可以将一行数据锁起来），表锁；支持外键

使用场景：增删改较多；支持事务必选InnoDB；

MyLSAM：没有事务（效率高）；只支持表锁；没有外键（无法做连表）

使用场景：查询多用mylsam

memory和mylsam几乎一样

sql语句优化：

就是如何防止sql出现全表扫描？

全值匹配效率最高，遵守最左前缀，带头大哥不能四，中间也不能断（例如用a,b,c做组合索引，前三种走索引性能很高；where c 就很慢走全表扫描）

  

最后一个没有走复合索引，走的是单例索引

索引列上少写计算（加减乘除）

建立一个age索引：写了计算也不走

前面有了范围，后面就不走索引了

走的是age的单例索引

Like百分写最右

覆盖索引不写

 

使用不等，空值，or索引会失效

  

总结：

要遵循最左前缀原则，复合索引首列不能没，索引列上不要用函数计算，like的%只能写最右边，尽量不用*，不使用不等，空值，or

分库/分表

sql结构上的优化

1：垂直拆分（读写分离）

2：水平拆分（mysql分片）

主键取模；日期拆分

上面两种都需要第三方技术实现：sharding-JDBC/mycat

3:表分区

mysql自带的

把数据一块一块的区分再存储

系统优化：

增加虚拟内存，更换性能较高的系统，例如：Linux，centos等（别用windows server）

硬件：

加内存，加cup，买服务器，固态硬盘。。。

面试题：

1：主键索引为什么比普通索引快？

非主键索引需要回表（查询两次，先查主键id再根据主键id查询数据；主键索引直接根据主键id查询出数据）

主键也叫聚簇索引

2：什么是最左前缀原则？

复合索引的时候，是从最左边开始匹配的

3：为什么用B+数做索引而不用hash表做索引？

hash主要用在内存储存引擎，通过hash算法计算将要存入那个索引

hash只能做等值匹配，在模糊索引很慢（哈希值不同，它只能一个个去比对），做范围查询也不行

索引无法排序

4：为什么建议使用主键自增的索引？

因为有序的排列会更快

如果不是主键自增，插入数据可能会导致数据重排，而主键自增是一直往后排，效率会更快

索引覆盖；索引下推

join关键字的作用？

join分为left join（左外连接：左表全部，右表匹配）right join（右外连接：右表全部，左表匹配）inner join（内连接：取两个表的交集）

笛卡儿积：查询出全部的数据，出现重复数据的存储，垃圾数据

join一般和on一起出现

什么是视图？

创建一张影子表，隐藏一些比较敏感的字段，修改影子表，主表也会一起修改

一定情况下索引越多，性能就一定高吗？

过多的索引会创建过的的B+数，会导致查询的性能反而会下降

插入10万条数据，如何让性能最高？

用JDBC原生的addBacth，来批量执行sql

事务特性？

A（原子性）C（一致性）I（隔离性）D（持久性）

如何实现原子性（事务如何回滚）？

数据库里有一个undo log表（回滚日志），记录执行的sql，如果需要回滚，就可以从表中取出来，反执行（insert，我就delete）

执行insert： mysql把执行的sql储存到undo lo表中，再把数据添加到表中，然后就是要么提交，要么回滚，提交就是删除undo lo表， 保存数据；回滚就是查询undo lo表，反执行sql删除保存好的数据

如何保证持久性？

采用re'do lo表来实现，当数据修改的时候，不仅在内存中操作，还会记录一份到redo lo，当事务提交的时候，会将redo lo数据刷到硬盘中（刷盘，redo lo一部分在内存中，一部分在磁盘上）。当数据库发生宕机重启后，会将redo lo中的内容恢复到数据库中

如何保证隔离性？

利用锁和MVCC机制（多版本并发控制）

如何保证一致性？

原子性，持久性和隔离性都满足了就能达到一致性

数据库的隔离级别

读已提交（项目中一般用的最多的）会引起不可重复读，幻读

读未提交，会引起不可重复读，幻读，脏读

可重复读（默认），会引起幻读

串行化，没有问题，但性能太低（一个一个的执行），Oracle数据库默认的引擎

脏读：一个事务读取另外一个事务更新的数据，然后那个事务回滚了，此时它读出的数据就是脏数据

（mybatis一二级缓存，开启二级缓存会读到已经删除的数据）

不可重复读：多次读取，读取到的数据不一致（事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致），并发修改的问题

幻读：读到不存在的数据（A将数据从分值改为等级，这个时候B插入了一条具体分值的数据，A修改结束后发现有一条数据没有修改）

不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

ps：在事务隔离级别为读已提交时，一个事务能读到另一个事务已提交的数据，这是不满足隔离性的。

但是事务隔离级别为可重复读时，是满足隔离性的。

java探针技术，idea的破解用的就是java的探针技术