Mysql索引（一看就会！！！）

最新推荐文章于 2024-09-12 15:59:09 发布

覃星笙

最新推荐文章于 2024-09-12 15:59:09 发布

阅读量225

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/Aprilqxs/article/details/130822440

版权

文章介绍了数据库索引的基本概念，包括Btree、B+Tree和Hash等索引类型，以及它们的优缺点。通过示例展示了如何在MySQL中创建和删除索引，并通过对比有无索引的查询速度来强调索引对查询性能的影响。此外，还提到了explain语句在分析查询计划中的作用。

摘要由CSDN通过智能技术生成

老规矩先上理论知识~

索引是什么

索引是为了加速查询的一种数据结构

类似于书的目录，所有的数据类型都可以被索引

为快速查找数据而排好序的一种数据结构

三种索引结构：Btree，B+Tree，Hash

优点：

提高mysql检索速度

索引减小了服务需要扫描的数据量

避免排序和临时i表

缺点：

降低更新表的速度

会占用磁盘空间的索引文件，如果创建了很多组合索引，索引文件会膨胀很快

如果数据列包含太多重复内容，建立建立索引没有太大实际效果

对于非常小的表，大部分情况下简单的全表扫描更高效，索引对于数据量越大，索引效果越明显

索引分类：

功能
- 普通索引
- 唯一索引
- 全文索引
作用字段
- 单列索引
- 多列索引

理论说的差不多了，那接下开始操作吧~

索引操作

创建索引
mysql> CREATE TABLE execdb.t11(name CHAR(20),uid INT, shell CHAR(20),INDEX(name),INDEX(uid),INDEX(shell)); #建表时创建索引

#命令——在哪个库. 创建的表名——字段设计——创建的索引字段index(字段名)

mysql> DESC execdb.t11; #查看表结构MUL

语法格式：

已有表添加索引

语法格式：

mysql> CREATE TABLE execdb.t12(name CHAR(20),uid INT, shell CHAR(20)); #建表

mysql> DESC execdb.t12; #查看表结构（key位置为空代表没有索引）

mysql> CREATE INDEX name ON execdb.t12(name); #添加索引

# 命令——索引名字——on对哪个库表的哪个字段添加索引

mysql> DESC execdb.t12; #查看表结构（key字段有MUL代表有索引）

#查看具体索引信息
mysql> SHOW INDEX FROM execdb.t12\G #查看表中所有索引

*************************** 1. row ***************************

Table: t12 #表名
Non_unique: 1 #是否为唯一索引，是0否1
Key_name: name #索引名称
Seq_in_index: 1 #该列在索引中的位置，因为有组合索引
Column_name: name #字段名
Collation: A #列以何种顺序存储在索引中，A为升序，NULL表示无分类
Cardinality: 0 #索引中唯一数目的估计值
Sub_part: NULL #列中被编入索引字符的数量，整列编入显示NULL
Packed: NULL #关键字如何被压缩，NULL表示没有被压缩
Null: YES #索引列中是否包含NULL
Index_type: BTREE #索引类别（BTREE、FULLTEXT、HASH、RTREE）
Comment: #显示评注
Index_comment: #索引单独评注

删除已有索引

mysql> DROP INDEX name ON execdb.t12; #删除指定索引

mysql> DESC execdb.t12; #查看表结构

举例一个索引案例能更好的理解哦~ 一起看看吧~

编写shell脚本生成1000000条数据
[root@server51 ~]# vim gendata.sh
[root@server51 ~]# cat gendata.sh
#!/bin/bash
#向execdb.t12表写入1000000条数据

shells=("/bin/bash" "/sbin/nologin" "/bin/false" "/sbin/shutdown")
for i in {1..1000000}
do
name="name$i"
uid=$i
num=$[RANDOM%4]
shell="${shells[$num]}"
echo ${name} ${uid} ${shell}
mysql -hlocalhost -uroot -p'123qqq...A' -e "INSERT INTO execdb.t12 VALUES(\"${name}\",${uid},\"${shell}\")" &> /dev/null
if [ $[i%10000] -eq 0 ];then
echo "已经写入${i}条数据"
fi
done

[root@server51 ~]# bash gendata.sh #执行脚本生成随机数据

[root@server51 ~]# mysql -hlocalhost -uroot -p'123qqq...A' #登录MySQL服务

mysql> SELECT COUNT(*) FROM execdb.t12; #查询表记录数据

mysql> CREATE INDEX name ON execdb.t12(name); #name字段添加索引

最后是索引的引用

#explain语句各个字段解释如下：

explain select * from execdb.t13 where name="lisi"\G #
id：表示当前select语句的编号，该值可能为空，如果行联合了其他行的结果；在这种情况下table列显示的是，引用的行的并集。
select_type：这个值有很多，暂时可以先记以下几个：
　　SIMPLE: 简单查询，不包含连接查询和子查询。
PRIMARY: 最外层查询，主键查询
UNION：连接查询的第二个或后面的查询语句。　其余参数可以查看https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
table：查询的表名

partitions：显示查询使用的分区，若为NULL则未使用分区。
type：表示表的连接类型，有如下取值：
const ：表示表中有多条记录，但只从表中查询一条记录;
eq_ref ：表示多表连接时，后面的表使用了UNIQUE或者PRIMARY KEY;
ref ：表示多表查询时，后面的表使用了普通索引;
unique_ subquery：表示子查询中使用了UNIQUE或者PRIMARY KEY;
index_ subquery：表示子查询中使用了普通索引;
range ：表示查询语句中给出了查询范围;
index ：表示对表中的索引进行了完整的扫描;
all ：表示此次查询进行了全表扫描;（一般来说全表扫描需要优化，表的记录很少除外）
possible_keys：表示查询中可能使用的索引；如果备选的数量大于3那说明已经太多了，因为太多会导致选择索引而损耗性能，所以建表时字段最好精简，同时也要建立联合索引，避免无效的单列索引；
key：查询实际使用的索引（不太准确，可以查阅官方文档）。
key_len：索引的长度
ref： REF列显示哪些列或常量与键列中所命名的索引进行比较，以从表中选择行。
rows：查询扫描的行数。
filtered：表示按条件过滤表行的百分比，最大为100表示100%。
Extra：表示查询额外的附加信息说明