MySQL优化
基础知识
1.操作数据语句优化认识
- 通过LOCK TABLES和UNLOCK TABLES语句可以显式地获取或释放锁
- 数据更新时,要获得独占的访问权,所有其它想要访问这个表的线程必须要等到其更新完成为止。
- 对某张表执行读取操作的客户端也必须获取一个锁,防止在读取的过程中,其它的客户端写入或改变表。但是这个锁不需要独占的访问权。因为读取操作不会改变数据,因此没有理由让某个读取者阻止其它的读取者访问这张表。
2.Insert语句优化
- 将进程/线程数控制在2倍于CPU数目相对合适
- 采用顺序主键策略(例如自增主键,或者修改业务逻辑,让插入的记录尽可能顺序主键)
- 考虑使用replace 语句代替insert语句。
3.MySQL中truncate table和delete语句的区别
- truncate table速度要更快一些,但truncate删除后不记录mysql日志,不可以恢复数据。
- 如果没有外键关联,innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。
- 如果使用innodb_file_per_table参数,truncate table 能重新利用释放的硬盘空间,在InnoDB Plugin中,truncate table为自动回收,如果不是用InnoDB Plugin,那么需要使用optimize table来优化表,释放空间。truncate table删除表后,optimize table尤其重要,特别是大数据数据库,表空间可以得到释放!
- 表有外键关联,truncate table删除表数据为逐行删除,如果外键指定级联删除(delete cascade),关联的子表也会会被删除所有表数据。如果外键未指定级联(cascde),truncate table逐行删除数据,如果是父行关联子表行数据,将会报错。
- auto_increment计数器在truncate table后会重置为0.与是否有外键关联没有关系。
4.搜索优化的整体思路
索引优化,查询优化,查询缓存,服务器设置优化,操作系统和硬件优化,应用层面优化(web服务器,缓存)等等。对于一个整体项目而言只有这些齐头并进,才能实现mysql高性能。
5.查询优化的因素思路
- 是否向数据库请求了不需要的数据。
- mysql是否扫描额外的纪录
查询是否扫描了过多的数据。最简单的衡量查询开销三个指标如下:响应时间;扫描的行数;返回的行数。
没有哪个指标能够完美地衡量查询的开销,但它们大致反映了mysql在内部执行查询时需要多少数据,并可以推算出查询运行的时间。
这三个指标都会记录到mysql的慢日志中,所以检查慢日志记录是找出扫描行数过多的查询的好办法。
响应时间:是两个部分之和:服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间。—可能是等io操作完成,也可能是等待行锁,等等。
扫描的行数和返回的行数:分析查询时,查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。
扫描的行数和访问类型: 在expain语句中的type列反应了访问类型。访问类型有很多种,从全表扫描(ALL)到索引扫描(index)到范围扫描()到唯一索引查询到常数引用等。这里列的这些,速度由慢到快,扫描的行数也是从小到大。
如果发现查询需要扫描大量的数据但只返回少数的行,那么通常可以尝试下面的技巧去优化它:
使用索引覆盖扫描。
改变库表结构。例如使用单独的汇总表。
重写这个复杂的查询。让mysql优化器能够以更优化的方式执行这个查询。
切分查询
有时候对于一个大查询我们需要“分而治之”,将大查询切分为小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。
MySQL慢查询日志
https://www.cnblogs.com/saneri/p/6656161.html
explain详解
http://www.cnblogs.com/saneri/p/7243249.html