MySQL数据库面试题

偏执的太偏执、 2024-03-23 11:51 73阅读 0赞

**文章目录**

一、什么是索引？

二、索引的作用

三、索引分类

四、索引原理

五、索引的优点

六、索引的缺点

七、索引操作

八、分析索引使用情况

九、哪些字段适合加索引？

十、哪些字段不适合加索引

十一、索引失效的几种场景

十二、联合索引最左匹配原则

十三、聚簇索引和非聚簇索引

十四、事务ACID四大要素

十五、什么是事务？

十六、事务的并发问题

十七、事务隔离性的作用

十八、mysql中锁的分类

十九、mysql中的几种连接查询

二十、mysql优化综合性考虑

--------------------

### 一、什么是索引？ ###

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

### 二、索引的作用 ###

索引可以提高数据库查询**效率**，降低查询的时间复杂度，提高数据库的性能和响应速度。

### 三、索引分类 ###

**普通索引**：最基本的索引，没有任何的限制。  
**唯一索引**：与普通索引一致，不同的就是索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。  
**主键索引**：它是一种特殊的索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary来约束。  
**联合索引**：联合索引又叫复合索引，多个字段上建立的索引，能够快速复合查询条件的检索。  
**全文索引**：老版本的MySQL自带的全文索引只能用于数据库引擎未MYISAM的数据表，新版本MySQL5.6的InnoDB支持全文索引  
**二级索引**：**除主键外**的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据，叶子节点还包含了主键值。

### 四、索引原理 ###

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

### 五、索引的优点 ###

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

### 六、索引的缺点 ###

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

###    七、索引操作 ###

**索引简单的增删改查**  
1.查询表中索引

show index from tableName;

2.创建索引

create index 索引名 on 表名（列名）;

3.删除索引

drop index IndexName on tableName;

### 八、分析索引使用情况 ###

1.explain 分析SQL语句，判断是否走了索引，SQL是否需要优化

2.show index命令查看索引情况

3.打开sql慢查询日志，可以定位超过多少秒的、查询时间比较慢的sql语句

4.使用性能分析工具来分析查询性能，例如mysqldumpslow、pt-query-digest等

### 九、哪些字段适合加索引？ ###

1.经常需要搜索的列上加索引

2.主键列上加索引可以确保列的唯一性

3.在表与表之间的连接条件上加上索引，可以加快速度查询的速度。

4.在经常需要排序（order by）,分组（group by）和distinct列上加索引，  
可以加快排序索引的时间。

### 十、哪些字段不适合加索引 ###

1.查询中很少使用到的列，不加索引，避免资源浪费  
2.很少数据的列也不应该建立索引。比如sex性别  
3.当修改的操作大于查询的操作

### 十一、索引失效的几种场景 ###

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

2.索引字段的值不能有null值，有null值会使该列索引失效

3.like模糊查询以%开头的

4.如果列类型是字符串，那一定要在条件中将数据使用**单引号**引用起来,否则不使用索引

SELECT * FROM users WHERE name = 'John';

在此示例中，name列是字符串类型的，查询条件中的'John'被引用起来，这样MySQL就会使用name列的索引来加速查询。如果不使用引号，查询条件将被视为一个表达式，MySQL将不会使用索引来加速查询。

### 十二、联合索引最左匹配原则 ###

1.在MySQL中，建立联合索引时，会遵循**最左前缀匹配**的原则  
2.这意味着在检索数据时，MySQL会从联合索引的最左边开始匹配，  
并且组合索引的第一个字段必须出现在查询条件中，这个索引才会被用到。  
示例1.建立一个(a, b, c)的联合索引，查询条件中包含a和b：-->用到索引

CREATE INDEX idx_abc ON table_name (a, b, c);
    SELECT * FROM table_name WHERE a = 1 AND b = 2;

示例2.建立一个(a, b, c)的联合索引，查询条件中只包含b：--->用不到索引

