当前位置：首页 > article >正文

MySQL数据库——数据类型，索引，事务，优化

article 2024/12/27 0:48:55

数据类型

存储MD5用varchar还是char

用varchar存储一本小说

datetime,timstamp

myisam和innodb有什么区别

索引

MySQL索引的优缺点

索引失效的原因

聚集索引和非聚集索引

MySQL索引采用B+树

B,B+树

为什么SQL语句命中索引比不命中快

事务

事务的隔离级别

实现原理

mvcc

innodb怎么解决幻读

性能优化

SQL执行慢有哪些排查思路

数据库连接池

分布式系统不推荐使用多表关联查询

数据类型

存储MD5用varchar还是char

char 的长度是固定，varchar是可变的，例如：定义char(10)时，输入值“ABC”，他占用的空间是10个字符，包含7个空字符。当输入的字符串长度超过指定的数时，char会截取超过的字符。MySQL会自动删除输入字符串末尾的空格。所以char适合存储很短，长度固定字符串。varchar用于存储可变长度65535，定义varchar(10)时，输入值“ABC”，他占用的空间是3个字符，varchar会保留1或2个额外的字节来记录字符串实际的长度，小于或等于保留1个字节，大于255保留2个字节。

存储效率不同，char每次修改后长度不变效率更高，varchar每次修改后更新长度，效率更低。

存储空间不同，char存储空间是初始化长度字符串加上一个记录字符串长度字节，可能存在多余的空间，而varchar的存储空间是实际字符串加上一个记录字符串长度的字节，空间更小。

使用char存储MD5，因为MD5是一个固定长度的值，使用char比varchar效率更高。

用varchar存储一本小说

一个表里每一行数据的上限是65535，除了text，blob等这些没有长度限制的字段，这一行里的其他所有列的总长度不能超过65535字节，64KB，如果换算成varchar，按每个字符4个字节计算，varchar最大能存储16383个字符，存储一本小说是不够的。

在innodb引擎里，每一行的大小不能超过数据页大小16KB的一半，所以MySQL设计了溢出页，如果字段超长，把这个字段放到溢出页里。例如，dynamic格式，那么数据行里存储只是溢出页的地址，固定长度的字段不会存储到溢出页里。所以无论存储多大的一行数据，只会存储在一个主数据页里，超出的部分存储在溢出页里。

MySQL不会对字段大小进行限制，对字段的数量做了限制，每张表只能有4096个字段，每行8000字节，例如，有一张表每个字段的长度是8字节，那么最大能创建1000个字段。

datetime,timstamp

datetime固定长度是8个字节，默认的时间格式YYYY-MM-DD HH:mm:ss。可以设计长度，代表的是存储的毫秒值，数值越大，时间精度越高，支持每次更新记录时间。

timestamp 是一种时间戳格式，存储的内容是1970-01-01到现在相差的毫秒数。默认占4个字节，支持毫秒数，设置为7个字节。支持跨时区和夏令时自适应。如果没有指定时区，在高并发场景里出现性能抖动，性能不如datetime.timestamp调用操作系统的tz_convert函数转换，这个函数每次交互时都会加锁。

myisam和innodb有什么区别

数据存储结构不同，每个myisam在磁盘上存储为3个文件，他们以表的名字开头，。frm文件存储表的定义，myd存储数据文件，myi存储文件索引。innodb在磁盘上保存2个文件，frm存储表结构文件，ibd存储数据和索引文件。

由于myisam的索引和数据是分开存储的，因此索引查找的时候，myisam的叶节点存储的是数据的地址，不是数据。innodb叶节点存储的是整个数据行的所有数据。

存储空间消耗不同，myisam可以压缩，存储空间较小，支持，静态表，动态表，压缩表。innodb需要更多的内存和存储空间，在内存里建立缓冲池，用来高速缓存数据和索引。所有的表都保存在同一个数据文件里，表的大小受限于操作系统的大小，一般为2G

事务支持不同，myisam不支持事务，每次查询具有原子性，执行速度比innodb快。innodb支持事务。

锁的支持不同，myisam在增删的时候需要锁定整个表格，效率会低一些。大部分表操作是查询，myisam效率更高

innodb支持行锁，删除，插入的时候只需要锁定行就可以。

外键支持，myisam不支持，innodb支持

索引

MySQL索引的优缺点

innodb使用B+树的结构来实现索引和数据存储。

优点：

1.通过B+树来存储数据，可以减少数据检索时的磁盘io次数，从而提升查询数据的性能。

2.B+树索引在进行范围查询的时候，只需要找到起始节点，然后基于叶节点的链表结构往下读取即可，查询效率高

3.通过唯一索引约束，可以保证数据表里每一行数据的唯一性。

缺点：

1.数据的增删改涉及索引的维护，在数据量大的请情况下，索引的维护带来较大的性能开销。

