MySQL索引

最新推荐文章于 2024-06-17 15:31:11 发布
最新推荐文章于 2024-06-17 15:31:11 发布
阅读量123 收藏
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bjgaocp/article/details/88047392
版权

索引的基本类型
btree 和 hash ，btree是默认索引

Btree 必须从根节点开始采用二分法查找
hash 算法是基于等值计算的

普通索引
普通索引 index 显示标志MUL
作用： 以索引为查寻条件加快查寻速度
通常表中作为查寻条件的字段设置为索引字段，字段值可以重复，一个表中可以有多个index字段，字段的值可以为空

1，把已有表中的字段设置为index字段
mysql> desc top1;
±------±-------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------------±-----±----±--------±------+
| id | int(3) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| sex | enum(‘boy’,‘girl’) | YES | | NULL | |
| name | char(5) | YES | | NULL | |

创建索引
mysql> create index index_name on top1(name);
在top1表中的name字段建立名为index_name的index索引,index后是索引名，top1（name）是top1表中的name字段

创建索引后检查
mysql> desc top1;
±------±-------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------------±-----±----±--------±------+
| id | int(3) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| sex | enum(‘boy’,‘girl’) | YES | | NULL | |
| name | char(5) | YES | MUL | NULL | |
这里我们看到在name字段的Key 显示MUL 表示已经创建成功普通索引 但是在这里我们看不到索引名 以后如果需要索引名就用下面命令

查看表中的索引要比desc详细（desc查看索引类型 show 查看索引名及其他）
mysql> show index from top1\G
*************************** 1. row ***************************
Table: top1
Non_unique: 1
Key_name: name_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:

前缀索引 指定已有字段中前N个字符做为索引
mysql> create index name_index on top1(name(3));
注：在top1表的name列的前3个字符创建名为name_index的普通索引

mysql> show index from top1\G
*************************** 1. row ***************************
Table: top1
Non_unique: 1
Key_name: name_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 7
Sub_part: 3
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:

为多个字段创建联合索引
mysql> create index id_name_index on top1(id,name);
为id和name字段创建联合索引
mysql> show index from top1\G
*************************** 1. row ***************************
Table: top1
Non_unique: 1
Key_name: id_name_index
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: top1
Non_unique: 1
Key_name: id_name_index
Seq_in_index: 2
Column_name: name
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:

提示：联合索引前缀生效条件
例 index(a,b,c) 仅a ab abc 三个查寻条件可以走索引

2，建表时，创建index字段
mysql> create table top7(
-> name char(20),
-> age int(2),
-> sex enum(“boy”,“girl”),
-> index(name),
-> index(sex)
-> );

mysql> desc top7;
±------±-------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------------±-----±----±--------±------+
| name | char(20) | YES | MUL | NULL | |
| age | int(2) | YES | | NULL | |
| sex | enum(‘boy’,‘girl’) | YES | MUL | NULL | |

注：这里我们创建的是两个普通索引而不是联合索引

删除索引
mysql> drop index id_name_index on top1;
注：index后面加的是索引名而不是字段名
mysql> desc top1;
±------±-------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------------±-----±----±--------±------+
| id | int(3) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| sex | enum(‘boy’,‘girl’) | YES | | NULL | |
| name | char(5) | YES | | NULL | |

唯一索引
约束插入条件唯一性，例如邮箱
唯一索引 unique 显示标志 UNI
字段的值不允许重复，但字段值可以为空，一个表中可以有多个unique字段，当把字段不允许为空时约束方式和主键一样。
1，把已有字段设置为unique字段
mysql> create unique index mail_index on top1(mail);
在top1表的mail字段设置唯一索引 ，索引名为 mail_index

添加之后检查
mysql> desc top1;
±------±-------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------------±-----±----±--------±------+
| id | int(3) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| sex | enum(‘boy’,‘girl’) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| mail | varchar(20) | YES | UNI | NULL | |
这里看到mail字段的Key已经变成UNI，表示添加成功

2，建表时创建
mysql> create table top8(
tel char(11),
mail varchar(50) not null,
name char(20),
unique(tel),
unique(mail)
);
注：如果唯一索引没有约束条件 not null的情况就显示为UNI 如果加上not null约束条件就会显示为PRI（主键索引）

mysql> desc top8;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| tel | char(11) | YES | UNI | NULL | |
| mail | varchar(50) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |

3,删除索引
mysql> drop index mail_index on top1;

主键索引
字段值不允许重复且字段值不允许为空，一个表中只能有一个主键字段。通常和auto_increment（让字段的值自动增长，自加1）约束条件一起使用 显示标志 PRI

1，把已有字段设置为primary key （建表后通过alter增加主键索引）一般不这样做
mysql> alter table top1 add primary key(id);
mysql> desc top1;
±------±-------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------------±-----±----±--------±------+
| id | int(3) | NO | PRI | 0 | |
| age | int(2) | YES | | NULL | |
| sex | enum(‘boy’,‘girl’) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| mail | varchar(20) | YES | | NULL | |
添加后查看id的Key字段变成了PRI 表示已经设置成功

