- 索引分类
- 普通索引
- 唯一性索引
- 主键索引
- 单列索引
- 多列索引
- 全文索引
- 如何使用索引
- 创建索引
- 删除索引
- 8.0版本索引新特性
- 降序索引
- 隐藏索引
按照功能划分，可以分为普通、唯一、主键、全文
按照物理实现，可以分为聚集、非聚集
按照字段个数，可以分为单列、多列
- 没有任何的限制条件，单纯用来提高查询效率，非常普通的索引.
- 这个索引可以用于任何的数据类型。
- 索引值是否唯一和非空，由字段本身的约束决定
- 限制该索引的值唯一，可以为空
- 一张表里可以有多个唯一索引
- 唯一约束通过唯一索引实现
- 使用unique创建
- 是特殊的唯一索引
- 不可为空
- 一张表里最多一个主键索引
- 在单个字段上创建索引
- 该索引可以是普通/唯一/主键索引中的任何一种，只要索引只对应一个字段
- 在多个字段上创建索引
- 遵循最左前缀集合，多个字段组成一个组合，只有查询时涉及到第一个字段才会用这个组合
- 也叫全文检索，是搜索引擎的关键技术
- 适合大型数据集，小型数据集作用不大
- 使用full text创建
- 只能创建在字符、字符串、文本类型的字段上
- 查询数据量非常大的字符串字段的时候，使用全文索引，效率非常高
如何使用索引 创建索引现在solr、ElasticSearch等专门的搜索引擎正在逐渐替代这种方式
有两种方式：隐式创建和显式创建。
隐式创建：
声明约束时，会自动添加索引
显式创建：
手动添加索引
第一种：创建表时添加索引
隐式创建
create表的时候约束了某个字段，也就自动添加了索引
mysql> CREATE TABLE dept(
-> dept_id INT PRIMARY KEY AUTO_INCREMENT,
-> dept_name VARCHAr(20)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE emp(
-> emp_id INT PRIMARY KEY AUTO_INCREMENT,
-> emp_name VARCHAr(20) UNIQUE,
-> dept_id INT,
-> CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
-> );
Query OK, 0 rows affected (0.09 sec)
显式创建一个普通索引
在写完表的字段后直接 index 索引名（哪个字段）
mysql> create table book(
-> book_id int,
-> book_name varchar(100),
-> atthors varchar(100),
-> comment varchar(100),
-> year_publication year,
# 创建索引
-> index id_bname(book_name)
-> );
Query OK, 0 rows affected (0.03 sec)
查看索引：
方式1：
mysql> show create table bookG;
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` (
`book_id` int DEFAULT NULL,
`book_name` varchar(100) DEFAULT NULL,
`atthors` varchar(100) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL,
`year_publication` year DEFAULT NULL,
KEY `id_bname` (`book_name`) # 索引位置
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.01 sec)
方式2:
mysql> show index from book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| book | 1 | id_bname | 1 | book_name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.02 sec)
添加索引的作用/效果：
根据索引字段查询时，效率成倍提升 mysql> select * from book where book_name='mysql';
创建唯一索引
unique index 索引名（绑定字段）
mysql> CREATE TABLE book1(
-> book_id INT ,
-> book_name VARCHAr(100),
-> AUTHORS VARCHAr(100),
-> info VARCHAr(100) ,
-> COMMENT VARCHAr(100),
-> year_publication YEAR,
-> #声明索引
-> UNIQUE INDEX uk_idx_cmt(COMMENT)
-> );
Query OK, 0 rows affected (0.03 sec)
主键索引
#通过定义主键约束的方式定义主键索引 CREATE TABLE book2( book_id INT PRIMARY KEY , book_name VARCHAr(100), AUTHORS VARCHAr(100), info VARCHAr(100) , COMMENT VARCHAr(100), year_publication YEAR ); SHOW INDEX FROM book2; #通过删除主键约束的方式删除主键索引 ALTER TABLE book2 DROp PRIMARY KEY;
全文索引
# 创建全文索引 CREATE TABLE test4( id INT NOT NULL, NAME CHAr(30) NOT NULL, age INT NOT NULL, info VARCHAr(255), FULLTEXT INDEX futxt_idx_info(info(50)) ) SHOW INDEX FROM test4;
第二种：表创建完成后添加索引
方式1： alter table.....add.... ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT); ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name); ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info); 方式2：create index.....on... CREATE INDEX idx_cmt ON book6(COMMENT); CREATE UNIQUE INDEX uk_idx_bname ON book6(book_name); CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);删除索引
方式1：ALTER TABLE .... DROp INDEX .... ALTER TABLE book5 DROP INDEX idx_cmt; 方式2：DROP INDEX ... ON ... DROP INDEX uk_idx_bname ON book5;
注意：
- 被auto-increment修饰的唯一索引不能被删除
- 删除表中列时，如果该列是索引的组成部分，则该列也会从索引中删除，如果组成索引的所有列都被删除，那么索引也就不存在了
降序索引是降序存储，将储存在一个特定列后者多列中的数据按降序排序，之前在B+树中时升序存储数据页，每次查询一张表都需要反向扫描，非常影响效率。
在一些场景中，如果需要对多个列排序，且顺序要求不一致,可以使用降序索引，避免重复排序浪费资源.
使用desc 在索引中指定 mysql> CREATE TABLE ts1(a INT,b INT,INDEX idx_a_b(a ASC,b DESC)); Query OK, 0 rows affected (0.04 sec) mysql> SHOW CREATE TABLE ts1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------+ | ts1 | CREATE TABLE `ts1` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, KEY `idx_a_b` (`a`,`b` DESC) #DESC就表示索引是降序搜寻 ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+-----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) //查看查询数量 降序情况下一个800条数据的表中只扫描了5个数据就可以查询到，升序存储下需要查询799条数据 EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;隐藏索引
在之前的版本，只能显式的删除索引，如果删除索引后出现错误，又只能显式的创建回来.当表中数据非常大或数据本身比较大，这种操作就非常消耗资源.
8.x版本后开始支持 隐藏索引，将需要删除的索引设为隐藏，就不会再使用这个索引，如果系统没有出现任何错误，就可以彻底删除索引.
索引后注释invisible 即可隐藏 #① 创建表时，隐藏索引 CREATE TABLE book7( book_id INT , book_name VARCHAr(100), AUTHORS VARCHAr(100), info VARCHAr(100) , COMMENT VARCHAr(100), year_publication YEAR, #创建不可见的索引 INDEX idx_cmt(COMMENT) invisible ); SHOW INDEX FROM book7; EXPLAIN SELECt * FROM book7 WHERe COMMENT = 'mysql....'; #② 创建表以后 ALTER TABLE book7 ADD UNIQUE INDEX uk_idx_bname(book_name) invisible; CREATE INDEX idx_year_pub ON book7(year_publication); EXPLAIN SELECt * FROM book7 WHERe year_publication = '2022'; #修改索引的可见性 ALTER TABLE book7 ALTER INDEX idx_year_pub invisible; #可见--->不可见 ALTER TABLE book7 ALTER INDEX idx_cmt visible; #不可见 ---> 可见 #了解：使隐藏索引对查询优化器可见 SELECt @@optimizer_switch G SET SESSION optimizer_switch="use_invisible_indexes=on"; EXPLAIN SELECT * FROM book7 WHERe year_publication = '2022';
