mysql常见面试题合集

骑猪看日落 2022-12-17 05:19 159阅读 0赞

**什么是索引**

数据库索引的本质是数据结构，这种数据结构能够帮助我们快速的获取数据库中的数据。

**索引的作用**

当表中的数据量越来越大时，索引对于性能的影响愈发重要。
    索引优化应该是对查询性能优化最有效的手段了。
    索引能够轻易将查询性能提高好几个数量级。
    有了索引相当于我们给数据库的数据加了目录一样，
    可以快速的找到数据，如果不适用索引则需要一点一点去查找数据
    简单来说
    提高数据查询的效率。

**索引的分类**

- 1.普通索引index :加速查找
    - 2.唯一索引
    - 3.联合索引（组合索引）
    - 4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。

**索引原理**

索引的实现本质上是为了让数据库能够快速查找数据，而单独维护的数据结构，
    mysql实现索引主要使用的两种数据结构：
    hash和B+树： 我们比较常用的 MyIsam 和 innoDB引擎都是基于B+树的。
    
    hash:（hash索引在mysql比较少用）他以把数据的索引以hash形式组织起来,
    因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,
    每个键只对应一个值,而且是散列的方式分布.
    所以他并不支持范围查找和排序等功能.
    
    B+树:b+tree是(mysql使用最频繁的一个索引数据结构)数据结构以平衡树的形式来组织,
    因为是树型结构,所以更适合用来处理排序,范围查找等功能.
    相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,
    但是因为更适合排序等操作,所以他更受用户的欢迎.
    毕竟不可能只对数据库进行单条记录的操作.

**索引的优点**

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
    2.建立索引可以大大提高检索的数据,以及减少表的检索行数
    3.在表连接的连接条件 可以加速表与表直接的相连
    4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
    5.建立索引,在查询中使用索引 可以提高性能

**索引的缺点**

1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
    2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,
    每一个索引还会占用一定的物理空间
    3.当对表的数据进行INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,
    这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。
    一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，
    索引文件的会膨胀很快)。

**索引操作**

对索引的简单增删改查语句要记得
    查看表中索引
    show index from tableName;
    创建索引
    CREATE INDEX 索引名 ON 表名 列名;
    删除索引
    DORP INDEX IndexName ON TableName
    分析索引使用情况
    explain select 语句

**分析索引使用情况**

explain显示了MySQL如何使用索引来处理select语句以及连接表。
    可以帮助选择更好的索引和写出更优化的查询语句。简单讲，
    它的作用就是分析查询性能。explain关键字的使用方法很简单，
    就是把它放在select查询语句的前面。mysql查看是否使用索引，
    简单的看type类型就可以。如果它是all，那说明这条查询语句遍历了所有的行，
    并没有使用到索引。 (最简单的说法，希望能说详细些)

**哪些字段适合加索引**

1.在经常需要搜索的列上,可以加快索引的速度
    2.主键列上可以确保列的唯一性
    3.在表与表的而连接条件上加上索引,可以加快连接查询的速度
    4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间,

**哪些字段不适合加索引**

1.查询中很少使用到的列 不应该创建索引,
    如果建立了索引然而还会降低mysql的性能和增大了空间需求.
    2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,
    在查询中,结果集的数据占了表中数据行的比例比较大,
    mysql需要扫描的行数很多,增加索引,并不能提高效率
    3.定义为text和image和bit数据类型的列不应该增加索引,
    4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系。

**哪些情况会造成索引失效**

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
    2.索引字段的值不能有null值，有null值会使该列索引失效
    3.对于多列索引，不是使用的第一部分，则不会使用索引（最左原则）
    4.like查询以%开头
    5.如果列类型是字符串，那一定要在条件中将数据使用单引号引用起来,
    否则不使用索引
    6.在索引的列上使用表达式或者函数会使索引失效

**联合索引最左原则**

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，
    在检索数据时从联合索引的最左边开始匹配，
    组合索引的第一个字段必须出现在查询组句中，
    这个索引才会被用到
    如创建组合索引 a,b,c     那么查询条件中单纯的使用  b 和 c是使用不到索引的

**聚簇索引和非聚簇索引**

