MySQL索引

最新推荐文章于 2024-11-01 14:31:36 发布
最新推荐文章于 2024-11-01 14:31:36 发布
阅读量503 收藏 2
点赞数
分类专栏： MySQL 文章标签： mysql android adb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_62106937/article/details/132816320
版权

MySQL索引

没有索引，可能会有什么问题

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度

常见索引:

  1. 主键索引
  2. 唯一键索引
  3. 普通索引
  4. 全文索引—解决中子文索引问题;

这里我们先姑且不谈什么是索引，我们先来看看没有索引会怎么样？有索引又会怎么样？

eg:
先整一个海量表，在查询的时候，看看没有索引时有什么问题？

  1. 构建一个8000000条记录的数据
  2. 构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解
--产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
 set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
 set i = i + 1;
 end while;
return return_str;
end $$
delimiter ;

--产生随机数字
delimiter $$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;
--创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0; 
repeat
 set i = i + 1;
 insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;
--执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

表结构:
在这里插入图片描述

查询员工编号为998877的员工信息:
在这里插入图片描述
可以看到耗时4.93秒，这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有
1000个人并发查询，那很可能就死机。
解决办法:创建索引:
alter table EMP add index (empno);
接着有了索引过后我们再来查询一下empno=998877的员工信息:
在这里插入图片描述
我们可以发现有了索引过后的查询变的非常快速！

由此我们可以看出：有索引和没索引的查询的效率真的是天差地别！
从而也验证了我们刚开头所说的:索引的价值在于提高数据的检索效率!

认识磁盘

MySQL与存储

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一个重要话题

研究一下磁盘

在这里插入图片描述

看看磁盘中一个盘片

在这里插入图片描述

扇区：
数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区；

  1. 从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大
  2. 那么，所有扇区都是默认512字节吗？目前是的，我们也这样认为。因为保证一个扇区多大，是由
    比特位密度决定的。
  3. 不过最新的磁盘技术，已经慢慢的让扇区大小不同了。我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。

所以，最基本的，找到一个文件的全部，本质，就是在磁盘找到所有保存文件的扇区。
而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

定位扇区:
在这里插入图片描述

  1. 柱面(磁道): 多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面
  2. 每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的
  3. 所以，我们只需要知道，磁头（Heads）、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编
    号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS 。不过实际系统软件使用
    的并不是 CHS （但是硬件是），而是 LBA ，一种线性地址，可以想象成虚拟地址与物理地址。系统
    将 LBA 地址最后会转化成为 CHS ，交给磁盘去进行数据读取。不过，我们现在不关心转化细节，知
    道这个东西，让我们逻辑自洽起来即可。

结论:
我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。那么在系统软件上，OS就直接按照扇区(512字节，部分4096字节),进行IO交互吗？不是!
为什么？

  1. 如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化
  2. 从目前来看，单次IO 512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低
  3. 之前学习文件系统，就是在磁盘的基本结构下建立的，文件系统读取基本单位，就不是扇区，而是数据块。

故，系统读取磁盘，是以块为单位的，基本单位是 4KB ;

磁盘随机访问(Random Access)与连续访问(Sequential Access):
随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。
连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次
IO操作，这样的多个IO操作称为连续访问。
因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。
磁盘是通过机械运动进行寻址的，随机访问不需要过多的定位，故效率比较高

MySQL与磁盘交互的基本单位

而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎讲解)
在这里插入图片描述
也就是说，磁盘这个硬件设备的基本单位是512字节，而MySQL InnoDB引擎使用16kb与磁盘进行IO交互；即MySQL和磁盘进行数据交互的基本单位是16kb。这个基本数据单元，在MySQL这里叫做page（注意和OS系统的page区分）；

