mysql 优化定位分析，索引，主从分库分表

mysql 优化定位分析，索引，主从分库分表

1.优化：
定位慢sql
sql执行计划
索引：存储引擎，索引底层数据结构，索引创建原则，索引失效场景
sql优化经验：
1.1聚合查询
1.2多表查询
1.3表数据量过大查询
1.4深度分页查询

2.如何定位慢查询 ?
调试工具：Arthas 
运维工具：Prometheus 、Skywalking
MySQL自带慢日志

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息

possible_key 当前sql可能会使用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
Extra 额外的优化建议

type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all
system：查询系统中的表
const：根据主键查询
eq_ref：主键索引查询或唯一索引查询
ref：索引查询
range：范围查询
index：索引树扫描
all：全盘扫描

select * from tb_sku t,
(select id from tb_sku order by id limit 9000000,10) a where t.id = a.id;

3.索引创建原则有哪些？
1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

先陈述自己在实际的工作中是怎么用的
主键索引
唯一索引
根据业务创建的索引(复合索引)

4.什么情况下索引会失效 ?
违反最左前缀法则
where:范围查询右边的列，不能使用索引
不要在索引列上进行运算操作， 索引将失效
字符串不加单引号，造成索引失效。(类型转换)
以%开头的Like模糊查询，索引失效

5.表的设计优化（参考阿里开发手册《嵩山版》）
比如设置合适的数值（tinyint int bigint），要根据实际情况选择
比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

6.SQL语句优化
SELECT语句务必指明字段名称（避免直接使用select * ）
SQL语句要避免造成索引失效的写法
尽量用union all代替union union会多一次过滤，效率低
避免在where子句中对字段进行表达式操作
Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动，
内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

7.数据库的ACID是什么？可以详细说一下吗？
原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题：脏读、不可重复读、幻读
隔离级别：读未提交、读已提交、可重复读、串行化

脏读：一个事务读到另外一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

隔离级别：
READ UNCOMMITTED 未提交读
READ COMMITTED 读已提交
REPEATABLE READ 可重复读
SERIALIZABLE 串行化

对事务进行隔离

8.undo log和redo log的区别
8.1undo log回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。
undo log可以实现事务的一致性和原子性

8.2redo log重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用

redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据
undo log ：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

9.解释一下MVCC
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突
MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView

快照读：select 不加锁就是快照读，读取的记录数据的可见版本，有可能是历史数据
readView解决的是一个事务查询选择版本的问题
不同的隔离级别快照读是不一样的，最终的访问的结果不一样
RC ：每一次执行快照读时生成ReadView
RR：仅在事务中第一次执行快照读时生成Read

10.垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

特点：
按业务对数据分级管理、维护、监控、扩展
在高并发下，提高磁盘IO和数据量连接数

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：
1，冷热数据分离
2，减少IO过渡争抢，两表互不影响

水平分表：将一个表的数据拆分到多个表中(可以在同一个库内)。

特点：
优化单一表数据量过大而产生的性能问题;
避免IO争抢并减少锁表的几率;

分库之后的问题：
分布式事务一致性问题
跨节点关联查询
跨节点分页、排序函数
主键避重

业务介绍
1，根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）
2，达到了什么样的量级（单表1000万或超过20G）

具体拆分策略
1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题
2，水平分表，解决单表存储和性能的问题
3，垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
4，垂直分表，冷热数据分离，多表互不影响