2022-04-23 00:25 已编辑门头沟学院 C++

关注

SQL面经

SQL优化方面一些技巧？

SQL优化：1.最大化利用索引；2.尽可能避免全表扫描；3.减少无效数据的查询；

SELECT语句的一些优化

避免出现select * 使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。
避免出现不确定结果的函数特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用查询缓存（query cache）。
多表关联查询时，小表在前，大表在后在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
使用表的别名当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
用where字句替换HAVING字句避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。 where和having的区别：where后面不能使用组函数
调整Where字句中的连接顺序 MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

编写过程： select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
解析过程： from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

数据量较大的场景下， SQL优化策略

尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下： SELECT * FROM t WHERE username LIKE '%陈%' 优化方式：尽量在字段后面使用模糊查询。如下：（原因涉及B+Tree索引最左前缀原则） SELECT * FROM t WHERE username LIKE '陈%' 若需求是要在前面使用模糊查询：

使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置
使用FullText全文索引，用match against 检索
数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。

尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下： SELECT * FROM t WHERE id = 1 OR id = 3 优化方式：可以用union代替or

SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下： NULL列在行中需要额外的空间以记录其值是否为NULL。对于MyISAM表，每个NULL列都多花一位，四舍五入到最接近的字节。MySQL库中的NULL值很容易导致我们在统计、查询表数据时出错。 SELECT * FROM t WHERE score IS NULL 优化方式：可以给字段添加默认值0，对0值进行判断。如下： SELECT * FROM t WHERE score = 0
尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描（可以将表达式、函数操作移动到等号右侧）

SELECT * FROM T WHERE score/10 = 9    #-- 全表扫描
SELECT * FROM T WHERE score = 10*9   #-- 走索引

当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and
查询条件不要用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如业务需要使用！=符号，需要重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

where条件仅包含复合索引非前置列（MySQL最左匹配原则）

select col1 from table where key_part2=1 and key_part3=2

复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列key_part1，按照MySQL联合索引最左匹配原则，不会走联合索引。。 8. 隐式类型转换造成不使用索引

select col1 from table where col_varchar=123;

SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。 9. order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;

对于上面的语句，数据库的处理顺序是：

首先，根据where条件和统计信息生成执行计划，得到数据。
将得到的数据排序。当（order by）执行处理数据时，数据库会先查看第一步的执行计划，看order by的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。返回排序后的数据。
当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

不建议用SELECT * 原因？

不需要的列会增加数据传输时间和网络开销用SELECT * 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。2）增大网络开销；* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。如果DB和应用程序不在同一台机器，这种开销非常明显。3）即使 mysql 服务器和客户端是在同一台机器上，使用协议还是 tcp，通信也需要额外时间。
对于无用的大字段，如 varchar、blob、text，会增加 io 操作。准确来说，长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作。（MySQL InnoDB）
无法使用MySQL优化器中“覆盖索引”策略优化 SELECT * 不走覆盖索引，无法MySQL优化器的“覆盖索引”策略的查询优化方式。如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。

如何避免全表扫描？

where字句别用or链接，可以union all
in和not in也慎用，可以between and
避免对字段进行null值判断
where字句别用!=和<>操作符
别用以通配符开头的like的查询
别在where子句对字段进行表达式操作和函数操作。
任何地方都不要使用 select * from 表
尽量使用数字型字段
复合索引尽量满足最左前缀原则。
在查找唯一一条数据的时候，使用limit 1
查询数据类型不一致会导致失效，例如字符串不加单引号会导致索引失效。

MySQL删除数据的方式都有哪些？

常用的三种删除方式：通过 delete、truncate、drop 关键字进行删除；这三种都可以用来删除数据，但用于的场景不同。

delete、truncate、drop的区别

从执行速度上来说 drop > truncate >> DELETE
从原理上讲一本书，delete是把目录撕了，truncate是把书的内容撕下来烧了，drop是把书烧了

DELETE DELETE from TABLE_NAME where xxx
DELETE属于数据库DML操作语言，只删除数据不删除表结构，会走事务，执行时触发trigger；
在 InnoDB 中，DELETE其实并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记为已删除，因此 delete 删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以重用这部分空间（重用 → 覆盖）。

DELETE执行时，会先将所删除数据缓存到rollback segement中，事务commit之后生效;
delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
对于delete from table_name where xxx 带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;
delete操作以后使用 optimize table table_name 会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。
delete 操作是一行一行执行删除的，并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，生成的大量日志也会占用磁盘空间。

