2023年7月27日发(作者：)

数据库索引设计索引的类型：normal：表⽰普通索引unique：表⽰唯⼀的，不允许重复的索引，如果该字段信息保证不会重复例如⾝份证号⽤作索引时，可设置为uniquefull textl: 表⽰ 全⽂搜索的索引。 FULLTEXT ⽤于搜索很长⼀篇⽂章的时候，效果最好。⽤在⽐较短的⽂本，如果就⼀两⾏字的，普通的INDEX 也可以。总结，索引的类别由建⽴索引的字段内容特性来决定，通常normal最常见。普通索引 添加INDEXALTER TABLE `table_name` ADD INDEX index_name ( `column` )下⾯演⽰下给user表的name字段添加⼀个索引mysql数据库如何创建索引mysql数据库如何创建索引2主键索引 添加PRIMARY KEYALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )mysql数据库如何创建索引mysql数据库如何创建索引3唯⼀索引 添加UNIQUEALTER TABLE `table_name` ADD UNIQUE ( `column` )mysql数据库如何创建索引4全⽂索引 添加FULLTEXTALTER TABLE `table_name` ADD FULLTEXT ( `column`)mysql数据库如何创建索引5如何添加多列索引ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )索引建⽴的原则基于合理的数据库设计，经过深思熟虑后为表建⽴索引，是获得⾼性能数据库系统的基础。⽽未经合理分析便添加索引，则会降低系统的总体性能。索引虽然说提⾼了数据的访问速度，但同时也增加了插⼊、更新和删除操作的处理时间。是否要为表增加索引、索引建⽴在那些字段上，是创建索引前必须要考虑的问题。解决此问题的⼀个⽐较好的⽅法，就是分析应⽤程序的业务处理、数据使⽤，为经常被⽤作查询条件、或者被要求排序的字段建⽴索引。基于优化器对SQL语句的优化处理，我们在创建索引时可以遵循下⾯的⼀般性原则：（1）为经常出现在关键字order by、group by、distinct后⾯的字段，建⽴索引。在这些字段上建⽴索引，可以有效地避免排序操作。如果建⽴的是复合索引，索引的字段顺序要和这些关键字后⾯的字段顺序⼀致，否则索引不会被使⽤。（2）在union等集合操作的结果集字段上，建⽴索引。其建⽴索引的⽬的同上。（3）为经常⽤作查询选择的字段，建⽴索引。（4）在经常⽤作表连接的属性上，建⽴索引。（5）考虑使⽤索引覆盖。对数据很少被更新的表，如果⽤户经常只查询其中的⼏个字段，可以考虑在这⼏个字段上建⽴索引，从⽽将表的扫描改变为索引的扫描。除了以上原则，在创建索引时，我们还应当注意以下的限制：（1）限制表上的索引数⽬。对⼀个存在⼤量更新操作的表，所建索引的数⽬⼀般不要超过3个，最多不要超过5个。索引虽说提⾼了访问速度，但太多索引会影响数据的更新操作。（2）不要在有⼤量相同取值的字段上，建⽴索引。在这样的字段（例如：性别）上建⽴索引，字段作为选择条件时将返回⼤量满⾜条件的记录，优化器不会使⽤该索引作为访问路径。（3）避免在取值朝⼀个⽅向增长的字段（例如：⽇期类型的字段）上，建⽴索引；对复合索引，避免将这种类型的字段放置在最前⾯。由于字段的取值总是朝⼀个⽅向增长，新记录总是存放在索引的最后⼀个叶页中，从⽽不断地引起该叶页的访问竞争、新叶页的分配、中间分⽀页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插⼊操作都集中在最后⼀个数据页上进⾏，从⽽引起插⼊“热点”。（4）对复合索引，按照字段在查询条件中出现的频度建⽴索引。在复合索引中，记录⾸先按照第⼀个字段排序。对于在第⼀个字段上取值相同的记录，系统再按照第⼆个字段的取值排序，以此类推。因此只有复合索引的第⼀个字段出现在查询条件中，该索引才可能被使⽤。因此将应⽤频度⾼的字段，放置在复合索引的前⾯，会使系统最⼤可能地使⽤此索引，发挥索引的作⽤。（5）删除不再使⽤，或者很少被使⽤的索引。表中的数据被⼤量更新，或者数据的使⽤⽅式被改变后，原有的⼀些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从⽽减少索引对更新操作的影响。索引建⽴原则⼆：建⽴索引的原则：1) 定义主键的数据列⼀定要建⽴索引。2) 定义有外键的数据列⼀定要建⽴索引。3) 对于经常查询的数据列最好建⽴索引。4) 对于需要在指定范围内的快速或频繁查询的数据列;5) 经常⽤在WHERE⼦句中的数据列。6) 经常出现在关键字order by、group by、distinct后⾯的字段，建⽴索引。如果建⽴的是复合索引，索引的字段顺序要和这些关键字后⾯的字段顺序⼀致，否则索引不会被使⽤。7) 对于那些查询中很少涉及的列，重复值⽐较多的列不要建⽴索引。8) 对于定义为text、image和bit的数据类型的列不要建⽴索引。9) 对于经常存取的列避免建⽴索引9) 限制表上的索引数⽬。对⼀个存在⼤量更新操作的表，所建索引的数⽬⼀般不要超过3个，最多不要超过5个。索引虽说提⾼了访问速度，但太多索引会影响数据的更新操作。10) 对复合索引，按照字段在查询条件中出现的频度建⽴索引。在复合索引中，记录⾸先按照第⼀个字段排序。对于在第⼀个字段上取值相同的记录，系统再按照第⼆个字段的取值排序，以此类推。因此只有复合索引的第⼀个字段出现在查询条件中，该索引才可能被使⽤,因此将应⽤频度⾼的字段，放置在复合索引的前⾯，会使系统最⼤可能地使⽤此索引，发挥索引的作⽤。组合多个索引⼀个单独的索引扫描只能⽤于这样的条件⼦句：使⽤被索引字段和索引操作符类中的操作符， 并且这些条件以AND连接。假设在(a, b)上有⼀个索引， 那么类似WHERE a = 5 AND b = 6的条件可以使⽤索引，但是像WHERE a = 5 OR b = 6的条件就不能直接使⽤索引。⼀个类似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 这样的查询可以分解成四个在x上的独⽴扫描，每个扫描使⽤⼀个条件， 最后将这些扫描的结果OR 在⼀起，⽣成最终结果。另外⼀个例⼦是，如果我们在x 和y上有独⽴的索引，⼀个类似WHERE x = 5 AND y = 6 这样的查询可以分解为⼏个使⽤独⽴索引的⼦句，然后把这⼏个结果AND 在⼀起，⽣成最终结果。在⼤多数最简单的应⽤⾥，可能有多种索引组合都是有⽤的，数据库开发⼈员必须在使⽤哪个索引之间作出平衡。有时候多字段索引是最好的，有时候创建⼀个独⽴索引并依靠索引组合是最好的。⽐如，假如你的查询有时候只涉及字段x，有时候只涉及字段y，有时候两个字段都涉及， 那么你可能会选择在x和y上创建两个独⽴的索引， 然后依靠索引组合来处理同时使⽤两个字段的查询。你也可以在(x, y)上创建⼀个多字段索引， 它在同时使⽤两个字段的查询通常⽐索引组合更⾼效，但是对那些只包含y的查询⼏乎没有⽤，因此它不能是唯⼀⼀个索引。⼀个多字段索引和y上的独⽴索引可能会更好。因为对那些只涉及x的查询， 可以使⽤多字段索引，但是它会更⼤，因此也⽐只在x上的索引更慢。最后⼀个选择是创建三个索引， 但是这种⽅法只有在表的更新远⽐查询少，并且所有三种查询都很普遍的情况下才是合理的。如果其中⼀种查询⽐其它的很多，那么你可能更愿意仅仅创建两种匹配更常见查询的索引。索引⽅法的区别：Hash 索引结构的特殊性，其检索效率⾮常⾼，索引的检索可以⼀次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远⾼于 B-Tree 索引。可能很多⼈⼜有疑问了，既然 Hash 索引的效率要⽐ B-Tree ⾼很多，为什么⼤家不都⽤ Hash 索引⽽还要使⽤ B-Tree 索引呢？任何事物都是有两⾯性的，Hash 索引也⼀样，虽然 Hash 索引效率⾼，但是 Hash 索引本⾝由于其特殊性也带来了很多限制和弊端，主要有以下这些。（1）Hash 索引仅仅能满⾜"=","IN"和"<=>"查询，不能使⽤范围查询。由于 Hash 索引⽐较的是进⾏ Hash 运算之后的 Hash 值，所以它只能⽤于等值的过滤，不能⽤于基于范围的过滤，因为经过相应的 Hash算法处理之后的 Hash 值的⼤⼩关系，并不能保证和Hash运算前完全⼀样。（2）Hash 索引⽆法被⽤来避免数据的排序操作。由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，⽽且Hash值的⼤⼩关系并不⼀定和 Hash 运算前的键值完全⼀样，所以数据库⽆法利⽤索引的数据来避免任何排序运算；（3）Hash 索引不能利⽤部分索引键查询。对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再⼀起计算 Hash 值，⽽不是单独计算 Hash 值，所以通过组合索引的前⾯⼀个或⼏个索引键进⾏查询的时候，Hash 索引也⽆法被利⽤。（4）Hash 索引在任何时候都不能避免表扫描。前⾯已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的⾏指针信息存放于⼀个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满⾜某个 Hash 键值的数据的记录条数，也⽆法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进⾏相应的⽐较，并得到相应的结果。（5）Hash 索引遇到⼤量Hash值相等的情况后性能并不⼀定就会⽐B-Tree索引⾼。对于选择性⽐较低的索引键，如果创建 Hash 索引，那么将会存在⼤量记录指针信息存于同⼀个 Hash 值相关联。这样要定位某⼀条记录时就会⾮常⿇烦，会浪费多次表数据的访问，⽽造成整体性能低下2. B-Tree索引B-Tree 索引是 MySQL 数据库中使⽤最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都⽀持 B-Tree 索引。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中B-Tree 索引也同样是作为最主要的索引类型，这主要是因为 B-Tree 索引的存储结构在数据库的数据检 索中有⾮常优异的表现。

⼀般来说， MySQL 中的 B-Tree 索引的物理⽂件⼤多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的Leaf Node ，⽽且到任何⼀个 Leaf Node 的最短路径的长度都是完全相同的，所以我们⼤家都称之为 B-Tree 索引当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放⾃⼰的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使⽤的存储结构实际上是 B+Tree ，也就是在 B-Tree 数据结构的基础上做了很⼩的改造，在每⼀个

Leaf Node 上⾯出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后⼀个 LeafNode 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。

在 Innodb 存储引擎中，存在两种不同形式的索引，⼀种是 Cluster 形式的主键索引（ Primary Key ），另外⼀种则是和其他存储引擎（如MyISAM 存储引擎）存放形式基本相同的普通 B-Tree 索引，这种索引在 Innodb 存储引擎中被称为 Secondary Index 。下⾯我们通过图⽰来针对这两种索引的存放

形式做⼀个⽐较。

忘了引⽤哪位⼤神的博客了，如有侵权，请联系我删除（本博客仅作为⾃⼰学习使⽤）