mysql汇总

1 mysql如何实现索引机制
     索引分为b+树索引（存储全量数据）
     hash索引（辅助查询可以更快定位数据是系统自动生成的和b+树一起用）
      全文索引
2innodb索引和myisam索引实现区别是什么
     共同点使用b+树
     不同点 innodb数据文件是主索引文件存储在一个文件
                myisam索引文件和数据文件分离 存储两个文件
3一个表没有创建索引 还会创建b+树吗
    如果有主键会创建聚簇索引 会创建b+树
     没有主键也会创建b+树 没有主键b+树会自动生成一个隐式的rowid
4b+树实现原理
  首先是一页（一页就是一个磁盘快 代表一次io）一页存储 每一页都是16kb 存储多少数据看数据的大小
  b树在每一页存储了主键和主键对应的数据
 b+树非叶子节点存储的是主键值 在最下面的叶子节点存储其他数据
 每一页包含主键最大值最小值 是否是叶子节点 主键对应的页码
  查询数据先看跟的非叶子节点那个范围，在向下一级一级查找对应的数据就好

5聚簇索引和非聚簇索引b+树 实现有什么区别
   聚簇索引 
特点 索引和数据保存在同一个b+树
        页内数据记录按照主键大小顺序形成单项链表
        页和页之间根据页中记录的主键大小形成双向链表
       非叶子节点存储记录也就是主键id和页号     叶子节点存储完整的数据
  优点  数据访问快 索引和数据都在同一个b+树 因此聚簇索引获取数据快
            对主键排序和范围查找快  聚簇索引排列顺序 查询显示一定范围数据时候 因为数据是紧密相连的 数据库可以从更少的数据快获取数据，节省io操作，
    缺点 插入严重依赖插入顺序 按照主键自增新增数据是最快的 其他情况会出现页分裂 影响性能 更新主键代价高 因为会导致被更新的行移动 主键不可更改
      限制  只有innodb支持聚簇索引 
             数据物理存储只能有一种 每个mysql表只能有一个聚簇索引 如果没有定义主键 innodb会选择一个非空的唯一索引代替，如果没有这样的列 会隐式定义rowid为聚簇索引  主要就是让使用者有序的存储数据
   非聚簇索引（二级索引 辅助索引）会创建一个自己的b+树
      聚簇索引按照主键查询才会有效果，非聚簇索引是除了聚簇索引都是非聚簇索引，
     非聚簇索引查询会根据非主键索引范围查找，它的叶子节点存储的是聚簇索引的主键id  找到对应的id 在根据id去聚簇索引中查找具体的数据是属于回表操作。
6 b+树聚簇索引查找匹配逻辑
   首先在跟非叶子节点根据范围查找主键id对应的页 ，直到找到非叶子节点具体数据，
7非聚簇索引查找匹配逻辑
    首先根据非聚簇索引查询跟的非叶子节点的范围（如果是字符串会转换成ASCII）对应的页码 直到找到叶子节点数据 这时叶子节点存储非聚簇索引和聚簇索引，要查询全部数据 需要根据聚簇索引主键id去聚簇索引的b+树查询
8平衡二叉树 红黑树 b树 b+树
  普通二叉树 就是树形结构 容易形成单项链表 因为要是一直往一边写数据就是单项链表 解决上面需要变成平衡二叉树  ，特点树左右两边高度差 不超过1 自旋左右平衡 只能存一份数据，数据量少没事 多了会增加自旋次数增加io  红黑树 hashmap存储 两次旋转达到平衡，允许向一端存储二叉树数据 
 b+树非叶子节点存储主键会同时存在子叶子的节点里面 由小到大 b+束非叶子节点用于索引不保存数据，叶子节点保存数据
   b树非叶子节点保存索引和数据记录
9 一个b+树能存储多少数据
   按照三层b+树  一页page是16kb 索引按照10个字节 就是跟的非叶子节点可以存储1600条主键和页码指针记录，跟层的子节点也是一样的 ，这1600条主键对应子节点1600页 一页1600条数据  第三层存储的数据是具体的数据按照占用1kb 一页可以存16条 三层就是1600×1600×16
