学习数据库索引(mysql)

学习索引 https://mp.weixin.qq.com/s/ofT3JtKSXxEm0NanC98yWQ

/*
索引的优点
1、大大减轻了服务器需要扫描的数据量，从而提高了数据的检索速度
2、帮助服务器避免排序和临时表
3、可以将随机I/O变为顺序I/O

1、主键索引
ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');

2、唯一索引
ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');

3、普通索引
ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');

4、全文索引
ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');

5、组合索引
ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);
*/
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test(
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(30) NOT NULL,
  sex BIT(1) NOT NULL DEFAULT b'1',
  city VARCHAR(50) NOT NULL,
  age INT NOT NULL
)ENGINE = INNODB DEFAULT CHARSET=utf8;
-- 创建索引
ALTER TABLE user_test ADD INDEX idx_user(user_name,city,age);

-- 索引与where后查询条件的顺序有关
-- 匹配最左前缀（user_name）、（user_name, city）、（user_name , city , age）
-- 满足最左前缀查询条件的顺序与索引列的顺序无关，如：（city, user_name）、（age, city, user_name）
SELECT * FROM user_test WHERE user_name = '存在' AND age = 18 AND city = '冷' 


-- 匹配列前缀（匹配列值的开头部分）
-- 匹配范围值（查询用户名以'存'开头的所有用户，这里使用了索引的第一列）
SELECT * FROM user_test WHERE user_name LIKE '存%';


-- 不包含索引列中的最左索引列，则无法使用到索引查询
SELECT * FROM user_test WHERE city = '冷';
SELECT * FROM user_test WHERE age= 28;
SELECT * FROM user_test WHERE city = '核' AND age = '18';


-- where的查询条件是最左索引列，也无法使用索引查询用户名以'在'结尾的用户
SELECT * FROM user_test WHERE user_name LIKE '%在';


-- 如果where查询条件中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询
SELECT * FROM user_test WHERE user_name = '存在' AND city LIKE '冷%' AND age = 18;


-- 索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询
SELECT * FROM user_test WHERE user_name = CONCAT(user_name,'fei');


-- 前缀索引（前缀索引计算 前缀索引要选择足够长的前缀以保证高的选择性，同时又不能太长，我们可以通过以下方式来计算出合适的前缀索引的选择长度值：）
SELECT COUNT(DISTINCT user_name)/COUNT(*) FROM user_test; -- index_column代表要添加前缀索引的列
/*SELECT
COUNT(DISTINCT LEFT(index_column,1))/COUNT(*),
COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),
COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)FROM user_test;

有时候需要索引很长的字符列，这会增加索引的存储空间以及降低索引的效率，一种策略是可以使用哈希索引，
还有一种就是可以使用前缀索引，前缀索引是选择字符列的前n个字符作为索引，这样可以大大节约索引空间，
从而提高索引效率。
*/


-- 前缀索引的创建
ALTER TABLE user_test ADD INDEX idx_test(user_name(2));-- MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描
-- 组合索引（将选择性最高的列放到索引最前列，该法则与前缀索引的选择性方法一致）
/*	
	聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，如果定义了主键，那么InnoDB会通过主键来聚集数据，
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。
	聚集索引可以很大程度的提高访问速度，因为聚集索引将索引和行数据保存在了同一个B-Tree中，所以找到了索引也就相应的找到了对应的行数据，
但在使用聚集索引的时候需注意避免随机的聚集索引（一般指主键值不连续，且分布范围不均匀），如使用UUID来作为聚集索引性能会很差，
因为UUID值的不连续会导致增加很多的索引碎片和随机I/O，最终导致查询的性能急剧下降。
	与聚集索引不同的是非聚集索引并不决定数据在磁盘上的物理排序，且在B-Tree中包含索引但不包含行数据，
行数据只是通过保存在B-Tree中的索引对应的指针来指向行数据，如：上面在（user_name，city, age）上建立的索引就是非聚集索引。
*/
-- 覆盖索引(包含所有要查询的字段的值，那么就称之为覆盖索引)
SELECT user_name, city, age FROM user_test WHERE user_name = '存在' AND age > 17;


-- 使用索引来排序，满足：1 ORDER BY子句后的列顺序要与组合索引的列顺序一致，且所有排序列的排序方向（正序/倒序）需一致
-- 2 所查询的字段值需要包含在索引列中，及满足覆盖索引
SELECT user_name, city, age FROM user_test ORDER BY user_name;
SELECT user_name, city, age FROM user_test ORDER BY user_name, city;
SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;
SELECT user_name, city, age FROM user_test WHERE user_name = '存在' ORDER BY city;


-- 无法使用索引排序的案例
-- 1.sex不在索引列中
SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;
-- 2.排序列的方向不一致
SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;
-- 3.所要查询的字段列sex没有包含在索引列中
SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;
-- 4.where查询条件后的user_name为范围查询，所以无法使用到索引的其他列
SELECT user_name, city, age FROM user_test WHERE user_name LIKE '存%' ORDER BY city;


