数据库索引

最新推荐文章于 2024-01-05 09:13:16 发布

只想撸铁

最新推荐文章于 2024-01-05 09:13:16 发布

阅读量1.4k

点赞数

本文链接：https://blog.csdn.net/m0_59002506/article/details/118314800

版权

数据库

索引是定义在table基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项组成，每一种索引项由索引字段和行指针构成。

**索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。**索引的实现通常使用B_TREE及其变种。索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

索引中包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（BTree）中，使SQL可以快速有效地查找与键值关联的行

为什么要建立索引，即索引的优点：

建立索引的列可以保证行的唯一性，生成唯一的rowId；
建立索引可以有效缩短数据的检索时间；
建立索引可以加快表与表之间的连接；
为用来排序或者是分组的字段添加索引可以加快分组和排序顺序。

索引的缺点：

创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大；
创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间），从而会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，这样会导致时间变长。
在我们需要修改表中的数据时，索引还需要进行动态的维护，所以对数据库的维护带来了一定的麻烦。

常用索引？

索引的优点

大大加快数据的检索速度，这也是创建索引的最主要的原因；
加速表和表之间的连接；
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

什么情况下设置了索引但无法使用？

以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
OR 语句前后没有同时使用索引；
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
对于多列索引，必须满足 最左匹配原则 (eg：多列索引col1、col2和col3，则索引生效的情形包括 col1或col1，col2或col1，col2，col3)。

什么样的字段适合创建索引？

经常作查询选择的字段
经常作表连接的字段
经常出现在order by, group by, distinct 后面的字段

创建索引时需要注意什么？

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

索引的缺点

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

索引的分类

普通索引和唯一性索引：索引列的值的唯一性
单个索引和复合索引：索引列所包含的列数
聚簇索引与非聚簇索引：聚簇索引按照数据的物理存储进行划分的。对于一堆记录来说，使用聚集索引就是对这堆记录进行堆划分，即主要描述的是物理上的存储。正是因为这种划分方法，导致聚簇索引必须是唯一的。聚集索引可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了；

而非聚集索引是把一个很大的范围，转换成一个小的地图，然后你需要在这个小地图中找你要寻找的信息的位置，最后通过这个位置，再去找你所需要的记录。

主键就是默认的主键索引。

唯一索引

普通索引

索引是帮助mysql高效获取数据的数据结构。索引就是数据结构

分类：

主键索引 primary key
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引 unique key
- 避免重复的列出现，可以重唯一索引可以重复，多个列都可以标志位唯一索引
常规索引
- 默认的，index key关键字来设置
全文索引
- 在特定的数据库引擎下菜有，myisam支持
- 快速定位数据
索引规则：

索引不是越多越好，不要对经常变动的数据加索引，小数据的表不需要加索引，索引一般加在常用来查询的字段上

主键、自增主键、主键索引与唯一索引概念区别

主键：指字段唯一、不为空值的列；

主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；

自增主键：字段类型为数字、自增、并且是主键；

唯一索引：索引列的值必须唯一，但允许有空值。

主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，

主键不允许有空值，所以不能说唯一索引也是主键。

索引优化

建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引。但必须注意以下几种可能会引起索引失效的情形：

以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
OR语句前后没有同时使用索引；
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
对于多列索引，必须满足最左匹配原则(eg,多列索引col1、col2和col3，则索引生效的情形包括col1或col1，col2或col1，col2，col3)。

什么是存储过程？有哪些优缺点？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。存储过程具有以下特点：

存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率；
当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码；
减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小；
通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；
Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；
Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

什么叫视图？游标是什么？

视图是一种虚拟的表，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能，可以对视图进行增，删，改，查等操作。特别地，对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易。

索引的底层原理？

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。

B-Tree结构 m叉

每个节点最多有m个子节点。
除了根节点和叶子节点，每个节点至少有 cell(m/2) 个子节点
若根节点不是叶子节点，至少有2个子节点
所有叶子节点都在同一层
每个非叶子节点由n个key与n+1个指针组成 cell(m/2) - 1 《 n《 n - 1

B+Tree InnoDB存储引擎的索引实现

与B树不同点：

