1.mysql
一种关系型数据库
2.什么是索引
索引是一种数据结构，帮助快速进行数据查找。
2.1聚簇索引
B+树是左小右大的顺序存储结构，节点只包含id索引列，而叶子节点包含索引列和数据，这种数据和索引在一起存储的索引方式叫做聚簇索引。
在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引，否则就是非聚簇索引. 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引.如果没有唯一键,则隐式的生成一个键来建立聚簇索引
2.2主键索引和唯一索引的区别
1.主键索引和唯一索引都不可以重复,主键索引不能为空，唯一索引列能为空
2.主键是一种约束，唯一索引是一种索引
2.3覆盖索引
覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询
2.4回表查询
回表查询就是辅助索引没找到指定的列时，需要再次回表查询，查询聚簇索引里面的字段，辅助索引只存储索引列和主键列。
3.mysql数据库引擎
3.1 myisam引擎
myisam引擎是5.1版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据是分开存储的。
存储三个文件：表的定义 数据文件 索引文件
myisam也是可以选择b+树存储的，不是聚簇索引而已。
myisam 使用b+树做索引时，叶子节点存储的是数据的地址
3.1 innodb引擎
innodb是基于聚簇索引建立的，和myisam相反它支持事务、外键、行锁，并且通过MVCC来支持高并发，索引和数据存储在一起。存储两个文件：表的定义 数据和索引存储文件，以主键进行聚集存储，把数据保存在叶子节点
InnoDB存储引擎是索引组织表
mysql索引按照数据结构体来分有hash索引和B+树索引。
哈希索引基于hash表实现，类似于Java中的HashMap，通过计算key的hash值映射对应的value，在不发生hash冲突的情况下时间复杂度为常数级别，MySQL的hash索引会对所有的索引列计算一个hash码，由于hash的索引的特点，它的缺点也显而易见，只有精确匹配索引所有列的查询才有效，hash索引数据也并不是按照索引值顺序存储的，所以也无法用于排序，只支持等值查询，不支持范围查询。
我们经常使用的innnoDB默认使用B+树索引。
无论是何种索引，每个页的默认大小都是16KB。
主键索引为什么不用哈希索引，B树，平衡二叉树？
哈希索引只能做等值查询，无法完成范围搜索，hash值是无序的
平衡二叉树：左子树和右子树的高度差不超过1
范围查询需要回旋查询，效率会非常低
等值查询随着树高度的增加复杂度提高。
B树：数据存在叶子节点和非叶子节点,查询不稳定,如果数据在根,查询速度快.
b树：解决了高度问题，等值查询比较快，树越矮查询速度越快，也是有回旋查找问题（范围查找需要一次中序遍历）
红黑树：树的高度随着数据量增加而增加，IO 代价高。
b+树：叶子节点既存储key也存储value（真实数据）,所有的key都在叶子节点上。
所有叶子节点形成了一个链表（双向链表），所以范围查询非常快。相当于是自动排好了序
用链表的方式解决了回旋查找问题
B+树.png
4.索引原理，最左匹配原则，explain分析
image.png
select_type：查找类型，简单的simple select 、union、subquery等
table:查找的表
type:这是重要的列，显示连接使用了何种类型。
从最好到最差的连接类型：const、eq_reg、ref、range、index和ALL
ref：Join语句中被驱动表索引引用的查询。
extra:排序是否使用了索引
possible keys：可能使用的索引
key:实际使用的索引
key_len:索引长度
ref:显示索引的哪一列被使用了，如果可能的话，是一个常数
rows:需要查询的数据行数
filtered：按表条件过滤的行百分比
5.破坏索引的情况
1.使用不等于查询,
2.列参与了数学运算或者函数
3.在字符串like时左边是通配符.类似于'%aaa'.
4.当mysql分析全表扫描比使用索引快的时候不使用索引.
5.当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.例如联合索引（a,b）破坏索引情况 where a>xxx and b=1 由于当a不确定时，b的大小是不确定的，所以无法使用二分法查找。
6.数据库ACID,事物隔离级别
6.1 原子性（undolog保证）
一个事物内的所有操作共同组成一个原子包，要么全部成功，要么全部失败，
6.2 隔离性
多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。
6.3 一致性
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
6.4 持久性（redolog保证）
当一个事物提交后，数据库的状态永久的发生了改变。即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作
6.5 脏读
一个事物处理过程中读到了另一个未提交的事物中的数据 （修改时允许读取）
6.6 不可重复读
不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了（读取时允许修改）
6.7 幻读
一个读取事务读取时，另一个插入事务（注意此处是插入）插入了一条新数据，这样就可能多读出一条数据，出现幻读。（读取时允许插入）
6.8 数据库提供的四种隔离级别
Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
Repeatable read (可重复读)：可避免脏读、不可重复读的发生。（默认）
Read committed (读已提交)：可避免脏读的发生。
Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
串行化：这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.
7 MVCC,如何保证ACID
首先要了解MVCC，MVCC叫做多版本并发控制
实际上就是保存了数据在某个时间节点的快照。
mvcc 如何保证读已提交 可重复读
undolog 版本链 readview
读已提交：通过版本链和readview控制
快照读
trx_id:事物ID
roll_pointer:回滚指针
readview:
creator_trx_id:当前事物ID
up_limit_id ：低水位 当前活跃事物的最小ID
low_limit_id:高水位 当前活跃事物的最大ID
trx_ids：
在每次语句执行的过程中，都关闭read_view, 重新在row_search_for_mysql函数中创建当前的一份read_view。这样就会产生不可重复读现象发生
在repeatable read的隔离级别下，创建事务trx结构的时候，就生成了当前的global read view。使用trx_assign_read_view函数创建，一直维持到事务结束。在事务结束这段时间内 每一次查询都不会重新重建Read View ， 从而实现了可重复读
image.png
7.1ACID如何保证
A 原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C 一致性一般由代码层面来保证
I 隔离性由MVCC来保证
D 持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复。buffer中存储部分数据，写数据先写入buffer然后在同步到磁盘，但是这样如果buffer异常了，导致磁盘没写入，造成数据丢失，因此引入了redo log，记录每次数据操作，预写模式，redolog减少无效i/o，进行追加.
7.2三种写入日志方式
0:等待主线程刷新
1:提交事物将redolog日志同步写入磁盘
2.提交事物异步写入磁盘，不能完全保证commit时会写入日志
7.3mysql中的日志类型：
1.二进制日志（binlog）
它记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。常用于数据恢复和主从复制。(用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。)
2.中继日志（relay log）
中继日志用于主从复制架构中的从服务器上，从服务器的 slave 进程从主服务器处获取二进制日志的内容并写入中继日志，然后由 IO 进程读取并执行中继日志中的语句。
3.redo log
redo log通常是物理日志，记录的是数据页的物理修改.
更新的时候，先写到 redo log 和内存里，这次更新就算是结束了。等到合适的时机再写到磁盘里，大大减小了写磁盘的次数。redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。
4.undo log
undolog用来回滚行记录到某个版本,undo log一般是逻辑日志，根据每行记录进行记录。
保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读.
5.redo log 和binlog的区别
redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
8.Mysql乐观锁和悲观锁：
从锁的类别上来讲,有共享锁和排他锁.
共享锁：也叫读锁，当用户需要读取数据时，加上共享锁，共享锁可以同时加上多个
排他锁：也叫写锁，只有当前事物可以进行读写操作
行锁：锁住一行数据，事物没提交其他事物无法操作这行数据
表锁：没有使用到索引，事物执行操作时导致了锁住了整张表，其他事物无法执行其他操作
间隙锁：间隙锁生效无法同时执行插入、更新操作，比如a有1，3，5，7四个值的行，中间2，4，6三个值的行没有，此时想要插入2，4，6中的行是不可以的
场景：我开启事物执行一条数据更新 条件是a=>1 and a<=7,此时没有提交事物，再执行插入a=2或4或6的数据行，无法插入进去，他把整个a的范围锁住了。避免使用间隙锁，尽量不要用范围查询条件去更新数据。
8.1乐观锁：总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。
8.1.1版本号机制
update tbl1 set name=XXX, version=version+1 where game_id=XXX and version=XXXX; //如果版本号和之前查到的不一样，说明有其他线程对该数据进行了修改
8.1.2CAS操作
内存值、预期值、更新值 （直白就是sql语句where后面的条件就是预期值(game_id,log_date)，数据库中的值就是内存值(game_id,log_date)，要更新的值就是更新值）update tbl1 set name=XXX where game_id=XXX and log_date=XXXX;
8.2悲观锁
悲观锁：总是认为会产生并发问题，其他线程会改变数据，共享锁和排它锁 （必须关闭数据库自动提交事物，必须明确的指定主键，否则锁会锁住整张表）
总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起。可以依靠数据库实现，如行锁、读锁和写锁等，都是在操作之前加锁，在Java中，synchronized的思想也是悲观锁。
注意：要使用数据库的悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。
8.2.1悲观锁分为两种：共享锁(读锁)和排它锁（写锁）
共享锁是其它事务可以读但是不能写：实现方式是在sql后加lock in share mode，比如select ... lock in share mode
排他锁是只有自己的事务有权限对此数据进行读写：实现方式是在sql后加for update，比如select ... for update ，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁
通过对比，发现for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读
从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适
使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。
9.数据库优化，如何分库，如何分表
9.1分库分表
首先分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直后水平。
基于现在微服务拆分来说，都是已经做到了垂直分库了
垂直分表：如果表字段比较多，将不常用的、数据较大的列做拆分，建新的表存储
水平分表：按日，按用户ID，等等，根据具体业务场景划分
9.2分表后如何保证ID唯一
. 1.设定步长，比如1-1024张表我们设定1024的基础步长，这样主键落到不同的表就不会冲突了。
. 2.分布式ID，自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种
. 3.分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样
. 4.redis维护唯一标识ID，每次取一定长度的ID，存在本地，需要的时候取出即可。
10.mysql主从、集群搭建、binlog
10.1binlog同步数据过程
1.master提交完事务后，写入binlog
2.slave连接到master，获取binlog
3.master创建log dump线程，推送binglog到slave
4.slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中
5.slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
6.slave记录自己的binglog
binlog主从同步.png
10.2全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响
10.3半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成
主从延迟解决方案：
1.针对特定的业务场景，读写请求都强制走主库
2.读请求走从库，如果没有数据，去主库做二次查询
11.MySQL的binlog有几种录入格式?分别有什么区别?
有三种格式,statement,row和mixed.
1.statement模式下：记录单元为sql语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.
优缺点：不用记录每条数据的变化，需要的存储空间更小，可能会造成数据丢失。
2.row级别下：记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.
优缺点：记录了每条数据的变化，数据完整，需要的存储空间更大
3.mixed.：是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。
12.mysql和psql的区别
1.与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难
2.PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。同时PostgreSQL是多进程的，而MySQL是线程的，虽然并发不高时
MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力
3.对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；
4.PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
5.PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
6.数据类型比mysql丰富，支持json等数据类型，因为它有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型，
13.行数据库和列数据库的区别
在已知的几种大数据处理软件中，Hadoop的HBase采用列存储，MongoDB是文档型的行存储，Lexst是二进制型的行存储。
行存储的写入是一次完成。如果这种写入建立在操作系统的文件系统上，可以保证写入过程的成功或者失败，数据的完整性因此可以确定。列存储由于需要把一行记录拆分成单列保存，写入次数明显比行存储多，再加上磁头需要在盘片上移动和定位花费的时间，实际时间消耗会更大。所以，行存储在写入上占有很大的优势。
再谈两种存储的数据分布。由于列存储的每一列数据类型是同质的，不存在二义性问题。比如说某列数据类型为整型（int），那么它的数据集合一定是整型数据。这种情况使数据解析变得十分容易。相比之下，行存储则要复杂得多，因为在一行记录中保存了多种类型的数据，数据解析需要在多种数据类型之间频繁转换，这个操作很消耗CPU，增加了解析的时间。所以，列存储的解析过程更有利于分析大数据行存储的写入是一次性完成，消耗的时间比列存储少，并且能够保证数据的完整性，缺点是数据读取过程中会产生冗余数据，如果只有少量数据，此影响可以忽略；数量大可能会影响到数据的处理效率。列存储在写入效率、保证数据完整性上都不如行存储，它的优势是在读取过程，不会产生冗余数据，这对数据完整性要求不高的大数据处理领域，比如互联网，犹为重要
14.mysql优化方向
14.1.针对sql优化，索引优化和查询语句优化 慢查询日志
14.2.频率控制优化，读缓存，写缓存，读缓存主要是使用redis，写缓存主要是使用消息队列
14.3数据规模过大，考虑分库分表
15.mysql死锁和慢查询
15.1.查看慢查询日志sql
select * from mysql.slow_log；（需要提前设置多久视为慢查询的时间）
15.2.查看执行时间最长的10条SQL:
mysqldumpslow -s a1 -n 10 mysql.slow_log；
15.3.查看锁表的情况
select * from sys.innodb_lock_waits；
找到进程id，直接kill
15.4.死锁检测
show engine innodb status;//分析日志
15.5.mysql添加索引或者加列是否会触发锁表操作
会 mysql5.6后支持线上无锁表加索引
1.ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
2.要不就是先创建副本，将表名改掉。
添加列：新建一张表，导入旧的数据，删除旧表，重命名新表的名字为旧表的名字
网友评论