[toc]
MySQL索引
1.数据库中有哪些索引类型?
索引的几种类型：唯一索引、主键索引、聚集索引、普通索引、组合索引、全文索引
唯一索引：在创建唯一索引时要不能给具有相同的索引值。在MySQL中创建索引：
CREATE UNIQUE INDEX indexName ON mytable(username(length));
主键索引：在我们给一个字段设置主键的时候，它就会自动创建主键索引，用来确保每一个值都是唯一的。
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
聚集索引：我们在表中添加数据的顺序，与我们创建的索引键值相同，而且一个表中只能有一个聚集索引。
普通索引：它的结构主要以B+树和哈希索引为主，主要是对数据表中的数据进行精确查找。
CREATE INDEX indexName ON mytable(username(length));
ALTER TABLE mytable ADD INDEX username(length);
组合索引：一个表中含有多个单列索引不代表是组合索引,通俗一点讲组合索引是:包含多个字段但是只有索引名称
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
全文索引：它的作用是搜索数据表中的字段是不是包含我们搜索的关键字，就像搜索引擎中的模糊查询。
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
使用索引的优点：
- 提高数据的搜索速度
- 加快表与表之间的连接速度
- 在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
使用索引的缺点:
- 在我们建立数据库的时候，需要花费的时间去建立和维护索引，而且随着数据量的增加，需要维护它的时间也会增加。
- 在创建索引的时候会占用存储空间。
- 在我们需要修改表中的数据时，索引还需要进行动态的维护，所以对数据库的维护带来了一定的麻烦。
2.数据库索引底层实现
MySQL中，索引的底层实现构主要是：哈希索引和B+树索引。
哈希索引的底层数据结构就是哈希表，查询性能最快；
B树索引：B树实现
B+树索引：B+树实现
3. 为什么选用B+树？
红黑树等数据结构也可以用来实现索引，但是文件系统以及数据库系统普遍采用B-Tree/B+Tree作为索引结构。
原因：索引本身也很大，因此索引往往是以索引文件的形式存储在磁盘上。所以，索引查找的过程就会产生磁盘的I/O操作，相比于内存存取，I/O存取消耗要高几个数量级，所以索引的优劣最重要的指标就是在查找过程中的磁盘I/O存取次数。
4. 哈希索引与B+树如何选用？
Hash索引是无序的，所以只适用于等值查询，而不能用于范围查询。大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。
5. 聚集索引和非聚集索引的区别
聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
区别：
- 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
- 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
- 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。
6. 什么情况下设置了索引但是会失效？
- 条件中有or
- 对于多列索引，不是使用的第一部分，则不会使用索引
- like查询时以%开头
- 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
SQL优化
1. 怎么对一条查询语句进行调优
逻辑层优化：如分步查询，然后在代码层进行拼接；用临时表；改变sql语句的写法等等
实现层的优化：然后我们看看每条sql语句执行的执行计划，用explain命令查看。可以看到这些表的执行顺序，可能使用到的索引，实际使用到索引，索引的长度，可能要扫描的行数等等信息，然后对这些信息分析，进行索引的优化。
2. 优化数据库的方案
- 参数优化
- MySQL设计优化
MySQL锁
1. MySQL有哪几种锁，分别怎么实现？
-
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
-
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
-
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
MySQL事务
事务时逻辑上的一组操作，要么都执行，要么都不执行。
1. 事务的四大特性
记忆：ACID
- 原子性（Atomicity）：事务是最小的执行单位，不允许分割。原子性就是为了保证动作要么全部完成，要么完全不起作用；
- 一致性(Consistency)：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
- 隔离性(Isolation)：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
- 持久性(Durability)：一个事务被提交之后。它对数据库中数据的改变是持久的，即数据库发生故障也不应该对其有任何影响。
2 有哪些数据库引擎，各自的区别
MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。
大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。
两者的对比：
- 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
- 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
- 是否支持外键： MyISAM不支持，而InnoDB支持。
-
是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在
READ COMMITTED和REPEATABLE READ两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读：MySQL-InnoDB-MVCC多版本并发控制 - ......
3. MySQL四种隔离引擎，底层实现
- READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
4. 数据库如何解决幻读
幻读：一次事务里，多次查询后，结果的个数不一致的情况叫做幻读。
如何解决：MVCC 和next-key（当前读）
next-key原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取数据是一致的。
next-key包含：记录锁、间隙锁。
记录锁：加在索引上的锁；间隙锁：加在索引之间的锁。
其他
1. 数据库的三大范式
1、第一范式：一个单元格只存储一个值。
当关系模式R的所有属性都不能在分解zhi为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
2、第二范式：满足所有的属性字段唯一依赖主键。
如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
3、第三范式：消除传递依赖。
设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。
网友评论