10 为什么建议使用自增主键
   自增主键能维持底层数据顺序写入 ，读取可以b+树的二分查找定位 支持范围查找 数据是有序的
11使用int自增id 后最大id是10 删除id10和9，在添加一条数据，最后添加id是多少，重启mysql后再添加一条id是几 
   删除之后 没有重启  会延续删除之前最大id开始递增  重启之后会从现有的数据最大id开始递增  因为自增主键 auto increment的值重启之后会重置
12索引优缺点
    优点 聚簇索引 顺序读写 范围查询 有顺序
            非聚簇索引 条件查询避免全表扫描 范围排序分组返回行id 再回表查询完整数据，覆盖索引不需要回表
        空间代价 
  建立一个索引会建立一个b+树 每一个树的每一个节点都是一个数据页 一页16kb的空间存储 ，一颗很大的树 由许多数据页组成，需要一定空间
         时间代价 
         每次对数据增删改 需要修改各个b+树索引 可能会对节点和记录排序造成破坏，存储引擎需要额外的时间记录位移页分裂去维护好节点的排序，如果创建很多索引 每个索引都需要维护 造成性能降低
 13 使用索引一定能提升效率吗
   数量少没有必要 如果只是增删改情况也没有必要 唯一索引会影响效率 在新增会先检查b+树有没有值 没有才会新增 有两次操作
14如果是大段文本内容怎么创建索引
    分表存储  然后创建索引 需要指定大文本的索引长度 一般是开始的前几个字创建索引 
  使用es为大文本创建索引 
15 crud聚簇索引和非聚簇索引区别
   聚簇索引插入比非聚簇索引插入速度慢，因为插入要保证主键不能重复  聚簇索引范围查询效率高因为是有序的 
   非聚簇索引访问需要两次索引查找，第一次找到主键 第二次根据主键找到行数据
 16非聚簇索引为什么不存储数据地址 而存主键
    聚簇索引要是进行分页查询 重新排序操作数据有可能会移动
17什么时回表查询
   使用非聚簇索引查找 返回的不是非聚簇索引的字段 
 18什么是覆盖索引
   使用非聚簇索引返回非聚簇索引
19 为什么需要回表查询 直接存数据不可以吗
    b树和b+树最主要区别是把数据从每一个节点中移动到叶子节点 这样树比较矮和胖 查询效率高 如果树高会自旋次数多 
   聚簇索引已经存储了一份数据 要是非聚簇索引在存储一份 数据成倍增加

 20 innodb表主键删除 怎么回表查询

   innodb会生成rowid辅助回表查询

21 什么是联合索引

  遵循全职匹配和最左原则匹配，联合索引创建顺序不一样，结果也是不一样的 

创建索引语句 creat index idx-c1c2 on user （c1，c2）

22 什么是唯一索引 是否影响性能

  就是字段的值只会出现一次，影响性能因为每次插入需要检查字段值是否存在，业务上具有唯一特性的字段 即使是多个字段组合 也必须创建唯一索引 如身份证号 手机号

23什么时候适合创建索引 什么时候不适合

  适合 频繁作为where条件查询的字段 关联字段需要建索引，排序字段 统计字段可以建索引

不合适 频繁更新的字段 可以确定表数据很少的情况下，参与mysql函数计算的字段

24什么是索引下推

 主要目的是提升索引效率， 

如果没有索引下推 select name ，age from user where name “%涨” and age =1（name和age为索引） ，第一步会把name等于涨**的先全部查找出来，之后回表操作把完整的数据放到内存，第二步把age=1数据筛选出来 之后进行再次回表把数据拿到内存，根据筛选条件在索引树筛选第一个条件 之后回表获取结果集，在进行其他字段筛选再次回表查找

5.6之后优化功能

  开启索引下推，在条件查询时候当前索引树如果满足全部筛选条件可以在当前树完成全部筛选条件过滤，得到最终结果再进行回表查找结果集，就是先name条件查找 之后下推到age条件查询，之后回表查询结果集