1.叶子节点冗余了所有的非叶子节点

2.每个叶子节点增加一个指向相邻叶子节点的指针

B+最多包含n个key，B-最多有n-1个
B+叶子节点包含所有的key信息，依key大小顺序排列
B+所有的非叶子节点都可看作是key的索引部分，B+所有的叶子节点包含了所存信息，非叶子节点不存储数据信息

优点：叶子节点有指向相邻节点的指针，提高了范围查询的效率

缺点：一个节点中存放多少元素合适

另外B+树相比AVL树层数低，降低了磁盘IO的操作次数

B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。
B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

为什么说B±tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？

B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；
B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；
**数据库索引采用B+树而不是B树的主要原因：**B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

B+Tree (B+树是 B 树的变体，也是一种多路查找树)

相对于b-树来说，他的最大的特点是：

其非叶子节点不存储数据，只存储关键字，所有数据都存储在叶子节点上。
b+树为所有叶子节点增加了一个链指针，指向下一条数据。这意味着所有的数据都是按关键字的顺序存储的。很适合查找范围数据。
每一个叶子页到根的距离相同

MySQL B+Tree索引和Hash索引的区别？

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;
B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问;

那为什么大家不都用Hash索引而还要使用B+树索引呢？

Hash索引

Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询,因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样；
Hash索引无法被用来避免数据的排序操作，因为Hash值的大小关系并不一定和Hash运算前的键值完全一样；
Hash索引不能利用部分索引键查询，对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用；
Hash索引在任何时候都不能避免表扫描，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要回表查询数据；
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+树索引高。

B+Tree索引

MySQL中，只有HEAP/MEMORY引擎才显示支持Hash索引。

常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

同理，哈希索引没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。

数据库的事务？

原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

事务开始前和结束后，数据库的完整性约束没有被破坏。一致性表示事务完成后，符合逻辑运算。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。　 持久性（Durability）

持久性 （Durability）

事务结束后的数据不随着外界原因导致数据丢失

若操作钱，服务器重启，事务没有提交就恢复到原状。若已提交结果不变，持久化到数据库。事务一旦提交就不可逆。

是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务隔离

为了提升性能, 事务会以较低的隔离级别运行，事务的隔离级别可以通过隔离事务属性指定。

事务的并发问题

1、脏读：一个事务处理过程中，读取了另一个未提交事务的数据。

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：一个事务在某个范围内多次查询返回不同的值，在这个时间范围内，被另外一个事务提交并修改。

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。

3、幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

幻读解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。

例如：事务A对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作这时事务B又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务A的用户如果再查看刚刚修改的数据，会发现还有跟没有修改一样，其实这行是从事务B中添加的，就好像产生幻觉一样，这就是发生了幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

不可重复读重点在于update和delete，而幻读的重点在于insert。避免不可重复读需要锁行（某一行在select操作时，不允许update与delete）就行，避免幻读则需要锁表。幻读不能通过行锁来避免，需要Serializable隔离级别，

数据库实现事务隔离的方式，基本上可分为以下两种：

一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

Oracle 支持的 2 种事务隔离级别：READ_COMMITED , SERIALIZABLE

事务隔离级别

read-uncommitted 未提交读 最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。 写数据会锁住整张表
read-committed 提交读 一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
repeatable-read 可重复读 默认级别，确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新，可以避免脏读和不可重复读，仍会出现幻读问题。 写数据会锁住整张表。
serializable 序列化 最严格的事务隔离级别，要求所有事务被串行执行，不能并发执行，可避免脏读、不可重复读、幻读情况的发生。 读写数据都会锁住整张表。

在Oracle、SqlServer中都是选择读已提交(Read Commited)作为默认的隔离级别，为什么Mysql不选择读已提交(Read Commited)作为默认隔离级别，而选择可重复读(Repeatable Read)作为默认的隔离级别呢？

RC与RR在锁方面的区别

1、RR repeatable-read 可重复读 要用到间隙锁，

而RC则没有间隙锁。因为MySQL的RR需要间隙锁来解决幻读问题。

而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多；
2、 RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会被释放掉，

RR隔离级别，即使不符合where条件的记录，也不会释放行锁和间隙锁，

