数据库索引

最新推荐文章于 2024-01-07 15:45:48 发布

123.com

最新推荐文章于 2024-01-07 15:45:48 发布

阅读量129

点赞数

分类专栏： mysql 文章标签：数据库索引

本文链接：https://blog.csdn.net/qq_38810767/article/details/86597140

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1.索引特性

索引的作用：提高海量数据的检索性能

索引的分类：

主键索引：设置主键（primary key）
唯一键索引：设置唯一键（unique）
普通键索引：设置普通键（index）
全文索引：FULLTEXT 解决文本索引的问题

索引实现：

采用的结构

搜索树

搜索树（B树系列）时间复杂度（O(log(n))）
检索效率和高度有关
可以提供有序性
相比较二叉搜索树，高度更低

哈希（hash）散列表
检索率高时间复杂度（O(1)）
但是无序索引实现

2.各个索引的创建：

索引创建原则：

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引

1.创建一个主键索引：

a.在创建表的时候进行创建

方法一：给指定的字段加上主键关键字

create table test(id int primary key,
name varchar (20));

结果：Query OK, 0 rows affected (0.06 sec)

方法二：
可以指定多列为主键

create table test1(id int, name varchar(20), primary key(id));

结果：Query OK, 0 rows affected (0.04 sec)

b.在已经创建的表上加上主键

create table test2(id int, name varchar(20));

alter table test add primary key(id);

结果：Query OK, 1 row affected (0.05 sec)      
Records: 1  Duplicates: 0  Warnings: 0

主键的特性：

一个表中只能有一个主键，但是主键可以由多个字段组成
主键的索引效率高（主键不可复用）
主键的字段不能为null，也不能有重复值
主键基本上都是int型

2.创建唯一键

a.在创建表的时候进行创建

方法一：给指定的字段加上唯一关键字（unique）

create table test(id int ,
name varchar (20) unique);

结果：Query OK, 0 rows affected (0.06 sec)

方法二：
可以指定多列为唯一键

create table test1(id int, name varchar(20), unique(name));

结果：Query OK, 0 rows affected (0.04 sec)

b.在已经创建的表上加上唯一键索引

create table test2(id int, name varchar(20));

alter table test add unique(name);

结果：Query OK, 1 row affected (0.05 sec)      
Records: 1  Duplicates: 0  Warnings: 0

主键的特性：

一个表中只能有一个主键，但是主键可以由多个字段组成
主键的索引效率高（主键不可复用）
主键的字段不能为null，也不能有重复值
主键基本上都是int型

3.创建一个普通索引

a.在创建表的时候创建

1.在表的定义最后，指定某列为索引

create table dab(id int primary key, name varchar(20),index(name));

结果：Query OK, 0 rows affected (0.05 sec)

2.创建完表以后指定某列为普通索引

create table dab1(id int primary key,name varchar (29));

alter table dab1 add index(name);

结果:Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.创建一个索引名为 idx_name 的索引

create table dab2(id int primary key,name varchar(20));

create index idx_name on  dab2(name);

结果：Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

普通索引的特点：

一个表中可以有多个索引，实际应用比较多
普通索引针对的是某个字段有重复值的情况，如果想加索引但是这个字段有重复值，那么可以用普通索引

4.全文索引

使用时机：对大量文章字段或者文字字段检索时

要求：要求表的存储引擎必须是MyISAM

默认全文检索：默认的全文索引支持英文，不支持中文

中文检索：使用sphinx的中文版(coreseek)。

创建：

create  table articles1(
id int  auto_increment primary key,
title varchar(100),
body TEXT,
FULLTEXT(title,body)
)engine=MyISAM;

结果：Query OK, 0 rows affected (0.02 sec)

插入数据：

INSERT INTO articles1(title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');


结果：Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

查询database数据：

select *from articles1 where body like '%database%';

结果：
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)

用explain查看是否用到全文索引

explain select *from articles1 where body like "%database%"\G;

\G:将行标题变成列标题显示


结果：
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles1
         type: ALL
possible_keys: NULL
          key: NULL --没有用到任何键
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

使用全文搜索：

select *from articles1 where match (title,body) against ("database");


结果：
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.02 sec)

查看是否用到索引：

explain select *from articles1 where match (title,body) against ("database")\G;

结果：
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles1
         type: fulltext
possible_keys: title
          key: title--显示用到全文索引
      key_len: 0
          ref: 
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

5.显示表中的所有索引

方法一：

show keys from tablename;


show keys from articles1;

方法二：

show index from articles1\G;

方法三：
显示的信息比较简略

desc articles1;

结果：
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| title | varchar(100) | YES  | MUL | NULL    |                |
| body  | text         | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

索引的删除：

主键删除

alter table 表名 drop primary key;

其他索引删除

方法一：

alter table 表名 drop index 索引名


alter table articles1 drop index title;

结果：
Query OK, 6 rows affected (0.00 sec)               
Records: 6  Duplicates: 0  Warnings: 0



删除后添加添加索引：

alter table articles1 add fulltext(title,body);
结果：
Query OK, 6 rows affected (0.00 sec)               
Records: 6  Duplicates: 0  Warnings: 0