可是按照我们对于计算机体系和计算机网络模型的理解，MySQL是属于一个应用层的程序，应用层的程序是不能够直接与磁盘这个硬件进行交互的，如果应用层程序想要与硬件进行交互，必须通过OS，也就是应用层程序通过与OS的直接交互来完成与硬件的间接交互，在Linux下一切皆文件！包括硬件，因此在OS系统内部会用一个struct file结构体来描述磁盘这个硬件，在这个每个struct file结构体都会有一个内核级的输入输出缓冲区，我们以前用的类似于write、read这样的接口本质上也就是将用户数据拷贝到目标文件对应的struct file中的缓冲区中，然后再由OS结合一定的刷新策略将缓冲区中的文件刷新到磁盘中去，这时候OS进行的工作才叫真正的IO；

为此在逻辑上我们认为MySQL服务是直接与磁盘进行交互的，但是实际上并不是的，实际上MySQL的16kb基本交互单位是与OS进行交互的，准确点来说就是与OS所维护的文件缓冲区进行交互，然后再由OS结合一定的策略将缓冲区中的数据刷新到磁盘，当然我们也可以利用系统调用fync(fd)来强制刷新文件缓冲区！强制OS将缓冲区中的数据刷新到磁盘:

MySQL服务、OS、磁盘的关系:
在这里插入图片描述

总结:

  1. MySQL本质上可以看作一个站在OS文件系统之上的“文件系统”
  2. MySQL服务在启动的时候，就已经申请了大量的空间，作为数据的缓冲区，这个数据缓冲区以page为单位(16kb)
  3. MySQL的CURD操作都是在内存中进行的；
  4. MySQL的与OS的交互的基本单位是16kb，即使我们只需要读取1kb的数据，那么也会以16kb为基本单位来读取这1kb数据，这样做的目的就是为了减少IO次数，因为过多的IO是会影响程序的运行效率的；

建立共识

  1. MySQL中的数据文件是以page为单位保存在磁盘中的；
  2. MySQL的CURD操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或查询的数据；
  3. 而只要涉及到计算，就需要CPU参与，而为了便于CPU参与，一定要先将数据加载到内存中；
  4. 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略刷新到磁盘。而这时候，就涉及到磁盘和内存的数据交互，也就是IO。而此时IO的基本单位是page；
  5. 为了更好的进行上面的操作，MySQL服务器在内存中运行的时候就就已经申请好了BufferPoll的缓冲区空间，该缓冲区空间是以page为基本单位的，该缓冲区用来进行各种缓存；
  6. 为了提高程序运行效率，一定要尽可能的减少系统和磁盘的IO次数；

索引的理解

建立测试表:
在这里插入图片描述

插入多条数据:
在这里插入图片描述
现象:
通过查看最后的结果集我们可以看到最后的结果集并没有按照我们的插入顺序来显示，而是以一个有序的方式来显示；
这是谁做的？
为什么这么做？

中断一下,为什么MySQL与磁盘交互要是page？不是page行不行?
如果MySQL与磁盘的交互不是page，那么当我们在利用MySQL查询id=2的数据时就需要进行两次IO，第一次加载id=1，第二次加载id=2；如果我们要查询id=5的数据，那么就要进行5此IO，很明显如果我们要查询的数据越靠后的话，需要进行的IO次数就会越多，IO次数越多，磁盘做机械运动的次数就越多，而磁盘机械运动的速率是远低于其它电子元件的，这样就会导致我们整个程序的效率变慢！
而如果我们使用page来与磁盘进行交互的话，那么当我们第一次查询id=1的数据的时候，OS会将id=1附近的page大小数据全部加载进内存，这些page个大小数据中自然也就包括id=2、3、4、5的数据，那么此时再查询id=2的数据的时候直接在内存中查询即可，不用在从磁盘中加载数据了，同理在查询id=5的数据的时候也不用在进行加载5次数据了，直接在内存中就能进行查询，在本次查询中我们只需要一次IO！相比于原来的没有以page为交互单位来说，本次查询大大的降低了MySQL与磁盘的IO次数，提高了MySQL的运行效率!
可是我们明明是读取的id=1的数据，为什么在使用page的情况下OS会将这id=1附近的page个大小数据全部给我干到内存？
这其实是由于计算机的局部性原理，OS也是在赌，赌你下一次读取的数据可能在id=1这个数据的page个大小附近，如果用户下次读取的数据真的在这个范围之内，那么对于OS来说就赚了，OS只用一次IO就完成了用户的多次需求，如果用户下次读取的数据不在这个范围，那么此时OS在去重新IO也不亏嘛！

