mysql面试题

一、MySQL高频面试20问  https://blog.csdn.net/weixin_45682261/article/details/125130807
1.1 MySQL的三大范式？
范式是我们创建表的时候要遵循的规范。满足最低要求的范式为第一范式1NF，在第一范式的基础上满足更多规范要求的称为第二范式2NF，第三范式3NF以此类推。
1
1NF： 第一范式即表的列具有原子性，不可以在分割，即列的信息不能再分解不能是集合和数组。只要是关系型数据库就自动满足1NF。如果一个表中的字段有多个值时必须拆分成不同属性。通俗上讲就是一个字段只能存储一个信息。
2NF： 第二范式是在第一范式的基础上建立的，所以满足第二范式的前提时满足第一范式。第二范式要求数据库表中每一个实例或者行必须可以唯一区别。通常需要我们设计一个主键来实现。通俗理解就是表中的一个字段只能依赖另一个字段。
3NF： 第三范式必须满足第二范式。第三范式要求一个数据库表中不包含已在其他表中包含的非主键字段。表中的非主键列要完全依赖主键，不能出现部分属性依赖其他属性。当出现传递依赖的时候要将非依赖于主键的列专门创建一张表进行管理。

1.2 MyISAM与InnoDB区别?
InnoDB聚簇索引，MyISAM非聚簇索引
InnoDB数据与索引一起保存.ibd，MyISAM表结构.frm 索引.myi 数据.myd
InnoDB支持事务、外键、行锁表锁，MyISAM不支持事务、外键、只支持表锁
select count(*)
MyISAM查询更优，InnoDB更新更优
都是B+tree索引
MyISAM支持全文索引，InnoDB5.6后支持

MyISAM：
不支持事务，但是每次查询都是原子的
支持表级锁，每次操作对整个表加锁
存储表的总行数
一个MyISAM表有三个文件：表结构.frm 索引.myi 数据 .myd
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

Innodb：
支持ACID事务，支持四种隔离级别
支持行级锁及外键约束，因此支持写并发
不存储总行
主键索引采用聚集索引(索引的数据域存储数据文件本身)，辅索引的数据域存储主键的值;因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

1.3 聚簇索引和非聚簇索引的区别？
聚簇索引： 将数据存储与索引放到了一块，找到索引也就找到了数据。
非聚簇索引： 将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。
主键索引和普通索引的区别： 主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)。
聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

1.4 char和varchar的区别？
1.CHAR的长度是不可变的，而VARCHAR的长度是可变的，也就是说，定义一个CHAR[10]和VARCHAR[10],如果存进去的是‘ABCD’, 那么CHAR所占的长度依然为10，除了字符‘ABCD’外，后面跟六个空格，而VARCHAR的长度变为4了，取数据的时候CHAR类型的要用trim()去掉多余的空格，而VARCHAR类型是不需要的。

2、CHAR的存取速度要比VARCHAR快得多，因为其长度固定，方便程序的存储与查找；但是CHAR为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可以说是以空间换取时间效率，而VARCHAR则是以空间效率为首位的。

3、CHAR的存储方式是，一个英文字符（ASCII）占用1个字节，一个汉字占用两个字节；而VARCHAR的存储方式是，一个英文字符占用2个字节，一个汉字也占用2个字节。

1.5 Innodb为什么推荐使用自增ID？
①主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费。

②新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗。

③减少了页分裂和碎片的产生。

UUID：大量的随机IO+页分裂导致移动大量的数据+数据会有碎片。

总结：自增ID有序，会按顺序往最后插入，而UUID无序，随机生成，随机插入，会造成频繁页分裂，内存碎片化，大量随机IO。

1.6 什么是索引？
排好序的数据结构，可以帮助快速查找数据。

优缺点：索引可以提高查询速度，查询使用优化隐藏器提高性能，但是也会占据物理空间，降低增删改的速度，因为还要操作索引文件。

1.7 索引类型？
覆盖索引+回表+索引下推+联合索引

1.普通索引：可以重复
2.唯一索引：唯一，可为空，表中只有一个主键索引，可多个唯一索引
3.主键索引：唯一，不为空，叶子结点存出了行记录数据，主键索引也称聚簇索引，对应非主键索引的叶子结点存的主键的值（二级索引），用二级索引查需要回表操作（根据二级索引查到主键，再根据主键去主键索引查）一般推荐用自增主键，保证空间利用率，减少页分裂
4.全文索引
5.覆盖索引：索引字段覆盖了查询语句涉及的字段，直接通过索引文件就可以返回查询所需的数据，不必通过回表操作。
6.回表：通过索引找到主键，再根据主键id去主键索引查。
7.索引下推：在根据索引查询过程中就根据查询条件过滤掉一些记录，减少最后的回表操作。
假如执行select * from stu where name=? and age=? 没有索引下推先再存储引擎根据name筛选数据返回给server层，然后server层再根据age过滤。有索引下推直接根据name和age在存储引擎层就筛选得到结果。

