数据库索引
一.什么是索引
索引是mysql的一种数据结构，这种数据结构称之为key，大白话说索引就是一种数据的组织方式
表中的一行行数据按照索引规定的结构组织成了一种树形结构，该树叫B+树
二.为何要用索引
优化查询速度
三.如何正确看待索引
错误的认知
1.软件上线之后，运行了一段时间，发现软件运行很卡，想到要加索引。火烧眉毛再想着加索引，光把问题定位到索引身上就耗费很长时间，排查成本很高
最好是在软件开发之初配合开发人员，定位常用的查询字段，然后为该字段提前创建索引
2.索引越多越好
索引是用于加速查询的，降低写效率
如果某一张表的ibd文件中创建了很多颗索引树，意味着很小一个update语句就会导致很多颗索引树都需要发生变化，从而把硬盘io打上去
四.储备知识
1.索引根本原理就是把硬盘io次数降下来
为一张表中的一行行记录创建索引就好比为书的一页页内容创建目录
有了目录结构之后，我们以后的查询都应该通过目录去查询
2.一次磁盘io带来的影响
7200转/分钟，120转/s
一次io的延迟时间=平均寻道时间(5ms)+平均延迟时间(4ms)——>9ms
3.磁盘预读
一页就是一个磁盘块
innodb存储引擎一页16k，即一次io读16k到内存中
五.索引分类
六.创建索引的俩个步骤
create index xxx on user(id);
1.提取索引(id)字段的值当作key，value就是对应的本行记录
10————>10zs
7————>7ls
13————>13ww
2.以key值为基础比较大小，生成树型结构
七.B+树
二叉树—>平衡二叉树—>B树—>B+树(叶子节点间也具有相互指向的指针)
二叉树：以索引字段key值做比较，右子树总比左子树大，会形成棍形，树的高度很高，io次数就多，查询效率不高
平衡二叉树：以索引字段key值做比较，左子树跟右子树的高度差不超过1，相比二叉树变矮变胖，优化查询效率，但每一叶放一行数据，对于海量数据效果不显著
B树：每个节点放key以及对应的value值，叶子节点内指针相互指向，等量数据下，B树比平衡二叉树矮了很多
B+数：1.非叶子节点只放key，叶子节点放key以及对应的value值—>非叶子节点能存放的key的个数变多，衍生出的指针越多，树会变得更矮更胖
2.叶子节点彼此之间有双向链表—>范围查询速度快，例如：where id>3,先找到id=4的叶子节点，然后只需要根据叶子节点的链表指向向右查找下一个叶子节点即可，不需要再回到根节点查找
3.叶子节点内的key值是单向链表，叶子节点与叶子节点之间是双向链表，即全部排好序了——>排序很快
4.一个页，一个磁盘块，一个节点固定大小16k，可以存放的数据量很多，按照每个节点存放1000数据来算，3层B+树可以组织：1000^3=10亿数据
leaf node：叶子节点
non-leaf node：根节点，树枝节点
create index xxx on user(id)
八.B+树分类
聚集索引，聚簇索引，主键索引：以主键字段值为key构建的B+树，该B+树的叶子节点key对应的value值是整行完整内容
即：表中的数据都聚集在叶子节点，所以称之为聚集索引
select * from user where id='2'
非聚集索引，非聚簇索引，辅助索引，二级索引：以非主键字段值为key构建的B+树，该B+树的叶子节点key所对应的value值是所对应的主键字段值
create index yyy on user(name);
select * from user where name="xxxx"
ps:一张innodb存储引擎表中必须要有且只能有一个聚集索引，但是可以多个辅助索引
九.覆盖了索引，回表操作
覆盖了索引：在命中索引的基础上，只在本索引树的叶子节点就找到了我们想要的数据
主键索引—>id字段
辅助索引—>name字段
select id,name from user where name="egon"；
select * from user where id=3；
回表操作：在命中辅助索引的基础上，在辅助索引的叶子节点并没有找到想要的完整数据，需要拿着对应的主键字段值去聚集索引再找一下
主键索引—>id字段
辅助字段—>name字段
select name,age,gender from user where name="egon"；
十.索引管理
create table t1(
id int,
class_name VARCHAR(10),
name VARCHAR(16),
age INT
);
创建主键索引:
alter table t1 add PRIMARY key t1(id);
alter table t1 DROP PRIMARY KEY;
创建唯一索引：
alter table t1 add UNIQUE key t1(class_name);
alter table t1 drop INDEX t1;
创建普通索引：
create index xxx on t1(name);
drop INDEX xxx on t1;
注：通常情况下 ，在建表之前建立索引
create table t1(
id int PRIMARY KEY auto_increment,
class_name VARCHAR(10) UNIQUE,
name VARCHAR(16),
age INT
);
十一.联合索引与最左前缀匹配原则
create index zzz on t1(id,name,age)；
应用场景，查询条件中出现
id name age
id name
id age
id
十二.如何正确使用索引
1.以什么字段的值为基础构建索引
最好是不为空，唯一，占用空间小的字段
2.针对全表查询语句如何优化
应用场景：用户想要浏览所有的商品信息
select count(id) from s1;
如何优化：
开发层面分页查找，用户每次看从现数据中拿
3.针对等值查询
以重复度低字段为基础创建索引加速效果明显
create index xxx on s1(id);
select count(id) from s1 where id = 3;
以重复度高字段为基础创建索引加速效果不明显
create index yyy on s1(name);
select count(id) from s1 where name="egon"; --速度极慢
select count(id) from s1 where name!="egon"; --速度极快
select count(id) from s1 where name="mm"; --速度快
以占用空间大字段为基础创建索引加速效果不明显
总结：给重复度低且占用空间小的字段值为基础构建索引
4.关于范围查询
select count(id) from s1 where id=33;
select count(id) from s1 where id>33;
select count(id) from s1 where id>33 and id<36;
select count(id) from s1 where id>33 and id< 1000000;
总结：1.innodb存储能加速范围查询，但是查询范围不能特别大
2.> >=，< <=，！=，between and，like 后的内容%应该往右放，并且左半部分的内容应该尽量精确
5.关于条件字段参与运算
select count(id) from s1 where id*12 = 10000;
select count(id) from s1 where id = 10000/12;
select count(id) from s1 where func(id) =10000/12；
总结：不要让条件字段参与运算，或者说传递给函数
6.索引下推技术
对于连续多个and的条件，mysql的优化器会分析出多条执行方案，选取最优的一种，即先找到某一个条件把范围缩小
select count(id) from s1 where name = "egon" and email = "egon1@asds" and gender="male";
select count(id) from s1 where name = "egon" or email = "egon1@asds" or gender="male";
总结：1.and连接的多个条件，锁定的是一个小范围，mysql的优化器会从and条件中选取一个最准确的来优先缩小范围
2.or连接的多个条件，锁定的是一个很大的范围，mysql优化没办法了，只能从左到右依次判断条件
7.联合索引与最左前缀匹配原则
什么时候创建联合索引，条件中需要用到多个字段，并且多次查询中的多个字段都包含某一个字段
需要注意的问题：重复度低且占用空间较小的字段应该尽量往左放，让其成为最左前缀
create index xxx on s1(name,age,gender);
最左前缀匹配原则：
and连接的多个条件中有
name age gender
name age
name gender
name
8.比较类型必须保持一致：id = 10 不要 id = '10'
