网站做开票,网站维护需要,番禺区网站建设公司,网站新闻被百度收录MySQL 中的索引 一、索引的创建和删除1.主键会自动添加索引2.unique 约束的字段自动添加索引3.给指定的字段添加索引4.删除指定索引5.查询表上的索引 二、索引的分类三、MySQL索引采用了B树数据结构1.B树的经典面试题 四、其他索引及相关调优1.Hash索引2.聚集索引和非聚集索引3… MySQL 中的索引 一、索引的创建和删除1.主键会自动添加索引2.unique 约束的字段自动添加索引3.给指定的字段添加索引4.删除指定索引5.查询表上的索引 二、索引的分类三、MySQL索引采用了B树数据结构1.B树的经典面试题 四、其他索引及相关调优1.Hash索引2.聚集索引和非聚集索引3.二级索引4.覆盖索引5.索引下推6.单列索引单一索引7.复合索引组合索引 五、索引的优缺点六、何时使用索引 索引index是一种能够提高检索查询效率的提前排好序的数据结构。例如书的目录就是一种索引机制。索引是解决 SQL 慢查询的一种方式。
一、索引的创建和删除
1.主键会自动添加索引
主键字段会自动添加索引不需要程序员干涉主键字段上的索引被称为主索引。
2.unique 约束的字段自动添加索引
unique约束的字段也会自动添加索引不需要程序员干涉这种字段上添加的索引称为唯一索引。
3.给指定的字段添加索引 建表时添加索引 drop table if exists test;
create table test (id int primary key auto_increment,name varchar(255),index index_name(name)
);如果表已经建好了后期给字段添加索引 alter table test add index index_age (age);直接创建索引 create index index_age on test(age);4.删除指定索引 删除索引 alter table test drop index index_name;5.查询表上的索引
查询指定表上的所有索引show index from test;二、索引的分类
不同的存储引擎有不同的索引类型和实现 按照数据结构分类 B树 索引MySQL 的 InnoDB 存储引擎采用的就是这种索引 采用 B树 的数据结构。Hash 索引仅 memory 存储引擎支持采用 哈希表 的数据结构。 按照物理存储分类 聚集索引聚簇索引索引和表中的数据放在一起数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引。非聚集索引非聚簇索引索引和表中数据是分开的索引是独立于表空间的一张表可以有多个非聚集索引。 按照字段特性分类 主键索引primary key唯一索引unique普通索引index全文索引fulltext仅 InnoDB 和 MyISAM 存储引擎要求字段的类型都是文本内容才可以使用全文索引。 按照字段个数分类 单列索引单一索引、联合索引复合索引、组合索引 三、MySQL索引采用了B树数据结构
关于这部分知识可以参考我博客查找中常见的树数据结构
1.B树的经典面试题
经典面试题 MySQL为什么选择B树作为索引的数据结构而不是B树 非叶子节点上可以存储更多的键值阶数可以更大更矮胖磁盘IO次数少数据查询效率高。所有数据都是有序存储在叶子节点上的让范围查找分组查找效率更高。数据页之间、数据记录之间采用指针链接让升序降序更加方便操作。 经典面试题 如果一张表没有主键索引那还会创建B树吗 当一张表没有主键索引时默认会使用一个隐藏的内置的聚集索引clustered index。这个聚集索引是基于表的物理存储顺序构建的通常是使用B树实现的。 四、其他索引及相关调优
1.Hash索引
支持Hash索引的存储器引擎有 InnoDB不支持手动创建Hash索引系统会自动维护一个自适应的Hash索引 对于 InnoDB 来说即使手动指定了某字段采用Hash索引最终show index from 表明的时候还是BTREE。 Memory支持Hash索引。 Hash索引底层的数据结构就是哈希表。一个数组数组中每个元素是链表。和Java中的HashMap一样。哈希表中每个元素都是key value结构。key存储索引值value存储行指针。 注意不同的字符串经过哈希算法得到的数组下标可能相同这种叫做哈希碰撞/哈希冲突。【不过好的哈希算法应该具有很低的碰撞概率。常用的哈希算法如 MD5、SHA-1、SHA-256 等都被设计为尽可能减少碰撞的发生。】Hash索引的优缺点 优点只能用在点查询中效率很高。例如age10。缺点不支持排序不支持范围查找。
2.聚集索引和非聚集索引
按照数据的物理存储方式不同可以将索引分为聚集索引聚簇索引和非聚集索引非聚簇索引。 存储引擎是InnoDB的主键上的索引属于聚集索引。 InnoDB的物理存储方式当创建一张表user并使用InnoDB存储引擎会在硬盘上生成这样的文件 user.iblInnoDB data表索引数据user.frm存储表结构信息 存储引擎是MyISAM的任意字段上的索引都是非聚集索引。 MyISMA的物理存储方式当创建一张表user并使用MyISAM存储引擎会在硬盘上生成这样的文件 user.MYD表数据user.MYI表索引user.frm表结构 注意从 MySQL8.0开始不再生成frm文件了引入了数据字典用数据字典来统一存储表结构信息例如 information_schema.TABLES表包含了数据库中所有表的信息例如表名、数据库名、存储引擎类型等。information_schema.COLUMNS表包含了数据库中所有表的列信息例如列名、数据类型、默认值等。 聚集索引的优点和缺点 优点聚集索引将数据存储到索引树的叶子节点上。可以减少一次查询因为查询索引树的同时可以获取数据。缺点对数据进行修改或删除时需要更新索引树会增加系统的开销。
3.二级索引
二级索引也属于非聚集索引。也有人把二级索引称为辅助索引。
4.覆盖索引 覆盖索引Covering Index顾名思义是指某个查询语句可以通过索引的覆盖来完成而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时查询需要的所有列都可以从索引中提取到而不需要再去查询实际数据行获取查询所需数据。 假设有一个用户表user包含以下列idusernameemailage。 常见的查询是根据用户名查询用户的邮箱。如果为了提高这个查询的性能可以创建一个覆盖索引包含usernameemail这两列。 创建覆盖索引 create index index_uername_email on user(username, email);当执行以下查询时 select email from user where usernamejack;MySQL可以直接使用覆盖索引index_username_email来获取查询结果而不必再去查找用户表中的数据。这样可以减少磁盘IO并提高查询效率。而如果没有覆盖索引MySQL会先使用索引username来匹配行然后再回到表查询获取email这个过程会增加更多的磁盘IO和查询时间。 值得注意的是覆盖索引的创建需要考虑查询的字段选择。如果查询需要的字段较多可能需要创建包含更多列的覆盖索引以满足完全覆盖查询的需要。
5.索引下推
索引下推Index Condition Pushdown是一种MySQL中的优化方法它可以将查询中的过滤条件下推到索引层级中处理从而减少回表次数优化查询性能。具体来说在使用索引下推时MySQL
6.单列索引单一索引 单一索引是指对数据库表中的某一列或属性进行索引创建对该列进行快速查找和排序操作。单一索引可以加快查询速度提高数据库的性能。 例如假设我们有一个学生表student其中有以下几个列学生编号stu_id、姓名name、年龄age和性别sex。 如果我们针对学生表的信息编号stu_id列创建单列索引那么可以快速地根据学生编号进行查询或者排序操作。例如我们可以使用下面的SQL语句查询学生编号为123456的学生信息 select * from student where stu_id123456;由于我们对学生编号建立了单一索引所以数据库可以直接通过索引快速定位到具有学生123456的那一行记录从而加快查询速度。
7.复合索引组合索引 五、索引的优缺点
索引是数据库中一种重要的数据结构用于加速数据的检索和查询操作。它的优点和缺点如下:优点 提高查询性能通过创建索引可以大大减少数据库查询的数据量从而提高查询的速度。加速排序当查询需要按照某个字段进行排序时索引可以加速排序的过程提高排序的效率。减少磁盘IO索引可以减少磁盘IO的次数这对于磁盘读写速度较低的场景尤其重要。 缺点 占据额外的存储空间索引需要占据额外的存储空间特别是在大型数据库系统中索引可能占据较大的空间。增删改操作的性能损耗每次对数据表进行插入、更新、删除等操作时需要更新索引会导致操作的性能降低。资源消耗较大索引需要占用内存和CPU资源特别是在大规模并发访问的情况下可能对系统的性能产生影响。 六、何时使用索引
在以下情况下建议使用索引 在需要经常搜索的列上创建索引。主键上创建索引MySQL中主键自动创建索引。经常用于连接的列上创建索引。经常需要根据范围查询进行搜索的列上创建索引。在 Where 查询子句中引用效率高的列上创建索引。在 Order by 和 Group by 子句中出现的列上创建索引。大表当表的数据量较大时使用索引可以快速定位到所需的数据提高查询效率。 注意在某一范围内频繁搜索的属性只有在当使用索引的查询结果不超过总记录的20%时才有明显效果 在以下情况下不建议使用索引】 频繁执行更新操作的表如果表经常被更新数据使用索引可能会降低更新操作的性能因为每次更新都需要维护索引。小表对于数据量小的表使用索引可能并不会带来明显的性能提升反而会占用额外的存储空间。对于唯一性很差的字段一般不建议添加索引。当一个字段的唯一性很差时查询操作基本上需要扫描表的大部分数据。如果为这样的字段创建索引索引的大小可能会比数据本身还大导致索引的存储空间占用过高同时也会导致查询操作的性能下降。 总之索引需要根据具体情况进行使用和权衡需要考虑到表的大小、查询频率、更新频率以及业务需求等多方面的因素。