理解单个Page

我们知道在MySQL服务启动的时候就会开辟一个大大的缓冲区，该缓冲区是以page为单位的，而MySQL中是会存在大量的数据表文件的，这些数据表文件又是有一个或多个page组成的，那么也就意味着在MySQL中一定会存在大量的page，MySQL需要需要将这些page管理起来？
当然要！
怎么管理？
先描述，在组织！
实际上在page中不仅只存在数据内容，在其头部的几个字节中还存在一些该page下的管理信息；
所以，我们不要简单的将page认为是一个内存块，page内部也必须写入一些管理信息！！
我们可以将page看作是一个
struct page
{
struct page*next;
struct page *prev;
char buffer[NUM];//实际存储数据空间的缓冲区；
};//这个page结构体16kb大小，之后，我们申请一个page，实际上就是申请一个struct page结构体
然后我们就可以按照一定的数据结构(比如链表)将申请的struct page结构体组织起来，之后MySQL对于page的管理就变成了对于链表的增删查改！

在这里插入图片描述

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表
因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。
为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？
实际上在也内部的数据MySQL也是利用的一个链表结构来进行存储的，链表结构的特点就是删除和插入非常快，但是查询特别慢，为此我们就需要优化查询效率，如果我们不对插入进来的数据进行排序，那么我们在查找的时候就会作为很多无效查找，浪费效率；而如果我们
对于插入进来的数据进行排序，那么在查询的时候，没有一个查找是浪费的，运气好的话我们还可以提前结束查找，同时还可以设计出基于有序查找的高效算法！因此我们在插入数据时对其进行排序是有利于提高我们的查询效率的！；

理解多个page

上面我们也说了每个page的大小是16kb，也就是说page是由大小上线的，那么也就意味着单个page
存储的数据量也是有上限的，现在要是我们插入大量数据的话，那么一个page肯定是不够的，我们需要多个page来存储这些大量数据:
在这里插入图片描述
可是这样的话，我们如果想要查某一条数据的话，那么我们就得一个page一个page的遍历，并且在每个page中又要进行页内遍历，时间时间复杂度比较高，效率低；
那么有没有什么办法来提高一下查询效率呢？

举个例子:就比如我们现在正在看《谭浩强C程序设计》，如果我们要看指针章节，我们有两种翻法:

  1. 一页一页翻找，这样绝对能找到只不过效率极低，现实生活中应该很少有人这么做；
  2. 通过查询这本书的目录，找到指针章节的页数，然后直接翻到对应页数，这样的查找方法效率极高，也是我们日常生活中最常用的作法，可是这样做的缺点就是，在这本书上能写的有效内容变少了，需要花费一些空间来存储这本书的目录！但是这点空间与我们极高的效率比起来我们是愿意以这点空间去换高效的时间的！

单page情况:
在上述情况中，在单页内部查询数据也是线性遍历，可是要是我们提高的单页的查询效率，那么是不是整体的查询效率也就提上来了？
当然！可以如何做？
我们可以借鉴上面那个例子中的为书籍中的有效内容建立目录的思想，我们在单个页中能不能也为有效数据建立一些目录？
当然可以:
在这里插入图片描述
这个目录项中主要存两个数据:
该目录所指向的数据的有效地址、该目录所指的数据的key值；
这样的话我们在页内查找数据的时候就能先在目录中查找了，找到合适的起始目录项（也就是小于目标key值的最大目录项），然后从起始目录项线性排查目标数据，效率自然就提来了！
举个具体的例子:
就比如夜歌page中现在又1000条数据，按照以往的查找方式，那么我们最坏需要查找1000次；
可是现在我们给这个页内设置了50个目录项，那么我们在这种情况下最坏需要查早50+20=70次，相比于原来的1000次查找，现在的查找效率提高了10倍！！

实际上在这里，我们就可以回答为什么对于有主键的数据，MySQL会自动排序；
就是为了方便引入目录！

多page情况:
在单page情况下，我们对于单page做了目录项，确实也是提高了单个page的查询效率，可是要是我们要查询的数据非常的多，那么这些数据就需要占据大量的page单元，也就意味着有在MySQL中会有大量page页，那么此时尽管你每个page的查询效率上去了，但是总的page单元数目太多了，整体效率又变慢了，那么还是基于目录的思想，我们可不可以以page为基本单元建立一个管理page目录项呢？
当然可以！
在这里插入图片描述
在上级page中，只存目录，不存储有效数据，该目录中只存下级page的地址和该page的首元素的key值，在查询的时候我们就可以利用目标数据的key值根据上级page中目录，先找到我们要查询的那个数据在那个page中，然后再到对应的page中进行页内查找；这样的话会排除很多没必要的pager的页内查找，从而提高整体效率，可是如果上级page又有很多个？那么就在基于上级page建立管理上级page的上上级page，该page中只存储上page的目录项；
可是，我们每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还要遍历啊？不用担心，可以在加目录页：
在这里插入图片描述
这货就是传说中的B+树啊！没错，索引的本质就是上面这个B+树的数据结构；
同时，我们也完成了表user构建主键索引的过程；
现在随便找一个id=x的数据，我们发现现在的每一次查找都是有效查找，没有一次浪费！
同时，我们最后只需要加载路劲上的page节点就行了，减少了对于无关page的加载，这也就意味着我们减少了MySQL的IO次数，提高了MySQL的运行效率！

总结:

  1. page分为目录页和数据页，目录页中只放下级page的目录项（下级page的地址、下级page的首元素key值），数据页中就主要存放有效数据！
  2. 查找的时候，自顶向下找，只需要加载部分目录页到内存，即可完成整个算法的查找过程，大大减少了IO次数；
  3. 当我们在创建表的时候没有显示指定主键，那么MySQL会给我们的表自动生成一列，由该列来充当该表的主键，然后根据该列来创建主键索引，该默认列对于用户是不可见的，对于MySQL内部是可见的！这其实也就是解释了，为什么我们对于没有主键的表中插入数据时，最后的输出结果就是插入顺序，因为对于没有主键的表中插入数据时，MySQL会根据插入顺序来形成主键列，最后的输出结果自然也就是插入顺序的结果！

InnoDB 在建立索引结构来管理数据的时候，其他数据结构为何不行？

链表: 搜索数据时是线性遍历，查询效率太慢了！
二叉搜索树：在极端情况下，二叉搜索树是会退化成单边树的，这时候查询效率也是线性的，在这种极端情况下，查询效率太慢了；
AVL树or红黑树:虽然这两种类型的二叉树绝对平衡或者接近平衡，但是整体相对于B+树来说会比较高，大家都是树结构都是自定向下查找，但是AVL树和红黑树由于过高，那么也就意味着使用AVL树活红黑树的话会增加MySQL与磁盘的IO次数，降低MySQL服务的运行效率，而B+树是“矮胖”类型的树，相比于AVL树或者红黑树来说就存储相同量级的数据会比较矮，那么也就意味着MySQL与磁盘进行IO的次数会减少，因此虽然AVL树或者红黑树很秀但是B+树更秀！
hash:MySQL是支持hash的，不过InnoDB、MyISAM不支持，那么为什么MySQL没有用hash结构来作为默认索引呢？
hash在单值查询的时候是很快，时间复杂度接近O（1），但是在面对范围查找的时候就不尽人意了，由于hash结构是不会对插入数据进行排序的，当我们要进行范围查找的时候就必须全局扫描，时间复杂度就又上去了，因此hash结构的索引适合数据比较少的表，而对于B+索引来说，范围查找就是小意思，因为在B+树的每个page
中数据都是按序保存到，两个page直接也是连接起来的，我们只需要找到起始范围，然后接着往下查询即可！

