面试mysql篇

事务的四大特性（ACID）

1. 一致性：指事务执行前和执行后处于一致性的状态

2. 原子性：当事务包含多个操作时，要么都成功，要么都失败

3. 隔离性：多个事务之间互不影响

4. 持久性：一旦事务被提交，修改操作是一直生效的，就算数据库遇到故障也不会丢失提交事务的操作

事务的隔离级别有哪些

先了解几个概念：

1. 脏读：指一个事务在处理过程中读取到另一个未提交事务中的数据

2. 不可重复读：指对于数据库的某行数据，一个事务范围多次查询出不同的数据，由于在查询间隔另一个事务修改了数据并提交

3. 幻读：当一个事务读取某个范围内的数据时，另一个事务又在该范围内新增了记录，当之前事务再次读取该范围的记录时就产生了幻读

脏读和不可重复读的区别是，脏读是读到了事务未提交的数据，不可重复读是读到了事务提交的数据

幻读和不可重复读的区别是，一个是读到修改的数据，一个读到删除或者新增的数据

为此mysql数据库为我们提供了4种隔离级别

1. Readuncommitted（读未提交）：所有事务都可以看到其他未提交事务的执行结果

2. Readcommitted(读已提交)：一个事务只能看见已经提交事务的数据，避免了脏读

3. Repeatable read (可重复读)：mysql的默认事务隔离级别，保证了一个事务的多个实例并发读取数据时，看到的是同样的数据行，解决了不可重复读的问题

4. Serializable (串行化)：通过强制事务排序，让事务之间不发生冲突，解决幻读问题

查看事务隔离级别

select @@transaction_isolation;

设置事务隔离级别

set session transaction isolation level read uncommitted;

索引

• 索引是数据库用于数据库表提高数据访问速度的一种数据结构

• 优点

1. 提高数据库查询速度

2. 用来排序或者分组的字段添加索引会提高排序或者分组的速度

3. 加快表与表之间的链接

• 缺点

4. 创建索引会占用物理空间

5. 会降低增删改的效率，增删改时需要动态维护索引

• 索引的作用：数据是存储在磁盘上的，查询数据时如果没有索引会将所有数据加载到磁盘，依次检索，有了索引就不需要加载所有的数据，一般B+树的高度在2到4层，只需要读取2-4次磁盘，查询速度大大提升

• 哪些字段适合建立索引：用于查询的字段，用于表链接的字段，用于排序的字段

• 索引的数据结构：

• B+树索引：B树中所有的key从左往右递增排列，进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

• 哈希索引：哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

• 索引的分类：

1、主键索引：名为primary的唯一非空索引，不允许有空值。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的SQL语句如下：

ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameUNIQUEKEY(column_1,column_2,...);

3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

4.全文索引：只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

• 最左匹配原则：如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。如果查询条件为a = 1 and b > 2 and c = 3，那么a、b个字两段能用到索引，而c无法使用索引，因为b字段是范围查询，导致后面的字段无法使用索引。

• 聚簇索引：InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

• 索引什么时候会失效：

对于组合索引，不是使用组合索引最左边的字段，则不会使用索引

以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引

查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

判断索引列是否不等于某个值时

对索引列进行运算

查询条件使用or连接，也会导致索引失效

• 什么是覆盖索引：

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index。

比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。

explainselectblog_idfromuser_likewhereuser_id=13;

explain结果的Extra列为Using index，查询的列被索引覆盖，并且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。

explainselectuser_idfromuser_likewhereblog_id=1;

explain结果的Extra列为Using where; Using index， 查询的列被索引覆盖，where筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

• 什么是前缀索引：

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

建立前缀索引的方式：

//email列创建前缀索引ALTERTABLEtable_nameADDKEY(column_name(prefix_length));