2，建表时创建primary key字段
mysql> create table top9(
-> id int(3) auto_increment,
-> stu_id char(4),
-> name carchar(20),
-> primary key(id)
-> );
建表后检查
mysql> desc top9;
±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int(3) | YES | | NULL | |

3，删除主键索引 primary key
mysql> alter table top1 drop primary key;
mysql> desc top1;
±------±-------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------------±-----±----±--------±------+
| id | int(3) | NO | | 0 | |
| age | int(2) | YES | | NULL | |
| sex | enum(‘boy’,‘girl’) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| mail | varchar(20) | YES | | NULL | |

注：删除有auto_increment的primary key必须先去掉auto_increment
mysql> mysql> alter table top9 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
不删除auto_increment 会报错的哦
正确做法
mysql> alter table top9 modify id int(3) not null;
mysql> alter table top9 drop primary key;
mysql> desc top9;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(3) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(3) | YES | | NULL | |

复合主键
中的多个字段同时做主键字段，必须建表时创建 复合主键字段的值只要不同时重复就可以
mysql> create table top8(
-> clientip varchar(15),
-> sername varchar(20),
-> port char(5),
-> primary key(clientip,port)
-> );
创建后查看
mysql> desc top8;
±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| clientip | varchar(15) | NO | PRI | | |
| sername | varchar(20) | YES | | NULL | |
| port | char(5) | NO | PRI | | |

外键索引
foreign key
让当前表中外键字段的值，只能在指定的另一个表中的某个字段值得范围内选择
使用规则
1，表的存储引擎必须是innodb
2，字段的类型要匹配
3，被参考字段不许是键值（key）中的一种，通常设置为主键