2.一个表里允许存在一个聚集索引和多个非聚集索引，但是索引数量不能创建太多，否则造成索引维护成本过高。

3.创建索引的时候需要考虑索引字段的分散性，如果字段的重复数据过多，那么创建索引会降低性能。

索引失效的原因

innodb里有2种索引，主键索引和普通索引，innodb使用B+树结果存储和索引数据。当使用索引列进行数据查询的时候，最终会到主键索引树里查询对应的数据行并返回，使用索引查询提高查询效率，不规范的使用会导致索引失效。

在索引列上做运算，例如使用函数，MySQL在生成执行计划的时候，根据统计信息来判断是否使用索引。在索引列上加函数运算，导致无法识别索引列，就不使用索引，MySQL8里，增加了索引函数，解决了这个问题。

在一个由列组成的组合索引里，需要按照最左匹配法则，从索引的最左列开始顺序检查，否则不会使用索引，在组合索引里，索引的存储结构是按照索引列的顺序来存储的，因此在SQL语句里也需要按照这个顺序才能进行逐一匹配，否则innodb无法识别索引，导致索引失败。

当索引存在隐式转换的时候，例如索引列是字符串类型，但是在SQL查询时没有使用引号，那么MySQL自动进行类型转换，从而导致索引失效。

使用like通配符匹配后缀%xxx时候，由于这种方式不符合索引的最左匹配原则，所以不会使用索引。正确方式，xxx%。

使用or连接查询，or语句前后没有同时使用索引，索引会失效。只有or语句前后的查询字段都是索引列的时候，才会生效。

聚集索引和非聚集索引

聚集索引是基于主键创建的索引，除了主键索引之外的其他索引是非聚集索引，也称2级索引。

在innodb引擎里，一张表数据对于的物理文件是按照B+树组织的一种索引结构，而聚集索引是按照每张表的主键来构建的一棵B+树，然后叶节点存储了这个表的每一行数据记录。所以基于这个的一个特性，聚集索引不仅是一种索引类型，还代表一个数据存储方式，同时意味着每张表必须有一个主键，如果没有innodb会默认选择或者添加一个隐藏列作为主键索引来存储表的数据行，一般选择自增的ID作为主键，这样ID本身具有连续性，使得对应的数据也会按照顺序存储到磁盘上，写入和索引性能高。如果使用uuid的随机ID，在插入数据的时候，随机磁盘io，导致导致性能下降。

在innodb里只存在一个聚集索引，如果存在多个聚集索引，那么这个表里的数据存在多个副本，造成磁盘空间浪费，数据维护困难。

主键索引表示的是一种数据存储结构，所以如果是基于非聚集索引来查询记录，最终还需要访问主键索引检索。

MySQL索引采用B+树

数据库的存储引擎采用B,B+树来实现索引的存储。b树是一个多路平衡树，用这种数据结构存储大量数据，他的高度比二叉树矮很多。对于数据库而言所有的数据都保存在磁盘上，而磁盘io的效率低，随机磁盘io效率更低，树的高度决定的磁盘的io次数，io次数越少，性能提升就越高，这是采用B树作为索引存储结构的原因。MySQL的innodb存储引擎B+树作为索引和存储结构。

1.B+树所有数据都存储在叶节点上，非叶子节点存储索引。

2.叶节点使用双向链表进行关联。

从磁盘io效率，B+树的非叶节点不存储数据，所以每一层能存储更多的索引。B+树在层数相同的情况下，能够比B树存储更多的数据，减少磁盘io次数。

范围查询效率，在MySQL里范围查询是一个常用操作，B+树所有数据都存储在叶节点上并且叶节点使用双向链表进行关联，所以B+树在范围查询时只需要查询2个节点并进行遍历，而B树需要获取所有节点，因此B+在范围查询时效率更高。

全表查询，B+树的叶节点存储所有数据，只需要扫描叶节点，实现全表查询，所以B+树的全局扫描能力更强，B树需要遍历整个树。

自增ID，基于B+树的数据结构，如果采用整型数据作为主键，能够更好避免增加数据时带来叶子节点分裂导致的大量运算问题。

B,B+树

树是一种数据结构，树的节点可以无限延伸，可以用实现菜单，这样的无限级，无限宽的树查询性能比较低，为了提高树的查询性能，出现了二叉树。

二叉树每个节点有2个分支，

二叉查找树，左子树的所有子节点都小于根节点，右子树的所有子节点都大于根节点。

平衡二叉树，左右2个子树的高度差的绝对值不超过不超过1，为了达到这样的平衡，引入一个左旋和右旋的机制来实现树的平衡。

B树是一种多路平衡查找树，他是平衡二叉树，有多个子路，子路的数量取决于关键字的数量。平衡二叉树的高度大于B树的高度。

