MYSQL索引
全文索引，主键索引，唯一索引，覆盖索引，组合索引，普通索引
一、为什么要用索引
使用索引的主要目的是提高数据库查询性能和加快数据检索的速度。以下是一些使用索引的好处：
- 快速数据检索：索引可以将数据按照某种特定的顺序进行排序，并创建一个快速访问路径。当查询时，数据库可以使用索引来快速定位符合查询条件的数据，而不需要全表扫描，从而大大提高查询的效率。
- 减少数据读取量：索引可以帮助数据库引擎减少需要读取的数据量。通过索引，数据库可以仅仅读取索引所需的数据页，而不是整个数据表。这对于大型表和复杂查询非常重要，可以减少磁盘IO和网络传输的开销。
- 加速排序和分组操作：当进行排序或分组操作时，索引可以提供预排序的数据，从而加快排序和分组的速度。数据库可以直接使用索引的排序顺序，而不需要再进行额外的排序操作。
- 提高并发性能：索引可以减少数据的访问冲突，提高并发性能。当多个查询同时进行时，索引可以使数据库引擎更有效地处理并发请求，减少锁竞争和等待时间。
- 强制唯一约束：通过在列上创建唯一索引，可以确保该列的值是唯一的，避免数据重复插入，保持数据的一致性和完整性。
需要注意的是，索引并非没有代价的。索引会占用额外的存储空间，并在插入、更新和删除数据时增加额外的开销。因此，在设计索引时需要权衡索引的数量、列的选择以及索引的维护代价。适当地使用索引可以提高数据库性能，但过多或不正确的索引可能会导致性能下降。
二、索引的缺点
尽管索引在提高数据库性能和查询速度方面有很多好处，但也存在一些缺点和限制。以下是一些常见的索引缺点：
- 占用存储空间：索引需要额外的存储空间来存储索引数据结构和索引列的值。对于大型表和多个索引的数据库，索引可能占据相当大的存储空间。
- 增加写操作的开销：当进行插入、更新或删除操作时，索引需要进行相应的更新。这会增加写操作的开销，尤其是对于包含多个索引的表或频繁更新的表。
- 增加索引维护的成本：随着数据的插入、更新和删除，索引需要进行维护和更新以保持其正确性和有效性。这可能会导致额外的CPU和IO开销。
- 不适用于频繁变更的列：如果某个列的值经常变动，那么相应的索引也需要频繁地进行更新。这可能会导致索引失效，增加维护的成本，并降低性能。
- 增加查询优化的复杂性：当存在多个索引时，数据库优化器需要选择最优的索引来执行查询。这涉及到成本估算和查询优化的复杂性，特别是对于复杂的查询语句。
- 不适用于部分匹配查询：对于某些查询模式，如以通配符开头的LIKE查询，索引可能无法被完全利用，导致性能下降。
- 增加索引的维护时间：随着数据库的增长和索引的增加，索引的维护时间可能会变长。当进行备份、恢复或重建索引时，这可能会导致较长的停机时间或影响系统的可用性。
因此，在设计索引时需要权衡索引的数量、选择合适的列和索引类型，以及平衡查询性能和写操作的开销。不恰当或过多的索引可能会导致性能下降和额外的维护成本。
三、索引介绍
1.0什么是全文索引？
全文索引是一种用于快速搜索和匹配文本内容的索引技术。与传统的普通索引只能精确匹配特定的关键字或短语不同，全文索引可以处理自然语言文本，并支持模糊匹配、语义搜索和相关性排序等功能。
全文索引的主要特点如下：
- 文本内容索引：全文索引会对文本内容进行分词和索引，将文本中的单词、短语等作为索引关键字。这样可以使得搜索引擎能够更高效地对文本内容进行搜索和匹配。
- 模糊匹配：全文索引支持模糊匹配，可以通过搜索关键字的部分匹配或近义词匹配来查找相关的文本内容。这对于处理用户输入的拼写错误、同义词替换和词形变化等情况非常有用。
- 语义搜索：全文索引可以根据文本内容的语义信息进行搜索和匹配。它能够理解词语之间的关系，如同义词、上下文关联等，从而提供更准确和相关的搜索结果。
- 相关性排序：全文索引可以根据搜索关键字与文本内容的相关性来对搜索结果进行排序。它会根据匹配的程度、关键字的位置和频率等因素来评估文本内容的相关性，以便将最相关的结果排在前面。
全文索引通常用于处理大量的文本数据，如文章、新闻、博客、论坛帖子、电子书等。它可以提供快速的搜索和匹配功能，帮助用户快速找到所需的信息。
在数据库中，全文索引可以用于对表中的文本列进行索引和搜索。常见的全文索引技术包括倒排索引（Inverted Index）和向量空间模型（Vector Space Model）等。
创建全文索引的具体语法和实现方式会根据具体的数据库系统和搜索引擎而有所不同。在使用全文索引时，需要注意索引的创建和维护成本，以及索引的查询性能和准确性。
你可以通过以下步骤在 MySQL 中创建全文索引：
- 确定需要创建全文索引的表和列，以及使用的全文索引类型。
- 使用 CREATE TABLE 命令创建表，并指定包含需要全文索引的列的数据类型为 TEXT 或 VARCHAR。
- 使用 ALTER TABLE 命令将全文索引添加到表中的列上。例如：
ALTER TABLE table_name ADD FULLTEXT(column_name);
其中，"table_name" 是表名，“column_name” 是要创建全文索引的列名。
- 在查询时，使用 MATCH AGAINST 子句搜索全文索引。例如：
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_text');
其中，“table_name” 是表名，“column_name” 是包含全文索引的列名，“search_text” 是要搜索的文本。
需要注意的是，MySQL 的全文索引仅支持 InnoDB 和 MyISAM 存储引擎，并且在 InnoDB 引擎中，必须使用 Fulltext 索引类型而不是默认的 Btree 类型。
2 什么是主键索引
主键索引是数据库中一种特殊的索引类型，用于唯一标识表中的每一行数据。主键索引的值必须是唯一的，且不能为空。主键索引可以帮助加快对表中数据的查找速度，并确保数据的唯一性和完整性。
主键索引通常是在一个或多个列上创建的，这些列定义了表中每一行的唯一标识。常见的主键索引是在单个列上创建的，例如一个自增的整数列，但也可以在多个列上创建复合主键索引。
主键索引的主要特点如下：
- 唯一性：主键索引要求每个索引键值在表中是唯一的，这样可以确保表中的每一行数据都有一个唯一的标识。
- 必填性：主键索引的索引键值不能为空，每一行的主键值必须存在。
- 快速查找：主键索引使用高效的数据结构（通常是B树或哈希表），可以快速定位到满足主键条件的数据行。
- 主键约束：主键索引通常与主键约束关联，主键约束是对主键列的一种约束条件，用于确保主键的唯一性和完整性。
创建主键索引的语法通常类似于以下形式：
CREATE TABLE table_name ( column1 datatype PRIMARY KEY, column2 datatype, ... );
其中，table_name 是表的名称，column1 是主键列的名称，datatype 是列的数据类型。
需要注意的是，每个表只能有一个主键索引。主键索引的选择应该是根据业务需求和数据模型来进行的，通常选择唯一且稳定的列作为主键。常见的选择是使用自增的整数列作为主键。
主键索引可以提高数据的查询性能和数据完整性，但在进行大量的插入和更新操作时，也会对性能产生一定的影响。因此，在设计数据库和选择主键时，需要综合考虑查询需求、数据完整性和性能优化。
在 MySQL 中，可以通过以下步骤创建主键索引：
- 确定需要创建主键索引的表和列。
- 使用 CREATE TABLE 命令创建表时，指定要成为主键的列，并使用 PRIMARY KEY 关键字定义主键索引。例如：
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... PRIMARY KEY (column_name) );
其中，“table_name” 是表名，“column_name” 是要成为主键的列名。
- 如果已经创建了表，可以使用 ALTER TABLE 命令添加主键索引。例如：
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
其中，“table_name” 是表名，“column_name” 是要成为主键的列名。
需要注意的是，主键索引具有唯一性和非空约束，因此某个表只能有一个主键索引。如果表中已经存在其他索引或约束，可能需要先删除它们，再创建主键索引。此外，主键索引通常用于加速数据访问和数据关联操作，所以建议选择适合数据模型和查询需求的列作为主键。
3 什么是唯一索引？
唯一索引是数据库中一种用于确保数据列中的值是唯一的索引类型。与主键索引类似，唯一索引也能够快速定位到满足索引条件的数据行，但它允许空值（NULL）的存在。
唯一索引的特点如下：
- 唯一性：唯一索引要求每个索引键值在表中是唯一的，即每个索引键值只能对应一条数据记录。这确保了数据列中的值不会重复。
- 空值允许：唯一索引允许索引列中存在空值（NULL）。空值在唯一索引中被视为独立的值，不与其他非空值冲突。
- 快速查找：唯一索引使用高效的数据结构（如B树或哈希表），能够快速定位到满足索引条件的数据行，提高查询性能。
- 索引约束：唯一索引通常与唯一约束（UNIQUE constraint）关联。唯一约束是对唯一索引列的一种约束条件，用于确保索引列的值的唯一性。
创建唯一索引的语法通常类似于以下形式：
在 MySQL 中，可以通过以下步骤创建唯一索引： 1. 确定需要创建唯一索引的表和列。 2. 使用 CREATE TABLE 命令创建表时，使用 UNIQUE 关键字定义唯一索引。例如： ``` CREATE TABLE table_name ( column1 datatype, column2 datatype, ... UNIQUE (column_name) ); ``` 其中，“table_name” 是表名，“column_name” 是要创建唯一索引的列名。 3. 如果已经创建了表，可以使用 ALTER TABLE 命令添加唯一索引。例如： ``` ALTER TABLE table_name ADD UNIQUE (column_name); ``` 其中，“table_name” 是表名，“column_name” 是要创建唯一索引的列名。 需要注意的是，唯一索引要求列中的值不能重复，因此某个表可能有多个唯一索引。如果尝试插入重复的值，会引发唯一性约束错误。此外，唯一索引可以加速数据关联操作和去重操作，但不同于主键索引，唯一索引的值可以为空。
其中，index_name 是索引的名称，table_name 是表的名称，column1, column2, ... 是要创建唯一索引的列。
需要注意的是，每个表可以有多个唯一索引，但每个唯一索引只能对应一个唯一约束。唯一索引可以用于加速对表中数据的查找，同时确保索引列的值的唯一性。
唯一索引适用于需要确保数据列中值的唯一性的场景，如用户名、邮箱地址等。它可以防止重复数据的插入或更新，并提供快速的唯一性检查和查询功能。
4 什么是覆盖索引？
覆盖索引是一种特殊类型的索引，它包含了查询所需的所有列，从而可以直接提供查询所需的数据，而无需访问表的实际数据行。通过覆盖索引，数据库可以更高效地执行查询操作，减少了对磁盘的访问和数据的读取量，从而提高查询性能。
通常，当执行查询时，数据库需要根据查询条件从磁盘读取表的数据行，并根据索引进行匹配。然后，数据库根据查询需要的列提取数据，并返回给用户。这个过程涉及了磁盘I/O和数据行的解析，可能会产生较大的开销，特别是对于大型表和复杂查询。
覆盖索引通过将查询所需的列包含在索引中，使得数据库可以直接从索引中获取查询所需的数据，而无需访问表的实际数据行。这样可以避免访问磁盘和解析数据行的开销，提高查询的执行速度。
使用覆盖索引可以带来以下好处：
- 减少磁盘I/O：覆盖索引避免了对表数据行的读取，减少了磁盘I/O操作的数量，从而提高了查询的性能。
- 减少数据解析：由于不需要解析表的数据行，覆盖索引可以减少对数据的解析开销，进一步提高查询的执行速度。
- 减少内存消耗：覆盖索引通常比完整的数据行更小，因为它只包含索引列和查询所需的列。这可以减少内存消耗，提高缓存效率。
要创建覆盖索引，需要选择适当的索引列，包括查询条件中的列以及需要返回的列。覆盖索引可以在查询中显式地指定，或者由数据库查询优化器自动选择使用。
需要注意的是，覆盖索引适用于查询中只涉及到索引列和覆盖列的情况。如果查询需要访问表的其他列，覆盖索引将无法满足需求。此外，覆盖索引可能增加了索引的存储空间和维护成本，因为它需要包含更多的列数据。因此，在创建覆盖索引时需要权衡查询性能和索引维护成本之间的关系。
5 什么是组合索引？
组合索引（Composite Index）是一种包含多个列的索引，用于提高多列查询的性能。与单列索引只针对单个列进行索引不同，组合索引可以同时索引多个列的组合，使得数据库可以更有效地执行涉及这些列的查询操作。
组合索引的特点如下：
- 多列索引：组合索引包含多个列，这些列按照指定的顺序形成索引。可以根据查询的需要选择合适的列组合进行索引。
- 聚集性：组合索引的列顺序对查询的效果有影响。如果查询中的列顺序与组合索引的列顺序完全匹配，称为覆盖索引，可以直接从索引中获取查询结果，提高查询性能。如果查询中的列顺序只部分匹配组合索引的列顺序，仍可以利用索引的前缀部分进行查询优化。
- 查询优化：组合索引可以提供更精确的索引选择，减少不必要的索引扫描。它可以有效地支持涉及多个列的查询条件，并提供更好的查询性能。
- 索引顺序：组合索引的列顺序很重要。在查询中，如果只使用索引的前缀部分进行匹配，那么组合索引的列顺序将决定索引的选择性和效果。
创建组合索引的语法通常类似于以下形式：
CREATE INDEX index_name ON table_name (column1, column2, ...);
其中，index_name 是索引的名称，table_name 是表的名称，column1, column2, ... 是要创建组合索引的列。
需要注意的是，组合索引可以包含多个列，但列的顺序很重要。在查询中，如果只使用索引的前缀部分进行匹配，那么组合索引的列顺序将决定索引的选择性和效果。
组合索引适用于需要同时考虑多个列的查询条件的情况。通过合理选择组合索引的列和顺序，可以提高查询的性能，并减少不必要的索引扫描和数据访问。
6 什么是普通索引
普通索引（或称为非唯一索引）是数据库中最常见的索引类型之一。它用于加快对表中数据的查询速度，提高数据库的性能。普通索引允许多个行具有相同的索引键值，不要求索引键值是唯一的。
普通索引可以在一个或多个列上创建，以便在查询中快速定位到满足特定条件的数据行。当使用普通索引进行查询时，数据库引擎会使用索引的数据结构（通常是B树或哈希表）来快速定位到满足查询条件的索引条目，然后再通过索引中的指针或引用回到原始表中获取相应的数据行。
普通索引适用于频繁被查询的列或经常用于连接操作的列。它可以加速等值查询、范围查询和排序操作。但是，当对索引列进行更新（插入、更新、删除）时，数据库引擎需要维护索引的一致性，可能会导致额外的性能开销。
需要注意的是，对于频繁进行插入、更新和删除操作的表，过多的普通索引可能会影响性能。在设计数据库和选择索引时，需要综合考虑查询的需求、表的更新频率以及数据库的整体性能优化。
在 MySQL 中，可以通过以下步骤创建普通索引：
- 确定需要创建普通索引的表和列。
- 使用 CREATE INDEX 命令创建索引。例如：
CREATE INDEX index_name ON table_name (column_name);
其中，“index_name” 是索引名称，“table_name” 是表名，“column_name” 是要创建索引的列名。
- 如果已经创建了表，可以使用 ALTER TABLE 命令添加普通索引。例如：
ALTER TABLE table_name ADD INDEX index_name (column_name);
其中，“table_name” 是表名，“index_name” 是索引名称，“column_name” 是要创建索引的列名。
需要注意的是，普通索引可以用于加速数据访问和查询操作。可以在单个列上创建普通索引，也可以在多个列上创建复合索引。在选择创建普通索引时，应考虑查询的频率和性能需求，选择适合的列作为索引。同时，索引会占用一定的存储空间并导致写操作的开销，因此在创建索引时需要权衡好查询性能和存储开销之间的平衡。
四、什么是回表查询
- 回表查询是指在使用非聚集索引进行查询时，需要通过索引定位到对应的数据行后，再回到原始表中获取其他列的值。当查询条件涉及到非索引列时，数据库引擎无法直接从索引中获取所需的数据，就需要进行回表查询。
在回表查询中，数据库引擎首先使用索引定位到满足查询条件的索引条目，然后通过索引中的指针或引用，回到原始表中找到对应的数据行。这个过程涉及额外的IO操作，可能会对查询性能产生一定的影响。
回表查询通常发生在以下情况下：
- 当查询条件中包含非索引列的筛选条件时，需要回到原始表中检查这些条件。
- 当查询结果需要获取非索引列的值时，需要回到原始表中获取这些列的数据。
回表查询的性能影响取决于多个因素，包括表的大小、索引的选择性、磁盘IO性能等。在某些情况下，回表查询可能会导致额外的IO开销，降低查询性能。为了减少回表查询的次数，可以考虑使用覆盖索引，即包含查询所需的所有列的索引，以避免回表操作。
需要注意的是，回表查询并不一定是不好的，它是在需要获取非索引列数据时的一种必要操作。在设计数据库和查询时，需要权衡索引的选择和查询需求，以获得最佳的性能和效率。
五、索引失效原因
理论实践
1 创建了组合索引分别是a_b_c ,在使用组合索引查询的时候 没有查询最左边的，就会导致索引失效
2 select * from 是走了全表扫描，没有用到任何索引，查询效率比较低，会导致索引失效
3 select * from user where id=1+1=2; 索引列上有计算也会导致索引失效
4 explain select * from user where SUBSTR(height,1,2)=17; 索引列上有函数也会导致索引失效
5 select * from user where code=101; 字段类型不同也会导致索引失效
6 select * from user where code like '%1'; 模糊匹配 左边包含%也会导致索引失效
7 select * from user where id=height 列对比也会导致索引失效，原因是 id是主键索引，而 height 创建了普通索引，因此两个两个字段进行比较会索引失效
8 select * from user where id=1 or height='175' or address='成都'; 使用or 也会索引失效，原因是前两个字段都有索引，而最后一个没有索引就会索引失效，必须保证要嘛都有索引，要嘛都没有索引
9 select * from user where code='101' order by code, name; order by 也会导致索引失效，原因是 必须要保证有where或limit 才不会索引失效
MySQL索引失效
- 不使用索引列进行查询：当查询条件中没有使用索引列，或者使用了函数、表达式或类型转换等操作，MySQL无法使用索引来加速查询，导致索引失效。
- 数据类型不匹配：当查询条件中的数据类型与索引列的数据类型不匹配时，MySQL可能无法使用索引。例如，将字符串与数字列进行比较，或者使用字符串函数对索引列进行操作。
- 数据量过小：当表中的数据量很小时，MySQL可能会选择全表扫描而不是使用索引，因为全表扫描的开销更小。
- LIKE查询以通配符开头：当使用LIKE查询时，如果通配符（如%）出现在查询模式的开头，索引可能无法被利用。例如，
WHERE column LIKE '%abc'。
- 多列索引顺序不正确：当使用多列索引时，索引的列顺序非常重要。如果查询条件中的列顺序与索引列的顺序不一致，索引可能无法被利用。
- 索引统计信息不准确：MySQL使用统计信息来选择最优的查询计划。如果统计信息不准确或过期，MySQL可能会做出错误的决策，导致索引失效。
- 索引列上存在函数或表达式：当在索引列上应用函数或表达式时，MySQL无法使用索引。例如，
WHERE YEAR(date_column) = 2021。
- 数据分布不均匀：当索引列的数据分布不均匀时，MySQL可能会选择不使用索引，而是进行全表扫描。
- 索引被强制类型转换：当查询条件中的值与索引列的数据类型不匹配时，MySQL可能会进行隐式或显式的类型转换，导致索引失效。
以上只是一些常见的索引失效原因，具体情况可能因数据库设计、查询语句和数据分布等因素而异。对于索引失效的问题，可以通过分析查询执行计划、优化查询语句、更新统计信息、重新设计索引等方法来解决。
