公司网站建设上海找谁,做的好看的国内网站欣赏,报告格式,网站先做前台还是后台1. 按照功能划分 按照功能来划分#xff0c;索引主要有四种#xff1a;
普通索引唯一性索引主键索引全文索引
普通索引就是最最基础的索引#xff0c;这种索引没有任何的约束作用#xff0c;它存在的主要意义就是提高查询效率。
普通索引创建方式如下#xff1a; CREATE…1. 按照功能划分 按照功能来划分索引主要有四种
普通索引唯一性索引主键索引全文索引
普通索引就是最最基础的索引这种索引没有任何的约束作用它存在的主要意义就是提高查询效率。
普通索引创建方式如下 CREATE TABLE user ( id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(64) DEFAULT NULL, PRIMARY KEY (id), KEY name (name) ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4; name 字段就是一个普通索引括号外面的是索引名里边的是索引的字段。
唯一性索引则在普通索引的基础上增加了数据唯一性的约束一张表中可以同时存在多个唯一性索引唯一性索引创建方式如下 CREATE TABLE user ( id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(64) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4; name 字段就是唯一性索引。
主键索引则是在唯一性索引的基础上又增加了不为空的约束换言之添加了唯一性索引的字段是可以包含 NULL 值的即 NOT NULLUNIQUE一张表里最多只有一个主键索引当然一个主键索引中可以包含多个字段。
全文索引其实我们很少在 MySQL 中用如果项目中有做全文索引的需求一般可以通过 Elasticsearch 或者 Solr 来做目前比较流行的就是 Elasticsearch。
全文索引在 MySQL 中支持的版本也需要大家留意一下
MySQL 5.6 以前的版本只有 MyISAM 存储引擎支持全文索引。MySQL 5.6 及以后的版本MyISAM 和 InnoDB 存储引擎均支持全文索引。
创建全文索引对字段类型也有要求只有字段的数据类型为 CHAR、VARCHAR 以及 TEXT 等才可以建立全文索引。
MySQL 的全文索引最开始只支持英文因为英文分词比较方便中文分词就比较麻烦所以最早的 MySQL 全文索引是不支持中文的。从 MySQL5.7.6 版本开始引入了 ngram 全文分析器来解决分词问题并且这个分词器对 MyISAM 和 InnoDB 引擎都有效。
不过 MySQL 的全文索引并不好用有这方面的需求还是直接上 Es 吧。
全文索引的创建方式如下 CREATE TABLE user ( id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(64) DEFAULT NULL, PRIMARY KEY (id), FULLTEXT KEY name (name) ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4; name 字段就是全文索引。
2. 按照物理实现划分 按照物理实现方式索引可以分为两大类
聚集索引有的人也称之为“聚簇索引”非聚集索引有的人也称之为“非聚簇索引”
2.1 聚集索引聚集索引在存储的时候可以按照主键不是必须看情况来排序存储数据BTree 的叶子结点就是完整的数据行查找的时候找到了主键也就找到了完整的数据行。
在聚集索引里表中数据行按索引的排序方式进行存储对查找行很有效。只有当表包含聚集索引时表内的数据行才会按找索引列的值在磁盘上进行物理排序和存储。每张表只能有一个聚集索引原因很简单因为数据行本身只能按一个顺序存储。
当我们基于 InnoDB 引擎创建一张表的时候都会创建一个聚集索引每张表都有唯一的聚集索引
如果这张表定义了主键索引那么这个主键索引就作为聚集索引。如果这张表没有定义主键索引那么该表的第一个唯一非空索引作为聚集索引。如果这张表也没有唯一非空索引那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引这个隐藏的主键是一个 6 个字节的列该列的值会随着数据的插入自增。
聚集索引最主要的优势就是查询快。如果要查询完整的数据行使用非聚集索引往往需要回表才能实现而使用聚集索引则能一步到位。
不过聚集索引也有一些劣势
聚集索引可以减少磁盘 IO 的次数这在传统的机械硬盘中是很有优势的不过要是固态硬盘或者内存有时候为了提高操作效率数据库服务器会整一个比较大的内存这个优势就不明显了。 聚集索引在插入的时候最好是主键自增自增主键插入的时候比较快直接插入即可不会涉及到叶子节点分裂等问题不需要挪动其他记录而其他非自增主键插入的时候可能要插入到两个已有的数据中间就有可能导致叶子节点分裂等问题插入效率低要挪动其他记录。如果聚集索引在插入的时候不是自增主键插入效率就会比较低。2.2 非聚集索引非聚集索引我们一般也称为二级索引或者辅助索引对于非聚集索引数据库会有单独的存储空间来存放。非聚集索引在查找的时候要经过两个步骤例如执行 select * from user where usernamexiaoming假设 username 字段是非聚集索引那么此时需要先搜索 username 这一列索引的 BTree这个 BTree 的叶子结点存储的不是完整的数据行而是主键值当我们搜索完成后得到主键的值然后拿着主键值再去搜索主键索引的 BTree就可以获取到一行完整的数据。
所以如果我们在查询中用到了非聚集索引那么就会搜索两棵 BTree第一次搜索 BTree 拿到主键值后再去搜索聚集索引的 BTree这个过程就是所谓的回表。
一张表只能有一个聚集索引但可以有多个非聚集索引。使用聚集索引的时候数据的查询效率高但如果对数据进行插入删除更新等操作效率会比非聚集索引低。
