索引索引 --索引是有序的数据结构 https://blog.csdn.net/mu_wind/article/deta

索引 --索引是有序的数据结构

blog.csdn.net/mu_wind/art…

优点:加速查找,唯一索引保证唯一性,加速表连接,加速排序缺点:额外空间和资源,动态维护

索引的数据结构

hash:速度快且唯一,但不支持范围查找
二叉树:每个节点都有data用来存储数据,且层高太高,定位数据比较慢
B树: 增加二叉树每层数量,减少层高(每个节点还是包含data结构)
B+树:改造B树后得到mysql使用的索引结构,数据只出现在叶子节点,所有叶子节点增加了一个链指针,非叶子结点去掉data结构,同一层可以存储更多节点,结构更扁平,查找次数更少(树的高度每增加一层，查询时的磁盘I/O次数就增加一次),最多不超过4层

1.先找到根节点所在磁盘块，读入内存。（第1次磁盘I/O操作,其实InnoDB引擎是将根节点常驻内存的，第1次磁盘I/O操作并不存在） 2.在内存中判断id=3所在区间(0,8)，找到该区间对应的指针1（第1次内存查找） 3.根据指针1记录的磁盘地址，找到磁盘块2并读入内存（第2次磁盘I/O操作） 4.在内存中判断id=3所在区间(0,4)，找到该区间对应的指针2（第2次内存查找） 5.根据指针2记录的磁盘地址，找到磁盘块4并读入内存（第3次磁盘I/O操作） 6.在内存中查找到id=2对应的数据行记录（第3次内存查找）

减少磁盘访问时数据库的性能优化的主要手段

聚集索引和非聚集索引

1.聚集索引(数据放在索引上) : innodb的主键索引就是聚集索引,数据都存在叶子节点上. 2.非聚集索引(索引和数据分开放) : 普通索引,在索引B+树上只存储索引相关的字段和主键,需要拿到主键回表才能拿到整行数据.

索引覆盖:联合索引上有要查询的字段时,不需要回表.

MYISAM和INNODB

MYISAM的表数据,表结构,索引是分3个文件存放的索引上记录的是内存地址:根据内存地址找到硬盘上的数据

INNODB的只分表索引,表结构2个文件

面试题

问: innodb 3层 b+树可以存储多少行数据?

一页:innodb最小存储单位是页,和操作系统有关,假设为16K,所有索引最大为1页一块:4K 一个扇区:512 byte

假如2层: 跟节点数量:主键类型bigint为8个字节,指针地址存储需要6个字节,根节点数量为 16K/14B = 1170 叶子节点:假如每行数据为1KB,一页存储16行数据因此:2行可存储161170行数据,3层可存储161170*1170行数据,约2000多万行

索引失效

索引列上有计算,使用函数

explain select * from user where id=1;
explain select * from user  where SUBSTR(height,1,2)=17;

字段类型不同

explain select * from user where code=101; //code是字符类型,赋值为101的int索引会失效

explain select * from user where height='175';//数字类型的字段使用字符,会自动转换

select 1 + '1'; //等于2

select concat(1,'1');//拼接字符

列对比

explain select * from user where id=height

id 和 height 都有索引,列对比还是索引失效

or or所有的字段必须有索引

//id height 有索引时,会有索引融合的效果,索引生效
explain select * from user where id=1 or height='175';

//or只要有1个字段是无索引的,索引失效
explain select * from user where id=1 or height='175' or address='成都';

in 一般是可以走索引的

对于 in 查询来说，会先执行子查询，如上边的 t2 表，然后把查询得到的结果和外表 t1 做笛卡尔积，再通过条件进行筛选

//伪代码如下
for(x in A){
    for(y in B){  //内表小时效率高,循环次数少
    	if(condition is true) {result.add();}
    }
}

in (个数) :单个时,索引是const级别 ,多个时是range级别,再多时会失效 mysql认为使用全表扫描要比使用索引快, 索引失效

not in, != 一般不会走索引 ,某些情况下mysql在默写时候可以优化成走索引 not in (个数=1) ,也会走索引

not in 必须保证匹配的字段是非空的,否则返回的整个结果集为空。

exists 会走索引

对于 exists 来说，是先查询遍历外表 t1 ，然后每次遍历时，再检查在内表是否符合匹配条件 , exists 返回的结果是一个 boolean 值 true 或者 false,适合外表

外表为驱动,不走索引,会全表扫描,exists , not exists时,可以走内表索引

explain select * from user  t1
where  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
//select  1 > column > *

//伪代码如下
for(x in A){  //循环外表,外表小时效率高
	if(exists condition is true){result.add();}
}

in not in走不走索引

EXPLAIN SELECT * FROM gb_gift_orde WHERE id in (1);

const级别

EXPLAIN SELECT * FROM gb_gift_orde WHERE id in (1,2...);

range级别,随着in的数量/总行数比值增大变成ALL级别,可能优化成 id = 1 or id = 2...

not in 主键容易被优化成走索引,普通索引都不走索引 EXPLAIN SELECT * FROM gb_gift_order WHERE id not in (2,3,4); //range EXPLAIN SELECT * FROM gb_gift_order WHERE customer_id not in (1); //all

使用limit可以提升命中率 EXPLAIN SELECT * FROM gb_gift_order WHERE customer_id not in (1) limit 100; //range

in 和 exists 孰快孰慢

-- 此为 5.7 写法，如果是 5.6版本，需要用 explain extended ...
explain select * from t1 where id in (select id from t2);
-- 本意为显示警告信息。但是和 explain 一块儿使用，就会显示出优化后的sql。需要注意使用顺序。
show warnings;

in语句会被优化成join语句来执行,因此子查询的查询所以等级为eq_ref

结论: 外层大表内层小表，用in, 外层小表内层大表，in和exists效率差不多(甚至 in 比 exists 还快，而并不是网上说的 exists 比 in 效率高)

not in 和 not exists 孰快孰慢

小表做外表的情况下。对于主键来说， not exists 比 not in 快, 对于普通索引来说， not in 和 not exists 差不了多少，甚至 not in 会稍快。

大表做外表的情况下，对于主键来说， not in 比 not exists 快。对于普通索引来说， not in 和 not exists 差不了多少，甚至 not in 会稍快。