面试题 数据库 MYSQL

1，mysql聚簇索引和非聚簇索引的区别

区别一：
聚集索引：就是以主键创建的索引，在叶子节点存储的是表中的数据。
非聚集索引：就是以非主键创建的索引(也叫做二级索引)，在叶子节点存储的是主键和索引列。
区别二：
聚集索引中表记录的排列顺序和索引的排列顺序一致；所以查询效率快，因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。
非聚集索引中表记录的排列顺序和索引的排列顺序不一致。
区别三：
聚集索引是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储不连续。
区别四：
聚集索引每张表只能有一个，非聚集索引可以有多个。

2，mysql索引结构，各自的优势

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。
B+树：
B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。
哈希索引：
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。

3，索引设计的原则

查询更快、占用空间更小
索引设计实践：
1.适合索引的列是出现在where子句的列，或者连接子句的列。
2.基数较小的表，索引效果较差，没有必要建立索引
3.选用短索引，如果对长字符串进行索引，应该指定一个前缀长度，这样能够节省大量的索引空间，如果搜索词长过索引前缀长度，则先使用索引排除不匹配的行，然后检查其余行是否匹配
4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在进行修改操作的时候，索引会进行更新甚至重构，索引列越多，这个消耗的时间就越长，所以只建立需要的索引。
5.定义有外键的数据列一定要定义索引
6.频繁更新的列不适合建立索引
7.区分度不高的列不适合做索引，比如性别
8.尽量扩展索引而不是新建索引，比如表中已有a索引，现在要添加(a,b)的索引，那么就修改原来的a索引即可
9.对于查询中很少涉及的列，重复值比较多的列不要建立索引
10.对于定义为text，image和bit的数据类型的列不要建立索引

4，mysql锁的类型有哪些

1，基于锁的属性分类：共享锁、排他锁
共享锁
又称为读锁，S锁，当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有读锁释放之后，其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免重复读取的问题。
排他锁
排他锁又称写锁，简称X锁，当一个事务为数据加上写锁的时候，其他事务不能对其加上任何锁。直到排他锁释放后，才能对数据进行加锁。排他锁的目的是在数据修改的时候，不允许其他人读取和修改，避免了脏数据的问题。
2，基于锁的粒度分类：行级锁（INNODB）、表级锁（INNODB、MYISAM）、页级锁（BDB引擎）、记录锁、间隙锁、临键锁。
表锁
对整个表进行上锁，当下一个事务访问该表的时候，必须等待上一个事务释放了表锁，才能对其表进行访问。不会出现死锁。
粒度大、加锁简单、容易冲突。
页锁
页锁介于表所和行锁之间，表锁速度快，但是冲突多，行锁冲突少，但是速度慢。一次锁定一组数据，并发一般，会出现死锁。
行锁
锁住的是表中的一行或者多行记录，其他事务访问同一张表的时候，只有被锁住的记录不能访问，其他的记录可以正常访问。会出现死锁。
粒度小，加锁比表锁困难，不易冲突，支持更高的并发。
记录锁
行锁的一种，和行锁的不同是，只能锁某一行的记录，属于精准命中，命中字段为唯一索引。
粒度更小，加锁更困难，不易冲突，支持更高的并发。
间隙锁
对于键值在条件范围内但不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这些“间隙”进⾏加锁,这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。
临键锁
也是行锁的一种，总结说就是记录锁和间隙锁的组合，临键锁会把查询出的记录锁住，并且把范围查询内的所有间隙也锁住。
3，基于表的状态分类：意向共享锁、意向排他锁。
意向共享锁
当一个事务试图对整个表进行加共享锁之前，首先需要获取这个表的意向共享锁。
意向排他锁
当一个事务试图对整个表进行加排他锁之前，需要先获得这个表的意向排他锁。
当加了这样一个状态，就相当于，告诉其他事务，我已经对整个表进行了共享锁会这我排他锁。避免了对整个索引的每个节点扫描是否加锁，而这个状态就是意向锁。

5，mysql执行计划怎么看

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数
EXPLAIN SELECT * FROM admin WHERE id > 1

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

