MySQL数据库常考的面试题总结

一、MySQL的优化策略 ​​有哪些？

• 最大连接数：通过修改MySQL的最大连接数，让MySQL支持更多的并发请求

• 查询缓存：启用MySQL自带的查询缓存，以提升查询操作的效率，但是可以使用redis来当缓存使用。

• 引擎优化：根据对数据表的访问特性使用不同的SQL引擎（写操作频繁使用InnorDB，读操作频繁则使用MyISAM）

• 索引：对于数据量比较大的数据表，建立索引，以提升查询效率（先查询索引，再根据索引找数据）；但是对数据表的添加和删除操作，需要同步修改索引，因此数据量比较小并且插入、删除操作频繁的表，无需创建索引

• SQL优化：编写SQL时遵守一些约定的习惯，避免引起数据表的全表扫描

二、索引的分类、索引的创建、索引的查看、索引的数据结构分别是怎么实现的？

索引定义：提取数据表某一列或某几列的数据，构建起的一个便于进行数据查找、提升查找效率的B+树结构。

索引分类

• 主键索引：主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键，在创建表的时候指定了主键，则会自动创建主键列的索引。

• 唯一索引：索引列的所有值都只能出现一次，即必须唯一，值可以为空。 unique

• 普通索引：基本的索引类型，值可以为空，没有唯一性的限制。

• 全文索引：全文索引的索引类型为FULLTEXT。

  • 全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。

  • 对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

  • 全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技术处理中文。

  • alter table 表名 add FULLTEXT(字段名);

• 创建索引:为数据表的某个字段创建索引

  • • 主键索引：设置为主键默认创建

    • 普通索引（单字段）   
      • create index 索引名称 on 表名(字段名)

    • 普通索引（多字段）

        create index 索引名称 on 表名(字段1,字段2)

    • 唯一索引

        create unique index 索引名称 on 表名(字段名)

    • 全文索引

        create fulltext index 索引名称 on 表名(字段名)

    • 查看索引

      show create table <table_name>\G;
      show indexes from <table_name>;
      show keys from <table_name>;

    • 测试索引

      explain select * from 表名 where 条件 \G;

    • 删除索引

      drop index <index_name> on 表名;

索引的原理

• 传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍

• 在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅提升查询效率)，找到相应的键从而获取数据

• 索引的代价

  • 创建索引是会产生索引文件的，占用磁盘空间

  • 索引文件是一个B+树类型的文件，我们的DML操作同样也会对索引文件进行修改，所以性能会下降

• 在哪些column上使用索引？

  • 较频繁的作为查询条件的字段应该创建索引

  • 唯一性太差的字段不适合创建索引（例如gender性别字段，尽管频繁作为查询条件）

  • 更新非常频繁的字段不适合作为索引

  • 不会出现在where子句中的字段不该创建索引

三、常用的SQL优化有哪些？

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

• 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

• 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以合并查询这样查询： select id from t where num=10 union all select id from t where num=20

• 下面的查询也将导致全表扫描： select id from t where name like '%abc%' 若要提高效率，可以考虑全文检索。

• in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3

• 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num

• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100 应改为: select id from t where num=100*2

• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
  如： select id from t where substring(name,1,3)='abc'
  --name以abc开头的id
  select id from t where datediff(day,createdate,'2005-11-30')=0
  --'2005-11-30'生成的id 应改为:
  select id from t where name like 'abc%'
  select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

• 不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(...)

• 很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)

• 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

• 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

• 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

• 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

• 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

• 避免频繁创建和删除临时表，以减少系统表资源的消耗。

• 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

• 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

• 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

• 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

• 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

• 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

• 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

• 尽量避免大事务操作，提高系统并发能力。

四、explain关键字的作用是什么？

`EXPLAIN`是MySQL中的关键字，用于查询执行计划，它可以帮助我们分析和优化查询语句的性能。

