详解MariaDB数据库的索引

简介: 1.什么是索引索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。

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.索引中数据必须与数据表数据同步:如果索引过多,当表中数据更新的时候后,索引也要同步更新,这就降低了效率。
目录
相关文章
|
29天前
|
数据库 索引
深入探索数据库索引技术:回表与索引下推解析
【10月更文挑战第15天】在数据库查询优化的领域中,回表和索引下推是两个核心概念,它们对于提高查询性能至关重要。本文将详细解释这两个术语,并探讨它们在数据库操作中的作用和影响。
46 3
|
29天前
|
数据库 索引
深入理解数据库索引技术:回表与索引下推详解
【10月更文挑战第23天】 在数据库查询性能优化中,索引的使用是提升查询效率的关键。然而,并非所有的索引都能直接加速查询。本文将深入探讨两个重要的数据库索引技术:回表和索引下推,解释它们的概念、工作原理以及对性能的影响。
51 3
|
15天前
|
数据库 索引
数据库索引
数据库索引 1、索引:建立在表一列或多列的辅助对象,目的是加快访问表的数据。 2、索引的优点: (1)、创建唯一性索引,可以确保数据的唯一性; (2)、大大加快数据检索速度; (3)、加速表与表之间的连接; (4)、在查询过程中,使用优化隐藏器,提高系统性能。 3、索引的缺点: (1)、创建和维护索引需要耗费时间,随数据量增加而增加; (2)、索引占用物理空间; (3)、对表的数据进行增删改时,索引需要动态维护,降低了数据的维护速度。
30 2
|
2月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
64 3
Mysql(4)—数据库索引
|
25天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
133 1
|
19天前
|
存储 关系型数据库 数据库
Postgres数据库BRIN索引介绍
BRIN索引是PostgreSQL提供的一种高效、轻量级的索引类型,特别适用于大规模、顺序数据的范围查询。通过存储数据块的摘要信息,BRIN索引在降低存储和维护成本的同时,提供了良好的查询性能。然而,其适用场景有限,不适合随机数据分布或频繁更新的场景。在选择索引类型时,需根据数据特性和查询需求进行权衡。希望本文对你理解和使用PostgreSQL的BRIN索引有所帮助。
29 0
|
26天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
56 0
|
2月前
|
存储 关系型数据库 MySQL
Maria DB Workbench支持哪些数据库引擎
【10月更文挑战第17天】Maria DB Workbench支持哪些数据库引擎
21 0
|
2月前
|
关系型数据库 MySQL 数据库
深入浅出MySQL索引优化:提升数据库性能的关键
在这个数据驱动的时代,数据库性能的优劣直接关系到应用的响应速度和用户体验。MySQL作为广泛使用的数据库之一,其索引优化是提升查询性能的关键。本文将带你一探MySQL索引的内部机制,分析索引的类型及其适用场景,并通过实际案例演示如何诊断和优化索引,以实现数据库性能的飞跃。
|
2月前
|
存储 SQL 数据库
深入理解数据库索引:提升查询性能的关键
数据库索引是优化查询性能的重要工具。本文将带你深入探索索引的内部结构和工作原理,揭示如何通过合理使用索引来加速数据库查询,同时避免常见的索引陷阱。

推荐镜像

更多
下一篇
无影云桌面