所以从锁方面来看，RC的并发应该要好于RR；
3、RC隔离级别时，事务中的每一条select语句会读取到他自己执行时已经提交了的记录，也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时，事务中的一致性读的ReadView是以第一条select语句的运行时，作为本事务的一致性读snapshot的建立时间点的，只能读取该时间点之前已经提交的数据。

什么是主键索引？

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定的主键，就会创建主键索引，CREATE INDEX不能用来创建主键索引，使用ALTER TABLE来代替。

数据库排序的两关键字是什么？

顺序：ASC
倒序：DESC

char 和 varchar 的区别是什么？

char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
chat 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。
varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

说一下MySQL常用的存储引擎？InnoDB引擎的事务实现

InnoDB 引擎：InnoDB引擎的4大特性

插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count() from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。
MyIASM 引擎：不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count() from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

而事务的ACID是通过InnoDB日志和锁来保证。

原子性和一致性通过Undo log（回撤日志）来实现。

事务的隔离性是通过数据库锁的机制实现的，

持久性通过redo log（重做日志）来实现，

Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了roll back语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将RedoLog持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是RedoLog已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

MySQL常见的三种存储引擎（InnoDB、MyISAM、MEMORY）的区别？

MySQL存储引擎中的MyISAM和InnoDB区别详解

http://blog.csdn.net/lc0817/article/details/52757194

MySQL存储引擎之MyISAM和Innodb总结性梳理

https://www.cnblogs.com/kevingrace/p/5685355.html

MySQL存储引擎MyISAM与InnoDB如何选择

MySQL有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用： MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

虽然MySQL里的存储引擎不只是MyISAM与InnoDB这两个，但常用的就是两个。

两种存储引擎的大致区别表现在：

InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
MyISAM适合查询以及插入为主的应用。
InnoDB适合频繁修改以及涉及到安全性较高的应用。
InnoDB支持外键，MyISAM不支持。
从MySQL5.5.5以后，InnoDB是默认引擎。
InnoDB不支持FULLTEXT类型的索引。
InnoDB中不保存表的行数，如 selectcount(*)fromtable时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。
DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，效率非常慢。MyISAM则会重建表。
InnoDB支持行锁（某些情况下还是锁整表，如 update tableseta=1whereuser like'%lee%'。

关于MySQL数据库提供的两种存储引擎，MyISAM与InnoDB选择使用：

INNODB会支持一些关系数据库的高级功能，如事务功能和行级锁，MyISAM不支持。
MyISAM的性能更优，占用的存储空间少，所以，选择何种存储引擎，视具体应用而定。
如果你的应用程序一定要使用事务，毫无疑问你要选择INNODB引擎。但要注意，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表。比如DELETE FROM mytable这样的删除语句。
如果你的应用程序对查询性能要求较高，就要使用MyISAM了。MyISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

有人说MyISAM只能用于小型应用，其实这只是一种偏见。如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，而不是单纯地依赖存储引擎。

现在一般都是选用innodb了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？

表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。
InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。MySQL 的内连接、左连接、右连接有什么区别？

如何处理慢查询

关心过业务系统里面sql耗时吗？统计过慢查询吗？对慢查询都怎么优化？

在业务系统中，除了使用主键进行查询，其他都会在测试库上测试其耗时。

建了索引不一定命中。

优化针对这三个方向

分析语句，是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果在不需要的列，对语句进行分析以及重写。
分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句尽可能命中索引。
如果对语句优化已经无法进行，可以考虑表中的数据是否过大，如果是的话可以进行横向或者纵向分表。

JDBC 对事务的支持

对于JDBC而言，每条单独的语句都是一个事务，即每个语句后都隐含一个commit。实际上，Connection 提供了一个auto-commit的属性来指定事务何时结束。当auto-commit为true时，当每个独立SQL操作的执行完毕，事务立即自动提交，也就是说，每个SQL操作都是一个事务；当auto-commit为false时，每个事务都必须显式调用commit方法进行提交，或者显式调用rollback方法进行回滚。auto-commit默认为true。

