关于 MySQL 索引的一些认识
23

最近由于公司的业务量增加需要给先有数据库增加索引,查询网上资料库,学习了一番,特来分享!

  • MYSQL索引类型:

    1. 普通索引(normal)
      这是最基本的索引,它没有任何限制
      CREATE INDEX indexName ON mytable(username(length));
      如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。
    2. 唯一索引(unique)
      它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
      CREATE UNIQUE INDEX indexName ON mytable(username(length))
    3. 全文索引(full textl)
      全文搜索的索引,用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通索引可以。
      CREATE FULLTEXT INDEX indexName ON mytable(username)

      总结,索引的类别由建立索引的字段内容特性来决定,通常normal最常见。

  • 建立索引:

    前言:给字段建立索引之前一定想一下该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效,以及效果如何。区分度越大,索引效果越明显。区分度SQL语句:select count(distinct(username))/count(*) from mytable;根据该SQL可知:区分度值位于0.000~1.000之间,该值越大即该字段的区分度越大!

    1. 建立单列索引:
      可以查看该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效,以及效果如何。区分度越大,索引效果越明显。
    2. 建立多列索引:

      多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效。

      eg:select * from t_base_user where name='' and status=1;应该增加 idx_name_status(name,status);而不是idx_status_name(status,name)

      最左前缀匹配原则:和where后面的顺序无关

      eg:当我们建立的索引为index(type,created_at,status)。这个根据最左前缀原则其实是建立了3个索引分别为index(type),index(type,created_at),index(type,created_at,status)。 以下三个SQL都会走index(type,created_at,status)这个索引,
      SQL1:select * from t_base_user where type=10 and created_at='2017-11-03' and status=1
      SQL2:select * from t_base_user where type=10 and status=1 and created_at='2017-11-03'
      SQL3:select * from t_base_user where created_at='2017-11-03' and status=1 and type=10

      因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。

Last::mysql会选择一个最严格(获得结果集记录数最少)的索引,意思也就是只会走一个索引
第一次写了个分享,耗时比较长不太会排版,请各位包含,如有不对请指正:bowtie:

本帖由 Summer 于 10个月前 加精
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
讨论数量: 4

select from t_base_user where 1
select
from t_base_user where 1 and type=10
select from t_base_user where 1 and type=10 and created_at='2017-11-03'
select
from t_base_user where 1 and type=10 and created_at='2017-11-03' and status=1
然后可能type created_at status顺序可能乱的
我可能有这些情况
我应该怎么建立

10个月前

@shijunti19 针对你的这几条SQL,我感觉应该建立索引index('type','created_at','status')这个索引。和顺序没有关系,就看你后面的where了,可以使用EXPLAIN来查看SQL的执行计划是否走索引

10个月前

可以哒

10个月前

@卫星 谢谢:blush:

10个月前

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