MySQL~索引分类和向表中添加删除索引、8.0版本新特性：降序和隐藏_云计算

发布时间:2024-07-11 19:56

阅读量:14

显式创建：

手动添加索引

第一种：创建表时添加索引

隐式创建
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;

注意：

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';