1.8 索引底层数据结构？
B+树、hash
hash底层是哈希表实现，等值查询，可以快速定位，一般情况效率很高，不稳定，当出现大量键重复哈希冲突，效率下降，不支持范围查询，无法用于排序分组，无法模糊查询，多列索引的最左前缀匹配原则，总要回表操作等。

1.9 B树与B+树区别？为何用B+树？
B+树：非叶子结点不存data，只存key，查询更稳定，增大了广度(B+树出度更大，树高矮，节点小，磁盘IO次数少)；叶子结点下一级指针（范围查询）；索引冗余。B树非叶子节点也可以存数据，B+树所有数据都是存储在叶子节点上的，非叶子节点不存放数据。叶子节点之间有双向链表相连适合范围查询。

与红黑树相比：
更少查询次数：B+树出度更大，树高更低，查询次数更少
磁盘预读原理：为了减少IO操作，往往不严格按需读取，而是预读。B+树叶子结点存储相临，读取会快一些。

存储更多索引结点：B+树只在叶子结点储存数据，非叶子结点存索引，而一个结点就是磁盘一个内存页，内存页大小固定，那么相比B树这些可以·存更多的索引结点，出度更大，树高矮，查询次数少，磁盘IO少。

1.10 索引字段是int好还是varchar好？
int占用更少的内存 innodb存储引擎采用的b+树每个节点是16k(show variables like ‘innodb_page_size’;)16438那么意味着占用更少的内存b+树节点可以存放更多的指针所以B+树可以更好的控制树的高度，减少io。

1.11 索引设计原则（查询快，占用空间少）
1.出现在where子句或则连接子句中的列
2.基数小的表没必要
3.使用短索引，如果索引长字符串列，应该指定前缀长度
4.定义有外键的数据列一定索引
5.不要过度索引
6.更新频繁的不适合
7.区分度不高的不适合，如性别
8.尽量扩展索引，别新建索引，如(a)->(a,b)
9.字符串字段建立索引方法
  1、直接创建完整索引，这样可能比较占用空间；
  2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
  3、倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  4、额外用一个字段进行索引，额外计算开销
总结：索引设计原则要求查询快，占用空间少；一般建在where条件，匹配度高的；要求基数大，区分度高，不要过大索引，尽量扩展，用联合索引，更新频繁不适合、使用短索引。

1.12 索引失效场景？
1.以“%”开头的like语句，索引无效，后缀“%”不影响
2.or语句前后没有同时使用索引
3.列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换）
4.如果mysql估计使用全表扫描比索引快，则不用索引（键值少，重复数据多）
5.组合索引要遵守最左前缀原则——不使用第一列索引 失效
6.在索引字段上使用not，<>，！= （对它处理是全表扫描）
7.对索引字段进行计算操作，字段使用函数也会失效
8.is null

1.13 如何创建索引?
ALTER TABLE table_name ADD INDEX index_name (column_list)；
CREATE INDEX index_name ON table_name (column_list)；
在create table时创建

1.14 非聚簇索引一定会回表查询吗？
查询字段全部命中索引，覆盖索引，不走回表，直接从索引得到结果，不要查数据文件
总结：覆盖索引就不走回表

1.15 联合索引的建立规则？
将查询需求频繁或者字段选择性高的列放在前面
索引的复用，可以少维护一些索引(a)->(a,b)
如果既有联合查询，又有基于a、b各自的查询呢？考虑的原则就是空间，将小的单独建索引

1.16 最左匹配原则?
从左往右匹配，直到遇到范围查询。建立联合索引（a,b,c）索引是先根据a排序，a相同时b有序，a不同无序，以此类推。总之遇到范围查询就停。

  （a,b）联合索引 [(2,4),(),()]
                    \|/    \|/
   [(1,1),(1,2),(2,1)]     [(2,4),(3,1),(3,2)]
规律：a有顺序（1，1，2，2，2，3，3）b无顺序，a相同时b又有顺序，不同a之间b没有顺序，所以a=1,b>2走联合索引；a>1,b>2不走索引。

select * from table_name where a = '1' and b = '2' and c = '3'
//全值匹配查询，用到索引，与顺序无关，查询优化器，会自动优化查询顺序 

select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'
//匹配左边的列时，用到了索引

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3'
//没有用到索引

select * from table_name where a = '1' and c = '3' 
//a用到了索引，b、c没有到

