索引
索引是一种有序的存储结构，按单个或多个列的值进行排序。可以提升搜索效率。
主键索引
非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息。
PRIMARY KEY(key1, key2)唯一索引
不可以出现相同的值，可以有 NULL 值。
UNIQUE(key)
普通索引
允许出现相同的索引内容。
INDEX(key)
-- OR
KEY(key[,...])组合索引
对表上的多个列进行索引
INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);全文索引
将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT; 在短字符串中用 LIKE %；在全文索引中用 match 和 against 。
主键选择
innodb 中表是索引组织表，每张表有且仅有一个主键；
1. 如果没有显示设置，则从非空唯一索引中选择；
a. 只有一个非空唯一索引，则选择该索引为主键；
b. 有多个非空唯一索引，则选择声明的第一个为主键；
2. 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键.
约束
为了实现数据的完整性，对于 innodb，提供了以下几种约束，primary key，unique key，foreign key，default，not null。
索引存储
每一个索引都对应了一个b+树，下面先介绍一下b+树
b+树
全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K，innodb 默认页大小为 16K；对页的访问是一次磁盘 IO，缓存中会缓存常访问的页。特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范 围查询； 每个索引对应着一个 B+ 树。
整体存储结构
页是 innodb 磁盘管理的最小单位；默认16K， B+ 树的一个节点的大小就是该页的值。
聚集索引
按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分。
辅助索引
叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还包含一个 bookmark ；该书签存储了聚集索引的 key。
实例
我们以下面的代码建立一个表
DROP TABLE IF EXISTS `covering_index_t`;
CREATE TABLE `covering_index_t` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) DEFAULT NULL,
`cid` INT(11) DEFAULT NULL,
`age` SMALLINT DEFAULT 0,
`score` SMALLINT DEFAULT 0,
PRIMARY KEY (`id`),
KEY `name_cid_idx` (`name`, `cid`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;
可以看到有五列，我们建立了一个主键索引“id”，以及组合索引组合name和cid的name_cid_idx。
如果执行下面的的代码
select * from user where id >= 18 and id < 40;则直接在聚合索引btree里查找，因为只有聚合索引有id项。
如果执行下面代码
select * from user where name = "小张";
因为根据name查找所以在辅助索引name_cid_idx中查找，但是需要的是*即所有数据，那么我们在辅助索引中得到id后回表在聚合索引中查找id即可。
这里要提一下
PRIMARY KEY (`id`),这个主键对应类似map<id,covering_index_t>存储结构
KEY `name_cid_idx` (`cid`)这个类似map<cid,pair<cid,id>>,所以上面我们才可以在辅助索引里得到id。
innodb体系结构
Buffer pool
Buffer pool 缓存表和索引数据；采用 LRU 算法（原理如下图）让 Buffer pool 只缓存比较热的数 据 。
buffer pool 中的数据修改没有刷到磁盘， 怎么确保内存中数据安全（mysql 关闭时，内存数据丢 失）？
有redolog来保证数据安全，类似redis的aof
Change buffer
Change buffer 缓存辅助（二级）索引的数据变更（DML 操作）这些数据并不在 buffer pool 中， Change buffer 中的数据将会异步 merge 到 buffer pool 中，当下次从磁盘当中读取非唯一索引的 数据；同时会定期合并到索引页中。
free list 组织 buffer pool 中未使用的缓存页；flush list 组织 buffer pool 中脏页，也就是待刷盘的 页；lru list 组织 buffer pool 中冷热数据，当 buffer pool 没有空闲页，将从 lru list 中最久未使用的 数据进行淘汰；
最左匹配原则
对于组合索引，从左到右依次匹配，遇到> < between like 就停止匹配。
举个例子大家就懂
DROP TABLE IF EXISTS `covering_index_t`;
CREATE TABLE `covering_index_t` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) DEFAULT NULL,
`cid` INT(11) DEFAULT NULL,
`age` SMALLINT DEFAULT 0,
`score` SMALLINT DEFAULT 0,
PRIMARY KEY (`id`),
KEY `name_cid_idx` (`name`, `cid`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;
仍然是这个例子如果我们用下面
select * from user where name = "小张";则可以在辅助索引里搜索，因为name在左边，但如果是下面的
select * from user where cid = 123;那就不进行辅助索引，因为最左匹配要先有左边的才行。那么
select * from user where name = "小张" and cid = 123;也可以，因为左边的已经有了，就可以有右边的。
覆盖索引
从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引 树；较少磁盘 IO。
索引下推
为了减少回表次数，提升查询效率；在 MySQL 5.6 的版本开始推出；
MySQL 架构分为 server 层和存储引擎层；
没有索引下推机制之前，server 层向存储引擎层请求数据，在 server 层根据索引条件判断进行数据 过滤；
有索引下推机制之后，将部分索引条件判断下推到存储引擎中过滤数据；最终由存储引擎将数据汇 总返回给 server 层；
索引失效
select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
索引字段参与运算，则索引失效；例如： from_unixtime(idx) = '2021-04-30'; 改成 idx = unix_timestamp("2021-04-30")
索引字段发生隐式转换，则索引失效；例如：将列隐式转换为某个类型，实际等价于在索引列上作 用了隐式转换函数；
LIKE 模糊查询，通配符 % 开头，则索引失效；例如：select * from user where name '%Mark';
在索引字段上使用 like NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0；
组合索引中，没使用第一列索引，索引失效；
索引原则
查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；
使用短索引；节点包含的信息多，较少磁盘 IO 操作；比如： smallint ， tinyint ；
对于很长的动态字符串，考虑使用前缀索引；
对于组合索引，考虑最左侧匹配原则、覆盖索引；
尽量选择区分度高的列作为索引；该列的值相同的越少越好；
出现了sql比较慢，怎么办？
1.先使用慢查询日志找到SQL语句
2.分析SQL语句：
是不是索引出了问题，where group by order by
是不是SQL语句里有繁杂的运行：in和not in优化成联合查询 减少联合查询
注意：工作中不要用age字段 应该改为生日
更多资料在：https://github.com/0voice查询
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
