MySQL索引概念、分类（普通索引、唯一索引、主键索引、组合索引、全文索引）、作用、创建方法、查看、删除

于 2020-09-11 14:08:52 发布
阅读量950 收藏
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_48191138/article/details/108507316
版权

目录

一、索引介绍

1.1 索引的概念

1.1.1 数据库索引

■是一个排序的列表，存储着索引值和这个值所对应的物理地址
■无需对整个表进行扫描，通过物理地址就可以找到所需数据
■是表中一列或若干列值排序的方法
■需要额外的磁盘空间

1.1.2 索引的作用

■设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率
■特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成千倍
■可以降低数据库的IO读写成本，并且索引还可以降低数据库的排序成本
■通过创建唯一性索引保证数据表数据的唯一性
■可以加快表与表之间的连接
■在使用分组和排序时，可大大减少分组和排序时间

1.2 索引的分类

1.2.1 普通索引

■最基本的索引类型，而且它没有唯一性之类的限制
■创建方式
●第一种方法：create方法创建

mysql> show databases;   '//先查看数据库的表'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bbs                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
mysql> create database school;    '//创建school库'
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed
mysql> create table info (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> address varchar(50) default 'nanjing',
    -> age int(3) not null);    '//创建info表，结构'
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info(name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);  '//导入数据'
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from info;  '//查看'
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql> desc info;    '//查看表结构'
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(4)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   |     | NULL    |                |
| address | varchar(50) | YES  |     | nanjing |                |
| age     | int(3)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create index index_age on info(age);  '//针对info表的age创建索引'
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from info;       '//查看age索引信息'
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_age |            1 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

●第二种方法：alter table方法创建

mysql> alter table info1 add index index_age(age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from info1;       '//查看age索引信息'
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info1  |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info1  |          1 | index_age |            1 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

●第三种方法：新建表的方法创建

mysql> create table info2 (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> score decimal not null,
    -> hobby int(2) not null default '1',
    -> index index_scroce (score));           
mysql> show index from info2;    '//查看'
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info2 |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| info2 |          1 | index_scrore |            1 | score       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

■’'和null的区别
‘’：空字符串（不占空间）
null：空对象（占空间）

1.2.2 唯一性索引

■这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一
■唯一性索引允许为空，但是只能有一次为空
●第一种方法：使用index方法创建

mysql> drop index index_age on info;  '//删除之前的索引'
'//或者：'
mysql> alter table info drop index index_age；  '//删除之前的索引'
mysql> create unique index unique_name on info (name);
'//创建索引'
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from info;   '//查看'
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY     |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | unique_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
mysql> drop index unique_name on info;  '//删除唯一索引'

●第二种方法：使用alter table方法创建唯一索引

mysql> alter table info add unique index_name (name);  '//创建索引'
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

●第三种方法：使用新建表的方法创建唯一索引

mysql> create table info1 (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> score decimal not null,
    -> hobby int(2) not null default '1',
    -> unique index name (score));   '//创建唯一索引'
Query OK, 0 rows affected (0.01 sec)
mysql> show index from info1;  '//查看唯一索引，name前的数字为0，对应Non——unique'
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info1 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| info1 |          0 | name     |            1 | score       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

1.2.3 主键索引

■是一种特殊的唯一性索引，指定为“PRIMARY KEY”
■一个表只能有一个主键，不允许有空值
●创建方法：

mysql> create table info2 (id int(4) not null  auto_increment,name varchar(10) not null,age int(3) not null,primary key (`id`));   '//创建主键索引'
Query OK, 0 rows affected (0.01 sec)
mysql> show index from info2;   '//查看主键索引，PRIMARY'
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info2 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

1.2.4 组合索引（单列索引与多列索引）

■可以是单列上创建的索引，也可以是多列上创建的索引
■最左原则，从左往右依次执行
■创建组合索引的方式

mysql> create table info3 (name varchar(10) not null,age int(3) not null,sex tinyint(1) not null,index info3(name,age,sex));  '//创建组合索引'
Query OK, 0 rows affected (0.00 sec)
mysql> show keys from info3;   '//查看组合索引，发现keyname统统是一样的'
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info3 |          1 | info3    |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| info3 |          1 | info3    |            2 | age         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| info3 |          1 | info3    |            3 | sex         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

1.2.5 全文索引

■MySQL从3.23.23版开始支持全文索引和全文检索
■索引类型为FULLTEXT
■可以在CHAR、VARCHAR或TEXT类型的列上创建

mysql> create fulltext index content on info4（content）;
mysql> create table info4 (id int(11) not null auto_increment,tile char(255) character set utf8 collate utf8_general_ci not null,content text character set utf8 collate utf8_general_ci not null,primary key (`id`),fulltext (content));  '//创建全文索引'
Query OK, 0 rows affected (0.03 sec)
mysql> show keys from info4;   '//查看全文索引，fulltext'
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info4 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| info4 |          1 | content  |            1 | content     | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

1.3 创建索引的原则依据

■表的主键、外键必须有索引
■数据量超过300行的表应该有索引
■经常与其他表进行连接的表，在连接字段上应该建立索引
■唯一性太差的字段不适合建立索引
■更新太频繁地字段不适合创建索引
■经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引
■索引应该建在选择性高的字段上
■索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引（可以是fulltext索引）

1.4 小结

■索引的概念
■索引的作用
■索引的分类
■索引的创建方法
■索引的查看
■索引的删除

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值