mysql索引分类以及创建使用
一、索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
二、索引类型
Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。
1. FULLTEXT
即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
2. HASH
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
3. BTREE
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
4. RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE，RTREE的优势在于范围查找。
按字段特性分类
MySQL索引按字段特性分类可分为：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
-
主键索引(PRIMARY KEY)
建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。 -
唯一索引(UNIQUE)
建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。 -
普通索引(INDEX)
建立在普通字段上的索引被称为普通索引。 -
全文索引(FULLTEXT)
MyISAM 存储引擎支持Full-text索引，用于查找文本中的关键词，而不是直接比较是否相等。Full-text索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。
三、索引种类
- 普通索引：仅加速查询
- 唯一索引：加速查询 + 列值唯一（可以有null）
- 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
- 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
- 全文索引：对文本的内容进行分词，进行搜索
ps.
索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
四、操作索引
//创建索引语法 CREATE TABLE table_name[col_name data type] [unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]
1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；
2、index和key为同义词，两者作用相同，用来指定创建索引
3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；
4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；
5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
6、asc或desc指定升序或降序的索引值存储
主键索引：
# 查看创建的索引关键词 PRIMARY KEY (`id`)
唯一索引：
-
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
# 创建唯一索引 mysql> alter table t1 add unique idx_u1(`u1`); # 查看创建的索引关键词 UNIQUE KEY `idx_u1` (`u1`)
普通索引-单列索引：
-
即索引创建在单个列上
# 创建单列索引 mysql> alter table t1 add index idx_u1(`u1`); # 查看创建的索引关键词 KEY `idx_u1` (`u1`)
普通索引-多列索引：
-
创建多列索引时记得遵循最左匹配原则，把区分度较高的字段放前面
# 创建多列索引 mysql> alter table t1 add index idx_u1_u2(`u1`,`u2`); # 查看创建的多列索引关键词 KEY `idx_u1_u2` (`u1`,`u2`)
案例：
DROP TABLE IF EXISTS `{dbprefix}order_cart`; CREATE TABLE IF NOT EXISTS `{dbprefix}order_cart` ( `id` BIGINT(18) unsigned NOT NULL AUTO_INCREMENT, `mid` varchar(100) NOT NULL COMMENT '模块mid', `uid` int(10) unsigned NOT NULL COMMENT '购买者', `agent` varchar(100) NOT NULL COMMENT '客户端编码', `cid` int(10) unsigned NOT NULL COMMENT '商品id', `price` decimal(10,2) NOT NULL COMMENT '售价', `quantity` int(10) NOT NULL COMMENT '数量', `sku_name` text NOT NULL COMMENT '商品属性名称', `sku_value` varchar(255) NOT NULL COMMENT '商品属性参数', `title` varchar(255) NOT NULL COMMENT '商品名称', `thumb` varchar(255) NOT NULL COMMENT '缩略图', `url` varchar(255) NOT NULL COMMENT '商品地址', `inputtime` int(10) NOT NULL COMMENT '加入时间', PRIMARY KEY (`id`), KEY `mid` (`mid`), KEY `uid` (`uid`), KEY `agent` (`agent`), KEY `price` (`price`), KEY `sku_value` (`sku_value`), KEY `inputtime` (`inputtime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='我的购物车表';
2022-10-15
参考文档：https://blog.csdn.net/n88Lpo/article/details/125923475