B+树在B树的基础上进行了增强，1.B树的数据存储在每个节点上，B+树数据存储在叶节点上，并且通过链表的方式将所有的叶节点全部串联起来。

2.B+树的子树数量等于他的关键字数量，B树是关键字数量加1.

为什么使用B,B+树作为索引结构，因为平衡二叉树的高度比B,B+树更高，这样有更多的磁盘io数量。为了减少io次数，文件系统或者数据库使用B,B+树作为索引结构。

MongoDB使用B树，所有的节点都有数据域，只要找到指定的索引就可以进行访问，这样单次查询速度更快。

MySQL是一种关系型数据库，区间访问是常见的情况，由于B+树的数据全部存储在叶节点上，并且通过指针串联在一起，容易进行区间遍历。实现服务查询。

为什么SQL语句命中索引比不命中快

数据库的索引帮助我们快速检索想要的数据，不至于每次都做全部扫描，在不使用如何算法的情况下，我们查询10万条记录里的某一条，最坏的情况下需要遍历10万次。

如果使用二分查找算法，只需要进行log2 20000约等于14.28，大约14搜索，就可以找到。

MySQL的innodb引擎采用的是B+树数据结构，当执行select语句查询数据的时候，innodb需要从磁盘上读取数据，这个过程涉及磁盘io。

系统会把数据的逻辑地址传给磁盘，磁盘控制线路按照寻址逻辑把逻辑地址翻译成物理地址，确定要读取的数据在那个磁道，扇区。为了读取这个扇区的数据，需要把磁头放在这个扇区上，磁盘不断旋转把目标扇区旋转到磁头下面，使得磁头能够找到对应的磁道。这里涉及寻道和旋转时间的损耗。这样磁盘io的性能开销是很大的，尤其是在查询数据量大的情况下。

在innodb里对存储在磁盘上的数据建立了索引，然后把索引数据和索引列对应的磁盘地址以B+树的方式进行存储。当需要查找目标数据的时候，根据索引查找目标数据即可。由于B+树的子树很多，所以只需要较少的磁盘io次数就能查询到数据。

建立太多索引会引起性能下降，例如给一个表里所有字段增加索引，如果一个索引和表一样长，那么他将再次成为一个需要查询的开销。

缺点，索引会降低写入的性能。当有索引存在时，插入一条数据，需要写入数据和更新索引。

定义索引，1.索引会降低写入性能

2.使用表里唯一值作为索引

3.在关系型数据库里做外键的字段必须建立索引，因为他有助于跨多个表进行复杂查询

4.索引使用磁盘空间，在选择索引字段时要小心。

事务

事务的隔离级别

解决多个事务相互竞争导致数据安全问题的一种规范。

事务竞争导致的现象，

脏读，假设有2个事务T1,T2同时执行，T1有可能读到事务2未提交的数据，但是未提交的事务2可能执行回滚操作，导致事务1读到最终不一定存在的数据。

不可重复读，假设2个事务，t1,t2同时执行，事务1在不同时刻读取同一行的数据结果可能不一样。

幻读，假设2个事务，t1,t2同时执行，事务1在执行范围查找或者范围修改的过程里，事务2插入了一条属于事务1范围的数据并提交了，这时在事务1查询发现多出一条数据，或者在事务1发现这条数据没有被修改。

在实际应用里可能有些场景不能接受这些现象，SQL标准规定了隔离级别

读未提交RU，在这种隔离级别下，可能产生脏读，不可重复读，幻读。

读已提交RC，在这种隔离级别下，可能产生不可重复读，幻读。

可重复读RR，在这种隔离级别下，可能产生幻读。

串行化，在这种隔离级别下，多个事务串行执行，不会产生安全问题，性能是最低的。

在MySQL，innodb引擎默认使用隔离级别是RR，因为他需要保证事务的隔离性。

实现原理

事务满足acid特性，如果实现acid

原子性，保证多个dml操作是原子的，要么都成功，要么都失败，失败需要对原本执行成功的数据进行回滚，使用undo-log表进行回滚，在执行事务的过程里，把修改之前的数据快照保存到undo-log里面，一旦出现错误，就从undo-log里读取数据执行反向操作。

一致性，数据的完整性约束没有被破坏。

隔离性，多个并行事务对同一个数据进行操作的时候，如果避免多个事务导致数据的混乱问题，innodb实现SQL92标准，提供4种隔离级别

持久性，只要事务提交成功，那么对于这个数据结果的影响是永久性的，不会因为宕机或其他原因导致数据变更失败。

在持久化过程里，如果数据库宕机，就会导致数据丢失。innodb引入redo-log文件避免这个问题，这个文件存储了数据被修改之后的值，当我们通过事务对数据进行变更操作时，除了修改内存缓冲区里面的数据，还会把本次修改的值写入redo-log里面。当提交事务的时候直接把redo-log日志刷到磁盘上持久化，一旦数据库宕机，在MySQL重启后读取redo-log日志，在执行一遍，保证持久性。

