mysql
mysql 优化
MySQL优化主要针对以下几方面的优化,从前往后,优先考虑。
sql语句优化
sql语句的执行顺序 FROM、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、UNION、ORDER BY 数据库在执行 SQL 语句的第一步是将数据从硬盘加载到数据缓冲区中
索引优化
- hash索引:键值
- btree索引
数据库结构优化
字段类型的选择
尽量使用数字型字段, 若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
表结构设计
尽量使用拆分表,因为很多场景都需要锁表
存储引擎的选择
- InnoDB表优化
- InnoDB存在两种索引,主索引(聚集索引)和辅助索引,基于辅助索引的查找都会还原到主索引上,给查询使用到最多的字段作为主索引,一般都是id
- 尽量通过拆分将大表拆分为小表
- 使用事务组合相关的修改操作,但是尽量不要将事务设置的过大
- 尽量避免使用表锁,可以使用for update使用行锁
- 启用innodb_file_per_table将各表的数据和索引分别进行存放
- innodb_buffer_pool_size、innodb_flush_log_at_trx_commit两个参数
- 使用风格统一的sql语句,提高缓存命中率
- MyISAM表优化:读写操作是9:1、8:2
MyISAM支持表压缩
- Memory表优化
理解查询执行计划 explain
缓冲和缓存
为innoDB的缓冲池、MYISAM的键缓存以及MySQL查询缓存设定了合适大小的内存空间,以便能够存储频繁访问的数据且又不会引起页面换出
锁优化、锁策略
MySQL服务器优化
cpu、内存、I\O、磁盘、网络、线程池管理
性能评估
MySQL优化内幕
sql语句优化
应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union all select id from t where num=20
in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了: select id from t where num between 1 and 3
应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描
select id from t where num/2=100 应改为: select id from t where num=100*2
使用union all替代union,这样就不需要对查询结果进行排序了,提高了效率
使用exists代替in、替代distinct
not会导致不使用索引,避免使用 not ,尽量用> < 实现
数据区分度小时,建立的索引会失效,如在性别上建立索引
尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了
不要使用select *
避免使用having子句,having只会在检索出所有记录后才对结果进行过滤,使用where子句可以限制记录的数目,顺序 where > group by > having
尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理
避免字段出现NULL值,是否在业务上给NULL值默认一个初始值,因为NULL会导致索引失效
尽量多用commit,事物是消耗资源的,大事物还容易引起死锁。
在Join表的时候确认两个表中Join的字段是被建过索引的
Prepared Statements,可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式”攻击。 在性能方面,当一个相同的查询被使用多次的时候,这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数,而MySQL只会解析一次。
尽量早过滤
禁用索引:插入记录时,MySQL会根据表的索引对插入的记录进行排序。如果插入大量数据时,这些排序会降低插入的速度。为了解决这种情况,在插入记录之 前先禁用索引。等插入之后再启用索引。对于新创建的表,可以先不创建索引,等记录都导入以后再创建索引。这样可以提高导入数据的速度。