Mysql服务的启动与停止指令：
cmd窗口输入：net start mysql；
启动net stop mysql; 停止
登录MySQL -uroot -p
密码MySQL -hip - uroot -p
密码MySQL --host=ip --user=root --password=连接目标的密码
退出：exitquit
SQL分类
DDL：（定义）用于定于数据库、表、列等。关键字：create drop alter等。
DML：（操作）用于对数据库中的表的数据进行增删改。关键字 insert delete update等
DQL：（查询）用来查询数据库表中的数据，关键字 select where等
DCL：（控制） 用来定义数据库的访问权限和安全级别以及用户的创建 关键字 GRANT REVOKE等
where和having的区别:
where用在分组查询前，having用在分组查询后；
where后面不能接聚合函数，having后面可以加聚合函数。
唯一约束的删除：（建表以后对列的操作）
切记，不能使用 alter atble 表名 modify 列名+unique 进行修改
可以使用： alter table 表名 drop index 列名 进行修改
唯一约束的添加：（建表以后）
alter table 表名 modify 列名+unique
删除主键：（建表完成后）
alter table 表名 modify id int；–无法删除主键，错误的删除方式
alter table 表名 drop primary key；–正确的删除方式
添加主键：
alter table 表名 modify id int primary key;
删除主键自增长：
alter table 表名 modify id int；
添加主键自增长：
alter table 表名 modify id int auto_incrument;
建立外键：（建表时）
–外键可以为null 但是不可以为不存在的外键值constraint 外键名称（随便起，不重复） foreign key (要关联到其他表的字段) references 被指向的表名（id）
删除外键：
alter table 表名 drop foreign key 外键名。
添加外键：（建表以后）
altet table 表名 add constraint 外键名称（随便起，不重复） foreign key (要关联到其他表的主键的那一列) references 被指向的表名（id）
级联操作：设置级联更新：–>先删除外键，然后在添加外键的时候设置级联更新以及级联删除。
altet table 表名 add constraint 外键名称（随便起，不重复） foreign key (要关联到其他表的主键的那一列) references 被指向的表名（id） on update cascade级联删除：altet table 表名 add constraint 外键名称（随便起，不重复） foreign key (要关联到其他表的主键的那一列) references 被指向的表名（id） on update cascade on delete cascade
自关联处理（员工表的id和员工表的管理员相关联）通过起别名建立虚拟表来实现。
查看Mysql的默认提交方式查询：
MySQL默认自动提交，orcal默认手动提交。select @@autocommit；–1代表自动提交 0代表手动提交修改默认提交方式：set @@autocommit=0 or 1 ；
MySQL的执行过程？–》半双工通信
- 大体上说的话MySql的架构分为两层，service层和存储引擎层，service层包括 连接器、查询缓存、分析器、优化器、执行器等。
- 连接器：在连接数据库的时候，首先遇到的就是数据库的连接器， 连接器的主要功能有：验证请求用户的账户和密码、检查用户的权限。建立连接的过程是比较复杂的，一般尽量减少建立连接的动作，也就是尽量使用长连接。（所以现场有很多连接池技术提供，阿里的、C3P0等等）。
- 查询缓存：主要就是为了提升查询的效率，把查询过的数据缓存在内存中，以 Key-Value 的方式（Key是具体的Sql语句，Value是结果的集合）。每次查询的时候，先在缓存中查找，命中了就直接返回，不然的话就继续往下走，到达分析器。（Mysql8.0以后删除了缓存，因为查询缓存失效的频率非常高，对于更新压力大的数据库，缓存的命中率会很低，现在比较推荐的做法是把缓存放在客户端。）
- 分析器： 分析器的主要作用是将客户端发过来的sql语句进行分析，包括预处理与解析过程，在这个阶段会解析sql语句的语义，对关键词和非关键词进行提取、解析， 如果分析到语法错误，会直接给客户端抛出异常， 这个阶段也会做一些校验:比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错。
优化器：经历了分析器，那么 MySQL 就知道你要干啥了，到底怎么做？优化器就去判断需要用哪个索引去查询、多条件查询的时候先执行哪个条件、或者决定 join 表的连接顺序等等， 选择一个最佳的执行方案。、 - 执行器：最后到了执行，知道了要干啥、怎么做，执行器开始执行 SQL 语句。包括获取锁、打开表、获取数据。但是开始前，会判断是否有查询的权限。如果没有返回权限错误。如果有权限，则打开表的，根据表的引擎定义，使用引擎提供的接口，最后把执行的结果集返回给客户端。
- 存储引擎层：用于负责数据的存储和提取。MySQL支持多种存储引擎， MyISAM，innodb。
MySQL中myisam与innodb的区别?
InnoDB支持事物，而MyISAM不支持事物
InnoDB支持行级锁，而MyISAM支持表级锁
InnoDB支持MVCC, 而MyISAM不支持
InnoDB支持外键，而MyISAM不支持
InnoDB不支持全文索引，而MyISAM支持。
CHAR和VARCHAR的差异？
char
- char表示定长字符串，长度是固定的；
- 如果插入数据的长度小于char的固定长度时，则用空格填充；
- 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
- 对于char来说，最多能存放的字符个数为255
varchar
- varchar表示可变长字符串，长度是可变的；
- 插入的数据是多长，就按照多长来存储；
- varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
- 对于varchar来说，最多能存放的字符个数为65532
数据库三大范式是什么
第一范式：每个列都不可以再拆分， 确保每一列的原子性， 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
第二范式：在满足第一范式的基础上， 就是要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键 ，也就是说，要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。 例如A（a(主键),b,c），不能存在b依赖c同时c依赖a,传递性
范式的优点：
范式化之后，只有很少的重复数据，再修改时候，只需要修改更少的数据，更新起来更加快；；
范式化的表更小，可以在内存中执行；
很少的冗余数据，在查询的时候需要更少的distinct或者group by语句。
缺点：
范式化的表，在查询的时候经常需要很多的关联， 会增加让查询的代价，也可能使一些索引策略无效（ 因为范式化将列存放在不同的表中，而这些列在一个表中本可以属于同一个索引）
越高的范式数据库冗余越小。但是有些时候一昧的追求范式减少冗余，反而会降低数据读写的效率，这个时候就要反范式，利用空间来换时间，就有了反范式。
反范式：增加冗余的数据减少表的关联查询。 例如A（a（主键），m，e，f）B（b（主键）,A_a,f,g）中A表的f实际上可以通过关联B表查询却存在A表。 如果关联查询很多情况，可以再考虑反范式或者非结构数据库MongoDB之类的。
优： 可以避免关联，因为所有的数据几乎都可以在一张表上显示； 可以设计有效的索引；
缺： 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失。
原子性的实现原理 undo log(版本链)
实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的 SQL 语句。InnoDB 实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB 会生成对应的undo log；保存数据的历史版本。如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。
undo log属于逻辑日志，它记录的是 SQL 执行的相关信息。当发生回滚时，InnoDB 会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。
持久性的实现原理 redo log
首先说一下为什么会出现数据丢失的情况。innoDB 作为 MySQL 的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘 IO，效率会很低。为此，InnoDB 提供了缓存（Buffer Pool），Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中，这一过程称为“刷脏”。
Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果 MySQL 宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果 MySQL 宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是顺序读写的方式，速度要比随机读写的速度快，所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求。
一条更新语句的执行流程为：
数据页到内存中——>修改数据——>更新数据页——>写入redolog（状态为prepare）——>写binlog–>提交事务（redolog状态修改为commit）；
数据库如何保证持久性？
主要是利用Innodb的redo log。重写日志， 正如之前说的，MySQL是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再写回到磁盘上。如果此时突然宕机，内存中的数据就会丢失。 怎么解决这个问题？ 简单啊，事务提交前直接把数据写入磁盘就行啊。
这么做有什么问题？
- 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
- 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。
于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。采用redo log的好处？****redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。
redo log与binlog的区别
redo log又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
binlog记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然后，若操作本身并没有导致数据库发生变化，那么该操作也会写入二进制日志。
第一:redo log是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层产生的，并且二进制日志不仅仅针对INNODB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。
第二：两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的redolog日志是物理日志，保存了数据库中的值。
第三：两种日志记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入。而InnoDB 存储引擎的重做日志在事务进行中不断地被写入（不断地写入redo log buffer，不断地刷新），这表现为日志并不是随事务提交的顺序进行写入的
（二进制日志仅在事务提交时记录，并且对于每一个事务，仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。而对于innodb存储引擎的重做日志，由于其记录是物理操作日志，因此每个事务对应多个日志条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，其在文件中记录的顺序并非是事务开始的顺序）
第四：binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redo log是循环使用。
第五：binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
如何保证redolog文件和binary文件的一致性
假设一：先写redo log再写binlog
想象一下，如果数据库系统在写完一个事务的redo log时发生crash，而此时这个事务的binlog还没有持久化。在数据库恢复后，主库会根据redo log中去完成此事务的重做，主库中就有这个事务的数据。但是，由于此事务并没有产生binlog，即使主库恢复后，关于此事务的数据修改也不会同步到从库上，这样就产生了主从不一致的错误。
假设二：先写binlog再写redo log
想象一下，如果数据库系统在写完一个事务的binlog时发生crash，而此时这个事务的redo log还没有持久化，或者说此事务的redo log还没记录完（至少没有记录commit log）。在数据库恢复后，从库会根据主库中记录的binlog去回放此事务的数据修改。但是，由于此事务并没有产生完整提交的redo log，主库在恢复后会回滚该事务，这样也会产生主从不一致的错误。
在MySQL内部，在事务提交时利用两阶段提交(内部XA的两阶段提交)很好地解决了上面提到的binlog和redo log的一致性问题：
第一阶段： InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID信息及redo和undo的内存日志。此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。
第二阶段：commit 阶段，这个阶段又分成两个步骤。第一步写binlog（先调用write()将binlog内存日志数据写入文件系统缓存，再调用fsync()将binlog文件系统缓存日志数据永久写入磁盘）；第二步完成事务的提交（commit），此时在redo log中记录此事务为提交状态。 还要注意的是，在这个过程中是以第二阶段中binlog的写入与否作为事务是否成功提交的标志。第一步写binlog完成 ，第二步redo log的commit未提交未完成，也算完成。
MySql的主从复制：
slave会从master读取binlog来进行数据同步。
MySQL的复制过程分为3步：
1、master将所有数据的改变记录到二进制文件（binary log）。这些过程叫做二进制日志事件，binary log events；
2、slave会在一定的时间间隔之内探测binlog是否发生改变。slave会通过IO/thread 线程将master的binary log events拷贝到它的中继日志（relay log）
3、同时，salve通过SQLthread 线程重行执行中继日志中的事件，将改变应用到自己的数据库中。Mysql的复制是异步的，且串行化的。
复制的基本原则：
每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个salve
#{}和${}的区别是什么？
动态 sql 是 mybatis 的主要特性之一，在 mapper 中定义的参数传到 xml 中之后，在查询之前 mybatis 会对其进行动态解析。完成动态拼接SQL的功能。mybatis 为我们提供了两种支持动态 sql 的语法：#{} 以及 KaTeX parse error: Expected 'EOF', got '#' at position 9: {}。 * #?{}是预编译处理，{}是字符串替换。
*
Mybatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值；
*
Mybatis在处理
时，就是把
{}时，就是把
时，就是把{}替换成变量的值。
*
使用#{}可以有效的防止SQL注入，提高系统安全性。
举例子
select * from ${tableName} where name = #{name}
在这个例子中，如果表名为
user; delete user; –
则动态解析之后 sql 如下：
select * from user; delete user; – where name = ?;
–之后的语句被注释掉，而原本查询用户的语句变成了查询所有用户信息+删除用户表的语句，会对数据库造成重大损伤，极大可能导致服务器宕机
事务的四大特征：
A（Atomic）：原子性，构成事务的所有操作，要么都执行完成，要么全部不执行，不可能出现部分成功部分失败的情况。
就会出现数据不一致的情形，AA账户减去50元，而B账户增加50元操作失败。系统将无故丢失50元~
C（Consistency）：一致性，在事务执行前后，数据库的一致性约束没有被破坏。数据处于合法的状态。比如：张三向李四转100元，转账前和转账后的数据是正确状态这叫一致性，如果出现张三转出100元，李四账户没有增加100元这就出现了数
据错误，就没有达到一致性。
I（Isolation）：隔离性，数据库中的事务一般都是并发的，隔离性是指并发的两个事务的执行互不干扰，一个事务不能看到其他事务运行过程的中间状态。
如果无法保证隔离性会怎么样？
假设A账户有200元，B账户0元。A账户往B账户转账两次，金额为50元，分别在两个事务中执行。如果无法保证隔离性，A可能就会出现扣款两次的情形，而B只加款一次，凭空消失了50元，依然出现了数据不一致的情形！
D（Durability）：持久性，事务完成之后，该事务对数据的更改会被持久化到数据库，且不会被回滚。
如果无法保证持久性会怎么样？
在MySQL中，为了解决CPU和磁盘速度不一致问题，MySQL是将磁盘上的数据加载到内存，对内存进行操作，然后再回写磁盘。好，假设此时宕机了，在内存中修改的数据全部丢失了，持久性就无法保证。
事务的隔离级别：
- 未提交读，事务中发生了修改，即使没有提交，其他事务也是可见的，比如对于一个数A原来50修改为100，但是我还没有提交修改，另一个事务看到这个修改，而这个时候原事务发生了回滚，这时候A还是50，但是另一个事务看到的A是100.可能会导致脏读、幻读或不可重复读
- 提交读，对于一个事务从开始直到提交之前，所做的任何修改是其他事务不可见的，举例就是对于一个数A原来是50，然后提交修改成100，这个时候另一个事务在A提交修改之前，读取的A是50，刚读取完，A就被修改成100，这个时候另一个事务再进行读取发现A就突然变成100了；可以阻止脏读，但是幻读或不可重复读仍有可能发生
- 重复读，就是对一个记录读取多次的记录是相同的，比如对于一个数A读取的话一直是A，前后两次读取的A是一致的；可以阻止脏读和不可重复读，但幻读仍有可能发生
- 可串行化读，在并发情况下，和串行化的读取的结果是一致的，没有什么不同，比如不会发生脏读和幻读；该级别可以防止脏读、不可重复读以及幻读
隔离界别：
1、read uncommitted：读未提交
*产生的问题：脏度、不可重复读、幻读
2、read committed：读已提交
*产生的问题：不可重复度、幻读
3、repeatable read：可重复度（MySQL默认）
*产生的问题：幻读
4、serializable：串行话
*可以解决所有的问题
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zb4pteVP-1657959112591)(C:\Users\Hao\Desktop\面试\img\image-20220714174433862.png)]
Innodb为什么要用自增id作为主键？
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。
MySQL中为什么要有事务回滚机制？回滚日志作用：
- 能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息
- 在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。
在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。 当事务已经被提交之后，就无法再次回滚了。
Mysql索引：
建立索引的语句：
Crreate [unique] index [索引名字] on 表名（列名）
alter table 表名 add [unique] index 索引名字(列名)
官方定义：MySQL索引是帮助MySQL高效获取数据的一种数据结构，而且是排好序的数据结构，索引存储在磁盘文件里面。索引能够极大地减少存储引擎需要扫描的数据量，从而提高数据的检索速度。
MySQL索引主要有两种结构：B+tree和hash（无特特殊说明）
索引的分类：
普通索引：大多数情况下都是用的普通索引。
唯一索引：表示唯一的不允许重复的索引（手机号、身份证），Primary Key是拥有自动定义的Unique约束。允许有空值。
全文索引：在检索全文索引的时候效果比较好，比如搜一篇文章。
空间索引：对地空间位置的数据类型的字段建立的索引。
给表添加索引：
Alter index 表名 add index 索引名字（要建立索引的字段） using btree–>普通索引
Alter index 表名 add unique index 索引名字（要建立索引的字段）using btree–>唯一索引
Mysql索引原理B树和B+树的区别与hash方式
MYySQL索引的底层数据结构是B+树
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
B-Tree结构图中每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。
B+tree
B+树的搜索与B树也基本相同，区别是B+树只有达到叶子结点才命中，也就是说只有在叶子结点才才有存储数据。非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储数据的数据层，叶子结点之间通过双端链表连接，且每一个节点是一个排序链表。 更适合文件索引系统 。实现顺序查找和范围查找的时候，B+tree只用找到一个节点，在叶子结点往前或者往后继续遍历就可以实现范围内的查找。并且文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，一般情况下3到4层的b+tree足以支撑千万级别的数据量，B+tree的叶子节点不存数据，因此一个节点（数据页，默认是16kb）存储的索引键比Btree多，这样可以减小树的高度，进而减少磁盘io的次数。同时，每一个节点存放的索引值越多，在内存中的比较次数就越多，但与磁盘的io相比，对资源的消耗还是要小很多。因此B+树相比B树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理
在Mysql中Btree索引
一般情况下3到4层的b+tree足以支撑千万级别的数据量
B+tree的索引结构（矮胖）：与Btree相比，所有的数据都存在叶子节点，并且是排好序的（当数据是String和电话、身份证等信息时，使用Ascii码进行排序）一个双向链表。实现顺序查找和范围查找的时候，B+tree只用找到一个节点，在叶子结点往前或者往后继续遍历就可以实现范围内的查找。B+tree的叶子节点不存数据，因此一个节点（数据页，默认是16kb）存储的索引键比Btree多，这样可以减小树的高度，进而减少磁盘io的次数。同时，每一个节点存放的索引值越多，在内存中的比较次数就越多，但与磁盘的io相比，对资源的消耗还是要小很多
InnoDB为什么不使用hash表作为mysql的索引结构？
memory的存储引擎就是使用hash表结构，innoDB支持自适应hash，由mysql自己决定。用户不能进行干预
1、需要比较好的hash算法，避免hash冲突，导致数据散裂不均匀。
2、由于hash是无序的，范围查找的时候需要挨个遍历，效率比较低。
回表
使用辅助索引进行查找时，由于辅助索引叶子节点存的并不是所有的数据，而是主键的id值，主键索引的叶子节点存储的是索引键以及对应的所有信息，因此这时需要再将获取到的主键id值拿到主键索引里面去查找其对应的所需要的的字段信息。这个过程称为回表。
覆盖索引
普通索引叶子结点的全部信息包含了要查询的字段，比如，主键ID，此时直接从索引中获取，不需要回表操作。如果包含了其他的字段且不包含在普通索引的数据信息中，则会进行回表操作。
最左匹配
创建索引的时候可以选择多了列来共同组成索引，此时叫做组合索引或者联合索引。要遵循最左匹配原则。查询的条件的顺序和索引字段的顺序保持一致。
索引下推：
如：select * from table where name=‘zhangsan’ and age=12;
在没有索引下推之前：
先根据name从存储引擎中拉取数据到server层，然后在server层中对age进行数据过滤。
有了索引下推以后：
根据name和age两个条件来做数据筛选，将筛选之后的结果返回给server层。
将server层进行的存储过滤下推倒了存储引擎层。
MyISAM和InnoDB底层索引有什么区别？
MyISAM的底层索引:
也是基于B+tree实现：辅助索引和主键索引叶子节点存储的都是索引键值以及对应的数据的地址，它的索引和数据是分开存放的。当查询的时候，首先查询到要获取的索引键以及其对应数据存放的地址，再利用获取到的地址进行数据字段的查询。这样可以避免回表。
InnoDB底层索引实现：
InnoDB的主键索引和辅助索引叶子结点存储的数据是有区别的，利用辅助索引进行查找的时候，需要回表。使用辅助索引进行查找时，由于辅助索引叶子节点存的并不是所有的数据，而是主键的id值，主键索引的叶子节点存储的是索引键以及对应的所有信息，因此这时需要再将获取到的主键id值拿到主键索引里面去查找其对应的所需要的的字段信息。这个过程称为回表。
inodb中的索引是聚簇索引吗？
聚集索引、聚簇索引：主键和数据存在一起的，InnoDB的主键索引就是聚簇索引。innoDB存储引擎在进行数据插入的时候，数据必须跟某个索引列存储在一起，这个索引列可以是主键，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的row来进行存储。数据必定是跟某一个索引绑定在一起的，绑定数据的索引叫做聚簇索引。那么其他索引的叶子节点绑定的数据是主键索引的id值。所以inodb中既有聚簇索引又有非聚簇索引。
非聚集索引、非聚簇索引：主键和数据没有在一起的，如MyISAM的索引。
MySQL之Innodb引擎的4大特性
1、插入缓冲 （Insert Buffer/Change Buffer）
插入缓存之前版本叫insert buffer，现版本 change buffer，主要提升插入性能，change buffer是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、都有效。有什么用呢？
对于非聚集索引来说，比如存在用户购买金额这样一个字段，索引是普通索引，每个用户的购买的金额不相同的概率比较大，这样导致可能出现购买记录数据里的排序可能是1000，3，499，35…，这种不连续的数据，一会插入这个数据页，一会插入那个数据页，这样造成的IO是很耗时的，所以出现了Insert Buffer。
Insert Buffer是怎么做的呢？mysql对于非聚集索引的插入，先去判断要插入的索引页是否已经在内存中了，如果不在，暂时不着急先把索引页加载到内存中，而是把它放到了一个Insert Buffer对象中，临时先放在这，然后等待情况，等待很多和现在情况一样的非聚集索引，再和要插入的非聚集索引页合并，比如说现在Insert Buffer中有1，99，2，100，合并之前可能要4次插入，合并之后1，2可能是一个页的，99，100可能是一个页的，这样就减少到了2次插入。这样就提升了效率和插入性能，减少了随机IO带来性能损耗
2.双写机制（Double Write）
Doublewrite缓存是位于系统表空间的存储区域，用来缓存InnoDB的数据页innodb buffer pool的数据，所以当操作系统或者数据库进程在数据页写磁盘的过程中崩溃，Innodb可以在doublewrite缓存中找到数据页的备份而用来执行crash恢复。因为此写入操作会以连续的方式进行写入。数据页写入到doublewrite缓存的动作所需要的IO消耗要小于写入到磁盘的消耗，
doublewrite由两部分组成组成：
内存中的doublewrite buffer,大小2M。
物理磁盘上共享表空间中连续的128个页，即2个区（extend），大小同样为2M。
它的执行过程是：对缓冲池的脏页进行刷新时，不是直接写磁盘，而是会通过memcpy()函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite 再分两次，每次1M顺序地写入共享表空间的物理磁盘上，在这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer 中的页写入各个表空间文件中，此时的写入则是随机的。如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，innodb可以从共享表空间中的doublewrite中找到该页的一个副本，进行恢复。
3.自适应哈希索引（Adaptive Hash Index，AHI）
哈希（hash）是一种非常快的等值查找方法，在一般情况下这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般3-4层，故需要3-4次的查询。
Innodb存储引擎会监控对表上辅助索引的查找，如果发现某辅助索引被频繁访问，辅助索引成为热数据，建立哈希索引可以带来速度的提升
经常访问的辅助索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，生成索引的索引。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
4.预读 （Read Ahead）
预读（read-ahead)操作是一种IO操作，预料这些页会马上被读取到，用于异步将磁盘的页读取到buffer pool中，。InnoDB使用两种预读算法：
Linear read-ahead：线性预读技术预测在buffer pool中被访问到的数据它临近的页也会很快被访问到。能够通过调整被连续访问的页的数量来控制InnoDB的预读操作，使用参数 innodb_read_ahead_threshold进行控制。
如果在一个区块顺序读取的页数大于或者等于 innodb_read_ahead_threshold 这个参数，InnoDB启动预读操作来读取下一个区块。
Random read-ahead: 随机预读通过buffer pool中存储的页来预测哪些页可能很快会被访问，而不考虑这些页的读取顺序。如果发现buffer pool中存在一个区段的13个连续的页，InnoDB会异步发起预读请求这个区段剩余的页。通过设置 innodb_random_read_ahead 为 ON开启随机预读特性。
MVCC多版本并发控制技术提高并发的读写效率
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，
基本原理：是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。是一种快照读。
当前读：
select lock in share mode (共享锁), select for update 这些操作都是一种当前读 它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
快照读：
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读 。之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销。
MVCC的实现是通过三个组件来实现的
隐藏字段：
每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID 等字段
DB_TRX_ID
6 byte，最近修改(修改/插入)事务 ID：记录最后一次修改该记录的事务 ID
DB_ROLL_PTR
7 byte，回滚指针，指向这条记录的上一个版本地址信息（存储于 rollback segment 里）
DB_ROW_ID
6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引
undolog（回滚日志）：
当不同事务对同一条记录进行修改的时候，当前记录的各个版本都会保存到undolog中，形成一个链表，链表的首部就是最新的历史记录，链表尾部为就是最早的历史记录。构成undolog日志
readView
Read View 就是事务进行快照读操作的时候生产的读视图 (Read View)，生成当前数据库系统的一个快照，并通过可见性算法来判断某一条记录对于当前事务是否可见来保证事物之间的隔离性。
具体流程是每次进行快照读的时生成的ReadView，维护了三个字段，当前数据库中活跃的事务ID，最小ID号和下一个尚未分配的ID。将这三个字段与某一条记录的最后一次修改事务ID相比较，并通过可见性算法来判断某一条记录对于当前事物是否可见。比如说一条记录最有一次修改的事务ID小于最小活跃ID号，则当前事务对着条记录就是可见的。在RR隔离级别下，一个事务中生成的readView会被重复利用以解决不可重复读的问题，而在RC的隔离级别下，每一次读取都会成生一个新的readView，所以不能保证可重复读的问题。注意（解决幻读问题需要进行加锁操作），比如当两个事物同时开启，一个事务进行一次select，记一次快照读，另一个事务增加了一条数据并且提交，在另一个事务进行更新操作就会发现更新一条没有的数据。这就是幻读，出现幻读的根本原因就是在一个事务中即出现了当前读又出现了快照读的情况就会出现幻读。在这种情况下解决幻读的问题需要通过加锁的方式来保证事务的一致性。
mysql中有哪些锁
共享锁，也叫读锁，当一个事务为数据加上读锁之后，其他的事务只能对该数据加读锁，而不能加写锁，直到所有的读锁释放之后其他的事务才能对其加上写锁。这样能够支持并发读取数据，读取数据不支持修改。
排它锁：也叫写锁，当一个事务为数据加上写锁之后，其他事务不能为数据加上任何的锁，直到该锁释放之后，其他事务才能对数据加锁。排它锁的目的是为了在数据修改的时候，不允许其他人同时修改，也不允许其他人读取。
表锁：标所是指上锁住得是整个表，当下一个事务访问该表时，必须等待事务释放锁之后才能进行访问。
行锁：行锁指上锁的时候锁住的是某一行，其他事务访问同一张表的时候，只有被锁住的记录不能访问，其他记录可正常访问。
记录锁：
记录锁也属于行锁的一种: 记录锁是对索引记录的锁定，加了记录锁之后可以避免数据在查询的时候本修改的重复度问题。
页锁:页锁是处于行锁和表锁中间的一种锁，表级锁速度快但是冲突多，行锁冲突少，但是速度慢，所以取了折中的方案，一次锁定相邻的一组记录。使得开销在行锁和表锁之间。
间隙锁;属于行锁的一种，间隙锁是在事务加锁之后，锁住表记录的某一一个区间，当表的相邻ID之间出现间隙就会形成一个区间。防止其他事务在间隙中插入数据，
临建锁： 是一种记录锁和间隙锁的组合锁。
InnoDB引擎
是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。
主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
内部做了很多优化，包括从磁盘读取数据时采用的，插入缓存提高插入数据的效率，双写机制保证数据的安全性。可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
MyISAM引擎
设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。
提供了大量的特性，包括压缩表、空间数据索引等。
不支持事务。
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。
MySQL常见的存储引擎InnoDB、MyISAM的区别？适用场景分别是？
1）事务：MyISAM不支持，InnoDB支持
2）锁级别： MyISAM 表级锁，InnoDB 行级锁及外键约束
3）MyISAM存储表的总行数；InnoDB不存储总行数；
4）MyISAM采用非聚集索引，B+树叶子存储指向数据文件的指针。InnoDB主键索引采用聚集索引，B+树叶子存储数据
适用场景： MyISAM适合： 插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择， 没有事务。 InnoDB适合： 可靠性要求比较高，或者要求事务； 表更新和查询都相当的频繁， 大量的INSERT或UPDATE
数据库为什么要进行分库和分表呢？
分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。
分库指将一个库中的表拆分到不同的库中；分表是将一个表中的数据拆分成不同的表。
2.1 为什么需要分库呢？
如果业务量剧增，数据库可能会出现性能瓶颈，这时候我们就需要考虑拆分数据库。
从这几方面来看
1、磁盘存储方面，业务量剧增，MySQL单机磁盘容量会撑爆，拆成多个数据库，磁盘使用率大大降低。
2、并发连接支撑、我们知道数据库连接是有限的。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！当前非常火的微服务架构出现，就是为了应对高并发。它把订单、用户、商品等不同模块，拆分成多个应用，并且把单个数据库也拆分成多个不同功能模块的数据库（订单库、用户库、商品库），以分担读写压力。
2.2 为什么需要分表？
数据量太大的话，SQL的查询就会变慢。如果一个查询SQL没命中索引，千百万数据量级别的表可能会拖垮整个数据库，即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢啦。数据库优化中有一个比较常用的手段就是把数据表进行拆分，
关于拆分数据表你了解哪些？怎么进行分库分表
拆分其实又分垂直拆分和水平拆分
案例： 简单购物系统暂设涉及如下表：
1.产品表（数据量10w，稳定）
2.订单表（数据量200w，且有增长趋势）
3.用户表 （数据量100w，且有增长趋势）
以 MySQL 为例讲述下水平拆分和垂直拆分，MySQL能容忍的数量级在百万静态数据可以到千万
垂直拆分
解决问题：表与表之间的io竞争
不解决问题：单表中数据量增长出现的压力
水平拆分
解决问题：单表中数据量增长出现的压力
不解决问题：表与表之间的io争夺
方案：用户表 通过性别拆分为男用户表和女用户表，订单表 通过已完成和完成中拆分为已完成订单和未完成订单，产品表 未完成订单放一个server上，已完成订单表盒男用户表放一个server上，女用户表放一个server上(女的爱购物 哈哈)
假如你所在的公司选择MySQL数据库作数据存储，一天五万条以上的增量，预计运维三年，你有哪些优化手段？
- 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
- 选择合适的表字段数据类型和存储引擎，适当的添加索引。
- MySQL库主从读写分离。
- 找规律分表，减少单表中的数据量提高查询速度。
- 添加缓存机制，比如Memcached，Apc等。
- 不经常改动的页面，生成静态页面。
- 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE
数据库表锁和行锁吧表锁
MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：表共享读锁和表独占写锁。
读锁会阻塞写，写锁会阻塞读和写
- 对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
- 对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
MyISAM不适合做写为主表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
行锁
在MySQL的InnoDB引擎支持行锁，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。
数据库高并发是我们经常会遇到的，你有什么好的解决方案吗？
- 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
- 增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
- 主从读写分离，让主服务器负责写，从服务器负责读。
- 将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。
- 使用分布式架构，分散计算压力。
既然Hash比B+树更快，为什么MySQL用B+树来存储索引呢？
MySQL中存储索引用到的数据结构是B+树，B+树的查询时间跟树的高度有关，是log(n)，如果用hash存储，那么查询时间是O(1)。
采用Hash来存储确实要更快，但是采用B+树来存储索引的原因主要有以下两点：
- 一、从内存角度上说，数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次性装入内存，B+树的设计可以允许数据分批加载。
- 二、从业务场景上说，如果只选择一个数据那确实是hash更快，但是数据库中经常会选中多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。
数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
- 增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
- 主从读写分离，让主服务器负责写，从服务器负责读。
- 将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。
- 使用分布式架构，分散计算压力。
既然Hash比B+树更快，为什么MySQL用B+树来存储索引呢？
MySQL中存储索引用到的数据结构是B+树，B+树的查询时间跟树的高度有关，是log(n)，如果用hash存储，那么查询时间是O(1)。
采用Hash来存储确实要更快，但是采用B+树来存储索引的原因主要有以下两点：
- 一、从内存角度上说，数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次性装入内存，B+树的设计可以允许数据分批加载。
- 二、从业务场景上说，如果只选择一个数据那确实是hash更快，但是数据库中经常会选中多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。
|