1，把已有字段设置为 foreign key
mysql> alter table top7 add foreign key(gz_id) references top6(yj_id) on update cascade on delete cascade;
查看外键名
mysql> show create table top7\G
*************************** 1. row ***************************
Table: top7
Create Table: CREATE TABLE top7 (
gz_id int(2) NOT NULL,
gz float(7,2) DEFAULT NULL,
KEY gz_id (gz_id),
CONSTRAINT top7_ibfk_1 FOREIGN KEY (gz_id) REFERENCES top6 (yj_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2 建表时创建外键 foreign key
mysql> create table top6(
-> yj_id int(2) primary key auto_increment,
-> name varchar(20)
-> );

mysql> create table top7(
-> gz_id int(2) not null,
-> gz float(7,2),
-> foreign key(gz_id) references top6(yj_id) on update cascade on delete cascade
-> );
top7表中gz_id字段参考top6表中yj_id字段 同步更新 同步删除，也就是top6表中添加删数据top7中也会做相应更改

在外键表中查看
mysql> show create table top7\G
*************************** 1. row ***************************
Table: top7
Create Table: CREATE TABLE top7 (
gz_id int(2) NOT NULL,
gz float(7,2) DEFAULT NULL,
KEY gz_id (gz_id),
CONSTRAINT top7_ibfk_1 FOREIGN KEY (gz_id) REFERENCES top6 (yj_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

注：CONSTRAINT top7_ibfk_1 这个就是键的名字

3 删除外键索引
mysql> alter table top7 drop foreign key top7_ibfk_1;

全文索引
fulltext
分词，全文索引以词为基础的

1 把已有字段设为 fulltext
mysql> create fulltext index full_index on top1(mail);
添加后查看
mysql> show index from top1\G
*************************** 1. row ***************************
Table: top1
Non_unique: 1
Key_name: full_index
Seq_in_index: 1
Column_name: mail
Collation: NULL
Cardinality: 8
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:

2 建表时创建全文索引 fulltext
mysql> create table top9(
-> id int(3),
-> name char(10),
-> mail varchar(30),
-> fulltext(name)
-> );

创建后查看
mysql> show index from top9\G
*************************** 1. row ***************************
Table: top9
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: NULL
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:

删除全文索引
mysql> drop index name on top9;

Mysql索引既然可以加快查寻速度，那么就给所有的列建索引？
答：因为索引不但占用系统磁盘空间，更新数据库时还需要维护索引数据，因此，索引是一把双刃剑，并不是越多越好，写频繁，读少的业务要少建立索引。

需要在哪些列上创建索引？
答：select user,host from mysql.user where host=….索引一定要创建在where后的条件列，而不是select后的选择数据列，另外我们要尽量选择在唯一值多的大表上建立索引。

bjgaocp
关注
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MYsql中的 TOP排序使用limit
Amir‘s IT tec
10-09 1455
在sqlserver中使用top排序，取得前面的几条：是在select 后面加 top n 但是mysql中就不是这样了，今天弄了会才反应过来 在mysql 中用limit 比如要查询user表中的前10条数据 用 select * from user limit 10 还可以分页查询 比sqlserver 方便多。 分页用 select * from user limit 10
我win7怎么安装不了python_学编程，安装python时出现不能安装的情况，如图，在win7系统下，怎么解决？...
weixin_39548606的博客
12-04 762
{"optioninfo":{"dynamic":"true","static":"true"},"simplifiedDisplay":"detailCard","newCard":[{"ifIcon":"icon","icon":"censor","iconImg":"","contentLink":"https://ai.aliyun.com/vi/censor","title":"视频审核...
MySQL数据排序asc、desc
Jason的博客
03-25 35万+
数据排序 asc、desc 1、单一字段排序order by 字段名称 作用： 通过哪个或哪些字段进行排序 含义： 排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序（asc），如果存在 where 子句，那么 order by 必须放到where 语句后面。 （1）、按照薪水由小到大排序（系统默认由小到...
mysql top语句报错_MySql中不支持top语句
weixin_32001191的博客
02-01 876
MySql中不支持top语句selecttop3*fromtabledesc取前5条记录：select * from tablename limit 0,5--------------------------------------------------------------------------------------------在HQL+MYSQL中不能直接在查询语句中使用LIMIT...
mysql desc asc基本命令总结
码农高端会所
04-11 4万+
Sql代码 asc 按升序排列 desc 按降序排列 下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
mysql 索引
liulanba的博客
10-25 1万+
在 MySQL 中创建索引可以大大提高查询效率，加快数据检索速度，他的索引包含 普通索引和唯一索引，以及主键索引，联合索引。
MySQL 索引
最新发布
weixin_68840588的博客
06-17 4230
介绍MySQL索引的相关知识点
分享几道关于MySQL索引的重点面试题
01-19
但MySQL 索引你真的懂吗？这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则？ 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话，采用B+树的结构，...
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
快速了解MySQL 索引
12-14
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引分...
MYSQL索引
01-27
MySQL索引是数据库管理系统中用于加速数据检索的关键结构。索引优化是提升查询性能的关键手段。MySQL中的索引主要在存储引擎层实现，而非服务器层。B-Tree索引是最常见的索引类型，其特点是所有值按顺序存储，每个...
MySQL字符集
bjgaocp的博客
03-04 3031
查看MySQL支持的字符集 mysql> show character set; 查看mysql当前字符集 mysql> show variables like ‘character_set%’; character_set_client：客户端的字符集 character_set_results：结果字符集 character_set_connection：连接字符集 临时更改方法 ...
多表连接查询(内连接)
bjgaocp的博客
08-31 2136
多表连接查询(内连接) 3.1 作用 单表数据不能满足查询需求时. 例子: 查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数 city: SELECT countrycode,NAME,population FROM city WHERE population<100; PCN Adamstown 42 country DESC country; CODE NAME SurfaceArea SELECT NAME ,SurfaceArea FROM country.
MySQL创建及授权账号
bjgaocp的博客
02-27 2121
grant all on . to wen@“localhost” identified by “111111”; grant 授权命令 all 对应权限 on . 目标：库和表 to wen@“localhost” 用户名和客户端主机 identified by “111111” 用户密码 操作案例：创建master用户对test库具备所有权限，允许从localhost主机登录管理数据库，密码为...
事务的ACID如何保证
bjgaocp的博客
07-23 1844
一些概念名词 redo log: 重做日志 ib_logfile0~1 默认50M , 轮询使用 redo log buffer : redo内存区域 ibd : 存储 数据行和索引 data buffer pool : 缓冲区池,数据和索引的缓冲 LSN : 日志序列号 ibd ,redolog ,data buffer pool, redo buffer MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动 WAL (持久化): wr
MySQL数据库负载过高
bjgaocp的博客
03-05 1610
1首先进入数据库中 show full processlist; 连续多执行几次 找出慢的语句 2找出语句后用explain 加上慢的整个语句 判断是否走索引 3查看表结构 show create table 表名\G 4查看表中列的重复值 select count(distinct 列名) from 表名;(查出列中的不重复的值唯一值) 5查看表中的总记录数 select count(*) ...
MySQL延迟同步
bjgaocp的博客
03-04 1483
目的是 防止主库的误操作造成从库也被误操作。 mysql&amp;amp;gt;stop slave; mysql&amp;amp;gt;CHANGE MASTER TO MASTER_DELAY = 1800；单位为秒 mysql&amp;amp;gt;start slave; mysql&amp;amp;gt;show slave status \G;? 如下图所示： 红色框中参数的意义： Master_Log_File 主库mysql的binlog文件.
mysql索引
05-29
MySQL索引是一种数据结构，可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率，降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法： 1. 索引类型 MySQL支持多种类型的索引，包括B树索引...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值