MySQL优化

1.如果需要储存千万级别的数据，可以先创建一个myisam储存引擎的表，插入之后再修改为innodb。
2.myisam表级别的锁限制了读写的性能，只支持表级别的锁，不支持事物，所以插入和查询的效率比较高；
3.5.7之后默认innodb搜索引擎，支持事务，支持表级别和行级别的锁，支持读写的并发，mvcc机制，写数据的时候不影响读；
4.写入的时候是redu log是顺序id，log buffer是内存缓冲区（默认是16m）；
mysql是如何执行一条sql语句的？
连接器-分析器/缓存（8.0之后直接废弃了）-优化器-执行器-储存引擎
连接器：连接管理，权限认证；
首先建立连接，获取权限和管理连接，会根据你的账号密码带出你的权限，你能做什么都依赖于你所拥有的权限，但是建立连接是一个很复杂的过程，也比较消耗性能，所以这里推荐使用长连接或者连接池工具进行对数据库的连接，比如说druid和c3p0；
在8.0之前会优先查询缓存中的数据，一旦击中就会直接返回数据，查询过程中会先校验之前是否执行过该语句，会以key-value的形式存在缓存中，一旦拥有就直接返回，如果没有就在执行完成后在放置缓存中供下次使用，但是这里有个缺陷，就是要是该表有更新和删除的动作，那么该表的缓存就会失效，并且索引也会失效，所以对于缓存会推荐第三方使用缓存，比如说mybatis等等；
分析器：词法分析，语法分析；
按照上续的流程，一旦没有击中缓存就会进入分析器，主要就是分析该sql语句的执行目的，主要分为两步：
词法分析：即为把该sql语句拆成每个单词，检查每个词是否属于该表正确的关键字，也就是提取关键字；
语法分析：分析你的这个sql语句的语法是否使用正确，比如说执行顺序，关键字的位置等等；
优化器：执行计划，索引选择；
做完优化器这一步，就大致知道该sql语句想要干嘛了，那么这时候就会根据该表的特性选择一条最优的执行策略，比如是否使用索 引，或者一条语句可以有多种执行路线，优化器会选择使用哪一条；
执行器：操作引擎，返回结果；
再次校验权限，根据上述的执行计划，返回相对应的结果，其中innodb在进行更新数据的时候还会涉及到两个日志模块的操作，binlog和redolog。

那么一条查询的sql语句是如何执行的：
连接器-缓存/分析器-优化器-权限校验-执行器-引擎
一条更新语句是如何执行的：
分析器-权限认证-优化器-权限认证-redo log-bin log

分析redolog和undolog和binlog：
undolog是用来记录每次执行的记录，用于回滚到某个版本。redolog也是用于恢复日志，但是仅仅只是用于记录数据页的物理修改，而不记录具体某一行修改成什么样了，只用于恢复最后一次

储存引擎：储存数据，提供读写操作。
mysql5.5.5之后使用innodb作为默认的储存引擎；

redolog的特点：相当于粉板的作用。
当进行更新写操作的时候，innodb引擎就会先把数据写到redolog日志里面，并更新内存，等到适当的时候再将改动写入磁盘中。但是当这个redolog写满的时候该怎么办呢，innodb的redoolog是固定大小的，可以分为4个文件夹，每个文件夹的内存大致为1G，写完所有的内存的时候，就从末尾写完之后就有从头循环写；
有了redolog，innodb就有了重启还能保证之前提交的数据不会丢失的，这个能力称之为crash-safe；

binlog的特点：
binlog是mysql的日志，所有引擎都可以使用，而redolog是innodb的专有日志；
redolog是物理日志，记录在某页数上做了什么动作，binlog是记录这个语句具体做了什么，一个是记录行为，一个是记录行为描述；
redolog是循环写入，binlog是追加写入，不会覆盖以前的日志。
区别于3者：
redolog重做日志：确保事务的一致性，确保在发生故障的时候脏数据能保存，重启数据库的时候达到事务的一致性；
undolog回滚日志：保存了事务发生之前的数据的一个版本，用于回滚，解决多版本并发下的读（MVCC）；
binlog二进制日志：主要用于主从同步。

