首页 > 数据库 >MySQL中的索引

MySQL中的索引

时间：2022-11-28 19:00:16浏览次数：48  
标签：index 列名 MySQL member 索引 表名 table

一、索引的概念

  • 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。
  • 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
  • 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
  • 索引是表中一列或者若干列值排序的方法。
  • 建立索引的目的是加快对表中记录的查找或排序。

二、索引的作用

1、索引的优点

  • 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建所有的最主要的原因。
  • 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
  • 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。
  • 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
  • 可以加快表与表之间的连接。
  • 在使用分组和排序时，可大大减少分组和排序的时间。

2、索引的副作用

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

三、创建索引的原则依据

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

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

四、索引的分类和创建

准备工作

安装mysql数据库并配置

复制代码 
mysql -u root -p

create database class;
use class;
create table member (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
desc member;

insert into member values (1,'zhangsan','111','111111','nanjing','vip');
insert into member values (3,'lisi','333','333333','nanjing','normal');
insert into member values (5,'wangwu','555','222222','suzhou','normal');
insert into member values (2,'zhaoliu','222','555555','nanjing','vip');
insert into member values (4,'qianqi','444','444444','shanghai','vip');
select * from member;
复制代码

1、普通索引

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

直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);
  • (列名(length))：length是可选项。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。 #索引名建议以“_index”结尾。
例：create index name_index on member (name);
select name from member;
show create table member;

修改表方式创建

复制代码 
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

例：alter table member add index id_index (id);
select id from member;
select id,name from member;
复制代码

创建表的时候指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

例：create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index cardid_index (cardid));
show create table test;

 2、唯一索引

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

直接创建唯一索引

复制代码 
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

例：select * from member;
create unique index remark_index on member (remark);
create unique index phone_index on member (phone);
show create table member;
复制代码

 修改表方式创建

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

例：alter table member add unique cardid_index (cardid);

 创建表的时候指定

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

例:create table member2 (id int,name varchar(20),unique id_index (id));
show creat table member2;

3、主键索引

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

创建表的时候指定

复制代码 
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

例：create table test1 (id int primary key,name varchar(20));
create table test2 (id int,name varchar(20),primary key (id));

show create table test1;
show create table test2;
复制代码

 修改表方式创建

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

alter table member add primary key （id）;
desc member;

4、组合索引（单列索引与多列索引)

  • 可以是单列上创建的索引，也可以是多列上创建的索引
复制代码 
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

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

例:create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table amd1;
insert into amd1 values(1,'zhangsan','123123');
select * from amd1 where name='zhangsan' and id=1;
复制代码

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

直接创建索引

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

例：select * from member;
create fulltext index remark_index on member (remark);

 修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));

使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

例：select * from member where match(remark) against('vip');

五、查看索引

1、命令格式

show index from 表名;
show index from 表名\G； 竖向显示表索引信息
show keys from 表名;
show keys from 表名\G;

2、字段解析

 六、删除索引

直接删除索引

DROP INDEX 索引名 ON 表名;

例：drop index name_index on member;

修改表方式删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

例：alter table member drop id_index;
show index from member;

删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

标签：index,列名,MySQL,member,索引,表名,table
From： https://www.cnblogs.com/zhao222/p/16933052.html

相关文章

  • 多表查询两种方法、可视化软件navicat、python操作mysql、pymysql模块
    目录多表查询的思路多表查询的两种方法小知识点补充数说明可视化软件Navicat多表查询练习题python操作MySQLpymysql补充说明多表查询的思路表数据准备表数据准备creat......
  • skywalking启动配置agent及数据储存对数据源(mysql,es)版本要求
    skywalking启动配置agent及数据储存对数据源(mysql,es)版本要求#skywalking-agent.jar的本地磁盘路径-javaagent:D:\SkyWalking\skywalking-agent\skywalking-agent.jar#......
  • Mysql 基础概念
      一、数据库的基本概念1、数据库的组成2、数据库的管理系统（DBMS）3、数据库系统（DBS） 二、主流数据库 三、关系数据库 四、非关系数据库 五、关系数据库和非关......
  • mysql基础语句
       一、mysql中6中常见的的约束/规则主键约束（primarykey）外键约束（foreignkey）：如果同一个属性字段x在表一中数主键，而在表二中不是主主键，则字段称为表二的外键。非......
  • Mysql 连接参数 useAffectedRows 详解
    Mysql连接参数useAffectedRows详解Javamysql链接串：jdbc:mysql://mysql安装IP:3306/db_test?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertTo......
  • MySQL多表查询语法
    MySQL多表查询语法多表查询准备数据准备createtabledep(idintprimarykeyauto_increment,namevarchar(20));createtableemp(idintprimarykeya......
  • mysql数据库之事务
     一、mysql事务1、事务的概念2、事务的ACID特点2.1原子性2.2一致性2.3隔离性2.4持久性3、两个事务之间的影响3.1脏读（读取未提交数据）3.2不可重复度（前后多......
  • MySQL深度掌握之路
    目录​​目录​​​​前言​​​​知识点​​前言知识点MySQL要求​​AUTO_INCREMENT​​列必须有某种索引，如果没有索引，那么表的定义就是不合法的。任何一个​​PRIMARYKE......
  • MySQL自定义函数
     ⚠不推荐将业务逻辑存储在数据库中.MySQL不仅提供了很多很方便的内置函数，用户还可以自定义函数。不同于MongoDB对Js函数的良好支持，MySQL的自定义函数用起来感觉处处掣......
  • 多mysql实例库联合查询
    情况一2个库在同一台物理主机情况二2个库不在同一台物理主机(即2个库分别在不同的物理主机)注意:myemployees库和shoppingCart库在同一台物理主机，如果不在同一台物理......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99