文章目录
- 索引分类
- 普通索引
- 唯一性索引
- 主键索引
- 单列索引
- 多列索引
- 全文索引
- 如何使用索引
- 创建索引
- 删除索引
- 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 book\G;
*************************** 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修饰的唯一索引不能被删除 - 删除表中列时，如果该列是索引的组成部分，则该列也会从索引中删除，如果组成索引的所有列都被删除，那么索引也就不存在了
8.0版本索引新特性
降序索引
降序索引是降序存储，将储存在一个特定列后者多列中的数据按降序排序，之前在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';
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