描述：
1.一次sql语句执行动作，最好是以更新数据为例，如果是查询的话，要是在内存中击中就直接返回了，只有更新的时候才会有事务和日志
2.首先先加载缓存数据，一开始就把该值（旧值）写入redolog中，该操作主要用于回滚；
3.在缓存池中进行更新内存数据，此时该数据会在innodb中起一个线程写入磁盘文件，但此时还是脏数据，也就是说如果后面binlog状态没有变成commit状态，会产生回滚回去；（更新内存并刷入磁盘文件，这里就会存在一个并发的问题，MVCC解决）；
4.写入redolog日志记录此次事务刷入磁盘文件中，写入binlog日志到文件中，并修改状态，从prepare到commit状态；

所以在执行一条sql语句的时候，引擎先是用搜索引擎搜索到这一行的数据，如果是在内存中找到就直接返回，如果没有就从磁盘中查找，找到之后再写入内存中；更新加一操作，然后写入redolog日志中，然后告诉执行器可以执行了，然后写入binlog日志中，并写入磁盘中；事务将redolog的prepare状态修改为commit状态。

执行顺序为：
1.根据索引查出该条记录；
2.判断内存中是否存在，是立即返回，不是从磁盘中获取再返回；
3.执行+1操作，并写入内存中，此时redolog还是prepare状态；
4.写入binlog日志中，提交事务。
至于prepare和commit操作其实就是二阶段提交操作。

那么这里存在一个问题，刚开始说到的mysql可以恢复到近半个月的任意一秒的数据，这是怎么做到的呢？
这里所说的半个月其实是很binlog日志相关，他是追加的形式，只需要设置成半个月的日志记录即可；同时还会对整个库做整库备份，那么如果你想恢复几天前的一条删除的数据，从虚拟库中取出那条binlog数据恢复到主库即可，这里使用到的两阶段提交是为了数据的一致性。

**

工作中mysql常见的问题

**
为了优化mysql的性能，可以修改以下这些参数进行配置：
1.bufferPoolSize：使用缓冲技术，缓冲池是数据和索引缓存的地方，这个值可以粗狂点设置的大一点，使用缓冲池是优势，这里可以让每次查询是直接查询内存而不是从磁盘中获取；
另外还有一个缓存参数：poolPreparedStatements=true;这个是缓存游标，所谓游标就是每次查询或者更新都会创建游标去操作，使用游标的好处是把集合的操作转换成单个操作记录处理的方式；可以按照用户自己的意思去处理这些记录。游标的原理就是取出所有的结果集，然后按条进行处理，可以理解成批量操作，转换成逐条执行。
一些常见的mysql优化参数配置项：
1.back_log：就是说mysql达到max_connections时，新来的请求会被存放在栈中，如果这个配置还超过了，那就不授予连接资源了；

mysql.conf文件新增优化配置：

#避免MySQL的外部锁定，减少出错几率增强稳定性
skip-locking
MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求
期望在一个短时间内有很多连接，这个参数至关重要，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中
back_log = 512
指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)
key_buffer_size = 384M
max_connections = 10000
InnoDB使用一个缓冲池来保存索引和原始数据, 默认值为128M
innodb_buffer_pool_size = 256M
对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接
max_connect_errors = 6000
MySQL打开的文件描述符限制，默认最小1024
open_files_limit = 65535
MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64
table_open_cache = 128
接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。
该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。
max_allowed_packet = 4M
一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K
binlog_cache_size = 1M
MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小
read_buffer_size = 2M
MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，
MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。
但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大
read_rnd_buffer_size = 8M
MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。
如果不能，可以尝试增加sort_buffer_size变量的大小
sort_buffer_size = 8M
联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享
join_buffer_size = 8M
#指定单个查询能够使用的缓冲区大小，默认1M
query_cache_limit = 2M
限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
innodb_open_files = 500

druid配置：

