MYSQL索引的使用详解

MySQL数据库中关于索引的使用，以及各索引的区别。在开发时经常遇到大数据查询缓慢等情况，这时候需要给相对应的字段添加索引来增加sql的执行速度。

一．索引概述

索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何 索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询 效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅 速翻阅到指定的章节，提升的查询性能，节约了查询资源。 由于引擎的关系，MyISAM 表是将数据行存放在数据文件里，索引值存放在索引文件 里。索引就是一个排好序的键值对数组，查询时通过这种键值对快速找到对应的数据。对于 InnoDB 表来说，它的索引也是排好序的数组，但它的数据行与索引值存放在同一个文件里。

二．索引种类

从索引的定义方式和用途中来看，所以一共有四种索引：

1.普通索引(Index)，用于提升查询效率；

2.唯一索引(Unique)，除了提升查询效率还要求字段值不得重复；

3.主键索引(PrimaryKey)，唯一性且不得为空的索引；

4.全文索引(Fulltext)，用于在大量文本搜索中建立的索引。

PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值； 主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但 唯一索引不一定是主键索引。

相比单表查询，如果没有建立索引，最多就是全表扫描一遍。但对于多表查询时，索引 的重要度就要大很多。比如三张表的联合查询时，如果没有建立索引，将是三张表数据行的 乘积。比如：

//假设每个表有 1000 条数据，那么需要扫描 1000*1000*1000=10 亿次 WHEREt1.c1=t2.c2ANDt1.c1=t3.c3;
如果给 c2，c3 分别建立了索引，那么充其量只扫描 t1 一个全表扫描，这样另外两张表 的 1000*1000=100 万次的乘积扫描量就减少了，大大的提高了查询速度。

虽然索引看上去那么美好，但凡事都有两面性，在提高效率和速度的同时却增加了某些负担：

1.索引需要使用更多的磁盘空间，索引越多，占用磁盘越多，有时甚至比表占用的多；

2.索引在提升查询速度的同时，降低了插入、更新和删除的操作速度，因为它们多了一 项工作，就是在增删改的过程中更新索引。索引越多，速度越慢。 所以，索引优化是一门比较大的学问，需要根据业务、环境、配置等一些列问题进行最 右设计，需要较长时间的学习和实践才能有所体会。

三．索引操作

1.创建主键索引 这个索引比较简单，我们一直在使用，只要创建一个无符号整型且自动增长的列，然后 设置成主键即可。

//通过 EXPLAIN 语句查看索引状态 EXPLAIN SELECT * FROM think_user WHERE id=1;
2.创建普通或唯一索引 直接进入 navicat 设计表的第二栏，选择一个字段(比如 user 字段)，添加一个 Nomral(普 通索引)或 Unique(唯一索引)。

//通过 EXPLAIN 语句查看索引状态 EXPLAIN SELECT * FROM think_user WHERE user='蜡笔老新';
//查看表所有索引情况 SHOW INDEX FROM think_user;
PS：在添加主键、唯一、普通索引的时候，我们会需要选择一种数据结构，默认为 B-Tree(二叉树)结构，还有一种为 HASH(哈希)结构。从理论上来看，HASH 索引的效率比 B-Tree 高，但常用的还是 B-Tree，因为两种在各自查询的方式和范围有所不同。

1.Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

2.Hash 索引无法被用来避免数据的排序操作。

3.Hash 索引不能利用部分索引键查询。

4.Hash 索引在任何时候都不能避免表扫描。

5.Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 B-Tree 索引高。

所以，要使用 HASH，就必须结合具体业务和大量经验。

3.全文索引 这个索引，一般来说对于我们用处不大。属于在大文章里进行对某些关键字进行索引， 但由于中文分词识别问题，基本无法使用。那么对于英文状态下，建立全文索引后，可以依 次 SQL 查询。

//全文索引搜索 SELECT * FROM think_user WHERE MATCH (intro) AGAINST ('teacher');
PS：如果搜索 is，则无效，因为这是最常见的却无意义的词，被 MySQL 设置成停止词。

四．索引原则

１.不要过度索引。索引越多，占用空间越大，反而性能变慢；

２.只对 WHERE 子句中频繁使用的建立索引；

３.尽可能使用唯一索引，重复值越少，索引效果越强；

