Mysql_索引的基本操作

最新推荐文章于 2024-07-11 21:41:59 发布
最新推荐文章于 2024-07-11 21:41:59 发布
阅读量229 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Wsxyi/article/details/115549301
版权

Mysql索引

一.Mysql索引

1.索引的概念

  • 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址

  • 使用索引后可以不用描述全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

  • 索引就好比一本书的目录，可以根据目录中的页码快速找到所需的内容

  • 索引是表中一列或者若干列数值排序的方法

  • 建立索引的目的是加快对表中记录的查找或排序

2.索引的作用

作用

  • 设置了合适的索引后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因

  • 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度

  • 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本

  • 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性

  • 可以加快表于表之间的连接

  • 在使用分组排序时，可大大减少分组和排序的时间

副作用

  • 索引需要占用额外的磁盘空间
  • 对于MylSAM 引擎而言，索引文件和数据文件时分离的，索引文件用于保存数据记录的地址
  • 而InnoDB 引擎的表数据文件本身就是索引文件
  • 在插入和修改数据时要花费更多时间，因为索引也要随之变动

3.创建索引的原则依据（mysql优化）

索引随可以提示数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会进行索引查询，然后定位到具体的数据行，如果索引使用不当，会增加数据库的负担

  • 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位。
  • 记录数超过300行的表应该有索引。如果没有索引，需要把表 遍历一遍，会严重影响数据库的性能
  • 经常与其他表连接的表，在连接字段上应该建立索引。
  • 唯一性太差的字段不适合建立索引
  • 更新太频繁字段不适合创建索引
  • 经常出现在where 子句中的字段，特别是大表的字段，应该建立索引
  • 索引应该建立选择高的字段上
  • 索引应该建立小的字段上，对于大的字段或者超长的字段，不要用索引

二.索引的分类和创建

创建一个表，插入数据

mysql -u root -p

create database number;
use number;
create table class(
id int(10) not null,
name varchar(20) not null,
sex char(2) not null,
cardid varchar(20) not null,
phone varchar(11),
address varchar(50));

desc class;

mysql> insert into class values ('1','张三','男','123456','111111','苏州');
mysql> insert into class values ('2','李四','男','123123','22222','南京');
mysql> insert into class values ('3','王五','女','123333','333333','南京');
mysql> insert into class values ('4','赵六','女','122222','444444','苏州');
mysql> insert into class values ('5','赵七','男','1444444','55555','上海');
select * from class;

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

1.普通索引

  • 最基本的索引类型，没有唯一性之类的限制
直接创建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);


#(列名(length))：length是可选项，下同。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾。

在这里插入图片描述

修改表方式创建索引
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

例：
alter table class add index id_index (id);
show index from class\G;

在这里插入图片描述

创建表的时候指定索引


CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));
create table class1(
 id int(10) not null,
 name varchar(20) not null,
index id_index(id));

show index from class1\G;

在这里插入图片描述

2.唯一索引

  • 唯一索引：与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

直接创建唯一索引：

CREATE UNIQUE INDEX 索引名 ON 表名(列名);
create unique index cardid_index on class(cardid);

show index from class\G;

在这里插入图片描述

在这里插入图片描述

修改表方式创建
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
alter table class add unique id_index(id);
show index from class\G;

在这里插入图片描述

创建表的时候指定
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

 create table class2(
id int(10) not null,
 name varchar(20) not null,
 unique id_index(id));

show index on class2;

在这里插入图片描述

3.主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

创建表的时候指定
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

 create table class3(
id int(10) not null,
 name varchar(20) not null,
primary key(id));


show index from class3\G;

在这里插入图片描述

修改表方式创建



ALTER TABLE 表名 ADD PRIMARY KEY (列名); 

alter table class add primary key (name);
show index from class\G;

在这里插入图片描述

4.组合索引（单列索引与多列索引）

  • 可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';


create table class5(
id int(10) not null,
 name varchar(20) not null,
sex char(2) not null,
index index_class5(id,name,sex));

show index from class5\G;



insert into class5 values(1,'zhangsan','男');


select * from class5 where id=1 and name='zhangsan';


select * from class5 where id=1;

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

5.全文索引（FULLTEXT）

  • 适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。
  • 在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，
  • 在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。
  • 全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。

直接创建索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

create fulltext index name_index on class5(name);
show index from class5;

