1、mysql in和exists区别

• in是先进行子查询，然后将内表(子查询)与外表(主查询)进行hash关联(笛卡尔积)，然后进行条件比较，即筛选in前后字段相等的数据保留，不相等的删除。
• exists是先进行外层表loop循环，然后将每一条数据当做条件进行子查询，如果子查询返回true则保存该数据，否则删除。
• 可以理解为用in时先子表全表查询，然后将每一条数据作为条件以及索引拿去进行主表查询，所以如果主表大于子表，那么用in就会效率高，
• 而用exists会先进行主表全表查询，然后将每一条数据作为条件以及索引拿去进行子表查询，所以如果主表小于子表，那么用exists会效率高。

not in主表和子表都不会用到索引且子表有null值时,查询结果有问题，而not exists子表始终会用到索引,而且不会存在子表有null值时的问题，所以，用not exists替代not in

2、MySQL查询语句in子查询的优化

1、仍使用in子查询，多查询一次（内部每次与外部比较时，都需要遍历一次表操作，可以采用另外一个方法，在嵌套一层子查询，避免多次遍历操作）
2、使用LEFT JOIN

3、幻读

innodb可重复读可以解决幻读，之所以有另一个图是因为一个是sql标准（可重复读不可以解决幻读），另一个是存储引擎的具体实现（可重复读可以解决幻读）

mvcc

4、mysql的事务隔离级别的底层实现原理

底层实现原理主要在读，写都会加排他锁
1、不会加锁
2、MVCC 每次select
3、MVCC 第一次select
4、加共享锁，读写互斥

1、 READ UNCOMMITTED（你读了别人正在处理的数据）

读不会加任何锁。而写会加排他锁，并到事务结束之后释放。

2、READ COMMITTED（两次读的都是真的（不脏读） 可是却存在不可重复，你读的期间别人插进来对数据操作了）

读取数据不加锁而是使用了MVCC机制，写数据时，使用排它锁。

该级别下还是遗留了不可重复读和幻读问题： MVCC版本的生成时机: 是每次select时。这就意味着，如果我们在事务A中执行多次的select，在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读，即：重复读时，会出现数据不一致问题，后面我们会讲解超支现象，就是这种引起的。

读时通过mvcc，访问的是创建版本最大&&删除版本为空的记录

3、REPEATABLE READ（事务是多次读取，得到的相同的值）
READ COMMITTED级别不同的是MVCC版本的生成时机，即：一次事务中只在第一次select时生成版本，后续的查询都是在这个版本上进行，从而实现了可重复读。

读时通过mvcc，访问的是创建版本小于等于当前版本&&（删除版本大于当前版本 || 删除版本为空）的记录

4、SERIALISABLE（你读的时候别人看不到，隔离开，单独执行）
该级别下，会自动将所有普通select转化为select ... lock in share mode执行，即针对同一数据的所有读写都变成互斥的了，可靠性大大提高，并发性大大降低。

5、MySQL的binlog日志(https://www.cnblogs.com/martinzhang/p/3454358.html)

6、 乐观锁重试

并发修改同一记录时，避免更新丢失，需要加锁。要么在应用层加锁，要么在缓存加锁，要么在数据库层使用乐观锁，使用version作为更新依据。说明：如果每次访问冲突概率小于20%，推荐使用乐观锁，否则使用悲观锁。乐观锁的重试次数不得小于3次。

7、悲观锁和乐观锁使用在哪些场景？

悲观锁和乐观锁是数据库用来保证数据并发安全防止更新丢失的两种方法，楼主列举的例子在select ... for update前加个事务就可以防止更新丢失。悲观锁和乐观锁大部分场景下差异不大，一些独特场景下有一些差别，一般我们可以从如下几个方面来判断：

1、响应速度：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁
2、冲突频率：如果冲突频率非常高，建议采用悲观锁，保证成功率，如果冲突频率大，乐观锁会需要多次重试才能成功，代价比较大
3、重试代价： 如果重试代价大，建议采用悲观锁

7、 MySQL主从复制的两种方式

1、通过binary log配置主从
2、通过GTID配置主从

问到一个表有3000万记录，假如有一列占8位字节的字段，根据这一列建索引的话索引树的高度是多少？

表的记录数是N，每一个BTREE节点平均有B个索引KEY，那么B+TREE索引树的高度就是logNB(等价于logN/logB)

现在我们假设表3000W条记录（因为2^{25=33554432），如果每个节点保存64个索引KEY,那么索引的高度就是（log2}25)/log64≈ 25/6 ≈ 4.17

节点保存的KEY的数量为pagesize/(keysize+pointsize)

假设平均指针大小是8个字节，那么索引树的每个节点可以存储16k/((8+8)*8)≈128。那么：一个拥有3000w数据，且主键是BIGINT类型的表的主键索引树的高度就是(log2^25)/log128 ≈ 25/7 ≈ 3.57

由上面的计算可知：一个千万量级，且存储引擎是MyISAM或者InnoDB的表，其索引树的高度在3~5之间。

8、 为什么要给表加上主键？

