总结MySQL建表、查询优化的一些实用小技巧

MySQL建表阶段是非常重要的一个环节，表结构的好坏、优劣直接影响着后续的管理维护，赶在明天上班前分享总结个人MySQL建表、MySQL查询优化积累的一些实用小技巧。

技巧一、数据表冗余记录添加时间与更新时间
我们用到的很多数据表大多情况下都会有表记录的“添加时间(add_time)”，我建议大家再新增一个记录“更新时间(update_time)”字段，在我的工作里需要为市场部、运营部等建立各种报表，而很多报表里的数据都是需要到大记录表里去查询的，如果直接查询大表的话，查询速度相当慢。那么，我们的解决办法就是建立各种数据快照，数据快照都是通过脚本定时跑的。假如，哪一天原始数据表出现了问题，那么快照数据也会有问题，这个时候我们的快照脚本就得重新跑一份修复好了的数据，这样有了update_time更新时间这个字段，我们就能很快定位记录更新的时间。

技巧二、不要抛弃insert ignore into与replace into
大家肯定写过一些脚本循环将记录插入到数据表，例如使用PHP脚本。如果期间的一条SQL插入失败，那么后续的插入将终止，你可以在脚本里绕过这个插入错误，一个好的办法是使用ignore关键字来屏蔽插入错误。如果，你的数据表里已经存在有将要插入的相同记录，那么这个时候就会产生插入报错，使用 insert ignore into将绕开这个错误，继续下一条记录的插入。

如果你的表结构设计了唯一索引的话，那么使用replace into来更新你的数据表记录再好不过了，尤其是你在写一个脚本的时候，你就不需要在脚本里先查询数据表是否存在该条记录，如果存在，则更新；如果不存在则插入。你可以直接使用replace into，它会自动到数据表检测，如果在唯一索引字段存在该值，则会先删除该条记录，然后再插入新记录；如果唯一索引字段不存在该值，则直接插入数据表，简单且非常实用。

技巧三、给你的表建立唯一索引
很多情况下建立表唯一索引，能给我们省下很多麻烦事，想上面说的replace into就必须是有唯一索引，当然，很多人喜欢用到的自增主键ID尽管也是唯一索引，我建议是能在其他经常出现在where条件屁股后面的字段，并且值是唯一的话可以加个索引甚至唯一索引，查询速度将会提高很多。

说在最后：当然，insert ignore into与replace into大家要根据自己的需求来确定，不一定就适合你现在的需求；我只是提示大家还有这么些个实用的小知识大家可以灵活运用到项目里边。

赞 (0)

相关推荐

  • 详解Mysql多表联合查询效率分析及优化

    1. 多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用','  如: SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2 由于其返回的结果为被连接的两个数据表的乘积,因此当有WHERE, ON或USING条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢.一般使用LE

  • mysql优化limit查询语句的5个方法

    mysql的分页比较简单,只需要limit offset,length就可以获取数据了,但是当offset和length比较大的时候,mysql明显性能下降 1.子查询优化法 先找出第一条数据,然后大于等于这条数据的id就是要获取的数据 缺点:数据必须是连续的,可以说不能有where条件,where条件会筛选数据,导致数据失去连续性,具体方法请看下面的查询实例: 复制代码 代码如下: mysql> set profiling=1; Query OK, 0 rows affected (0.00

  • MySQL查询优化之索引的应用详解

    糟糕的SQL查询语句可对整个应用程序的运行产生严重的影响,其不仅消耗掉更多的数据库时间,且它将对其他应用组件产生影响. 如同其它学科,优化查询性能很大程度上决定于开发者的直觉.幸运的是,像MySQL这样的数据库自带有一些协助工具.本文简要讨论诸多工具之三种:使用索引,使用EXPLAIN分析查询以及调整MySQL的内部配置. MySQL允许对数据库表进行索引,以此能迅速查找记录,而无需一开始就扫描整个表,由此显著地加快查询速度.每个表最多可以做到16个索引,此外MySQL还支持多列索引及全文检索.

  • MySQL查询优化的5个实用技巧

    本文总结分析了MySQL查询优化的技巧.分享给大家供大家参考,具体如下: 熟悉SQL语句的人都清楚,如果要对一个任务进行操作的话,SQL语句可以有很多种相关写法,但是不同的写法查询的性能可能会有天壤之别. 本文列举出五个MySQL查询优化的方法,当然,优化的方法还有很多. 1.优化数据类型 MySQL中数据类型有多种,如果你是一名DBA,正在按照优化的原则对数据类型进行严格的检查,但开发人员可能会选择他们认为最简单的方案,以加快编码速度,或者选择最明显的选择,因此,你可能面临的都不是最佳的选择,

  • MySQL中表子查询与关联子查询的基础学习教程

    MySQL中表子查询与关联子查询的基础学习教程

    MySQL 表子查询 表子查询是指子查询返回的结果集是 N 行 N 列的一个表数据. MySQL 表子查询实例 下面是用于例子的两张原始数据表: article 表: blog 表: SQL 如下: SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog) 查询返回结果如下所示: 该 SQL 的意义在于查找 article 表中指定的字段同时也存在于 blog 表中的所有的行(注

  • 大幅优化MySQL查询性能的奇技淫巧

    大幅优化MySQL查询性能的奇技淫巧

    回顾 MySQL / InnoDB 的改善历史.你能很容易发现.在MySQL 5.6稳定版本中从来没有在read-only 这么快的提速,它很容易搞懂,以及在read-only(RO)有着良好的扩张性.也很期待它在read+write(RW)上达到一个较高水平.(特别是在读取数据是数据库主要工作的时候) 然而.我们对于RO在 MySQL 5.6的表现也十分的高兴,在5.7这个版本中,主要工作集中在 read+write (RW)上, 因为在大数据的处理上还没能达到我们的期望.但是RW依赖RO下.

  • 优化MySQL数据库中的查询语句详解

    很多时候基于php+MySQL建立的网站所出现的系统性能瓶颈往往是出在MySQL上,而MySQL中用的最多的语句就是查询语句,因此,针对MySQL数据库查询语句的优化就显得至关重要!本文就此问题做出详细分析如下: 1.判断是否向MySQL数据库请求了不需要的数据,如下列情况: (1).查询不需要的数据,例如你需要10条数据,但是你选出了100条数据加了limit做限制. (2).多表关联时返回全部列 (3).总是取出全部列select*......取出全部列,会让优化器无法完成索引覆盖扫描这类优

  • php+mysql查询优化简单实例

    本文实例分析了php+mysql查询优化的方法.分享给大家供大家参考.具体分析如下: PHP+Mysql是一个最经常使用的黄金搭档,它们俩配合使用,能够发挥出最佳性能,当然,如果配合Apache使用,就更加Perfect了. 因此,需要做好对mysql的查询优化,下面通过一个简单的例子,展现不同的SQL语句对于查询速度的影响. 存在这样的一张表test,它有一个自增的id作为主索引,现在要查询id号处于某一个范围内的记录,可以使用如下SQL语句: 复制代码 代码如下: SELECT * FROM

  • mysql关联子查询的一种优化方法分析

    mysql关联子查询的一种优化方法分析

    本文实例讲述了mysql关联子查询的一种优化方法.分享给大家供大家参考,具体如下: 很多时候,在mysql上实现的子查询的性能较差,这听起来实在有点难过.特别有时候,用到IN()子查询语句时,对于上了某种数量级的表来说,耗时多的难以估计.本人mysql知识所涉不深,只能慢慢摸透个中玄机了. 假设有这样的一个exists查询语句: select * from table1 where exists (select * from table2 where id>=30000 and table1.u

  • 浅谈MySQL中优化sql语句查询常用的30种方法

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描. 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from

  • mysql 查询重复的数据的SQL优化方案

    在mysql中查询不区分大小写重复的数据,往往会用到子查询,并在子查询中使用upper函数来将条件转化为大写.如: 复制代码 代码如下: select * from staticcatalogue WHERE UPPER(Source) IN (SELECT UPPER(Source) FROM staticcatalogue GROUP BY UPPER(Source) having count(UPPER(Source))>1) ORDER BY upper(Source) DESC; 这条

随机推荐