truncate

Truncate table TABLE_NAME
truncate：属于数据库DDL定义语言，不走事务，原数据不放到 rollback segment 中，操作不触发 trigger（触发器）。执行后立即生效，无法找回！！！

truncate table table_name 立刻释放磁盘空间，不管是 InnoDB和MyISAM 。truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;

truncate能够快速清空一个表。并且重置auto_increment的值但对于不同的类型存储引擎需要注意的地方是：

对于MyISAM，truncate会重置auto_increment（自增序列）的值为1。而delete后表仍然保持auto_increment。
对于InnoDB，truncate会重置auto_increment的值为1。delete后表仍然保持auto_increment。但是在做delete整个表之后重启MySQL的话，则重启后的auto_increment会被置为1 （InnoDB的表本身是无法持久保存auto_increment。delete表之后auto_increment仍然保存在内存，但是重启后就丢失了，只能从1开始。实质上重启后的auto_increment会从 SELECT 1+MAX(ai_col) FROM t 开始。）

Drop

Drop table Tablename
drop：属于数据库DDL定义语言，同Truncate；执行后立即生效，无法找回！！！
drop table table_name 立刻释放磁盘空间，不管是 InnoDB 和 MyISAM; drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是变为 invalid（无效）状态。

数据库范式：

范式是为了消除重复数据减少冗余数据，从而让数据库内的数据更好的组织，让磁盘空间得到更有效利用的一种标准化标准，满足高等级的范式的先决条件是满足低等级范式。(比如满足2nf一定满足1nf)（范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。）

第一、二、三范式。第四、第五范式、BCNF

第一范式就是属性不可分割，每个字段都应该是不可再拆分的。比如一个字段是姓名（NAME），国内通常都是姓名是一个不可再拆分的单位，这时候就符合第一范式；国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。
第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记住主键约束就好。比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。
第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好。比如说有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任，系主任也存到这个学生表中，那就会造成数据大量的冗余，一是这些信息在系信息表中已存在，二是系中有1000个学生的话这些信息就要存1000遍。因此第三范式的做法是在学生表中增加一个系编号的字段（外键），与系信息表做关联。
第四范式：要求把同一表内的多对多关系删除。
第五范式：从最终结构重新建立原始结构。
BC范式（BCNF）：符合3NF，并且，主属性不依赖于主属性。若关系模式R属于第一范式，且每个属性都不传递依赖于键码，则R属于BC范式。

使用范式的好处：

减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）
消除异常（插入异常，更新异常，删除异常）
让数据组织的更加和谐…

MySQL事务

事务就是一组原子性的SQL执行单元，单个事务，为一个不可分割的最小工作单元。该组SQL要么全部执行成功（commit），要么全部执行失败（rollback）。

脏读，幻读，不可重复读（并发场景下事务出现的问题）？并发场景下MySQL事务可能会出现丢失修改、脏读、幻读、不可重复读问题；

脏读：一个事务读取了另一个事务修改但未提交的数据。
丢失修改：数据被两个事务连续修改，第一个事务的修改丢失了。
不可重复读，一个事务连续读两次数据，但结果不一样。(两次读之间，数据被其他事务修改)。
幻读：一个事务连续读两次数据，读取数据量不一样。(两次读之前，数据被其他事务删除或新增)。脏读（针对的是未提交读数据）；不可重复读（针对其他提交前后，读取数据本身的对比）

幻读（针对其他提交前后，读取数据条数的对比）

脏读：事务知道了自己本不应该知道的东西，强调的动作是查询，我看到了自己不该看的东西 ;

不可重复读：一个人查的时候，其他人却可以增删改, 但却不知道数据被改，还拿去做之前的用途；

幻读：我修改了数据，等我要查的时候，却发现有我没有修改的记录，因为有其他人插了一条新的。

Innodb是如何解决幻读问题的呢？

MySQL 存储引擎 InnoDB 在可重复读（RR）隔离级别下解决幻读问题：方法是通过next-key lock（MySQL将行锁 + 间隙锁组合统称为 next-key lock，通过 next-key lock 解决了幻读问题。但会在并发情况下造成死锁）在当前读事务开启时：

给涉及到的行加写锁（行锁）防止写操作；
给涉及到的行两端加间隙锁(Gap Lock)防止新增行写入；从而解决了幻读问题。

