Mysql 知识点 

一、二叉树、B树、B+树
二叉树具有以下性质：左子树的键值小于根的键值，右子树
的键值大于根的键值。二叉树的查询效率就低了。





平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。




 




平衡多路查找树（B-Tree），系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来。InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位，默认16K。InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。










缺点：每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。








B+Tree相对于B-Tree有几点不同：


非叶子节点只存储键值信息。


所有叶子节点之间都有一个链指针。


数据记录都存放在叶子节点中



在数据库中，B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。







数据库索引采用B+树的主要原因是：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。
B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。










二、聚集索引与非聚集索引


聚集索引： 数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引,一般指主键。


非聚集索引：除聚集索引外其他都是的，包括普通索引，唯一索引，全文索引。叶子节点并不包含行记录的全部数据，




 　　而是存储相应行数据的聚集索引键。使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，
    		　　那么他还要进行第二次的查询，查询节点上对应的数据行的数据。
    		　　mysql索引一般建5-8个左右,索引过多，影响数据的操作(insert、update、delete)优化建议使用联合索引。大字段值不建议建索引。


　　创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
　　 		索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
    		　　当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。


 






三、如何解决非聚集索引的二次查询问题





 


  如建立符复合索引，只查询复合索引里的列的数据而不需要进行回表二次查询，注意最左原则





四、Mysql 索引失效   

    1、查询时索引列不能为null值，因为索引是有序的，建索引时无法确定位置。

    2、查询时 采用is Null,只能全表扫描。


    3、数据库数据量较少时，mysql判断全表查询快时将不使用索引。


    4、like查询以%开头，类型隐士转换、列参与函数运算、使用or查询，联合索引非最左

 





五、MySQL中Myisam与Innodb的区别



    

  • InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
    • 

  • InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
    • 

  • MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
    • 

  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
    • 







　　InnoDB支持事物、支持行级锁(基于索引来完成行锁)，Myisam支持表级锁,不支持事物。




　　InnoDB支持MVCC、外键，Myisam不支持,支持全文索引。




　　Myisam查询表较快。


 



六、Innodb引擎四大特点



 




插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)






七、Innodb的事务与日志的实现方式





 隔离级别：读未提交(RU)、读已提交(RC)、可重复读(RR)、串行，mysql默认可重复读，mysql通过MVCC(多版本控制)和 Next-key lock(间隙锁)解决了幻读。


MVCC原理：通过在每行纪录后面保存三个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。



MVCC是解决读写并行的幻读，而next-key lock 间隙锁 是解决写写并行的幻读。



Mysql日志：


慢查询日志（slow query log）:记录慢查询的日志文件


查询日志（general log）:记录所有对数据库请求的信息


重做日志（redo log）:


回滚日志（undo log）:


二进制日志（binlog）:用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步


错误日志（errorlog）:


中继日志（relay log）:




 


事务日志是通过redo和innodb的存储引擎插入缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志


的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”。





八、MySQL数据库cpu飙升到500%的话他怎么处理？



 


列出所有进程，观察所有进程，没有状态变化的(干掉)


查看超时日志或者错误日志



九、你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？



 


主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等


 



十、你们数据库是否支持emoji表情，如果不支持，如何操作？



 


如果是utf8字符集的话，需要升级至utf8_mb4方可支持


 


十一、Mysql索引查询优化








1.避免在索引列上使用IS NULL和IS NOT NULL，列字段尽量要有默认值.


2.避免 SELECT *


3.千万不要 ORDER BY RAND()


4.在Join表的时候使用相同的类型并将其索引


5.当只要一行数据时使用 LIMIT 1


6.EXPLAIN 分析你的 SELECT 查询


7.每张表都设置ID


8.不要条件列进行函数处理


9.避免使用 or，like 等字段




10.选取最适用的字段属性，尽可能减少定义字段宽度


11.用EXISTS替代IN、用NOT EXISTS替代NOT IN。




 


 

