mysql
**索引 **
什么是索引
一种排好序的数据结构，帮助mysql高效获取数据 以索引文件的形式存储在磁盘上 为什么选择B树/B+树结构作为索引结构 由于索引是以文件形式存储在磁盘上，所以评价一个数据结构作为索引的优劣便是查找过程中磁盘I/O。
为什么不选择二叉树/红黑树/HASH？
二叉树极端情况是一条斜线，查找还是从头查到尾，和没有索引一样；红黑树可以自动调节深度，数量多-深度大，查找叶子节点需要非常多的I/O操作；HASH只适合查找某一个，不适合范围查找。 树的高度越高对应I/O磁盘操作越多。 因为磁盘操作有磁盘寻道与旋转，主要耗费在寻道上，所以高度越高对应I/O磁盘操作越多，速度越慢。
度：节点大小，节点存储的数据个数。
B树特点：每个节点存储多个数据，而不是单个数据，mysql默认每个节点16k，节点存储多数据，高度就会越低。 为了让树的高度更可控，mysql采用B树的变种B+树。
B+树特点：非子节点不存储数据，只冗余存储索引，这样每个节点(16kb)存储的索引会更多，即使数据量非常大树的高度也很低，磁盘I/O操作更少。只有叶子节点包含索引+数据，叶子节点还包含了一条指针指向下一个叶子节点（方便遍历/排序）。
两种树的数据结构演示 https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B树的节点数据是不是越大越好
一次磁盘I/O操作=n页(4k*n)数据，过大不能一次加载到内存中也需要多次I/O操作，所以最好就是一个节点中存放一次I/O操作的数据。
索引是怎么支撑千万级表的快速查找
一个索引固定为8b，指针固定为6b，结构图见简书；每个节点是16kb，也就是每个非子节点节点大概包含1170个索引元素；子节点假设一行数据占1kb，每个节点16kb包含16条数据；那高度仅仅为3的B+树就包含1170117016=21902400也就是2190万条数据。
每个节点占用内存大小
show global status like 'Innodb_page_size'; 16384字节=16kb
聚集索引，非聚集索引是基于表级别的，建表的时候可以选择。
非聚集索引-myisam引擎
索引文件.MYI与数据文件.MYD分离
主键索引同非主键索引
索引文件叶子节点存储数据文件的文件指针，根据文件指针定位文件数据
聚集索引-innodb引擎
索引文件与数据文件不分离，索引文件本身就是数据文件.idb 叶子节点存储完整数据
主键索引：叶子节点中存储的是完整数据
非主键索引：叶子节点中存储的是主键索引
为什么聚集索引非主键索引叶子节点存储主键值？
保障主键索引与非主键索引之间的数据一致性，节省存储空间。
为什么innodb必须有主键索引？并且推荐使用整型的自增主键？
表结构文件本身就是按B+树组织的一个索引结构文件，没有会默认生成类似ROW_ID的虚拟主键。
为什么推荐整型，比较大小快，占用空间少。
自增会减少原B+树分裂平衡等操作，自增只需要往后边添加树的结点，分裂平衡操作少。
联合索引底层结构是什么样的？
联合索引是将各个索引字段做字符串拼接后作为key，使用时作为整体进行匹配。
最左原则：参考联合索引的底层结构，其是作为整体进行匹配的，也就是会逐字段比较
原则上sql中索引字段的顺序应该是固定，但是mysql会帮我们做优化，改变其顺序。
sql优化
慢sql定位
1.1.mysql配置my.cnf
开启慢sql
slow_query_log = 1
msql存放位置
slow_query_log_file = /usr/local/mysql/data/appledeMacBook-Pro-slow.log
慢sql的值大于多少秒
long_query_time = 3
日志输出为文件
log_output=file
将所有没有使用带索引的查询语句全部写到慢查询日志中
set global log_queries_not_using_indexes = 1
1.2.sql检测平台
2.接口效率慢（日常使用/压测/报警）
sql优化思路
1.是否有使用索引
2.索引是否生效
索引是否生效
通过explain执行计划可以查看索引是否生效
explain里边有很多字段，一般可以看下type表的查找方式all,index,range,ref等一些级别，possible keys就是mysql根据使用的字段简单判断了下可以使用哪些索引，key就是实际使用到的索引，key_len可以通过该字段查询使用索引的长度，典型应用就是联合索引场景可以通过key_len判断具体使用了联合索引的哪几个字段。然后就是最重要的字段extra，其中经常出现的有usering index使用了索引且是较优条件，如覆盖索引；还有usering where等信息；然后有两个开发人员需要密切关注的就是usering filesort以及usering template。什么意思呢，就是语句中出现了全表扫描，出现了临时表。usering filesort典型场景就是order by，user template典型场景就是group by。实际上group by底层会先排序后分组，所以解决了usering filesort就相当于解决了usering template。
我们根据sql的复杂程度将sql优化分两个场景，一为单表优化，二为多表优化。
首先说单表优化
根据场景索引失效一般分为两种，查询索引失效和排序索引失效
1.查询索引失效总体来说是比较简单的，无非就是错误的使用了索引，如使用条件表达式，like使用通配符等等，这个比较简单就不详细介绍了
问题如果where a and b，a,b分别为两个索引，此时会走到哪个索引。
不确定，如果一个索引能唯一定位，可能只走一个索引，都不能唯一定位且范围大可能也会走两个，具体看key字段。
2.排序索引失效就比较麻烦了，上边介绍了会出现全表扫描嘛，怎么解决呢，也就是利用索引，为什么使用索引也可以解决排序呢，就是因为索引底层的数据结构是一种排好序的数据结构，所以可以解决排序的问题。
2.1联合索引usering filesort，举例说明联合索引abc三个字段，where a and b order by c这就是我们想要的索引排序；如果where a and b order by d，这样排序就会产生filesort；如果where a and b> order by c这样索引就会断，因为b>导致索引不连续了；还有where a order by (b,a)这种实际mysql会进行优化，也会使用到索引ab；简单举了几个经常出现的场景根据经验可以直接定位到，实际上判断肯定依据explain的extra字段来确认。
2.2问题那么usering filesort在联合索引的场景就介绍完了，也就是实际上会使用最多的场景，还有其他如where条件与order by条件使用不同的索引导致全表扫描，这种解决方案根据业务和实际场景考虑换为联合索引，或者各自加索引多数场景也可以解决。
有一个点需要注意，大范围查找数据时，有时即使感觉肯定会走到索引，但是却显示没有使用索引，无论是查询还是排序，这是因为mysql底层会解析使用索引还是全表扫描更快，解析过程可以通过trace工具查看，如果只是想测试是否用到索引到范围较大时加上limit分页，效果会更明显。
其次说多表查询也就是表关联查询
这里我直接举一个生产的例子，优化成果是从一个15s-1min的慢sql优化到了10ms-1s的这样的一个结果。大概数据量是这样的，一张表是商品表，一张表是品牌表，商品表包含了大约接近100w的数据，品牌表就很少了几千的数据，sql就是商品表关联品牌表查询根据商品上架时间做倒序加分页的一个sql；另一个现象就是由于商品表本身就有很多搜索维度，该表已经创建了一些索引，其中包含了单独的索引和联合索引等，如商品名称有个单独索引，如店铺，商品状态，商品类型，促销等字段有个联合索引。整体表的状况大体都清晰了，那么要怎么处理呢。
思路，多表拆单表，大表拆小表，解决单表优化
我们的sql之前是goods left join brand on xxx where 各种条件 order by xxx limit 10，那我们两个表关联查询时候只是只是结果关联了品牌的一些信息，所有查询的逻辑都是在商品表上边呢，sql慢的原因是1是查商品本身就慢，2是商品表查出来的数据太多了，然后还要做关联导致越来越慢。
那我们的第一个思路就是将这个复杂多表sql简化为简单单表sql，首先就是要去掉表关联，我们将查询+排序+分页的逻辑放到商品表上，将原本很多的数据分页为几条数据后再关联品牌表，sql就变为了(goods where where 各种条件 order by xxx limit 10) goods_n left join brand on xxx，这样一种将多表拆为单表，将大表拆为小表的一个方法，来解决表关联leftjoin的问题。
表关联的问题解决了，接下来解决单表商品表的查询+排序+分页的优化。
首先分析了sql的构成，发现有一些值在该场景是必传的，如后台查询时都会查店铺=本店铺，商品状态=上架/下架，商品类型=普通商品这几个条件，之前分析表结构是发现有这几个条件的联合索引的，那我们将sql的排序和分页先去掉，看下查询是否有使用到该联合索引，测试结果显示是可以使用到的，如果没有使用到就要分析下是不是部分字段没有使用到导致联合索引失效，考虑去除该联合索引的无用字段。查询测试完毕后，加上排序测试下，发现出现了usering filesort，这时候其实解决该问题也很简单了，在联合索引加上排序字段上架时间，这里是因为即使其他场景根据上架时间排序也是很通用的所以可以直接加，加上之后usering filesort也解决了，这时候加上分页，在整体查询后加上关联品牌表，这样的一个复杂sql就解决。
最后一点要补充的就是为什么只有后台管理会出现慢sql，而我们其他场景也是类似的sql却不会出现慢sql，是由于范围的问题，后台管理大多不会传入类似商品名称，商品id等信息可以定位到小范围数据，所以使用的是联合索引且结果很多；而其他场景多是以查小范围数据为主，使用的是该表的其他字段的索引如商品名称索引，查出的结果很少，表关联也不会有问题。这里可以看出mysql选择索引是很智能的，其会分析使用或不适用索引，使用哪条索引，查询结果会更快。那mysql的具体分析情况可以通过自带的trace工具查看。
trace工具
trace信息分几个阶段
sql准备阶段:顾名思义sql信息准备
sql优化阶段:如联合索引字段调整顺序就是在这里,这里有几个信息是我们需要关注的,如行数估计及选择的执行计划;
访问成本估计,1会计算全表扫描的情况,然后根据"扫描的行数"计算出查询成本 2.会分析是否会用到索引,包含主键索引,其他索引,然后根据"索引使用范围"来计算分析各个索引的查询成本,最终根据上边全表扫描的成本及各个索引的查询成本来决定是否使用索引以及使用哪个索引
sql执行阶段:执行阶段内容较少,如果使用usering filesort这里会有一些全表扫描的信息,如全表扫描的排序方式单路/双路等排序的信息
usering filesort原理--trace工具sort_mode可以看出使用的哪种排序
单路排序:一次性取出满足条件行的所有字段存入sort buffer,然后在sort buffer中排序
双路排序:取出满足条件行对应的排序字段和主键id存入sort buffer,然后在sort buffer中排序,排序后根据id取原表中取出其他需要的字段
mysql通过比较max_length_for_sort_data大小和需要查询字段大小判断使用哪种排序模式
count选择
count(1),count(id),count(*),count(name)选择
以上四个sql的执行计划是一样的,都是使用的非主键索引,执行效率也差不多,区别只是count字段不会统计字段为null的数据行.
为什么mysql最终选择非主键索引而不是主键索引呢?
因为从索引结构分析,非主键索引叶子节点只存储主键索引,其存储的数据会少很多,所以选择非主键索引,索引性能更高.
为什么选择count()呢?*
为了防止null影响,参考阿里巴巴编程规范,选择count(*)
表关联算法
Nested-Loop Join算法:一次一行循环从第一张表读取行,取出关联字段,根据关联字段在另一张表取出满足条件的行,然后取出两张表的结果合集(索引)
Block Nested-Loop Join算法:一次把第一张表的数据读入到join buffer中,然后扫描第二张表,把第二张表每一行数据取出来跟join buffer中数据对比(非索引)
mysql锁，事务，mvcc面试题
https://www.jianshu.com/p/a26fd393f5a3
主从配置-读写分离
判断同步是否报错
1、查看Slave_IO_Running与Slave_SQL_Running状态，Slave_IO_Running=YES && Slave_SQL_Running=NO说明slave复制出错了
2、查看Last_Error以及Last_SQL_Error，获取到具体的报错信息，获取到具体的binlog以及position
3、通过binlog及posttion到主库上，解析对应的binlog，找到报错的sql。
4、解决报错，参考：https://blog.51cto.com/hujiangtao/1932166
如何正确判断SLAVE的延迟情况，判定slave是否追上master的binlog：
1、首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差异；
2、如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件；
3、如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距；
4、如果以上都不能发现问题，可使用pt_heartbeat工具来监控主备复制的延迟。
同步方式
1.master将数据记录到binlog中
2.slave的IO线程连接到master，并请求从指定日志的指定位置之后的内容，master接收到slave的请求后，负责复制的IO线程会根据根据请求的信息从指定位置获取日志信息+本次binlog文件名+binlong日志的位置，返回给slave
3.slave接收到返回信息后，将接收到的日志内容添加到slave的relay-log中末尾，并将返回的binlong文件名+位置记录到master-info中，下次同步时通过该信息获取
4.slave的sql线程检测到relay-log中新增了内容后，解析为对应的sql，在slave执行
延时问题
主从同步方式：同步复制，异步复制(默认)，半同步复制
同步复制：串行，性能差，金融场景可考虑
1.master.excute 2.master写入binlog 3.slave执行db落库 4.commit
异步复制：并行，性能高，可能会丢数据
1.master.excute 2.master写入binlog 3.slave执行db落库 3.commit
半同步复制：需要至少一次tcp调用，ack不及时(10s)，会降为异步
1.master.excute 2.master写入binlog 3.slave1同步relaylog 3.slave2同步relaylog 4.slave中任意一个relaylog同步完毕，则给master一个ack 4.slave并行落db 4.master接到ack后commit
商城数据库配置
mysql：业务分库（RDS高可用(一主一备)）+主从配置（一主一从(RDS只读实例)），当然这中间间隔时间是很长的，怕踩坑。
项目：springboot多数据源配置，不同文件夹对应不同数据源；sharding-sphere配置读写分离；
为什么选择sharding-sphere呢？经过调研备选主要就两个，一个是sharding-sphere，另一个是mycat，选择sharding-sphere的原因很简单，mycat的首页实在是太low了，就感觉不像是个官方技术首页，而是个宗教网站似的，所以毅然决然的抛弃他。
关于踩坑：
关于多数据源踩坑，加了多数据源之后，就要做分布式事务，我们选择的是Atomikos来做分布式事务，为什么选择采用Atomikos呢？
一是基于我们业务场景一般都是单应用保证这个多数据源事务；二是它业务无侵入；三是shardingsphere是支持采用Atomikos的；所以这个就成为了我们的最佳选择。具体踩坑，中间自然经历了事务不回滚等坑。如Atomikos配置事务他是将我们的事务统一起来的的，所以我们原来根据每个数据源定义的事务失效 TODO优化；见分布式事务面试http://note.youdao.com/s/WHRO6Nho
关于读写分离踩坑，自然涉及到同步出错，同步延时等问题，主从https://www.jianshu.com/p/b42776d4a804
同步延时问题。首先，这是一个在很多场景几乎无可避免的问题。嗯，那么我们要做的是什么呢？我们可以去认为两种情况。
1.第一种就是页面上的。比如。我这边插了一条数据，然后用户通过页面来查看订单的这种。这种情况即使有一些延迟。用户也会自觉的通过刷新来查看订单。而且一般这种延迟会很低很低。所以用户是感知不到的。当然，我们也可以通过硬件的手段来降低这个延迟性。这是页面上的，这种情况。
2.另外一种情况就是我们在代码中在插入之后立即查询的这种。这种情况如果发生，比上一种查不到这条数据的概率要高，因为代码的运行速度一般，肯定会小于你的这个同步延迟的这个时间的。所以我们在写代码的时候肯定要避免这种情况，如果我们插入一条数据，然后立即查询这条数据，我们本身是知道的，所以多数情况我们不要这么写。但是肯定也有情况，会插入之后立即查询。那这样，我们如果想避免这个延迟问题，只有一个方案就是去查主库。如果使用的是shardingsphere，如shardingsphere开启事务的话，在你做了一次增删改的操作后，再次查的话就是查的主库来实现这个事情。这种方案结合着业务是可以参考的，因为多数情况我们一般都是多表操作嘛，做统一变更处理是很常见的。
第一版关于数据源的优化就到这里了，后续第二版我们又基于sharding-sphere做了分表，因为我们是业务分库吗，所以没有再次拆分库，只做了分表处理。当然这里最开始也是只是先针对一个模块项目的分表试水。其实分表踩坑也很多，如分表的维度如何选择，分布式id，分布式事务等。
shardiing-sphere相关原理 http://note.youdao.com/s/TLJyW3HU
关于分表这里我们已经在考虑分布式事务的问题了，当时应该是1月份，当时阿里巴巴开源了一个分布式事务seata，当时名字还是叫Fescar，里边有个AT模式，我觉得真的非常实用，程序猿的福音，等到今年4月份的时候，seata正式发布了大版本1.0，我还去现场听了宣讲会介绍，并且sharding-sphere也在四月份的时候从apache孵化成功，并且也支持了seata，两者强强联手，我觉得分库分表sharding-sphere+分布式事务seata就是以后的主流。
seata-AT模式原理
见分布式事务面试http://note.youdao.com/s/WHRO6Nho
面试题：系统运行一段时间，数据量已经很大，这时候系统升级，有张表A需要增加个字段，并发量白天晚上都很大，请问怎么修改表结构
网友评论