-- 多表连接查询时，只有当ORDER BY后的排序字段都是第一个表中的索引列（需要满足以上索引排序的两个规则）时，方可使用索引排序
DROP TABLE IF EXISTS user_test_ext;
 
CREATE TABLE user_test_ext(
 
    id INT AUTO_INCREMENT PRIMARY KEY,
 
    uid INT NOT NULL,
 
    u_password VARCHAR(64) NOT NULL
 
) ENGINE=INNODB DEFAULT CHARSET=utf8;
 
ALTER TABLE user_test_ext ADD INDEX index_user_ext(uid);
-- 走索引排序
SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;
-- 不走索引排序
SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

然后学习一波二叉树实现

二叉树条件：

        二叉树是基本是左<根<右

        二叉树的每个结点至多只有二棵子树(不存在度大于2的结点)，二叉树的子树有左右之分，次序不能颠倒。二叉树的第i层至多有2^{i-1}个结点；深度为k的二叉树至多有2^k-1个结点；对任何一棵二叉树T，如果其终端结点数为n_0，度为2的结点数为n_2，则n_0=n_2+1。

下面是递归实现：

	public void insert(Node newNode,Node oldNode){
		if(newNode.data<oldNode.data){
			if(oldNode.left == null)oldNode.left = newNode;
			else insert(newNode,oldNode.left);
		}else if(newNode.data > oldNode.data){
			if(oldNode.right == null)oldNode.right = newNode;
			else insert(newNode,oldNode.right);
		}
	}

        类似以上结构，这样的查询的时间复杂度就是对数以2为底的函数O(logn)，假设查询的是2，可以无需遍历全部，可以直接查询到根节点，发现2<4，然后选择左子树，刚好查到2。这种查询，在每一层都可以分成左子树或右子树，这样就可以是每次查询次数除以2，即为2的幂次方，就是所谓的O(logn)，这里的log下标是2。但是这里还不是索引的结构，索引使用的是B+Tree。

接着学习b-树 附上链接http://mp.weixin.qq.com/s/ecaSI1ScMpYFkprw-2KdUA

        这篇文章讲到，使用二叉树做索引的话，假如二叉树深度特别大(深度即为树的层),每一层都要进行磁盘IO，特别耗时好资源，虽然二叉树时间最短，但是可以做出b-tree这种矮树，减少IO也可以做到查询快。

下面来具体介绍一下B-树（Balance Tree），一个m阶的B树具有如下几个特征：

        1.根结点至少有两个子女。

        2.每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m

        3.每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m

        4.所有的叶子结点都位于同一层。

        5.每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

  好吧，我看的也是懵逼，我上个图

        这样就不一样了，这样若果根节点是两个数的话，那么就不只是每个根节点只有两个子树了，这样可以造出矮胖的数，而且可以尝试查询下，比如：3和5就很直接，若是4，到了3和5，知道4在这两个数的范围内，就定位到了4，当然这样的树，看不出来多好，当层数特别大的时候，那么就越明显了。

        这里由规定叶子节点必须都在一层，也就是说不论树分支到第几层，最下面都是都是在同一层，这样就造成插入数据的不方便，比如插入 6

        这里有一堆连锁操作，很麻烦，然后删除方面还有左旋右旋(红黑树就有)，文章说到mangoBD使用就是b-tree。

再到最后就是b+tree http://mp.weixin.qq.com/s/ecaSI1ScMpYFkprw-2KdUA

一个m阶的B+树具有如下几个特征：

        1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

        2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

        3.所有的中间节点元素都同时存在子节点，在子节点元素中是最大（或最小）元素。

粗暴点，附上图：

        这里的树，除了叶子节点，也就是最下面的节点，其它的数字都不是存储对应的数据，都只是指针(指向正确的位置)，只有叶子节点才是真正的数据，而且叶子节点是连起来的链表，这样方便前后索引继续查找，这里每一层的数字都会出现在下一层，这样做看似重复，其实只是指针，比没有数据的拷贝。注意规则是分出来的左子树小于左边的数字，3和5小于7，1小3，这种方式类推得到树，这里叶子节点才存储“卫星数据”，文章引出来的概念，这里b+tree比b-tree还要矮胖，而且还稳定，叶子节点连起来有排序的链表，可以做范围查询，也就是数据库的范围查询这种样子(从树的顶点到每层根都可以知道下面树的范围<1-11>)。

        这里只是我根据文章加上理解做的笔记，希望看不懂的老友可以直接去看原文，中间很大部分是文章本篇内容，有点不好意思，希望大家点进去看文章顺便关注公众号，公众号里的历史文章好多好学的内容。