• 1，id：是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按select出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。
• 2，selectType表示查询中每个select子句的类型：
  • SIMPLE： 表示此查询不包含 UNION 查询或子查询
  • PRIMARY： 表示此查询是最外层的查询（包含子查询）
  • SUBQUERY： 子查询中的第一个 SELECT
  • UNION： 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查 询的结果.
  • DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）
• 3，table：表示该语句查询的表
• 4，type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：
  • const：通过索引一次命中，匹配一行数据
  • system: 表中只有一行记录，相当于系统表；
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
  • ref: 非唯一性索引扫描,返回匹配某个值的所有
  • range: 只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
  • index: 只遍历索引树；
  • ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。 那么基本就是随着表的数量增多， 执行效率越慢。

执行效率：ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index

• 5，possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。
• 6，key：此字段是 mysql 在当前查询时所真正使用到的索引。 他是possible_keys的子集
• 7，key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标
• 8，rows：mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大
• 9，filtered：返回结果的行占需要读到的行(rows列的值)的百分比，就是百分比越高，说明需要查询数据越准确， 百分比越小，说明查询到的数据量大，而结果集很少
• 10，extra
  • using filesort ：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大，延时大。
  • using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
  • using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况，查询效率不高，建议优化。
  • using where ：sql使用了where过滤,效率较高。

6，事务的基本特性和隔离级别

一，事务的基本特性ACID分别是：

• 原子性：指的是一个事务中的操作要么全部成功，要么全部失败。
• 一致性：指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态(事务最终的目的)。
• 隔离性：指的是一个事务修改在最终提交前，对其他事务是不可见的。
  隔离解决方法
• 持久性：指的是一旦事务提交，所做的修改就会永久保存到数据库中。

二，数据库的隔离级别
隔离性有四个隔离级别，分别是：
读未提交(read uncommit)：可能会读到其他事务未提交的数据，也叫做脏读。
读已提交(read commit)：两次读取的结果不一致，叫做不可重复读(加个读锁可以解决)
可重复读(repeatable read)：这是MySQL的默认级别，就是每次读取结果都一样，但是有可能产生幻读
串行(serializable)：一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题

7，索引查询失效的几个情况

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
2、or语句前后没有同时使用索引。
3、组合索引，不是使用第一列索引，索引失效。
4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
5、在索引字段上使用not，<>，!=。
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
6、对索引字段进行计算操作、字段上使用函数。
7、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
如果mysql估计使用全表扫描要比使用索引快,则不使用索引

8，怎么处理慢查询

1、开启慢查询日志，准确定位到哪个sql语句出现了问题。
2、分析sql语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
3、分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
4、如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。
优化语句：
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

9，ACID靠什么保证的

原子性，由 undolog 日志来保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql。
一致性，是由其他三大特性保证，程序代码要保证业务上的一致性。
隔离性，是由 MVCC 来保证。
持久性，由 redolog 来保证，mysql 修改数据的时候会在 redolog 中记录一份日志数据，就算数据没有保存成功，只要日志保存成功了，数据仍然不会丢失。

10，什么是MVCC

MVCC，全称Multi-Version Concurrentcy Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为Mysql实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志，readVIew。（多版本并发控制，它是通过读取历史版本的数据，来降低并发事务冲突，从而提高并发性能的一种机制）
1，事务版本号
2，表的隐藏列
记录中的隐藏字段含义
DB_TRX_ID最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段
3，undo log
回滚日志，在insert，update，delete的时候产生的便于数据回滚的日志。当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。
4，read view
ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
ReadView中包含了四个核心字段：
字段含义
m_ids当前活跃的事务ID集合
min_trx_id最小活跃事务ID
max_trx_id预分配事务ID，当前最大事务ID+1
creator_trx_idReadView创建者的事务ID

11，mysql主从同步原理

