mysql索引

建索引注意事项

去my.cnf里配置三个配置

打开慢查询日志
slow_query_log=1
慢查询日志存储路径
slow_query_log_file=/var/log/mysql/log-slow-queries.log
SQL执行时间大于3秒，则记录日志
long_query_time=3

业务SQL十分复杂，没法优化。参照如下规则建立索引
(1)索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响insert,delete,update等语句的性能

(2)避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引

(3)数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果

(4)在条件表达式中经常用到不同值较多的列上创建索引，在不同值很少的列上不要建立索引。比如性别字段只有“男”“女”俩个值，就无需建立索引。如果建立了索引不但不会提升效率，反而严重减低数据的更新速度

(5)在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引。

索引的分类

从物理存储角度:
聚集索引和非聚集索引
聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。
从数据结构角度:
B+树索引、hash索引、FULLTEXT索引、R-Tree索引
从逻辑角度:

主键索引：主键索引是一种特殊的唯一索引，不允许有空值
普通索引或者单列索引
多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
唯一索引或者非唯一索引
空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

如何避免回表查询?什么是索引覆盖?

当能通过读取索引就可以得到想要的数据，那就不需要回表读取行了。
一个索引包含了（或覆盖了）满足查询结果的数据就叫做索引覆盖。
回表查询
例如此时有一张表table1,有一个联合索引(a,b)

select a,b from table1

在索引上就能找到结果，就不用回表去查询！

select a,b,c from table2

c列在索引上不存在，就需要回表查询。
覆盖索引
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B+ tree索引做覆盖索引。

某列所有数据唯一的，选唯一索引还是普通索引？

答唯一索引！
【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

在进行非聚簇索引的插入时，InnoDB 引入了 Change Buffer，Insert Buffer。可以对 insert、delete、update 都进行缓存。然后再以一定频率和情况进行缓存和原数据页合并(merge)操作。将多个插入合并到一个操作中，就大大提高了非聚簇索引的插入性能。

唯一速度的插入比普通索引慢的原因就是:
唯一索引无法利用Change Buffer。因为唯一索引为了保证唯一性，需要将数据页加载进内存才能判断是否违反唯一性约束。
普通索引可以利用Change Buffer
唯一索引的搜索速度比普通索引快的原因就是:
普通索引在找到满足条件的第一条记录后，还需要判断下一条记录，直到第一个不满足条件的记录出现。
唯一索引在找到满足条件的第一条记录后，直接返回，不用判断下一条记录了。

5、mysql索引是什么结构的？用红黑树可以么?

在MyISAM引擎中的实现（二级索引也是这样实现的）

在InnoDB中的实现

B-tree：树内存储数据，叶子节点上无链表，这样导致在非叶子节点中能保存的指针数量变少，指针少的情况下要保存大量数据，只能增加树的高度,导致IO操作多，查询性能低；
Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
AVL树和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据数据存储的时候，显然不能将全部数据全部加载进内存，树的高度随着数据量增加而增加，IO代价高
B+ tree的两个明显特点
数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。
总结
1、MySQL使用B+Tree作为索引数据结构。
2、B+Tree在新增数据时，会根据索引指定列的值对旧的B+Tree做调整。
4、从物理存储结构上说，B-Tree和B+Tree都以页(4K)来划分节点的大小，但是由于B+Tree中中间节点不存储数据，因此B+Tree能够在同样大小的节点中，存储更多的key，提高查找效率。
5、影响MySQL查找性能的主要还是磁盘IO次数，大部分是磁头移动到指定磁道的时间花费。
6、MyISAM存储引擎下索引和数据存储是分离的，InnoDB索引和数据存储在一起。
7、InnoDB存储引擎下索引的实现，(辅助索引)全部是依赖于主索引建立的(辅助索引中叶子结点存储的并不是数据的地址，还是主索引的值，因此，所有依赖于辅助索引的都是先根据辅助索引查到主索引，再根据主索引查数据的地址)。
8、由于InnoDB索引的特性，因此如果主索引不是自增的(id作主键)，那么每次插入新的数据，都很可能对B+Tree的主索引进行重整，影响性能。因此，尽量以自增id作为InnoDB的主索引。

多个单索引，查询多个条件时如何走索引的

多个单列索引在多条件查询时只会生效一个索引。优化器如何抉择索引的。Mysql在优化器中有一个优化器称为Range 优化器，负责进行范围查询的优化！那么该优化器计算执行成本，Cardinality值越大，就意味着，使用索引能排除越多的数据，执行也更为高效。

联合索引

当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引
联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。

参考：https://blog.csdn.net/fujiandiyi008/article/details/102617916?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task