MySQL常见知识点详解：索引、事务与性能优化-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_49692893/article/details/122801002

一、MySQL常见知识点总结

1.1 MySQL的三大范式？

范式是我们创建表的时候要遵循的规范。满足最低要求的范式为第一范式1NF，在第一范式的基础上满足更多规范要求的称为第二范式2NF，第三范式3NF以此类推。

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的区别？

CHAR的长度是不可变的，而VARCHAR的长度是可变的，也就是说，定义一个CHAR[10]和VARCHAR[10]，如果存进去的是‘ABCD’, 那么CHAR所占的长度依然为10，除了字符‘ABCD’外，后面跟六个空格，而VARCHAR的长度变为4了，取数据的时候CHAR类型的要用trim()去掉多余的空格，而VARCHAR类型是不需要的。
CHAR的存取速度要比VARCHAR快得多，因为其长度固定，方便程序的存储与查找；但是CHAR为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可以说是以空间换取时间效率，而VARCHAR则是以空间效率为首位的。
CHAR的存储方式是一个英文字符（ASCII）占用1个字节，一个汉字占用两个字节；而VARCHAR的存储方式是一个英文字符占用2个字节，一个汉字也占用2个字节。

1.5 Innodb为什么推荐使用自增ID？

主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费。
新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗。
减少了页分裂和碎片的产生。

UUID不适合做为主键的原因：大量的随机IO+页分裂导致移动大量的数据+数据会有碎片。

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

1.6 什么是索引？

排好序的数据结构，可以帮助MySQL快速查找数据。

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

1.7 索引类型？

覆盖索引 + 回表 + 索引下推 + 联合索引

普通索引：可以重复
唯一索引：唯一，可为空，表中只有一个主键索引，可多个唯一索引
主键索引：唯一，不为空，叶子结点存出了行记录数据，主键索引也称聚簇索引，对应非主键索引的叶子结点存的主键的值（二级索引），用二级索引查需要回表操作（根据二级索引查到主键，再根据主键去主键索引查）一般推荐用自增主键，保证空间利用率，减少页分裂
全文索引
覆盖索引：索引字段覆盖了查询语句涉及的字段，直接通过索引文件就可以返回查询所需的数据，不必通过回表操作。
回表：通过索引找到主键，再根据主键id去主键索引查。
索引下推：在根据索引查询过程中就根据查询条件过滤掉一些记录，减少最后的回表操作。
假如执行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 索引设计原则（查询快，占用空间少）

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

总结：索引设计原则要求查询快，占用空间少；一般建在where条件，匹配度高的；要求基数大，区分度高，不要过大索引，尽量扩展，用联合索引，更新频繁不适合、使用短索引。

1.12 索引失效场景？

以%开头的like语句，索引无效，后缀%不影响
or语句前后没有同时使用索引
列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换）
如果mysql估计使用全表扫描比索引快，则不用索引（键值少，重复数据多）
组合索引要遵守最左前缀原则——不使用第一列索引会失效
在索引字段上使用<>，!= （对它处理是全表扫描）
对索引字段进行计算操作，字段使用函数也会失效
is null，in，not in集合数据量过大的话也会全表扫描，量少的话还是会走索引的

1.13 如何创建索引?

ALTER TABLE table_name ADD INDEX index_name (column_list)；

CREATE INDEX index_name ON table_name (column_list)；

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优化案例

为经常查询和排序的字段建立合适的索引，应尽量避免全表扫描，提高查询效率。
尽可能减少表的join连接操作，使用代码逻辑进行组装数据进行返回以及连接字段加索引，小表驱动大表。
表设计是否合理，字段类型选择合适的存储类型和大小，主键字段存储空间不宜过大，通常设置为自增，且建议我们自己主动设置主键，避免InnoDB为我们创建(InnoDB主键生成策略)。
explain关键字查看SQL执行计划，关注type列阿里巴巴开发规范手册要求SQL性能优化的目标：至少达到range级别，要求是ref级别，最好是const级别。避免All全表扫描。
对于连续的数值，能用between就不用in，in中数据量过大会让索引失效。
避免索引失效导致全表扫描的场景如：select id from t where name like ‘%abc%’
尽量使用覆盖索引返回查询需要的字段，避免select * 返回完整记录，增加网络传输开销及查询效率。
尽量避免大事务操作，提高系统并发能力。
联合索引需要满足最左匹配原则，避免联合索引失效而导致全表扫描。
查询数据量过大时可以使用分页处理，减少网络间传输负担。

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 谈一谈你对 MySQL 性能优化的理解？

MySQL 的性能优化我认为可以分为 4 大部分：

硬件和操作系统层面的优化
架构设计层面的优化
MySQL 程序配置优化
SQL 优化

1.硬件及操作系统层面优化

从硬件层面来说，影响Mysql性能的因素有CPU、可用内存大小、磁盘读写速度、网络带宽。
从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到MySQL性能。
这部分的优化一般由DBA或者运维工程师去完成。
在硬件基础资源的优化中，我们重点应该关注服务本身承载的体量，然后提出合理的指标要求，避免出现资源浪费！

2.架构设计层面的优化
MySQL是一个磁盘IO访问量非常频繁的关系型数据库，在高并发和高性能的场景中，MySQL数据库必然会承受巨大的并发压力，而此时我们的优化方式可以分为几个部分。

搭建Mysql主从集群，单个Mysql服务容易单点故障，一旦服务器宕机，将会导致依赖Mysql数据库的应用全部无法响应。主从集群可以保证服务的高可用性。
读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能影响。
引入分库分表机制，通过分库可以降低单个服务器节点的IO压力，通过分表的方式可以降低单表数据量，从而提升SQL查询的效率。
针对热点数据，可以引入更为高效的分布式数据库，比如Redis、MongoDB等，他们可以很好的缓解Mysql的访问压力，同时还能提升数据检索性能。

3.MySQL程序配置优化
MySQL数据库本身的优化，可以通过Mysql中的配置文件my.cnf来完成的，比如：

Mysql5.7版本默认的最大连接数是151个，这个值可以在my.cnf中修改
binlog日志，默认是不开启
缓存池bufferpoll的默认大小配置等

4.SQL优化又能分为三步曲

慢SQL的定位和排查
我们可以通过慢查询日志和慢查询日志分析工具得到有问题的SQL列表。
第二、执行计划分析
针对慢SQL，我们可以使用关键字explain来查看当前sql的执行计划，可以重点关注type key rows filterd等字段，从而定位该SQL执行慢的根本原因。
使用 show profile 工具
Show Profile是MySQL提供的可以用来分析当前会话中，SQL语句资源消耗情况的工具，可用于 SQL调优的测量。在当前会话中.默认情况下处于 show profile是关闭状态，打开之后保存最近15次的运行结果针对运行慢的SQL，通过profile工具进行详细分析，可以得到SQL执行过程中所有的资源开销情况如IO开销，CPU开销，内存开销等。

最后总结一下常见的SQL优化规则：