MySQL索引(三)

MySQL索引(三)

为什么建索引？

1.索引大大减少了MySQL需要扫描的数据量

如果没有建立索引，查数据的时间复杂度是O(n)，查询效率低下；建立索引后，B+树是个多叉树，搜索的时间复杂度是O(logN)，提高查询效率

2.索引帮助MySQL避免外部排序和使用临时表

3.将随机IO转变为顺序IO

索引是把数据顺序存放，这样可能相邻的数据会存储到一个连续的磁盘空间，这样就有可能是顺序IO了

怎么建立索引

适用于索引：

1.字段具有唯一性限制的，比如商品编码，id

2.经常使用==WHERE条件==的字段，如果是多个就创建联合索引

3.经常用于==GROUP BY和ORDER BY==的字段，这样在查询的时候就不用去做一次排序了，因为建立索引后在B+树中的记录是排序好的

不适应于索引的：

1.WHERE和GROUP BY、ORDER BY中用不到的字段，不起到定位作用的字段不需要建立索引，因为索引也消耗物理空间

2.字段中大量重复数据，比如性别字段。因为MySQL中的查询优化器发现某个值在该字段占比很高，就会忽略索引，进行全表扫描

3.经常更新的字段不用创建索引，因为索引字段频繁更新，需要去维护B+树的有序性，频繁的重建索引会影响数据库性能

为什么不是说索引越多越好

• 空间消耗：索引占用物理空间，数量越大，占据空间越大

多一个索引，就多一个B+树索引，表数据量越大，索引占用空间越大

• 时间消耗：降低表的增删改查的效率，每次增删改查，B+树都需要维护索引有序性，进行动态维护

什么时候不用索引更好

建立索引就会有时间和空间上的消耗，见上文。

1.如果一个表经常被增删改的话，也就是读多写少场景下，不建立索引更好，因为维护索引的开销可能大于索引提升的性能

2.如果某个字段中的值高度重复，建立索引也没有用，查询优化器会选择全表扫描，这样建立的索引既占空间，也影响增删改的效率

索引怎么优化

• 覆盖索引优化：

比如商品名和商品价格：select pro_id,pro_name,pro_price from table where pro_name="apple;"，把商品名和商品价格建立一个联合索引，这使用使用上面的sql语句就利用覆盖索引优化了，因为索引中已经包含这两个字段的数据了，所以查询不会再次去查询主键索引，避免了回表，减少大量IO操作

• 主键索引最好是自增的

如果是随机值，会造成页分裂，页分裂会导致大量内存碎片，使索引结构不紧凑，影响查询效率

• 避免索引失效

1.使用左或左右模糊匹配会造成索引失效，like %xx 或like %xx%

2.当我们在查询条件中对索引列进行计算、函数、类型转换操作时都会造成索引失效

3.联合索引必须遵守最左匹配原则

4.WHERE字句中，如果OR前的条件列是索引列，OR后的条件列不是索引列，也会造成索引失效

• 前缀查询优化

对于一些大字符串的索引，我们可以考虑使用前缀索引只对索引列的前缀部分建立索引，节省索引的存储空间

索引失效

如果用varchar定义一个日期字段，并且其中有个数据"20120101"，如果这个time字段有索引，并且查询时的条件是where time=20120101不加单引号，会不会命中索引？

答案是不会。

因为MySQL在遇到字符串和数字比较时，会把字符串先转化为数字，然后进行比较。也就是对字符串执行一个CAST函数。

比如：

select* from table_name where time=20120101

实际上执行的是select* from table_name where CAST(time AS signed int)=20120101

索引字段进行了函数计算就会发生索引失效。

反过来，如果一个id字段是整型类型，查询的id=“1”，就不会发生索引失效

select* from table_name where id="1"

实际上执行的是select* from table_name where id=CAST("1 AS signed int")

索引字段并没有发生函数计算，所以不会索引失效

如何解决索引失效

MySQL8.0：函数索引和索引跳跃扫描机制

• 函数索引

针对函数计算后的值建立一个索引，也就是该索引的值是函数计算后的值，这样就可以通过扫描索引来查询数据

比如：针对length(name)的计算结果建立一个索引

alter table t_user add key idx_name_length((length(name)))
explain select * from t_user where length(name)=6;

这时候就走索引了

• 索引跳跃扫描机制

之前使用联合索引时，如果不满足最左匹配原则会造成索引失效，8.0有了索引跳跃扫描机制之后，即使没有遵循最左匹配原则，任然可以使用联合索引