数据库基础

数据库作为底层存储组件，在日常开发中非常重要，这篇文章主要记录了 MySql 相关知识点。

数据库事务

事务的四大特性：ACID，原子性、一致性、持久性、隔离性
事务并发的问题：脏读、不可重复读、幻读
事务的四大隔离级别：1、读未提交无法解决事务并发问题；2、读已提交可以解决脏读问题，Oracle 默认使用此隔离级别；3、可重复读可以解决不可重复读问题，也是mysql默认的事务隔离级别；4、可串行化是最高的隔离级别，能解决所有事务并发问题，但是会很大程度上影响事务的并发度。

为啥 MySql 索引使用 B+ 树实现

支持快速插入和删除的动态数据结构有散列表、平衡二叉查找树、跳表。散列表支持 O(1) 的查找效率，但是不支持范围查找，所以不可用。平衡二叉查找树支持 O(logn) 的查询效率，并且可通过中序遍历得到有序的序列，但是也没法支持指定范围查找。跳表支持快速的查找、删除、插入数据，对应的时间复杂度为 O(logn) ，并且能够支持按区间快速的查找数据，可以用来实现索引。其实B+树的思路和跳表类似。

B+ 树是从二叉查找树演进而来的，数据存储在叶子节点，且叶子节点的深度都一致，非叶子节点只存储索引。但是这样的话数据量太大就会导致树的高度很大，占用很大的内存。使用时间换空间的思想，可以将树存储在硬盘上，但是由于树的高度太高，查找时需要磁盘 IO 的次数太多，导致查询效率太低。那就别二叉了，使用m叉来存储，m越大，树的高度就越低。是不是m取多大合适呢？不管是内存中的数据还是磁盘中的数据，操作系统都是按照一页一页来读的，所以尽可能保证每个非叶子节点的大小为一页的大小。和跳表一样，在删除和插入索引的时候可能需要动态的调整节点，到新增索引导致节点超过页大小时进行节点分裂，但这可能导致节点的个数大于m，这个时候就继续分裂父节点。同理，当删除索引是可能导致合并节点。

B 树和 B+ 树的区别，为啥不用 B 树

B 树的非叶子节点也存储了数据，而 B+ 树的非叶子节点只是存储了索引的键值。每个节点中的 key 个数越多，那么树的高度越小，需要磁盘 I/O 的次数越少，因此一般来说 B+Tree 比 BTree 更快，因为 B+Tree 的非叶节点中不存储 data，就可以存储更多的 key。

索引的类型

单列索引：只有一个列的索引，可通过 CREATE INDEX index_name ON table_name (column_name) 创建。注意：where子句中使用索引字段or来连接非索引字段将导致引擎放弃索引而进行全表扫描。
联合索引：多个列组合在一起的索引，可通过 CREATE INDEX index_name ON table_name (column1_name,column2_name,column3_name) 创建。注意：where子句联合索引字段不能用or连接；用and连接时必须包含联合索引的第一个字段；否则引擎不会使用索引。
聚集索引：聚集索引确定表中数据的物理顺序，一个表只能包含一个聚集索引，但该索引可以包含多个列（联合索引）（不过mysql的innodb只支持主键聚集索引，不支持联合聚集索引）。
注意：1，如果一个主键被定义了，那么这个主键就是作为聚集索引；2，如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引；3，如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。
非聚集索引：一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表可以包含多个非聚集索引。注意：innodb的非聚集索引的叶子节点上的data是主键，为什么存放的主键，而不是记录所在地址呢，理由相当简单，因为记录所在地址并不能保证一定不会变，但主键可以保证。
唯一索引：标识改索引的值具有唯一性，可以为单列索引或联合索引。可通过 ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name ...);

参考文档：单列索引和联合索引，explain执行计划

建索引需要注意的点

最左前缀匹配原则，非常重要的原则，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的查询优化器会帮你优化成索引可以识别的形式。
尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

常用优化方案

sql优化

不要使用 select * from table_name ，用具体的字段代替 *
尽量避免在where子句中使用 != < > 操作符、对字段进行null值判断、对字段进行表达式操作、使用or来连接条件、使用like进行最左侧模糊查询、使用in和not in，这些操作都可能导致引擎放弃使用索引而进行全表扫描。

具体内容参考MySQL优化