mysql设置索引保留字 mysql索引设置怎么实现的

转载

编程梦想家 2024-01-23 22:37:19

文章标签 mysql设置索引保留字 mysql mysql索引索引原理 mysql索引分类 文章分类 MySQL 数据库

MySql索引

1.索引定义
1.1索引的优点

从使用角度来说
从底层原理来说

1.2索引的缺点
1.3索引选取的数据类型
1.4索引使用场景

什么场景不适合创建索引
什么样的字段适合创建索引

2.索引的类型及实现原理(从数据结构角度的来划分)
2.1哈希索引

2.1.1哈希索引特点
2.1.2哈希索引适用场景

2.2全文索引：

注意：

2.3 BTree索引
2.4 B+Tree索引（InnoDB和MyISAM默认使用B+Tree索引实现索引）
2.5 空间数据索引（R-Tree索引）
3.索引的分类（从物理存储角度来划分）
3.1 MyISAM——非聚簇索引（non-clustered index，也叫非聚集索引）

备注：

3.2InnoDB——聚簇索引（clustered index，也叫聚集索引）

备注：

3.3聚簇索引与非聚簇索引的区别

备注:

4 索引分类

4.1普通索引（又叫单列索引）
4.2唯一索引
4.3主键索引
4.4多列索引（联合索引）：

4.4.1联合索引测试案例
4.4.2查询条件为a :用到了索引a (长度为5)
4.4.3查询条件为b：未用到索引
4.4.4查询条件为c：未用到索引 (d同理)
4.4.5查询条件为 b 、 c ：未用到索引
4.4.6查询条件为 a 、 b：用到了联合索引 a 、b (长度为10)
4.4.7查询条件为 a、c ：用到了联合索引a (长度为5)
4.4.8查询条件为 a 、b、c、c：用到了联合索引a b c d (长度为20)
4.4.9查询条件为 a or b ：未用到索引
4.5.0 联合索引总结
4.5.1 查询条件为 name：使用到了索引 name（长度为512 = 4 * 128 + 2）
4.5.2查询条件为 name 、 age ：只使用了第一个 name索引（长度为512 = 4 * 128 + 2）
4.5. 3 查询条件为a > 3 使用了索引 a （长度为 5 ）
4.5. 4 查询条件为a = 1 and b > 1 ：使用了联合索引 a、b（长度为10）
4.5. 5 查询条件为a = 5 AND b > 6 AND c = 7 ：使用了联合索引 a、b（长度为10）
4.5. 6排序条件为order by a 却使用到了联合索引 a b c d ，而不是a
4.5. 7排序条件为order by b 未使用索引

5索引的优化
6索引的策略

什么时候要使用索引？
什么时候不要使用索引？
索引失效的情况：

1.索引定义

索引是告诉存储引擎，如何快速的找到数据。
（mysql的索引都是在存储引擎层实现的，而不是在mysql服务器层。）
不使用索引的话，MySQL必须全表扫描从第一条记录遍历到相关行，表越大，花费的时间越多。
索引是系统根据某种算法（如：哈希算法）将已有的数据（未来可能新增的数据）单独建立一个文件（索引本身会产生索引文件，有时候会比数据文件还打，所以索引建多了，也不是什么好事。），该文件能够实现快速的匹配数据，并且能够快速找到对应表中的记录。

1.1索引的优点

从使用角度来说

1.通过创建唯一索引，可以保证数据库每一行数据的唯一性
2.可以大大提高查询速度
3.可以加速表与表的连接
4.可以显著的减少查询中分组和排序的时间。

从底层原理来说

1、索引大大减少服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机I/O变为顺序I/O

1.2索引的缺点

1.创建索引和维护索引需要时间，而且数据量越大时间越长
2.创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达到最大文件尺寸
3.当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

1.3索引选取的数据类型

1、越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

2、简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。

3、尽量避免NULL：应该指定列为NOT nuLL,在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂

1.4索引使用场景

什么场景不适合创建索引

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

第五，不会出现在where条件中的字段不该建立索引。

什么样的字段适合创建索引

1、表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
4、经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
5、经常用到排序的列上，因为索引已经排序。
6、经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

2.索引的类型及实现原理(从数据结构角度的来划分)

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等，

2.1哈希索引

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。也因此哈希索引任何时候都不能避免全表扫描。

哈希索引，数据不是按照索引值顺序存储的，所以无法用于排序。

2.1.1哈希索引特点

a 仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询

b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引

c 只有Memory存储引擎显示支持hash索引

d Hash 索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

e Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。
hash值即为通过特定算法由指定列数据计算出来，磁盘地址即为所在数据行存储在硬盘上的地址（也有可能是其他存储地址，其实MEMORY会将hash表导入内存）。
这样，当我们进行WHERE age = 18 时，会将18通过相同的算法计算出一个
hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。

f 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。
解决办法：当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行数据比较，直到找到所有符合条件的行。如果哈希冲突有很多的话，一些索引维护操作的代价也会很高。

2.1.2哈希索引适用场景

（1）Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。例如，where price > 100 由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。例如，在数据列（A，B）上建立哈希索引，如果查询只有数据列A,则无法使用该索引

2.2全文索引：

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。
对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。
FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

//创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

注意：

*对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。

*5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引

*在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

*在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。

2.3 BTree索引

BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以一下条件：

每个叶子结点的高度一样，等于h；

每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；

叶子结点指针都为null；

非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

BTree的结构如下：

mysql设置索引保留字 mysql索引设置怎么实现的_mysql索引分类

在BTree的机构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构。

2.4 B+Tree索引（InnoDB和MyISAM默认使用B+Tree索引实现索引）

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

B+Tree中的非叶子结点不存储数据，只存储键值；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
B+Tree的每个非叶子节点由n个键值key和n个指针point组成；
B+Tree的结构如下：

mysql设置索引保留字 mysql索引设置怎么实现的_mysql设置索引保留字_02

B+Tree对比BTree的优点：

1、磁盘读写代价更低

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

2、查询速度更稳定

由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

3、带顺序索引的B+TREE
很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

B+Tree的结构如下：

mysql设置索引保留字 mysql索引设置怎么实现的_mysql设置索引保留字_03

2.5 空间数据索引（R-Tree索引）

索引	MyISAM引擎	InnoDB引擎	Memory引擎
B-Tree索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-Tree索引	支持	不支持	不支持
Full-text索引	支持	暂不支持（现在支持）	不支持

MyISAM表支持空间索引，可以用作地理数据存储。但是mysql的GIS相关函数支持并不完善，大部分人不用这个特性。开源关系型数据库对GIS的解决方案做的比较好的是PostgreSQL和PostGIS。

3.索引的分类（从物理存储角度来划分）

MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序

非聚簇索引的解释是:索引顺序与数据物理排列顺序无关

在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

3.1 MyISAM——非聚簇索引（non-clustered index，也叫非聚集索引）

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
非聚簇索引的数据表和索引表是分开存储的。
非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)
*最开始我一直不懂既然非聚簇索引的主索引和辅助索引指向相同的内容，为什么还要辅助索引这个东西呢，后来才明白索引不就是用来查询的吗，用在那些地方呢，不就是WHERE和ORDER BY 语句后面吗，那么如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。

备注：

1、非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。 记录的物理顺序与逻辑顺序没有必然的联系
2、索引是通过B-Tree的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

3.2InnoDB——聚簇索引（clustered index，也叫聚集索引）

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的数据和主键索引存储在一起。

聚簇索引的数据是根据主键的顺序保存。充分利用服务器的顺序I/O,避免随机I/O,因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。

在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

备注：

1、聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。 即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

2、聚集索引确定表中数据的物理顺序 。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引）， 就像电话簿按姓氏和名字进行组织一样。

3、聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。
例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

4、当索引值唯一时，使用聚集索引查找特定的行也很有效率。 例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

5、如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

3.3聚簇索引与非聚簇索引的区别

*使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

*因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。

*聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

下图可以形象的说明聚簇索引和非聚簇索引的区别：

mysql设置索引保留字 mysql索引设置怎么实现的_索引原理_04

从上图中可以看到聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

备注:

1、每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚簇索引。
2、聚集索引一张表只能创建一个，非聚集索引一张表可以创建多个，在mysql中InnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键（Primary Key）进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。

3、非聚簇索引需要大量的硬盘空间和内存。
另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。
每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。
如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。
另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。

此外MyISAM和innoDB的区别总结如下：

mysql设置索引保留字 mysql索引设置怎么实现的_mysql_05

4 索引分类

4.1普通索引（又叫单列索引）

仅加速查询最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

CREATE INDEX  index_name  on user_info(name)  ;

4.2唯一索引

与普通索引类型，不同的是：加速查询 + 列值唯一（可以有null）

CREATE UNIQUE INDEX  mail  on user_info(name)  ;

4.3主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.4多列索引（联合索引）：

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
注意：
1、对于联合索引，如果以错误的方式使用，则即使建立索引也会不奏效。
2、对于创建的多列联合索引,只要查询的条件中用到了最左边的列,索引一般就会被使用
3、用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,例如:必须or前后都有索引才能被使用，而且必须是单列索引。

