MySQL高性能索引 索引类型、创建索引、实现高性能索引方法的全面总结
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL高性能索引 索引类型、创建索引、实现高性能索引方法的全面总结，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7797字，纯文字阅读大概需要12分钟。
内容图文
目录
索引是什么
索引(index)在MySQL中又叫作键“key”，是存储引擎用于快速找到记录的一种数据结构。
实现索引的数据类型
- B-Tree索引
- B+Tree索引
- 哈希索引
- 空间数据索引R-Tree
- 全文索引
MySQL中索引的类型以及创建方式
概览：
数据库索引分为两类：聚簇索引以及非聚簇索引（二级索引）。聚簇索引的索引顺序与行数据的物理顺序是一致的，反之就是非聚集索引。这两种索引实际上是由存储引擎实现的。
聚簇索引与非聚簇索引：
InnoDB的主键索引：
InnoDB的主键索引属于聚簇索引，非叶子节点保存了索引列以及下级节点的指针，而叶子节点保存了行数据、事务ID等。
主键索引保存的顺序和叶子节点的顺序总是保持一致的，当索引的位置发生改变时，保存在物理磁盘中的叶子节点的行数据也会相应改变其在物理磁盘的位置；
由于数据行无法放在两个不同地方，索引一个表只能有一个聚簇索引，而可以有多个非聚簇索引，因此InnoDB中，除了聚簇索引外的索引实际上都是非聚簇索引。
MyISAM的主键索引：
而MyISAM的主键索引属于非聚簇索引，它的表数据是按照行号递增的方式按顺序存储插入的，其叶子节点实际上保存的是指向这个数据表的行号指针，因此当索引位置发生改变时，改变的其实是叶子节点在树中的位置，其行数据实际上是不会改变的；
聚簇索引的优缺点：
优点：
- 数据访问更快，索引和数据都保存在同一个BTree中，因此获取数据通常比非聚簇索引（非聚簇索引会根据指针进行再次查表的操作）更快；
- 将相关数据保存在一起，可以有效的避免某些情况下的随机I/O（得益于物理顺序与索引顺序相同）；
缺点：
- 插入速度依赖于插入顺序。按照主键的插入顺序是加载数据到表中的最快方式。譬如自增主键插入1，2，3……那么它的插入对树的操作影响是最小的，它将会做最小树旋转操作；
- 表插入新行、主键更新导致移动行数据时，有可能产生页分裂（page split）的问题，从而占用更多磁盘空间；
- 更新列的代价很高，因为聚簇索引会强制InnoBD将每个被更新的行移动到新位置（个人理解，实际上索引位置变更才会移动位置，不然索引没变更，那么行又需要移动到哪去）
- 可能导致全表扫描变慢，尤其是行比较稀疏的时候，获取页分裂导致数据存储不连续的时候。
MySQL中使用InnoDB的索引创建：
以下的索引创建都以user表为例，当然在建表的时候可以直接在create见表语句中直接添加索引。
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`code` varchar(40) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
主键索引
描述：属于聚簇索引。不允许有NULL和重复值。只能存在一个主键索引。
--
PRIMARY KEY (`id`) USING BTREE
--
ALTER TABLE `user` ADD PRIMARY KEY (id)
-- 或者创建一个多列主键
ALTER TABLE `user`
ADD CONSTRAINT pk_userID PRIMARY KEY (`code`,`name`)
-- 撤销主键
ALTER TABLE `user` DROP PRIMARY KEY
以下索引都属于二级索引
普通索引
描述：允许重复值，允许多个NULL值。多列索引可以重复。
-- 建表create
KEY `uqIndex` (`code`) USING BTREE
-- 创建索引的方式
CREATE INDEX `nameIndex` ON `user` (`name`)
-- 多列索引
CREATE INDEX `nameIndexs` ON `user` (`code`,`name`)
-- 更新表的方式
ALTER TABLE `user` ADD KEY (`name`)
-- 删除指定索引
DROP INDEX `nameIndex` ON `user`;
唯一索引
描述：不允许重复值，允许多个null值。多列索引要求多列索引的组合是唯一的。
-- create
UNIQUE KEY `uqIndex` (`code`) USING BTREE
-- 单列
CREATE UNIQUE INDEX `uqIndex` ON `user` (`name`)
-- 多列索引
CREATE UNIQUE INDEX `uqIndexs` ON `user` (`code`,`name`)
前缀索引
描述：实际上就是截取列的部分字段进行索引。这一索引用以解决长字段的索引问题。它的语法跟普通索引一样，只是在列字段后加上表示截取数量的数字。
CREATE INDEX `nameIndex` ON `user` (`name`（1）)
ALTER TABLE `user` ADD KEY `nameIndex` (`name`(1))
索引的优点（B-Tree）
- 让服务器快速的定位到表的指定位置。
- 大大减少了服务器需要扫描的数据量
- 可以帮助服务器避免排序和临时表
- 可以将随机I/O变为顺序I/O（就访问内存的数据而言）
什么时候用到索引
- 总的来说，当索引帮助存储引擎快速查找到记录带来的好处大于期带来的额外工作时，索引才是最有效的。
- 对于非常小的表来说，大部分情况下全表扫描更有效。
- 对于中到大型的表，索引就很有效。
- 但对于特大型的表（如TB级别的数据），建立和使用索引的代价将随之增长。这个时候可以考虑分区，而不是通过索引来一条记录一条记录的匹配。
如何做到高性能的索引策略
独立的列
描述：索引列不能是表达式的一部分，也不能是函数的参数
实例：
为index_id列建立索引，如下反例将不会使用到索引：
--表达式的一部分:
select * from user where id + 1 = 5;
--函数的参数：
select * from user where concat(code, '_3852') = 'xxxx_3852';
模拟哈希索引
描述：对于长字符串的列，索引时可以模拟哈希索引，即增加一个列用于保存这个长字符串的hash值。可以使用的哈希函数有CRC32、SHA1、MD5（CRC32返回的是32位的整数，如果数表非常大，它就会出现大量冲突；而后两者是强加密函数，设计目标是最大限度的消除冲突，但是后两者生成的是很长的字符串，会浪费大量空间，且比较时也会很慢）。
优点：
- 提高了对长字符串的索引性能
- 提高了指定查询的效率，访问哈希索引的数据非常快
缺点：
- 哈希索引不是按索引值顺序存储的，只支持等值比较查询，不支持任何范围查询，也无法用于排序
- 不支持部分索引列匹配查找
- 需要维护hash值
- 当hash冲突过多，一些索引的维护操作的代价会很高。比如，当删除一个hash冲突高的一个哈希索引时，存储引擎此时需要遍历多个hash值相等的行，以删除对应的数据，冲突越多，代价越大。
前缀索引和索引选择性
描述：除了使用模拟哈希索引外，对于长字符串，还可以使用前缀索引的方式。即只索引列开始部分的字符。要选择足够长的前缀以保证较高的选择性，使前缀索引的选择性接近于索引的整个列，同时又不能太长以便节约空间。
优点：
这样可以大大节约索引空间，提高索引效率。
缺点：
会降低索引的选择性（选择的前缀索引还是会存在冲突，即相等的情况）
索引的选择性：是指不重复的索引值（基数cardinality）和数据表的记录总数的比值。选择性的值越大则重复性越低、查询效率越高。
索引选择性的一般计算：
SELECT COUNT(DISTINCT `name`) /COUNT(*) FROM `user`
SELECT COUNT(DISTINCT LEFT(`name`, 1)) /COUNT(*) FROM `user`
多列索引
描述：关于多列索引的常见错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多个索引。实际上在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0以后的版本引入了索引合并的策略，一定程序上可以使表的多个单列索引来定位指定的行。
索引合并（index merge）：能使单列索引进行扫描，并将结果进行合并。这个算法有三个变种，OR条件的联合，AND条件的相交，组合前两种情况的联合及相交。
选择合适的索引列顺序
描述：正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。
方法：
- 当排序和分组不那么重要时，可以将选择性最高的列放刀索引最前列，这种索引方式能很大条件的优化WHERE条件的查询。
- 查询性能除了考虑选择性之外，还与考虑查询条件的具体值，也就是值的分布有关。这个时候应该根据那些查询频率最高的查询来调整索引列的顺序。
覆盖索引
描述：索引覆盖的意思是，索引的叶子节点（最底层节点）中已经包含了要查询的字段值，这样MySQL就可以通过索引来直接获取列的数据，这样就不再需要读取数据行了（在EXPLAIN的Extra列看到Using index时，就代表了使用了索引覆盖查询）。实际上在5.6版本中，在存储引擎API上做了一个重要改进，即索引条件推送（index condition pushdown）。
优点：
- 索引条目远小于数据行时，如果只需要读取索引，那MySQL就会极大的减少数据访问量。
- 索引的顺序存储形式，对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。
- 覆盖索引对InnoDB特别有用，InnoDB的二级索引在叶子节点保存了行的主键值，如果二级主键能够覆盖查询，那么就可以避免对主键索引的二次查询。
缺点：
- 覆盖索引不易实现，大部分的关联查询中实现条件较为苛刻。假设索引覆盖了WHERE条件中的字段，但不是整个查询涉及的字段，5.5及之前的版本总是会回表获取数据行。
实例：
如果以下查询有一个多列索引（user_id, role_id），那么下面的查询就是一个覆盖查询，因为索引已经包含了查询结果，注意加上主键，在innoDB中也是覆盖索引，因为该索引作为一个二级索引，其叶子节点保存了主键值。
SELECT user_id, role_id FROM user_role_relation
避免索引无效的情况
索引无效的情况：
- 查询表中的所有列无法名中任何索引，因为没有任何索引覆盖了所有列。
- MySQL不能在索引中执行LIKE操作。MySQL能在索引中做最左前缀匹配的LIKE比较，，即LIKE 'word%'，是因为该操作可以转换为简单的比较操作（MySQL5.5及之前只允许索引做简单的比较操作、大于、小于、不等于）。
参考
书籍：高性能MySQL
内容总结
以上是互联网集市为您收集整理的MySQL高性能索引 索引类型、创建索引、实现高性能索引方法的全面总结全部内容，希望文章能够帮你解决MySQL高性能索引 索引类型、创建索引、实现高性能索引方法的全面总结所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。