索引相关问题

索引的优点

1、加快查询速度
2、使用唯一性索引或主键索引，可以保证数据的微唯一性，是解决接口幂等的方法之一
3、在使用分组和排序子句进行数据搜索时，可以减少分组和排序时间
4、索引可以将随机IO变成顺序IO，减少IO操作，减少对存储设备的请求，提高系统的响应速度。
5、增强数据安全性：索引不仅可以提高数据查询效率，还可以通过在某些列上创建索引来保证数据安全性，防止数据泄露等安全问题。

索引的缺点

1、创建索引和维护索引需要耗费时间和空间
2、对表中数据进行增删改时，索引也要动态维护
3、对于数据量比较小的表，大部分情况下简单的全表扫描更高效

建议创建索引的列

1、频繁作为查询条件的列：当查询语句中经常使用的列，如WHERE、JOIN和ORDER BY等查询条件中的列，建议为该列创建索引，能够有效地提高查询性能。
2、主键列：主键列是用于唯一标识表中每一行数据的列，在创建主键时，会隐式地为该列创建一个索引。
3、外键列：外键列用于引入另一个表中的主键，可以有效地提高关联查询的速度。
4、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
5、经常需要排序的列：当需要进行ORDER BY或GROUP BY排序操作时，为排序列创建索引，能够有效地提高排序和分组查询的性能。
6、包含大量重复数据的列：当列中数据重复度较高时，可以为该列创建索引，可以大大减少数据库扫描的数据量，提高查询速度。

不建议创建索引的列

1、查询少的列
2、只有很少数据或重复数据多的列
3、定义为text、image、bit数据类型的列
4、当改列的修改性能要求高于检索要求时，不建议创建索引，因为修改性能和检索性能是互相矛盾的

索引失效的原因

1、like以“%”开头
2、不能在索引上使用计算、函数、类型转换等操作
3、使用 OR 连接符：如果查询条件中存在用 OR 连接的多个条件，且这些条件不都是索引列，可能导致索引失效
4、复合索引不能使用不等于（！=、<>）或 is null、is not null,否则自身和右侧索引全部失效
5、在查询时使用NOT操作符会导致索引失效。
6、数据量太少：如果一张表只有几条记录，即使不使用索引，数据库的查询速度仍然非常快，此时使用索引不一定能提高数据库查询速度。
7、索引列类型不匹配：当查询条件与索引列的类型不匹配时，可能导致类型隐式转换，从而使索引失效。

索引结构

mysql中常用的索引结构有：b树，b+树，HASH

B+树为什么比B树更适合索引

1、磁盘读写特性：
B+树的内部节点只存储键信息，不存储数据，这样每个节点可以存储更多的键，树的高度相对比B树小，在进行磁盘I/O时，需要的次数相对要少。减少I/O次数可以显著提高查询性能。
2、数据存储容量：
B+树的内部节点只存储索引键，不存储实际数据，因此可以存储更多的索引键，从而提高了索引的容量。这意味着对于相同的数据集，B+树可能比B树层级更少，进一步减少了查询时需要的磁盘I/O次数。
3、范围查询效率：
B+树的叶子节点通过指针相连，形成一个有序链表。这种结构对于范围查询非常高效，因为一旦找到范围的起始点，就可以通过叶子节点的链表顺序访问，获取范围内的所有数据，而无需回到树的上级节点。相比之下，B树在范围查询时需要不断地在树中上下移动，效率较低。
4、插入和删除操作的稳定性：
B+树在插入和删除数据时，由于叶子节点包含所有数据，并且叶子节点之间有指针相连，这些操作对树结构的影响较小。相比之下，B树在插入和删除时可能需要进行更多的节点分裂和合并操作，这可能导致树的平衡性被破坏，需要重新平衡树结构，从而影响性能。

sql调优

1、创建索引
2、避免在索引上使用计算
3、使用预编译查询
4、使用表别名
5、使用临时表暂存中间结果，避免多次扫描主表，也减少阻塞
6、避免在where中使用null值判断，否则会索引失效导致全局扫描
7、调整where语句中的连接顺序

EXPLAIN列的解释

table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 indexhe和ALL
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