mysql

mysql本身：单进程,多线程的  c/s架构,所谓的服务就是由一个一个线程执行的,可以这么理解--》一个线程就是一个连接(session)
mysql层面mysql性能优化：
1,清除掉不用的连接--->帮助mysql服务回收线程
2,设置最大连接数大体算法：
如果服务器的内存是8G,-2G给操作系统=6G,再-3G给内存缓冲区(buffer pool)=3G  再除以linux操作系统单进程的栈空间8M=350
如果项目是多线程的,一个项目有20个线程并发(！=连接池),总连接数=350*20=7000
如果设置想永久生效,需要修改etc/my.cnf文件,参数设置session和globl级别重启之后都不生效
mysql查询流程：客户端请求到服务端-->查询缓存(5.5版本之前开启,5.7版本关闭,8.0版本直接去掉了)-->词法解析(sql语法验证)
    -->语法解析(验证表是不是存在等)-->小型的优化(根据成本算出最优)-->生成执行计划-->调用执行引擎-->返回结果

数据库的日志：
binlog  存的都是ddl和dml语句,二进制的格式数据
        功能：数据恢复,主从复制
        1,数据恢复：一般情况下,数据库是有定时备份的,假定在2点备份.
        场景：如果数据库9点被误删了
        具体恢复步骤：恢复备份数据(恢复2点之前的数据)-->根据binlog恢复2点到9点的数据,如果有其他问题-->人工处理
        2,主从复制：一般情况下,一个库读,一个库写,对数据一致性要求稍差一些-->mssql一致性要求高一些
        具体步骤：主库有logdumpThread-->从库ioThread-->relaylog(产生小部分的中继日志)-->sqlthread-->存到从库
undolog 回滚日志,记录事务之前的日志,保证事务的原子性(一起成功,一起失败)
redolog 保证 buffer pool的数据在mysql崩溃的时候不丢,保证事务的持久性
        特点：本身环形日志,就是说如果日志满了都开始刷脏
        问题：如果说buffer pool设置的很大,但是redolog空间很小,这样buffer pool没有用满都开始刷脏了,相当于buffer pool设置的
        大小没有意义,所以redolog页需要相应调整大小(一般500M),最好和buffer pool保持同步才可以达到提升性能的目的

mysql 预读：当读取的数据很小的时候,不够一个数据页(16k),mysql会多读一些(局部性原理)

修改一条数据mysql的内部流程：
修改表数据结果--->buffer pool--->修改redolog(状态为prepared)--->记录undolog(提交之前的状态)--->通知可以提交事务--->修改binlog--->提交事务--->修改redolog(状态为commit)
 上面的流程是二阶段提交,为了保证数据的一致性,因为修改binlog之前重启mysql的话,buffer pool-可以恢复并且刷盘,但是binlog没有数据了

buffer pool 内存缓冲区
在内存和磁盘之间的一块区域,其实是也是内存,一般情况下大小应该设置到服务器内存的0.60-0.8之间。
数据查询流程：磁盘-->buffer pool-->内存-->返回,如果buffer pool有数据,直接返回
数据修改流程： 数据-->更新buffer pool-->刷脏线程将脏数据(redolog不同步的数据)同步到磁盘
如果buffer pool满了怎么办？-->内存淘汰策略lru

存储引擎：
innodb：默认存储引擎,支持行锁,支持事务和事务回滚(mvcc操作)
myisam：只支持表锁,但是查询的效率特别快,所以字典型数据的情况可以用myisam存储引擎
b+树：非叶子节点存放索引值和下一层级的地址,叶子节点存放数据
特点：数据顺序排列(顺序读),双向指针,一般三层(2层指针,一层数据)的结构,数据相对稳定
最大数据量计算：(一个数据库的磁盘页=16484字节),一般情况下
        (一个索引+一个指针=12个字节),16484/12=1300
        (一条完整的数据=字段数X每个字段的大小大约=1600个字节) 16484/1600=10,
所以三层b+树可以存放数据=1300X1300X10=1700w,所以一个表里有2000w的数据,查询的速度相对是稳定的,所以b+树的层级和表的字段结构
也有关系,也就是2000w的数据未必有5000w的数据快

hash索引：支持点到点的查询,myisam引擎的时候用
索引的使用原则：
        1,列的离散度要大,因为索引分不开相同的字段,所以查询的时候还是顺序读,而且索引增大了io,影响到了mysql优化器的判断,对于离散度小的这种情况的优化
需要分表,加上limit限制查询数量
        2,需要遵循最左匹配,就是查询条件有顺序,从左到右依次和联合索引的顺序一致
        3,在条件上加索引,order by, group by 也可以
        4,不要用uuid做主键,因为主键默认索引,所以uuid表数据的b+树会频繁变化,最好是顺序增长的
索引失效：只要是索引不明确都会失效
        1,索引上用函数
        2,索引上有隐式转换(类似字符串上不加引号)
        3,like关键字前模糊 前面有%
        4,负向like not like

