一.为什么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的不够了，也会排除一些异常，导致插入失败。

八.大数据量(超100W行)的批量写(update、delete、insert)操作会导致什么问题？

1.阻塞其他操作

批量写操作可能需要占用大量的系统资源，包括CPU、内存、磁盘等，如果写操作持续时间过长，可能会阻塞其他操作，导致系统响应变慢(导致Mysql其他的查询相应时间变慢)；

2.磁盘空间不足

批量写操作可能会占用大量的磁盘空间，如果磁盘空间不足，可能会导致写操作失败或者数据库无法正常工作(并不是插入100M数据，就是占用100M空间，它还会算上索引这些的资源，占用的内存只会更多)；

3.日志过大

在执行写操作时，MYQL会生成事务日志用于保证数据的一致性，如果写操作的数据量过大，事务日志也会变得非常大，可能会导致磁盘空间不足或者日志写入速度变慢；

4.死锁

如果多个客户端同时进行批量写操作，并且操作的数据范围有重，可能会导致死锁(多线程操作数据的时候一定要数据区分清楚，一旦有重叠，就容易导致死锁)；

5.数据库性能下降

如果批量写操作的负载太大，可能会导致数据库性能下降，查询响应时间变慢，甚至出现数据库崩溃的情况；

6.主从延迟(如果是高并发的主从架构中)

在MYSQL主从复制架构中，如果主服务器上发生了大量的写操作，从服务器需要读取和应用这些操作，就会导致主从延迟，特别地，如果从服务器在处理写操作的过程中遇到了锁冲突或者主服务器上的写操作太多，从服务器的复制进程可能会被阻塞，从而导致主从延迟。

采取的措施：

1)尽量减少批量写操作的数据量可以将大批量数据分批进行写入，以避免对系统的影响；

2)预留足够的磁盘空间和系统资源，以确保批量写操作能够正常执行；

3)优化数据库表结构和索引，以提高写操作的性能和效率；

4)使用事务进行批量写操作，以确保数据的一致性；

5)使用数据库中的队列(比如MQ)或者缓存机制，将批量写操作异步化处理(本质就是时间换空间)，以避免对系统的影响。