B vs B+
B树：在这里插入图片描述
B+树:
在这里插入图片描述
目前这两颗树对于我们最大的意义就是:

  1. B树的目录page中不仅会存下级page的目录项，也会存一些有效数据;
  2. B+树种目录page种只存下级page的目录项，不存储有效数据；

那么为什么选择B+而不是B树呢?

  1. 从二者的结构上来看，B树的目录page中不仅会存下级page的目录项也会存一些数据项，那么相对于B+树来说，对于最底层的叶子节点来说就会减少，从而导致我们的整颗B树向中间缩进，变成“廋高”类型的一棵树，这样的话，我们在自定向下查找数据时会进行多次IO，增加IO的次数，从而降低整个服务的查询效率
  2. B树不能很好的支持范围查找，因为在B树中底层的叶子节点并没有相互连接起来，如果我们要查找的范围在多个page的话，那么针对B树来说MySQL就得经历多次自顶向下查询，也就是会进行多次IO，而同样情况下，相对于B+书来说，B+树中每个叶子节点之间都是相互连接的，那么当我们的范围出现在多个page的时候，我们只需要先找到第一个page，然后按照page之间的连接、继续加载下一个page即可，不用再自定向下查询，相比于B树来说降低了IO的次数，提高了服务的查询效率；

聚簇索引 VS 非聚簇索引

简单点来说:
聚簇索引:就是索引与有效数据合在一个文件中存储；
非聚簇索引:就是索引和有效数据分别存储一个文件;

eg:
InnocentDB存储引擎就是采用的聚簇索引，索引和有效数据是放在一起存储的:
证明:
在这里插入图片描述
这就叫做聚簇索引:
MyISAM存储引擎所采用的是非聚簇索引，也就是MyISAM将索引结构与有效数据分开存储:
在这里插入图片描述

非聚簇索引:
在这里插入图片描述
在非聚簇主键索引中，底层叶子page中存的是数据的key值与对应有效数据在另一个文件中的地址；
而在聚簇主键索引中，底层叶子page中存的就是以key值为索引的全部数据！

索引分类

索引主要分为:

  1. 主键索引
    以主键建立的索引，主键数据不能重复；
  2. 唯一键索引
    以唯一键建立的索引，唯一键数据不能重复
  3. 普通索引
    就是以表中普通的一列属性建立的索引，该列建立的索引是允许重复的
    其中:唯一键索引和普通索引，我们都叫做非主键索引;
  4. 全文索引
    通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

在聚簇索引的情况下:
非主键索引中，叶子page中并没有存储非主键值与之对应的有效数据，而是存储的非主键值与其该数据对应的Key值，在聚簇索引的情况下，我们可以通过非主键索引，先找到要查询的非主键列值对应的key值，然后在拿着查出来的主键回表 到主键索引查找该key值对应的有效数据:
在这里插入图片描述
聚簇索引这么做的理由是什么?
原因就是索引本身也是需要占用空间的，如果每床创建一个非主键索引就单独形成一个完整的数据，那么就太浪费空间了！

而在非聚簇索引的情况下，非主键索引的叶子page中存的数据就是:
非主键索引值+有效数据在其它文件中的地址，不需要进行回表操作，直接根据有效数据地址去读取有效数据即可！

索引操作

创建主键索引

  1. 第一种方式:
    create table user(id int primary key,name varchar(20));
  2. 第二种方式:
    create table user(id,int ,name varchar(20) primary key(id));
  3. 第三种方式:
    create table user(id,int ,name varchar(20));
    alter table user add primary key(id);

主键索引的特点:

  1. 一个表中最多有一个主键索引;
  2. 主键索引效率高（主键不可重复）
  3. 创建主键的列不可以为null，不可以重复
  4. 主键索引基本上都是整型居多；

