MySQL数据库索引原理及优化策略
作者:ZIYE_190 发布时间:2024-01-20 00:35:42
1 索引
索引概念
索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现。
索引作用
数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系,索引所起的作用类似书籍目录,可用于快速定位、检索数据。索引可以极大地提高数据库的性能。
索引的使用场景
要考虑对数据库表的某列或某几列创建索引,需要考虑以下几点:
数据量较大,且经常对这些列进行条件查询。
该数据库表的插入操作,及对这些列的修改操作频率较低。
索引会占用额外的磁盘空间。
2 索引分类
从索引存储结构划分:BTree索引、Hash索引、FULLTEXT全文索引、RTree索引
从应用层次划分:普通索引,唯一索引,主键索引,复合索引
从索引键值类型划分,主键索引,辅助索引(二级索引)
从数据存储和索引键值逻辑关系划分:聚集索引(聚簇索引)非聚集泰引(非聚簇索)
从索引列数量划分:单列索引,复合索引
B树索引和B+树索引区别
区别:
数据的保存位置不同:B+树保存在叶子节点,B树保存在所有的节点中
体现出B+树优势:节点不存储data,这样一个节点就可以存储更多的key。可以使得树更矮,所以IO操作次数更少。 查询性能稳定:每次查询都是从根节点遍历到叶子节点,查询路径长度相同,即每次查询效率相当,时间复杂度固定是O(log(n))
叶子节点的指向:B+树相邻的叶子节点通过指针相连,B树没有
体现出B+树优势:所有叶子节点形成有序链表,便于范围查找
3 索引操作
创建主键索引
-- 在创建表的时候,直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
-- 在创建表的最后,指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));
-- 创建表以后再添加主键
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);
主键索引的特点:
一个表中,最多有一个主键索引,当然可以使符合主键
主键索引的效率高(主键不可重复)
创建主键索引的列,它的值不能为null,且不能重复
主键索引的列基本上是int
唯一索引的创建
-- 在表定义时,在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);
-- 创建表时,在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));
-- 创建表以后再添加unique
create table user6(id int primary key, name varchar(30));
alter table user6 add unique(name);
唯一索引的特点:
一个表中,可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引,必须保证这列不能有重复数据
如果一个唯一索引上指定not null,等价于主键索引
普通索引的创建
--在表的定义最后,指定某列为索引
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name)
);
--创建完表以后指定某列为普通索引
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name);
-- 创建一个索引名为 idx_name 的索引
create table user10(id int primary key, name varchar(20), email varchar(30));
create index idx_name on user10(name);
普通索引的特点:
一个表中可以有多个普通索引,普通索引在实际开发中用的比较多
如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引
查询索引
show keys from 表名
mysql> show keys from goods\G
*********** 1. row ***********
Table: goods <= 表名
Non_unique: 0 <= 0表示唯一索引
Key_name: PRIMARY <= 主键索引
Seq_in_index: 1
Column_name: goods_id <= 索引在哪列
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE <= 以二叉树形式的索引
Comment:
1 row in set (0.00 sec)
show index from 表名;
desc 表名
删除索引
删除主键索引: alter table 表名 drop primary key;
其他索引的删除: alter table 表名 drop index 索引名; 索引名就是show keys from 表名中的Key_name 字段
mysql> alter table user10 drop index idx_name;
drop index 索引名 on 表名
mysql> drop index name on user8
索引创建原则
比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引
来源:https://blog.csdn.net/ZIYE_190/article/details/130225079
猜你喜欢
- 如下所示:list = [‘a','b','c']想用for循环输出list的元素以及对应的索引。代
- 一、介绍在做YOLOv3项目时,会需要将文本文件中的某部分内容进行批量替换和修改,所以编写了python程序批量替换所有文本文件 * 定部分的
- 下面是出现的错误解释RuntimeError: An attempt ha
- 本文实例为大家分享了python3单目标粒子群算法的具体代码,供大家参考,具体内容如下关于PSO的基本知识......就说一下算法流程1)
- 由于想使用python用训练好的caffemodel来对很多图片进行批处理分类,学习过程中,碰到了argsort函数,因此去查了相关文献,也
- 情境问题小王是一名法务专员,工作中会处理所在公司的侵权事件并向侵权方发送法务函。他会按照【法务函模板.docx】 Word 文件给【封号名单
- 一、题目内容给定一个非负整数 num。对于 0 ≤ i ≤ num 范围中的每个数字 i ,计算其二进制数中的
- 本脚本为本人在性能测试过程中编写,用于对进程状态的监控,也可以用于日常的监控,适用性一般,扩展性还行# -*- coding: UTF-8
- windows下mysql双向同步备份实现方法以下的文章主要讲述的是在windows环境下实现MySQL数据库的主从同步备份的正确操作方案,
- 如何用通过Web访问OLAP数据? <% Set Conn=Server.CreateObject(″A
- class Helper_Page{ /** 总信息数 */ var $infoCount; /** 总页数 */ var $pageCou
- 前言我看到最近几部电影很火,查了一下猫眼电影上的数据,发现还有个榜单,里面有各种经典和热映电影的排行榜,然后我觉得电影封面图还挺好看的,想着
- PySnooper 是一个非常方便的调试器。如果您正在试图弄清楚为什么您的Python代码没有按照您的预期去做,您会希望使用具有断点和监视功
- 要介绍Python的三元表达式,可以先看看其他编程语言比如C,JAVA中应用:public class java { public stat
- 见下,把数字转成条形图、条形码的一个程序:<%Sub ShowChart(ByRef aValues,
- IE历来被web标准的拥护者所诟病,而当FireFox横空出世以后,更多的网页制作者开始关注web标准设计。看着FireFox的市场占有率不
- 前言使用 Python 的时候,我们知道 list 是一个长度可变对的数组, 可以通过 insert,append 和 extend 轻易的
- 本文实例讲述了MySQL无法存储emoji表情解决方案。分享给大家供大家参考,具体如下:今天学习爬虫爬伯乐在线的文章,由于在文章中有emoj
- 一、爬取数据话不多说了,直接上代码( copy即可用 )import requestsimport pandas as pdclass Sp
- 现在要解决的问题如下:我们有一个数据的表第7列有许多数字,并且是用逗号分隔的,数字又有一个对应的关系:我们要得到第7列对应关系的统计,就是每