为了能够将多条SQL当成一个事务执行，必须首先通过Connection关闭auto-commit模式，然后通过Connection的setTransactionIsolation()方法设置事务的隔离级别，最后分别通过Connection的commit()方法和rollback()方法来提交事务和回滚事务。

MySQL存储引擎中的MyISAM和InnoDB区别详解

在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，其由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然MyISAM性能极佳，但却有一个显著的缺点：不支持事务处理。不过，MySQL也导入了另一种数据库引擎InnoDB，以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

InnoDB是MySQL的数据库引擎之一，其由Innobase oy公司所开发，2006年五月由甲骨文公司并购。与传统的ISAM、MyISAM相比，InnoDB的最大特色就是支持ACID兼容的事务功能，类似于PostgreSQL。目前InnoDB采用双轨制授权，一是GPL授权，另一是专有软件授权。具体地，MyISAM与InnoDB作为MySQL的两大存储引擎的差异主要包括：

存储结构：每个MyISAM在磁盘上存储成三个文件：第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
存储空间：MyISAM可被压缩，占据的存储空间较小，支持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
可移植性、备份及恢复：MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，同时在备份和恢复时也可单独针对某个表进行操作。InnoDB免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
事务支持：MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。
AUTO_INCREMENT：在MyISAM中，可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。InnoDB中必须包含只有该字段的索引，并且引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
表锁差异：MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
全文索引：MyISAM支持 FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
表的具体行数：MyISAM保存表的总行数，select count() from table;会直接取出出该值；而InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
CURD操作：在MyISAM中，如果执行大量的SELECT，MyISAM是更好的选择。对于InnoDB，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
外键：MyISAM不支持外键，而InnoDB支持外键。

通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁、外键等等。尤其在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，必须需要注意的是，任何一种表都不是万能的，合适的才是最好的，才能最大的发挥MySQL的性能优势。如果是不复杂的、非关键的Web应用，还是可以继续考虑MyISAM的，这个具体情况具体考虑。

sql常用的关键字方面的查询

查询语句不同元素（where、jion、limit、group by、having等等）执行先后顺序？

查询中用到的关键词主要包含六个，并且他们的顺序依次为 select--from--where--groupby--having--orderby

from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据

from后面的表关联，是自右向左解析而where条件的解析顺序是自下而上的。

也就是说，在写SQL文的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表），而把能筛选出小量数据的条件放在where语句的最左边（用小表去匹配大表）

内连接关键字：inner join；左连接：left join；右连接：right join。

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录，没有显示null
　　right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录，没有显示null
　　inner join(等值连接) 只返回两个表中联结字段相等的行

union unionall

union是取两个表的并集,然后去除重复的值,运行结果可以看出来查出来的值去除了跟另一个表的重复数据

UNION ALL只是简单的将两个结果合并后就返回

写SQL时需要注意,在用到UNION和UNION ALL查询的时候,返回的结果集的列名是以第一个表的列名为结果集的列名,两表的字段明可以不一样,但是类型一定要一样。

总结
1、对重复结果的处理：UNION 在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
2、对排序的处理：UNION 将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
3、从效率上说，UNION ALL 要比 UNION 快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。
我们没有必要在每一个select结果集中使用order by子句来进行排序，我们可以在最后使用一条order by来对整个结果进行排序。例如：
select empno,ename from emp
union
select deptno,dname from dept
order by ename

什么是临时表，临时表什么时候删除？

临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。

创建临时表的语法与创建表语法类似，不同之处是增加关键字TEMPORARY

CREATE TEMPORARY TABLE tmp_table (	
    NAME VARCHAR (10) NOT NULL,	
    time date NOT NULL	
);	
	
select * from tmp_table;

慢查询如何查找原因？如何解决？

怎么发现有问题的SQL?（通过MySQL慢查询日志对有效率问题的SQL进行监控）

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。慢查询日志的相关参数如下所示：

1 通过explain查询和分析SQL的执行计划

使用 explain关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。

通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。

当扩展列extra出现Using filesort和Using temporay，则往往表示SQL需要优化了。

explain select * from table

SQL语句的优化

优化insert语句：一次插入多值；
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；
优化嵌套查询：子查询可以被更有效率的连接(Join)替代；
很多时候用 exists 代替 in 是一个好的选择。