４.使用短索引，如果 char(255)太大，应该给它指定一个前缀长度，大部分情况下前 10 位或 20 位值基本是唯一的，那么就不要对整个列进行索引；

５.充分利用左前缀，这是针对复合索引，因为 WHERE 语句如果有 AND 并列，只 能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将 WHERE 最频繁的放置在左边。

如何创建mysql索引以及索引的优缺点

mysql教程：索引的使用以及索引的优缺点1. 索引（index）是帮助MySQL高效获取数据的数据结构。 它对于高性能非常关键，但人们通常会忘记或误解它。 索引在数据越大的时候越重要。 规模小、负载轻的数据库即使没有索引，也能有好的性能，但是当数据增加的时候，性能就会下降很快。 Tip:蠕虫复制,可以快速复制大量的数据例：insert into emp select * from emp; 2. MySQL中常见的索引 ◆普通索引 ◆唯一索引 ◆主键索引◆组合索引 ◆全文索引◆外键 （只有innodb存储引擎才支持）2.1普通索引：这是最基本的索引，它没有任何限制。 有以下几种创建方式：有以下几种创建方式：◆创建索引CREATE INDEX indexName ON tablename(username(length)); ◆修改表结构ALTER tablenameADD INDEX indexName (username(length))Tip：length可以小于字段实际长度;如果是BLOB 和 TEXT 类型，必须指定length ,下同◆创建表的时候直接指定CREATE TABLE mytableuuu( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX indexName (username(length)) );CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL);create index index1 on mytable(id);//创建普通索引◆删掉索引：drop index index1 on mytable;有一个概念，行定义：在声明字段（列）的时候定义的，比如primary key表定义：在所有字段（列）声明完之后定义的，比如primary key，indexCREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL,index index1(username));3.0唯一索引（unique）索引列的值必须唯一，但允许有空值。 1）创建索引：Create UNIQUE INDEX indexName ON tableName(tableColumns(length))2）修改表结构：Alter tableName ADD UNIQUE [indexName] ON (tableColumns(length)3）创建表的时候直接指定：Create TABLE tableName ( [...], UNIQUE [indexName](tableColumns(length)); 4.0主键索引（primarykey）

mysql索引的应用场景以及如何使用

唯一的是什么？1.索引列（字段）的所有值都只能出现一次，即必须唯一--------------------------------------------------------------------------------主键索引与唯一索引的区别1.主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。 2.主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。 3.唯一性索引列允许空值，而主键列不允许为空值。 4.主键列在创建时，已经默认为空值++ 唯一索引了。 5.一个表最多只能创建一个主键，但可以创建多个唯一索引。 6.主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。 7.主键可以被其他表引用为外键，而唯一索引不能。 ？--------------------------------------------------------------------------------复合索引1.用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。 2.就是几个字段联合在一起组成一个索引.复合索引的创建方法与创建单一索引的方法完全一样。 3.但复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。 4.当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度。 --------------------------------------------------------------------------------（abc） （ab） （ac）（bc）（a） （b） （c）1.复合索引又叫联合索引。 2. abc ab a ac 可以3.对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。 4.例如索引是key index (a,b,c).可以支持a | a,b| a,b,c 3种组合进行查找，但不支持b,c进行查找,当最左侧字段是常量引用时，索引就十分有效。 --------------------------------------------------------------------------------除复合索引外，什么情况下索引被使用，什么情况下不会被使用1.建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立。 2.如果表是经常需要更新的也不适合做索引 。 频繁更新会导致索引也会频繁更新，降低写的效率。 3.唯一性差的字段不适合创建索引。 4.当给一个字段创建了索引的话，而这个字段要进行like模糊查询的话，那么这个值左边不可以有%，因为索引查询是要从左到右的，你如果给它加上%后，左边的值不是确定的话，它会找不到这个索引。 所以在使用like模糊查询的时候，值得左边不可以有%。 5. order by 不会使用索引6. or当前后2个字段都有索引时才可以索引出来 否则不可以。 7.如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引。 查询大量数据时,索引有效,但是慢8.不使用索引的原因：因为索引时会先过一遍索引在过一遍数据--------------------------------------------------------------------------------全文检索：1.全文检索就是FULLTEXT，用于MyISAM表，在列类型为CHAR、VARCHAR或TEXT列上创建。 2.将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTER TABLE (或CREATE INDEX)创建索引，这将是非常快的。 3.将数据装载到一个已经有FULLTEXT索引的表中，将是非常慢的。 4. MySQL自带的全文索引只能对英文进行全文检索。 要检索中文用sphinx。 5.创建全文检索：alter table表名add fulltext index索引名(列名,[列名]，[列名]..（可以有多个，可以有一个）6.在创建表的时候建全文检索：CREATE TABLE article ( INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, (title, content) --在title和content列上创建全文索引);--------------------------------------------------------------------------------场景1.当数据多且字段值有相同的值得时候用普通索引。 2.当字段多且字段值没有重复的时候用唯一索引。 3.当有多个字段名都经常被查询的话用复合索引。 4.普通索引不支持空值，唯一索引支持空值。 5.但是，若是这张表增删改多而查询较少的话，就不要创建索引了，因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，6.若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集，7.若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集，8.若是删，则会把索引中以这个字段为名的索引的子集删掉。 9.所以，会对增删改的执行减缓速度，10.所以，若是这张表增删改多而查询较少的话，就不要创建索引了。 11.更新太频繁地字段不适合创建索引。 12.不会出现在where条件中的字段不该建立索引。 --------------------------------------------------------------------------------索引创建CREATE [UNIQUE|CLUSTERED] INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)；其中UNIQUE和CLUSTERED为可选项，分别是建立唯一索引和聚簇索引，具体解释为：UNIQUE:表示此索引的每一个索引值只对应唯一的数据。 CLUSTERED:表示要建立的索引时聚簇索引，即索引项的顺序与表中记录的物理顺序一致的索引组织ALTER TABLE table_name ADD INDEX index_name (column_list)mysql索引的应用场景以及如何使用标签：col如何alt也会自动物理bodynot选项

