做网站需要域名吗,公司建设网站时的注意事项,自贡网页制作,做教育网站的er图InnoDB
1.索引类型 主键索引(Primary Key) 数据表的主键列使用的就是主键索引。 一张数据表有只能有一个主键#xff0c;并且主键不能为 null#xff0c;不能重复。 在 mysql 的 InnoDB 的表中#xff0c;当没有显示的指定表的主键时#xff0c;InnoDB 会自动先检查表中是…InnoDB
1.索引类型 主键索引(Primary Key) 数据表的主键列使用的就是主键索引。 一张数据表有只能有一个主键并且主键不能为 null不能重复。 在 mysql 的 InnoDB 的表中当没有显示的指定表的主键时InnoDB 会自动先检查表中是否有唯一索引的字段如果有则选择该字段为默认的主键否则 InnoDB 将会自动创建一个 6Byte 的自增主键。 注InnoDB主键索引中 B树的叶子节点key为主键的值,data为数据的内容 二级索引(辅助索引) 二级索引又称为辅助索引是因为二级索引的叶子节点存储的数据是主键。也就是说通过二级索引可以定位主键的位置。 唯一索引普通索引前缀索引等索引属于二级索引。 唯一索引(Unique Key) 唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据但是允许数据为 NULL一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性而不是为了查询效率。 普通索引(Index) 普通索引的唯一作用就是为了快速查询数据一张表允许创建多个普通索引并允许数据重复和 NULL。 前缀索引(Prefix) 前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引相比普通索引建立的数据更小 因为只取前几个字符。 全文索引(Full Text) 全文索引主要是为了检索大文本数据中的关键字的信息是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引5.6 之后 InnoDB 也支持了全文索引。 注InnoDB二级索引中 B树的叶子节点key为索引的值,data为主键的值 二级索引(图片来源于网络) 2. 聚集索引与非聚集索引 2.1 聚集索引 聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。 在 Mysql 中InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据对于 InnoDB 引擎表来说该表的索引(B树)的每个非叶子节点存储索引叶子节点存储索引和索引对应的数据。 聚集索引的优点 聚集索引的查询速度非常的快因为整个 B树本身就是一颗多叉平衡树叶子节点也都是有序的定位到索引的节点就相当于定位到了数据。 聚集索引的缺点 依赖于有序的数据 因为 B树是多路平衡树如果索引的数据不是有序的那么就需要在插入时排序如果数据是整型还好否则类似于字符串或 UUID 这种又长又难比较的数据插入或查找的速度肯定比较慢。 更新代价大 如果对索引列的数据被修改时那么对应的索引也将会被修改 而且况聚集索引的叶子节点还存放着数据修改代价肯定是较大的 所以对于主键索引来说主键一般都是不可被修改的。 2.2 非聚集索引 非聚集索引即索引结构和数据分开存放的索引。 二级索引属于非聚集索引。 MYISAM 引擎的表的.MYI 文件包含了表的索引 该表的索引(B树)的每个叶子非叶子节点存储索引 叶子节点存储索引和索引对应数据的指针指向.MYD 文件的数据。 非聚集索引的叶子节点并不一定存放数据的指针 因为二级索引的叶子节点就存放的是主键根据主键再回表查数据。 非聚集索引的优点 更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了非聚集索引的叶子节点是不存放数据的 非聚集索引的缺点 跟聚集索引一样非聚集索引也依赖于有序的数据 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后可能还需要根据指针或主键再到数据文件或表中查询。 这是 Mysql 的表的文件截图: 2.3 聚集索引和非聚集索引示意图: 3. 非聚集索引的回表 3.1 为什么非聚集索引要回表操作 在InnoDB中表数据是按照聚集索引通常是主键索引的顺序物理存储的。非聚集索引的叶子节点仅包含主键值和索引值不包含实际的行数据。因此当通过非聚集索引查询数据时数据库首先通过非聚集索引找到主键值然后再通过主键值去聚集索引中查找完整的行数据。这个过程就是回表操作。(非聚集索引的叶子节点的key是索引值,value是主键值) 3.2 非聚集索引一定回表查询吗(覆盖索引)? 非聚集索引不一定回表查询。 试想一种情况用户准备使用 SQL 查询用户名而用户名字段正好建立了索引。 SELECT name FROM table WHERE nameguang19; 那么这个索引的 key 本身就是 name查到对应的 name 直接返回就行了无需回表查询。 即使是 MYISAM 也是这样虽然 MYISAM 的主键索引确实需要回表 因为它的主键索引的叶子节点存放的是指针。但是如果 SQL 查的就是主键呢? SELECT id FROM table WHERE id1; 主键索引本身的 key 就是主键查到返回就行了。这种情况就称之为覆盖索引了。 4.覆盖索引 如果一个索引包含或者说覆盖所有需要查询的字段的值我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中如果不是主键索引叶子节点存储的是主键列值。最终还是要“回表”也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的不做回表操作 覆盖索引即需要查询的字段正好是索引的字段那么直接根据该索引就可以查到数据了 而无需回表查询。 如主键索引如果一条 SQL 需要查询主键那么正好根据主键索引就可以查到主键。 再如普通索引如果一条 SQL 需要查询 namename 字段正好有索引 那么直接根据这个索引就可以查到数据也无需回表。 覆盖索引: 5.联合索引和全文索引 5.1 全文索引 全文索引的使用主要涉及创建全文索引、插入数据、以及通过全文搜索查询数据。以下是一个具体的例子以MySQL数据库为例进行说明 1. 创建包含全文索引的表 首先需要创建一个表并在表中的某个或某些文本字段上创建全文索引。以下是一个创建表的例子其中在address字段上创建了全文索引 CREATE TABLE worker ( id int NOT NULL, name varchar(50) NOT NULL, age int NOT NULL, address varchar(100) NOT NULL, PRIMARY KEY (id), FULLTEXT INDEX idx_fulltext (address) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; 在这个例子中worker表有四个字段id、name、age和address。其中id是主键而address字段上创建了名为idx_fulltext的全文索引。注意从MySQL 5.7开始InnoDB存储引擎也支持全文索引但在早期版本中只有MyISAM存储引擎支持全文索引。 2. 插入数据 接下来向表中插入一些包含文本数据的记录。以下是一些示例插入语句 INSERT INTO worker (id, name, age, address) VALUES (1, aa, 18, 河北省保定市莲池区); INSERT INTO worker (id, name, age, address) VALUES (2, bb, 19, 河北省保定市竞秀区); INSERT INTO worker (id, name, age, address) VALUES (3, cc, 20, 河北省邢台市五四东路); -- 插入更多记录... 3. 使用全文搜索查询数据 最后通过全文搜索查询包含特定关键词的记录。在MySQL中可以使用MATCH()和AGAINST()函数来进行全文搜索。以下是一个查询示例它搜索address字段中包含“河北省保定市”的记录 SELECT * FROM worker WHERE MATCH(address) AGAINST(河北省保定市); 这个查询将返回所有address字段中包含“广东省深圳市”的记录。MySQL的全文搜索引擎会计算每个文档对象与查询的相关度并基于匹配的关键词个数和在文档中出现的次数来排序结果。 注意事项 在使用全文索引时需要注意全文索引对文本长度的要求。MySQL InnoDB存储引擎的全文索引默认最短索引字符串长度为4个字符可以通过ft_min_word_len系统变量调整。全文索引对于非常短的文本字段可能效果不佳因为建立和维护索引的成本可能超过其带来的性能提升。可以通过IN BOOLEAN MODE进行布尔模式的搜索这允许更复杂的查询条件如包含或排除特定关键词。 4.全文索引和like的区别 全文索引 适用于需要处理大量文本数据、要求高效率和相关性排序的场景。如搜索引擎、博客平台、新闻网站等需要对文本内容进行复杂搜索的应用。LIKE 适用于简单的字符串模式匹配场景。如在用户注册信息中查找包含特定关键字的用户名等 5.2 联合索引 了解联合索引首先先介绍两个概念 单列索引单列索引即由一列属性组成的索引。联合索引(多列索引)联合索引即由多列属性组成索引。 其次了解最左前缀原则 假设创建的联合索引由三个字段组成: ALTER TABLE table ADD INDEX index_name (num,name,age) 那么当查询的条件有为:num / (num AND name) / (num AND name AND age)时索引才生效。所以在创建联合索引时尽量把查询最频繁的那个字段作为最左(第一个)字段。查询的时候也尽量以这个字段为第一条件。再次强调 最左前缀原则指的是如果查询的时候查询条件精确匹配索引的左边连续一列或几列则此列就可以被用到。 select * from user where namexx and cityxx ; 可以命中索引
select * from user where namexx ; // 可以命中索引
select * from user where cityxx ; // 无法命中索引 这里需要注意的是查询的时候如果两个条件都用上了但是顺序不同如city xx and name xx那么现在的查询引擎会自动优化为匹配联合索引的顺序这样是能够命中索引的。 由于最左前缀原则在创建联合索引时索引字段的顺序需要考虑字段值去重之后的个数较多的放前面。ORDER BY子句也遵循此规则。 例如创建了a,b,c联合索引 where axx and b443 and c32;(√) where c32 and b -123 and a 123;(√) where b32 and c -123;(×) 注现在我给num和name和age字段建立联合索引那么 BTree 在排序的时候会首先按照 num排序当 排序相同的时候再按照 name进行排序。依次类推 6.如何创建索引 1.添加PRIMARY KEY主键索引 ALTER TABLE table_name ADD PRIMARY KEY ( column ) 2.添加UNIQUE(唯一索引) ALTER TABLE table_name ADD UNIQUE ( column ) 3.添加INDEX(普通索引) ALTER TABLE table_name ADD INDEX index_name ( column ) 4.添加FULLTEXT(全文索引) ALTER TABLE table_name ADD FULLTEXT ( column) 5.添加多列索引 ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
