数据库-索引

1.什么是索引?

索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构,在MySQL中默认使用的是InnoDB引擎,InnoDB引擎使用的是B+Tree。

2.索引类型

  • 主键索引(PRIMARY KEY)
    它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。注意:一个表只能有一个主键索引。
  • 唯一索引(UNIQUE)
    唯一索引列的值必须唯一,但允许有空值。
    ALTER TABLE 表名 ADD UNIQUE(字段名称)  
    
  • 普通索引(INDEX)
    最基本的索引,它没有任何限制
    ALTER TABLE 表名 ADD INDEX(字段名称)  
    
  • 组合索引(INDEX)
    一个索引包含多个列,多用于避免回表查询
    ALTER TABLE 表名 ADD INDEX(字段名称1,字段名称2,字段名称n.)  
    
  • 全文索引(FULLTEXT)
    全文检索,是目前搜索引擎使用的一种关键技术
    ALTER TABLE 表名 ADD FULLTEXT(字段名称)  
    

3.查看指定表的索引

SHOW INDEX FROM 表名;  

4.索引的优缺点

  • 优点:
    1.索引大大减少了服务器需要扫描的数据量
    2.索引可以帮助服务器避免排序和临时表
  • 缺点:
    1.降低更新表的速度,如对表进行INSER、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存索引文件。
    2.建立索引会占用磁盘空间的索引文件,一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件会膨胀很快。
    3.如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
    4.对于非常小的表,大部分情况下简单的全表扫描更高效。

5.索引设计的原则

  • 适合索引的列是出现在while子句中的列。
  • 更新十分频繁的字段上不宜建立索引;因为更新操作会变更B+树,重建索引。这个过程是十分消耗数据库性能的。
  • 区分度不大的字段上不宜建立索引;类似于性别这种区分度不大的字段,建立索引的意义不大。因为不能有效过滤,性能和全表扫描相当。另外返回数据的比例在30%以外的情况下,优化器不会选择使用索引。
  • 业务上具有唯一特性的字段,即使是多个字段组合也必须建成唯一索引,虽然唯一索引会影响insert速度,但是对于查询的速度提升是十分明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,在并发的情况下,依然有脏数据产生。
  • 多表关联时,要保证关联字段上一定有索引。(外键字段要加索引)

6.索引优化规则

  • 如果MySQL估计使用索引比全表扫描还慢,则不会使用索引。返回数据的比例是一个重要的指标,比例越低越容易命中索引。记住这个范围值:30%,后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。
  • 前导模糊查询不会命中索引。
    SELECT * FROM user WHERE name LIKE '%s%'
    
  • 非前导模糊查询则可以使用索引,可优化为使用非前导模糊查询。
    SELECT * FROM user WHERE name LIKE 's%'
    
  • 数据类型出现隐式转换的时候不会命中索引,特别是当列类型是字符串,一定要将字符常量值用引号引起来。
  • 复合索引的情况下,查询条件不包含索引列最左边部分(不满足最左原则),不会命中复合索引。
  • union、in、or 都能命中索引,建议使用in。
  • 用or分割开的条件,如果or前的条件中列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到。
  • 负向条件查询不能使用索引(where中条件判断为不等于),可以优化为in查询。
  • 数据库执行计算或函数不会命中索引。
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 218,941评论 6 508
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 93,397评论 3 395
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 165,345评论 0 356
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,851评论 1 295
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,868评论 6 392
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,688评论 1 305
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,414评论 3 418
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 39,319评论 0 276
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,775评论 1 315
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,945评论 3 336
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,096评论 1 350
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,789评论 5 346
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 41,437评论 3 331
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,993评论 0 22
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,107评论 1 271
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 48,308评论 3 372
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 45,037评论 2 355