ACID四大特性的理解

ACID特性：原子性、一致性、隔离性、持久性

原子性(Atomicity) 单个事务，为一个不可分割的最小工作单元，整个事务中的所有操作要么全部commit成功，要么全部失败rollback，对于一个事务来说，不可能只执行其中的一部分SQL操作，这就是事务的原子性。
一致性(Consistency) 数据库总是从一个一致性的状态转换到另外一个一致性的状态。一致性确保若事务执行失败，没有提交，事务中所做的修改也不会保存到数据库中，保证数据一致性。
隔离性(Isolation) 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性(Durability) 一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

对表锁、行锁的理解：

表锁：表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。

优点：实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。
缺点：出现锁定资源争用的概率也会最高，大大降低并发度。使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

2.行锁

优点：锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。
缺点：锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情多，带来的消耗大。行级锁定也最容易发生死锁。使用行级锁定的主要是InnoDB存储引擎，基于索引来完成行锁。

适用场景：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新数据的情况，同时又有并发查询的应用场景。

3.页锁除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别。特点：锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。页级锁定和行级锁定一样，会发生死锁。使用页级锁定的主要是BerkeleyDB存储引擎

数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。两种锁的使用场景
乐观锁适用于写少的情况下（多读场景），即冲突很少发生的时候，省去了锁的开销，加大系统整个吞吐量。
悲观锁适用于写多的情况，即一般会经常产生冲突，上层应用会不断的进行retry，用悲观锁降低系统性能损耗。

MySQL里有哪些比较重要的日志？

MySQL日志主要包括：

重做日志（redo log）
回滚日志（undo log）
归档日志（binlog）
错误日志（errorlog）
慢查询日志（slow query log）
一般查询日志（general log）
中继日志（relay log）先写日志，再写磁盘慢查询日志是用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。 MySQL错误日志是记录MySQL 运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息。

MySQL日志系统：redo log、binlog、undo log 区别与作用

日志系统主要有redo log和binlog(归档日志)。InnoDB事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。 undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

redo log日志模块(重做日志)

redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中。（引出WAL技术） WAL即Write Ahead logging技术，他的关键点是先写日志，再写磁盘。(「预写式日志」WAL）是关系数据库系统中用于提供原子性和持久性技术)。mysql 通过 redo、undo 日志实现 WAL。每当有操作时，在数据变更之前将操作写入 redo log，这样当发生掉电之类的情况时系统可以在重启后继续操作。undo log 称为撤销日志，当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之间的状态。mysql 中用 redo log 来在系统崩溃重启之类的情况时修复数据（事务的持久性），而 undo log 来保证事务的原子性。有了redo log日志，那么在数据库进行异常重启的时候，可以根据redo log日志进行恢复，也就达到了crash-safe（崩溃安全）redo log日志的大小是固定的，即记录满了以后就从头循环写。

binlog日志模块(归档日志)

binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的。binlog用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。binlog也用于数据库的基于时间点的还原。

redo log和binlog区别

redo log是属于InnoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

Undo log日志模块(回滚日志)

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

MVCC多版本并发控制（Multiversion Concurrency Control）

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。每一个写操作都会创建一个新版本的数据，读操作会从有限多个版本的数据中挑选一个最合适的结果直接返回；在这时，读写操作之间的冲突就不再需要被关注，而管理和快速挑选数据的版本就成了 MVCC 需要解决的主要问题。各数据库中MVCC实现并不统一，MVCC只在 READ COMMITTED （读提交）和 REPEATABLE READ （可重复读）两个隔离级别下工作; 对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列：(trx_id事务ID、roll_pointer上个版本指针,其实还有一个row_id的隐藏列但这里用不着); 每次对记录进行改动，都会把对应的事务id赋值给trx_id隐藏列，也会把旧的版本写入到undo日志中；所以在并发情况下，一个记录可能存在多个版本，通过roll_pointer形成一个版本链。MVCC的核心任务就是：判断一下版本链中的哪个版本是当前事务可见的。这就有了ReadView的概念，这个ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids；根据ReadView的活跃事务ID列表和版本链事务ID进行比较找出可见的事务ID最大的版本： 1、如果版本的trx_id属性值小于m_ids列表中最小的事务id，表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。 2、如果版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。 3、被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。 MVCC只在读已提交和可重复读这两个隔离机制下运行。这两个隔离机制下MVCC实现方式的区别就在于：读已提交是每次读取数据前都生成一个ReadView；而可重复读，是在第一次读取数据时生成一个ReadView，后序的重复查询就不再生产ReadView了。

