MySQL索引的分类、何时使用、何时不使用、何时失效？

指标嘛，分数一目了然。
MySQL有五种类型：普通、唯一、主键、组合和全文。
每种用途都不一样，要根据情况而定。

普通索引是最常见的，你可以添加任何内容，创建也很简单。
在表（列）上创建 INDEX index_name 就足够了。
或者您可以在创建表时直接INDEX index_name(column)。

唯一索引必须是唯一的，并且值必须不同，空值除外。
创建方法和普通索引类似，只是添加UNIQUE即可。
在表（列）上创建单个索引索引名。

主键索引比较特殊，其值不能为空，而且一张表中只能有一个。
创建表时使用PRIMARY KEY(column)来指定。
这个东西默认是有索引的。

组合索引是多个字段在一起的索引。
例如，ALTER TABLE ADD INDEX index_name(a,b,c)。
使用时需要遵循最左边前缀的原则，按照从左到右的顺序进行搜索。

全文索引专用于文本搜索。
匹配……对抗这个东西。
仅适用于 CHAR、VARCHAR 和 TEXT 类型。
当数据量较大时，先插入数据再建索引效率更高。

什么时候使用索引？主键和唯一字段默认可用。
当您将字段链接到表（例如 JOIN）时，添加索引会更快。
在 WHERE 子句中使用 >、>=、=、<、<=、IS NULL 和 BETWEEN 等条件的字段可以更有效地建立索引。
LIKE查询时，如果通配符前面是%China，索引就会失效。
如果具体字符在 China% 前面的话就可以了。
聚合函数MIN()、MAX()，这些字段，字段的排序分组ORDER BY、GROUP BY，也可以建立索引。

什么时候不应该使用它？当表记录很少时，例如几百条，全表扫描可能比索引更快。
如果数据特别重复，比如性别字段只有男性和女性，则不需要索引。
如果表不断变化，索引在插入、删除或修改时会变慢，具体取决于读写比率。
大型二进制或文本字段（例如 TEXT 和 IMAGE）通常不会建立索引，因为数据太大。
如果前几个字符是唯一的，您可以创建前缀索引。
有时候全表扫描比较快，MySQL自己可以判断，索引没啥用。

指数什么时候到期？组合索引不会根据最左边的前缀进行搜索。
例如，如果索引是(A,B)，并且您搜索WHERE B=b，则该索引将无用。
LIKE查询不遵循最左边的前缀WHERE A LIKE '%China'，索引没有用。
索引之间的排序，WHERE A=a ORDER BY B，只能使用A的索引，因为一次只能使用一个索引。
OR条件不正确，检查WHERE A=a1 OR A=a2 即可使用索引。
如果你检查WHERE A=a OR B=b，索引将毫无用处。
如果字符串类型字段不加引号，WHERE A=China，则会自动转换类型并禁用索引。
如果索引列中使用了UPPER()、!=、NOT IN等函数，索引也会失效。

如何知道索引是否有用？使用 EXPLAIN 语句进行检查。
type字段视情况而定：ALL是对表进行全扫描，不带索引。
使用非唯一索引 e 扫描 ref一一检查。
range 是索引的范围扫描，例如 BETWEEN。
eq_ref 使用唯一索引来匹配查询。
const/system 是主键或唯一索引查找，并被直接视为常量。
null 不检查任何内容并直接提供结果。

关键字段取决于实际使用的索引。
如果不使用，则无效。

mysql如何优化like模糊查询

哦，我来说说我当时陷入的陷阱，比如MySQL模糊查询。

2 004 年，我刚拿到一个旅游网站的数据库，用户表有几十万条记录。
用户总是搜索“去海南”并使用LIKE“%海南%”。
你猜怎么着？每次我检查时，数据库都会崩溃，CPU 峰值会达到 2 00%。
看一下壁纸并扫描整个板！那时候的数据没有现在这么大，但是也足够了。

后来我向老板请教，他说，你的文笔不好。
您需要将其更改为 LIKE 'Hainan%' 并为 city 字段创建索引。
我本来很疑惑，但是改了之后，哦！查询立即快了 N 倍。
后来才知道B+树索引是预读的，最后还有%，不知道从哪里开始看。
只能一一比较，何不扫描全表。

有一个很深的坑，就是LIKE '%keyword%'。
有一次，我帮助一家小公司进行优化。
他们用它来检查用户名。
该列表只有数万条条目，但它被卡住了。
检查了半天，发现username字段没有索引。
我尝试了一下并建立了索引，但仍然很慢。
老板说，尝试为用户名的第一个单词单独建立一个索引，比如INDEX(username(5 ))。
嘿，这行得通！事实证明，指数越大越好。
如果太长，既不会节省多少空间，还会影响查询的速度。

而且，对于那些很长的字段，比如TEXT、LONGTEXT，执行模糊查询会比较困难。
2 008 年，我正在开发一个论坛系统。
用户使用 LIKE '%content of a给定帖子%' 来检查帖子。
记录有几十万条，系统崩溃了。
检查日志发现索引也创建了但是还是全表扫描。
后来无奈，只好添加一个新的keyword字段，从帖子中提取一些关键词，保存下来，然后为这个字段建立索引。
或者只使用全文索引，例如 ALTER TABLE posts ADD FULLTEXT (content);然后使用 MATCH(content) AGAINST('keyword') 进行搜索。
这东西的分词还是挺聪明的，比LIKE好多了。

最神奇的事情是什么？它是一个覆盖索引。
有一次，我接手了一个有数百万表记录的老项目。
用户搜索名称字段并使用 LIKE '张%'。
我看了一下，发现name字段是有索引的，但是每次查找都得回表获取整行数据。
后来，我建议将其重写为 SELECT name FROM users WHERE name LIKE 'Zhang%' 并仅检查名称字段。
这次直接从索引取数据，不用回表，速度快很多。
这是一个覆盖索引。
一切都包含在索引中。
查找时直接使用索引数据，无需查表。

现在数据量更大了，几千万、几亿条，这些原生MySQL方法就有点吃不消了。
2 01 4 年我接手了一个电子商务项目，有数千万的产品列表。
用户使用LIKE来检查产品名称，但是还是很慢。
最后没办法，只能去Elasticsearch同步产品数据。
用它来搜索、分词、高亮、相关性排序都非常有趣。
使用Logstash同步数据，每天运行一个定时任务，还是比较靠谱的。

总而言之，在提出 LIKE 问题时，如果可以使用前缀，就不要使用后缀。
必要时构建索引，尤其是前缀索引。
对于长字段，全文索引是个好东西，或者直接上Elasticsearch。
不要弄乱那些 SELECT，添加任何您想要检查的字段，索引覆盖率会很高，速度也会很快。
当数据量达到一定程度时，就应该考虑外部工具了。