侯体宗的博客
  • 首页
  • Hyperf版
  • beego仿版
  • 人生(杂谈)
  • 技术
  • 关于我
  • 更多分类
    • 文件下载
    • 文字修仙
    • 中国象棋ai
    • 群聊
    • 九宫格抽奖
    • 拼图
    • 消消乐
    • 相册

mysql优化,建立索引的优缺点,什么场景需要建立索引,什么场景下无需建立索引,mysql索引知识

数据库  /  管理员 发布于 5年前   1195

一、什么是索引?

索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构。

我们需要知道索引其实是一种数据结构,其功能是帮助我们快速匹配查找到需要的数据行,是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

二、索引设计的原则

适合索引的列是出现在where子句中的列,或者连接子句中指定的列;基数较小的类,索引效果较差,没有必要在此列建立索引;使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间;

不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。

三、优点:

通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。可以加快数据的检索速度;可以加速表与表之间的连接;在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间

四、缺点

创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加;索引需要占用物理空间,数据量越大,占用空间越大;会降低表的增删改的效率,因为每次增删改索引,都需要进行动态维护

五、什么时候需要创建索引

主键自动建立唯一索引;频繁作为查询条件的字段应该创建索引;查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找;查询中统计或者分组的字段;

六、什么时候不需要创建索引

频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件where条件里用不到的字段,不创建索引;表记录太少,不需要创建索引;经常增删改的表;数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。

七、索引的分类:(可以使用SHOW INDEX FROM table_name;查看索引详情)

主键索引 PRIMARY KEY
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。注意:一个表只能有一个主键。

唯一索引 UNIQUE
唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引:
可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引:

普通索引 INDEX
这是最基本的索引,它没有任何限制。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引:

组合索引 INDEX
即一个索引包含多个列,多用于避免回表查询。
可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引:

全文索引 FULLTEXT
也称全文检索,是目前搜索引擎使用的一种关键技术。
可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引:
索引一经创建不能修改,如果要修改索引,只能删除重建。可以使用DROP INDEX index_name ON table_name;删除索引。

八、索引和sql语句的优化

1、前导模糊查询不能使用索引,如name like ‘%静’
2、Union、in、or可以命中索引,建议使用in
3、负条件查询不能使用索引,可以优化为in查询,其中负条件有!=、<>、not in、not exists、not like等
4、联合索引最左前缀原则,又叫最左侧查询,
  如果在(a,b,c)三个字段上建立联合索引,那么它能够加快a|(a,b)|(a,b,c)三组的查询速度。
5、建立联合查询时,区分度最高的字段在最左边
6、如果建立了(a,b)联合索引,就不必再单独建立a索引。同理,如果建立了(a,b,c)索引就不必再建立a,         (a,b)索引
7、存在非等号和等号混合判断条件时,在建索引时,要把等号条件的列前置
8、范围列可以用到索引,但是范围列后面的列无法用到索引。
  索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。范围条件有:<、
9、把计算放到业务层而不是数据库层。在字段上计算不能命中索引,
10、强制类型转换会全表扫描,如果phone字段是varcher类型,则下面的SQL不能命中索引。Select * fromuser              where phone=13800001234
11、更新十分频繁、数据区分度不高的字段上不宜建立索引。更新会变更B+树,更新频繁的字段建立索引会大大降低         数据库性能。“性别”这种区分度不太大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表          扫  描类似。一般区分度在80%以上就可以建立索引。区分度可以使用count(distinct(列名))/count(*)来          计算。
12、利用覆盖索引来进行查询操作,避免回表。被查询的列,数据能从索引中取得,而不是通过定位符row-locator再       到row上获取,即“被查询列要被所建的索引覆盖”,这能够加速度查询。
13、建立索引的列不能为null,使用not null约束及默认值
14、利用延迟关联或者子查询优化超多分页场景,MySQL并不是跳过offset行,而是取offset+N行,然后放弃前               offset行,返回N行, 那当offset特别大的时候,效率非常低下,要么控制返回的总数,要么对超过特定阈值的页        进行SQL改写。
15、业务上唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
16、超过三个表最好不要用join,需要join的字段,数据类型必须一致,多表关联查询时,保证被关联的字段需要有索       引。
17、如果明确知道查询结果只要一条,limit 1能够提高效率,比如验证登录的时候。
18、Select语句务必指明字段名称
19、如果排序字段没有用到索引,就尽量少排序
20、尽量用union all 代替 union。Union需要将集合合并后在进行唯一性过滤操作,这会涉及到排序,大量的cpu运算,加大资源消耗及延迟,
    当然,使用union all的前提条件是两个结果集没有重复数据。

