MySQL数据库面试题

fighting！899

已于 2023-07-06 16:00:10 修改

阅读量517

点赞数

文章标签：数据库 mysql 数据结构

于 2023-04-13 17:43:07 首次发布

本文链接：https://blog.csdn.net/weixin_71921932/article/details/130135531

版权

文章目录

一、什么是索引？

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

二、索引的作用

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

三、索引分类

普通索引：最基本的索引，没有任何的限制。
唯一索引：与普通索引一致，不同的就是索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。
主键索引：它是一种特殊的索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用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执行计划