MYSQL索引

MySQL索引

数据库中提供了存储共享数据的功能,但是更重要的是快速高效的获取指定的数据。
在没有索引的情况下,如果要寻找特定行,数据库可能要遍历整个数据库,使用索引后,数据库可以根据索引找出这一行,极大提高查询效率。

什么是索引

MySQL官方对索引的定义为:索引Index是帮助MySQL高效获取数据的数据结构。
索引的本质:索引是数据结构。
数据库查询是数据库的最主要功能之一,希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。

MySQL目前主要有普通索引、唯一索引、主键索引、组合索引和全文索引共5种索引类型。

普通索引和唯一索引、主键索引

普通索引和唯一索引是根据索引的功能来划分。
普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值。

CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

DROP INDEX index_name ON table

唯一索引的索引列的值必须唯一,但允许空值。如果是组合索引,则列的值得纸盒必须唯一。主键索引是一种特殊的唯一索引,不允许有空值。

CREATE UNIQUE INDEX indexName ON table(column(length))
 ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

主键索引

一种特殊的唯一索引,不允许有空值,一般在建表的时候同时建立主键索引

单列索引和组合索引

单列索引和组合索引的划分是根据创建索引时所引用的列的数量来划分。
单列索引是只一个索引只包含单个列,一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建索引,但只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。
ALTER TABLE table ADD INDEX name_city_age (name,city,age);
组合索引可以起到多个索引的作用,但是使用时并不是随便哪个字段都可以使用索引的,而是遵从“最左前缀”:利用索引中最左边的列集列匹配行,这样的列集称为最左前缀。例如由id、name、age这3个字段字段构成的索引,索引行中按id/name/age的顺序存放,索引可以搜索下面字段组合:(id, name , age)、(id , name)、(id)。如果不构成索引的最左边的前缀,MySQL不能使用局部索引,如(age)、(name, age)都不能使用索引进行查询。

全文索引

全文索引类型为FULLTEXT,在定义索引的列上至此值需得全文查找,允许在这些索引列上插入空值和重复值
like + % 在文本比较少时是合适的,但是对于大量的文本数据检索,是不可想象的。全文索引在大量的数据面前,能比 like + % 快 N 倍,速度不是一个数量级,但是全文索引可能存在精度问题。
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
create fulltext index cft on test(content,tag);
在具体的应用中,一般不会使用数据库管理系统的全文索引,而是引入ElasticSearch这种NoSQL数据库实现

空间索引

空间索引可以用作地理数据存储,通过R树来实现。空间索引会从所有维度来索引数据,使得空间搜索变得高效。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据。
space geometry not null,
spatial index index6_sp(space)
创建了一个名为index6_sp的空间索引
索引的优点
可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性

建立索引可以大大提高检索的数据,以及减少表的检索行数
在表连接的连接条件 可以加速表与表直接的相连
在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
建立索引,在查询中使用索引 可以提高性能,但是会影响修改的性能

索引的缺点

在创建索引和维护索引 会耗费时间,随着数据量的增加而增加,所以一般建议一个表上创建的索引数不要超过6个
索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快)

注意事项

  • 1、索引不会包含有null值的列,在数据库设计时不要让索引字段的默认值为null。
  • 2、使用短索
  • 3、索引列排序
  • 4、like语句操作, like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
  • 5、不要在列上进行运算
  • 6、不使用not in和<>操作
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
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
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值