ssm

mysql

为什么选择B+树作为索引结构（必考）

Inodb存储引擎 默认是 B+Tree索引；MyISAM 存储引擎 默认是Fulltext索引；Memory 存储引擎 默认 Hash索引；

• B树：有序数组+平衡多叉树；
• B+树：有序数组链表+平衡多叉树；

B+树(叶节点保存数据，其他的节点 全部存放索引)，数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。 B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

正如上面所说，在数据库中基于范围的查询是非常频繁的，因此MySQL最终选择的索引结构是B+树而不是B树。

注：与其他数据结构的对比：

• Hash索引：Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描
• 二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表。
• 平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低。
• 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了 AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多。
• B+树：在B树的基础上，将非叶节点改造为不存储数据纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效。

MySQL常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

• INDEX（普通索引）：ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col’)最基本的索引，没有任何限制
• UNIQUE（唯一索引）：ALTER TABLE ‘table_name’ ADD UNIQUE(‘col’)，与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。
• PRIMARY KEY（主键索引）：ALTER TABLE ‘table_name’ ADD PRIMARY KEY(‘col’) 是一种特殊的唯一索引，不允许有空值。
• FULLTEXT（全文索引）：ALTER TABLE ‘table_name’ ADD FULLTEXT(‘col’)，仅可用于MyISAM和InoDB，针对较大的数据，生成全文索引很耗时耗空间
• 组合索引：ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’)

为了更多的提高mysql效率可建立组合索引，遵循“最左前缀”原则。创建复合索引应该将最常用（频率）做限制条件的列放在最左边，一次递减。组合索引最左字段用in是可以用到索引的。相当于建立了col1,col1col2,col1col2col3三个索引。

索引B+树的叶子节点都可以存哪些东西（或问聚簇索引与非聚簇索引的区别？）（必考）

可能存储的是整行数据，也有可能是主键的值。

B+树的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

• 聚簇索引

所谓聚簇索引，就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引。

• 非聚簇索引

非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的，主要用在MyISAM存储引擎中。非聚簇索引比聚簇索引多了一次读取数据的IO操作，所以查找性能上会差。

• 覆盖索引

指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

注意：
MySQL InnoDB一定会建立聚簇索引，把实际数据行和相关的键值保存在一块，这也决定了一个表只能有一个聚簇索引，即MySQL不会一次把数据行保存在二个地方。

• InnoDB通常根据主键值(primary key)进行聚簇
• 如果没有创建主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引
• 上面二个条件都不满足，InnoDB会自己创建一个虚拟的聚集索引
• 正因为InnoDB将数据保存在一处，因此其插入速度严重依赖插入顺序。按照主键顺序插入无疑是最快的。如果不是按照主键插入，建议加载完成后最好使用OPTIMIZE TABLE重新组织一下表。

补充问题：InnoDB一棵B+树可以存放多少行数据？

这个问题的简单回答是：约2千万

mysql 的内连接、左连接、右连接有什么区别？

内连接关键字：inner join；左连接：left join；右连接：right join。
内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

怎么验证 mysql 的索引是否满足需求？

使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。
explain 语法：explain select * from table where type=1。

说一下数据库的事务隔离？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：transaction-isolation = REPEATABLE-READ
可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。
READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。
脏读：一个事务读取到了另外一个事务没有提交的数据
不可重复读：在同一事务中，两次读取同一数据，得到内容不同
幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同
解决：读取数据时加共享锁，写数据时加排他锁，都是事务提交才释放锁

加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句

你们项目中选了哪个隔离级别？为什么？

Mysql默认的事务隔离级别是可重复读(Repeatable Read)，项目中一般用**读已提交(Read Commited)**这个隔离级别！

Mysql主从复制，是基于binlog复制的。binlog有3种格式？1.statement:记录的是修改SQL语句 2.row：记录的是每行实际数据的变更 3.mixed：statement和row模式的混合

为什么mysql用的是可重复读而不是读已提交？

在 5.0之前只有statement一种格式，而主从复制存在了大量的不一致，故选用可重复读

主从不一致性的问题！原因其实很简单，就是在master上执行的顺序为先删后插！而此时binlog为STATEMENT格式，它记录的顺序为先插后删！从(slave)同步的是binglog，因此从机执行的顺序和主机不一致！就会出现主从不一致！

解决方案有两种！

(1)隔离级别设为可重复读(Repeatable Read),在该隔离级别下引入间隙锁。当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住！
(2)将binglog的格式修改为row格式，此时是基于行的复制，自然就不会出现sql执行顺序不一样的问题！奈何这个格式在mysql5.1版本开始才引入。因此由于历史原因，mysql将默认的隔离级别设为可重复读，保证主从复制不出问题！

为什么项目中选了读已提交(Read Commited)隔离级别？

项目中是不用**读未提交(Read UnCommitted)和串行化(Serializable)**两个隔离级别，原因有二

• 采用读未提交(Read UnCommitted),一个事务读到另一个事务未提交读数据，这个不用多说吧，从逻辑上都说不过去！
• 采用串行化(Serializable)，每个次读操作都会加锁，快照读失效，一般是使用mysql自带分布式事务功能时才使用该隔离级别！(笔者从未用过mysql自带的这个功能，因为这是XA事务，是强一致性事务，性能不佳！互联网的分布式方案，多采用最终一致性的事务解决方案！)

可重复读，简称为RR；读已提交，简称为RC；

缘由一：在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！

缘由二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行

缘由三：在RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性！

​ 在5.1.15的时候，innodb引入了一个概念叫做“semi-consistent”，减少了更新同一行记录时的冲突，减少锁等待。
​ 所谓半一致性读就是，一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)！

说一下 mysql 常用的引擎？

InnoDB 引擎：InnoDB 引擎提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count() from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。
MyIASM 引擎：MySQL 的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

说一下 mysql 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

mysql 问题排查都有哪些手段？

使用 show processlist 命令查看当前所有连接信息。
使用 explain 命令查询 SQL 语句执行计划。
开启慢查询日志，查看慢查询的 SQL。

如何做 mysql 的性能优化？

添加适当的索引（普通索引、主键索引、唯一索引、全文索引）；
避免使用 select *，列出需要查询的字段。
选择正确的存储引擎。
库表优化，表设计合理化，符合三大范式；
分库分表；读写分离等；
sql语句优化，定位执行效率低，慢sql的语句，通过explain分析低效率的原因；

索引的优缺点，什么字段上建立索引

优点方面：第一，通过创建唯一索引可以保证数据的唯一性；第二，可以大大加快数据的检索速度，是主要目的；第三；在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间；第四，可以在查询中使用优化隐藏器，提高系统的性能；　　
缺点方面：第一，创建索引和维护索引要耗费时间，并且随着数据量的增加而增加；第二，每一个索引需要占用额外的物理空间，需要的磁盘开销更大；第三，当对表中的数据进行增加、删除、修改操作时，索引也要动态维护，降低了数据的维护速度；　　
一般来说，在经常需要搜索的列上，强制该列的唯一性和组织表中数据的排列结构的列，在经常用在链接的列上，在经常需要排序的列上，在经常使用在where字句的列上可以添加索引，以提升查询速度；同样，对于一些甚少使用或者参考的列，只有很少数值的列（如性别），定义为text,image,bit的列，修改性能远远大于检索性能的列不适合添加索引；

什么情况下索引会失效？

• 1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
• 2.对于多列索引，不是使用的第一部分，则不会使用索引
• 3.like查询是以%开头
• 4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

sql