MySQLDEFAULT 关键字 mysql code关键字

一、为什么要使用索引？

为了减少IO次数

二、为什么不使用二叉树？

减少IO次数。

我们创建的索引可能有几个G那么大，不可能一次性都加载到内存中，需要逐一加载磁盘页（一个磁盘页对应一个节点），二叉树的最坏查询复杂度取决于树的高度。数据量越大，二叉树越“高”，使用B+树而不使用二叉树的原因在于将一棵“瘦高树”替换为“矮胖树”。

三、实现索引的数据结构B（balance）+Tree

• mysql默认存储引擎：InnoDB
• 叶子节点：一棵树当中没有子结点（即度为0）的结点称为叶子结点，简称“叶子”。又称为终端结点

mysql的数据都保存在磁盘上，创建索引的作用是为了快速定位磁盘块的位置，减少磁盘的访问次数，（访问一次内存的速度远快于访问一次磁盘）

系统磁盘以磁盘块为基本单位，InnoDB有页 的概念，页默认大小是16KB，而系统磁盘块达不到这么大，所以取N个地址连续的磁盘块作为一页。

• 两个指针：一个指向根节点，另一个指向最小的叶子节点
• 若一个节点有N个子树，那么此节点就包含N个关键字
• 叶子节点：所有的叶子节点中包含全部的关键字信息。叶子节点本身按照关键字的大小自小而大，按照顺序排列；叶子节点包括①关键字②指向含这些关键字记录的指针（指针指向该条记录在磁盘上的位置）
• 非终端节点：仅包含其子树中最大/最小的关键字

在mysql中，‘%xxx’、‘%xxxx%’均无法使用索引，‘xxx%’（叶子节点形成有序链表，可以使用范围查询）可以使用索引。

• B+Tree索引并不能找到一个给定值的具体行，而是找到具体行所在的页，通过数据库把页读入到内存中，然后在内存中进行查找，最后找到目标行。
• 组合索引的使用

--创建组合索引
alter table test add index index_common_name (common_name,name_code)
结论
1、两个字段的前后使用顺序不限制(a、b)
2、两个字段和其他字段组合查询时顺序也不受限制(c、d、e)
3、like使用索引注意百分号的位置(w)
4、只使用其中一个字段查询时，索引无法使用(f、g)
--a
explain select * from test where common_name = '(普通)河乌' and name_code = 'e09235e5-88da-4db3-8ff5-ce069debb192'
1	SIMPLE	test	ref	index_common_name	index_common_name	904	const,const	1	Using where
--b
explain select * from test where  name_code = 'e09235e5-88da-4db3-8ff5-ce069debb192' and common_name = '(普通)河乌' 
1	SIMPLE	test	ref	index_common_name	index_common_name	904	const,const	1	Using where
--w
explain select * from test where  name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' and common_name = '(普通)河乌' 
1	SIMPLE	test	range	index_common_name	index_common_name	904	(null)	1	Using where
--c
explain select * from test where  name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' and common_name = '(普通)河乌'  and language = 'Chinese'
1	SIMPLE	test	range	index_common_name	index_common_name	904	(null)	1	Using where
--d
explain select * from test where   language = 'Chinese' and name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' and common_name = '(普通)河乌' 
1	SIMPLE	test	range	index_common_name	index_common_name	904	(null)	1	Using where
--e
explain select * from test where name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' and  language = 'Chinese'   and common_name = '(普通)河乌' 
1	SIMPLE	test	range	index_common_name	index_common_name	904	(null)	1	Using where
--f
explain select * from test where   language = 'Chinese' and name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' --w无法使用索引
1	SIMPLE	test	ALL	(null)	(null)	(null)	(null)	29360	Using where
--g
explain select * from test where  name_code = 'e09235e5-88da-4db3-8ff5-ce069debb19' 
1	SIMPLE	test	ALL	(null)	(null)	(null)	(null)	29360	Using where

多个索引的使用

--添加索引
alter table test add index index_common_name (common_name)
alter table test add index index_common_name_2 (name_code)
结论
1、两个索引可以组合使用
2、索引列可以和任何非索引列组合查询，索引生效
--a
explain select * from test where common_name = '(普通)河乌' and name_code = 'e09235e5-88da-4db3-8ff5-ce069debb192'
id      select_type     possible_keys          possible_keys            key                     key_len    
1	SIMPLE	test	ref	index_common_name,index_common_name_2	index_common_name	752	const	1	Using where
--b
explain select * from test where  name_code = 'e09235e5-88da-4db3-8ff5-ce069debb192' and common_name = '(普通)河乌' 
1	SIMPLE	test	ref	index_common_name,index_common_name_2	index_common_name	752	const	1	Using where
--w
explain select * from test where  name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' and common_name = '(普通)河乌' 
1	SIMPLE	test	ref	index_common_name,index_common_name_2	index_common_name	752	const	1	Using where
--c
explain select * from test where  name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' and common_name = '(普通)河乌'  and language = 'Chinese'
1	SIMPLE	test	ref	index_common_name,index_common_name_2	index_common_name	752	const	1	Using where
--d
explain select * from test where   language = 'Chinese' and name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' and common_name = '(普通)河乌' 
1	SIMPLE	test	ref	index_common_name,index_common_name_2	index_common_name	752	const	1	Using where
--e
explain select * from test where name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' and  language = 'Chinese'   and common_name = '(普通)河乌' 
1	SIMPLE	test	ref	index_common_name,index_common_name_2	index_common_name	752	const	1	Using where
--f
explain select * from test where   language = 'Chinese' and name_code like 'e09235e5-88da-4db3-8ff5-ce069debb19%' 
1	SIMPLE	test	range	index_common_name_2	index_common_name_2	152	(null)	7	Using where
--g
explain select * from test where  name_code = 'e09235e5-88da-4db3-8ff5-ce069debb19' 
1	SIMPLE	test	ref	index_common_name_2	index_common_name_2	152	const	1	Using where

四、B树：文件系统或MongoDB索引

五、引用

小灰：漫画：什么是B+树？