mysql索引

梅你不可的小东西
149 阅读6分钟

mysql索引

索引的概念

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

索引的作用

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

索引的副作用

  • 索引需要占用额外的磁盘空间

    对于MyISAM引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址

而InnoDB引擎的表数据文件本身就是索引文件

  • 更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新

    理想的做法是仅仅在常被搜索的列（以及表）上面创建索引

索引如何工作

  • 如何索引时，每次查询都需要先全表扫描来定位行数据，查询速度慢，IO资源消耗大

  • 有索引时，会先进行索引查询，然后通过索引值的数据对应的物理地址定位到具体数据行，从而加快查询速度

创建索引的原则依据

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

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

索引的分类

  • 普通索引：最基本的索引类型，没有唯一性的限制
  • 唯一索引：与普通索引类似，但区别是唯一索引列的每个值都为一，唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引
  • 主键索引：是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。添加主键将自动创建主键索引
  • 组合索引（单列索引与多列索引）：可以是在多列上创建的索引

普通索引

直接创建索引

create index 索引名 on 表名 （列名（length））；
#索引名建议以“_index”结尾
#length是可选项，如果忽略length的值，则使用整个列的值作为索引。如果指定，使用列的前length个字符来创建索引，这样有利于减少索引文件的大小

微信截图_20221021160121.png

微信截图_20221021160327.png

修改表方式创建索引

alter table 表名 add index 索引名 （列名）；

微信截图_20221021163144.png

创建表的时直接创建索引

create table 表名 （字段1 数据类型，字段2 数据类型，...,index 索引名 （列 名））；

微信截图_20221021172617.png

删除索引方法一

  drop index 索引名 on 表名；

微信截图_20221021173614.png

微信截图_20221021173720.png

删除索引方法二

 alter table 表名 drop index “索引名”；

微信截图_20221023202907.png

唯一索引

直接创建唯一索引

 create unique index  索引名  on 表名 （列 名）

微信截图_20221031120944.png

修改表方式创建

 alter table 表名 add unique 索引名（列 名）；

微信截图_20221031121757.png

创建表时创建

create  table 表名 （字段1 数据类型，....，unique 索引名（列 名））；

微信截图_20221102192916.png

主键索引

创建表时创建

create table 表名 （字段1 数据类型，...,primary key （列 名））；

微信截图_20221102195640.png

修改表方式创建

alter table 表名 add primary key（列 名）；

微信截图_20221102200222.png

删除主键索引

 alter table 表名 drop primary key；

微信截图_20221102200010.png

组合索引

直接创建

create index 索引名 on 表名 (字段名1，字段名2...)；

微信截图_20221102202139.png

修改表方式创建

 alter table 表名 add index 索引名 （字段1，字段2...）；

微信截图_20221102202344.png

唯一组合索引

多列组合的值不能重复，其中某个单列是可以重复的

直接创捷

create unique index 索引名 on 表名 （字段名1，字段名2，...）；

微信截图_20221102202931.png

修改表方式创建

 alter table 表名 add unique 索引名 （字段名1，字段名2，...）；

微信截图_20221102203512.png

全文索引

只适用于char、varchar、text类型字段

直接创建

create fulltext index 索引名 on 表名（列 名）；

微信截图_20221102204032.png

修改表方式创建

 alter table 表名 add fulltext 索引名 （字段名）；

微信截图_20221102204625.png

使用全文索引查询

 select * from 表名 where match（列 名） against（'查询内容'）；

微信截图_20221102205148.png

各字段的含义

微信截图_20221102205924.png

Table：表的名称 Non_unique：如果索引不能包括重复词，则为0；如果可以，则为1 Key_name：索引的名称 Seq_in_index：索引中的列序号，从1开始 Column_name：列名称 Collation：列以什么方式存储索引中。在MySQL中，有值'A'(升序)或Null（无分类） Cardinality：索引中唯一值数目的估计值 Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为Null Packed：指示关键字如何被压缩，如果没有被压缩，则为Null Null：如果列含有Null，则含有yes；如果没有，则该列含有NO Index_type：用过的索引方法（btree、fulltext、hash、rtree） comment：备注

select语句查询慢咋办（重点）

explain 分析select语句，确认是否实际使用索引，如果没有索引则给相关添加索引

微信截图_20221102212523.png

avatar
文章
阅读
粉丝