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

谈谈Mysql的常见基础问题

article 2024/11/24 16:49:24

一.为什么Mysql选择B+树作为索引？

索引(Index)是帮助MySQL高效获取数据的数据结构；

但是索引的数据结构有多种，如MySQL的InnoDB存储引擎支持B+树索引还有哈希索引(可以理解为简单的HashMap)。

而针对关系型的数据，如果选用哈希索引会有以下的问题:

1.哈希索引只能匹配是否相等，不能实现范围查找;

2.当需要按照索引进行order by时，哈希索引没办法支持排序(哈希索引是根据哈希进行排序的，B+树本身是有顺序的):

3.B+树索引支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了a和b也可以查询的，如果使用哈希索引，没办法支持部分索引(如果是哈希索引使用的是组合索引，必须是a,b,c作为一个哈希进行匹配，只有a,b是没办法进行匹配的);

4.当数据量很大时，哈希索引的hash冲突的概率也会非常大。

所以选择B+树作为索引。

浅谈B+树：

1.B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最常用和最为有效的索引；

2.B+树索引的构造类似于二叉树，根据键值(Key-Value)快速找到数据。

注意B+树中的B不是代表二叉(binary)，而是代表平衡(balance)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

比如，在以下数组中找到数字48对应的下标：

二分查找法：至少3次，最多4次；

顺序查找法：至少8次，最多10次；

下图举例B+树：

1.上图中，最下面的每个叶子页存储了实际的数据，叶子页就可以存放多条数据，叶子节点由小到大(有序)串联在一起(通过链表:相邻的叶子节点之间用指针相连)，叶子页中的数据也是排好序的:
而通过以上图中可以看到，通过B+树去查找一个数据，二分查找的次数最多为3次。

2.同时B+树叶非常方便的支持顺序查询:

比如select * from table where id >=21 and id <=48，那么只需要根据21和48定位到对应的叶子节点数据，然后从叶子节点的21到48顺序的读取出来即可，这样就非常快速的完成了范围查询，并且出来的数据也已经排好顺序的。

二.Mysql的优化可以从哪些方面考虑？

1.索引优化：索引是加速数据库查询的关键。

(1)在设计表结构时，应该根据查询的需求添加合适的索引。常用的索引包括主键唯一索引、普通索引、全文索引等。

(2)同时要避免过多的索引，因为每个索引都需要占用存储空间，会影响写入性能。我们需要综合读和写，将查询频率高的字段加上索引，同时也需要避免过多的索引，影响写入和修改的效率。

2.查询优化：优化查询语句是提高Mysql性能的重要手段。

(1)要尽可能使用索引，避免全表扫描(通过执行计划查看是否使用了索引，是否有全表扫描，表之间的关联是怎么样的，涉及到的数量是多少等信息)。

(2)同时要避免使用子查询，尽可能使用连接查询(因为子查询对比于连接查询的优化空间并不大，子查询在执行时，MySQL需要创建临时表来存储内层查询的结果，查询完成后这些临时表会被删除，这个过程增加了额外的开销，导致子查询的执行效率较低‌；相比之下，连接查询直接在内存中进行表的连接操作，不需要创建和销毁临时表，因此执行速度更快‌);

(3)避免在查询中使用“%”通配符(会导致索引失效)；

(4)避免多余的字段等等(不要select *,会导致回表操作)。

3.数据库表结构优化：合理的表结构可以提高查询效率和减少存储空间。

(1)比如对于同一个数字类型，可以使用long 也可以使用int ，推荐使用内存占用更少的int类型，这样就可以提高查询效率，减少存储空间，对于Mysql来说，如果字段类型越小，查询的磁盘范围就越少，数据在磁盘存储的空间就会更加进紧凑，所以查询的时候，磁盘开销IO开销就会更少；

(2)应该避免使用大字段，如TEXT、BLOB等，因为这些字段会占用大量的存储空间；

(3)同时应该避免冗余字段(但是有的时候设计A、B表的时候，为了查询效率，会把B表的某些字段放在A表中，此时，这些冗余字段需要选使用频次比较高的，不然在查询A表的时候，发现并不需要B表的那些字段，就会导致存储空间和查询效率的浪费)，避免更新和维护时的复杂性。

4.缓存优化：使用缓存可以大大减轻MySQL数据库的压力，提高查询效率。

常用的缓存技术包括Memcached和Redis等。

5.分区优化：分库分表。

对于数据量较大的表，可以使用分区技术将表分成多个部分，这样可以提高查询效率，同时降低了单个表的存储空间和索引大小。