MySQL索引篇

MySQL中的索引是数据检索的加速器，犹如数据海洋中的导航灯塔，通过磁盘存储优化查询速度。 其核心优势在于显著提升检索效率，减少磁盘IO和CPU的消耗，但同时也需权衡磁盘空间和更新性能的牺牲。 MySQL索引类型丰富多样，包括单列、组合、主键、唯一、全文和空间索引，每一种都适用于不同的查询场景，如等值、范围查找时，需兼顾时间和空间效率的考量。 在众多数据结构中，Hash表以O(1)的等值查询效率受青睐，但范围查找却并非其强项；相比之下，红黑树的理想O(logn)查询性能令人瞩目，但树的高度影响了磁盘IO，如百万数据需20次IO，耗时0.2秒。 B树的改进则在于通过多叉结构将IO降至2层，大大提升查询效率，支持等值查询，但范围查询需要递归遍历。 MySQL的B+树在此基础上进一步优化，非叶子节点不存储数据，减小空间占用，实现了等值查询路径稳定和范围查询的递归搜索。 磁盘IO是查询性能的关键，例如在MyISAM引擎中，如id为主键、age为索引的表t_user_myisam中，查询30时，需经历三次IO（根-左子-叶节点）；而范围查询如30<56，只需两次IO。 MyISAM利用MySQL缓存和操作系统缓存来优化，辅助索引与主键结构相似，但范围查询可能需借助范围遍历。 InnoDB引擎则以聚簇索引（B+树）为核心，辅助索引存储主键值，要求表必须有主键。 主键查询快速，辅助索引查询时需回表查找，可能涉及更多磁盘IO。 组合索引在设计时需谨慎，既要考虑查询效率，又要考虑构建和查找的复杂性。 在索引的选择和创建上，遵循一些基本原则。 首先，选择频繁出现在where、order by和group by中的列进行组合，如(a, c)联合索引适合a查询和c排序。 其次，考虑覆盖索引，当辅助索引能包含查询所需所有列时，可以避免回表查询，节省资源。 MySQL 5.6引入了索引条件下推（ICP），通过在检索时预判索引条件，减少回表次数，进一步优化查询性能。 总的来说，优化MySQL索引是提升查询性能的关键，要根据实际需求选择合适的索引类型，遵循覆盖索引和索引条件下推等策略，以实现高效、空间节约的查询处理。 在设计索引时，还要注意全值匹配、最左前缀等原则，避免无效使用，以达到最佳性能。 通过这些技巧，MySQL索引能如虎添翼，为你的数据查询保驾护航。

更多优质资源请微信扫码访问：盘姬资源网小程序