CREATE INDEX idx_abc ON table_name (a, b, c);
    SELECT * FROM table_name WHERE b = 2;

### 十三、聚簇索引和非聚簇索引 ###

InnoDB——聚簇索引：索引和数据放在一起存储

MyISAM——非聚簇索引：索引和数据分开存储

###    十四、事务ACID四大要素 ###

**1.原子性**：一个原子操作要么同时成功，要么同时失败  
**2.一致性**：事务的执行不能破坏数据库数据的完整性和一致性，事务执行前后数据的状态保持一致  
**3.隔离性**：在并发场景下，不同的事务并发操作相同的数据，每个数据有自己的完整的数据空间，事务之间相互隔离  
**4.持久性**：事务一旦提交，数据库的数据改变是永久的，即使数据库崩溃宕机，重启数据库后也能恢复数据

### 十五、什么是事务？ ###

事务就是一组操作，要么同时成功，要么同时失败。

### 十六、事务的并发问题 ###

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

### 十七、事务隔离性的作用 ###

就是保证数据的一致性、完整性。事务隔离级别越高，在并发下会产生的问题就越少。

###    十八、mysql中锁的分类 ###

按操作分  
1.读锁（共享锁）：其他的进程也可以进行读操作，但写操作会阻塞，所以称为共享锁

2.写锁（排它锁）：加了写锁，其他进程读和写操作都会进入阻塞状态

按粒度分  
3.表锁：开销小、加锁快，不会出现死锁；锁粒度大，锁冲突高，并发低  
加锁方式：

lock table tableName read; //读锁
    lock table tableName write; //写锁

解锁方式：

unlock tables;//释放全部锁

4.行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。  
加锁方式：

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

乐观锁：  
看待事情比较悲观， 认为别人会修改它的数据，需要上锁来保证数据的安全

select * from employee where id = 1 版本 = 1
    update employee set salary=2000,version=version+1 where id=1 and version = 1

###    十九、mysql中的几种连接查询 ###

内连接：表A与表B的交集

SELECT A.id, A.name, B.address
    FROM tableA A
    INNER JOIN tableB B
    ON A.id = B.id;

外连接：

left join：A表所有数据与B表匹配的数据
    
    right join：B表所有数据与A表匹配的数据
    
    full join：A表与B表不匹配的全部数据

###    二十、mysql优化综合性考虑 ###

**1.表的设计优化**：选择合适的存储引擎  
myisam: 不支持事务，低并发场景  
Innodb： 支持事务，高并发场景  
**2.索引优化**：  
表的主键、外键必须有索引；  
数据量大的表应该有索引；  
经常与其他表进行连接的表，在连接字段上应该建立索引；  
经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；  
索引应该建在选择性高的字段上； （sex 性别这种就不适合）  
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；  
频繁进行数据操作的表，不要建立太多的索引；  
删除无用的索引，避免对执行计划造成负面影响；  
表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；  
相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。  
**3、sql优化**：  
1.查询SQL尽量不要使用select \*，而是select具体字段  
2.如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1  
3.应尽量避免在where子句中使用or来连接条件  
4.优化limit分页  
5.优化你的like语句  
6.使用where条件限定要查询的数据，避免返回多余的行  
7.使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。  
8.对查询进行优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。  
9.在适当的时候，添加覆盖索引。  
10.不要有超过5个以上的表连接  
11.给表起别名  
12.explain分析sql执行计划

**4、缓存优化：**  
mysql也支持缓存，默认缓存设置是关闭，  
需要我们在mysql.ini 配置文件中开启:

**5、主从复制，读写分离**  
mysql主库负责增、改、删操作  
mysql从库复制查询操作

**6、分库分表**  
垂直拆分：（分库）  
业务表太多？ 将业务细化 不同的小业务专门用一个库来维护  
水平拆分：（分表）  
单个表存的数据太多，装不下了？ 将该表查分成多个