MySQL 创建高性能的索引
95

  • 什么是索引
    • 我们可以把索引当成书籍的目录,我们想要知道书的特定的内容就要查看目录,然后定位页码
    • mysql的存储引擎采用类似的方法进行数据查询,先去索引中找到对应的值,然后根据匹配的索引找到对应的数据行

  • 索引的类型
    • 索引有很多的类型,都是实现在存储引擎层的
    • 普通索引,最基本的索引,没有任何约束限制
    • 唯一索引,与普通索引类似,但是具有唯一性约束 (一个表可以有多个唯一索引)
    • 主键索引,特殊的唯一索引,不允许有空值 (一个表只能有一个主键索引, 主键还可以与外键构成参照完整性约束,反正数据不一致,而唯一索引不行)
    • 组合索引,将多个列组合在一起创建索引,可以覆盖多个列
    • 外键索引,只有 InnoDB 类型的表才能使用外键索引,保证数据的一致性,完整性和实现级联操作
    • 全文索引,只能对英文进行全文检索,mysql 5.6 版本以前只能由于MyISAM,5.6版本以后InnoDB 也可以使用

  • 索引对性能的影响

    • 大大减少服务器对数据的扫描量
    • 帮助服务器避免排序和临时表
    • 将随机IO变成顺序IO
    • 大大提高查询速率,但会降低写的速度(因为在对数据库进行写操作时,会额外的再操作一遍索引),并占用磁盘(因为索引本身也是一种数据)

  • 索引的使用场景
    • 对于非常小的表(比如只有几十个固定数据的表)来说,大部分情况下不使用索引,全盘扫描效率会更加的高
    • 中到大型表,索引非常有效
    • 特大型的表,建立和使用索引的代价会随之增加,可以使用分区技术来解决

  • 索引的创建原则

    • 最适合索引的列是出现在WHERE子句当中的列或连接子句中的列,而不是出现在SELECT关键字后的列
    • 索引列的基数越大,索引的效果越好
    • 对字符串进行索引,应制定一个前缀长度,可以节省大量的索引空间
    • 根据情况创建复合索引,复合索引可以提高查询效率
    • 避免创建过多的索引,索引会额外的占用磁盘空间,降低写操作效率
    • 主键尽可能选择较短的数据类型,可以有效的减少索引的磁盘占用,提高查询效率

  • 索引的注意事项
    • 复合索引遵循最左前缀原则
    • like查询,%号不能在前,可以使用全文索引
    • column is null 可以使用索引
    • 如果mysql估计使用索引比全文扫描更加慢,会放弃使用索引
    • 如果or前的条件中有列的索引,后面的没有,索引都不会被用到
    • 列类型是字符串的话,查询时一定要给值加引号,否则索引失效

如果感觉我的文章不错请为我点个赞,或者关注一下我,因为大家的鼓励对我真的超重要,每一个赞都能让我开心的不得了

本帖由 Summer 于 8个月前 加精
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
讨论数量: 5

@licheng

列类型是字符串的话,查询时一定要给值加引号,负责索引失效

这句是不是有问题

8个月前
licheng

@Flyertutor 是否则

8个月前
licheng

@Flyertutor 手打的 笔误 已更正 谢谢指正

8个月前

之前同事一直给我灌输 null 会影响索引 (我不知道这句话是指哪个版本的)..

但是自己实践后发现没有. 好气! 理论对应得实践还是得自己动手

8个月前
CismonX

@overfalse

MySQL官方文档指出:

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

尽可能避免使用NULL,它会影响索引的性能。但是查询中IS NULL是可以使用索引的,参考MySQL官方文档

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

7个月前

  • 请注意单词拼写,以及中英文排版,参考此页
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 Emoji 自动补全来咯,可用的 Emoji 请见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!