当我们执行一条SQL查询语句时，MySQL会对该查询进行优化和执行。`EXPLAIN`关键字可以让我们查看MySQL对查询语句的执行计划，包括查询的顺序、使用的索引、连接方式等信息，从而帮助我们理解查询的执行过程和性能瓶颈。

使用`EXPLAIN`关键字的语法如下：

EXPLAIN SELECT * FROM table_name WHERE condition;

执行上述语句后，MySQL会返回一张表格，其中包含了查询的执行计划的详细信息。常见的字段包括：

- `id`：查询的标识符，每个查询都有一个唯一的标识符。
- `select_type`：查询的类型，常见的类型有`SIMPLE`、`PRIMARY`、`SUBQUERY`等。
- `table`：查询的表名。
- `type`：访问表的方式，常见的类型有`ALL`、`index`、`range`等。
- `possible_keys`：可能使用的索引。
- `key`：实际使用的索引。
- `key_len`：使用的索引的长度。
- `ref`：哪个列或常量与索引一起使用。
- `rows`：估计的要检查的行数。
- `Extra`：其他的一些附加信息，如`Using filesort`、`Using temporary`等。

通过分析`EXPLAIN`的输出，我们可以判断查询语句是否使用了合适的索引，是否存在全表扫描的情况，是否存在性能瓶颈等。根据这些信息，我们可以进行相应的优化，例如创建合适的索引、调整查询语句的顺序、优化连接方式等，以提高查询的性能和效率。

五、常见的导致索引失效的情况有哪些？

1、在 where 子句中使用!=或<>操作符。

2、在 where 子句中对字段进行 null 值判断。

3、在 where 子句中使用 or 来连接条件。

4、like，in，not  in关键字的使用也有可能导致索引失效。

六、mysql的锁的具体特性是什么？

MySQL中的锁是用于控制并发访问的机制，它可以保证多个并发事务之间的数据一致性和完整性。

MySQL中的锁可以分为两类：共享锁（Shared Lock）和排他锁（Exclusive Lock）。

1. 共享锁（Shared Lock）：
   - 多个事务可以同时持有共享锁，用于读取数据。
   - 共享锁之间不互斥，即多个事务可以同时持有共享锁。
   - 共享锁与共享锁之间不会产生冲突。
   - 如果一个事务持有共享锁，则其他事务只能获取共享锁，不能获取排他锁。

2. 排他锁（Exclusive Lock）：
   - 一个事务持有排他锁时，其他事务不能同时持有共享锁或排他锁。
   - 排他锁与排他锁之间互斥，即同一时刻只能有一个事务持有排他锁。
   - 如果一个事务持有排他锁，则其他事务不能获取共享锁或排他锁。

MySQL中的锁还具有以下特性：

- 锁的粒度：MySQL中的锁可以是行级锁、表级锁或页面级锁。行级锁的粒度最细，可以最大程度地提高并发性，但也会增加系统开销。
- 锁的隔离级别：MySQL支持多个事务并发执行，而事务之间可能存在读取脏数据、不可重复读、幻读等问题。为了解决这些问题，MySQL定义了四个隔离级别：读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。
- 锁的自动释放：MySQL中的锁可以自动释放。当事务提交或回滚时，MySQL会自动释放该事务持有的锁，以避免锁的长时间占用。
- 死锁检测：MySQL具有死锁检测机制，当发生死锁时，MySQL会自动检测并选择一个事务进行回滚，以解除死锁。

使用锁可以保证数据的一致性和完整性，但同时也会带来一定的性能开销。因此，在设计数据库和编写应用程序时，需要合理使用锁，并根据实际情况选择合适的锁粒度和隔离级别，以平衡并发性能和数据一致性的需求。

七、mysql的存储类型有哪些，innodb和myisam存储引擎的区别是什么？

数据库引擎

• 数据库引擎是用于存储、处理和保护数据的核心服务。

• 利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。

• 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

InnoDB引擎

• InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁和外键，InnoDB是默认的MySQL引擎。

• InnoDB主要特性有：

  • 1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。

  • 2、InnoDB是为处理巨大数据量的最大性能设计

  • 3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

  • 4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键.