1、什么是mysql的主从复制？
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
2、mysql复制原理
（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。
具体步骤：
1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave
2、从库的IO线程和主库的dump线程建立连接。
3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。
4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。
5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到http://master.info中
6、从库SQL线程应用relay-log，并且把应用过的记录到http://relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge
3、mysql主从形式
（一）一主一从
（二）主主复制
（三）一主多从
（四）多主一从
（五）联级复制
4、mysql主从同步延时分析
mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高，slave的sql thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多，另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。
解决方案：
1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
4.不同业务的mysql物理上放在不同机器，分散压力。
5.使用比主库更好的硬件设备作为slave，mysql压力小，延迟自然会变小。
6.使用更加强劲的硬件设备、

12，简述Myisam和Innodb的区别

1、InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2、InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3、InnoDB是聚簇索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚簇索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
4、InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
那么为什么InnoDB没有了这个变量呢？
因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
5、Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
6、MyISAM表格可以被压缩后进行查询操作
7、InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
例如：
t_user(uid, uname, age, sex) innodb;
uid PK 无其他索引
update t_user set age=10 where uid=1; 命中索引，行锁。
update t_user set age=10 where uid != 1; 未命中索引，表锁。
update t_user set age=10 where name=‘chackca’; 无索引，表锁。
8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
如何选择：
1，是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2，如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3，系统奔溃后，MyISAM恢复起来更困难，能否接受；
4，MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。
InnoDB为什么推荐使用自增ID作为主键？
答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。
innodb引擎的4大特性
插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

13，简述mysql中索引类型及对数据库的性能的影响

普通索引：
允许被索引的数据列包含重复的值。
唯⼀索引：
可以保证数据记录的唯⼀性 。
联合索引：
索引可以 覆盖多个数据列 ，如像INDEX(columnA, columnB)索引。
全文索引：
通过建⽴ 倒排索引 ,可以极⼤的提升检索效率,解决判断字段是否包含的问题，是⽬前搜索引擎使⽤的⼀种关键技术。可以通过 ALTER TABLE table_name ADD FULLTEXT (column) ;创建全⽂索引
主键：
是⼀种特殊的唯⼀索引，在⼀张表中只能定义⼀个主键索引，主键⽤于唯⼀标识⼀条记录，使⽤关键字 PRIMARY KEY 来创建。
索引的作用：
索引可以极⼤的提⾼数据的查询速度。
通过使⽤索引，可以在查询的过程中，使⽤优化隐藏器，提⾼系统的性能。
但是会降低插⼊、删除、更新表的速度，因为在执⾏这些写操作时，还要操作索引⽂件
索引需要占 物理空间 ，除了数据表占数据空间之外，每⼀个索引还要占⼀定的物理空间，如果要建⽴聚簇索引，那么需要的空间就会更⼤，如果⾮聚集索引很多，⼀旦聚集索引改变，那么所有⾮聚集索引都会跟着变。
在一个表中，主键索引只能有一个，唯一索引可以有多个。
主键索引（PRIMARY KEY）：
唯一的标识，主键不可以重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）：
避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（KEY/INDEX）：
默认的，index，key关键字来设置
全文索引（FullText）：
在特定的数据库引擎下才有，MyISAM
快速定位数据

14，B+tree 与 B-tree区别

原理:分批次的将磁盘块加载进内存中进行检索,若查到数据,则直接返回,若查不到,则释放内存,并重新加载同等数据量的索引进内存,重新遍历
B+Tree 结构: 数据 向下的指针 指向数据的指针
特点:
1，节点排序
2 .一个节点可以存多个元索，多个元索也排序了
B-Tree 结构: 数据 向下的指针
特点:
1.拥有B树的特点
2.叶子节点之间有指针
3.非叶子节点上的元素在叶子节点上都冗余了，也就是叶子节点中存储了所有的元素，并且排好顺序
从结构上看,B+Tree 相较于 B-Tree 而言 缺少了指向数据的指针；
Mysq|索引使用的是B+树，因为索引是用来加快查询的，而B+树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元素，从而可以使得B+树的高度不会太高，在Mysql中一个Innodb页就是一个B+树节点，一个Innodb页默认16kb，所以一般情况下一颗两层的B+树可以存2000万行左右的数据，然后通过利用B+树叶子节点存储了所有数据并且进行了排序，并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等SQL语句