十二、分布式事物理解

 


1.事物的ACID。原子性、一致性、隔离性、持久性


2.分布式系统中经常出现断电、网络异常、应用宕机等问题导致不一致问题。


3.执行事务的时候数据库首先会记录下这个事务的redo操作日志，然后才开始真正操作数据库，在操作之前首先会把日志文件写入磁盘，那么当突然断电的时候，即使操作没有完成，在重新启动数据库时候，数据库会根据当前数据的情况进行undo回滚或者是redo前滚，这样就保证了数据的强一致性。


4.随着业务扩展，单库无法满足，需对数据库垂直分库，此时单个数据库ACID已经不能适应。


5.CAP定理，一致性，可用性，分区容错性。


6.程序中追求可用性要高于一致性，此时出现BASE定理,基本可用、软状态、最终一致性。


 


解决方案：


   



 




    

  • 两阶段提交
    • 

  • 基于可靠消息服务MQ的分布式事务，主流MQ不支持，
    • 

  • TCC（两阶段+补偿）二阶段提交失败，嵌入回滚代码，Confirm/Cancel服务的幂等性保障。增大业务复杂度。
    • 

  • 本地消息表：将分布式事务分成多个本地事物，然后通过异步mq发送消息保证一致性。
    • 





 

十三、mysql集群原理及方案

    采用主从复制，读写分离，主备模式




　　 　　　


 



主从复制原理：


   采用异步复制模型 
　　





十四、什么是索引？
     索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。
　  索引可以加快数据检索速度，但是创建索引和维护索引需要耗时，而且还要占用物理空间。
　  类型：主键索引、唯一索引、普通索引、全文索引。索引的数据结构B+树、hash。

十五、创建索引的原则？
　　1.较为频繁查询条件的字段；
　　2.字段区分度、离散值较高的；
　　3.索引字段长度不能太长；
　　4.索引的个数不要太多，能复用组合索引尽量复用；
　　5.索引列尽量指定为NOT NULL；
　　
十六、索引下推ICP

索引条件下推，Index Condition Pushdown，简称ICP，是MySQL内部通过索引查询数据的一种优化方法，简单来说就是将原本需要在Server层对数据进行过滤的条件下推到了引擎层去做，在引擎层过滤更多的数据，这样从引擎层发送到Server层的数据就会显著减少，从而优化性能。




            1、当 SQL 需要全表访问时，ICP 的优化策略可用于 range, ref, eq_ref, ref_or_null 类型的数据访问方式
　　　　2、只适用于 InnoDB 和 MyISAM 两种存储引擎
　　　　3、在 InnoDB 中，ICP 只适用于二级索引
		ICP 的目的就是为了减少回表导致的磁盘 I/O，而 InnoDB 的聚簇索引的叶子节点存放的就是完整的数据记录，只要索引数据被读到内存了，那么索引项对应的完整数据记录也就读到内存了，那么通过索引项获取数据记录的过程就在内存中进行了，无需进行磁盘 I/O；也就说聚簇索引上应用 ICP，不会减少磁盘 I/O，也就没有使用的意义了
　　　　4、不支持覆盖索引
　　　　5、不支持子查询条件的下推
　　　　6、不支持存储过程条件、触发器条件的下推



十七、count 字段区别


1.count(可空字段) 扫描全表，读到server层，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加
2.count(非空字段)与count(主键 id)     扫描全表，读到server层，判断字段不可空，按行累加。
3.count(1)    扫描全表，但不取值，server层收到的每一行都是1，判断不可能是null，按值累加。
注意：count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
4.count(*)   MySQL 执行count(*)在优化器做了专门优化。因为count(*)返回的行一定不是空。扫描全表，但是不取值，按行累加。



























            

            
posted @ 
2019-06-04 18:20 
作死的学 
阅读(423) 
评论(0) 
编辑 
收藏 
举报