spring.datasource.url=jdbc:mysql://192.168.0.246:6006/eyas?useUnicode=true&characterEncoding=UTF-8&useSSL=false&verifyServerCertificate=false&allowPublicKeyRetrieval=true&createDatabaseIfNotExist=true
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.initialSize=10
spring.datasource.minIdle=10
spring.datasource.maxIdle=20
spring.datasource.maxActive=100000
spring.datasource.maxWait=1000
移除空闲线程任务的间隔时间毫秒数
spring.datasource.timeBetweenEvictionRunsMillis=60000
移除空闲线程的最小时间毫秒数
spring.datasource.minEvictableIdleTimeMillis=250000
连接泄露检查，打开removeAbandoned功能 , 连接从连接池借出后，长时间不归还，将触发强制回连接。
回收周期随timeBetweenEvictionRunsMillis进行，如果连接为从连接池借出状态，并且未执行任何sql，并且从借出时间起已超过removeAbandonedTimeout时间，则强制归还连接到连接池中。
spring.datasource.druid.removeAbandoned=true
超过多长时间
spring.datasource.druid.removeAbandonedTimeout=180
指定发生removeabandned的时候,是否记录当前线程的堆栈信息到日志中
#spring.datasource.druid.logAbandoned=true
检测是否有效的查询
spring.datasource.validationQuery=select 1
设置从连接池获取连接时是否检查连接有效性，true时，如果连接空闲时间超过minEvictableIdleTimeMillis进行检查，否则不检查;false时，不检查
spring.datasource.testWhileIdle=true
设置从连接池获取连接时是否检查连接有效性，true时，每次都检查;false时，不检查
spring.datasource.testOnBorrow=false
设置往连接池归还连接时是否检查连接有效性，true时，每次都检查;false时，不检查
spring.datasource.testOnReturn=false
打开PSCache，并且指定每个连接上PSCache的大小，Oracle等支持游标的数据库，打开此开关，会以数量级提升性能，具体查阅PSCache相关资料
spring.datasource.poolPreparedStatements=true
要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,log4j
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
打开后，增强timeBetweenEvictionRunsMillis的周期性连接检查，minIdle内的空闲连接，每次检查强制验证连接有效性. 参考：https://github.com/alibaba/druid/wiki/KeepAlive_cn
spring.datasource.keepAlive=false