mvcc

多版本并发控制，multi-version concurrency control 解决事务操作里多线程并发安全问题，使用无锁并发技术，

数据库并发场景，

读和读并发，多个线程同时进行读取操作，这时不会产生并发问题，不需要并发控制。

读写并发，多个线程子同一时间进行读写操作，这时可能产生事务隔离问题，可能遇到脏读，不可重复读，幻读问题。

写写并发，多个线程同时进行写操作，这时可能出现数据更新丢失问题。

mvcc为每个修改保存一个版本，这个版本与事务时间戳相关，读操作读取该事务开始前的数据库快照。他是通过数据库记录里的隐式字段undo日志，Read View实现。

在并发读写数据库时，可以做到在执行读操作时，不阻塞写操作，执行写操作时不阻塞读操作，提高了数据库的并发读写能力

实现读的一致性，解决脏读，不可重复读，幻读问题，不解决数据更新丢失问题

采用乐观锁或悲观锁机制解决读写冲突，从而提供数据库并发性能。

innodb怎么解决幻读

MySQL有4种事务隔离级别，用来解决脏读，不可重复读，幻读问题。

幻读，在同一个事务里，前后2次查询相同的范围，得到的结果不一致。例如，事务1里执行里一个范围查询操作，这时满足查询条件的数据只要一个，事务2插入了一行数据并提交了，事务1再去查询的时候，发现比第一次查询的结果多了一条数据。

这样幻读带来数据一致性问题。innodb加入间隙锁和next-Key Lock机制解决幻读问题。

间隙锁，功能是锁定一定范围内的索引记录，这时其他事务对这个区间进行增删改操作会被锁住。例如查询 select * from user where id>3 and id <7

next-Key Lock，临键锁，MySQL默认的行锁算法，相当于记录锁和间隙锁的集合.

例如。 select * from user where id>3每个数据行的非唯一索引列上都存在一个next-key Lock，当某个事务持有该数据行的next-key Lock时，会锁住一段左开右闭区间的数据。

他们区别，间隙锁只锁定2个索引之间的引用间隙，而next-key 锁锁定多个索引区间，包含记录锁和间隙锁。lbcc

性能优化

SQL执行慢有哪些排查思路

执行慢的原因，

没有索引或者索引失效，打开MySQL的慢查询日志，收集一段时间的日志内容，找出耗时最长的SQL语句，然后对这些语句进行分析。使用执行计划查看SQL是否命中索引。若没有命中，优化这些SQL语句，保证SQL使用索引执行。如果SQL结果没有办法优化可以给表加上索引。

表单数据量过多导致查询瓶颈，即使SQL语句使用了索引，表现也不好的时候，考虑对表进行切分，表切分规则分为2种水平切分和垂直切分。水平切分，把一张数据行达到千万级别的大表，按照业务主键分成多张小表，这些小表可能有100到1000张。

垂直切分，将一张表里的多个列按照业务逻辑把关联性比较大的列放在同一张表里。

分库，例如我们把切完1000张表后，把后缀为0到100的表放在同一个数据库实例里，把后缀100到200的表放在另一个数据库实例里，以此类推，这样把1000张表放在10个数据库实例里，这样可以根据不同的业务主键把请求路由到不同的数据库实例里，这样每个数据库承担的流量就比较小，达到提供数据库性能的目的。

网络原因或机器负载过高，读写分离，MySQL支持一主多从的分布式部署，可以将主库用来处理写操作，多个从库只用来处理读操作。在流量比较大的场景里，可以增加从库来提高数据库的负载能力，从而提升数据库的总体性能。

热点数据导致单点负载不均衡，增加缓存，将查询比较频繁的数据预存到缓存里，来缓解数据压力，提高数据库的响应速度。

数据库连接池

是一种池化技术，核心思想是实现资源的复用，避免资源重复创建和销毁的开销。在数据库应用场景里，应用程序每次向数据库发起crud操作的时候，都需要创建连接，在数据库访问量较大的情况下，频繁的创建连接会带来比较大的性能开销，使用连接池可以避免创建连接的开销，应用程序初始化的时候，提前初始化一部分连接并保存到连接池里，当需要使用连接的时候，直接从连接池里获取一个建立好连接。

关键参数，

初始化连接数，

最大连接数，同时最大支持多少连接，超过后后续获取连接的线程会阻塞

最大空闲连接数，没有请求的时候，连接池里保留的最大连接数

最小空闲连接数，当连接数小于这个值的时候，连接池需要创建连接。

最大等待时间，连接池里面连接用完后，新请求等待的时间，超过这个时间触发超时异常

无效连接清除，清除连接池里面的无效连接，避免使用连接时出现错误。