MySQL数据库索引

MySQL数据库索引

索引

介绍索引之前 我们先了解一下什么是键、主键、外键
键就是关键字，是关系模型中一个非常重要的元素
主键是唯一标识表的行数据，一个主键值对应一行数据。主键由一个或者多个字段组成，其值具有唯一性，不允许为空值（null）一个表只有一个主键。外键 一个关系数据库通常包括多个表，通过外键可以使这个表关联起来。外键是用于建立和加强两个表数据治安的链接的一列或者多列。通过将表中主键值得一列或者多列添加到另一个表，可以创建两个表之间的链接，这个列就成为第二个表的外键。

索引的概念

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

索引的作用

数据库利用各种快速定位技术，能够大大加快查询速率
当表很大或查询涉及到多个表时，可以成干上万倍地提
高查询速度
可以降低数据库的IO成本，并且还可以降低数据库的排
序成本
通过创建唯━性索引保证数据表数据的唯一性
可以加快表与表之间的连接
在使用分组和排序时，可大大减少分组和排序时间

索引的分类

普通索引

最基本的索引类型，没有唯一性之类的限制

唯一性索引

与“普通索引”基本相同
与普通索引的区别是索引列的所有值只能出现一次，即必须唯一

主键索引

是一种特殊的唯一索引，指定为“PRIMARY KEY“

组合索引

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

全文索引

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

创建索引的原则依据

表的主键、外键必须有索引
记录数超过300行的表应该有索引
经常与其他表进行连接的表，在连接字段上应该建立索引
唯一性太差的字段不适合建立索引
更新太频繁地字段不适合创建索引

关于索引的实验

1、创建数据库，创建表

show databases;
#查看数据库
create database school;
#创建数据库school
show databases;
#查看创建的school
use school; 
#进入school数据库
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);
#ID是4位并且不能为空 ID是主键 ID可以自动增量
#名字是的文本类型10位的 不能为空  
#地址 文本类型是50位 南京
#年龄是三个字段的 并且不能为
 insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22); 
#在表info中插入名字 地址和年龄
select * from info;
#查看这个表的情况
desc info; 
#查看表的结构

另一种方法创建普通索引

 create index index_age on info (age);
#针对info表中，age创建索引
 show index from info; 
#查看这个表中的索引情况
 drop index index_age on info;
#删除索引
show index from info;  
#查看索引，发现索引已经删除

2、创建唯一索引 还以库school和表info为列

create unique index unique_name on info (name); 
#在info (name)上创建唯一索引
show index from info;
drop index unique_name on info;
#删除唯一索引##
#使用alter table方法创建唯一索引#
alter table info add unique index_name (name);

3、组合索引

create table user3 (    
name varchar(10) not null,   
age int(3) not null,    
sex tinyint(1) not null,
index user3(name,age,sex));
#下面是查看这个索user3
show keys from user3;

4、全文索引

create table user4 (
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));

也或者用alter table创建索引
alter table user4 add fulltext index_content(content);
也或者用fulltext创建全文索引
create fulltext index index_content on user4(content); 
#查看这个索引
show keys from user4;

5、主键索引

create table user2 (
 id int(4) not null  auto_increment,
 name varchar(10) not null,
 age int(3) not null,
 primary key (`id`));