做宣传类网站需要什么资质,wordpress 标签小工具栏,南昌地宝网app,静态网站如何建设目录 一、索引
1.1、简单介绍
1.2、索引的分类
1.2.1、主键索引
1.2.2、单值索引#xff08;单列索引、普通索引#xff09;
1.2.3、唯一索引
1.2.4、复合索引
1.2.5、复合索引经典问题
1.3、索引原理
1.3.1、主键自动排序
1.3.2、索引的底层原理
1.3.3、B 树和 B…目录 一、索引
1.1、简单介绍
1.2、索引的分类
1.2.1、主键索引
1.2.2、单值索引单列索引、普通索引
1.2.3、唯一索引
1.2.4、复合索引
1.2.5、复合索引经典问题
1.3、索引原理
1.3.1、主键自动排序
1.3.2、索引的底层原理
1.3.3、B 树和 B树的区别
1.4、聚簇索引和非聚簇索引
1.4.1、innoDB 中的主键索引
1.4.2、使用聚簇索引的优势
1.4.3、使用聚簇索引需要注意什么
1.4.4、为什么主键通常建议使用自增 id
1.5、索引失效的场景 一、索引 1.1、简单介绍
索引就是一种帮助 mysql 提高查询效率的数据结构.
优点
大大增加了查询速度.
缺点
索引实际上是一张表因此需要消耗一部分空间资源.对表中的数据进行增删改的时候需要更新索引因此速度会受到一定影响. 1.2、索引的分类
1.2.1、主键索引
实际上就是我们创建数据库时指定的主键主键索引值不能为空、不能重复.会自动创建索引叫做 “主键索引”在 innodb 引擎中就是所谓的 “聚簇索引”.
例如以 id 为主键建表
create table user(id int PRIMARY KEY, name varchar(20), age int);
然后通过以下命令查看 user 表的索引
show index from user; 1.2.2、单值索引单列索引、普通索引
就是为表中的某一列创建的索引一个表中可以有多个单列索引.
例如表中有字段 id、name、age那么为 其中的 name 创建一个索引就叫单列索引.
创建方式有以下两种
a建表时创建注意这种方式创建索引名和字段名一致
# 给 name 单独创建索引
create table user(id int primary key, name varchar(20), age int, key(name));# 给 name 和 age 分别创建索引
create table user(id int primary key, name varchar(20), age int, key(name), key(age));b建表后创建
create table user(id int primary key, name varchar(20), age int);create index index_name on user(name);c删除索引
drop index 索引名 on 表明 1.2.3、唯一索引
在创建表的时候有时候我们会通过 unique 指定某个字段唯一这个时候就会创建唯一索引. Ps允许有 null 值并且可以有多个. 创建方式有以下两种
a建表时指定
# 第一种写法
create table user(id int primary key, name varchar(20) unique, age int);# 第二种写法
create table user(id int primary key, name varchar(20), age int, unique(name));b建表后创建
create unique index index_name on user(name);1.2.4、复合索引
就是我们为表中的多个字段一起创建一个索引. Ps查询时在 where 条件后必须要使用 and 连接复合索引字段否则不生效. 创建方式有以下两种
a建表时创建
create table user(id int primary key, name varchar(20), age int, key(name, age));b建表后创建
create index name_age_index on user(name, age);1.2.5、复合索引经典问题 问题有一个用户表给 name、age、gender 三个字段创建了一个复合索引 key(name, age, gender)以下场景哪种查询索引会生效 以下是 where 查询后通过 and 拼接的字段.
name 生效name age 生效name age gender 生效name gender age 生效age gender 失效gender 失效gender age name 生效
该怎么判断呢符合索引生效只要满足以下任意一个原则即可
最左前缀元组必须包含做前缀也就意味着 name、 name age、name age gender 是生效的.mysql 引擎为了更好的利用索引在查询过程中会动态调整查询字段顺序便于利用索引也就意味着只要包含所有索引字段即可任意的组合都可以. 1.3、索引原理
1.3.1、主键自动排序
当我创建一个 user 表含主键 id然后按照无序 id 的方式插入数据会发现查询结果尽然按照 主键 id 排序了 为什么会进行排序呢 排序之后相对来说查询更快. 例如有 10 个自增 id现在查询 id 3 的那么只需要向下对比三次即可得到而对于无序数据来说每次都需要遍历一遍数据才能得到.
这也就说明为啥主键不建议使用 uuid 去建立而是使用 int 类型因为在主键建立索引的时候会先根据表中的主键去排序排序后在查询效率会更高.
1.3.2、索引的底层原理
假设有如下表和信息 索引的数据结构就是一个 b 树原理如下
a排序形成链表表中的每一条数据组织成一个链表中的一个节点结构由三部分构成“主键 数据 指针”数据就是表中的非主键索引字段name, age指针就是用来指向下一个节点这些节点会现经过主键 id 的排序最后组织成一个链表的结构得到b树的叶子节点 如下 b页管理将链表进行分页管理每一页的大小默认存储 16kb假设如下图真实情况一页存放的数据有很多. c页目录管理将每一页最左边节点的主键 和 指针 拿出来存放到页目录中页目录的默认大小也是 16kb d如果页目录的大小占满了那么可能还会继续向上生成页目录父节点不过一般开发存储的数据树的高度都不会超过 4 的也就是说当需要查找某一数据时最多只需要 1~3 次 I/O 操作注意顶层的根节点时在内存中的.
1.3.3、B 树和 B树的区别
B 树相当于是在 B 上的一种优化主要区别如下
B 树非叶子节点只存储键值对信息B 树 data 数据也需要存储而每一页的存储空间是有限的默认 16 kb那么如果 data 数据较大时每个节点能存储的 key 就很少进而导致树的深度较大增大了查询时的磁盘 IO 次数每一层都进行一次 IO.B 树的叶子节点保存全集数据是一个链表结构而非叶子节点只存储 key大大增加了非叶子节点存储 key 的数量降低了树高. 1.4、聚簇索引和非聚簇索引
1.4.1、innoDB 中的主键索引
聚簇索引由 主键索引 和 辅助索引 构成.
主键索引主键索引中叶子节点保存表中每一行的所有数据当需要查找例如 where Id 14就会去主键索引 B 树上找到的叶子节点然后获取行数据. Ps如果没有定义主键就会选择唯一且非空的索引代替如果非空索引也没有就会自己隐式定义一个主键作为聚簇索引 辅助索引innoDB 中的非聚簇索引就是辅助索引就是在聚簇索引之上建立的索引一般来说就是表中给其他字段建立的索引非主键索引也就是 复合索引、单列索引、唯一索引并且的叶子节点存储的不再是行物理地址而是主键值因此辅助索引最少需要二次查询才能找到数据例如 where namecyk步骤如下
在辅助索引 B 树种检索 name然后到达叶子节点获取对应的主键.根据主键在聚簇索引 B 树种在及进行一次检索操作最终到达叶子节点获取整行数据. 非聚簇索引在 myisam 使用的是非聚簇索引也由两颗 B 树构成主键索引、辅助索引主键索引B树节点存储了主键辅助索引 B 树种存储了辅助键. 叶子节点都是用一个地址指向真正的表的数据因此辅助键无需像 innoDB 一样访问主键索引树. 1.4.2、使用聚簇索引的优势
问题每次使用辅助索引检索都需要经过两次 B 树查询看上去聚簇索引的效率明显低于非聚簇索引这不是多此一举么聚簇索引优势在哪
访问同一数据也不同记录时会把页加载到缓存中再次访问的时候会在内存中完成访问不必访问磁盘而主键和数据又是一起被载入内存的因此按照主键 id 来组织数据排好序的获取更快.innoDB 中的辅助索引叶子节点存储主键值而不是物理地址因此当行数据发生改变时对表进行增删改叶子节点也无需像 myisam 非聚簇索引的辅助索引一样改变地址只需要维护索引树即可.innoDB 中的辅助索引叶子节点存放的是主键值而 myisam 中存储的是物理地址因此空间占用更小. 1.4.3、使用聚簇索引需要注意什么
主键最好不要使用 uuid因为 uuid 值过于离散不适合排序并且有可能生成的 uuid 插入在索引树的中间位置导致树调整复杂度变大查询时消耗更多的时间.
建议使用 int 或者 bigint 类型的自增方便排序并且默认会在索引树的末尾增加主键值对索引树的结构影响最小. 1.4.4、为什么主键通常建议使用自增 id
聚簇索引的数据物理地址存放顺序和索引主键 id 顺序时一致的因此索引是相邻的对应的数据也是在相邻的磁盘上. 如果主键不是自增 id那么会不断调整数据的物理地址来进行分页. 如果是自增就只要一页一页写磁盘碎片也就少了. 1.5、索引失效的场景
1. 查询语句中使用 like 关键字如果匹配字符串的第一个字符为 %索引不会被使用如果 % 不是在第一个位置索引就会被使用.
2. 查询语句中使用复合索引需要满足匹配原则才可以上面讲到过了。
3. 查询语句中使用 or 关键字时如果 or 前后的两个条件都是索引那么就会使用索引如果任意一个不是索引那么查询中不使用索引.
