MySQL索引优化

1.索引的类型
MySQL中的索引是在存储引擎层实现的。不同的存储引擎中支持的索引类型不同。即使是同一类型的索引，在不同的存储引擎中的实现方式或者存储方式也不尽相同。

主要的索引的类型有：
B-Tree：最常见的索引类型，支持大部分的存储引擎。
B+Tree：在B-Tree索引的基础上进行优化的结果，在MySQL中大部分存储引擎会支持B+Tree索引。如果没有为数据库或者数据表显式地指定索引的类型。则MySQL底层默认会使用B+tree索引。
Hash：比较适合存储Key-Value型数据。查询Key-Value型数据库时，会根据Key快速获取数据。但是hash索引有一个弊端，即不适合根据某个数据的范围来查询数据。
R-Tree：空间索引，对于地理空间，类型的数据来说，通常会使用R-Tree索引。
Full+Text：主要用于全文索引。在MySQL5.6版本之前，Full-Text索引只支持MyISAM存储引擎。从MySQL5.6版本开始，InnerDB存储引擎开始支持Full-Text索引。

注意：在MySQL8.x中新增了隐藏索引，降序索引和函数索引。

2.使用索引的场景
a.全值匹配
全值匹配是指在MySQL的查询条件中包含索引中的所有列，并且针对索引中的每列进行行等值判断。
实例：
explain select * from goods where id =10 \G

b.查询范围
MySQL支持对索引的值进行范围的查找。
实例：
explain select * from goods where id >=10 and id<=20 \G;

c.匹配最左前缀
MySQL在使用联合索引查询数据的时候从联合索引中的最左边的列开始查询，并且不能挑过索引中的列。如果跳过索引中的列查询数据，则在后续的查询中将不在使用索引。

d.查询索引列
MySQL在查询包含索引的列或者查询的列都在索引中的时候，查询的效率比使用select * 或者查询没有索引的列的效率要高的很多。

e.匹配字段的前缀

如果数据表中的字段存储的数据比较长，则在整个字段上添加索引会影响数据的写入性能，增加MySQL维护索引的负担。此时，可以在字段的开头部分添加索引，并按照此索引进行数据的查询。
实例：在数据表tab_01的字段t_name字段上前10个字符上添加索引并查询数据。

f.精准与范围匹配索引
在查询数据的时候，可以同时精确匹配索引并按照另一个索引的范围进行数的查询。

g.匹配null
在MySQL中，对一个添加了索引的字段判断是否为null时会使用索引进行查询。

h.使用join连接语句查询多个数据表中的数据，并且实现join连接的字段上添加了索引时，MySQL会使用索引查询数据。

i.like匹配索引
当like语句中的查询条件不以通配符开始的时候，MySQL会使用索引查询数据。
当使用以通配符开始的like语句查询数据时，MySQL不会使用索引。

3.无法使用索引的场景
a.以通配符开始的like语句
当使用以通配符开始的like语句查询数据时，MySQL不会使用索引。

b.数据类型的转换
当查询的字段的数据进行了数据类型的转换的时候，MySQL不会使用索引查询数据。例如，按照字符串类型的字段查询数据的时候，匹配的条件的值没有带引号。

c.联合索引没有匹配最左列
当数据表中创建了联合索引，如果在查询数据时，查询条件不包含联合索引中最左边的列或者最左边的列的开始部分，即不满足最左前缀匹配的规则，那么MySQL不会使用索引。

d.or语句
查询语句中使用or来连接多个查询条件的时候，只要查询条件中存在未创建索引的字段，mysql就不会使用索引。

e.计算索引值
查询数据时对查询条件的字段添加了索引，而且在查询数据时对字段进行了计算或者使用了函数，此时，MySQL不会使用索引。

f.范围条件右侧的列无法使用索引

使用联合所以查询数据时，如果按照联合索引中字段的某个范围查询数据，则此字段后面的列无法使用索引，会进行全表扫描。

g.使用<>或者!=操作符匹配查询条件

在MySQL中，使用<>或者时!=操作符匹配查询条件时不会使用索引。

h.匹配not null值
在MySQL中使用IS NULL判断某个字段是否为NULL时，会使用该字段的索引。相反，如果使用NOT NULL来验证某个字段不为NULL时，会进行全表扫描操作。

4.使用索引提示
a.使用索引
use index 紧跟在查询语句中的数据表名的后面。使用use index能够提示查询优化器使用指定的索引，从而不再评估是否使用其他索引。

explain select * from goods use index(t_name,t_price)
->where t_price=100 or t_name='女装' \G

b.忽略索引
使用ignore index 可以提示查询优化器在进行数据查询操作的时候，忽略某个或者某些索引

explain select * from goods ignore index(t_name)
->where t_name=‘女装’ \G

c.强制使用索引

MySQL支持在查询数据时强制使用某个索引来检索数据，此时可以使用force index实现。

explain select * from goods froce index(t_name)
->where t_name=‘女装’ \G