数据库（Mysql索引原理）
一、索引定义
1.1 什么是索引：
索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
参考：https://www.cnblogs.com/Eva-J/articles/10126413.html
二、Mysql数据库的储存结构(B+树)
2.1 二叉搜索树(AVL树)
平衡二叉搜索树(Self-balancing binary search tree)又被称为AVL树(有别于AVL算法),且具有以下性质:它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1
2.2 平衡树(B树)
aa
2.3 平衡树(B+树)特点：
b+树是b树的plus版本，集成了更多的特性
平衡树能够提高读的速度,但是降低了写的速度
磁盘查找(IO)的效率高于b树->b数据根节点或者分支节点存储有数据，磁盘加载(和预加载)数据库页默认为16Kb，如果使用b+，则16Kb内存全部存放的关键字指针，没有存储数据浪费空间(数据存放在叶子节点)
基于索引的表扫描性能高于b树，因为b+树数据都在叶子节点(双向链表)->索引(按一定规则的排序的数据结构)指针直接指向叶子链表的地址，从而拿到数据
排序能力更强于b树，因为b+树数据都在叶子节点(双向链表)->然而b树叶子或者根节点存在数据，需要去遍历获取数据后再实现排序
基于索引的查询b+更趋于稳定，例如：查询到当前关键字在根节点，继续向子节点遍历，直到叶子节点关键字(获取关键字数据区数据)
2.4 数据库存储特点：
1、所有的数据都存储在叶子节点上而不是存储在根节点或者分支中
2、在叶子节点之间添加了双向链表,让范围查询变得更加容易，更加高效
3、数据的比较过程采用左闭合的比较方式
2.5 影响查找速度的最根本的原因是什么？
树的高度 --> IO次数
2.6 如何降低树的高度，提高索引效率：
1、增加数据与数据之间的区分度
2、降低目录列的数据的长度
三、 索引原理(聚集索引与辅助索引)
数据库引擎的相关参考：https://www.cnblogs.com/renzherushe/p/4780226.html
3.1 myisam数据引擎：
MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址(因为myisam索引结构和数据分两个文件存放的，需要通过存储结构data域存放的地址指针获取到数据文件中指针地址对应的数据)
3.2 innodb数据引擎：
1、 特点
数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），只用主键才是聚簇索引(聚集索引)，其他索引都是非聚簇索引(非聚集索引)
聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引(主键索引)通过b+树特性，因为innodb引擎索引结构和数据在同一个文件中(数据存储在b+树叶子节点的数据区)，直接遍历获取叶子节点数据区数据。如果是非聚集索引，则需要通过非聚集索引的数据结构，查询叶子节点中数据区存储的聚集索引(主键索引)的关键字(id)，通过再次回表实现主键索引查询(关键
innodb引擎的主要索引结构是聚集索引，索引为维持b+树的平衡特性，元素操作(增，删，改)会存在合并和分裂操作(指针存在变动)，导致非聚集索引叶子节点不能直接存放聚集索引叶子节点数据区地址指针，而是存放关键字(id)，通过回表查询数据。
2、 聚集索引与辅助索引的区别：
聚集索引与辅助索引的区别
聚集索引(聚簇索引)
1.纪录的索引顺序与物理顺序相同
因此更适合between and和order by操作
2.叶子结点直接对应数据
从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引(聚集索引储存主键)
4.只有innodb存储引擎才有聚集索引
非聚集索引(辅助索引)
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容
多个索引会影响insert和update的速度
4.innodb中和myisam中都可以存在
3.3 列的离散度：
离散度(列数据的相同程度)在不超过全表的10%-15%的前提下索引才可以显示其所具有的价值。当离散度超过该值的情况下全表扫描可能反倒比索引扫描更有效。
四、MySQL索引管理
4.1 认识mysql中的key：
- index key 普通索引,能够加速查询,辅助索引
- unique key 唯一 + 索引,辅助索引
- primary key 唯一 + 非空 + 聚集索引
- foreign key 本身没有索引的,但是它关联的外表中的字段是unique索引
4.2 总结：
- 主键作为条件的查询如果能够让索引生效那么效率总是更高
- primary key(聚集索引) 和unique(辅助索引) 标识的字段其本身就是索引结构，不需要再添加新的索引
- 直接就可以利用索引加速查询，能用unique的时候尽量不用index
- 对索引项中关键字的对比，一定是从左往右依次进行的
4.3 常用索引：
点击查看代码
普通索引INDEX：加速查找
唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）
联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
1、联合索引
联合索引是一个索引
联合索引遵循最左前缀原则(最常用列 > 离散度高 > 最少空间)
单列索引是一种特殊的联合索引
覆盖索引是解决回表查询的一种重要手段。例如存在联合索引(name+phoneNum)，则select phoneNum from table1 where name=?则不需要回表，可直接通过联合索引查询需要的phoneNum信息
2、三星索引
第一颗星：where字句中条件匹配的索引关键字中列越多扫描的数据越少
第二颗星：避免再次排序。例如：select * from table1 where age > 12 order by id desc 需要先通过非聚集索引age查询到结果，通过物化再次排序才能返回排序后的结果；如果直接使用select * from table1 where age > 12 order by age desc则不需要从新排序(b+树叶子节点链表特点)，直接回表查询后返回查询结果
第三颗星：尽可能运用到覆盖索引进行数据的扫描，减少回表IO操作
4.4 创建/删除索引的语法：
语法结构
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;
示例
#方式一
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id), # 唯一索引
index ix_name(name) # index没有key(index:普通索引)
);
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index(name) # index没有key
);
#方式二
create index ix_age on t1(age); # 创建普通索引
#方式三
alter table t1 add index ix_sex(sex); # 指定添加的索引名(ix_sex)
alter table t1 add index(sex); #不指定索引名(默认：sex)
#查看
mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
UNIQUE KEY `uni_id` (`id`), # 索引：uni_id
KEY `ix_name` (`name`), # 索引：ix_name
KEY `ix_age` (`age`), # 索引：ix_age
KEY `ix_sex` (`sex`) # 索引：ix_sex
) ENGINE=InnoDB DEFAULT CHARSET=latin1
4.5 索引的特点与注意事项：
点击查看代码
# 索引的特点：
# 1.条件一定是建立了索引的字段,如果条件使用的字段根本就没有创建索引,那么索引不生效
# 2.如果条件是一个范围,随着范围的值逐渐增大,那么索引能发挥的作用也越小
# 3.如果使用like进行模糊查询,那么使用a%的形式能命中索引,%a形式不能命中索引
# 4.尽量选择区分度高的字段作为索引列
# 5.索引列不能在条件中参与计算,也不能使用函数
# 6.在多个条件以and相连的时候,会优点选择区分度高的索引列来进行查询
# 在多个条件以or相连的时候,就是从左到右依次判断
# 7.制作联合索引
# 1.最左前缀原则 a,b,c,d 条件是a的能命中索引,条件是a,b能命中索引,a,b,c能命中,a,c.... 只要没有a就不能命中索引
# 如果在联合查询中,总是涉及到同一个字段,那么就在建立联合索引的时候将这个字段放在最左侧
# 2.联合索引 如果按照定义顺序,从左到右遇到的第一个在条件中以范围为条件的字段,索引失效
# 尽量将带着范围查询的字段,定义在联合索引的最后面
# 如果我们查询的条件总是多个列合在一起查,那么就建立联合索引
# 创建联合索引：
# create index ind_mix on s1(id,email)
# 示例：
# select * from s1 where id = 1000000 命中索引
# select * from s1 where email = 'eva1000000@oldboy' 未命中索引
# 但凡是创建了联合索引,那么在查询的时候,再创建顺序中从左到右的第一列必须出现在条件中
# select count(*) from s1 where id = 1000000 and email = 'eva10%'; 命中索引
# select count(*) from s1 where id = 1000000 and email like 'eva10%'; 可以命中索引
# 范围类型示例:
# select * from s1 where id >3000 and email = 'eva300000@oldboy'; 不能命中索引
# 8.条件中涉及的字段的值必须和定义表中字段的数据类型一致,否则不能命中索引
其他注意事项
- 避免使用select *
- 使用count(*)
- 创建表时尽量使用 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
4.6 覆盖索引：
InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录-->不需要回表
辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。所以尽量避免回表操作，例如不使用select * from table
4.7 合并索引：
点击查看代码
合并索引:
1、当我们为单独的一列创建索引的时候
如果条件是这一列,且使用正确就可以命中索引
2、当我们为两列分别创建单独的索引的时候
如果这两列都是条件,那么可能只能命中期中一个条件
如果这两列都是条件,那么可能会命中两个索引 - 合并索引
3、我们为多列直接创建联合索引
条件命中联合索引
五、查询优化神器—explain
点击查看代码
执行计划：让mysql预估执行操作(一般正确)
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
id,email
慢：
select * from userinfo3 where name='alex'
explain select * from userinfo3 where name='alex'
type: ALL(全表扫描)
select * from userinfo3 limit 1;
快：
select * from userinfo3 where email='alex'
type: const(走索引)
文章来源 https://www.cnblogs.com/WiseAdministrator/articles/10777149.html
浙公网安备 33010602011771号