select * from table_name where  a > 1 and a < 3 and b > 1;
//只有a用到索引，在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤

select * from table_name where  a = 1 and b > 3;
// a=1的情况下b是有序的，进行范围查找走的是联合索引 走 a b索引(a相同时b有序)

1.17 一条sql执行很慢的原因？
一个 SQL 执行的很慢，我们要分两种情况讨论：
1、大多数情况下很正常，偶尔很慢，则有如下原因
(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
(2)、执行的时候，遇到锁，如表锁、行锁。
(3)、sql写的烂了
2、这条 SQL 语句一直执行的很慢，则有如下原因
(1)、没有用上索引或则索引失效：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
(2)、有索引可能会走全表扫描
怎样判断是否走全表扫描：
索引区分度（索引的值不同越多，区分度越高），称为基数，而数据量大时不可能全部扫描一遍得到基数，而是采样部分数据进行预测，那有可能预测错了，导致走全表扫描。

1.18 SQL优化的例子？
1、应尽量避免全表扫描，应考虑在where及order by涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询select id from t where num=0。
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描
6.对于连续的数值，能用 between 就不要用 in 了
7.查询也将导致全表扫描 select id from t where name like ‘%abc%’
8.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
9.尽量避免大事务操作，提高系统并发能力。

1.19 COUNT()
1、COUNT有几种用法？
count() count(列名) count(1)三种用法。阿里巴巴java开发手册中提及到：强制不要使用count(列名) count(1)来代替count()
count(*)是sql92定义的用来统计行数的语法，跟null与非null无关。
2、COUNT(字段名)和COUNT()的查询结果有什么不同？
count(字段名)如果该字段为null 那么他不统计null忽略null值 count()将null值也统计进来，包含null值的行数
count(字段)进行统计的时候会将整个字段进行非空判断，效率比count(*)低
3、COUNT(1)和COUNT()之间有什么不同？
COUNT(常量) 和 COUNT()表示的是直接查询符合条件的数据库表的行数。而COUNT(列名)表示的是查询符合条件的列的值
不为NULL的行数。
4、COUNT(1)和COUNT()之间的效率哪个更高？
对于COUNT(1)和COUNT()，MySQL的优化是完全一样的，根本不存在谁比谁快！
5、为什么《阿里巴巴Java开发手册》建议使用COUNT()
建议使用COUNT()！因为这个是SQL92定义的标准统计行数的语法
6、MySQL的MyISAM引擎对COUNT()做了哪些优化？
MyISAM做了一个简单的优化，那就是它可以把表的总行数单独记录下来，如果从一张表中使用COUNT()进行查询的时候
可以直接返回这个记录下来的数值就可以了，当然，前提是不能有where条件。

1.20 什么是mysql事务？事务的四大特性是什么？
事务是恢复和并发控制的基本单位。是一组原子性的sql查询，或者说是一个独立的工作单元。事务内的语句要么全部执行成功要么全部失败。
事务的四大特性：
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
原子性(通过undolog日志来实现的):
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么
全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性(一致性是事务的最终追求,一致性是通过其他三个特性来实现的):
数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即
使在转账过程中系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做
的修改也不会保存到数据库中。）

隔离性(通过mvcc+锁来实现的):
通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。（在前面的例
子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则
其看到支票帐户的余额并没有被减去200美元。）

持久性(通过redo log日志来实现的):
一旦事务提交，则其所做的修改会永久保存到数据库。
说明:事务能够保证数据的完整性和一致性，让用户的操作更加安全。

1.21 说一说Drop、Delete与Truncate的共同点和区别？
Drop、Delete、Truncate都表示删除，但是三者有一些差别：
Delete用来删除表的全部或者一部分数据行，执行Delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除，会触发这个表上所有的delete触发器。
Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比Delete更快，占用的空间更小。
Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。
因此，在不再需要一张表的时候，用Drop；在想删除部分数据行时候，用Delete；在保留表而删除所有数据的时候用Truncate。


 sql注入攻击原理, 代码层面防止sql注入
# 原理 通过前端的表单提交的数据中携带sql语句, 欺骗服务器, 在后端对数据进行存储时, 执行恶意的sql语句 # 出现在哪里 比如在使用pymysql操作数据库是使用字符串拼接生产sql语句, 就会出现sql注入漏洞 
# 如何防止 
1. 使用pymysql时, 不要使用字符串拼接, 而是使用execute方法, pymysql模块已经对提交的数据做了处理, 不会出 现sql注入 
2. 不要相信前端提交的任何数据, 要严格校验 
3. 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。 
4. 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息 
5. 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装 
6. 使用辅助软件对网站进行检测, 软件一般采用sql注入检测工具jsky, 网站平台就有亿思网站安全平台检测