目录
1. MySQL如何定位慢查询？
问题原因
在压测接口过程中有的时候接口响应非常的慢，可能会达到好几秒，那么针对这种情况，是不是我们代码写的冗余？如果是代码冗余我们可以自己优化，如果是数据库IO的问题我们该怎样能够定位到慢查询的SQL语句呢？
解决方案
方案一：Skywalking
一般公司都会使用运维监控系统，拿我们公司举例使用的就是Skywalking，通过这个工具可在其展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题。如图所示！
方案二：MySQL自带慢查询SQL日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志 如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log。
2. 如何分析慢SQL语句原因？
问题原因
在开发中如果接口响应比较慢一般是因为数据库IO造成的，那么我们如何分析这条SQL语句为什么慢呢？
解决方案
可以采用EXPLAIN自动执行计划 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息。
-直接在select语句之前加上关键字 explain/desc
EXPLAN SELECT 字段名称 FROM 表明 WHERE 条件；查询结果如图所示
开发中通常通过如下几个参数来确定慢SQL的原因
id: 多表联查的执行顺序，ID值相同执行顺序从上到下，ID不同值越大越先执行
select_Type：当前select语句类型
possible_key：当前SQL可能会使用到的索引（重要）
key：当前SQL实际命中的索引（主要）
key_len：索引占用的大小（主要）越小越好
Extra：额外的优化建议，看有没有回表（如果为null就是走了回表查询，如果为Using index就是不需要回表查询）
type：这条SQL的连接的类型，性能由好到差为NULL、system、const,eq_ref,ref,range,index,all
- NULL：查询的表为空（几乎不可能）
- system：查询系统中的表（几乎不可能）
- const：根据主键查询
- eq_ref：主键索引查询或唯一索引查询（一条数据）
- ref：索引查询（好比将地域设置索引，会查询出很多数据）
- range：范围查询
- index：索引树扫描
- all：全盘扫描
3. 什么是索引？
在数据中假如有一张表1000条数据，我们要查询赵敏的信息，如果我们没有索引就需要一条一条找，如果有索引我们可以直接定位到赵敏的位置，从而快速找到信息。
4. MySQL底层数据结构(B+树)
MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是
1、B+树是一个矮胖树，非叶子节点存储指针，叶子节点存储数据，也就是说在查找数据时寻找的层级比较短，所带来的磁盘IO时代价更低
2、B+树便于区间查询，由于而 b +树的所有数据都是存在叶子节点里面，并且使用了双向链表来关联，所以在查询的时候只需要查两个节点，然后进行遍历就好了
5. B树和B+树的区别
1、B树每个节点最多有5个子节点，节点内最多有4个Key，并且B树的叶子节点和非叶子节点都会存放数据。
2、B+树数据都存储在叶子节点，非叶子节点存放指针，并且使用双向链表进行关联
3、它们二者最大的区别我认为除了数据存放方式不一样，再有就是索引数据时方式不一致，例如查询一条数据B+树的叶子节点会根据指针找到数据而不会将自身也查询一遍，而B树叶子非叶子都存储数据，他就需要一层一层去查询知道查找到数据
6. 聚簇索引——非聚簇索引
聚簇索引
聚簇索引又称之为聚集索引，指的是数据和索引存储在一起，表示的是B+树叶子节点保存了整行的数据，可以通过聚簇索引直接找到叶子节点中的数据。
聚集索引选取规则:
1）如果存在主键，主键索引就是聚集索引。
2）如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3）如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
非聚簇索引
非聚簇索引又称之为二级索引，非聚簇索引指的是索引和数据分开存储，B+树叶子节点保存对应的主键，可以有多个，假如我们将name设置为索引，那么此时B+树的叶子节点所存储到的就是索引所对应的主键。一般我们自己定义的索引都是非聚簇索引
7. 回表查询
回表查询指的是，假如现在我们查找数据不是按照聚簇索引直接通过主键或者唯一索引找到存放在B+树的叶子节点整行的数据，而是通过非聚簇索引进行查询，由于非聚簇索引B+树的叶子节点存储的是主键，那么查找这条数据的过程就是通过叶子节点找到存储在叶子节点的主键，再通过主键回头去通过聚簇索引找到存放在叶子节点的数据，这个过程就称之为回表查询。
8. 覆盖索引
覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。
假设现在有一张表，三条SQL，我来为你解答为什么有的SQL走了覆盖索引而有的没有
（一）SQL
此时我们的主键id是聚簇索引B+树保存的是整行的数据，name为非聚簇索引B+树保存的是对应的主键，由于我们采用id作为查询条件直接走聚簇索引查询出整行的数据，也就是返回所有的数据，所以这就是覆盖索引，即使用索引作为查询条件那么在返回的列中全部能够找到。
（二）SQL
此时我们使用name非聚簇索引作为查询条件，Arm中的大写字母A在最左边经过比对，此时叶子节点存储着我们作为非聚簇索引的Arm以及他对应的主键ID，所以我们在返回的列中都包含。这就是覆盖索引
（三）SQL
此时我们将name聚簇索引作为查询条件，继续走聚簇索引查询，在B+树的叶子节点我们可以找到Id和name但是返回的列中还包含了gender，为了能够找到gender我们还需要根据主键回表查询，所以这条SQL由于查询条件和返回的列中不对应，所以不是覆盖索引。
9. MySQL超大分页处理
问题原因
在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
因为，当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大 。
解决方案
一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化
select *from tb_sku t,(select id from tb_sku order by id limit 9000000,10) a
where t.d = a.id;
这条SQL的意思是，(select id from tb_sku order by id limit 9000000,10) a，使用ID作为order by条件，由于ID是聚簇索引效率高直接B+树叶子节点返回全部数据，并且返回的列也是ID，然后在进行分页起别名a，然后由于这条SQL即是覆盖索引查询也作为子查询条件并且里面也完成了分页，在跟外表ID作为where条件，所以性能就提高了。
10.创建索引的原则
在日常开发过程中，索引的创建必不可少为的是提高查询效率，增强用户体验，但是索引的创建也是一门学问，以下是我结合自身经验总结的。
1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
11.索引失效的情况
问题原因
在开发中，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析 ，那么导致SQL语句失效的情况有哪些呢？
解决方案
比如我们给tb_seller创建联合索引，字段顺序：name，status，address
1）违反最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引
我们可以看到我们按照索引的顺序进行查询全部都走了索引
同样的语句写成违法最左前缀法则 ， 索引失效：
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
2) 范围查询右边的列，不能使用索引 。
根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。
3) 不要在索引列上进行运算操作， 索引将失效。
4) 字符串不加单引号，造成索引失效。
数据库的列是字符串，而查询时没加单引号所以会失效
5) 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模
糊匹配，索引失效。
12.SQL优化方案
结合自身经验总结以下几点。
1）在使用一个以上索引作为查询条件时，最好将各索引组合成联合索引，因为MySQL内部的机制面多多索引查询时，他只会选择一个自认为性能好的索引当作查询条件，而这个当作查询条件的索引是一个非聚簇索引叶子节点只保存本身及对应的主键ID，那么剩下的索引字段由于返回的数据当中没有本身，就得通过叶子节点的主键ID进行回表，性能就下降了。
2）SQL做聚合查询的话尽量用union all代替union，因为union all不会过滤掉重复的数据，而union会把重复的数据过滤掉，索引union的效率低
3）能用innerjoin连接就不用leftjoin和rightjoin，如果必须使用左连接或右连接，尽量以小表为驱动，就是说innerjoin会对两个表进行优化，优先把小表放外面他会自动调整顺序，而left right就相当于写死了，不会在进行调整，就有可能大表在外层小表在里层，从而影响性能。
4）避免写成索引失效的情况（具体参考第11小节）
- （违反最左前缀法则）你在使用组合组合索引时违反了最左前缀法则，就是说你不能跳过某一列去查询
- 范围查询右边的列不能使用索引，不然会失效
- 不要再索引列上进行运算操作，不然会失效
- 数据库列本身是字符串，然后你查询时没有加单引号，也会造成索引失效
- 以%开头的模糊查询都会导致索引失效
5）主从复制、读写分离
如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。 读写分离解决的是，数据库的写入，影响了查询的效率。
13. A C I D
在明白ACID之前我们先了解一个概念 " 事物 " 事务是一组操作的集合，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
那么我们开发中最常用的MySQL存储引擎就是InnoDB里面就集成了事物的特性ACID
A：原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
比如A向B转账500元，转账完成后，A必须扣除500元，B必须增加500元，转账完成后它们两个必须同时扣除还有增加。不能一个成功一个失败，原子性主要体现在要么同时成功，要么同时失败。
C：一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
比如A向B转账500元，转账完成后，数据必须一致，也就是A减少500元，B增加500元，不能A转了500而B收到了400。
I：隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
比如A向B转账500元，在转账的操作过程中，不受外部事物与因素映像，完全独立的。
D：持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
比如A向B转账500元，转账完成后A减少500元，B增加500元，转账完成后数据就是持久的，持久性体现在事物提交后，要把数据持久化，可以简单理解为落盘操作。
14. 事物隔离级别
问题原因
我们在项目开发中，多个事务并发进行是经常发生的，并发也是必然的，有可能导致一些问题
1）脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”。
2）不可重复度：事务A查询ID为1的数据，事务B修改了ID为1的数据并且提交了，这时事务A再次去查询ID为1的数据就跟上次查询的不一致，因为查询的是事务B提交的数据，也就是一次事物内查询到了两种不同的数据。
3）幻读：事物A读取了几行数据，接着另一个并发事务B插入了一些数据。在随后的查询中，第一个事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读
解决方案
解决方案是对事务进行隔离，事务隔离级别越高，数据越安全，但是性能越低，其中MySQL默认隔离级别是可重复读，为什么不用串行化是因为他的原理是一个事物执行下个事物才可以执行，相当于放弃了并发事物，在开发中不可能会用。
MySQL支持四种隔离级别，分别有：
1）未提交读（什么也解决不了）
2）读已提交(脏读)
3）可重复读（脏读，不可重复读）
4）串行化（什么都可以解决）
15. MySQL中redo log与undo log
redo log
在讲解redo log前我们先明确MySQL两个概念，缓冲池(buffer pool)、数据页(page)
数据页(page)：我们MySQL的数据是存储在磁盘当中的数据页，每个页默认大小是16KB，页里面就是一行行数据
缓冲池(buffer pool)：我们都知道MySQL的数据时存储在磁盘当中的，但是我们平常对数据库做操作时并不是直接操作的磁盘，而是先操作的缓冲池并且对缓冲池进行操作是基于内存进行操作所以速度特别快，然后由磁盘中的数据页同步到缓冲池。
redo log由两部分组成，重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中，我们知道缓冲池是基于内存的当服务器宕机那么数据就会丢失，也就无法同步到磁盘当中，有了redo log之后当做增删改操作时缓冲池就会发生变化，那么redo log buffer就会记录数据页的变化，然后同步到磁盘中redo log file，当意外发生时，就可以恢复数据了，所以有了redo log的存在就保证了数据的持久性
undo log
undo log可以实现事务的一致性和原子性
也叫回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。
可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然， 当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
16. 主从同步原理
问题原因
在项目上线时我们一般为数据库搭建主从结构，主节点负责写数据DML从节点负责读数据DQL为了缓解单节点MySQL的压力，那么我们两个数据库节点我们是如何实现数据同步的呢？
解决方案
当主库的数据发生了改变可以是(对数据库的操作DDL)及DML，然后把这些命令写入到binlog日志文件当中
然后从库有一个IOthread线程去主库binlog日志中读取数据，然后写入到从库Relay log日志中，然后再由从库SQLthread线程去Relaylog中读取，然后从库把里面的命令在执行一下，就完成了主从同步。
17. 全局锁
问题原因
在开发中我们有时需要对数据进行提取备份，但是在备份的过程中，可能会有用户的数据发生改变，就会造成备份数据和实际数据不一致的情况，所以我们需要进行上锁。
解决方案
加锁语句
flush tables with read lock ;数据备份语句(在Windos环境下输入)
mysqldump -uroot –p1234 itcast > itcast.sql释放锁
unlock tables ;全局锁存在的问题
mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql18. 表级锁
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。
语法：
加锁：lock tables 表名... read
释放锁：unlock tables /读锁的执行流程：客户端一对表进行上锁，此时客户端一还有客户端二都只能是进行DQL也就是可以进行读取操作不能是写操作，等上锁的客户端一完成操作后释放锁客户端一二才可进行写操作
语法：
加锁：lock tables 表名... write
释放锁：unlock tables /写锁的执行流程：客户端一对表进行上锁，本身可以进行读操作以及写操作，但是上锁期间客户端二则不能对表进行操作，属于阻塞状态。
707
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
