MySQL的索引实现原理
MySQL数据库索引总结
1. 使用索引的原由
2. 数据结构：Hash、平衡二叉树、B树、B+树区别
3. 机械硬盘、固态硬盘区别
4. Myisam与Innodb B+树的区别
5. MySQL中的索引什么数据结构
6. B+树中的节点到底存放多少
MySQL官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。
MySQL索引采用的数据结构有哪些？
Hash 平衡二叉树 B树 B+树区别
Hash算法：
哈希表（Hash table，也叫散列表），是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。
优点: 查找可以直接根据key访问
缺点: 不能进行范围查找
index=Hash(key)
比如 通过name 去做hash值
3 = Hash("jack")
4= Hash("Mack")
存放的是存放到数组中
然后 查询时候
select * from table where name = 'jack'; 先计算出hash值 然后根据下表的位置就可以找到了
效率高，但是不能做范围查询 （ hash是个散列的 不能有范围！ 计算出hash值去比较的 如果不是name 是数字的话 hash值散列 没法获取正确的范围）
平衡二叉树：
平衡二叉查找树，又称 AVL树。 它除了具备二叉查找树的基本特征之外，还具有一个非常重要的特点：它 的左子树和右子树都是平衡二叉树，且左子树和右子树的深度之差的绝对值（平衡因子 ） 不超过1。 也就是说AVL树每个节点的平衡因子只可能是-1、0和1（左子树高度减去右子树高度）。
优点: 平衡二叉树算法基本与二叉树查询相同，效率比较高
缺点: 插入操作需要旋转，支持范围查询
通过平衡二叉树建立id的索引，
平衡二叉树，会取一个中间值，中间值左边成为左子树，中间值右边称为右子树
左子树值<中间值<右子树值
如果利用平衡二叉树建立索引的话会生成一个文件
如果有一百万条数据 ，则会有一百万个节点。
查询时候，索引文件加载到内存，然后进行比较。这样容易内存溢出
一般在做的时候：
平衡二叉树查询原理：
假设查询10 （需要经历4次IO操作）
1次 从硬盘中读取4 （内存），判断下10>4，取右指针
2次 从硬盘中读取8 （内存），判断下10>8，取右指针
3次 从硬盘中读取9 （内存），判断下10>，取右指针
4次 从硬盘中读取10 （内存），判断下10=10，定位到数据
查询四次！
平衡二叉树 查询效率还可以，缺点：虽然支持范围查询，但是回旋查询效率低。
规律：如果树的高度越高，那么查询IO次数会越多。
数据结构B树
维基百科对B树的定义为“在计算机科学中，B树（B-tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同，B-树为系统最优化大块数据的读和写操作。B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度。普遍运用在数据库和文件系统。”
因为B树节点元素比平衡二叉树要多，所以B树数据结构相比平衡二叉树数据结构实现减少磁盘IO的操作。
B树对于平衡二叉树做改进
平衡二叉树高度越高 查询IO次数越多
规律： 如果树的高度越高，查询次数越多。如何减少IO次数？ B树在平衡二叉树中，减少树的高度。
明显高度低了
假设查询10 （需要经历3次IO操作）
1次 从硬盘中读取4 （内存），判断下10>4，取右指针
2次 从硬盘中读取6，8 （内存），判断下10>8，取右指针
3次 从硬盘中读取9，10 （内存），判断下10=10 定位到数据
B树比平衡二叉树减少了一次IO操作
结论： B树查询效率比平衡二叉树高，因为B树节点中可以有多个元素，从而减少树的高度，减少IO操作，从而提高查询效率。但是范围查询效率依然比较低
数据结构B+树
B+树相比B树，继承了B数的特征。新增叶子节点与非叶子节点关系，叶子节点中包含了key和value，非叶子节点中只是包含了key，不包含value。
所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高。
通过非叶子节点查询叶子节点获取对应的value。范围查询的效率提高！
缺点： 因为有冗余的节点数据，会比较占硬盘。
结合索引：
key就是建立索引的值
value是地址
B+树 解决范围查询问题、减少IO查询的操
结合数据库，B+树是怎么玩起来的？
MyISAM和InnoDB对B-Tree索引不同的实现方式 主键索引： MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
下图是MyISAM主键索引的
这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出
上图： 以主键建立的索引
叶子节点的key就是主键id value定位到数据库中哪一行的数据的地址
区别：
InnoDB直接存放的数据不是地址是数据，它的value直接就是那一行的数据（但是有大小限制的不可能存放无限制的）！
而MyISam是地址，再通过地址查询行数。
MyISAM和InnoDB对B-Tree索引不同的实现方式 然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同.
MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
索引文件最终是存放在硬盘上的，不是内存哈。有IO操作的
B+树相比B树，新增叶子节点与非叶子节点关系，叶子节点中包含了key和value，非叶子节点中只是包含了key，不包含value。
所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高
注意：MyISAM和InnoDB对B-Tree索引不同的实现方式
MyISAM底层使用B+树 叶子节点的value对应存放行数的地址，在通过行数定位到数据。
InnoDB底层使用B+树，叶子节点的value对应存放是行的data数据，相比MyISAM效率要高一些，但是比较占硬盘内存大小。
最终形成了一个索引文件，是存放在硬盘的。每次查询时候都是在读硬盘。
选择B+树的原因:
B+树索引具有范围查找和前缀查找的能力，相当于二分查找。
Hash索引只能支持等于查询，无法支持范围查询
索引文件如何查看
默认数据与索引文件位置: /var/lib/mysql
MyISAM引擎的文件：
.myd 即 my data，表数据文件
.myi 即my index，索引文件
.log 日志文件。
InnoDB引擎的文件：
采用表空间（tablespace）来管理数据，存储表数据和索引，
InnoDB数据库文件（即InnoDB文件集，ib-file set）：
ibdata1、ibdata2等：系统表空间文件，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用。
.ibd文件：单表表空间文件，每个表使用一个表空间文件（file per table），存放用户数据库表数据和索引。
结合图形化界面：
有好多引擎
进入/var/lib/mysql/test （tes为数据库名称）
对于SQL查询优化
开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
先捕获低效SQL→慢查询优化方案→慢查询优化原则
MySQL数据库配置慢查询
参数说明:
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录
1.查询慢查询配置
show variables like 'slow_query%';
2.查询慢查询限制时间
show variables like 'long_query_time';
3.将 slow_query_log 全局变量设置为“ON”状态
set global slow_query_log='ON';
4.查询超过1秒就记录
set global long_query_time=1;
5.查询cat /var/lib/mysql/localhost-slow.log
service mysqld restart
1、
2、
默认10s没有响应的话 就会记录到下面的文件中去：
正确步骤是
第一步中开启慢查询 ON， 将 slow_query_log 全局变量设置为“ON”状态。
set global slow_query_log='ON';
然后再去修改时间秒数，查询超过1秒就记录 set global long_query_time=1;
关闭当前连接，然后重新连接
查看此文件：
如果满足条件 查询时间很慢 会记录在这里
关于索引失效问题
索引为什么会失效？注意那些事项？
1.索引无法存储null值，where num is null 全表扫描
2.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
3.对于多列索引，不是使用的第一部分，则不会使用索引
4.like查询以%开头
5.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
6.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
7.避免where字句中 ！= 或 <>
8.in 和 not in 也要慎用
创建表：
CREATE TABLE `user_details` (
`id` int(11) ,
`user_name` varchar(50) DEFAULT NULL,
`user_phone` varchar(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
主键索引： id
创建普通索引：
ALTER TABLE `user_details` ADD INDEX user_name_index ( `user_name` )
查看表结构：
DESC user_details； 看到有两个索引id 和 user_name
插入数据:
insert into user_details values(1,'a1','12312312');
insert into user_details values(2,'a2','12312123');
insert into user_details values(3,'a3','15924134');
insert into user_details values(4,'a4','23423423');
insert into user_details values(5,'a5','12352343');
insert into user_details values(6,'a6','35634523');
全表扫描：
优化查询时候 尽量使用索引字段进行查询
两个索引都没有用上
like 不能用到索引哦
普通索引 百分号放后面时候 like 可以用到索引
主键索引不存在模糊查询的 本身就唯一 不存在多个
如果列类型是字符串，一定要在条件中将数据使用引号，否则用不到索引
- 索引无法存储null值
- 前导模糊查询不能利用索引(like '%XX'或者like '%XX%')
- 索引失效的几种情况
1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.对于多列索引，不是使用的第一部分，则不会使用索引
3.like查询以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
联合索引为什么需要遵循左前缀原则？
创建表：
CREATE TABLE `user_details` (
`id` int(11) ,
`user_name` varchar(50) ,
`user_phone` varchar(11) DEFAULT NULL,
PRIMARY KEY (id,user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
主键是id user_name
insert into user_details values(1,'a1','12312312');
insert into user_details values(1,'a2','12312123');
可以插入成功！ 联合主键索引 id+user_name保证唯一就OK了！ 多个
插入如下数据：
insert into user_details values(1,'a1','12312123');
insert into user_details values(1,'a2','12312123');
insert into user_details values(2,'a1','12312123');
insert into user_details values(2,'a2','12312123');
insert into user_details values(3,'a1','12312123');
insert into user_details values(3,'a2','12312123');
如果只有一个条件 user_name
联合主键索引中： 左前缀指的就是第一个索引 id
联合主键索引中，必须要加上左前缀，才会生效？
叶子节点分析 链表，
[1+a1] ---> [1+a2] ----> [2+a1]---> [2+a2]--->[3+a1]--->[3+a2]
select * from user_deatils where id = 1 and user_name = 'a1'
通过前缀就可以查找到叶子，知道有个范围了 “范围”是个亮点！！！
如果不带第一个索引 查询时候 会因为不知道第一个索引是什么，只有第二个，而进行全表查询
因为索引底层采用B+树叶子节点顺序排列，必须通过左前缀索引才能定位到具体的节点范围。
联合主键索引：
分表分库能够提高数据查询效率？
分表分库为什么提高查询的效率？因为会将一张表的数据拆分成多个n张表进行存放，让后在使用第三方中间件（MyCat或者Sharding-JDBC）并行同时查询，让后在交给第三方中间进行组合返回给客户端。
同时操作，就跟多线程一样。
分表分库为什么提高查询的效率？因为会将一张表的数据拆分成多个n张表进行存放，让后在使用第三方中间件（MyCat或者Sharding-JDBC）并行同时查询，让后在交给第三方中间进行组合返回给客户端。
不适合建索引的字段
索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。
因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。 另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
总的来说，小型表肯定不建索引，
或者数据库记录在亿条数据级以上，还是建议使用非关系型数据库。
还有些特殊字段的数据库，比如BLOB，CLOB字段肯定也不适合建索引。
从以下几个方面分析：
1. 创建索引
也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
2. 复合索引
select * from users where area=’beijing’ and age=22; 在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，这样已经相对不做索引时全表扫描提高了效率。但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀 特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
注1：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。
注2：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
MySQL 最左前缀匹配原则（即创建联合索引的使用）
1、在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列col1、列col2和列col3建一个联合索引
KEY index_col1_col2_col3 on test(col1,col2,col3);
联合索引 index_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
SELECT * FROM table WHERE col1="1" AND clo2="2" AND clo4="4"
上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。索引的字段可以是任意顺序的。
使用联合索引的好处
- 减少开销。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
- 效率高。索引列越多，通过索引筛选出的数据越少。
3.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
4.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
5.排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
6.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%a%” 不会使用索引而like “aaa%”可以使用索引。
7.不要在列上进行运算
select * from users where YEAR(adddate)
8.不使用NOT IN和操作
NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id
索引的一些分类：
1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用
谈一谈InnoDb索引的使用，一直以来项目开发用的都是InnoD引擎，而innodb使用的是b+tree进行存储，数据存放在子节点上
多列索引如何决定顺序
- 选择性高的放在最左边
- 把长度小的列放在左侧(单页可以容纳的数据更多，减少io)
- 使用最频繁的放在左侧
组合索引的生效原则是 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用
比如
where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用； where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果 where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果； where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关
（a,b,c） 三个列上加了联合索引（是联合索引 不是在每个列上单独加索引）
注：， (a,b,c)多列索引和 (a,c,b)是不一样的
复制代码 (0) select * from mytable where a=3 and b=5 and c=4; abc三个索引都在where条件里面用到了，而且都发挥了作用 (1) select * from mytable where c=4 and b=6 and a=3; 这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样 (2) select * from mytable where a=3 and c=7; a用到索引，b没有用，所以c是没有用到索引效果的 (3) select * from mytable where a=3 and b>7 and c=3; a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引 (4) select * from mytable where b=3 and c=4; 因为a索引没有使用，所以这里 bc都没有用上索引效果 (5) select * from mytable where a>4 and b=7 and c=9; a用到了 b没有使用，c没有使用 (6) select * from mytable where a=3 order by b; a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的 (7) select * from mytable where a=3 order by c; a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort (8) select * from mytable where b=3 order by a; b没有用到索引，排序中a也没有发挥索引效果
索引是一种数据结构：
索引采用的B+树，为何用B+树呢？
引入二叉查找树：这个约束性很差 可能会出现链表的样子！
平衡二分查找树：
种类2：
二叉树缺陷：
1. 搜索效率问题：树的深度决定了搜索时候IO的次数。
2. 内容数量问题：
每一个磁盘块（节点/页）保存的关键字数量太少了。
没有利用好操作系统和磁盘的数据交互特性，操作系统和磁盘进行数据交互.也没有利用好磁盘IO预读能力（空间局部性原理）
备注：交换的单位是页 4k （ssd 4k对齐)做一次IO 加载4k 浪费资源！
多路平衡查找树：
B树的变种，MySQL的B+树：
B+树和B-树的区别
B+树的关键字搜索采用闭合区间
B+树的非叶子节点不保存数据相关信息，只保存关键字和子节点引用。
B+树关键字对应的数据保存在叶子节点中
B+树叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。
InnoDB:
InnoDB VS MyISAM:
2. 联合索引
单列索引： 节点中关键字【name】
联合索引： 节点中关键字 【name, sex, mobile】
注： 单列索引是特殊的联合索引
联合索引列选择原则：
1.经常用的列优先(最左匹配原则)
2.离散度高的优先(离散度高原则)
3.宽度小的列优先(最少空间原则)
最左匹配原则：
比如经常用到：
SELECT * FROM `user` WHERE `name` = "";
SELECT * FROM `user` WHERE `name`= "" AND mobile = ""
则应该这样创建索引：
create index idx_name_mobile on user (name, mobile)
覆盖索引：
- 解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
- 解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
- 解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。