MyISAM引擎

• MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

• MyISAM主要特性有：

  • 1、支持大文件（达到63位文件长度）

  • 2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。

  • 3、每个MyISAM表最大索引数是64，每个索引最大的列数是16

  • 4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上

  • 5、BLOB和TEXT列可以被索引

  • 6、NULL被允许在索引的列中，这个值占每个键的0~1个字节

  • 7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩

  • 8、MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

  • 9、可以把数据文件和索引文件放在不同目录

  • 10、每个字符列可以有不同的字符集

  • 11、有VARCHAR的表可以固定或动态记录长度

  • 12、VARCHAR和CHAR列可以多达64KB

其他引擎

• BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

• Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

• Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

• Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

• Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

• Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

• CSV：引用由逗号隔开的用作数据库表的文件

• Blackhole：用于临时禁止对数据库的应用程序输入

• Example引擎：可为快速创建定制的插件式存储引擎提供帮助

引擎的查看与修改

• 查看表引擎：

  show create table tablename；
  SHOW TABLE STATUS FROM sites WHERE NAME='site';  
  SHOW TABLE STATUS FROM db_name WHERE NAME='table_name';  

• 修改表引擎：

  alter table table_name engine=innodb;  
  alter table table_name engine=myisam;  

InnoDB和MyISAM的区别如下：

InnoDB

• 特点：1.支持事务；2.支持行锁；3.支持外键

• 数据库InnoDB锁机制是需要运行在事务中的

• 行锁

  • 读锁\乐观锁：允许其他事务查询数据，但不允许修改数据

    select column from table where conditions lock in share mode；

  • 写锁\悲观锁：不允许其他事务查询及修改数据

    select math from zje where math >60 for update；

• 总结：

  1. 行锁只有InnoDB有

  2. 提交事务就是释放锁

  3. 锁需要在事务内开启

MyISAM

• 特点：1.不支持事务;2.查询效率高; 3.支持全文索引；4,不支持外键。

• 表锁：手动上锁代码如下：

  lock table 表名 read；
  --SQL操作；
  unlock tables；

引擎优化

• 一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

• InnoDB提供提交、回滚、崩溃恢复能力及并发控制能力，适用于对数据更新操作频率高的数据表

• MyISAM引擎能提供较高的查询效率，适用于对数据进行频繁查询操作的数据表

八、mysql的行列转换问题？

MySQL中的行列转换，通常是指将行数据转换为列数据，或将列数据转换为行数据的操作。

1. 行转列（行数据转换为列数据）：
   - 使用`CASE WHEN`语句或`IF`函数可以将行数据转换为列数据。
   - 通过在`SELECT`语句中使用`CASE WHEN`语句或`IF`函数，可以根据条件将不同的行数据转换为不同的列数据。

   示例：

SELECT
       id,
       MAX(CASE WHEN category = 'A' THEN value END) AS value_a,
       MAX(CASE WHEN category = 'B' THEN value END) AS value_b,
       MAX(CASE WHEN category = 'C' THEN value END) AS value_c
   FROM
       table_name
   GROUP BY id;

   上述示例中，将`table_name`表中的行数据按照`id`进行分组，并使用`CASE WHEN`语句将不同的`category`对应的`value`转换为不同的列。

2. 列转行（列数据转换为行数据）：
   - 使用`UNION`或`UNION ALL`操作符可以将多个列数据合并为一列数据。
   - 通过将多个`SELECT`语句使用`UNION`或`UNION ALL`连接在一起，可以将多个列数据合并为一列数据。

   示例：

   SELECT value_a AS value FROM table_name
   UNION
   SELECT value_b AS value FROM table_name
   UNION
   SELECT value_c AS value FROM table_name;

上述示例中，将`table_name`表中的`value_a`、`value_b`、`value_c`三列数据合并为一列数据。

行列转换在一些特定场景下非常有用，例如将某些行数据进行聚合汇总并转换为列数据，或者将多个列数据合并为一列进行展示。在实际使用中，可以根据具体的需求和数据结构选择合适的行列转换方式。

