当前位置: 首页> 汽车> 行情 > 索引小tips

索引小tips

时间:2025/7/13 6:53:55来源:https://blog.csdn.net/2301_76207358/article/details/140934735 浏览次数: 0次

一、优化原则

关于创建索引:

1. 【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁⽌被更新 。

2. 【强制】InnoDB和MyISAM存储引擎表,索引类型必须为 BTREE 。 

3. 【建议】主键的名称以 pk 开头,唯⼀键以 uni 或 uk 开头,普通索引以 idx 开头,⼀律 使⽤⼩写格式,以字段的名称或缩写作为后缀。

4. 【建议】多单词组成的columnname,取前⼏个单词⾸字⺟,加末单词组成column_name。如: sample 表 member_id 上的索引:idx_sample_mid。

5. 【建议】单个表上的索引个数不能超过6个 。

6. 【建议】在建⽴索引时,多考虑建⽴联合索引 ,并把区分度最⾼的字段放在最前⾯。

7. 【建议】在多表 JOIN 的SQL⾥,保证被驱动表的连接列上有索引,这样JOIN 执⾏效率最⾼。 8. 【建议】建表或加索引时,保证表⾥互相不存在冗余索引 。 ⽐如:如果表⾥已经存在key(a,b), 则key(a)为冗余索引,需要删除。

 关于SQL编写:

1. 【强制】程序端SELECT语句必须指定具体字段名称,禁⽌写成 *。

2. 【建议】程序端insert语句指定具体字段名称,不要写成INSERT INTO t1 VALUES( … )。

3. 【建议】除静态表或⼩表(100⾏以内),DML语句必须有WHERE条件,且使⽤索引查找。

4. 【建议】INSERT INTO … VALUES(XX),(XX),(XX).. 这⾥XX的值不要超过5000个。 值过多虽然上线很 快,但会引起主从同步延迟。

5. 【建议】SELECT语句不要使⽤UNION,推荐使⽤UNION ALL,并且UNION⼦句个数限制在5个以内。

6. 【建议】线上环境,多表 JOIN 不要超过5个表。

7. 【建议】减少使⽤ORDER BY,和业务沟通能不排序就不排序,或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的。

8. 【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句,WHERE 条件过滤出来的结果 集请保持在1000⾏以内,否则SQL会很慢。

9. 【建议】对单表的多次alter操作必须合并为⼀次 对于超过100W⾏的⼤表进⾏alter table,必须经过DBA审核,并在业务低峰期执⾏,多个alter需整合在⼀起。 因为alter table会产⽣表锁 ,期间阻塞对于该表的所有写⼊,对于业务可能会产⽣极⼤影响。

10. 【建议】批量操作数据时,需要控制事务处理间隔时间,进⾏必要的sleep。

11. 【建议】事务⾥包含SQL不超过5个。 因为过⻓的事务会导致锁数据较久,MySQL内部缓存、连接消耗过多等问题。

12. 【建议】事务⾥更新语句尽量基于主键或UNIQUE KEY,如UPDATE … WHERE id=XX; 4 否则会产⽣间隙锁,内部扩⼤锁定范围,导致系统性能下降,产⽣死锁。

二、关于索引失效:

1. 最佳左前缀法则

2. 主键插⼊顺序

3. 计算、函数导致索引失效

4. 类型转换导致索引失效

5. 范围条件右边的列索引失效

6. 不等于(!= 或者<>)索引失效

7. is null可以使⽤索引,is not null⽆法使⽤索引

8. like以通配符%开头索引失效

9. OR 前后存在⾮索引的列,索引失效

10. 数据库和表的字符集统⼀使⽤utf8mb

三、为什么不⽤红⿊树⽽是⽤B+tree

MySQL选择使⽤B+树⽽不是红⿊树作为其索引数据结构,主要是出于以下⼏个原因:

1. 磁盘访问效率 :

数据库系统通常需要处理⼤量的数据,这些数据往往存储在磁盘上。磁盘访问的代价远⾼于内存访问,因此优化磁盘访问效率⾄关重要。B+树的设计特别适合磁盘存储, 其节点可以容纳更多的键值对,减少了磁盘I/O次数。

2. 数据组织 :

B+树将所有数据记录存储在叶⼦节点上,⽽⾮叶⼦节点只包含键值和指向其他节点的指针。这种结构使得范围查询和顺序访问更加⾼效,因为数据记录在叶⼦节点上连续存储,便于磁盘的顺序读取。

3. ⾼度平衡 :

B+树的⾼度通常较低,因为每个节点可以包含更多的⼦节点。这意味着从根节点到叶⼦节点的距离较短,查询效率更⾼。红⿊树虽然也是平衡树,但在插⼊和删除操作中可能需要进⾏较多的结构调整,以保持平衡。

4. 空间利⽤率 :

B+树的空间利⽤率更⾼。由于⾮叶⼦节点不存储数据记录,节点的空间可以被更充分地利⽤,减少了空间浪费。

5. 并发控制 :

在数据库系统中,并发访问和锁机制是⾮常重要的。B+树的结构使得它可以更好地⽀持并发操作,尤其是在进⾏范围查询时,可以更容易地实现锁的粒度控制。

综上所述,B+树在磁盘存储、数据组织、查询效率、空间利⽤率和并发控制等⽅⾯都更适合⽤于 数据库索引,因此MySQL选择了B+树作为其索引数据结构。

关键字:索引小tips

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

责任编辑: