专栏首页关忆北.MySQL优化以及索引的使用

MySQL优化以及索引的使用

MySQL优化

  • 选取最适用的字段长度

MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。

  • 使用连接(JOIN)来代替子查询(Sub-Queries)
  • 使用事务
  • 优化SQL语句 SQL语句优化的41条建议
    • 是否请求了不需要的数据
    • 拆分复杂查询,不方便掌握其性能
    • 改写子查询,使用连接查询
    • 优化关联查询

确保on/where语句中的列上有索引. 确保order by / group by 只根据一个表上的字段进行,这样才有使用索引进行排序分组的可能性.

  • 使用外键

锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。 例如,外键可以保证每一条销售记录都指向某一个存在的客户。在这里,外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID,任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。 注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候,salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。

CREATE    TABLE    customerinfo( CustomerIDINT    NOT    NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

CREATE    TABLE    salesinfo( SalesIDNT    NOT    NULL,CustomerIDINT    NOT    NULL,

PRIMARYKEY(CustomerID,SalesID),

FOREIGNKEY(CustomerID)    REFERENCES    customerinfo(CustomerID)    ON    DELETE    CASCADE)TYPE=INNODB;
  • 使用索引

索引的优点

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

注意:索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。

索引的缺点

  • 创建索引和维护索引需要消耗时间
  • 占用物理内存

所以单表数据太少,索引反而会影响速度;更新非常频繁的数据不适宜建索引

索引的类型

  • 唯一索引:唯一索引不允许其中任何两行具有相同索引值的索引
  • 主键索引:表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时, 它允许对数据的快速访问 从物理存储角度,索引分为聚集索引和非聚集索引
  • 聚集索引:表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表 只能包含一个聚集索引

索引最左匹配原则

  • 索引可以简单如一个列(a),也可以复杂如多个列(a, b, c, d),即联合索引。
  • 如果是联合索引,那么key也由多个列组成,同时,索引只能用于查找key是否存在(相等),遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了,后续退化为线性查找。因此,列的排列顺序决定了可命中索引的列数。

如有索引(a, b, c, d),查询条件a = 1 and b = 2 and c > 3 and d = 4,则会在每个节点依次命中a、b、c,无法命中d。(很简单:索引命中只能是相等的情况,不能是范围匹配,在第三个c中,是大于,执行万第三个,无法,命中)

索引的使用

  • 为经常需要排序、分组操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT等操作的字段,排序操作会浪费很多时间。如果为分组字段或者排序字段建立索引,可以有效地避免排序操作。

  • 为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。

  • 索引列不能参与计算

即索引列不能带函数,否则会导致索引失效

  • 尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,个人认为尽量不要选用状态\状态字段为索引

  • 尽量使用数据量少的索引

如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间要比对CHAR(10)类型的字段需要的时间要多。

  • 删除不再使用或者很少使用的索引

MySQL索引最多包含16个索引列

  • 条件带like 注意事项

like 模糊查询中,右模糊查询(abc%)会使用索引,而(%abc)和(%abc%)会放弃索引而使用全表扫描

  • =和in可以乱序
  • 联合查询

联合查询,子查询等多表操作时关连字段要加索引,Rmman大佬说,不建议使用子查询

索引使用位置

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!
本文分享自作者个人站点/博客:https://blog.csdn.net/weixin_42313773复制
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • MySQL 的索引查询以及优化技巧

    本文的内容是总结MySQL在没有DBA的团队中的一些常见使用技巧。以下内容以mysql5.5为准。除非另有说明,否则存储引擎以InnoDB为准。

    叫我可儿呀
  • MySQL索引原理以及查询优化

    一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,...

    yaphetsfang
  • MySQL优化--概述以及索引优化分析

    win:C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

    shimeath
  • MySQL索引算法原理以及常见索引的使用

    B Tree指的是Balance Tree,也就是平衡树。平衡树是一颗查找树,并且所有叶子节点位于同一层,如下:

    爱拼才会赢
  • MySQL索引原理以及查询优化「建议收藏」

    一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,...

    全栈程序员站长
  • Mysql索引优化初体验(二)-使用索引

    通过查询分析器explain分析这条查询语句(就在这个语句前面加上explain):

    程序员小明
  • MYSQL 索引优化

    提升SELECT 的最好方式是使用索引。索引条目作为表数据行的指针,使得查询能够很快的定位到所要查找的数据。所有的MySQL数据类型都可以创建索引。

    WindWant
  • MySQL索引优化

    当建立索引后,能再where条件中使用索引列,就尽量使用。 例如 alter table staffs add index idx_staffs_nameAg...

    用户1483438
  • Mysql索引优化

    在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。

    北溟有鱼QAQ
  • Mysql优化-索引

    单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上; 用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);

    剑行者
  • mysql索引优化

    当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样可以保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,...

    哲洛不闹
  • Mysql索引优化

    在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。

    仙士可
  • MySQL-索引优化篇(2)_使用索引扫描来优化排序

    http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.co...

    小小工匠
  • MYSQL的索引优化 顶

    当一个表的数据量较大时,我们需要对这个表做优化,除了分表分库以外,最常见的就是索引优化了,那做索引优化的原则是什么呢?

    算法之名
  • 面试又给我问到MySQL索引【索引的使用策略及优化】

    索引的最左前缀和和B+Tree中的“最左前缀原理”有关,举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引:col1,<...

    码农编程进阶笔记
  • Mysql资料 索引--索引优化(上)

    该sql命中了索引,但未覆盖索引。 select * from s1 where id=123;

    陈不成i
  • Mysql资料 索引--索引优化(下)

    陈不成i
  • Mysql合理建立索引,索引优化

    在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。

    宣言言言
  • mysql索引及优化

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

    allsmallpig

扫码关注腾讯云开发者

领取腾讯云代金券