6.配置优化：MySQL的参数配置会影响MySQL的性能。

需要根据实际情况进行调整，包括缓冲区、连接数、线程数、查询缓存等等。

7.硬件优化：硬件设备也会影响MySQL的性能。

(1)要选择更快速的硬件设备，如更快的磁盘、更快的CPU和更多的内存等等；

(2)同时，要根据实际情况来决定使用RAID、SSD等技术。

小结：

很明显从图上可以看出，越往上走，难度越来越高，收益却是越来越小的。

1.对于架构调优，在系统设计时首先需要充分考虑业务的实际情况，是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做；然后考虑写的并发量有多大，是否需要采用分布式;最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失。所以在进行优化时，首先需要关注和优化的应该是架构。

2.对于MySQL调优，需要确认业务表结构设计是否合理，SQL语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引等等。

3.比如硬件和OS调优，需要对硬件和OS有着非常深刻的了解，仅仅就磁盘一项来说，一般非DBA能想到的调整就是SSD盘比用机械硬盘更好。DBA级别考虑的至少包括了，使用什么样的磁盘阵列(RAID)级别、是否可以分散磁盘、是否使用裸设备存放数据，使用哪种文件系统(目前比较推荐的是XFS)，操作系统的磁盘调度算法选择，是否需要调整操作系统文件管理方面比如atime属性等等。

注意：

综上所述：我们重点关注MySQL方面的调优，特别是索引，SQL索引调优要求对业务和数据流非常清楚。

三.什么是慢查询，如何优化？

首先，我们先说一下慢查询日志，顾名思义，就是查询花费大量时间的记录日志，是指mysql记录所有执行超过long_query_time(默认是10s，可以根实际需求修改时间，修改后需要重启mysql才能生效)参数设定的时间阈值的SQL语句的日志。该日志能够定位一些可能有问题的查询语句，能为SQL语句的优化带来很好的帮助。

1.默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能，通过如下语句查看是否开启：show variables like 'slow_query_log'，结果未OFF即为关闭；

2.需要手动开启：set global slow_query_log=1，再查看是否开启，如果是ON即为开启；

慢查询是指执行时间较长的查询操作

为避免慢查询，可以采取以下方法:

(1)使用合适的索引：根据具体查询需求创建适当的索引，以提高查询速度；

(2)优化查询语句：通过分析查询语句，找出潜在的性能问题并进行优化；

(3)避免全表扫描：尽可能地利用索引来进行查询，避免对整个表进行扫描；

(4)配置合理的缓存：通过合理配置数据库的缓存参数来提高查询性能。

四.什么是执行计划，如何理解？

通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，用来分析查询语句或是表结构的性能瓶颈。

执行计划的语法其实非常简单：在SQL查询的前面加上EXPLAIN关键字就行。

比如:EXPLAIN select * from table1

五.如何优化数据库的表结构？

1.索引列的类型尽量小(原则上越小越好)：

(1)我们在做索引选择的时候，比如字段是存数字，它可以选择int类型、bigint类型、tinyint类型，这三个类型中tinyint占用的空间最少，那么在它上面建立索引的话，效率是最高的；

(2)这种情况是在我们业务允许的情况下，设置的类型越小，占用的字段越少的话，那么它在一个数据页里面它能后存放更多的数据，就可以减少磁盘IO带来的性能损耗，也可以加快读写的效率；

(3)而且对于主键来说，主键也称作聚族索引(主键和主键的值存放在一起)，这中选择就可以节约更多的空间，较少更多的IO。

2.索引的选择性(优先选择离散度高的字段)：

(1)比如说sex字段，一般来说它只有男女，从概率来说，查询的时候是一半一半的概率，那么它的离散度就很低；

(2)比如说name字段，再进行查询的时候就有很多不同的名字了，它的离散度就很高。

3.前缀索引：

比如我有一个地区字段area，字段存储的数据都是：重庆市...区...街道，那么可以添加前缀索引alter table area add key(column(3))，当查询字段是重庆市、四川省、上海市等的时候可以提高效率的。

也就是说：如果一个字段比较长，而且该字段的前面几个字是这个字段的关键字的话，可以使用前缀索引，也可以加快索引查询效率。

4.只为用于搜索、排序或分组的列创建索引：

如果有多个搜索条件，需要创建联合索引。

5.多列索引的优化：

需要遵守最左匹配原则，否则会导致索引失效。

六.如何避免死锁？

