mysql结构划分
mysql可以划分为客户端、server端、存储引擎三部分，server端包括了连接器、分析器、优化器、执行器4部分
- 客户端通过navicat、jdbc等方式与服务端连接器建立连接，发送sql语句
- 如果是查询语句，且mysql缓存中有查询结果则立即返回，否则server利用分析器对sql进行词法和语法分析，看sql语句是否错误
- 之后优化器对sql语句进行优化，有CBO(基于成本)和RBO(基于规则)两种方式，但大部分使用的是CBO，基于查询成本的方式进行优化
- 将优化完成的sql交给执行器，执行器通过数据库引擎执行sql，最后将执行结果返回
mysql调优
性能监控
- show profiles可以查询sql语句各步骤的执行时间
- show processlist 命令查询数据库连接数量,command字段可以查看该线程状态，正在做什么
- show engine innodb status;可以查询数据库引擎运行的详情信息，里面包括连接线程等待锁的信息
schema与数据结构优化
在数据库和表的创建之初就采用更合理的设计，比如表中包含哪些字段以及选择合适的字段类型
- 当数据类型有多种选择时，更小的通常更好，例如整型数字类型由tinyint、samllint、mediumint、int、bigint，它们分别占1、2、3、4、8个字节，当它们都满足取值范围时选取较小的数据类型进行数据存储，首先可以节省存储空间，并且处理时需要的CPU周期较小也可以增快查询效率
- 当存储字符串类型的数据时，如果我们已经知道了它的长度固定格式，例如手机号11位、身份证，那么就使用char类型，不要用varchar，这样可以减少一个字节用于存储字符串长度的存储空间
- 合理使用范式(范式是为了解决数据冗余的问题)和反范式，使用范式可以减少冗余，但会出现大量外键和关联查询，查询效率较低，因此在实际工作中允许出现一些反范式，即存在冗余三范式
- mysql 字符集用utf-8mb4，因为utf8存储的时候某些中文是乱码
- 在innodb中索引和数据是一起存储的，myisam中是分开存储的；innodb才有redo log和undo log，它是用来支持事务的
项目中慢查询
1.mysql需要手动开启慢查询
在mysql配置文件中添加如下参数
long_query_time=1（多长时间视为慢查询，1秒）
slow_query_log（是否开启了慢查询日志，off表示未开启，on表示开启）
slow_query_log_file（慢查询日志存放的位置）
项目中一般把慢查询时间设置为1S。当出现慢查询时利用explain命令分析SQL的执行计划，看它有没有使用到索引，有没有做全表扫描。当执行explain命令时有两个字段比较重要
- type 表示对表的访问类型，常见的有ALL（全表扫描）、index（遍历索引树）、range（索引范围扫描）、 ref（使用了索引）、eq_ref（唯一索引）、const(单条匹配 如主键查询)、system(从左到右，查询性能从差到好)，一般需要保证type至少要到range这个级别
PS:
const：结果集只有一条，一般当使用主键或唯一索引时会出现
system：当表里只有一条数据时，会出现
eq_ref: 通过主键关联或唯一键关联查询，我只会查询一条记录，效率也是非常高的
expalin命令字段解析 - key 即查询时真实用到的索引，如果没有使用索引，则该字段为NULL
2.当sql语句语句没有使用索引时，可以为它添加合适的索引，提高查询效率，如果已经使用了索引但是用explain命令发现索引并未生效时，就需要检查一下是不是SQL查询条件where部分写的有问题导致索引没有生效，例如(下列sql假设key字段有索引)
- 对索引字段使用is null，(select col1，col2 from table where key is null)
- 在索引字段上做了函数运算，(select col1，col2 from table where key + 1 = 3)
- 索引是否进行了类型转换，例如key是char类型，但查询时传入了int类型参数，(select col1，col2 from table where key = 3)
- 使用like关键字，如果%位于前面(‘%abc’，‘%abc%’)将无法使用索引，如果%在后面(‘abc%’)索引则可以生效
- 当使用or关键字时，如果or左右两边的字段为主键会使用索引(select * from table id =2 or id =3)；若一遍有索引一遍没有，则全表扫描；当or两边都与索引，无论这两个字段是不是同一列，是否使用索引要看查询优化器的评估，如果代价过高依然会走全表扫描
- 索引没有遵循最左匹配原则
索引不生效情况
3.当使用了索引，并且索引也已生效，就需要考虑下是不是SQL太过复杂，考虑对SQL进行拆分等优化，并且对相关业务代码进行调整
实际保险项目中包含有保单管理等模块，保单表的数据量大概有个几百万，且保单管理模块查询条件做的比较丰富，一开始我们做了很多次联表查询，join许多其他表，例如投保人、被保人、规划师、支付流水表(有分期付款)表等，sql写的很复杂，且偶尔会有慢查询的情况出现，后来发现大部分是出现在利用投保人和被保人姓名做查询的时候，在实际表中并没有对姓名加索引，之后考虑到尽量压缩关联查询的数据量，所以把投保人和被保人相关的查询单独拆出来不再做join查询，分两步先利用投被保人信息查询出具体的保单id，再利用保单id去做二次查询
索引
- 什么是聚集索引和非聚集索引？
- 聚集索引就是指索引与数据一起存储，非聚集索引就是说索引与数据分开存储；（百度定义：聚集索引是指数据库表行中数据的物理存储顺序与键值的逻辑（索引）顺序相同）
- 在MyISAM存储引擎底层它的索引文件和数据文件是分开存储的所以它里面都是非聚集索引；
- Innodb的索引和数据存放在一个文件中，主键索引B+树的叶子节点中存放了其所在数据行的完整数据信息，它是聚集索引，但是非主键索引树叶子节点只存储了索引所在行的主键信息，所有非主键索引为非聚集索引
- 索引是怎么实现的？
-
在mysql中大部分索引是使用B+树来实现的，还有一小部分使用hash(哈希表，但不支持范围查询)
-
在myisam 无论主键索引还是非主键索引叶子节点存储的是索引对应数据行在磁盘存储位置的指针
-
innodb 非主键索引叶子节点存储的是索引对应行的主键值，主键叶子节点存储了其数据行的完整数据信息
-
mysql对B+树中一个节点的大小设置的是16K，B+树叶子节点间存在双向指针
-
Innodb为什么一定要有主键，并推荐使用整型自增主键？
- innodb采用了非主键索引B+树的叶子节点存储的是该索引值所在数据行的主键，主键B+树叶子节点包含了主键值及其代表行的完整数据这种形式来存储索引和数据，如果在innodb没有指明主键的情况下，它会自动从数据中选择一个可以唯一标识数据的列作为主键索引，如果找不到的话它会生成一个默认的列，所以innodb一定会有主键；
- 之所以推荐用整型是因为在B+树中每个索引关键字是按照从左到右递增的顺序去排列的，使用整型查找、比较速度会更快，并且如果是自增的话新数据插入的时候直接在原B+树某节点右边扩展就好了，树结构的调整比较小，效率高，可以避免B+树频繁合并和分裂（对比使用UUID），如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。
-
为什么Innodb非主键索引结构叶子节点存储的是主键值？
- 在进行update等数据操作的时候，会改变数据行存储数据信息，此时仅会修改聚集索引叶子节点中存储的数据信息，非聚集索引叶子节点存储的是主键的值，不会发生改变，减少数据修改量，速度比较快
- 仅在聚集索引叶子节点存储完整的数据信息，节省存储空间
-
什么是回表？
例如 state字段有普通索引，select id，name from table where state = 2
回表出现在innodb存储引擎中，当使用非聚集索引查询且select查询的字段没有全部出现在索引B+树叶子节点时，需要根据叶子节点存储的主键再次去聚集索引B+树进行一次查询，从聚集索引树的叶子节点获取完整的数据然后返回，这种二次查询叫做回表
-
什么是索引覆盖？
- 进行B+树检索的时候尽量不要回表，减少查询次数，可以使用覆盖索引。使用非聚集索引查询的时候如果需要返回的字段在B+树叶子节点中都有，则不需要再查一遍聚集索引的B+树，此时可以将查询字段创建一个组合索引，例如 id为主键、name和age有组合索引，select name，age from table where name = ‘zhangsan’
- select * from table where id =1和select id from table where id =1，后者用到了索引覆盖，使用explain时extra显示为using index 就表示使用了索引覆盖
- 下面这个图是组合索引的B+树，但需要指出的是一般组合索引都是非聚集索引，叶子节点存储的是主键的值，除非组合索引是唯一索引且表中没有主键，则它会被当作主键使用
-
索引下推是什么？(name age有组合索引) select * from table where name like ‘x%’ and age = y；
- mysql5.6 之前,先根据name从存储引擎中查找出所有数据，然后在server层利用age进行数据过滤
- mysql5.6 之后根据name和age两个值去存储引擎中获取数据，直接把数据返回,这样做整体的IO量会小很多，效率提升
使用索引下推优化，可以有效减少需要回表的数据数量，也可以避免server 层从存储引擎层接收数据后再次进行数据过滤等处理操作，提升查询性能
索引下推
* 索引的使用策略
- 当使用索引列进行查询时尽量不要使用表达式，把计算放到业务层，而不是数据库层
- 尽量使用主键查询。而不是其他索引，因为主键查询不会出现回表查询
- 使用前缀索引,前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引
为什么要用前缀索引： 前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。
什么时候用前缀索引
当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。 - 使用索引扫描来排序，当查询条件中包含索引，且order by的字段与查询条件中索引一致时
- union all、in ， or 都能使用索引但是推荐使用in
- 范围列可以用到索引，例如 < <= > >= betweeen，但是范围列后面的列无法用到索引，索引最多用于一个范围列表
- 强制类型转换会触发全表扫描 例如 phone字段为char 且有索引，select * from table where phone = 12313123213 不会用到索引，select * from table where phone = '12313123213’会用到索引
- 更新十分频繁、数据区分度不高的字段上不宜创建索引，更新索引会调整B+树，更新频繁的字段建索引会大大降低数据库性能；类似于性别这种区分度不高的字段，建立索引没有意义，不能有效的过滤数据，性能类似全表扫描
- 利用覆盖索引来进行查询操作，避免回表
- 使用合理的分页提高效率。select id,name from product limit 866613, 20,使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。
优化的方法如下：
可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。
比如此列中，上一页最大的id是866612。
SQL可以采用如下的写法：select id,name from product where id> 866612 limit 20。
mysql 存储引擎
- MyISAM和InnoDB存储引擎的区别？MyISAM和Innodb的区别
| MyISAM | InnoDB |
|---|---|
| 支持表锁 | 即支持表锁也支持行锁 |
| 不支持事务 | 支持事务 |
| 不支持外键 | 支持外键 |
| 非聚集索引，无论主键还是非主键B+树叶子节点存储的都是对应数据存储地址的指针 | 主键是聚集索引，索引和数据存储在一起；非主键B+树叶子节点存储的是索引对应的主键值，主键B+树叶子节点存储的是主键对应的完整数据 |
| 存储文件中索引和数据是两个文件分开存储的 | 索引和数据用同一个文件存储 |
| 可以没有主键 | 一定要有主键 |
- Innodb中的事务
事务可以理解是一组sql集合，他们的执行要满足ACID四个特性。一致性是事务的根本追求
| 事务特性 | 实现原理 |
|---|---|
| 原子性 | undo_log由innodb引擎产生，当进行数据操作之前，首先将原数据备份到undo log之中，然后进行数据修改，如果出现的错误，或者用户执行了回滚rollback语句，系统可以利用undo log中的备份将数据恢复到事务开始之前到状态，undo log还用于实现多版本并发控制(MVCC)；需要注意的是undo log是逻辑日志，可以理解为当delete一条记录时，undo log中会记录一条对应的insert语句，当insert一条语句时，undo log中记录的是一条delete语句，update时，会记录一条相反的update记录 |
| 一致性 | 通过原子性 隔离性 持久性来保证 |
| 隔离性 | 实现原理锁，共享锁S，排他锁X，事务根据自己对数据的操作类型申请相应的锁；申请锁的请求被发送到锁管理器，锁管理器根据当前数据项是否已经有锁以及事务申请和持有的锁是否冲突来决定是否为该请求授予锁；若授予锁则事务可以继续执行，若拒绝，则申请锁的事务将进行等待，直到锁被其他事务释放。 |
| 持久性 | （redo log、binlog实现） redo log 记录了新数据的备份，在事务提交前，将要保证redo log持久化到磁盘，此时不需要将数据持久化。当系统奔溃时，虽然数据没有持久化，但是系统可以根据redo log内容，将数据恢复到最新状态，redo log刷入磁盘的频率可以由 innodb_flush_log_at_trx_commit参数控制 0每次commit前将rodo log写入log buffer，每秒将日志写入os buffer并调用fsync()刷入磁盘(这里的commit应该是指事务提交)；1每次commit前将rodo log写入os buffer并调用fsync()将日志写入磁盘；2每次commit时将rodo log 写入os buffer，每秒钟调用fsync()将日志刷入磁盘。默认是1，数据安全性高，但是会影响性能，0和2相比，他们预防的宕机级别不一样，当mysql宕机log buffer中数据就会丢失，os buffer是系统内存当mysql挂掉时，只要操作系统没有挂，它的数据就不受影响依然可以刷到磁盘 |
 |