在这里插入图片描述
在这里插入图片描述

修改表方式创建
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
alter table class5 add fulltext name_index(name);
show index from class5\G;

在这里插入图片描述

创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT


create table class6 (
id int(10) not null,
name varchar(20) not null,
address varchar(50),
 fulltext name_index(name));

show index from class6\G;

在这里插入图片描述

使用全文索引查询
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');


insert into class6 values ('3','zhangsan','南京');
 insert into class6 values ('4','lisi','上海');
 insert into class6 values ('2','liliu','北京');



select * from class6 where match(name) against('zhangsan');

select * from class6 where match(name) against('lisi');

在这里插入图片描述

三.查看索引

show index from 表名;
show keys from 表名;

各字段的含义如下：
Table：表的名称。
Non_unique：如果索引不能包括重复词，则为 0；如果可以，则为 1。
Key_name：索引的名称。
Seq_in_index：索引中的列序号，从 1 开始。
Column_name：列名称。
Collation：列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。
Cardinality：索引中唯一值数目的估计值。
Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
Packed：指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null：如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment：备注。



 show index from class6\G;

在这里插入图片描述

四.删除索引

直接删除索引
DROP INDEX 索引名 ON 表名;
drop index name_index on class6;

在这里插入图片描述

修改表方式删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

 alter table class6 drop index id_index;
 show index from class6\G;

在这里插入图片描述

删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;


alter table class drop primary key;

show index from class\G;

在这里插入图片描述

总结：