死锁是指两个或多个事务互相等待资源释放，从而导致无法继续进行的情况(A、B操作，操作时均上锁，A在B没有释放锁的时候去操作B，B同时也在A没有释放锁的时候去操作A，这样就会造成锁的等待情况，互相等待资源释放，出现死锁)。

为避免死锁，可以采取以下措施：

1.给事务加锁顺序：让所有事务按照相同的顺序获取锁，可以避免死锁的发生(最好是使用分布式锁，比如Redis分布式锁、zookeeper分布式锁，尽量不要去使用Mysql的锁，因为Mysql虽然说在一个库里面，多个引用也具备分布式锁的特性，但本身Mysql的锁不是加在内存，而是磁盘，同时它有事务的概念，事务它有互斥性，这样会对我们对它应用的并发量有一定影响)；

2.使用悲观锁(语句添加for update)：即在事务开始时直接加锁，确保事务执行的完整性，但会对性能产生影响；

3.设置合理的超时时间：对于被锁定的资源，设置合理的超时时间，避免长时间等待导致死锁。

遇到这种问题，其实Mysql也没有办法平滑的处理，它检测出来以后，也是很简单的把一个事务回滚掉，让另外一个事务得以进行，如果在工作中遇到这种死锁的问题，最好的方式就是排除代码，因为出现这种死锁的问题，必然不是Mysql的问题，而是业务的问题，因为作为数据库是没有办法完全避免这种问题，一般都是业务在对事务加锁的时候，出现了循环加锁的情况。

MySQL中的死锁的案例：

1.两个会话1、2，开启事务，上锁：

会话1：

begin；
select * from teacher where number = 1 for update；

会话2：

begin；
select * from teacher where number = 3 for update；

2.两个会话分别执行对方上锁内容的操作：

会话1：

select * from teacher where number = 3 for update；

可以看到这个语句的执行将会被阻塞

会话2：

select * from teacher where number = 1 for update；

MySQL检测到了死锁，并结束了会话2中事务的执行，此时，切回会话1，发现原本阻塞的SQL语句执行完成了(这是MYsql自带机制，会检测死锁，关闭后面的一个事务)；

同时通过：show engine innodb status\G 可以看见死锁的详细情况，一般情况下，是无法查看实物的加锁情况，需要修改系统变量查看事务加锁的情况，innodb status output locks(MySQL5.6.16引入)，缺省是OFF，通过show variables like ’innodb_status_output_locks'进行查看，设置set global innodb status output locks = ON即可，然后再开启事务，执行语句。

七.如果优化大量数据插入的性能？

对于大量数据插入的场景(几十万上百万的数据)，可以采取以下优化措施：

1.使用批量插入(多个批次，最好没批不要超过1000条)：

合并多条insert 为一条，即:insert into tvalues(a,b,c),(d,ef)，将多个插入操作合并为一个大的插入操作，减少连接开销和通信次数，并且建议不要一次性插入全部，而是分多次进行插入，这个时候需要简练一个零时表，记录插入的条数，以防出现问题，可以去进行问题的回溯，来确保批量插入的正常；

2.修改参数bulk insert buffer size，调大批量插入的缓存(插入完成后再调整会正常的值)；

3.使用Load data语句：MySQL提供了Load data语句来快速导入大量数据，在某些情况下比INSERT语句更高效(因为使用Load data的时候，不会对SQL的语义进行分析，通过insert会进行语义分析，这样效率并不高)；

4.设置innodb flush log at trx commit =0，相对于 innodb flush log at trx commit =1 可以十分明显的提升导入速度；

innodb flush log at trx commit = 0时，log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file 的刷新或者文件系统到磁盘的刷新操作；

而如果这个值是其他的情况：

值等于1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步(在数据库中一旦涉及到磁盘，它的效率就是比较低的)；

值等于2：事务提交会触发log buffer 到log fie的刷新，但并不会触发磁盘文件系统到磁盘的同步，此外，每秒会有一次文件系统到磁盘同步操作。

在业务上还有一种优化的说法：

(1)先把对应的一些主键、索引先不创建，因为对于一张表，除非插入的是唯一性索引，这个是需要校验的，对于一些普通索引，是可以先不创建的，先把数据插入，然后在业务的低谷期再对它进行索引的创建，就把它的完成插入分为多步，这样也可以有效的去提高大量数据的插入性能;

(2)通过程序代码，使用多线程插入。

为什么说批量插入的条数不能过大？

因为插入的时候，数据是是先到Mysql中的内存池(buffer_pool)中，然后在从buffer_pool同步到磁盘，如果一次插入数据量很大，如果buffer_pool的不够了，也会排除一些异常，导致插入失败。