tp5手机网站开发,网站改版方案流程,seo排名优化关键词,seo网站优化方案MySQL 实验 7#xff1a;索引的操作
索引是对数据表中一列或多列的值进行排序的一种结构#xff0c;索引可以大大提高 MySQL 的检索速度。合理使用索引#xff0c;可以大大提升 SQL 查询的性能。
索引好比是一本书前面的目录#xff0c;假如我们需要从书籍查找与 xx 相关…MySQL 实验 7索引的操作
索引是对数据表中一列或多列的值进行排序的一种结构索引可以大大提高 MySQL 的检索速度。合理使用索引可以大大提升 SQL 查询的性能。
索引好比是一本书前面的目录假如我们需要从书籍查找与 xx 相关的内容我们可以从目录中查找定位到 xx 内容所在页面如果没有设置目录索引则只能逐字逐页阅读文本查找。
当执行查询操作时如果不使用索引MySQL 必须从第一条记录开始读完整个表直到找出相关的行。如果表中查询的列有一个索引MySQL 能够快速到达一个位置去搜索数据文件而不必查看所有数据。
一、索引的优缺点
索引虽然可以提高检索的速度但创建过多的、不必要的索引还会影响数据增、删、改的效率。
1、索引的优点
1索引大大减小了服务器需要扫描的数据量从而大大加快数据的检索速度这也是创建索引的最主要的原因。
2通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。
3可以加速表和表之间的连接。
4在使用分组和排序子句进行数据检索时可以减少查询中分组和排序的时间。
2、索引的缺点
1创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加。
2索引需要占物理空间除了数据表占用数据空间之外每一个索引还要占用一定的物理空间如果需要建立聚簇索引那么需要占用的空间会更大。
3对表中的数据进行增、删、改的时候索引也要动态的维护降低了数据的维护速度。
3、创建索引的原则
1在经常需要检索的列上创建索引可以加快检索的速度。
2在作为主键的列上创建聚簇索引可以保证该列的唯一性和组织表中数据的排列顺序。
3在经常用在连接的列上一般是表中的外键创建索引可以提高连接的速度。
4在经常需要根据范围BETWEENIN进行搜索的列上创建索引。
4在经常需要排序order by的列上创建索引因为索引已经排序这样查询可以利用索引的排序加快排序查询时间。
5在经常使用在 WHERE 子句中的列上面创建索引可以加快条件的判断速度。
6对于那些在查询中很少使用的列不应该创建索引。
7对于那些重复值过多的列也不应该增加索引。
8经常进行数据更新的列不应该创建索引。
二、索引的分类
MySQL 的索引有两种分类方式逻辑分类和物理分类。
1、逻辑分类
1按功能划分索引可以分为四类主键索引、唯一索引、普通索引和全文索引。
主键索引主键索引默认创建为聚簇索引主键索引会改变表中记录的物理顺序。一张表只能创建一个主键索引主键索引所包含的列不允许重复、不允许为 NULL。
唯一索引唯一索引包含的数据列不允许取重复值但允许为 NULL 值。一张表可以创建多个唯一索引索引列的值必须唯一如果是组合索引则唯一索引包含的所有列的组合必须取值唯一。
普通索引一张表可以创建多个普通索引普通索引允许数据重复索引所包含的列允许取 NULL 值。
全文索引查找文本中的关键词主要用于全文检索。
2按索引包含的列数可以分为两类单列索引和多列索引又叫组合索引。
单例索引一个索引只包含一个列一个表可以有多个单例索引。
组合索引一个组合索引包含两个或两个以上的列。查询时遵循组合索引的【最左前缀】原则即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。
2、物理分类
按索引的存储结构划分可以把索引分为聚簇索引也叫聚集索引和非聚簇索引。
1聚簇索引聚簇索引clustered index不是单独的一种索引类型而是一种数据存储方式每张表最多只能拥有一个聚簇索引。表中的数据其实就是按照聚簇索引的顺序进行排列。因此按照聚簇索引查询速度更快。
2非聚簇索引聚簇索引之外的索引称之为非聚簇索引又称为辅助索引。查找数据时首先通过非聚簇索引找到主键值然后到主键索引树中通过主键值找到数据行。
三、创建表的同时创建索引
1主键索引和唯一索引的创建请参见【MySQL 实验6定义数据的完整性】。
2创建普通索引可以使用关键字 key 或 index 创建普通索引。语法格式如下
create table table_name(col_name data_type primary key,col_name data_type,... ,col_name data_type,INDEX|KEY [索引名] (列名[(长度)] , ...)
);
-- 说明
1索引名给创建的索引取一个新名称。如果不指定则采用字段名作为索引名。
2列名指定索引对应的列的名称。列名可以有多个即创建多列索引。
3长度指索引的长度字符串类型才可以使用。
4可以根据表达式创建索引。例如
create table emp02(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30),key(e_name), -- 不指定索引名称index idx_salary(salary), -- 指定索引名称index idx_dept_salary(dept_name,salary), -- 指定多列索引key idx_phone(phone(11)) -- 指定索引长度
);-- 查看表结构
mysql desc emp02;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| e_id | int(11) | NO | PRI | NULL | |
| e_name | char(20) | YES | MUL | NULL | |
| birth | date | YES | | NULL | |
| salary | decimal(10,2) | YES | MUL | NULL | |
| phone | char(20) | YES | MUL | NULL | |
| address | varchar(200) | YES | | NULL | |
| dept_name | char(30) | YES | MUL | NULL | |
-----------------------------------------------------
7 rows in set (0.00 sec)-- 查看索引
mysql show create table emp02\G
*************************** 1. row ***************************Table: emp02
Create Table: CREATE TABLE emp02 (e_id int(11) NOT NULL,e_name char(20) DEFAULT NULL,birth date DEFAULT NULL,salary decimal(10,2) DEFAULT NULL,phone char(20) DEFAULT NULL,address varchar(200) DEFAULT NULL,dept_name char(30) DEFAULT NULL,PRIMARY KEY (e_id),KEY e_name (e_name),KEY idx_salary (salary),KEY idx_dept_salary (dept_name,salary),KEY idx_phone (phone(11))
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)00试环境进行据库
create table emp02(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30),key(e_name), -- 不指定索引名称index idx_salary(salary), -- 指定索引名称index idx_dept_salary(dept_name,salary), -- 指定多列索引key idx_phone(phone(11))
);四、添加索引
数据表创建完成后可以使用 alter table 或 create index 命令添加所需的索引。
1、使用 alter table 添加索引
语法格式如下
ALTER TABLE 表名
ADD INDEX|KEY [索引名](字段名[(长度)], ...);例如
create table emp03(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30)
);-- 添加索引不指定索引名
alter table emp03 add index(e_name);-- 添加索引指定索引名
alter table emp03 add index idx_salary(salary);-- 添加多列索引指定索引名
alter table emp03 add index idx_dept_salary(dept_name, salary);-- 添加索引指定长度
alter table emp03 add index idx_phone(phone(11));-- 查看表结构
mysql desc emp03;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| e_id | int(11) | NO | PRI | NULL | |
| e_name | char(20) | YES | MUL | NULL | |
| birth | date | YES | | NULL | |
| salary | decimal(10,2) | YES | MUL | NULL | |
| phone | char(20) | YES | MUL | NULL | |
| address | varchar(200) | YES | | NULL | |
| dept_name | char(30) | YES | MUL | NULL | |
-----------------------------------------------------
7 rows in set (0.01 sec)-- 查看索引
mysql show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE emp03 (e_id int(11) NOT NULL,e_name char(20) DEFAULT NULL,birth date DEFAULT NULL,salary decimal(10,2) DEFAULT NULL,phone char(20) DEFAULT NULL,address varchar(200) DEFAULT NULL,dept_name char(30) DEFAULT NULL,PRIMARY KEY (e_id),KEY e_name (e_name),KEY idx_salary (salary),KEY idx_dept_salary (dept_name,salary),KEY idx_phone (phone(11))
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)2、使用 create index 添加索引
语法格式如下
CREATE INDEX 索引名 ON 表名(字段名[(长度)], ...);例如
create table emp04(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30)
);-- 添加单列索引
create index idx_name on emp04(e_name);-- 添加多列索引
create index idx_dept_salary on emp04(dept_name,salary);-- 指定索引长度
create index idx_phone on emp04(phone(11));-- 查看表结构
mysql desc emp04;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| e_id | int(11) | NO | PRI | NULL | |
| e_name | char(20) | YES | MUL | NULL | |
| birth | date | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| phone | char(20) | YES | MUL | NULL | |
| address | varchar(200) | YES | | NULL | |
| dept_name | char(30) | YES | MUL | NULL | |
-----------------------------------------------------
7 rows in set (0.01 sec)-- 查看索引
mysql show create table emp04\G
*************************** 1. row ***************************Table: emp04
Create Table: CREATE TABLE emp04 (e_id int(11) NOT NULL,e_name char(20) DEFAULT NULL,birth date DEFAULT NULL,salary decimal(10,2) DEFAULT NULL,phone char(20) DEFAULT NULL,address varchar(200) DEFAULT NULL,dept_name char(30) DEFAULT NULL,PRIMARY KEY (e_id),KEY idx_name (e_name),KEY idx_dept_salary (dept_name,salary),KEY idx_phone (phone(11))
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)五、删除索引
删除索引的语法格式如下
-- 使用 alter table 命令删除索引
LTER TABLE 表名 DROP INDEX 索引名;
-- 使用 drop index 命令删除索引
DROP INDEX 索引名 ON 表名;例如
-- 查看 emp03 表中的索引
mysql show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE emp03 (e_id int(11) NOT NULL,e_name char(20) DEFAULT NULL,birth date DEFAULT NULL,salary decimal(10,2) DEFAULT NULL,phone char(20) DEFAULT NULL,address varchar(200) DEFAULT NULL,dept_name char(30) DEFAULT NULL,PRIMARY KEY (e_id),KEY e_name (e_name),KEY idx_salary (salary),KEY idx_dept_salary (dept_name,salary),KEY idx_phone (phone(11))
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)-- 删除索引e_name
alter table emp03 drop index e_name;-- 删除索引idx_salary
drop index idx_salary on emp03;-- 重新查看 emp03 表中的索引
mysql show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE emp03 (e_id int(11) NOT NULL,e_name char(20) DEFAULT NULL,birth date DEFAULT NULL,salary decimal(10,2) DEFAULT NULL,phone char(20) DEFAULT NULL,address varchar(200) DEFAULT NULL,dept_name char(30) DEFAULT NULL,PRIMARY KEY (e_id),KEY idx_dept_salary (dept_name,salary),KEY idx_phone (phone(11))
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)