覆盖索引 VS 回表：
前提：innodb-->数据即索引-->索引和数据都存在一个文件中,数据是存在叶子节点上的
聚集索引：叶子节点就是数据的索引-->前提条件：id,非空唯一搜索引,rowid做索引--->索引覆盖.或者查询的数据就是id是同样的道理
二级索引(普通索引)：叶子节点是聚集索引的id,并不是数据-->需要再次通过id查询一下数据-->聚集索引--->回表(走了两次索引也就是查询了两次io)
数据查询优化方案：
开启慢sql日志,一般设置500ms,查看慢sql统计的数据：mysqldumpslow -s ...还有很多参数，需要加一个软连接才可以用mysqldumpslow(mysql自带工具)
数据库层面的：1,更改数据每个session的连接时间 2,增加mysql的连接数(有计算方法) 3,增加buffer pool和redo log 的大小(有计算方法) 4,需要估算一个表的是数据量,是不是超过了4层b+树
表层面：1,查看在执行计划,是不是用到了索引 2,是不是走了回表(一般 select * 中会出现) 3,索引是不是失效  4,联合索引是不是有最左匹配  5,索引是不是用在了正确的字段(离散性角度)
6,需不需要加分区表  7,分库(参考微服务)分表操作,8,是不是开启了索引下推 9,级联查询小表驱动大表(较少过程数据)+历史数据缓存(可选)

事务(完整的begin,commit可以理解为客户端线程)：最终都是为了达到数据的一致性
sql 92标准：所有数据库都依据这样的标准
隔离级别：读未提交=-->现象：脏读 RU
隔离级别：读已提交=-->现象：可重复读 RC
隔离级别：可重复读=-->现象：幻读 -->RR  mysql通过MVCC在RR级别解决了幻读的问题
隔离级别：串行化=-->现象：问题都不会出现 seri

mysql解决读取数据一致性的问题采用了快照读+mvcc=->MVCC  多并发版本控制
原理：mysql维护一个表(类似),字段有事务操作数据的"创建版本"和"删除版本","版本"就是一个自增字段(一个begin，commit算一个事务),这个字段是根据每个新事务自增,也就是说最终隔离性根据每个事务区分(每个事务对于数据库来说是最小的操作单元,对服务端来说,就是客户端的一次tps)的,
通过undo log 记录事务开始时的状态实现事务回滚

锁：
共享锁：select * from table in share model 多个读数据事务可以共享
排他锁：select * from table for update  只有一个事务获取,一般情况下,分布式的任务可以直接用排他锁防止任务跑重
innodb锁的是索引-->,有索引才可以实现行锁,锁行数据-->gap(间隙锁)-->临界锁
一个表有5,8,10,15 4条数据
行锁：select * from table where id = '10' for update  -->id = '10'的记录是存在的,只锁id = '10'这条记录
间隙锁：select * from table where id = '11' for update  -->id = '11'的记录是不存在的,需要锁区间(10-15)
临界锁：select * from table where id > '10' and id <= 15 for update  -->id = '15'的记录是存在的,需要锁区间(10-15],(15,无穷大)
左开右闭是为了解决幻读的问题-->锁数据的时候有其他事务操作数据库

索引下推：
前提：查询的条件字段需要>=2,并且全部在联合索引的字段中(叶子节点是多个字段组成的联合数据和主键),最左匹配不满足(第一个条件查询是模糊匹配,多个数据),正常需要回表查询完整数据再筛选第二个条件,但是索引下推在搜索引擎中直接过滤了
        第二个条件(第二个条件也在复合索引列中,其他查询字段也是一样),默认开启索引下推
效果：减少回表,一次查询io



一些有用的命令：
show global status  服务端运行状态   举例：show global status like 'com_select'  查看执行select的次数,show global status like '%Threads%' 查看mysql线程数使用数量(个人理解都是连接数)
show processlist  查看哪些客户端连接数据库
show engine innodb status 查看搜索引擎的状态-->一般监控sql用
show global variables like 'wait_timeout' -->会话交互时间
show global variables like 'interacive_timeout' -->非会话交互时间
show global variables like 'max_connections' -->连接数


执行计划：
id：如果有大有小,从大到小执行; 如果值相同,从上往下执行   -->通过id可以查到表的执行顺序,是不是小表驱动大表(减少中间数据)(小表在前,大表在后)
type：const 主键或者唯一性索引直接命中的情况(匹配到唯一条数据),eq_ref 一般在join中,表示前表的每一个结果都能匹配到后表的唯一数据,ref 用非唯一(普通,二级索引)索引做常数匹配查询
    条件,或者索引的左前缀,rang  通过索引范围查询(> <),index  查询索引的字段(没有查询条件), all 没有索引
possible_keys：可能用到的索引
keys：最终用到的索引
Extra：using index：用到了覆盖索引

分库分表：一般用mycat,Sharding-JDBC
分库：-->参考微服务
分表：水平分-->一致性哈希方法(需要用到路由到不同的数据库)：先范围分表以id自增为例：-->1-2000w分一个库,2-4000w分一个库,以此类推...之后每个库中按hash取模的方式打到不同的数据表,查询也是如此
      垂直分-->根据字段的活跃程度分表(主表,扩展表)