1. 一个没加主键的表,它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐.
2. 一个加了主键的表,并不能被称之为「表」。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,并且是「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错,再说一遍,整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键,一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

9、为何要使用自增int作为主键？

1、int 相比varchar、char、text使用更少的存储空间，而且数据类型简单，可以节约CPU的开销，更便于表结构的维护
2、默认都会在主键上建立主键索引，使用整形作为主键可以将更多的索引载入内存，提高查询性能
3、对于InnoDB存储引擎而言，每个二级索引都会使用主键作为索引值的后缀，使用自增主键可以减少索引的长度（大小），方便更多的索引数据载入内存
4、可以使索引数据更加紧凑，在数据插入、删除、更新时可以做到索引数据尽可能少的移动、分裂页，减少碎片的产生（可以通过optimize table 来重建表），减少维护开销
5、在数据插入时，可以保证逻辑相邻的元素物理也相邻，便于范围查找

当然，使用自增int作为主键也不是百利无一害，在高并发的情况下也可能会造成锁的争用问题。

10、 InnoDB的RR级别如何避免幻读？

避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。

在RR的隔离级别下，Innodb使用MVVC和next-key locks解决幻读，MVVC解决的是普通读（快照读）的幻读，next-key locks解决的是当前读情况下的幻读。

11、 哪些情况不能用索引？

1、like “%xxx”
2、not in ，NOT IN可以NOT EXISTS代替
3、对列进行函数运算的情况（如 where md5(password) = “xxxx”）
4、OR， WHERE index=1 OR A=10
5、适当考虑建立联合索引，等于建立（a）（a,b）(a,c) (a,b,c)

12、 执行select和update会如何加锁

因为select 是读取操作，所以会加read lock（共享锁）上面的实验中，update操作要等待select 的锁释放，才能加 write lock（排他锁）。 所以会卡住直到select 结束。而同时的select 操作则不需要等待，因为文档内说明了read lock的上锁条件为只要没有write lock 就可以。

13、 delete和truncate区别

truncate和 delete只删除数据不删除表的结构(定义) ; drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.

1、truncate在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令delete将被撤销，而truncate则不会被撤销。
2、truncate不能进行回滚操作。
3、truncate不触发任何delete触发器。
5、当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。
6、不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除。

7. delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

14、 如果索引值为null，走不走索引

答案：走
MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。
可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。当可空列被索引的时候，

每条记录都需要一个额外的字节，还可能导致 MyISAM 中固定大小的索引(例如一个整数列上的索引)变成可变大小的索引。

即使要在表中储存「没有值」的字段，还是有可能不使用 NULL 的，考虑使用 0、特殊值或空字符串来代替它。

把 NULL 列改为 NOT NULL 带来的性能提升很小，所以除非确定它引入了问题，否则就不要把它当作优先的优化措施。
然后，如果计划对列进行索引，就要尽量避免把它设置为可空,虽然在mysql里 Null值的列也是走索引的。对MySQL来说，null是一个特殊的值。

15、 单独两个索引 ，使用or 查询会失效吗？

如下图，单独两个索引会失效，而联合索引不会

单独两个索引

image.png

联合索引

image.png

16、 为什么MySQL的索引结构，采用了B+树，没有使用跳跃表呢？

首先，跳跃表不适用于磁盘读取的场景，B+树的页天生就和磁盘块对应
其二，跳跃表的查找效率不如B+树效率高，也不如B+树稳定。

17、 mysql 主从复制延迟及解决

原因：主库并发量大，而从库复制是单线程，从库过多，主从系统配置不当，cpu，内存等，慢sql过大多，大的事物，网络延迟，跨公网的主从复制很容易导致主从复制延迟

解决方法

1. 适当数量的从库，3-5个，从库配置更好的硬件，网络配置等
2. 将大事物拆分成多个小事物进行提交，表加主键，否在会全表扫描
3. mysql 5.7.19 + 版本支持并行复制
4. 如果对实时性要求高的系统，从服务器只当备份使用，数据从缓存返回，降低主服务器压力。

主机与从机之间的物理延迟是无法避免的，既然无法避免就可以考虑尝试通过缓存等方式，降低新修改数据被立即读取的概率。

18、mysql中sql注入是什么，如何避免？

sql注入分为平台层注入和代码层注入，前者由不安全的数据库配置或数据库平台的漏洞所致，后者主要由于程序员对输入未进行细致地过滤。

如何避免

1. PreparedStatement
2. 使用正则表达式过滤传入的参数
3. JSP中调用该函数检查是否包函非法字符
4. JSP页面判断代码
5. 字符串过滤

19、 MySQL优化

面试的时候可以从4个维度去讲
1、表结构和索引
2、SQL语句优化
3、MySQL参数优化（对连接数的管理，索引缓存）
4、硬件及系统配置（操作系统配置，内存、固态硬盘）

image.png

在我们做mysql性能分析的时候，最常用的有三种方式：

（1）慢查询 （分析出现出问题的sql）
（2）Explain （显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句）
（3）Profile（查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.）

19、mysql数据库死锁的产生原因及解决办法

1、死锁的第一种情况

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样
用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：

对于数据库的多表操作时，尽量按照相同的顺序进 行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