MyISAM——非聚簇索引
    MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
    非聚簇索引的数据表和索引表是分开存储的。
    
    
    InnoDB——聚簇索引
    聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
    聚簇索引的数据和主键索引存储在一起。

**事务的基本要素（ACID）**

（1）原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，
    不可能停滞在中间某个环节。事务在执行过程中发生错误，
    会被回滚到事务开始前的状态，就像这个事务从来没发生过一样。
    例如：A账户中有1000元，B账户中有1000元。A要给B转账500元。
    A扣款和B加款这两条要么同时执行，要么同时不执行。
    如果在A扣款后B加款之前，系统发生故障，会回滚到A扣款之前的状态。
    （2）一致性：事务开始之前和事务结束后，数据库的完整性约束没有被破坏。
    例如：不论汇款成败，A账户B账户总额是2000元。
    （3）隔离性：事务的执行互不干扰。
    （4）持久性：事务执行成功后，该事务对数据库的更改是持久保存在数据库中的，不会被回滚。
    可以使用日志记录或影子副本来实现。

**什么是事务？**

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组
    如果任何一个语句操作失败那么整个操作就被失败，
    以后操作就会回滚到操作前状态，或者是上有个节点。
    为了确保要么执行，要么不执行，就可以使用事务。
    要将有组语句作为事务考虑，就需要通过ACID测试：
    即原子性，一致性，隔离性和持久性。
    
    - 锁：锁是实现事务的关键，锁可以保证事务的完整性和并发性。
    -  与现实生活中锁一样，它可以使某些数据的拥有者，
    - 在某段时间内不能使用某些数据或数据结构。
    老李 给 老王汇钱
    老李把钱 ==》 银行 老李账户扣钱
    银行 ==》 老王 成功 老王账户加钱
    不成功 老账户补钱（银行将钱返给老李）

**事务的并发问题**

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，
    那么A读取到的数据是脏数据
    2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，
    对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
    3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级
    ，但是系统管理员B就在这个时候插入了一条具体分数的记录，
    当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，
    这就叫幻读。

**事务隔离性的作用**

就是保证数据的一致性、完整性。
    事务隔离级别越高，在并发下会产生的问题就越少，
    但同时付出的性能消耗也将越大，因此很多时候必须在并发性和性能之间做一个权衡。
    所以设立了几种事务隔离级别，以便让不同的项目可以根据自己项目的并发情况选择合适的事务隔离级别，对于在事务隔离级别之外会产生的并发问题，在代码中做补偿。

**事务的隔离级别4个**

<table> 
 <thead> 
  <tr> 
   <th>事务隔离级别</th> 
   <th>读未提交</th> 
   <th>读已提交</th> 
   <th>可重复读</th> 
   <th>串行化</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>脏读</td> 
   <td>是</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>--------</td> 
   <td>--------</td> 
   <td>--------</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>不可重复读</td> 
   <td>是</td> 
   <td>是</td> 
   <td>否</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>--------</td> 
   <td>--------</td> 
   <td>--------</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>幻读</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

**mysql中锁的分类**

按操作分
    	读锁(共享锁)
    		加了读锁,   其他的进程也可以进行读操作，但写操作会阻塞，所以称为共享锁
    	写锁(排它锁)
    		加了写锁，  其他的进程读操作和写操作都会进入阻塞状态	
    按粒度分
    	表锁
    		加锁特点：开销小、加锁快，不会出现死锁；锁粒度大，锁冲突高，并发低
    		加锁方式：
    			lock table tableName read; //读锁
    			lock table tableName write; //写锁
    		解锁方式:
    			unlock tables;//释放全部锁
    	行锁
    		开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 
    		加锁方式：
    			select * from table where id=1 lock in share mode; //读锁
    			select * from table where id=1 for update; //写锁
    			
    		解锁方式:
            	commit; //提交事务即解锁
    	页锁
    		介于上面两个之间，不用特意阐述
    从思想的层面:
    	悲观锁：
    	看待事情比较悲观， 认为别人会修改它的数据，需要上锁来保证数据的安全
    	select * from employee where id = 1 for update
    	update -- 
    	乐观锁：
    	看待事情比较乐观， 
    	
    	id   name  salary version 
    	1     老王   500     1
    	
       客户端1   
       select * from employee where id = 1    版本 = 1   
       update employee set salary=1000,version=version+1 where id=1 and version = 1
       
       
       客户端2   
       select * from employee where id = 1    版本 = 1  2
       update employee set salary=1000,version=version+1 where id=1 and version = 1
       
       
       读操作多 选乐观锁
       写操作多 选悲观锁