25哪些情况索引失效

  计算函数导致索引失效 sun 求和 最大值最小值 截取字符串

  like 以 “%**开头的索引失效

  不等于索引失效 

   类型转换索引失效

   复合索引 最左边没有用到

  is not null 有可能走索引 要是null值比较多可能走 走不走索引是mysql优化器决定的

26为嘛like以百分号开头索引失效

  其实并不会完全失效 ，覆盖索引下会出现type等于index 表示遍历了索引树 

覆盖索引没有用到 type等于all 没有高效使用索引是因为字符串会转换成accli码 生成b+树安首个字符串排序 类似复合索引没有用最左列字段查询一样

27 使用order by能否走索引

 order by没有过滤条件不走索引 group by 没有过滤条件也可以用上索引

28 mysql内部支持缓存查询吗

 mysql 收到客户端查询sql之后 只需要对其进行权限验证，就会通过query cache 查找结果，mysql 5.7支持内部缓存 8.0之后废弃

原因是sql必须完全一样才导致cache命中，缓存在分库分表下不起作用，表结构或者数据发生改变，基于表的cache全部失效，可以用redis替代缓存

29mysql内部有哪些核心模块

 客户端-处理连接-查询缓存-语法解析- 查询优化 innodb -文件系统

30mysql内部如何执行sql

 首先mysql客户端和服务端连接，通过sql接口发送sql语句，先检查缓存，如果有直接返回，没有进行语句解析，预处理器根据mysql进一步检查解析树是否合法 这里检查数据表和数据列是否存在，然后预处理进行查询重写生成一个新的解析树，查询优化器将解析树转换为执行计划，最后进入执行器阶段 查询数据返回客户端。

31一张表创建多个索引 在那个阶段进行索引优化

  在优化器阶段，查询优化器

32mysql存储引擎

 innodb myisam 

innodb可以保证事物 数据文件结果为 表名.frm存储表结构 表名.ibd 存储数据和索引

33mysql存储引擎架构

 内存区域 

          buffer pool 在innodb访问表记录和索引时会在buffer pool的页中缓存，以后使用减少磁盘io，提升效率，主要缓存热的数据页和索引页

          log buffer 用来缓存redo log

          adaptive hash index 自适应哈希索引

          change buffer 应用在非唯一普通索引页，不在缓冲池，对页进行写操作，不会立刻将磁盘页加载到缓冲池，仅是记录缓冲变更，等以后数据被读取时候，再将数据合并（merge） 恢复到缓冲池的技术，写缓冲池降低写操作磁盘io

    磁盘区域

           分为表空间和重做日志

           表空间 分为系统表空间，临时表空间，常规表空间 undo表空间 系统表空间包括 innodb数据字典，双写缓冲区（doublewrite buffer） ，修改缓存（change buffer）undo日志等

          redo日志存储就是在log buffer刷到磁盘的数据

34myisam和innodb区别

  myisam 外键（不支持） 事物（不支持）行表锁（表锁，即使操作一条数据也会锁住整个表） 关注点（并发查询）

 innodb外键（支持） 事物（支持）行表锁（行锁，操作只锁一条数据，不影响其他数据） 关注点（并发写，事物）

35mysql事物 

   事物 数据库最小单元，要么全部成功 要么全部失败 事物都有ACID特性

  A 原子性 只做一个步骤 全部成功或者全部失败

  C 一致性 一个状态变更到另一个状态保证数据的结果最终一致性

  I 隔离性 一个事物执行不被其他事物干扰

  D持久性 一个事物提交，对数据库的数据改变是永久的保存到磁盘中

36并发事物有哪些问题

 什么是脏读 （dirty read）

      一个事物在处理过程中读取到另一个事物还没有提交的数据（事物A将name改成张三，该事物还没有最终提交，事物B读取这条数据，读到的name是张三 这时事物A出现异常数据回滚，产生脏读）

  什么是修改丢失

       事物A读取一条数据，事物B也读取了这条数据，这是事物A修改完数据提交，事物B也修改完提交，这时事物A修改的数据就丢失了，事物B修改的结果覆盖了事物A的结果

 

 什么是不可重复读（同一条数据 重点修改 不同结果）

        事物A多次读取一条数据，事物B也读取了这条数据并修改成功，那么事物A多次读取的数据结果会产生不一样，这就是不可重复读