死锁的第二种情况

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。

解决方法：
1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。
3、使用悲观锁进行控制。

死锁的第三种情况

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法：
SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

20、幻读和不可重复读的区别

• 不可重复读:同样的条件，你读取过的数据，再次读取出来发现值不一样了。
• 幻读:同样的条件，第1次和第2次读出来的记录数不一样

不可重复读重点在于只锁住update和delete，而幻读的重点还在于锁住insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，有没有锁住insert。

21、QPS与并发数的关系

一般是10万到百万的QPS，他才能被称作是高并发。

并发用户数和QPS两个概念没有直接关系，但是如果要说QPS时，一定需要指明是多少并发用户数下的QPS，否则豪无意义，因为单用户数的40QPS和20并发用户数下的40QPS是两个不同的概念
QPS（TPS）= 并发数/平均响应时间

22、一条sql执行过长的时间，你如何优化，从哪些方面？

1、查看sql是否涉及多表的联表或者子查询，如果有，看是否能进行业务拆分，相关字段冗余或者合并成临时表（业务和算法的优化）
2、涉及链表的查询，是否能进行分表查询，单表查询之后的结果进行字段整合
3、如果以上两种都不能操作，非要链表查询，那么考虑对相对应的查询条件做索引。加快查询速度
4、针对数量大的表进行历史表分离（如交易流水表）
5、数据库主从分离，读写分离，降低读写针对同一表同时的压力，至于主从同步，mysql有自带的binlog实现 主从同步
6、explain分析sql语句，查看执行计划，分析索引是否用上，分析扫描行数等等
7、查看mysql执行日志，看看是否有其他方面的问题

23、水平分库后查询如何排序

跨分片的排序分页
一般来讲，分页时需要按照指定字段进行排序。

• 当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，
• 而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。

24、主备流程和两阶段提交

image.png

25、b+树一般多高，能存储多少数据？

一般3到4层 能存储到2千万到25亿数据

Innodb 的所有数据文件（后缀为 ibd 的文件），他的大小始终都是 16384（16k）的整数倍。

我们假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170

那么可以算出一棵高度为 2 的 B+ 树，能存放 1170*16=18720 （两万）条这样的数据记录。

根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放： 1170117016=21902400 （两千万）条这样的记录。

根据同样的原理我们可以算出一个高度为 4 的 B+ 树可以存放： 117011701170*16=25 625 808 000 （25亿）条这样的记录。

26、MySQL常用函数

聚合函数

• count（）：求数据表的行数
• max（）：求某列的最大数值
• min（）：求某列的最小值
• sum（）：对数据表的某列进行求和操作
• avg()：对数据表的某列进行求平均值操作

数学函数

• abs(X):返回X的绝对值
• mod(N,M)或%:返回N被M除的余数
• floor(X):返回不大于X的最大整数值
• round(X) :返回参数X的四舍五入的一个整数

字符串函数

• concat(str1,str2,...):返回来自于参数连结的字符串
• length(str):返回字符串str的长度
• substring(str,pos):从字符串str的起始位置pos返回一个子串
• replace(str,from_str,to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替

日期和时间函数

• dayofweek(date):返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)
• dayofmonth(date):返回date的月份中的日期，在1到31范围内
• month(date):返回date的月份，范围1到12
• now():以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间

条件判断函数

• CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...
  如：SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
• IF(expr1,expr2,expr3)：如果 expr1 是TRUE，则 IF()的返回值为expr2; 否则返回值则为 expr3

系统信息函数

• version()：函数返回数据库的版本号
• connection_ID()：函数返回服务器的连接数
• database()和schema()返回当前数据库名。

加密函数

• password(str)：函数可以对字符串str进行加密
• MD5(str)加密函数

27、一条SQL语句执行得很慢的原因有哪些？

image.png

28、判断列是否要添加索引的标准

有时候由数据库的查询优化器自动判断是否使用索引；

• 1、较频繁的作为查询条件的字段应该创建索引，且唯一性较好的
• 2、show index from table_name ## 查看该表的索引信息

Cardinality 值非常重要，优化器会根据这个值来判断是否使用这个索引，但是这个值并不是实时更新的，即并非索引的更新都会更新该值，因为代价太大，只是一个大概值

Cardinality ：非常关键的值，标识索引中唯一值的数目的估计值，Cardinality/表的记录数应尽可能的接近1，如果非常小,那用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对于字段添加B+树索引是非常有必要的

29、哪些情况会导致索引失效？

1、左模糊
2、不符合最左原则
3、or
4、is null
5、!=或<>
6、in 和 not in
7、表达式操作：where num/2=100
8、函数：where substring(name,1,3)='abc'

30、sql中where、having、group by、order by执行和书写顺序？

当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

1. 执行where xx对全表数据做筛选，返回第1个结果集。
2. 针对第1个结果集使用group by分组，返回第2个结果集。
3. 针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
4. 针对第3个结集执行having xx进行筛选，返回第4个结果集。
5. 针对第4个结果集排序。

from --> where --[result 1]--> group by --[result 2]--> select (x N) --[result 3]--> having --[result 4]--> order by --> OUTPUT