Mysql(五)高级原理问题

1.Mysql的binlog有哪集中录入格式，有什么区别？

1）3种格式，statement,row,mixed

2. statement模式：

• 每一条会修改数据的sql都会记录binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高了性能。由于sql的执行是有上下文的，
• 由于sql的执行是有上下文的，因为保存的时候，需要保存相关的信息。同时还有一些使用了函数之类的语句无法被复制。（例如hostname函数）

3）row模式：

• 不记录sql语句上下文相关信息。仅保存哪条记录被修改。
• 缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

4）mixed模式：

• 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式
• 新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

2.Mysql数据表类型有哪些？

MyIsam innoDB CSV heap …

3.简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别

• MyISAM：

  • 不支持事务，但是每次查询都是原子的；
  • 支持表级锁，即每次操作是对整个表加锁；
  • 存储表的总行数；
  • 一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件；
  • 采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

• InnoDb：

  • 支持 ACID 的事务，支持事务的四种隔离级别；
  • 支持行级锁及外键约束：因此可以支持写并发；
  • 不存储总行数：一个 InnoDb 引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制；
  • innodb的数据文件，所有表的数据和索引共享存储在ibdata1文件夹里面（可以配置每个表一个文件），每个表都会有一个db.opt文件，存储该表的字符集和排序规则；还有一个表名.frm文件存储对应表的结构。
  • 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+树结构，文件的大调整。

4.百万级别的数据如何删除

• 索引文件单独存在的文件。对数据的增加删除修改，都会产生额外的对索引文件的操作，消耗额外的IO，降低效率。
• 在删除数据的时候，Mysql官方手册，得知删除数据的速度和创建的索引数量成正比。
• 因此具体删除百万级数据的流程：
  • 先删除索引（大概3分钟）
  • 删除真正的数据（不到2分钟）
  • 删除数据后，重新创建索引，此时数据很少了。（10分钟）
• 如果是直接删除，速度很慢。而且万一删除中断，一切删除回滚。

5.Mysql中的锁的理解

• myisam和innodb的表锁和行锁。（innodb在没有索引的情况下，默认使用表锁）

• 锁的类别：

  • 共享锁，读锁，可以加多个。
  • 排他锁，写锁，只能加一个。

• innodb引擎的行锁是怎么实现的？

  • select * from tb_with_index where id=1 for update.
  • id必须是索引键的咧，否则就是表锁

• 数据库的悲观锁和乐观锁：

  • 悲观锁（写多读少）：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务
  • 乐观锁（读多写少）：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

6.触发器

• 定义：触发器指的是某个事件发生时，自动执行这些代码。
• 使用场景
  • 数据库中的相关表实现级联更改；
  • 监控字段的更改；
• 触发器的类型
  • Before insert
  • After insert
  • Before update
  • After update
  • Before delete
  • After delete

7.超大分页怎么处理？

• select * from table where age > 20 limit 1000000,10，会把1000000数据查询出来，取10条
• 优化方式1：如果ID连续的话，select * from table where id > 1000000 limit 10
• 优化方式2：select * from table where id in (select id from table where age > 20 limit 1000000,10)
  • 于索引覆盖,要查询的所有字段都在索引中,所以速度会很快.

8.Mysql数据库CPU飙升500%怎么处理？

• 使用top命令查看是不是mysqld的进程导致的
• 如果是mysqld造成的。用命令show processlist,查看session运行的情况
• 判断是不是有消耗资源的sql运行，使用explain查看这个sql的执行计划。
• 有可能是每个sql耗费资源不多，但是突然之间有大量的session连接，导致cpu飙升，分析下为什么会激增。判断是否需要限制连接数等。

9.Mysql的分区？

• innodb分区：
  • 默认innodb的所有表的数据文件存在共享文件夹ibdata1中。若要innodb分区，必须独立表空间。修改配置文件 ，在/etc/my.cnf文件中设置innodb_file_per_table=1即可
  • 然后分区后，根据分区规则，会生成。p0.ibd p1.ibd …
• myisam分区：
  • 默认的myisam一个表，对应了3个文件：frm存储表结构，myd数据，myi索引。
  • 分区后生成：(p0.myd,p0.myi) (p1.myd,p1.myi)…
  • 分区的类型：
    • RANGE分区，基于属于一个连续空间的列值，把多行分配给分区。
    • HASH分区
    • key分区

10.Mysql中日期格式比较？

• 日期类型

  • datetime: YYYY-MM-DD HH:MM:SS，范围为’1000-01-01 00:00:00’到’9999-12-31 23:59:59’，允许字符串或数字进行分配值
  • timestamp：1970-01-01 00:00:00 到 2037年。设置值时只允许设置数字类型的值。
  • date：格式YYYY-MM-DD,范围是1000-01-01到9999-12-31.允许字符串或数字为此列赋值。

