java重点学习-mysql

二 mysql

2.1 如何定位慢查询?

1:介绍一下当时产生问题的场景(我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟)

2.我们系统中当时采用了运维工具(Skywalking)，可以监测出哪个接口，最终因为是sql的问题

3.在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中(调试阶段)

2.2 SQL语句执行很慢,如何分析呢?

可以采用MySQL自带的分析工具 EXPLAIN

通过key和key len检查是否命中了索引(索引本身存在是否有失效的情况)

通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描

通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

2.3 了解过索引吗?(什么是索引)

索引(index)是帮助MySQL高效获取数据的数据结构(有序)

提高数据检索的效率，降低数据库的IO成本(不需要全表扫描)

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的底层数据结构了解过嘛?

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

阶数更多，路径更短

磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据

B+树便于扫库和区间查询，叶子节点是一个双向链表

2.4 知道什么叫覆盖索引嘛 ?

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到

使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select*

MYSQL超大分页怎么处理?

问题:在数据量比较大时，limit分页查询，需要对数据进行排序，效率低

解决方案:覆盖索引+子查询

2.5 索引创建原则有哪些?

1).针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据(增加用户体验)

2).针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。

3).尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4).如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5).尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6).要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7).如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

2.6 什么情况下索引会失效?

① 违反最左前缀法则

② 范围查询右边的列，不能使用索引

③ 不要在索引列上进行运算操作， 索引将失效

④ 字符串不加单引号，造成索引失效。(类型转换)

⑤ 以%开头的Like模糊查询，索引失效

2.7 谈谈你对sql的优化的经验

·---表的设计优化(参考阿里开发手册《嵩山版》)

① 比如设置合适的数值(tinyint int bigint)，要根据实际情况选择

② 比如设置合适的字符串类型(char和varchar)char定长效率高，varchar可变长度，效率稍低

---SQL语句优化

① SELECT语句务必指明字段名称(避免直接使用select*)

② SQL语句要避免造成索引失效的写法

③ 尽量用union all代替union union会多一次过滤，效率低

④避免在where子句中对字段进行表达式操作

G)Join优化 能用innerioin 就不用left join right join，如必须使用 一定要以小表为驱动内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。leftjoin 或 right join，不会重新调整顺序

2.8 事务的特性是什么?

原子性( Atomicity)

-致性( Consistency )

隔离性( lsolation )

●持久性( Durability

ACID，分别指的是:原子性、一致性、隔离性、持久性;我举个例子A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败

在转账的过程中，数据要一致，A扣除了500，B必须增加500

在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰

在转账的过程中，持久性体现在事务提交后，要把数据持久化(可以说是落盘操作)

2.9 并发事务带来哪些问题?怎么解决这些问题呢?MySQL的默认隔离级别是?

并发事务的问题:.

① 脏读:一个事务读到另外一个事务还没有提交的数据。

② 不可重复读:一个事务先后读取同一条记录，但两次读取的数据不同

③ 幻读:一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

隔离级别:

①READ UNCOMMITTED 未提交读脏读、 不可重复读、幻读

② READ COMMITTED 读已提交 不可重复读、幻读

③ REPEATABLE READ 可重复读 幻读

④ SERIALIZABLE 串行化

2.10 undo log和redo log的区别

redo log:记录的是数据页的物理变化，服务宕机可用来同步数据

undo log :记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据.

redo log保证了事务的持久性，undolog保证了事务的原子性和一致性

其中redolog日志记录的是数据页的物理变化，服务机可用来同步数据，而undolog不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在undolog日志文件中新增一条delete语句，如果发生回滚就执行逆操作;

redolog保证了事务的持久性，undolog保证了事务的原子性和一致性

2.11 事务中的隔离性是如何保证的呢?(你解释一下MVCC)

2.12 mysql 主从同步原理

MVSQL主从复制的核心就是二进制日志binog(DDL(数据定义语言)语句和 DML(数据操纵语言)语句)① ① 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

② 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log。

③ 从库重做中继日志中的事件，将改变反映它自己的数据

2.13分库分表

分库之后的问题:

• 1.分布式事务一致性问题.
• 2.跨节点关联查询.
• 3.跨节点分页、排序函数
• 4.主键避重

应该使用 分库分表中间件:

• sharding-sphere
• mycat