MySQL数据库

SQL语言

• 表的创建，删除，修改
• 链接方式的区别(内链接，左外连接，右外连接，全连接)，连接的条件(natural，using，on)
• 嵌入子查询

MySQL的架构

MySQL的架构分三层，最上层是采用客户端和服务端的交互模式，响应请求；中间层是核心，用于实现查询解析，缓存，优化等；第三层是存储引擎，负责数据的存储和提取。

索引方法

顺序索引(稠密索引和稀疏索引)

顺序索引的方式和操作系统内存管理的分页机制比较相似。稠密索引就是给每一个数据块建一个指针，把指针顺序摆放在一起就是索引了。稀疏索引，要求数据块本身是聚集堆放的，找到最大的小于要查询值的指针，然后循序遍历至找到目标为止。对于文件过大的时候，索引本身就很庞大，因此又建立了分级索引。

B树索引

B树索引，实际上是分为B树索引和B+树索引两种方法。MySQL用的B+树，MongoDB用的B树。关于B数和B+树如何索引，给大家安利两篇文章，写的通俗易懂。

漫画：什么是 B- 树？

漫画：什么是 B+ 树？

为什么选择B树？

(1) 对于数据库查找，索引的文件都存在磁盘上，磁盘IO的是非常消耗时间的，因此要减少磁盘IO的次数，而B数相比于二叉树，或者其变种AVL和RD-Tree的高度更低，因此磁盘IO的次数更好；
(2) B数是平衡树，查找，删除和插入是相对稳定的。

为什么选择B+树
(1) B+树所有的数据都保存在叶节点上，查找数据都必须到达叶子节点，因此其查找效率是恒定的；
(2) B+树的叶子节点有指针指向下一个叶子节点，形成了一个链表。而叶子节点间又是排序的，因此对于范围查找只需要先找到头的位置，然后遍历链表就可以了；
(3) B+树的非叶子节点不需要存储数据，只作为索引，因此B+数的阶数可以更高，故单个节点保存的数据个数会更多，使得B+树更加矮胖，因此磁盘IO的次数会更少。

哈希索引

散列索引就比较好理解了，实际就是在哈希表中保存文件的指针。所遇到的问题和哈希表中存在的问题是相同，如何选择好的哈希函数，数据过大后如果扩容，扩容的过程中有拷贝如何提高扩容的效率。当超过了装载因子后，可以新建一个更大的哈希表，但是并不一次性拷贝全部数据，而是当要插入或删除某个数据时，就将该桶中的所有数据拷贝。

哈希索引必须要匹配所有的关键词，才能使用索引，因为哈希值是由所有的关键词共同来实现的。而B+树索引，是分层索引，可以先匹配第一个关键词，再匹配第二个关键词。但是不能跨级索引，比如没有第一个关键词，直接通过第二个关键词索引。

在InnoDB引擎使用的是B+树索引，但是会自动为查询频繁的数据建立一个哈希索引，提高效率。

MySQL引擎InnoDB，MyISAM区别

区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

6. Innodb锁的粒度更细，支持行锁，MyISAM不支持。

如何选择

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

5. 数据量较大(3TB以上)时不要用MyISAM，崩溃后难以恢复。

数据库的锁

锁的粒度

• Innodb支持行锁，MyISAM不支持。

• 锁的粒度越细，支持的并发就更高，但是资源的消耗也越大，也容易引起死锁等其他问题。

锁的种类

读写锁：读锁又称为共享锁，写锁称为排外锁，与Linux中的读写锁实现相同的功能。

读锁是共享的，也就是同时可以多个客户访问同一个数据。写锁是排外的，在一定的时间范围内，只能有一个客户去访问锁住的资源。如果读者一直源源不断的加入，使得读锁无法被释放，导致写者发生饥饿。为了解决这种情况，有些实现上会提高写锁的优先级，当有写锁到来的时候，会将其他想要获取读锁的事件阻塞在后面。

事务的ACID特性

• 事物：构成单一逻辑工作单元的操作集合
• ACID特性
  (1) 原子性(Atomicity)：一个事务是不可分割的，要么都发生，要么都不发生；
  (2) 一致性(Consistency)：事务开始和结束前后，数据保存一致性，A和B转账，必须保证转账前后A和B账户的总额不变；
  (3) 隔离性(Isolation)：多个事务并发执行，避免事务之间的干扰。一个事务在提交前所做的修改对其他事务通常是不可见的；
  (4) 持久性(Durability)：事务一旦提交，所做的修改就永久保存在数据库中，即使此时服务器崩溃，修改的数据也不会丢失。

隔离级别

• 未提交读：事务中的修改，即使没有提交，对其他事务也都是可见的。读取未提交的数据，称为脏读。这个级别性能并没有好太多，但是却会带来很多问题，一般很少使用。

• 已提交读：解决了脏读的问题，一个事务开始后所做的修改在提交前对其他事务是不可见的，但是不保证在一个事务中执行两次相同的查询，其结果相同，也就是所谓的不可重复读。

• 可重复读：解决了重复读的问题，可以保证在一个事务中对同一数据的查询结果是一致的。它是MySQL的默认隔离级别。但是可能会出现幻读。幻读是指对一个范围内数据进行读取时，另外 一个事务插入了新行，再次读取时发现刚插入的那行没有被改变，像产生了幻觉一样，称为幻读。解决幻读的问题，可以通过加范围锁，锁住要修改的范围行区间。

• 可串行化：对行进行加锁读，可能会导致锁超时。

由上到下隔离级别依次升高。但都不允许脏写。隔离级别越低，支持的并发就越高，而且系统的开销也更小。

优化

从索引上优化

• 索引的列单独放在比较符号的一侧，例如

select * from table where id + 1 = 5  //无法使用索引select * from table where id = 4      //可以使用索引

• 索引不要太长，越长的索引会导致整个索引项越庞大，导致查询效率低。而且对于Innodb引擎更是灾难，因为innodb中辅助索引是先查询到主索引，再依据主索引去查询数据，如果主索引太长，会导致所有的索引的都会很庞大。可以使用部分前缀来索引。

• 建立多列索引，而不是为每个列建立一个独立的索引

select * from table where colum1 = a and colum2 = b;//对于上面这种查询建立多个独立索引是没有用的，会导致全表扫描select * from table where colum1 = aintersectselect * from table where colum2 = b;

• 将选择性更高的列放在前面

create table employees(varchar name(20);varchar department(20);)ENGINE = InnoDB;//这张表中name，的选择性肯定要比department高，department的重复率太高了

• 对于聚集性索引(InnoDB)主键最好呈自增性，可以减少插入是时B+树的分裂，因为InnoDB的叶节点就是数据的一行，分裂会导致数据的移动。

从SQL语言上优化

（explain，慢查询，show profile）

分库分表，主从复制，读写分离。

一致性哈希