• 比较：

  • 范围不一样。timestamp的范围只能到2037年
  • 赋值不一样。timestamp只能赋值数字类型的值，其他的可以赋值数字类型或者字符串类型。
  • 对于timestamp，它把客户端插入的时间从当前时区转换为UTC(世界标准时间)进行存储。查询时，将其又转化为客户端当前时区进行返回。而Datetime不做任何改变，基本原样输出。

• 使用：

  • kgms使用的是datetime：

  数据库类型：   datetime
  java类型：    @LastModifiedDate  //private Date updateAt;
  

  • cv使用的是timestamp：

  数据库类型： timestamp
  java类型：  @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8") //时区问题
             public Timestamp createTime;
  

11.Mysql的query_cache_size,buffer pool涉及查询和更新？

• buffer pool： 查询缓存，默认是128M。建议优化为2G。

• free链表：

  • 出现原因： buffer pool缓存page页数据，一旦buffer pool被缓存满了，就会根据LRU算法清除某些page，但是清除后的内存空间是不知道在哪个位置的，因此需要将这些空闲的区域的位置记录下来，便于后面的数据缓存。
  • free链表含义：free链表就是记录这些buffer pool中的空闲区域。使用链表的意义是，增删快。

• flush链表：

  • 出现原因：对数据操作进行update。会直接修改buffer pool中的数据，将这些数据页放在flush链表中，后台开一个线程去真正修改同步数据。
  • flush链表含义：存储update的数据对应的page页的控制快，后台线程去持久化数据。

• lru链表：

  • 出现原因：buffer pool的数据页满了
  • lru链表含义：最近使用最少。新缓存的数据，加入到链表的头部。如果再次使用到了lru链表上的数据页，会把位置移动到链表最头部。链表靠后的数据页就是最少使用的数据。
  • 存在问题：mysql不是用上诉方式。因为：如果使用select * from t，把所有的全表数据缓存到buffer pool，可能就会导致lru链表上的所有数据全部清除，甚至于为了缓存select * 缓存都不够。
  • mysql的lru链表：5/8存储热数据区，3/8存储冷数据区。
    • 查询mysql，第一次缓存的数据，加入到冷数据区。
    • 再次查询mysql，如果缓存的数据是一样的数据，并且时间t2-t1>1s，转移控制块到热数据区。

12.Mysql的redo log？

• 概念：mysql执行update语句，先修改buffer pool中的缓存页数据。然后会生成一个redo log的文件，最后后台线程根据redo log的文件去持久化真正的数据。
• 参数：为什么要先写入到redo log中，而不是立即去持久化？
  • update语句，需要看事务。是否是真正的commit了。执行了commit后，才会真正持久化数据（拿出redo log的数据去执行）。
  • 立刻去持久化：缓存页中的数据，在磁盘上是随机IO，不是连续的，速度慢。
  • 写入redo log：提前在磁盘上分配好了两块空间ib_logfile0,ib_logfile1,写入redo log是顺序IO，速度很快。
• 参数（优化512M）：
  • 配置redo log的磁盘文件大小：innodb_log_file_size=512M
  • p位置redo log的磁盘文件个数：innodb_log_files_in_group=2
  • log buffer：真正要执行的redo log日志，生成的序列化对象，缓存的空间，配置为16M。
  • innodb_flush_log_at_trx_commit=1
• 文件大小的影响：
  • 文件一旦满了，会触发check_point，立刻把该文件中的redo log（未执行的update）执行，数据持久化。
  • 其他什么时候，数据持久化：
    • innodb_flush_log_at_trx_commit=1 执行的update语句事务提交时，立刻把redo log进行持久化。
    • innodb_flush_log_at_trx_commit=2，事务提交的时候，立刻将redo_log写入操作系统缓存中，不会直接将redo_log持久化，不会丢数据。
    • innodb_flush_log_at_trx_commit=0，不会立刻持久化，后台线程持久化。可能会丢失数据。

13.Mysql的binlog undolog？

• redo log是innodb里面的。binlog是属于mysql server的
• undo log：如果update的语句，回滚了rollback。undo log记录的是update之前的数据。

14.Mysql的Double write buffer？

• 问题：update生成的redo log，开始去持久化数据的时候。一个page也是16kb，操作系统的page是4kb，因此，一个数据页要写4次。如果写了2次服务器挂了。再次启动mysql，数据库得到的数据就是不完整的数据。
• 需要一个额外的double write buffer，先缓存一次。

15.Mysql中的thread_cache_size？

• 含义：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

  即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。

• 设置建议：

  • 短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁；
  • 长连接，可以设置成小一点，一般在30-50左右
  • 1G内存-8,2G内存-16，3G内存32左右

• 生产环境：38（真实）