mybatis.mapper-locations=classpath:fmc/**/mapper/xml/*Mapper.xml

数据库的优化层面：
1.重启：线程数降不下去，关闭一些连接；
2.sql语句优化，索引优化；
3.表和存储引擎的选择，表的字段和优化；
4.数据库的架构进行优化，进行多个实例进行优化，比如说集群，一主一从等负载均衡，读写分离，加缓存；
5.进行分片管理，就是所谓的分库分表，mycat。
主从就是冗余，分库分表就是分片；
6.配置优化，修改mysql的cnf文件，修改druid的连接配置；
7.硬件层面，修改运行内存。

那么什么时候才要去做分库分表呢，也就是说表的量达到多少的时候才会去做？

对应用进行拆分有什么好处？更快的定位问题；
分表的方式：根据字段垂直切分，那么一张表多少个字段比较合适？100个字段就已经算是比较多了；
根据某个条件进行水平切分：
案例1：
1.比如当天的数据存放在A表里面，即当天的数据；
2.过了当天之后，把前一天的数据用定时任务移到B表中；
3.超过一月的数据，也用定时任务放置C表中，所以C表是最大的一张表，所以可以按照时间做一定的分区；
但是分库分表也会带来复杂性，会带来一定的问题：
跨库的关联查询，解决办法：
表设计的时候直接字段冗余的方式，就不需要进行关联（不推荐）；
通过ETL把数据同步的方式，避免跨库的关联；
基础数据不动的情况下，也可以做一个全局表；

分库分表存在的问题：
排序，分页，函数计算，分表之后的id重复性：也就是说在跨库的时候需要解决的问题；
分布式id生成器解决全局主键

多数据源的解决方案：

AbstractRoutingDataSource用于动态解决数据库的选择；

**

mysql面试题

**
索引在哪些情况下会失效：
1.查询条件包含or、like（但是%在后面会走range索引，在前面不会走索引）；
2.隐式转换会索引失效，比如where id = ‘1’，会从int类型转换成varchar类型；
3.索引字段进行运算和函数运算，或者使用not in、不等于和is null等一系列操作索引字段都会导致索引失效；

mysql什么情况下存在死锁，是怎么解决死锁的？
两个事务都持有对方需要的锁，双方都在等待的情况就会产生死锁，这个跟synronise是一样的情况；不过现在mysql的死锁情况一般比较少了，并发场景下有MVCC机制处理，不会直接进行锁定的方式去执行；如果真的有这个情况，可以查看死锁的日志：show engine innodb status；

生产上你是如何优化mysql的？
1.先从慢查询开始入手：
1> sql语句层面，一些常见优化的sql语法问题，连表和一些简化的sql语句;
2> 添加索引继续优化sql语句，explain解析语句达到没有什么优化空间；
2.查看mysql的状态，比如连接数和内存使用状态；
3.根据基本的参数对druid参数进行优化，比如说加缓存和索引缓存等一些基础的优化参数；
4.修改mysql.cnf文件，增加mysql的基本参数，添加缓存和最大连接数和等待时间等；
5.分库分表等操作，这里推荐使用mycat；
1> 水平分表和垂直分表；
2> 解决分库分表的事务问题，分布式事务问题
6.从设计层面减少对mysql的查询和使用；
7.多数据源查询的方式，或者启用缓存redis或者mongodb；

mysql为什么使用B+树，而不用B树？
1.当查询的数据量大时，B树的查询的树高度会很大，对多查几次IO，查询速度会比较慢；
2.B+树提供范围查找，序列和数据分开储存，查到叶子节点就是结果集，所以叶子节点不保存真实的数据，因此扫表和扫库的能力更强；

在并发情况下，如何做到安全的修改一条数据？
使用悲观锁的方式对行级数据进行锁定，这时候必须关闭mysql的autocommit=0的参数；

优化一条sql语句：
select DISTINCT * from A where project_id in (1200021，13203231);
1.索引字段要是没使用到位，就会导致虽然用到了索引，但是索引效果会很差；
2.用union all的方式去代替in的方式；
3.正确使用字段的数据类型，比如project_id是varchar类型的，那么就尽可能使用字符串的形势；
4.使用group by替换distinct；
5.最后还可以用多个独立查询代替in；

Druid参数详解：
1.minIdle：线程池最小空闲数，Druid会定期扫描连接数情况，如果扫描的值大于该值就关闭多余的连接数，小于就创建符合要求的连接数；这个参数的主要用处是突然有大量的请求的时候，就会创建新的连接数，这是个比较耗时的操作；
2.maxActive：线程池中最大连接数，
3.timeBetweenEvictionRunsMillis（默认60秒）：判断连接池的连接空闲数是否大于minIdle，如果是则关闭多余的连接数，少的就补上，（如果当前连接池中某个连接在空闲了timeBetweenEvictionRunsMillis时间后仍然没有使用，则被物理性的关闭掉）；
4.removeAbandoned（默认false）：如果连接泄露，是否需要回收泄露的连接；那么在getNumActive()快要到getMaxActive()的时候，系统会进行无效的Connection的回收，回收的 Connection为removeAbandonedTimeout(默认300秒)中设置的秒数后没有使用的Connection；
5.removeAbandonedTimeout(默认300秒)：设置druid 强制回收连接的时限，当程序从池中get到连接开始算起，druid将强制回收该连接，单位秒
6.logAbandoned：如果回收了泄露的连接，是否要打印一条log，默认false；
7.removeAbandonedTimeoutMillis（默认5分钟）：连接回收的超时时间；设置了removeAbandoned为true，Druid会定期检查线程池溢出的情况，如果不是运行状态，且超过设置的时间就会被回收；
8.testWhileIdle（默认false）是一个检测配置项：建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效；
9.testOnReturn（默认false）：建议配置为false，归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能；
10.testOnBorrow（默认true）：建议配置为false，申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
11.minEvictableIdleTimeMillis （默认30分钟）：连接保持空闲而不被驱逐的最长存活时间。Destory线程中如果检测到当前连接的最后活跃时间和当前时间的差值大于minEvictableIdleTimeMillis，则关闭当前连接。这个参数和timeBetweenEvictionRunsMillis参数有点冲突，可默认不管这个参数；
12.validationQuery：用来检测连接是否有效的sql，如果validationQuery为空，那么testOnBorrow、testOnReturn、testWhileIdle这三个参数都不会起作用，因为这三个参数都是通过执行参数validationQuery指定的SQL来验证数据库连接的有效性，配置参考validationQuery=SELECT 1
13.poolPreparedStatements：5.7之后支持游标，是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大；
14.maxPoolPrepareStatementPerConnectionSize：每个连接最多缓存多少个SQL；
15.keepAlive（默认关闭）：初始化连接池时会填充到minIdle数量，连接池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作，打开会一直保持minIdle的数量值；

Druid连接池流程：
1.数据库连接池在初始化的时候会创建initialSize个连接，当有数据库操作时，会从池中取出一个连接；
　　如果当前池中正在使用的连接数等于maxActive，则会等待一段时间，等待其他操作释放掉某一个连接，
　　如果这个等待时间超过了maxWait，则会报错；
　　如果当前使用的连接数没有达到maxActive，则判断当前是否空闲连接，如果有则直接使用空闲连接，如果没有则新建立一个连接。
　　在连接使用完毕后，不是将其物理连接关闭，而是将其放入池中等待其他操作复用。

2.同时连接池内部有机制判断，如果当前的总的连接数少于miniIdle，则会建立新的空闲连接，以保证连接数得到miniIdle。
　　如果当前连接池中某个连接在空闲了timeBetweenEvictionRunsMillis时间后仍然没有使用，则被物理性的关闭掉。
　　有些数据库连接的时候有超时限制（mysql连接在8小时后断开），或者由于网络中断等原因，连接池的连接会出现失效的情况，
　　这时候设置一个testWhileIdle参数为true，可以保证连接池内部定时检测连接的可用性，不可用的连接会被抛弃或者重建，
　　最大情况的保证从连接池中得到的Connection对象是可用的。当然，为了保证绝对的可用性，你也可以使用testOnBorrow为true（即在获取Connection对象时检测其可用性），不过这样会影响性能。

修改之后的配置：
//初始化的第一次连接参数
conf.put(DruidDataSourceFactory.PROP_INITIALSIZE, “10”);
//连接池中的最小空闲连接数，Druid会定时扫描连接池的连接，如果空闲的连接数大于该值，则关闭多余的连接，反之则创建更多的连接以满足最小连接数要求
conf.put(DruidDataSourceFactory.PROP_MINIDLE,“10”);
conf.put(DruidDataSourceFactory.PROP_MAXIDLE,“20”);
conf.put(DruidDataSourceFactory.PROP_MAXACTIVE,“100000”);
conf.put(DruidDataSourceFactory.PROP_TIMEBETWEENEVICTIONRUNSMILLIS,“180000”);
conf.put(DruidDataSourceFactory.PROP_REMOVEABANDONED,“true”);
conf.put(DruidDataSourceFactory.PROP_REMOVEABANDONEDTIMEOUT,“600”);
conf.put(DruidDataSourceFactory.PROP_VALIDATIONQUERY,“select 1”);
conf.put(DruidDataSourceFactory.PROP_TESTWHILEIDLE,“true”);
conf.put(DruidDataSourceFactory.PROP_TESTONBORROW,“false”);
conf.put(DruidDataSourceFactory.PROP_TESTONRETURN,“false”);
conf.put(DruidDataSourceFactory.PROP_POOLPREPAREDSTATEMENTS,“true”);
conf.put(“maxPoolPrepareStatementPerConnectionSize”,“50”);
//eyas 2019-5-29 从连接池获取连接等待超时的时间,10秒
conf.put(DruidDataSourceFactory.PROP_MAXWAIT, “60000”);
conf.put(“keepAlive”, “true”);

修改前后对比：
去掉以下参数：

修改参数：

新增参数：