总结：

多版本并发控制指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复这个ReadView就好了。

MySQL存储引擎MyISAM与InnoDB区别

常用的存储引擎有以下：

Innodb引擎：Innodb擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高

MyISAM不支持事务，而Innodb支持事务。
Myisam是表级锁，而Innodb是行级锁。
外键支持：MyISAM表不支持外键，而InnoDB支持。
SELECT MyISAM更优；INSERT、UPDATE、DELETE InnoDB更优
select count(*) myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式：B+树索引，MyISAM是堆表；B+树索引，Innodb 是索引组织表
锁支持：MyISAM是表级锁定；Innodb 行级锁定、表级锁定，锁定力度小并发能力高
存储结构上：MyISAM：每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
存储空间 MyISAM： MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。 InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
可移植性、备份及恢复 MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

索引

索引是建的越多越好吗？（MySQL的索引）

不是建越多索引越好

数据量小的表不需要建立索引，建立会增加额外的索引开销
不经常引用的列不要建立索引，因为不常用，即使建立了索引也没有多大意义
经常频繁更新的列不要建立索引，因为肯定会影响插入或更新的效率
数据重复且分布平均的字段，因此他建立索引就没有太大的效果（例如性别字段，只有男女，不适合建立索引）
数据变更需要维护索引，意味着索引越多维护成本越高。
更多的索引也需要更多的存储空间

索引优化：

根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边
模糊查询以%为开始的查询，只能使用全文索引来进行优化。
使用短索引。对串列进行索引，如果可能应该指定一个前缀长度。

什么是索引？索引有哪些优缺点？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引本身很大，不可能全部存储在内存中，因此索引以索引表的形式存储在磁盘中。索引查找过程中就要产生磁盘I/O消耗。索引的实现通常使用B+树。
索引的原理：就是把无序的数据变成有序的查询

索引的优点：

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

什么时候要使用索引？！

主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；(单纯的order by 不会用到索引，但如果在where中出现，就可以用索引了。)
查询中与其他表关联的字段，外键关系建立索引
较频繁作为查询条件的字段、高并发条件下倾向组合索引；
用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不要使用索引？！

经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引。数据库测试数据不多时，往往在执行完第一条查询命令之后就被全部加载到内存，这将使后续的查询命令不管有没有使用索引都执行得非常快。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。
定义为text、image和bit的数据类型的列不要建立索引
尽量的扩展索引，不要新建索引索引使用的场景：SQL语句的where、order by、join 索引覆盖：如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描）。

创建索引的注意事项：

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度大）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

创建索引的三种方式：

在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);

使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 #table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

删除索引：

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名
删除主键索引：alter table 表名 drop primary key（若主键自增则需先取消自增长再删）

索引有哪几种类型？

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

ALTER TABLE table_name ADD UNIQUE (column); #创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column1,column2); #创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

ALTER TABLE table_name ADD INDEX index_name (column);#创建普通索引
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);#创建组合索引

全文索引：是目前搜索引擎使用的一种关键技术。

ALTER TABLE table_name ADD FULLTEXT (column);#创建全文索引

联合索引:使用多个字段同时建立一个索引,在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
覆盖索引:一个索引包含所有需要查询的字段的值。覆盖索引把要查询出的列和索引对应，不用回表操作！InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。当我们建立联合索引时，联合索引当然还是一颗B+树。

最左匹配原则

最左匹配原则的定义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

当对索引中所有列通过"=" 或 “IN” 进行精确匹配时，索引都可以被用到。

如果建的索引顺序是(a,b)，查询语句 where b = 1 AND a = ‘aaa’; 为什么还能利用到索引？理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。

索引不会包含有 NULL 值的列:只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL 如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描;like 语句的索引问题:如果通配符 % 不出现在开头，则可以用到索引;执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。所以，只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。

12.索引的数据结构（b+树，hash）索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择B+树索引。评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。一、演变过程：二叉排序树 → 二叉平衡树 → B-Tree（B树） → B+Tree（B+树）