mysql中的锁
-
MyISAM中有两种锁
表共享读锁、表独占写锁，对MyISAM表的读操作，不会阻塞其他用户对同一个表的读操作，但是会阻塞对同一张表的写请求；对MyISAM表的写操作则会阻塞其他用户对同一张表的读和写请求；MyISAM表的读写操作之间、写操作之间是串行的。 -
Innodb
- 共享锁(S):又称读锁，允许一个事务去读一行，阻止其他事务获取同一数据集的排他锁，若事务T对数据对象A加上S锁，则事务T可以读A但是不能修改A，其他事务只能对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但是在T释放A上S锁之前不能对A做任何修改
- 排他锁(X)：又称为写锁，允许获取排他锁的事务更新数据，阻止其他事务获取相同数据集的共享读锁和排他写锁。若事务T为数据对象A加上X锁，事务T既可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
- mysql innodb引擎默认为update、insert、delete语句自动加上排他锁，select语句默认不加任何类型的锁，如果想加排他锁可以使用 select … for update，加共享锁使用 select … lock in share mode语句。所以加过排他锁的数据行在其他任何事务中是不能修改的，也不能通过 for update和lock in share mode的方式查询数据，但可以通过select … from …普通查询语句查询数据，因为普通查询没有任何锁机制。
- innodb行锁的实现方式是通过给索引上的索引项加锁来实现的，这种方式意味着只有通过索引条件来检索数据，innodb才能使用行级锁，否则将使用表锁
mysql中的事务隔离基本
- 可重复读隔离级别的实现
下面写的不对，实际增加的两个隐藏列为db_tx_id(创建当前版本数据的事务id)、db_roll_ptr(指向前一个版本数据的指针)，历史版本数据存储在undolog中，readView、数据版本链、mvcc算法看这个链接
已提交读 隔离级别在每一次进行普通 SELECT 操作前都会生成一个ReadView。
可重复读 只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView。
//下面的不对
在InnoDB中，给每行增加两个隐藏字段来实现MVCC(多版本并发控制)，一个用来记录数据行的创建时间，另一个用来记录行的过期时间（删除时间）。在实际操作中，存储的并不是时间，而是事务的版本号，每个事务都有一个版本号，每开启一个新事务，事务的版本号就会递增。
于是乎，默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：
SELECT
读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
INSERT
将当前事务的版本号保存至行的创建版本号
UPDATE
新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
DELETE
将当前事务的版本号保存至行的删除版本号
一个小知识点
UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
Bin Log
二进制日志 binlog，记录了数据库执行sql的写入性操作信息，如insert、update、delete操作，由mysql server层产生，以二进制数据的形式保存在磁盘上，无论使用何种数据库引擎，mysql都会产生bin log。
对于InnoDB引擎来说，只有在提交事务时才会产生bin log，但此时bin log还是存在binlog缓存中，最终需要被存储在磁盘中，它的刷盘时机，mysql通过sync_binlog参数来控制
- 0 不强制要求，由系统自行判断何时写入磁盘
- 1 每次commit前都要将bin log写入磁盘
- n 每N个事务commit，才会将一批bin log写入磁盘
sync_binlog最安全的配置是1，这也是mysql 5.7.7之后版本的默认配置，但也可以设置一个较大的值，牺牲一些一致性提升数据库性能。
bin log的应用场景有两个
- mysql 集群主从复制，在master端开启binlog，然后将binlog发送给slave，slave重放binlog达到主从数据一致
- 数据恢复，只要保存有足够的binlog数据，可以把数据库状态恢复至任意时刻的状态
binlog包含三种模式
- statement 基于sql语句，每一条修改了DB中数据的sql都会被记录到binlog中，优点是：不必记录每条发生变化的数据，日志量较小，节约IO，提高性能；
缺点是：在sql中使用函数有可能会导致主从数据不一致例如sysdate()等 - row 不需要记录sql，将每条发生改变的数据存储进binlog中，优点：不会出现存储过程、函数导致主从数据不一致的情况 缺点：数据量较大，尤其是alter table会让日志量暴增
- mixed statement和mix两种混合，一般情况下使用statement存储，对于statement无法保存的binlog使用row的形成存储binlog
保险用的是row
redo log、undo log
crash-safe能力，在事务提交的任意阶段，数据库宕机，重启后保证事务完整性，已提交的事务不会丢失，未提交的事务自动回滚，使用redo_log、undo_log、binlog实现
mysql的数据存储在磁盘上，直接对磁盘进行访问操作涉及到磁盘的I/O访问，效率较低，因此在mysql的innodb引擎中将数据读取到内存中一个缓冲池buffer poll里以数据页的形式进行存储，当mysql对数据进行查询、修改等操作时实际是对数据页进行操作。mysql中有一个很重要的原则是日志先行，为了避免buffer poll中的数据丢失和保障mysql宕机恢复后数据的完整性，为了满足事务持久性提供了redo_log，为满足原子性提供了undo_log。
例如在执行 update order set status = 2 where id =1；
假设 innodb_flush_log_at_trx_commit和sync_binlog都为1
- 在开始时会先判断id为1的数据是否在buffer poll缓冲池中，如果没有将相关数据页其加载到内存中
- 记录在undo_log中记录id=1的status的初始值
- 对buffer poll中数据页id=1的记录进行操作，
- 将相应的数据修改结果生成redo_log信息,存储进os buffer中，调用fsync()方法将redo log刷入磁盘，状态置为prepare
- 在mysql server层将数据操作记录存储进binlog buffer中，并将其刷入磁盘
- 将redo_log中记录置为commit状态（binlog刷入磁盘后对应redo log内日志才置为commit，若未刷置磁盘则为prepare状态）
- 事务commit
- 返回操作结果
redo_log和bin_log的区别：
9. redo_log是数据库引擎实现的，bin_log是数据库server层实现的
10. redo_log文件固定大小，采用循环写的方式来记录日志，当写到结尾时会回到开头重新写；bin_log是通过追加的方式，当文件大小不足时，则生成一个新的文件继续写
11. redo_log一般用于mysql宕机后恢复保证数据完整性，bin_log一般用于mysql主从同步(slave获取master的bin_log，将其写入自己的中继日志relay log中，将操作重放)，误操作后数据恢复
undo_log用于保证事务的原子性，当事务处理出现错误时，回滚到事务最初的状态
由于buffer poll和redo_log的大小并不能无限扩展，所有需要按照一定的节奏将buffer poll(数据库缓冲池)中的脏页(缓冲池中数据页的数据与磁盘中不一致)刷新至磁盘，这个刷脏页的时刻就被称作check_point
当数据库宕机恢复时，在rodo log内从check point(check point之前的日志对应的缓冲区内的数据均已刷至磁盘)点之后开始，判断redo log记录是否完整且为prepare状态，然后从binlog中找相应的事务记录，如果可以找到则重新commit redolog，并将数据恢复至buffer pool缓冲池中；若redo log日志为commit状态且在bin log中也可以找到记录也会恢复数据至缓冲池，否则执行undolog还原数据至缓冲区
146
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
