mysql学习之索引原理

数据表中默认字段

数据表中，不要将0值设置为可能会正常出现的业务字段。

理由是：当相应的字段在数据库中查不到时，如果在java程序中的处理不是返回null，那么将默认为0值，

此时 "未找到" 和 可能的合法的一个默认0值将发生冲突。

所以。

 

 

学习mysql：

http://cenalulu.github.io/mysql/mysql-book-for-newbie/

 

group by：

“groups a set of rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row for each group”

将多行分成子group，使操作针对子group来进行。

---

 

 

触发器

当数据表中的某项数据依赖于另外的数据行的更新的时候，为了同步某项数据,我们可以采用触发器。 
触发器语法：

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中trigger_time取值BEFORE或者AFTER 
trigger_event取值为INSERT、UPDATE或者DELETE 
trigger_stmt可以是一句sql语句，或者采用bengin-end对，中间插入计算逻辑。

例子： 
在《高性能mysql》中有一例，维护hash索引时，采用了触发器

采用DELIMITER //将结束符设为//,这样就可以在触发器语法中使用;了。

 

HAVING：对聚合结果进行筛选。

 

EXPLAIN + SQL ： 可以看到sql语句被查询优化器准备如何执行。

 

---

• 触发器
• 数据库索引
  • 索引的类型
  • 索引的优点
  • 索引的数据结构
    • B-Tree
    • B+Tree
    • 聚簇索引与非聚簇索引

数据库索引

数据库建索引的方式：

索引(在mysql中也叫作”键(key)”)是存储引擎用于快速找到记录的一种数据结构。

索引的类型

• B-树索引 
  InnoDB使用B-树（实际上使用的是B+Tree），MyISAM也使用B-Tree索引。存储引擎1?InnoDB、MyISAM是两种不同类型的存储引擎。 
  B-Tree索引意味着所有的值是按序存储的，查询时采用类似排序树的搜索方式，而不需要全表扫描。 
  适用于 全值匹配，最左前缀匹配，范围值匹配，只访问索引的查询（覆盖索引）。 
  对应sql语句：where的按值查找，orderby，groupby等。

• Hash索引 
  Memory引擎默认索引类型，非唯一Hash索引（链式冲突法） 
  对索引列计算hashcode，在对应的slot位置的value放指向数据实际行的指针。 
  无法用于排序，不支持部分匹配和范围查询。访问速度快。适合特定场合。

• 空间索引(R-Tree)

• 全文索引

 

1. mysql内置几种类型的存储引擎，默认的是InnoDB，还有MyISAM。其它的如Archive、Blackhole、memory等。 ↩

---

 

索引的优点

索引大大减少了服务器需要扫描的数据量。 
索引可以帮助服务器避免排序和临时表。 
索引可以将随机IO变为顺序IO。

“三星”索引

Q：索引一定好吗？ 
A：小表全表扫描，中到大表索引有效，特大型表，可采用分区技术。

高性能索引：

• 独立的列：索引列不能使表达式一部分或者函数参数
• 前缀索引：前缀长度适合，选择性接近完整列为好
• 多列索引：选择合适的索引列顺序
• 聚簇索引：InnoDB支持，在叶子节点中存储了索引列以及剩余的列。默认主键作为聚簇索引的索引列。特点：索引和数据保存在同一个B-Tree结构中。 
  缺点：插入速度严重依赖插入顺序；插入新行或者主键更新需要移动行时，导致页分裂的问题—导致表占用更多的磁盘空间；可能使全表扫描变慢；二级索引需要两次查找B-Tree。 
  与非聚簇索引在物理结构上的差别：非聚簇索引的主键索引和其它索引在结构上没有本质的差别，叶子节点存储数据行的存储位置指针（行指针）；聚簇索引，主键索引上存储索引和行数据，表就是主键索引，而二级索引叶子节点存储主键值（优点是表移动时无需更新二级索引）。
• 覆盖索引：

 

如果一个索引包含（或者覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。

 

• 可以使用索引扫描来做排序

  只有当索引列的顺序和order by顺序完全一致，并且所有列的排序方向都一样时，才能使用索引进行排序。

 

---

 

 

索引的数据结构