4.4.1联合索引测试案例

可以通过key_len的长度来判断联合索引使用到了那些。

CREATE TABLE `d001_index` (
    `id` bigint(16) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(128) DEFAULT NULL,
    `age` bigint(20) DEFAULT '0',
    `country` varchar(50) DEFAULT NULL,
    `a` int(11) DEFAULT '0',
    `b` int(11) DEFAULT '0',
    `c` int(11) DEFAULT '0',
    `d` int(11) DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `idx_a_b_c_d` (`a`,`b`,`c`,`d`),
    KEY `idx_age` (`age`),
    KEY `idx_name` (`name`)
)



INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (1, 'zhangyj', 25, 'chine', 1, 2, 3, 4);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (2, 'healerjean', 24, 'china', 2, 3, 4, 5);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (3, 'n', 22, 'a', 2, 4, 5, 6);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (4, 'k', 2, 'b', 3, 5, 6, 8);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'zhangyj', 25, 'chine', 1, 2, 3, 4);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'healerjean', 24, 'china', 2, 3, 4, 5);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'n', 22, 'a', 2, 4, 5, 6);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'k', 2, 'b', 3, 5, 6, 8);

4.4.2查询条件为a :用到了索引a (长度为5)

explain  SELECT * from d001_index WHERE a = 1 ;

4.4.3查询条件为b：未用到索引

explain  SELECT * from d001_index WHERE b = 1 ;

4.4.4查询条件为c：未用到索引 (d同理)

explain  SELECT * from d001_index WHERE c = 1 ;

4.4.5查询条件为 b 、 c ：未用到索引

explain  SELECT * from d001_index WHERE b = 1 and c = 2 ;

4.4.6查询条件为 a 、 b：用到了联合索引 a 、b (长度为10)

explain  SELECT * from d001_index WHERE a = 1 and b = 2 ;

4.4.7查询条件为 a、c ：用到了联合索引a (长度为5)

explain  SELECT * from d001_index WHERE a = 1 and c = 3 ;

4.4.8查询条件为 a 、b、c、c：用到了联合索引a b c d (长度为20)

explain  SELECT * from d001_index WHERE a = 1 and b = 2 and c = 3  and d = 4 ;

4.4.9查询条件为 a or b ：未用到索引

explain  SELECT * from d001_index WHERE a = 1 or b = 2;

4.5.0 联合索引总结

1、顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上

2、多个单列索引在多条件查询时只会生效第一个索引！所以多条件联合查询时最好建联合索引！

当创建 (a,b,c)联合索引时,相当于创建了(a)单列索引，(a,b)联合索引以及 (a,b,c)联合索引
想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

3、具体使用 a b c 的顺序无关，mysql会自动优化，但是我们建议按照索引的顺序进行查询，而且尽量将筛选力度大的放到前面，其实这种也不要一定是准确的，其实真正有影响的是是否用到了索引

4.5.1 查询条件为 name：使用到了索引 name（长度为512 = 4 * 128 + 2）

explain  SELECT * from d001_index WHERE name = 'zhangyj' ;

4.5.2查询条件为 name 、 age ：只使用了第一个 name索引（长度为512 = 4 * 128 + 2）

4.5. 3 查询条件为a > 3 使用了索引 a （长度为 5 ）

4.5. 4 查询条件为a = 1 and b > 1 ：使用了联合索引 a、b（长度为10）

4.5. 5 查询条件为a = 5 AND b > 6 AND c = 7 ：使用了联合索引 a、b（长度为10）

explain SELECT * from d001_index WHERE a = 5 AND b > 6 AND c = 7

4.5. 6排序条件为order by a 却使用到了联合索引 a b c d ，而不是a

explain  SELECT * from d001_index  order by  a  ;

4.5. 7排序条件为order by b 未使用索引

explain  SELECT * from d001_index  order by b  limit  1;

5索引的优化

1、最左前缀

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。

2、带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，’%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。

3、为检索的条件构建全文索引，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

4、使用短索引
对字符串列进行索引，如果可能应该指定一个前缀长度。
例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

6索引的策略

什么时候要使用索引？

主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向组合索引；
用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不要使用索引？

经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

索引失效的情况：

在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
LIKE操作中，’%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，## 在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email=‘99999’。
在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：iOS 马甲包代码混淆 ios马甲包下载

下一篇：python 偏相关分析图偏相关分析spss

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