关于 MySQL 相关的文章和教程发布了很多,关注微信公众号 Java后端,回复 666 下载就行了。
4. 应尽量避免在 where 子句中使用 or 来连接条件, 否则将导致引擎放弃使用索引而进行全表扫描, 可以 使用UNION合并查询:
select id from t where num=10 union all select id from t where num=20
5. in 和 not in 也要慎用,否则会导致全表扫描,对于连续的数值,能用 between 就不要用 in 了:
Select id from t where num between 1 and 3
6. 下面的查询也将导致全表扫描:
select id from t where name like ‘%abc%’
或者
select id from t where name like ‘%abc’
若要提高效率,可以考虑全文检索。而 select id from t where name like ‘abc%’ 才用到索引
简化SQL语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,后面的查询就在tempdb中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。
查询的结果用于“插、删、改”的不能加nolock !查询的表属于频繁发生页分裂的,慎用nolock !使用临时表一样可以保存“数据前影”,起到类似Oracle的undo表空间的功能,能采用临时表提高并发性能的,不要用nolock 。
select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ (A = B ,B = ‘号码’)
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ and b.referenceid = ‘JCNPRH39681’ (A = B ,B = ‘号码’, A = ‘号码’)
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = ‘JCNPRH39681’ and a.personMemberID = ‘JCNPRH39681’ (B = ‘号码’, A = ‘号码’)
在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用;要注意索引的维护,周期性重建索引,重新编译存储过程。
SELECT * FROM record WHERE substrINg(card_no,1,4)=’5378’ (13秒)
SELECT * FROM record WHERE amount/30< 1000 (11秒)
SELECT * FROM record WHERE convert(char(10),date,112)=’19991201’ (10秒)
分析:
WHERE子句中对列的任何操作结果都是在SQL运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表搜索,因此将SQL重写成下面这样:
SELECT * FROM record WHERE card_no like ‘5378%’ (< 1秒)
SELECT * FROM record WHERE amount< 1000*30 (< 1秒)
SELECT * FROM record WHERE date= ‘1999/12/01’ (< 1秒)
(例如:列出上个月的每一天,我会用connect by去递归查询一下,绝不会去用循环从上个月第一天到最后一天)
oracle 的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB =’PRESIDENT’
OR JOB =’MANAGER’
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB =’PRESIDENT’
OR JOB =’MANAGER’
GROUP BY JOB
表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。
尽量不要对数据库中某个含有大量重复的值的字段建立索引。
40,mysql查询优化总结:使用慢查询日志去发现慢查询,使用执行计划去判断查询是否正常运行,总是去测试你的查询看看是否他们运行在最佳状态下。
久而久之性能总会变化,避免在整个表上使用count(*),它可能锁住整张表,使查询保持一致以便后续相似的查询可以使用查询缓存 ,在适当的情形下使用GROUP BY而不是DISTINCT,在WHERE, GROUP BY和ORDER BY子句中使用有索引的列,保持索引简单,不在多个索引中包含同一个列,有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX,检查使用SQL_MODE=STRICT的问题,对于记录数小于5的索引字段,在UNION的时候使用LIMIT不是是用OR。
为了避免在更新前SELECT,使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现,不要使用 MAX,使用索引字段和ORDER BY子句,LIMIT M,N实际上可以减缓查询在某些情况下,有节制地使用,在WHERE子句中使用UNION代替子查询.
在重新启动的MySQL,记得来温暖你的数据库,以确保您的数据在内存和查询速度快,考虑持久连接,而不是多个连接,以减少开销,基准查询,包括使用服务器上的负载,有时一个简单的查询可以影响其他查询,当负载增加您的服务器上,使用SHOW PROCESSLIST查看慢的和有问题的查询,在开发环境中产生的镜像数据中 测试的所有可疑的查询。
从二级复制服务器上进行备份。在进行备份期间停止复制,以避免在数据依赖和外键约束上出现不一致。彻底停止MySQL,从数据库文件进行备份。
如果使用 MySQL dump进行备份,请同时备份二进制日志文件 – 确保复制没有中断。不要信任LVM 快照,这很可能产生数据不一致,将来会给你带来麻烦。为了更容易进行单表恢复,以表为单位导出数据 – 如果数据是与其他表隔离的。
当使用mysqldump时请使用 –opt。在备份之前检查和优化表。为了更快的进行导入,在导入时临时禁用外键约束。
为了更快的进行导入,在导入时临时禁用唯一性检测。在每一次备份后计算数据库,表以及索引的尺寸,以便更够监控数据尺寸的增长。
通过自动调度脚本监控复制实例的错误和延迟。定期执行备份。
在代码层面去实现貌似是不合理的。
无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。
当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。
myisam: 应用时以读和插入操作为主,只有少量的更新和删除,并且对事务的完整性,并发性要求不是很高的。
Innodb:事务处理,以及并发条件下要求数据的一致性。除了插入和查询外,包括很多的更新和删除。(Innodb有效地降低删除和更新导致的锁定)。对于支持事务的InnoDB类型的表来说,影响速度的主要原因是AUTOCOMMIT默认设置是打开的,而且程序没有显式调用BEGIN 开始事务,导致每插入一条都自动提交,严重影响了速度。可以在执行sql前调用begin,多条sql形成一个事物(即使autocommit打开也可以),将大大提高性能。
原则:更小通常更好,简单就好,所有字段都得有默认值,尽量避免null。
例如:数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型.(mediumint就比int更合适)
比如时间字段:datetime和timestamp, datetime占用8个字节,而timestamp占用4个字节,只用了一半,而timestamp表示的范围是1970—2037适合做更新时间
MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。
因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。例如,
在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,
甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。同样的,如果可以的话,
我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。
对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,
而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。