详解MariaDB数据库的索引-阿里云开发者社区

1.什么是索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。

对于索引，会保存在额外的文件中。

2.索引的类型

1、普通索引
2、唯一性索引
3、主键索引（主索引）
4、复合索引
5、全文索引

2.1. 普通索引

最基本的索引，不具备唯一性，仅加快查询速度

2.1.1 创建普通索引

方法一：创建表时添加索引

语法：

create table 表名（
    列定义
    index 索引名称 （字段）
    index 索引名称 （字段）
）

注：可以使用key关键字，也可以使用index关键字。索引名称，可以加也可以不加，不加使用字段名作为索引名。

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  |     | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

MariaDB [book]> create table demo(id int(4),name varchar(20),pwd varchar(20),index(pwd));
Query OK, 0 rows affected (0.08 sec)

MariaDB [book]> create table demo1(id int(4),name varchar(20),pwd varchar(20),key(pwd));
Query OK, 0 rows affected (0.01 sec)

MariaDB [book]> create table demo2(id int(4),name varchar(20),pwd varchar(20),key index_pwd(pwd));      # 为索引加上名称
Query OK, 0 rows affected (0.02 sec)

注意

index和 key作用是相同的

方法二：当表创建完成后，使用alter为表添加索引：
语法：

alter table 表名 add index 索引名称 （字段1，字段2.....）;

例子：

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  |     | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

MariaDB [book]> alter table books add key (bName,bTypeId);
Query OK, 44 rows affected (0.01 sec)              
Records: 44  Duplicates: 0  Warnings: 0

MariaDB [book]> desc books;         # 可以看到在bName字段上添加的索引
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  | MUL | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

2.1.2 查看索引

语法：

desc 索引名(或索引字段)

例子：

MariaDB [book]> desc demo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| pwd   | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [book]> desc demo1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| pwd   | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [book]> desc demo2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| pwd   | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  | MUL | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

注意：如果Key是MUL, 那么该列的值可以重复。该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。

2.1.3 删除索引

语法：

alter table 表名 drop key 索引名(或字段名);

例子：

MariaDB [book]> alter table demo drop key pwd;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [book]> alter table demo1 drop key pwd;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [book]> alter table demo2 drop key index_pwd;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [book]> alter table books add key (bName,bTypeId);
Query OK, 44 rows affected (0.01 sec)              
Records: 44  Duplicates: 0  Warnings: 0

2.2 唯一索引

与普通索引基本相同，但有一个区别：

索引列的所有值都只能出现一次，即必须唯一，用来约束内容。
字段值只能出现一次时，应该加唯一索引。
唯一性允许有NULL值<允许为空>。

2.2.1 创建唯一索引

方法一：创建表时加唯一索引

语法：

create table 表名(
    列定义：
    unique key 索引名 （字段）;
)

注意：常用在值不能重复的字段上，比如说用户名，电话号码，身份证号。

例子：

MariaDB [book]> create table demo3(id int(4) auto_increment primary key,uName varchar(20),uPwd varchar(20),unique index(uName));
Query OK, 0 rows affected (0.07 sec)

MariaDB [book]> desc demo3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| uName | varchar(20) | YES  | UNI | NULL    |                |
| uPwd  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

方法二：修改表时加唯一索引

语法：

alter table 表名 add unique 索引名 (字段);

例子：

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  |     | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

MariaDB [book]> alter table books add unique index_bName (bName);
Query OK, 43 rows affected (0.01 sec)              
Records: 43  Duplicates: 0  Warnings: 0

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  | UNI | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.02 sec)

2.2.2 删除唯一索引

alter table 表名 drop key 索引名(或字段名);

例子：

MariaDB [book]> alter table books drop key index_bName;
Query OK, 43 rows affected (0.01 sec)              
Records: 43  Duplicates: 0  Warnings: 0

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  |     | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

MariaDB [book]> desc demo3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| uName | varchar(20) | YES  | UNI | NULL    |                |
| uPwd  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

