MySQL 创建高性能的索引

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

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

  • 索引对性能的影响

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

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

  • 索引的创建原则

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

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

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

本帖由 Summer 于 1年前 加精
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 6

@licheng

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

这句是不是有问题

1年前
licheng

@Flyertutor 是否则

1年前
licheng

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

1年前

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

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

1年前
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.

11个月前
__中国人

myisam不支持外键,可以使用模型观察器做级联删除

3个月前

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!