啥是索引
索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。
索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。
索引常见模型
举3个🌰 : hash表 、 有序集合 、🌲
哈希表是一种以key-value存储数据的结构
利: 等值查询
弊: 范围查询 没有任何顺序关系 -> 全表扫描
问: hash碰撞是咋整的?
先问问小泉
$this->狗亮亮曾经分享给我的拉链法
typedef struct _hashtable {
uint nTableSize; // hash Bucket的大小，最小为8，以2x增长。
uint nTableMask; // nTableSize-1 ， 索引取值的优化
uint nNumOfElements; // hash Bucket中当前存在的元素个数，count()函数会直接返回此值
ulong nNextFreeElement; // 下一个数字索引的位置
Bucket *pInternalPointer; // 当前遍历的指针（foreach比for快的原因之一）
Bucket *pListHead; // 存储数组头元素指针
Bucket *pListTail; // 存储数组尾元素指针
Bucket **arBuckets; // 存储hash数组
dtor_func_t pDestructor; // 在删除元素时执行的回调函数，用于资源的释放
zend_bool persistent; //指出了Bucket内存分配的方式。如果persisient为TRUE，则使用操作系统本身的内存分配函数为Bucket分配内存，否则使用PHP的内存分配函数。
unsigned char nApplyCount; // 标记当前hash Bucket被递归访问的次数（防止多次递归）
zend_bool bApplyProtection;// 标记当前hash桶允许不允许多次访问，不允许时，最多只能递归3次
#if ZEND_DEBUG
int inconsistent;
#endif
} HashTable;
有序集合
利: 等值查询、范围查询(2分法) 都挺好
弊: 适用静态存储引擎. 更新数据,维护结构成本高
🌲
多路平衡查询树 节点是天然有序的 左子<父<右子
InnoDB中:
一个索引对应一颗B+🌲
那么..........子叶节点 可以存储what?
1)整行数据 -> 主键索引 聚簇索引
2)主键值 -> 非主键索引 二级索引 非聚簇索引
聚簇索引和非聚簇索引，在查询数据的时候有区别吗？
🌰🌰🌰🌰🌰🌰
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;
select * from T where id=500
select * from T where k=5
select id from T where k=5
image.png
索引维护:
维持多路平衡 维持索引的有序性
上边🌰, 新增一个id: 700 在R5 后继续新增就行了 . 如果插入一个ID:400的 就很烦了
更闹心的是页分裂, 如果R5所在的数据页满了,这时候根据B+树算法,就需要再申请一个数据页,然后挪部分数据过去 这又造成了数据页的利用率问题 当两个相邻数据页有数据删除 利用率很低时,会产生页分裂的逆过程 页的合并
绝大情况 :NOT NULL PRIMARY KEY AUTO_INCREMENT。 (只有一个索引 且是唯一索引)
新增记录 追加操作 避免页分裂
从性能和存储空间方面考量，自增主键往往是更合理的选择。
身份证主键 还是自增id?
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
小泉听懂了么? 对下面两个情况 你有啥理解?
重建索引k
alter table T drop index k;
alter table T add index(k);
重建主键索引
alter table T drop primary key;
alter table T add primary key(id);
重建索引k是合理的
而重置主键 就坑了 不论删除主键还是创建主键都会重建整个儿表.
不如用alter table T engine=InnoDB 为啥? 小泉回答
(alter table t engine=innodb,ALGORITHM=inplace;) 下期再见
为啥重置索引 索引可能因为删除、页分裂的原因,导致数据页空洞.重建索引的过程会创建一个新的索引 把数据按顺序插入. 这样数据页的利用率高 提高了索引的紧凑、更省空间
谈下一话题
mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
image.png
回表:回到主键索引树搜索的过程 select * from T where k betwee 3 and 5;
覆盖索引 select id from T where k betwee 3 and 5;
最左前缀原则
image.png
建立联合索引 如何安排索引字段顺序?
由于最左前缀原则
建立联合索引 (a,b,c) 相当于创建了 a ab abc三个索引
如果可以通过顺序安排 能够减少一个索引的维护 那很有必要考虑这个顺序问题
在考虑空间问题
如上面🌰 name和age的联合索引 如果是个老外: 小泉纯一郎-梅川酷紫海梅游对象渣男斯基 占用空间太大了 那建议再建立一个age的单字段索引.
索引下推
姓张 十岁的所有男孩
mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;
mysql5.6以前. 无索引下推
image.png
mysql5.6引入的 index condition pushdown 优化
image.png
以后 可能要分享内容:
普通索引 唯一索引如何选择 、 change buffer 与 redo log 、索引与优化器的爱恨情仇 、 前缀索引给覆盖索引使坏
网友评论