参考了网络上的一篇文章,个人认为写的很好。 
在mysql的InnoDB中，索引是通过B-Tree树，准确来说是B+Tree树的数据结构来实现的。B-Tree和B+Tree是怎样的数据结构，有何特点？为什么不用二叉树或者红黑树？为什么说B+Tree比B-Tree更适合在现在的磁盘系统上建立索引？

 

B-Tree

B-Tree是一个高度平衡的树，这点和红黑树有几分类似。 
有如下定义： 
d为大于1的正整数，称为B-Tree的度，其高度称为h。 
节点分为非叶子节点和叶子节点。所有的节点都是由若干个key和指针组成，不过非叶子节点和叶子节点的个数上下限有区别： 
节点包含n-1个key和n个指针，对非叶子节点 ，而对叶子节点 。 
在每个节点中，key和指针是相互间隔的，多个key从左至右非递减排序。 
叶子节点的指针均为null。

d=2的B-Tree示意图。

 

其查找效率很高，在每个节点上进行二分查找，如果找到就返回节点对应的data，否则对相应区间指针指向的节点进行递归查找。 
查找某个节点的渐进复杂度为 , 表示所有的记录数。但是其插入删除需要进行分裂、合并等操作，比较复杂，类似红黑树的思想。

 

B+Tree

与B-Tree相比，B+Tree有以下不同点： 
每个节点的指针上限为2d而不是2d+1。 
内节点不存储data，只存储key；叶子节点不存储指针。

这里我对第二行不理解，B-Tree哪里节点指针上线是2d+1了？

不过这里有很重要的一点变化，就是内节点（非叶子节点）只存储key，不存储data。这是B+Tree比B-Tree更加适合现在的磁盘IO的原因。接下来会详细解释。另外，还有一个改进就是在每个叶子节点中增加了指向相邻的叶子节点的指针，主要是用于区间查找。

磁盘预读原理： 
因为磁盘IO是程序运行中很耗时的一部分，所以现在的操作系统往往采用磁盘预读技术，每次磁盘IO将一个磁盘页整数倍大小的数据都读到内存中来，这样做的原理还有一个就是局部性原理。

在数据库引擎中，利用了磁盘预读原理，将实现B-Tree的一个节点设计为一个页面大小，这样保证一个节点存储在一个页里面，保证一个节点只需要一次IO。 
按照这样的思路，B-Tree一次检索最多需要h-1次IO(根节点是常驻内存的)，而 ,一般d非常大，因此h非常小。

所以B-Tree作为索引结构的效率是非常高的。

为什么不用二叉树、红黑树？ 
因为红黑树h要大的多，再加上逻辑上相邻的节点比如父子节点，在物理存储上可能相聚甚远，这样就可能需要很多次的磁盘IO。

另外从上面可以看出，一个节点的总大小受页面大小的限制，d越大那么我们一次检索需要的IO实际上是越小的，如果将B-Tree内的data域去掉，只保留key和指针，就能将d最大化。因此在B+Tree中就是去掉了内节点中的data域。

 

聚簇索引与非聚簇索引

InnoDB 
InnoDB默认是聚簇索引，其B-Tree的叶子节点的data域保存了完整的数据记录，表数据文件本身就是主索引。

主索引，以数据表主键为key的索引。此外还有二级索引（辅助索引），key不是主键的索引。

在InnoDB中，二级索引的data域存的是主键值，辅助索引的搜索需要两边索引：首先二级索引确定主键，然后主索引检索记录。

MyISAM 
在MyISAM中是不支持聚簇索引的，每个索引基本上是平等的，叶子节点的data域存的是对应数据记录的物理存储地址。

---

 

Mysql 优化实践

需求1：

poseidon-server中，SkuManagerService中，查找某个后台品类对应的所有线上商品时，出现了在循环中调用dao查数据库的操作。

优化：将循环操作变成批量查找。

效果对比： 优化之后，查询backCateId = 1， 耗时：27ms

优化之前：1970ms。

...

 

需求2：

 

---

 

MySql事务

事务特点(ACID) 
atomicity：原子性 
consistency：一致性 
isolation：隔离性 
durability：持久性

事务隔离级别 
未提交读 
提交读 
可重复读（MySql默认隔离级别） 
可串行化