....  

有漏的可以在底部留言建议 3Q

小结:

对于自己编写的SQL查询语句,要尽量使用EXPLAIN命令分析一下,做一个对SQL性能有追求的程序员。衡量一个程序员是否靠谱,SQL能力是一个重要的指标。作为后端程序员,深以为然。



  • 上一条:
    laravel中DB操作转MOdels操作查询表数据区别
    下一条:
    mysql事件的编写,使用Navicat添加mysql事件,并设置定时运行mysql事件
  • 昵称:

    邮箱:

    0条评论 (评论内容有缓存机制,请悉知!)
    最新最热
    • 分类目录
    • 人生(杂谈)
    • 技术
    • linux
    • Java
    • php
    • 框架(架构)
    • 前端
    • ThinkPHP
    • 数据库
    • 微信(小程序)
    • Laravel
    • Redis
    • Docker
    • Go
    • swoole
    • Windows
    • Python
    • 苹果(mac/ios)
    • 相关文章
    • 分库分表的目的、优缺点及具体实现方式介绍(0个评论)
    • DevDB - 在 VS 代码中直接访问数据库(0个评论)
    • 在ubuntu系统中实现mysql数据存储目录迁移流程步骤(0个评论)
    • 在mysql中使用存储过程批量新增测试数据流程步骤(0个评论)
    • php+mysql数据库批量根据条件快速更新、连表更新sql实现(0个评论)
    • 近期文章
    • 智能合约Solidity学习CryptoZombie第一课:生成一只你的僵尸(0个评论)
    • 在go中实现一个常用的先进先出的缓存淘汰算法示例代码(0个评论)
    • 在go+gin中使用"github.com/skip2/go-qrcode"实现url转二维码功能(0个评论)
    • 在go语言中使用api.geonames.org接口实现根据国际邮政编码获取地址信息功能(1个评论)
    • 在go语言中使用github.com/signintech/gopdf实现生成pdf分页文件功能(0个评论)
    • gmail发邮件报错:534 5.7.9 Application-specific password required...解决方案(0个评论)
    • 欧盟关于强迫劳动的规定的官方举报渠道及官方举报网站(0个评论)
    • 在go语言中使用github.com/signintech/gopdf实现生成pdf文件功能(0个评论)
    • Laravel从Accel获得5700万美元A轮融资(0个评论)
    • 在go + gin中gorm实现指定搜索/区间搜索分页列表功能接口实例(0个评论)
    • 近期评论
    • 122 在

      学历:一种延缓就业设计,生活需求下的权衡之选中评论 工作几年后,报名考研了,到现在还没认真学习备考,迷茫中。作为一名北漂互联网打工人..
    • 123 在

      Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..
    • 原梓番博客 在

      在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..
    • 博主 在

      佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..
    • 1111 在

      佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..
    • 2017-06
    • 2017-08
    • 2017-09
    • 2017-10
    • 2017-11
    • 2018-01
    • 2018-05
    • 2018-10
    • 2018-11
    • 2020-02
    • 2020-03
    • 2020-04
    • 2020-05
    • 2020-06
    • 2020-07
    • 2020-08
    • 2020-09
    • 2021-02
    • 2021-04
    • 2021-07
    • 2021-08
    • 2021-11
    • 2021-12
    • 2022-02
    • 2022-03
    • 2022-05
    • 2022-06
    • 2022-07
    • 2022-08
    • 2022-09
    • 2022-10
    • 2022-11
    • 2022-12
    • 2023-01
    • 2023-03
    • 2023-04
    • 2023-05
    • 2023-07
    • 2023-08
    • 2023-10
    • 2023-11
    • 2023-12
    • 2024-01
    • 2024-03
    Top

    Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号 PHP交流群

    侯体宗的博客