当前位置:Gxlcms > mysql > Mysql优化与使用集锦_MySQL

Mysql优化与使用集锦_MySQL

时间:2021-07-01 10:21:17 帮助过:10人阅读

MyISAM的性能是比Innodb强

MyISAM的索引和数据是分开的,并且索引是有压缩的

Innodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成Innodb比MyISAM体积庞大不小

MyISAM不支持外键 Innodb支持

MyISAM不支持事务 Innodb支持

MyISAM只支持表所 Innodb支持行锁

对数据信息的存储方式不同,MyISAM创建一张表对应3个文件,Innodb则只有一个文件.frm,数据存储在ibdata1

复制自己insert into tt select * from tt

--------------------------

恢复

不小心update一个表where写的范围不对,导致这个表没法正常用了,这个时候MyISAM的优越性就体现出来了,随便从当天拷贝的压缩包取出对应表的文件,随便放到一个数据库目录下,然后dump成sql再导回到主库,并把对应的binlog补上。如果是Innodb,恐怕不可能有这么快速度

--------------------------

锁表

select count(*) 和order by 是最频繁的,大概能占了整个sql总语句的60%以上的操作,而这种操作Innodb其实也是会锁表的,很多人以为Innodb是行级锁,那个只是where对它主键是有效,非主键的都会锁全表的。

-------------------------

大小

保证数据库单个实例尽量不要超过150G。

受文件系统操作限制,文件数过大需要更多文件句柄,且大目录 操作造成复制、压缩、备份效率低。

- 打开表占用数据库资源(table_cache)

√ 建议一个库不应超过300-400个表

√ 建议一般带char字段的表不应超过500万rows.基于数字的字段为主的表不要超过1000万rows.


切分尽量多的小实例,一个机器跑7-8个实例,平常load avg不超过1-2,峰值不超过6-7为合理。

-------------------------

主从

通过分多个主库,便于未来可扩展

通过使用replicate_do_db(table)来解决从库追主库延迟时间较长的问题由于mysql的从库只能单进程追,而通过上述方式,就能形成多进程追不同库来减少延迟,缺点是管理成本会很大。

---------------------------

多IDC

通过多IDC提升数据库平台99.999%稳定性

---------------------------
分表
按时间(财经)
按ID号hash分(统一通行证)
按业务项目(通用投票)

merge引擎

提升代码开发速度
1.
比如有些项目,需要定期存用户离线消息,可以采取程序只访问对应的merge表,然后merge表对应7个子表(比如周一到周日)。
2.
比如统计项目,可能分表策略是每个月一个表,然后要做如一季度,二季度的统计,为
了方便开发,可以采取程序只访问对应merge表,然后自由结合1234,N表作为merge表的子表。

---------------------------

索引

正确使用索引,避免全表搜索使用定长表,且定期做OPTIMIZE TABLE命令(注意这个命令会锁表,请在数据库访问小的时候做)在对大表进行添加索引,一定要选择访问小的时间段做,否则会导致严重问题。

注:一般临晨2-3点时候是大部分项目访问的低谷。

索引优化,选择实验
稳妥地改进
将需要优化的相关表复制到测试环境
在测试环境启动一个测试daemon,关闭query cache或是使用select SQL_NO_CACHE 方式。
未优化时测试若干次查询时间,以及explain检查扫描集。
选择合适的索引试验建立。可以通过use index(xx)来强制使用。检查是否有效。
测试查询时间变化,反复试验得到最优结果

保持关注,根据情况随时改变索引设置

采取从库不同索引的模式来提升性能
比如有些项目,有很多不同的排序需求,需
要建立很多索引,但是如果都加必然导致性
能下降,所以采取不同功能使用对应索引的
从库来解决。

-------------------------------------

排序

尽量使用带主键的字段做order by 的排序尽量不要多提供页面的查找(最好只提供100页内),避免机器爬虫抓取数据,导致数据库压力负载过高。因为做order by field1 limit xxxxxx,20是非常消耗数据库资源。 -------------------------------------批量通过使用insert批量的方式来提升主库的写速度,通过批量values模式都能提升主库写性能。 -------------------------------------- key-value通过简单的key-value模式数据库来处理简单逻辑业务,如berkeley DB, LightCloud, Tokyo Tyrant------------------------------------ NoSQL通过Memcache来缓冲频繁update的数据库
比如通过设定阀值500次才往数据库做一次写操作,或是间隔30分钟往数据库写一次。------------------------------------- 补丁通过使用如ebay公司开发的heap补丁来解决一些如session业务比如跑一些数据总大小不会很大,但是update特别频繁的,比如用户状态值,补丁的好处是省内存。------------------------------------ 监控进程列表mysql>show processlist; profiling该方式默认是关闭的。 可以通过以下语句查看
mysql>select @@profiling;
mysql>set profiling=1; //打开
执行需要测试的sql 语句:mysql> show profiles;
通过指定的Query_ID 来查询指定的sql语句的执行信息:
mysql> show profile for query 1;
测试完毕以后 ,关闭参数: mysql> set profiling=0

人气教程排行