没用到索引

select * from t where 100 <c and c < 100000; 刚好你的 c 字段上没有索引，那么抱歉，只能走全表扫描了，你就体验不会索引带来的乐趣了，所以，这回导致这条查询语句很慢。

字段有索引，但却没有用索引

select * from t where c - 1 = 1000;

如果我们在字段的左边做了运算，那么很抱歉，在查询的时候，就不会用上索引了，所以呢，大家要注意这种字段上有索引，但由于自己的疏忽，导致系统没有使用索引的情况了。

正确的查询应该如下

select * from t where c = 1000 + 1;

函数操作导致没有用上索引

select * from t where pow(c,2) = 1000;

使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间long_query_time参数的语句

使用打开记录查询慢的sql记录日志：

如何判断有没有用到索引？ explain

解释函数explain

explain select * from table

我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引：

type结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

**possible_keys：**sql所用到的索引

**key：**显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数。

注意：如遇到字段太长，添加不了索引，而又不能减小字段长度，则可以需要指定索引长度。

如：

CREATE INDEX 索引名称 ON 表名 (字段名(size)) ;

CREATE INDEX IDX_ELEMENT_XXX ON table_element (xxx_id(200)) ;

给table_element表中的xxx_id字段添加指定200长度的索引IDX_ELEMENT_XXX

数据库三大范式是什么

第一范式：

没意列都是不可在分割的原子数据项。

第二范式

前提：满足第一范式

每张表只描述一件事情

需要确保数据表中每一列都和主键相关，而不能只与主键的一部分相关

第三范式

在第二范式的基础上，需要确保每一列数据都和主键直接相关，而不能间接相关

关联的表不能超过三张表（阿里）

在规范性能的问题的时候，需要适当考虑规范性

故意增加一列（大数据量降低小数据量的查询：索引。比如百万级，可以加一列算count）

JDBC

Java操作数据库的规范，俗称JDBC

java.sql包 javax.sql包，数据驱动包

大量数据的表查询如何优化？

创建索引的原则（重中之重）
索引虽好，但也不是无限制的使用，最好符合一下几个原则

1）最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，

比如 a = 1 and b = 2 and c > 3 and d = 4

如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

创建索引时需要注意什么？

一、数据库索引创建规则

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；搜索

8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；

二、一般选择在这样的列上创建索引

在经常需要搜索查询的列上创建索引，可以加快搜索的速度；
在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上创建索引，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在Where子句中的列上面创建索引，加快条件的判断速度；
为经常出现在关键字order by、group by、distinct后面的字段，建立索引。

三、一般不选择具有这些特点的列上创建索引

对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求；
不要在有大量相同取值的字段上，建立索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度；
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少；
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

四、创建索引需要注意的地方

限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。
对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
删除不再使用，或者很少被使用的索引。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

联合索引原理

提到联合索引，不得不提的就是最左匹配原则

所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。

虽然依然是B+树，但联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

因为mysql索引查询会遵循最左前缀匹配的原则，在检索数据时从联合索引的最左边开始匹配。当我们创建一个联合索引的时候，如(key1,key2,key3),相当于创建了(key1),(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。 key（a，b，c），可以支持a、（a,b）、(a,b,c)这些组合的进行查询。

以下情况不会使用index排序

1、检查的行数过多，并且没有使用覆盖索引。
2、使用了多个索引，mysql一次只会采用一个索引。
3、对索引列同时使用了ASC和DESC。通过where语句将order by中索引列转为常量，则除外。
4、where与order by使用了不同的索引，与第二条类似
5、where或者ORDER BY中索引列使用了表达式(+1，-1这种操作)，包括函数表达式。参见第8，9句
6、where 与ORDER BY组合满足最左前缀，但where中使用了范围查询。
7、order by中加入了非索引列,且非索引列不在where中。
8、order by或者它与where组合没有满足索引最左前列。
9、当使用left join，使用右边的表字段排序。

百万级别或以上的数据如何删除

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了
什么是最左前缀原则？什么是最左匹配原则
顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
什么是聚簇索引？何时使用聚簇索引与非聚簇索引
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

何时使用聚簇索引与非聚簇索引