目录
一、索引介绍
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 小结
■索引的概念
■索引的作用
■索引的分类
■索引的创建方法
■索引的查看
■索引的删除