九、sql语句的执行顺序是怎样的？什么是DDL,DML,DQL,DCL?

SQL语句的执行顺序如下：

1. from子句：从指定的表中获取数据。
2. where子句：根据指定的条件筛选数据。
3. group by子句：按照指定的列对数据进行分组。
4. having子句：对分组后的数据进行筛选。
5. select子句：选择需要查询的列。
6. distinct关键字：去除重复的行。
7. order by子句：对结果进行排序。
8. limit子句：限制返回的行数。

DDL、DML、DQL和DCL是SQL语言中的四种类型，分别表示数据定义语言、数据操作语言、数据查询语言和数据控制语言。

- DDL（Data Definition Language）：用于定义数据库结构和对象，包括创建、修改和删除数据库、表、索引等。常见的DDL命令有creat、alter和drop。
- DML（Data Manipulation Language）：用于操作数据库中的数据，包括插入、更新和删除数据。常见的DML命令有insert、update和delete。
- DQL（Data Query Language）：用于查询数据库中的数据，包括select语句。DQL用于从数据库中检索数据。
- DCL（Data Control Language）：用于控制数据库的访问权限和事务处理，包括grant和revoke命令。DCL用于授权和撤销权限。

总结起来，DDL用于定义数据库结构，DML用于操作数据库数据，DQL用于查询数据库数据，DCL用于控制数据库访问权限和事务处理。这四种类型的SQL语句在数据库的设计、操作和管理中起着不同的作用。

10、mysql的事务是什么意思，事务的特性，事务的隔离级别，事物的传播行为分别是什么？

MySQL的事务是一组数据库操作，它们被视为一个单独的工作单元，要么全部成功执行，要么全部回滚（撤销）。事务的目的是确保数据库的一致性和完整性。

事务具有以下四个特性（ACID特性）：

1. 原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部回滚。如果事务中的任何一个操作失败，那么整个事务将回滚到初始状态，数据库不会受到部分操作的影响。

2. 一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。事务执行过程中，如果有任何违反数据库约束的操作发生，事务将回滚到初始状态。

3. 隔离性（Isolation）：事务的执行是相互隔离的，一个事务的操作不应该对其他事务产生影响。隔离级别定义了事务之间的隔离程度，可以避免脏读、不可重复读和幻读等问题。

4. 持久性（Durability）：一旦事务提交成功，其所做的修改将永久保存在数据库中，即使发生系统崩溃或重启，也不会丢失。

MySQL提供了四个事务隔离级别：

1. 读未提交（Read Uncommitted）：最低级别的隔离级别，允许事务读取未提交的数据。可能会出现脏读、不可重复读和幻读等问题。

2. 读已提交（Read Committed）：保证一个事务读取的数据是已经提交的数据。可以避免脏读问题，但仍然可能出现不可重复读和幻读问题。

3. 可重复读（Repeatable Read）：保证同一事务中多次读取同一数据的结果是一致的。可以避免脏读和不可重复读问题，但仍然可能出现幻读问题。

4. 串行化（Serializable）：最高级别的隔离级别，事务串行执行，可以避免脏读、不可重复读和幻读等问题。但是并发性能较差，一般情况下不建议使用。

事务的传播行为定义了事务在嵌套调用中的行为：

1. required：如果当前存在事务，则加入该事务；如果当前没有事务，则创建一个新的事务。
2. requires_new：无论当前是否存在事务，都创建一个新的事务，并挂起当前事务。
3. supports：如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务方式执行。
4. not_supported：以非事务方式执行操作，如果当前存在事务，则挂起该事务。
5. mandatory：如果当前存在事务，则加入该事务；如果当前没有事务，则抛出异常。
6. never：以非事务方式执行操作，如果当前存在事务，则抛出异常。

事务的隔离级别和传播行为可以根据具体的业务需求进行设置，以保证数据的一致性和完整性，并提高并发性能。