mysql索引失效&&索引创建原则

最新推荐文章于 2023-06-30 17:38:58 发布

尼牟

最新推荐文章于 2023-06-30 17:38:58 发布

阅读量1k

点赞数

分类专栏： mysql 文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/WbX7342188/article/details/127305320

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、mysql索引失效：

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2、or语句前后没有同时使用索引。

当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。

3、组合索引，不是使用第一列索引，索引失效。

违背了最佳左前缀原则。

4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5、在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效。

由于版本问题，和索引字段可为null.所以并不一定100%索引失效。

由于B+树不对null做索引创建，所以，我们要想办法避免出现null。比如：给字段设置default默认值、或者将字段设计为非空。

6、在索引字段上使用not，<>，!=。

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。

7、对索引字段进行计算操作（加减乘除）、字段上使用函数。（索引为 emp(ename,empno,sal)）

二、创建索引原则：

1.字段的数值具备唯一性的特性，此时即使它是组合字段，也必须建立唯一索引。

2.频繁作为where查询条件的字段。

3.经常`group by`和`order by`的列

# 添加联合索引
ALTER table student_info add INDEX idx_sid_cre_time(student_id,create_time desc);
# 添加联合索引之后再查询，时间缩短到0.5s
SELECT student_id, count(*) AS num 
FROM student_info 
GROUP BY student_id 
ORDER BY create_time desc LIMIT 10;

注意：联合索引中，根据最左匹配原则，要将已经具有索引的student_id放到前面。

4.`Update`、`Delete`操作中，`where`条件中的列，也可以添加索引。

如果进行更新的时候，更新的字段是非索引字段，那么此时提升的效率会更明显，因为非索引字段更新的时候不需要对索引进行维护。

5.`Distinct`字段需要创建索引。

6.多表Join连接操作的时候创建索引注意事项。

select xxx from A,B 
on A.id = B.id 
where A.name = 'xxx';

注意点：

连接表的数量尽量不要超过3张，因为每增加一张表，相当于增加一次嵌套循环，数量级增长快。
对where条件创建索引。因为where语句才是对数据条件进行过滤。
最后。对于连接的字段创建索引。同时该字段在多张表中的类型必须一致。

7.使用列的类型小的创建索引。

类型大小指的是该类型表示的数据范围大小。以整数类型为例，有tinyint，mediumint，int，bigint。它们占用的存储空间依次递增。此时我们应该从小的类型开始去创建索引。

原因：

数据类型越小，在查询时进行的比较操作越快。数据类型越小，索引占用的存储空间就越少，在一个数据页中就可以存储更多的记录，从而减少磁盘IO带来的性能损耗。

8.使用字符串前缀创建索引。

前缀索引：当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

背景：表中某个列的字符串长度很长。

这种情况下带来的问题：

B+树索引中的记录，也就是叶子节点上会保存该数据的完整信息，这个保存的过程耗费的时间长。同时字符串越长，占据的存储空间越大。字符串越长，做字符串比较的时候会占用更多的时间。为了解决这种情况，最好通过截取字符串的方式，将截取部分作为索引，也就是建立前缀索引。不仅能节约空间，还能减少字符串的比较时间。同时Alibaba开发手册中建议：

在varchar字段上建立索引的时候，必须指定索引的长度，没必要对全字段建立索引。

例如
```
create table shop(address varchar(120) not null);
# 取address字段的前12位
alter table shop add index(address(12));
```
一般长度为20的索引，区分度就高达90%以上了。

区分度计算公式：

count(distinct left(列名, 索引长度))/count(*)