江苏龙网

  1. 首页 > 人文 > >

Mysql不止CRUD，聊聊索引( 五 )


6、创建前缀索引
CREATE TABLE test.t1 (col1 VARCHAR(10),col2 VARCHAR(20),INDEX (col1, col2(10)));
Mysql不止CRUD，聊聊索引文章插图
前缀索引的目的主要是减少字符串作为索引占用的空间 ， 提高查询速度
2.3.2、基于已创建好的表创建索引在已经存在的表中创建索引 ， 可以使用ALTER TABLE语句或者CREATE INDEX语句创建索引 。
1、使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]2、使用CREATE INDEX创建索引
【Mysql不止CRUD，聊聊索引】CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ONtable_name (col_name[length],...) [ASC|DESC]2.4、删除索引1、使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_nameCREATE TABLE test.t1 (id int AUTO_INCREMENT primary key,col1 VARCHAR(10),col2 VARCHAR(20),INDEX (col1, col2(10)));
Mysql不止CRUD，聊聊索引文章插图
alter table test.t1 drop index col1;
alter table test.t1 drop index PRIMARY; #会报错：添加AUTO_INCREMENT约束字段的唯一索引不能被删除
Mysql不止CRUD，聊聊索引文章插图
2、使用DROP INDEX语句删除索引
DROP INDEX index_name ON table_name;2.5、聚集索引和二级索引1、聚集索引
InnoDB存储引擎表是索引组织表 ， 即表中数据按照主键顺序存放 。 而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树 ， 同时叶子节点中存放的即为整张表的行记录数据 ， 也将聚集索引的叶子节点称为数据页 。 聚集索引的这个特性决定了索引组织表中数据也是索引的一部分 。 同B+树数据结构一样 ， 每个数据页都通过一个双向链表来进行链接 。
由于实际的数据页只能按照一棵B+树进行排序 ， 因此每张表只能拥有一个聚集索引 。 由于定义了数据的逻辑顺序 ， 聚集索引能够特别快地访问针对范围值的查询 。 查询优化器能够快速发现某一段范围的数据页需要扫描 。
聚集索引的存储并不是物理上连续的 ， 而是逻辑上连续的 。 这其中有两点：一是前面说过的页通过双向链表链接 ， 页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的 ， 物理存储上可以同样不按照主键存储 。
2、二级索引（辅助索引）
对于辅助索引（Secondary Index） ， 叶子节点并不包含行记录的全部数据 。 叶子节点除了包含键值以外 ， 每个叶子节点中的索引行中还包含了一个书签（bookmark） 。 该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据 。 由于InnoDB存储引擎表是索引组织表 ， 因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键 。
当通过辅助索引来寻找数据时 ， InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键 ， 然后再通过主键索引来找到一个完整的行记录 。
2.6、覆盖索引InnoDB存储引擎支持覆盖索引（covering index ， 或称索引覆盖） ， 即从辅助索引中就可以得到查询的记录 ， 而不需要查询聚集索引中的记录 。 使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息 ， 故其大小要远小于聚集索引 ， 因此可以减少大量的IO操作 。
CREATE TABLE `item` (`item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`store_id` bigint(20) unsigned NOT NULL COMMENT '所属店铺ID',`type` tinyint(3) unsigned NOT NULL COMMENT '商品类型 . 不同类型的商品, 保存到各自不同的表中. 参考 ',`state` tinyint(3) unsigned NOT NULL COMMENT '状态',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,`create_user_id` bigint(20) unsigned NOT NULL,`last_modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`last_modify_user_id` bigint(20) unsigned NOT NULL,`is_deleted` bigint(20) unsigned NOT NULL DEFAULT '0',PRIMARY KEY (`item_id`),KEY `fk_store_id` (`store_id`)) ENGINE=InnoDB AUTO_INCREMENT=332604631475558863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='代表所有的物品 ， 之前有把用户ID放进来 ， 表示该物品所属的用户 ， 但是考虑到如果有子账号的情况 ， 物品难道属于这个子账号所属的用户吗？而且记录了创建人用户ID ， 考虑这两个因素 ， 因此不设置用户ID列'


推荐阅读


上一篇：YAPI接口文档部署教程 Ubuntu 16.04

下一篇：我为什么放弃了ThinkPad，选择入手ThinkBook