• 数据库索引和索引优化


    一、索引

    1.什么是索引

    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。简而言之,数据库索引是排好序的数据结构,为了提高数据的查询效率。

    在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

    MySQL常用的索引就是B+树。

    2.索引的分类

    索引分为聚簇索引和非聚簇索引两种。

    聚簇索引是按照数据存放的物理位置为顺序的,能提高多行检索的速度。
    注意:一个表中只能有一个聚集索引。

    非聚簇索引不对数据进⾏物理排序,对于单行的检索很快。
    注意:一个表中可以有多个非聚集索引。

    单值索引(最通用、最简单):一个索引只包含单个列,一个表中可以有多个单值索引

    唯一索引:索引列的值必须唯一,可为空

    复合索引(又叫联合索引,功能最强大):一个索引包括多个列

    3.索引的优势

    1)提高数据检索效率,降低磁盘IO成本
    2)通过对数据的排序,降低排序成本

    4. 索引的劣势

    1)索引虽提高了查询效率,但同时降低了更新、修改、删除的效率,因为MySQL不仅要保存数据,还要维护数据和索引的关系。

    2)需要成本去维护索引。一个性能良好的索引需要不断的去尝试,以找到最优解。

    5. 什么情况下适合建立索引

    1)主键自动建立唯一索引
    2)频繁作为查询条件的字段(where后面的字段)
    3)查询中与其他表关联的字段(各种join on后面的字段)
    4)单值/复合索引选择?(高并发下倾向选择复合索引)
    5)查询中排序的字段
    6)查询中统计或分组的字段

    6. 什么情况下不适合建立索引

    1)表数据太少
    2)频繁更新的字段
    3)where后面用不到的字段

    7. 什么时候会出现索引失效

    1)like以通配符开头(‘%abc’)会导致索引失效,违反最左前缀法则
    最左前缀法则:最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

    2)在索引列上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描

    3)存储引擎不能使用索引中范围条件右边的列,举例:select id,name from student where id > 50 and name = ‘张三’,会导致name索引失效

    4)尽量使用覆盖索引,不要select *

    5)MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描,理由也很简单,B+Tree叶子节点用指针相连且是排好序的,这种数据结构只能解决有序的定值查询,像不等于这种无法利用索引查询。

    6)IS NULL、IS NOT NULL无法使用索引,理由同上

    7)字符串不加单引号索引失效
    ​ 隐式转换–>函数操作

    8)用or连接时会导致索引失效

    8. 为什么建议InnoDB必须建主键

    因为这样能降低数据库开销,对于InnoDB来说,如果不手动建主键索引,MySQL底层依然会帮我们创建一个聚集索引来维护整张表的所有数据,因为B+Tree必须依靠索引才能建立。

    9. 为什么推荐使用整型主键

    因为比较两个int型更具有优势,字符串需要逐位的去比较。

    10. 为什么推荐使用自增主键

    因为如果主键已经插入了1、2、3、4、6、7,这时候我们插入了5,MySQL在维护索引的时候就会打破原有链表顺序,导致链表节点分裂重排,从而消耗性能。而使用自增主键就不会产生这种问题。

    11. 为什么InnoDB非主键索引存储的是主键值

    保持一致性,当数据库表进行DML操作时,同一行记录的页地址会发生改变,因非主键索引保存的是主键的值,无需进行更改。同时还可以节省存储空间,因为Innodb数据本身就已经汇聚到主键索引所在的B+树上了, 如果普通索引还继续再保存一份数据,就会导致有多少索引就要存多少份数据。

    二、MySQL优化

    1、选择最合适的字段属性
    2、尽量把字段设置为NOT NULL
    3、使用连接(JOIN)来代替子查询(Sub-Queries)
    4、使用联合(UNION)来代替手动创建的临时表
    5、使用事务
    6、使用外键
    7、锁定表
    8、使用索引
    9、优化查询语句

    • 9.1 不使用子查询
    • 9.2 避免函数索引
    • 9.3 用IN来替换OR
    • 9.4 LIKE双百分号无法使用到索引
    • 9.5 读取适当的记录LIMIT M,N
    • 9.6 避免数据类型不一致
    • 9.7 分组统计可以禁止排序
    • 9.8 避免随机取记录
    • 9.9 禁止不必要的ORDER BY排序
    • 9.10 批量INSERT插入
  • 相关阅读:
    JDK1.7下测试ConnectorJ连接MySQL8.0
    springboot整合knife4j,从此告别手写接口文档
    知识问答之信息检索-1
    齐岳定制:DBCO-PEG-Mesylate|二苯并环辛炔-聚乙二醇-甲磺酸酯
    HTTP与HTTPS 对比,区别详解(2024-04-25)
    怎么在循环List的时候删除List的元素
    Nacos使用(三)
    观后感《经济逆境中,普通人的机会在哪里?》
    Mybatis学习笔记——mybatis的整体架构
    配置 身份验证 的 Squid代理服务器
  • 原文地址:https://blog.csdn.net/wdywxwxld/article/details/126385325