**mysql中的几种连接查询**

内连接：只有两个元素表相匹配的才能在结果集中显示。
    	inner join
    外连接：
        左外连接:左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
        	left join
    	右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
    		right join
    	全外连接：连接的表中不匹配的数据全部会显示出来。
    	 	full join

**sql的书写顺序和执行顺序**

-- 编写顺序
    select  distinct  查询字段
    from  表名
    JOIN 表名
    ON  连接条件
    where 查询条件
    group by 分组字段
    having 分组后条件
    order by  排序条件
    limit 查询起始位置, 查询条数
    
    -- 执行顺序
    from  表名
    ON  连接条件
    JOIN 表名
    where 查询条件
    group by 分组字段
    having 分组后条件
    select  distinct  查询字段
    order by  排序条件
    limit 查询起始位置, 查询条数

**mysql优化综合性**

1.表的设计优化
    选择表合适存储引擎：
    myisam: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。
    Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。
    尽量 设计 所有字段都得有默认值,尽量避免null。
    数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型.
    
    但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。
    tinyint   int   bigint
    因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，
    在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，
     CHAR (255)            VARCHAR (255)    10  
     定长                   可变长度
     
     
     
     
    100101
    甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，
    我们应该使用TINYINT而不是BIGINT来定义整型字段。
    应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
    对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理， 而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
    
    
    
    2.索引优化
    表的主键、外键必须有索引；
    数据量大的表应该有索引；
    经常与其他表进行连接的表，在连接字段上应该建立索引；
    经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
    索引应该建在选择性高的字段上； （sex 性别这种就不适合）
    索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
    频繁进行数据操作的表，不要建立太多的索引；
    删除无用的索引，避免对执行计划造成负面影响；
    表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
    
    
    3.sql语句优化
    SELECT语句务必指明字段名称（避免直接使用select * ）
    SQL语句要避免造成索引失效的写法
    SQL语句中IN包含的值不应过多
    当只需要一条数据的时候，使用limit 1
    如果排序字段没有用到索引，就尽量少排序
    如果限制条件中其他字段没有索引，尽量少用or
    尽量用union all代替union
    避免在where子句中对字段进行null值判断
    不建议使用%前缀模糊查询
    避免在where子句中对字段进行表达式操作
    Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要已小表为驱动
    
    
    4.缓存优化 (数据库自身缓存  redis缓存 等等 )
    为了提高查询速度，我们可以通过不同的方式去缓存我们的结果从而提高响应效率。
    
    数据库本身也是支持缓存的 --> 查询缓存query_cache , 默认查询缓存是关闭的
    需要我们在mysql.ini 配置文件中开启:
    开启方法： 
    query_cache_type=0   #关闭查询缓存
    query_cache_type=1   #开启查询缓存,mysql自动帮我们缓存满足条件的查询数据
    query_cache_type=2   #开启查询缓存,需要在参数中手动指定要缓存的查询
    
    不过因为我们的课程体系主要讲解的是redis,所以在这里可以引入redis的知识点。
    
    
    
    5.主从复制、读写分离
    如果数据库的使用场景读的操作比较的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构，读写分离，解决的是，数据库的写入，影响了查询的效率。读写分离的基本原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
    参考: https://www.jianshu.com/p/faf0127f1cb2
    
    
    6.mysql的分库分表   
    数据量越来越大时，单体数据库无法满足要求，可以考虑分库分表
    两种拆分方案：
    垂直拆分：（分库）
    业务表太多？ 将业务细化 不同的小业务专门用一个库来维护
    水平拆分：（分表）
    单个表存的数据太多，装不下了？ 将该表查分成多个
    
    分库分表经常使用的数据库中间件：
    MyCat
    https://www.cnblogs.com/chongaizhen/p/11083226.html
    Sharding-JDBC
    https://blog.csdn.net/forezp/article/details/94343671