创建唯一键索引

  1. 第一种方式
    create table user （id int primary key,name varchar(20),tel int unique);
  2. 第二种方式：
    create table user (id int primary key,name varchar(20),tel int ,unique key(tel));
    3.第三种方式:
    create table user(id int primary key,name varchar(20),tel int);
    alter table user add unique key(tel);

唯一键索引特点:

  1. 一个表中可以有多个唯一键索引
  2. 利用唯一键索引查询效率高
  3. 如果以某一列创建唯一索引，那么必须保证该列数据不能重复，可以为null
  4. 如果一个唯一键索引加上约束 not null等价于主键索引;

创建普通索引

  1. 第一种方式:
    create table user (id int primary key,name varchar(20),index(name));
  2. 第二种方式:
    create table user(id int primary key,name,varchar(20));
    alter table user add index(name);
  3. 第三种方式:
    create table user(id int primary key,name,varchar(20));
    create index index_name on user(name);

普通索引特点:

  1. 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
  2. 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

查询索引

  1. show index from table_name;
  2. show keys from table_name;
  3. desc table_name;
    在这里插入图片描述

删除索引

  1. 删除主键索引:
    allter table table_name drop primary key;
  2. 删除唯一键索引或者普通索引:
    alter table table_name drop index index_name;//主键索引不能用该语句删除
  3. drop index_name on table_name;//删除某表种索引名为index_name的索引;
    主键索引不能通过该语句删除，唯一键索引和普通索引可以;

索引创建原则:

  1. 比较频繁查询的字段可以设置为索引；
  2. 唯一性太差的字段不适合创建索引
  3. 更新频繁的字段不适合创建索引
  4. 不会出现在where子句中的字段不应该创建索引

索引的缺点

前面都是我们讲的索引的优点;通过创建索引，可以提高我们查询的效率，但是索引有没有缺点呢？

  1. 通过创建索引，查询效率是提高了，但是增删改的效率却变低了，因为在进行这些操作的时候，索引是动态变化的，需要我们时刻更新索引；
  2. 创建索引和维护索引要耗费时间，而且时间随着数据量的增加而增大
  3. 索引需要占用物理空间，如果要建立聚簇索引，所需要的空间会更大