MariaDB [book]> alter table demo3 drop key uName;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [book]> desc demo3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| uName | varchar(20) | YES  |     | NULL    |                |
| uPwd  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

2.3 主键索引

查询数据库，按主键查询是最快的，每个表只能有一个主键列，可以有多个普通索引列。

主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，但不允许为空

2.3.1 创建主键索引

方法一：创建表创建主键索引

MariaDB [book]> create table demo4 (id int(4) not null auto_increment,name varchar(4) default null,primary key(id));
Query OK, 0 rows affected (0.02 sec)

MariaDB [book]> desc demo4;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(4)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(4) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

MariaDB [book]> show index from demo4;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| demo4 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

方法二：创建表后添加<不推荐>

MariaDB [book]> drop table demo4;
Query OK, 0 rows affected (0.01 sec)

MariaDB [book]> create table demo4 (id int(4) not null auto_increment primary key,name varchar(4) not null);
Query OK, 0 rows affected (0.02 sec)

MariaDB [book]> alter table demo4 change id id int(4) not null;
Query OK, 0 rows affected (0.03 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [book]> desc demo4;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(4)     | NO   | PRI | NULL    |       |
| name  | varchar(4) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [book]> show index from demo4;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| demo4 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

2.3.2 删除主键索引

MariaDB [book]> alter table demo4 drop primary key;
Query OK, 0 rows affected (0.03 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [book]> desc demo4;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(4)     | NO   |     | NULL    |       |
| name  | varchar(4) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [book]> show index from demo4;
Empty set (0.00 sec)

主键索引，唯一性索引区别：主键索引不能有NULL，唯一性索引可以有空值

2.4 复合索引

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引

复合索引专门用于组合搜索，其效率大于索引合并

例：创建一个表存放服务器允许或拒绝的IP和port，但记录中IP和port要唯一。

MariaDB [book]> create table firewall(host varchar(15) not null,port smallint(4) not null,access enum("deny","allow") not null,primary key(host,port)); # 用host和port列作复合索引
Query OK, 0 rows affected (0.11 sec)

MariaDB [book]> insert into firewall values("192.168.81.100",22,"deny");
Query OK, 1 row affected (0.02 sec)

MariaDB [book]> select * from firewall;
+---------------+------+--------+
| host          | port | access |
+---------------+------+--------+
| 192.168.81.100 |   22 | deny   |
+---------------+------+--------+
1 row in set (0.00 sec)

MariaDB [book]> insert into firewall values("192.168.81.101",22,"allow");
Query OK, 1 row affected (0.04 sec)

MariaDB [book]> insert into firewall values("192.168.81.100",22,"deny");    # 插入数据时，IP和port要唯一，否则会报错
ERROR 1062 (23000): Duplicate entry '192.168.81.100-22' for key 'PRIMARY'
MariaDB [book]> select * from firewall;
+----------------+------+--------+
| host           | port | access |
+----------------+------+--------+
| 192.168.81.100  |   22 | deny   |
| 192.168.81.101 |   22 | allow  |
+----------------+------+--------+
2 rows in set (0.01 sec)

MariaDB [book]> desc firewall;              # 查看索引
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| host   | varchar(15)          | NO   | PRI | NULL    |       |
| port   | smallint(4)          | NO   | PRI | NULL    |       |
| access | enum('deny','allow') | NO   |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

建表的时候如果加各种索引，顺序如下：

create table 表名（字段定义，PRIMARYKEY  (`bId`),UNIQUE KEY `bi` (`bImg`),KEY `bn` (`bName`),KEY `ba` (`author`)）

2.5 全文索引 (FULLTEXT INDEX)

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。

它能够利用"分词技术"等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果

mysql 在数据量较大的情况下，高并发连接的情况下:

select 语句  where bName like '%网%'
使用% 和　_ 通配符进行查找，因为索引里面保存的是一堆指针，不会保存具体内容，只能进行全表扫描，造成数据库压力大

全文索引只能用在varchar和text字段中

2.5.1 创建全文索引

方法一：创建表时创建

语法：

create table 表名（
    列定义，
    fulltext key 索引名 （字段）;
）

方法二：修改表时添加
语法：

alter table 表名 add fulltext 索引名 （字段）;

例子：

MariaDB [book]> show create table books;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| books | CREATE TABLE `books` (
  `bId` int(4) NOT NULL AUTO_INCREMENT,
  `bName` varchar(255) DEFAULT NULL,
  `bTypeId` enum('1','2','3','4','5','6','7','8','9','10') DEFAULT NULL,
  `publishing` varchar(255) DEFAULT NULL,
  `price` int(4) DEFAULT NULL,
  `pubDate` date DEFAULT NULL,
  `author` varchar(30) DEFAULT NULL,
  `ISBN` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`bId`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MariaDB [book]> alter table books add fulltext index_bName (`bName`);
Query OK, 43 rows affected (0.04 sec)              
Records: 43  Duplicates: 0  Warnings: 0

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  | MUL | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

MariaDB [book]> alter table books drop key index_bName;
Query OK, 43 rows affected (0.03 sec)              
Records: 43  Duplicates: 0  Warnings: 0

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  |     | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

MariaDB [book]> alter table books add fulltext index_bName (`bName`,`publishing`);
Query OK, 43 rows affected (0.02 sec)              
Records: 43  Duplicates: 0  Warnings: 0

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  | MUL | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

MariaDB [book]> alter table books drop key index_bName;
Query OK, 43 rows affected (0.02 sec)              
Records: 43  Duplicates: 0  Warnings: 0

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  |     | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

MariaDB [book]> alter table books add fulltext index_bName (`publishing`);
Query OK, 43 rows affected (0.02 sec)              
Records: 43  Duplicates: 0  Warnings: 0

MariaDB [book]> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  |     | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  | MUL | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

2.5.2 删除全文索引

MariaDB [book]> alter table books drop key index_bName;
Query OK, 39 rows affected (0.02 sec)              
Records: 39  Duplicates: 0  Warnings: 0

MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效
MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。

3.索引设计原则

1、索引并非越多越好
2、数据量不大不需要建立索引
3、列中的值变化不多不需要建立索引 row id
4、经常排序（order by）和分组（group by）的列需要建立索引
5、唯一性约束对应使用唯一性索引

4.索引的优点：

由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会很快，查询时间会减少

5.索引的缺点

1.索引是以文件存储的。如果索引过多，占磁盘空间较大。而且他影响： insert ,update ,delete 执行时间。
2.索引中数据必须与数据表数据同步：如果索引过多，当表中数据更新的时候后，索引也要同步更新，这就降低了效率。

详解MariaDB数据库的索引

1.什么是索引

2.索引的类型

2.1. 普通索引

2.1.1 创建普通索引

2.1.2 查看索引

2.1.3 删除索引

2.2 唯一索引

2.2.1 创建唯一索引

2.2.2 删除唯一索引

2.3 主键索引

2.3.1 创建主键索引

2.3.2 删除主键索引

2.4 复合索引

2.5 全文索引 (FULLTEXT INDEX)

2.5.1 创建全文索引

2.5.2 删除全文索引

3.索引设计原则

4.索引的优点：

5.索引的缺点

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

详解MariaDB数据库的索引

1.什么是索引

2.索引的类型

2.1. 普通索引

2.1.1 创建普通索引

2.1.2 查看索引

2.1.3 删除索引

2.2 唯一索引

2.2.1 创建唯一索引

2.2.2 删除唯一索引

2.3 主键索引

2.3.1 创建主键索引

2.3.2 删除主键索引

2.4 复合索引

2.5 全文索引 (FULLTEXT INDEX)

2.5.1 创建全文索引

2.5.2 删除全文索引

3.索引设计原则

4.索引的优点：

5.索引的缺点

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像