索引：一个排序的列表，通过整个列表可以找到对应的数据
索引的作用：
①快速定位、减少查询时间、加快查询速度
②降低数据库的IO(输入输出）、排序成本
③加快表之间的连接
④建立唯一索引 保证唯一性
⑤分组和排序时，减少分组排序时间
索引的副作用：①占用额外的磁盘空间
②插入和修改数据时候，需要花费更多的时间

mysql优化=创建索引

创建索引得条件
建立在： 选择高、小的字段上
①表的主键、外键必须有索引
②超过300行的表，要有索引
③经常与其他表连接的表，要有索引

④唯一性差不适合
⑤更新频繁不时候

索引分类：
普通索引
唯一索引
主键索引
组合索引
全文索引

Wsxyi
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql加索引的坏处_数据库创建索引的利弊
weixin_36325879的博客
01-28 2210
索引就像是书的目录，是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引中包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(BTree)中，使SQL可以快速有效地查找与键值关联的行。2.为什么要建立索引，即索引的优点：①建立索引的列可以保证行的唯一性，生成唯一的rowId②建立索引可以有效缩短数据的检索时间③建立索引可以加快表与表之间的连接④为用来排序或...
Mysql索引常用操作
weixin_45118229的博客
04-10 1171
文章目录前言一、创建索引二、查看索引三、删除索引四、练习 前言 在数据库中如何创建索引。本文中练习中，使用的表数据DDL如下： create table tb_user ( id int auto_increment comment '主键' primary key, name varchar(50) not null comment '用户名', phone varchar(11) not null com
mysql的索引的增删改查
weixin_39944891的博客
08-25 239
普通索引INDEX：加速查找 唯一索引： -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复） -唯一索引UNIQUE:加速查找+约束（不能重复） 联合索引： -PRIMARY KEY(id,name):联合主键索引 -UNIQUE(id,name):联合唯一索引 -IND...
服务端指南 数据存储篇 | MySQL（04） 索引使用的注意事项
weixin_34418883的博客
04-13 174
MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，在索引的使用过程中，存在一些使用细节和注意事项。 原文地址：服务端指南 数据存储篇 | MySQL（04）索引使用的注意事项博客地址：blog.720ui.com/ 不要在列上使用函数和进行运算 不要在列上使用函数，这将导致索引失效而进行全表扫描。 select * from news where year(publ...
【详解】MySQL索引的基本操作，索引（主键索引，普通索引，组合索引，唯一索引）
CodingLJ
11-13 2446
前言 索引是什么？ 索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。 一、索引的基本操作 ①索引的优点 大大加快了数据的查询速度。 ②索引的缺点 1.维护索引需要...
mysql.rar_MYSQL_linux c mysql_linux mysql_mysql linux_mysql 操作
09-23
本教程将深入探讨如何在Linux系统上安装、配置、连接以及执行基本的数据操作。 1. **MySQL的安装** 在Linux系统中，通常使用包管理器如`apt`（Ubuntu/Debian）或`yum`（CentOS/RHEL）来安装MySQL。首先更新包列表...
MySQL.rar_mysql简单操作
09-24
在本资料包“MySQL.rar”中，我们聚焦于MySQL的基本操作，这些操作是数据库管理和开发的基础。以下是对这些关键知识点的详细说明： 1. **安装与启动MySQL**：在Windows或Linux环境下，可以通过下载安装包并按照向导...
MySQL索引操作命令详解
09-09
删除索引是MySQL索引操作命令中的一种基本操作，删除索引的语法如下： ``` ALTER TABLE table_name DROP INDEX index_name; ``` 这条命令可以删除指定的索引，从而释放索引所占用的磁盘空间。 三、修改索引 修改...
MySQL.rar_MYSQL_mysql 演示
09-21
在本“MySQL.rar_MYSQL_mysql 演示”中，我们可以期待找到一系列关于MySQL数据库操作的示例和教程，这对于初学者了解和掌握数据库管理的基本概念和技能极其有价值。 首先，让我们来探讨一下MySQL的基础知识。MySQL...
MySQL.rar_MYSQL_mysql 安装包
09-14
在安装MySQL之前，了解一些基本概念和步骤是必要的： 1. **版本选择**：MySQL有不同的版本，如Community Server（社区版）、Enterprise Server（企业版）等。社区版是免费的，适合个人和小型项目；企业版则提供更多...
mysql索引的弊端_数据库索引的坏处
weixin_42132598的博客
02-28 2494
一、数据库索引的坏处索引是完全独立于基础数据之外的一部分数据。假 设在Table ta 中的Column ca 创建了索引 idx_ta_ca，那么任何更新 Column ca 的操作，MySQL在更新表中 Column ca的同时，都须要更新Column ca 的索引数据，调整因为更新带来键值变化的索引信息。而如果没有对 Column ca 进行索引，MySQL要做的仅仅是更新表中 Column...
单键索引还是组合索引
talentluke的专栏
04-08 613
摘自http://book.51cto.com/art/200906/132406.htm   8.4.5  索引的利弊与如何判定，是否需要索引   相信读者都知道索引能够极大地提高数据检索的效率，让Query 执行得更快，但是可能并不是每一位朋友都清楚索引在极大提高检索效率的同时，也给数据库带来了一些负面的影响。下面就分别对 MySQL 中索引的利与弊做一个简单的分析。   索引...
linux mysql 建索引_mysql 查看索引、添加索引、删除索引命令
weixin_36330888的博客
01-21 341
查看索引mysql> show index from tblname;mysql> show keys from tblname;mysql> show index from center_bank_rate;+------------------+------------+----------+--------------+-------------+-----------+-...
MySQL索引——分类、何时使用、何时不使用、何时失效
热门推荐
weixin_39420024的博客
04-22 2万+
1、分类     MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），但是只要列中有null值就不会被包含在索引中。     （1）普通索引：create index index_name on table(column)；     或者创建表时指定，create table(..., index i...
MySQL索引相关语句
footman的足迹
04-19 1127
1.PRIMARY KEY（主键索引） mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )   2.UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.INDEX(普通索引) mysq...
centos7中MySQL的卸载
CodeTom的博客
07-11 225
6、删除/var/log/mysqld.log（如果不删除这个文件，会导致新安装的mysql无法生存新密码，会出现无法登陆的情况）卸载完成后不要忘记检查一遍是否卸载干净。4、对安装的所有XXX目录进行删除操作。1、查看mysql安装了哪些东西。5、删除/etc/my.cnf。删除完成后检查是否删除干净。3、找mysql相关目录。
JavaFx+MySql学生管理系统
最新发布
2301_81253185的博客
07-11 1096
上个月学习了javafx和mysql数据库,于是写了一个学生管理系统,因为上个月在复习并且有一些事情,比较忙,所以没有更新博客了,这个项目页面虽然看着有点简陋了,但是大致内容还是比较简单的,于是现在跟大家分享一下我的学生管理系统,希望对这方面有兴趣的同学提供一些帮助。
数据库（mysql）忘记密码解决办法
GGB_GG的博客
07-11 488
【代码】数据库（mysql）忘记密码解决办法。
mysql笔记1
m0_62975692的博客
07-11 373
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值