南猿北者
关注
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
mysql 索引
liulanba的博客
10-25 1万+
在 MySQL 中创建索引可以大大提高查询效率，加快数据检索速度，他的索引包含 普通索引和唯一索引，以及主键索引，联合索引。
MySQL 索引
weixin_68840588的博客
06-17 4607
介绍MySQL索引的相关知识点
Mysql 索引
Impassive_y的博客
04-26 1万+
图片来源网络，侵删 索引 Mysql 的索引类型有很多种，Hash索引，B树索引，B+树索引和全文索引。Mysql有多种存储引擎，每个存储引擎对索引的支持可能会不同。 What Mysql 索引是能改善数据库表随机访问速度的一种数据结构。可以通过指定一个或者多个指定列的方式创建索引。 所以索引是一种数据结构。 Why 当数据量达到一定程度的时候，如果根据顺序扫描全部的数据，会出现数据查询效果低，查询时间长，导致用户体验不佳。为了提高数据的访问的效率，引入了索引。 How 创建索引的时候，需要指定至少一列数据
MySQL索引详解
m0_73594607的博客
07-20 3908
本篇文章详细讲解了MySQL中索引的知识, 包括他的原理, 优缺点, 使用, 以及的底层数据结构.
Mysql索引
qq_35716085的博客
07-11 5776
表名：student 字段：id,name,age,grade,school,sex 主键：id 普通索引：name 联合索引：age,grage,sex,school。磁盘预读：内存在跟磁盘交互的时候，有一个最基本的逻辑单位，称之为页，也叫做datapage,一般情况下是4k，跟操作系统相关，我们进行数据读取的时候默认读取页的整数倍。除二叉树，其他三棵树有序。B树：树的每个节点存储多个值，每个值会记录相应的键值（表中记录的主键）、指针（存储子节点的地址信息）、数据（表数据中除主键外的数据）。
MySQL 索引原理
杨宇昌的博客
04-24 1万+
MySQL索引深入剖析
MySQL索引类型
杨思默
03-06 6258
mysql索引类型
分享几道关于MySQL索引的重点面试题
01-19
但MySQL 索引你真的懂吗？这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则？ 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话，采用B+树的结构，...
快速了解MySQL 索引
12-14
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引分...
【Linux】MySQLMGR主从复制
Stringzhua的博客
10-30 842
MySQL是⽬前最流⾏的开源关系型数据库，国内⾦融⾏业也开始全⾯使⽤，其中MySQL5.7.17提出的 MGR(MySQL Group Replication)既可以很好的保证数据⼀致性⼜可以⾃动切换，具备故障检测功能、 ⽀持多节点写⼊，MGR是⼀项被普遍看好的技术。MGR(MySQL Group Replication)是MVSQL⾃带的⼀个插件，可以灵活部署。
easy-es使用以及Es和MySQL同步
weixin_51918722的博客
10-31 441
它主要就是简化了ES相关的API, 使用起来像MP一样舒服。
【重学 MySQL】七十九、深入探索用户变量
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
10-30 870
定义：用户变量以“@”符号开头，后面跟着变量名。变量名可以是任何随机的、复合的标量表达式，只要其中没有列指定。赋值可以使用SET语句来定义和赋值用户变量，如。也可以使用SELECT语句来为用户变量赋值，但必须使用“:=”赋值运算符，因为在SELECT语句中，MySQL将“=”运算符视为等于运算符。例如，。作用域：会话用户变量的作用域是当前会话，而局部变量的作用域是其定义的BEGIN/END语句块。命名：会话用户变量以“@”符号开头，而局部变量不需要。使用场景。
【MySql】第五章（存储函数与存储过程）习题
最新发布
她是不会无条件的！！
11-01 517
【MySql】第五章（存储函数与存储过程）习题
mysql 8.0.39 Caused by: java.sql.SQLException: Illegal mix of collations 异常解决
LFCuiYs的博客
10-30 392
发现设置utf8mb3_general_ci 会报错，所以要与参数一直 改成utf8mb4_general_ci 即可生效。java服务可以正常启动，页面发现查询报错Illegal mix of collations。经查看发现是关联表排序异常，因是mysql 8.0.39。然后定位异常 调成一直参数问题解决。
mysql索引
05-29
MySQL索引是一种数据结构，可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率，降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法： 1. 索引类型 MySQL支持多种类型的索引，包括B树索引、哈希索引、全文索引等。其中，B树索引是最常用的一种，也是默认的索引类型。B树索引可以用于精确匹配和范围查询，而哈希索引主要用于等值查询，全文索引则用于文本检索。 2. 索引创建 可以在创建表时指定索引，例如： ``` CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), INDEX idx_email (email) ); ``` 也可以在已有的表上添加索引，例如： ``` ALTER TABLE users ADD INDEX idx_name (name); ``` 3. 索引使用 查询语句中可以使用WHERE子句和ORDER BY子句来利用索引，例如： ``` SELECT * FROM users WHERE email = 'example@example.com'; SELECT * FROM users WHERE name LIKE 'John%' ORDER BY id DESC; ``` 需要注意的是，索引并不是越多越好，过多的索引会占用过多的磁盘空间并降低写操作的性能。因此，需要根据实际情况选择合适的索引。同时，还需要定期对索引进行维护，包括优化查询语句、删除不必要的索引等。 4. 索引优化 MySQL提供了一些工具来优化索引，例如EXPLAIN命令可以帮助分析查询语句的执行计划，找出慢查询和不必要的全表扫描。可以使用OPTIMIZE TABLE命令来优化表的索引和碎片，从而提高查询性能。还可以使用缓存来避免频繁的查询操作，例如使用Memcached或Redis等缓存工具。 以上就是MySQL索引的一些基本概念和使用方法，需要根据实际情况进行选择和优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

南猿北者

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值