二叉排序树（二叉搜索树，或二叉查找树）：对于一个节点，左子树孩子节点值都要小于它本身，右子树孩子节点值都要大于它本身，所有节点都满足这个条件。对二叉查找树进行中序遍历，即可得到有序的数列。查询复杂度，和二分查找一样，插入和查找的时间复杂度均为 O(logn) ，但是在最坏的情况下仍然会有 O(n) 的时间复杂度。原因在于插入和删除元素的时候，树没有保持平衡。
平衡二叉搜索树（AVL树）它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
B树（Balanced Tree）多路平衡查找树特点如下：

所有键值分布在整个树中
任何关键字出现且只出现在一个节点中
搜索有可能在非叶子节点结束
在关键字全集内做一次查找，性能逼近二分查找算法

B+Tree (B+树是B树的变体，多路搜索树)---查询条件是一个不以通配符开头的常量

所有关键字存储在叶子节点，非叶子节点不存储真正的data，从而可以快速定位到叶子结点。
为所有叶子节点增加了一个链指针，意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。
B+Tree可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。

B+树的优点：

比较的次数均衡，减少了I/O次数，查找速度高，查找稳定。
B+树的磁盘读写代价更低；B+树的查询效率更加稳定。每次创建表，系统会为你自动创建一个基于ID的聚集索引，存储全部数据；你每次增加索引，数据库就会为你创建一个附加索引，索引选取的字段个数就是每个节点存储数据索引的个数，注意该索引并不存储全部数据。

B+tree性质：

n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
B+ 树中，数据对象的插入和删除仅在叶节点上进行。
B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

B树和B+树的区别：

在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
在B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。使用B树的好处：

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

使用B+树的好处：

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，需要更多的内存置换次数，因此需要花费更多的时间

数据库为什么使用B+树而不是B树  B树只适合随机检索，而B+树同时支持随机检索和顺序检索；  B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；  B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。  B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。  增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

哈希索引（只能用于对等比较）

类似于数据结构中简单实现的HASH表（散列表），当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法：直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

缺点：1、不能使用范围查询 2、无法利用索引的数据来避免任何排序运算 3、不支持多列联合索引的最左匹配规则 4、任何时候都不能避免表扫描5、哈希碰撞

Hash索引和B+树索引有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理：

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

他们有以下的不同：

hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
hash索引不支持使用索引进行排序，原理同上。
hash索引不支持模糊查询以及多列索引的最左前缀匹配。因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。 13.什么是聚簇索引？何时使用聚簇索引与非聚簇索引（聚集/非聚集）聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。  聚簇索引：聚簇索引是物理索引，数据表按顺序存储，物理上连续。一旦创建了聚簇索引，表中的所有列都根据构造聚簇索引的关键列来存储。因此一个表只能有一个聚簇索引。

MySQL中InnoDB表的聚簇索引：

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。每个InnoDB表都需要一个聚簇索引。该聚簇索引可以帮助表优化增删改查操作。如果你为表定义了一个主键，MySQL将使用主键作为聚簇索引。如果你不为表指定一个主键，MySQL讲索第一个组成列都not null的唯一索引作为聚簇索引。如果InnoBD表没有主键且没有适合的唯一索引（没有构成该唯一索引的所有列都NOT NULL），MySQL将自动创建一个隐藏的名字为“GEN_CLUST_INDEX ”的聚簇索引。因此每个InnoDB表都有且仅有一个聚簇索引。聚簇索引只可能是主键，或者所有组成唯一键的所有列都为NOT NULL的第一个唯一索引，或者隐式创建的聚簇索引这三种情况。

聚簇索引的优缺点

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

使用聚簇索引的情况：列经常被分组排序或返回某范围内的数据、主键列及外键列
使用非聚簇索引的情况：列经常被分组排序、频繁更新的列、频繁修改索引列、主键列、外键列
非聚簇索引：所有不是聚簇索引的索引都叫非聚簇索引或者辅助索引。在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。一张表可以有多个辅助索引。
InnoDB中，B+树索引可以分为聚簇索引和辅助索引（非聚簇索引或二级索引）。这两种索引内部都是B+树。
InnoDB中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。InnoDB中，每个辅助索引的每条记录都包含主键，也包含非聚簇索引指定的列。MySQL使用这个主键值来检索局促索引。因此应该尽可能将主键缩短，否则辅助索引占用空间大。一般来说用自增的整数型列作为主键列。

主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。总之，在数据量大一些的情况下，用自增主键性能会好一些。