目录
6.什么是聚簇索引、非聚簇索引以及区别？InnoDB、MyISAM中索引的区别？
9.Hash索引和B+树的区别？什么是多列索引的最左前缀匹配原则？
一、SQL优化面试题30道
mysql性能调优面试题_Knowledge丶的博客-CSDN博客_mysql调优面试题
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。
2.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。 sql中有两种方式表示不等于,一种是"<>"(不含引号),另一种是"!="(不含引号),用法是一样的。
3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null，可以在num上设置默认值0以确保表中num列没有null值，然后这样查询：select id from t where num=0。
4.在 where 子句中使用 or 作为连接条件时，如果or前的列和后面的列中有一个没有索引，那么涉及的索引都不会被用到。
5.下面的查询也将导致全表扫描：select id from t where name like '%abc%'，对于 like '..%' (不以 % 开头)，则可以应用到 colunm 上的index。
6.in 和 not in会导致全表扫描（in会走索引,但是当in的取值范围较大时会导致索引失效,走全表扫描），如：select id from t where num in(1,2,3,4...28......)。对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3，但是between…and…也可能让索引失效：当sql语句查询出来的数据量超过总数据量一定的比例( 30%左右)会使索引失效。
7.如果在 where 子句中使用形参变量，也会导致全表扫描。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为查询使用强制索引：
select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
9.应尽量避免在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'10.不要在 where 子句中的“=”【左边】进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是【复合索引】，那么必须使用到该索引中的【第一个字段】作为条件时才能保证系统使用该索引，否则该索引将不会被使用。并且应【尽可能】的让字段顺序与索引顺序相一致。（字段顺序也可以不与索引顺序一致，但是一定要包含【第一个字段】）
12.如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码只复制结构不复制数据，但是会消耗系统资源的，应改成这样：
create table #t(......)
下面是创建临时表的两种方法：
第一种方法（全新创建）——
create table #t(uid int,name varchar(100),sex varchar(100),city varchar(100));
详解：
#t，意为表名是t，加#号，表示此表为临时表而非实体表；
第二种方法（复制创建）——
select uid,name,sex,city into #t from [users] where 1=2
详解：
select uid,name,sex,city意为选择这些字段；
into #t 意为插入到一个临时表中，表名为t，#号是表示临时表而非实体表；
from [users]表示创建的临时表是从表名为users的表中去复制；
where 1=2表示只复制表users的结构而不复制表中的数据，假如写为1=1则表示通过复制表结构来创建临时表的同时还要复制相关字段的所有数据。
我们在处理SQL查询的时候，往往需要创建临时表，多数情况下，表结构是一样的（或者部分字段一样），那么采用第二种方法创建无疑是最快最好的。（如果字段全部一样，字段就用*星号表示，若仅选择其中几个字段，则可以select uid,name,sex即可）。
如果临时表结构与库中现有表没有较多相似，则选择第一种方法来创建。
13.很多时候用 exists 代替 in 是一个好的选择：
select * from a where id in(select id from b)
用下面的语句替换：
select * from a where exists(select 1 from b where id=a.id)
对于这样的sql查询同一个库，结果是一样的，但是对于不同情况查询速度差别较大，
使用in时是先执行子查询再执行主查询，而使用exists时是先执行主查询再执行子查询。
对于主表数据较多时，使用in查询速度比exist更快；
反之从表数据较多时，使用exists查询速度更快；都会走索引。
如果使用的是not in与not exists，请直接使用not exists，因为not in会进行全表扫描不走索引，而not exists会走索引。
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的（mysql 分为service层和引擎层。所有的sql在执行前会经过service层的优化），当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，字段值male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常用到的列上建的索引是否必要。
16.应尽可能的避免更新 clustered 聚簇索引数据列（如InnoDB引擎的主键索引就是聚簇索引），因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17.尽量使用数值型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数值型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
20.尽量用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。
MySQL表变量简介
MySQL表变量是MySQL数据库中一种特殊的变量类型，可以在查询中使用它们来模拟临时表。表变量是一种内存中的数据结构，可以存储和操作数据。在某些情况下，使用表变量可以提高查询性能并简化查询逻辑。创建表变量
要创建一个表变量，我们使用DECLARE语句。下面的代码示例演示了如何创建一个名为my_var的表变量，包含两个列id和name。DECLARE my_var TABLE ( id INT, name VARCHAR(255) );插入数据
一旦创建了表变量，我们可以使用INSERT INTO语句向其中插入数据。下面的代码示例演示了向my_var表变量中插入两行数据的方法。INSERT INTO my_var (id, name) VALUES (1, 'Alice');
INSERT INTO my_var (id, name) VALUES (2, 'Bob');
查询表变量
查询表变量与查询普通表一样简单。我们可以使用SELECT语句从表变量中检索数据并进行任何所需的操作。下面的代码示例演示了如何查询my_var表变量中的所有数据。SELECT * FROM my_var;
使用表变量进行数据操作
表变量不仅可以用于存储数据，还可以执行各种操作，例如过滤、排序和聚合。（聚合函数对一组值执行计算并返回单一的值。MySQL中有5种聚合函数较为常用，分别是SUM()、求平均函数AVG()、最大值函数MAX()、最小值函数MIN()和计数函数COUNT()）-- 过滤数据
SELECT * FROM my_var WHERE id > 1;-- 按名称排序
SELECT * FROM my_var ORDER BY name ASC;-- 计算行数
SELECT COUNT(*) FROM my_var;
表变量的优势
1.由于表变量是在内存中创建的，在查询中减少了对硬盘的I/O操作，因此对表变量的读写操作速度更快。2.使用表变量还可以减少临时表的创建和销毁次数，从而减少了系统资源的消耗。
3.另一个优势是表变量可以提高查询的可读性和可重用性，简化查询逻辑：通过将计算和过滤操作应用于表变量，我们可以将复杂的查询逻辑分解为更易于理解和维护的部分。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.select count (*)和select count(1)两者返回结果是一样的，都包括对NULL的统计，而select count(column) 是不包括对NULL的统计的。
如果表有主键，select count(主键)最快。
如果表沒有主键, select count(1)比select count(*)快。
如果表只有一个字段的话，select count(*)就是最快的。
23.什么情况下设置了索引但无法使用？
① 在 where 子句中使用以“%..”开头的LIKE语句进行模糊匹配，对字段进行 null 值判断，或者使用!=或<>操作符，或者使用in、not in（in会走索引,但是当in的取值范围较大时会导致索引失效,走全表扫描）。
② 在 where 子句中使用 or 作为连接条件时，如果or前的列和后面的列中有一个没有索引，那么涉及的索引都不会被用到。
③ 数据类型不匹配，如果查询语句中的数据类型与索引列的数据类型不匹配，则无法使用索引。例如，如果索引列是整数类型，但查询语句中使用了字符串类型，则无法使用索引。
④对小表查询，如果mysql估计全表扫描要比使用索引快，会不使用索引；查询数量是大表的大部分，应该是30%左右，会不使用索引，如between…and…命中的数据量过大可能让索引失效。
⑤索引列参与函数、算术运算或其他表达式运算会导致索引失效。
⑥违背最左匹配原则：在使用索引字段作为条件时，如果该索引是【复合索引】，那么必须使用到该索引中的【第一个字段】作为条件时才能保证系统使用该索引，否则该索引将不会被使用。并且应【尽可能】的让字段顺序与索引顺序相一致。（字段顺序也可以不与索引顺序一致，但是一定要包含【第一个字段】）
⑦并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，字段值male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表（数据字典）的较长时间锁定。
| drop table的时候数据库至少会做两件事： 1、删除表所占有的空间 2、在数据字典中删除表的信息 如果直接drop table，那么这两个步骤要保证一致，在同一个事务中完成，特别是数据量很大的表，这个过程会占用比较长的时间。如果先truncat table后drop table， 1、可以降低操作失败的风险 2、可以降低数据字典锁占用的时间，降低系统开销 |
25.drop 、 truncate 、 delete三者的区别
-
数据恢复方面：delete 可以恢复删除的数据，而 truncate 和 drop 不能恢复删除的数据。
-
执行速度方面：drop > truncate > delete。
-
删除数据方面：drop 是删除整张表，包含行数据和字段、索引等数据，而 truncate 和 delete 只删除了行数据。
-
添加条件方面：delete 可以使用 where 表达式添加查询条件，而 truncate 和 drop 不能添加 where 查询条件。
-
重置自增列方面：在 InnoDB 引擎中，truncate 可以重置自增列，而 delete 不能重置自增列。
26.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
游标(cursor)是一个存储在服务器上的数据库查询的结果集。游标可以在存储过程和自定义函数中使用，申明游标、打开游标、使用游标、关闭游标。
游标的作用就是对查询数据库所返回的结果集进行遍历，以便进行相应的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；正是游标把作为面向数据集的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够结合使用。
MySQL游标有下面这些属性：
- 游标是只读的，也就是不能更新它；
- 游标是单向的，也就是只能在一个方向上从开始往后进行遍历，不能从后往前，也不能跳过某些记录随意进退；
- 避免在已经打开游标的表上更新数据。
27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28.在所有存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF ，这样就无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
30.尽量避免大事务操作，以提高系统并发能力。
二、无意看到的知识点
1.如何设置MySQL分布式架构主键ID，为什么不能使用自增ID或者UUID做主键？
-
自增ID问题：在分布式数据库中，如果使用自增ID作为主键，每个数据库节点的自增值可能会重叠，导致主键冲突。
-
UUID问题：UUID是唯一的，但是由于其长度较长，可能会影响性能，并且在某些情况下，UUID作为主键可能会导致数据分布不均，影响查询性能。
解决方案：
-
使用分布式主键生成策略，如使用开源的
UUID生成算法，或者使用第三方服务生成主键ID，如TiDB的TiKV的分布式自增ID机制。 -
使用
Snowflake算法生成主键ID，它结合了机器的标识、时间和序列号，能够在分布式系统中生成全局唯一且按时间顺序递增的ID。
2.使用自增id的缺点
- 别人一旦爬取你的数据库，就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况
- 对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争
- Auto_Increment锁机制会造成自增锁的抢夺，有一定的性能损失
3.什么是自增锁、行级锁和间隙锁？
自增锁
隐式事务:没有开启和结束的标志,默认执行完SQL语句就自动提交,比如我们经常使用的INSERT、UPDATE、DELETE语句就属于隐式事务。显式事务例如spring事务。
自增锁的设计初衷是为了解决在并发环境下如何保证自增ID的唯一性和递增性。在早期的MySQL版本中（MySQL 8.0.3之前），系统变量
innodb_autoinc_lock_mode的默认值为1，自增锁的实现采用了表级锁的方式，意味着在插入操作时需要获取表级锁来分配自增值，这在一定程度上限制了并发性能，尤其是在大量数据插入时。随着MySQL版本的更新（MySQL 8.0.3及以后），通过调整系统变量innodb_autoinc_lock_mode的值，默认值变为2，可以优化自增锁的行为，减少锁竞争，提高系统的并发处理能力。
行级锁
For update是MySQL中用于实现行锁的一种语法,其主要作用是在SELECT查询语句中加上FOR UPDATE子句,以保证查询结果集中的每一行都被锁定，避免其他事务对这些行进行修改。 SELECT * FROM table WHERE id BETWEEN 10 AND 20 FOR UPDATE;
间隙锁
间隙锁的出现是因为“为了在innodb的可重复读隔离级别下，解决幻读问题”产生的。
间隙锁（Gap Locks）是数据库中用于锁定索引范围的一种锁。它们的主要目的是防止其他事务在给定范围内插入新的数据，保证范围内数据的一致性和避免幻读现象。
间隙锁的工作原理可以这样理解：当执行一个范围查询并请求锁（如SELECT ... FOR UPDATE）时，InnoDB不仅会对满足查询条件的已有数据记录加锁，还会对键值在条件范围内但并不存在的记录所形成的“间隙”加锁。这样，即使查询条件范围内的记录不存在，也能防止其他事务在这个范围内插入新的记录，从而避免了幻读现象。
举例来说，假设有一个用户表（user table），其中只有101条记录，用户ID（userid）的值从1到100。如果执行一个查询语句如“select * from user where userid > 100 for update;”，InnoDB不仅会对userid值为101的记录加锁，还会对userid大于101的“间隙”加锁，以防止其他事务在这个范围内插入新的用户记录。这样做是为了保证在事务并发情况下，同一查询不会返回不同数量的结果，即避免了幻读问。
临键锁
临键锁（Next-Key Lock）是InnoDB存储引擎中的一种锁机制，它是行锁和间隙锁的结合，用于解决幻读问题。临键锁不仅锁定一行记录，还锁定该记录与下一个记录之间的间隙，从而防止其他事务在同一个范围内进行插入或修改操作。这种锁定机制主要应用于InnoDB存储引擎的事务处理中，以避免幻读问题，即在一个事务读取某些行后，另一个事务插入新行导致第一个事务再次读取时出现额外的行。
临键锁的设计目的是为了解决幻读问题，主要通过锁定一段左开右闭的索引区间来实现。这意味着，如果一个事务正在访问某个数据范围，临键锁将确保其他事务不能在这个范围内插入新的记录，除非它们也获得了相应的临键锁。这种锁定机制有助于保护数据的一致性，特别是在高并发的环境中。
此外，临键锁的锁定范围包括记录本身及其后的间隙，这意味着它是一种细粒度的锁定机制。与间隙锁相比，间隙锁主要锁定多个索引记录之间的间隙，是一种粗粒度的锁定机制。这两种锁的共存性表明，它们是一对互斥的锁，即同一个事务不能同时拥有临键锁和间隙锁，这有助于避免死锁的可能性。
综上所述，临键锁是一种重要的数据库锁定机制，它通过结合行锁和间隙锁的特性，确保在并发操作中数据的一致性和完整性，特别是在InnoDB存储引擎中，它有效地避免了幻读问题，提高了数据库的并发处理能力和数据的一致性。
三、MySQL面试宝典(尚硅谷)
1.MySQL中索引有几种？
InnoDB、MyISAM存储引擎都可分为三类索引：B+树索引（全量索引）、Hash索引(配合B+树索引使用、快速精准定位数据、自动生成无法手动创建)、全文索引（全文索引可以对文本中的关键词创建索引，支持模糊查询、MyISAM存储引擎可以直接支持全文索引，而InnoDB存储引擎则需要进行一些设置才能够支持全文索引）。
2.索引是个什么东西？
索引是对数据库数据表的一列或者多列的值进行排序的一种数据结构，使用索引可以快速访问数据表中的特定数据。
3.索引的优缺点？
优点：
1.B+树将随机I/O变成顺序I/O(因为B+树的叶子节点是连接在一起的)可以减少磁盘I/O次数和时间开销，大大加快数据检索的速度。 2.B+树的空间利用率高，和B树相比，B树的每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树的一个内部节点就可以存储更多的索引，从而使树的高度变低，减少了I/O次数，使得数据检索速度更快。
缺点：
1.从空间角度考虑，建立索引需要占用物理空间。
2.从时间角度考虑，创建和维护索引需要花费时间，对数据进行增删改的时候都需要维护索引。
4.索引的数据结构有哪些？
数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。
5.介绍下B+树索引？
InnoDB、MyISAM存储引擎中存储索引的数据结构都有B+树。
B＋树的叶子节点是相连在一起的，具有有序性，方便顺序检索，支持排序和范围查找。
B+树索引又可以分为聚簇索引和非聚簇索引。
InnoDB索引分为两种：
- 聚簇索引:也称聚集索引,①一般建表时的主键就会被mysql作为聚簇索引,②如果没有主键,则选择非空唯一的索引作为聚簇索引,③都没有则隐式创建一个row_id作为聚簇索引
- 非聚簇索引:也称辅助索引或二级索引,平时我们添加的索引就是辅助索引
MyISAM只有非聚簇索引：
1. MyISAM 只有非聚簇索引:数据文件和索引文件是分离的，索引保存的是数据的指针。
补充：
InnoDB必然有主键(没有则会隐式创建)。而MyISAM可以没有主键(不会隐式创建)，也可以有主键。因此主键索引可能是聚簇索引，也可能是非聚簇索引：
聚簇索引是以主键作为B+树索引的键值所构成的B+树索引，聚簇索引的叶子节点存储着完整的数据记录，聚簇索引就是按照每张表的主键构造一颗B+树，每张表只能有一个聚簇索引。
非聚簇索引是以非主键的列作为B+树索引的键值所构成的B+树索引，非聚簇索引的叶子节点存储着主键值(InnoDB)或者指针(MyISAM)。所以使用非聚簇索引进行查询时，会先找到主键值，然后到根据聚簇索引找到主键对应的数据域。
上图中叶子节点存储的是数据记录，为聚簇索引的结构图，非聚簇索引的结构图如下：
6.什么是聚簇索引、非聚簇索引以及区别？InnoDB、MyISAM中索引的区别？
6.1什么是聚簇索引、非聚簇索引以及区别？
聚簇索引、非聚簇索引的数据结构都是B+树。
聚簇索引将数据和索引放到一起存储，索引结构的叶子节点保留了数据行记录。它并不是一种单独的索引类型， 而是一种数据存储方式。
非聚簇索引将数据和索引分开存储，索引叶子节点存储的是指向数据行的地址（MyISAM）或者主键值（InnoDB）。
聚簇索引和非聚簇索引的区别
1）聚簇索引叶子节点存放的是数据行，支持覆盖索引（指查找的数据全部出自索引数据本身），访问数据只需要一次索引查找；覆盖索引（covering index 或称为索引覆盖）指查找的数据全部出自索引数据本身，避免了回表的产生减少了树的搜索次数，显著提升性能。 覆盖索引将频繁的查询条件字段和查询结果字段组成一个联合索引，这样这个索引项相对数据项存储空间也比较小，但是其中已经包括了所需要的结果，就不用二次查表 。
2）非聚簇索引的叶子节点存放的是主键值或指向数据行的指针，非聚簇索引访问数据需要两次索引查找，第一次找到主键值/地址值，第二次根据主键值/地址值找到行数据。（InnoDB的非聚簇索引使用主键值作为指针，会让非聚簇索引占用更多的空间，但是InnoDB在移动行时无需更新非聚簇索引的这个指针）(MyISAM的非聚簇索引使用地址作为指针)。
聚簇索引优点
1）数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树，因此从聚簇索引中获取数据比非聚簇索引更快
2）对于主键的排序查找和范围查找速度非常快
聚簇索引缺点
1）插入速度严重依赖插入顺序，因为插入要保证主键不能重复，速度要慢很多（InnoDB相比MyISAM写效率较差）
2）更新主键代价高，因为会导致更新的行移动
6.2InnoDB、MyISAM中索引的区别？
InnoDB数据文件和索引文件是同一个文件，MyISAM数据文件和索引文件是分开存储的。
在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，叶子节点保留了数据行。在主索引之上创建的索引为辅助索引，也是非聚簇索引，为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。
在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引(辅助索引)，索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引树。
在InnoDB存储引擎中，按照每张表的主键（没有主键会生成rowid作为隐式主键）构造一颗B+树结构的聚簇索引，每张表只能拥有一个聚簇索引，在该聚簇索引基础上构建B+树结构的辅助索引又称二级索引和非聚簇索引，InnoDB中非聚簇索引的B+树的叶子节点存储的是主键值。
在MyISAM存储引擎中，主键索引和辅助索引都是B+树的非聚簇索引，MyISAM中非聚簇索引的B+树的叶子节点存储的是数据行的指针。
6.3InnoDB、MyISAM的区别及适用场景？
1. InnoDB支持事务，MyISAM不支持。
2. InnoDB支持外键, MyISAM不支持。
3. InnoDB和MyISAM的索引存储结构之一都是B+树，InnoDB使用的是聚集索引和非聚集索引，而MyISAM是非聚集索引。Innodb在版本5.6之前不支持FULLTEXT全文索引，而MyISAM支持全文索引。 4. InnoDB表必须有主键且主键索引是聚簇索引，而MyISAM可以没有主键且主键索引是非聚簇索引。
5. InnoDB支持 MVCC(多版本并发控制)，MyISAM 不支持。
可将MVCC看成行级锁的一种妥协，它在许多情况下避免了使用锁，可以提供更小的开销。根据实现的不同，它可以允许非阻塞式读，在写操作进行时只锁定必要的记录。MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ UNCOMMITED不是MVCC兼容的。SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据 。
6. InnoDB支持行级锁和表级锁，默认为行级锁，(某些情况下还是锁整表，如 update table set a=1 where user like '%lee%')，MyISAM 只有表级锁。
7. InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行（可能用到索引如count(id)等场景，也可能没用到）。而MyISAM只要简单的读出保存好的行数即可，注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。
8. 适用场景不同。
MyISAM适合：增删改不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择， 没有事务。
InnoDB适合：表更新和查询都相当的频繁， 大量的INSERT或UPDATE；可靠性要求比较高，或者要求事务。
9. 对于自增长的字段，InnoDB中必须要有只包含该字段的索引，但是在MyISAM中则可以和其他字段一起建立联合索引。
10. InnoDB按主键大小顺序插入，MyISAM按记录插入顺序保存。
11. InnoDB需要更多的内存和存储空间，而MyISAM可被压缩，存储空间较小。
12. 清空整表时InnoDB是逐行删除效率慢而MyISAM会重建表，InnoDB相比MyISAM写效率较差。
7.介绍下Hash索引？
哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会通过哈希算法对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针（物理地址）作为哈希表的value值。这样查找一个数据的时间复杂度就是o(1)，一般多用于精确查找。Hash索引是自动生成的，用于辅助B+数索引，程序员不能手动创建。哈希索引一般多用于精确的等值查找，B+索引则多用于除了精确的等值查找外的其他查找如范围查找和排序。哈希索引不支持排序，因为哈希表是无序的。
8.B树和B+树的区别？数据库为什么使用B+树而不是B树？
8.1B树和B+树的区别？
B树，全名BalanceTree，平衡树
1. B树中的内部节点和叶子节点均存放键和值，而B+树的内部节点只有键没有值，叶子节点存放所有的值。
2. B＋树的叶子节点是相连在一起的，方便顺序检索。
8.2数据库为什么使用B+树而不是B树？
1. B树适用于精确的等值查找即随机检索，而B+树适用于范围检索和顺序检索。
2. B+树的空间利用率更高，因为B树每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树的一个节点就可以存储更多的键，从而使树的高度变低，减少了I/O次数，使得数据检索速度更快。
3. B+树的叶子节点都是连接在一起的，所以范围查找，顺序查找更加方便。
4. B+树的性能更加稳定，因为在B+树中，每次查询都是从根节点到叶子节点，而在B树中，要查询的值可能不在叶子节点，在内部节点就已经找到。
那在什么情况适合使用B树呢？因为B树的内部节点也可以存储值，所以可以把一些频繁访问的值放在距离根节点比较近的地方，这样就可以提高查询效率。综上所述，B+树的性能更加适合作为数据库的索引。
9.Hash索引和B+树的区别？什么是多列索引的最左前缀匹配原则？
9.1Hash索引和B+树的区别？
1. 哈希索引不支持排序，因为哈希表是无序的。
2. 哈希索引不支持范围查找，用于精确的等值查找。
3. 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
4. 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。
9.2什么是多列索引的最左前缀匹配原则？
原文链接：https://blog.csdn.net/yuanchangliang/article/details/107798724
最左前缀匹配原则，用几句话来概述就是：
顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边；在设计sql时，要根据多列索引和是否用到范围查询来决定where条件中哪些字段必须出现和出现的先后顺序。
建立一个多列索引，对于索引中的字段，mysql会从左一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的（a，b，c可以），如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。比如b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，b、c、d都是用不到索引的。
=和in可以乱序，比如建立(a,b,c)索引时a = 1 and b = 2 and c = 3 可以任意顺序，b = 2 and c = 3 and a = 1也可以，mysql的查询优化器会帮你优化成索引可以识别的形式。
测试：
创建一张表，建立1个复合索引/多列索引（name在前，cid在后）
drop table if EXISTS student; CREATE TABLE `student` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `name` VARCHAR ( 255 ) DEFAULT NULL, `cid` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ), KEY `name_cid_INX` ( `name`, `cid` ), KEY `name_INX` ( `name`) ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8EXPLAIN SELECT * FROM student WHERE name='小红'; EXPLAIN SELECT * FROM student WHERE cid=1; EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小红'; possible_keys： 显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用 key： 实际使用的索引。如果为NULL，则没有使用索引
上图WHERE name='小红';符合复合索引的最左前缀匹配原则，所以用到了name_INX、name_cid_INX，且type是ref（可以理解为索引有效），尽管cid不作为查询条件。
上图WHERE cid=1;不符合复合索引的最左前缀匹配原则，所以用到了name_cid_INX但type是index(可以理解为索引失效)。那么什么时候才能生效呢?当然是cid字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下cid才是有序的，如下：
下图WHERE cid=1 AND name='小红';看似不符合复合索引的最左前缀匹配原则，cid在前name在后，但是=和in可以乱序（mysql查询优化器会根据复合索引的最左前缀匹配原则纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划），所以最终用到了name_cid_INX且type是ref(可以理解为索引有效)。
index：索引全扫描，MySQL遍历整个索引来查询匹配的行。缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据（可以理解为索引失效了），直到找到符合判断条件的某个索引。
————————————————ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。有些了解的人可能会问，索引不都是一个有序排列的数据结构么。不过答案说的还不够完善，那只是针对单个索引，而复合索引的情况有些同学可能就不太了解了。下面就说下复合索引：以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：
mysql创建复合索引的排序规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调建立复合索引和执行sql时候要符合最左前缀原则的原因。
总结多列索引的最左前缀匹配：若存在复合索引（a,b,c），如果where条件中用到了bc没用到a，或者用到了c没用到ab，则一定造成type=index即索引全扫描（可理解为索引失效），因为后面字段的索引排序依赖前面字段的索引排序。用到了a没用到bc、或者用到了ab没用到c、abc都用到，复合索引都可以生效或者部分字段生效（(>、<、between、like停止匹配)、（=和in可以乱序））。
10.MySQL中建立、删除索引的方法有哪些？
10.1 建立普通索引
10.2 建立唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name) ;
10.3 建立全文索引
10.4 建立多列/复合索引
CREATE INDEX index_name ON table_name (column1, column2);10.5 建立主键索引
alter table table_name add primary key (column_list);CREATE PRIMARY KEY INDEX index_name ON table_name (column_name) ;
10.6 删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;11.B+树索引的数据结构详细介绍（以聚簇索引为例）
12.一个b+树中大概能存放多少条索引记录？
-
真实环境中一个页存放的记录数量是非常大的（默认16KB），假设指针与键值忽略不计（或看做10个字节），数据占 1 kb 的空间： -
如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 16 条记录。
-
如果B+树有2层，最多能存放
1600×16=25600条记录。 -
如果B+树有3层，最多能存放
1600×1600×16=40960000条记录。 -
如果存储千万级别的数据，只需要三层就够了
B+树的非叶子节点不存储值，只存储键，相对B树每个节点可以存储更多的键，树的高度会更矮胖，IO次数也会更少（节点 = 数据页 = 单个磁盘块 = 1次IO操作）。
扫一扫
1040
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
