洛阳网站建设培训,注册域名怎么做网站,做养殖推广什么网站好,宁波seo优化公司排名文章目录 索引索引分类主键选择索引的代价 约束外键约束约束与索引的区别 索引使用场景不要使用索引的场景总结 索引
索引的概念#xff1a;索引是一种有序的存储结构。索引按照单个或多个列的值进行排序。 索引的目的#xff1a;提升搜索效率。
索引分类 按照数据结构分为… 文章目录 索引索引分类主键选择索引的代价 约束外键约束约束与索引的区别 索引使用场景不要使用索引的场景总结 索引
索引的概念索引是一种有序的存储结构。索引按照单个或多个列的值进行排序。 索引的目的提升搜索效率。
索引分类 按照数据结构分为 1B树索引。 2自适应hash索引。主要用在内存当中看MySQL缓冲中是否有数据。 3全文索引。将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术关键词 FULLTEXT在短字符串中用 LIKE %在全文索引中用 match 和against。一般不会用到如果要用全文索引会使用elasticsearch工具。
按照物理存储分为 1聚集索引或者称为聚簇索引。聚集索引是主键对应的那个B树会存储对应的行数据。 2辅助索引或称为二级索引。比如通常设计的普通索引或组合索引只有索引信息和主键ID信息没有行数据。
按照列属性分为 1主键索引。是一个非空唯一索引一个表只有一个主键索引在 innodb 中主键索引的 B 树包含表数据信息。 PRIMARY KEY(key) 2唯一索引。不可以出现相同的值允许出现null。 UNIQUE(key) 3普通索引。允许出现相同的索引内容。
INDEX(key)
-- or
KEY(key[...])4前缀索引。只比较前几个字符的长字符串。 按照列的个数分为 1单列索引。 2组合索引。对表上的多个列进行索引。
INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);主键选择
innodb 中表是索引组织表每张表有且仅有一个主键。
如果显示设置 PRIMARY KEY则该设置的 key 为该表的主键。如果没有显示设置则从非空唯一索引中选择。 a. 只有一个非空唯一索引则选择该索引为主键 b. 有多个非空唯一索引则选择声明的第一个为主键没有非空唯一索引则自动生成一个 6 字节的 _rowid 作为主键。 说明此时数据会按照插入的顺序存放到这个系统自动生成的聚集索引B树中这其实就相当于全表扫描时mysql的数据存放
索引的代价
1空间上索引是一种存储结构需要写入磁盘中会占用空间在工程应用中一般不会超过8个索引。 2时间上维护的代价体现在DML操作会变慢因为它要维护所有索引对应的B树。
约束
为了实现数据的完整性对于 innodb提供了以下几种约束primary keyunique keyforeign keydefaultnot null。
外键约束
外键用来关联两个表来保证参照完整性MyISAM 存储引擎本身并不支持外键只起到注释作用而 innodb 完整支持外键并具备事务性。
create table parent (id int not null,primary key(id) ) engineinnodb;create table child (id int,parent_id int,foreign key(parent_id) references parent(id) ON DELETE CASCADE ON UPDATE CASCADE
) engineinnodb;-- 被引用的表为父表引用的表称为子表
-- 外键定义时可以设置行为 ON DELETE 和 ON UPDATE行为发生时的操作可选择
-- CASCADE 子表做同样的行为
-- SET NULL 更新子表相应字段为 NULL
-- NO ACTION 父类做相应行为报错
-- RESTRICT 同 NO ACTION-- 测试
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO child VALUES (10, 1);
INSERT INTO child VALUES (20, 2);
DELETE FROM parent WHERE id 1;被引用的表为父表引用的表称为子表。 外键定义时可以设置行为 ON DELETE 和 ON UPDATE行为发生时的操作可选择 1CASCADE子表做同样的行为。 2SET NULL 更新子表相应字段为 NULL。 3NO ACTION 父类做相应行为报错。 4RESTRICT 同 NO ACTION。
约束与索引的区别
创建主键索引或者唯一索引的时候同时创建了相应的约束但是约束是逻辑上的概念索引是一个数据结构既包含逻辑的概念也包含物理的存储方式。
索引使用场景
1使用where条件判断时会使用索引。 2使用group by分组查询时会判断后面的列是否创建了索引如果创建了就会使用索引对应的B树。 3使用order by时会判断后面的列是否创建了索引如果创建了就会使用索引对应的B树。
不要使用索引的场景
1没有涉及到where、grop by、order by的使用不用创建索引。 2区分度不高的列没必要使用索引。 3经常修改的列不要创建索引因为维护代价太高。 4表的数据量少没必要创建索引。
总结
一定要确定一个主键索引的原因是 主键索引对应的是聚集索引B树所有的数据要存储在主键对应的B树中。