什么是幻读（重点新增或者删除数据导致查询结果不一样）

    同一个事物内多次查询返回的结果不一样（一个事物读取几行数据，事物还没有结束，另一个事物插入一些数据，在随后的查询，第一个事物读取的数据比原来读取到多了 产生幻读）

37 mysql 怎么避免并发事物问题

  mysql内部通过加锁方式实现的，就是配置事物的隔离级别

 隔离级别分为

     读未提交 RU 脏读（会） 不可重复读（会） 幻读（会）

     不可重复读 RC 脏读（不会） 不可重复读（会） 幻读（会）

     可重复读RR 脏读（不会） 不可重复（不会） 幻读（会） mysql默认的隔离级别   

      串行化 脏读（不会） 不可重复读（不会） 幻读（不会）

38 怎么选择事物隔离级别

    隔离级别越低，，事物请求锁越少，性能越高，一般用默认，如果系统有高频读写并对一致性要求高可以用串行化

修改隔离全局，修改mysql.ini配置文件，在最后加上（READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE）

transaction-isolation = REPEATABLE-READ

39mysql事务隔离级别怎么实现的

  隔离级别实现主要是读写锁和mvcc

40什么是事务一致性和非锁定读和锁定读

 锁定读 使用读写锁，是事务隔离级别实现的

  1每次读操作需要获取一个共享读锁，每次写操作获取一个写锁

 2共享锁不会产生互斥，共享和写锁有互斥

 3有竞争下需要等待一方释放锁

 锁机制 解决多个事务同时更新数据

   行级锁解决多个事务更新同一个数据

   间隙锁 解决多个事务更新多条数据

    非锁定读 使用mvcc多版本控制实现

41mvcc内部细节

 多版本并发控制，实现依赖于 隐藏字段，read view undo log

 隐藏字段分为

      1 DB_TRX_ID用来表示最近一次本行记录做修改的事物标识符，就是最后一次修改，本行记录事务id，如果有delete操作在innodb存储引擎内部属于一次update，更新行中一个特殊位，将行标识为已删除，并不是真正删除

   2DB_ROLL_PTR回滚指针，执行该行的undo log，如果没有被更新 为空

  3 undo log 除了用来回滚数据，还可以读取可见版本数据，以此实现非锁定锁

42mysql事务一致性原子性怎么实现

  通过锁和mvcc实现了执行过程的一致性和原子性，其次在灾备通过redo log会把事务在执行过程对数据库做的全部修改记录下来，在之后系统崩溃重启可以把事务的修改恢复

 43事务持久性

 使用redo log 保证事务持久性，当事务提交 需要先把事务的所有日志文件进行持久化，出现断电可以从redo log 恢复，如果写入日志失败也意味着修改失败事务回滚

44表级锁和行级锁

  表级锁 串行化整张表加锁，事务访问表数据需要申请锁，

 行级锁 只锁一行数据，

 共享锁（读锁，S锁）去它事务可以继续加共享锁，但不能加排它锁

排它锁（写锁， X锁）进行写操作申请获取锁，其他事务不能获取锁

 间隙锁 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

临键锁（Next-Key Locks）锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁

45是否用过select for update 会产生那些操作

 用这个会让select语句产生一个排它锁，这个锁和update一页，会使两个事务无法同时更新一条数据，只用于innodb，必须在事务快中（begin/commit）才生效，

如果查询条件是索引那么是行级锁，不是索引就是表级锁

46mysql死锁

 死锁和锁等待两个概念，如果没有开启事务，多个客户端insert操作，当多个事务同时持有和请求同一个资源上的锁产生循环依赖时候产生死锁。

排查 正在运行的任务

  show full processlist 找到卡住的进程

解开死锁 unlock tables 

  查看当前运行事务

select *from information_schema.innodb_trx

当前出现锁

select *from information_schema.innodb_locks

 查看innodb锁状态

  show status like “innodb_row_lock%”

解决 死锁无法避免，上线前进行严格压力测试

  快速失败 innodb_lock_wait_timeout 行锁超时时间

  拆分sql 严禁大事务

  利用索引 减少表锁 

 无法避免时 操作多张表尽量相同的顺序访问，避免形成等到环路，使用排它锁 for update

47mysql日志

错误日志（error log） 慢查询日志（slow query log） 重写日志（redo log） 回滚日志（undo log） 二进制日志（bin log）

age＝18修改成age＝19 首先会记录undo log（age＝18，数据修改之前值，作用事务失败之后回滚，利用mvcc访问老版本的数据）之后再记录redo log（记录在磁盘，age＝19 数据修改之后的值，在服务器宕机重启后，可以让事务继续执行，恢复更新内存但是由于宕机没有刷入到磁盘的那部分数据）

最后执行bin log （不包括查询sql，记录对表结构和数据修改sql，作用灾备恢复数据，做主从同步）

48mysql 日志是否是实时写入磁盘的

 磁盘写入慢，参数sync_binlog 写入策略

sync_binlog=0，1，大于1

等于0不会马上写入磁盘，先写page cache 有日志丢失存在 等于1每次提交事务都会指向fsync写入磁盘，是强一致性的 大于1先写pagecache 积累了n个事务之后才会fsync到磁盘 ，有日志丢失存在  

49可以用mysql直接存储文件吗

 可以用blob存储二进制 ，需要高效查询并且文件很小可以存储，文件比较大或者更新频繁不合适

50emoji乱码怎么办

 使用utf8mb4 mysql5.5.3 之后增加了这个编码

51怎么存储ip地址

 1使用字符串会 2使用无符号整形的int（支持范围查询）

52长文本存储 大段文本怎么设计表结构

 使用test类型存储，存储到es搜索引擎，指定长度，分段存储 后创建索引

53存储日期

timestamp有时区的转化

datetime 没有存什么就是什么 

不能用字符串因为没有办法范围查询

54char和varchar

 char 固定长度，没有碎片，尤其是更新频繁，存储读取数据快，缺点空间冗余，，对于非固定长度的字段浪费空间

varchar 是弹性长度，存储空间根据存储内容变化，优点节省空间，缺点读取和存储需要读取信息计算下标获取数据

55mysql调优

 sql调优，表结构设计调优，索引调优，慢查询调优，数据库参数调优，操作系统参数调优

官方自带的调优 explain mysqldumpslow慢查询

 监控线上环境查询比较慢的sql 开启慢查询 默认没有开启，set global slow_query_log=1只对当前窗口生效，重启服务器失败 生成show.log文件用完之后关闭慢查询 影响性能

explain

id select type（是什么查询，简单查询还是嵌套查询） table partitions（分区） type（是否用索引 all ref rang） possoble（可能用到的索引） key（用到的索引） rows（返回了多少行数据） filtered（命中率是多少，用到的数据） extra（排序 索引的使用情况）

56mysql数据库cpu飙升 

 首先定位问题，是否树mysql引起的，是否是系统的问题，最后看mysql 先使用top观察mysql的cpu利用率，切换到数据库，使用show full processlist 查看回话，观察那些sql消耗，定位具体sql，服务器是否运行了其他服务 pref top 工具 分析那些函数引发cpu高

57分库分表思路

 本地aop实现拦截sql 改写 路由处理

分库分表问题 执行效率降低 表结构很难再次调整，引发分布式id问题 产生夸表sql

58count（*）和count（列名）区别

count（*）会统计值为null的行

count（列名） 不会统计值为null的行

59表数据查询很慢，思路

  前段优化 -减少查询合并请求，多个请求需要的数据尽量一条sql拿出来，避免无效刷新

  2多级缓存 不要触及到数据库，把数据热点数据缓存到redis 底一致性缓存，

 3使用合适的字段类型 比如varchar换成char

  4高效使用索引，使用explain观察索引使用情况，检查select字段最好满足索引覆盖 复合索引注意观察key_len索引使用情况

   5检查查询是否可以分段查询，避免一次性拿出过多无用数据

  6读写分离

  7分库分表