MySQL定期分析检查与优化表的方法小结
时间:2024-06-03 12:20:01 来源:未知 作者:站长技术网 点击:次
定期分析表 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] 本语句用于分析和存储表的关键字分布。在分析期间,使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表,本语句与使用myisamchk -a相当。 MySQL使用已存储的关键字分布来决定,当您对除常数以外的对象执行联合时,表按什么顺序进行联合。 mysql> analyze table a; 定期检查表 CHECK TABLE tbl_name [, tbl_name] [option] option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} mysql> check table a; mysql> create view a_view as select * from a; 然后CHECK一下该视图,发现没有问题 mysql> check table a_view; mysql> drop table a; mysql> check table a_view\G; ERROR: OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] mysql> OPTIMIZE table a; **** **** by 陈于喆 show table status 这里的rows行是表的行数,但是实际上是不准的。myisam是准的,其他的存储引擎是不准的。要准确的行数就需要使用count(*) 来获取了。 mysql执行大批量删除 因为如果不用limit,删除大量数据很有可能造成死锁 如果delete的where语句不在索引上,可以先找主键,然后根据主键删除数据库 ps: 平时update和delete的时候最好也加上limit 1 来防止误操作 optimize、Analyze、check、repair维护操作 optimize 数据在插入,更新,删除的时候难免一些数据迁移,分页,之后就出现一些碎片,久而久之碎片积累起来影响性能,这就需要DBA定期的优化数据库减少碎片,这就通过optimize命令。 如对MyisAM表操作:optimize table 表名 对于InnoDB表是不支持optimize操作,否则提示“Table does not support optimize, doing recreate + analyze instead”,当然也可以通过命令:alter table one type=innodb; 来替代。 Analyze 用来分析和存储表的关键字的分布,使得系统获得准确的统计信息,影响 SQL 的执行计划的生成。对于数据基本没有发生变化的表,是不需要经常进行表分析的。但是如果表的数据量变化很明显,用户感觉实际的执行计划和预期的执行计划不 同的时候,执行一次表分析可能有助于产生预期的执行计划。 Analyze table 表名 Check检查表或者视图是否存在错误,对 MyISAM 和 InnoDB 存储引擎的表有作用。对于 MyISAM 存储引擎的表进行表检查,也会同时更新关键字统计数据 Repair optimize需要有足够的硬盘空间,否则可能会破坏表,导致不能操作,那就要用上repair,注意INNODB不支持repair操作 使用预设表 比如id和toid的映射 其中id是固定的,toid是随机的。 然后在redis或memcache中记录一个指针值,指向id 当要获取一个新toid的时候,取出指针值,加1,然后去预设表中获取toid 查询和索引 比如有几个小招: 1 不要在索引列中使用表达式 where mycol *2 < 4 2 不要在like模式的开始位置使用通配符% where col_name like ‘%string%' 不如 where col_name like ‘string%' 3 避免过多使用mysql自动转换类型,有可能无法用到index 比如 select * from mytbl where str_col=4 但是str_col为字符串,这里其实就隐含了字符串变化 应该使用 select * from mytbl where str_col='4' 索引比表还大就不需要建立索引了吗 索引是按照顺序排列的。所以即使索引比表大,也是可以加快查询速度的。 当然如果索引比表还大首要的任务必须是检查下索引建立地是否有问题 Char和varchar如何选择 char会在后面空余的行填充上空字符串 myisam建议使用char。myisam中有个静态表的概念。使用char比使用varchar的查询效率高很多。 innodb建议使用varchar。主要是从节省空间的方面考虑 多个TimeStamp设置默认值 对于下面的需求: 一个表中,有两个字段,createtime和updatetime。 1 当insert的时候,sql两个字段都不设置,会设置为当前的时间 这样的需求是做不到的。因为你无法避免在两个字段上设置CURRENT_TIMESTAMP 解决办法有几个: 1 使用触发器。 2 将第一个timestamp的default设置为0 3 老老实实在sql语句中使用时间戳。 查询数据表有多少行,多少容量 使用show table status like ‘table_name' 但是innodb的话会有50%左右的浮动,是个预估值 AUTO_INCREMENT的设置 1 不要设置为int,请设置为unsinged int,auto_increment的范围是根据类型来判定的 UPDATE table SET seq = LAST_INSERT_ID(seq -1) mysql的表示时间的字段用什么类型 datetime表示的时间可以从0000-00-00:00:00 到9999-12-31:00:00:00 timestamp表示的时间为1970-01-01 08:00:01到2038-01-19 11:14:07 timestamp占用的空间比datetime少,且可以设置时区等功能,所以能使用timestamp的地方尽量使用timestamp 使用timestamp还可以设置 [ON UPDATE CURRENT_TIMESTAMP] [DEFAULT CURRENT_TIMESTAMP] myisam和innodb支持外键 如果你使用创建外键的命令对myisam的表操作,操作不会返回失败,但是是没有外键关联建立起来的。 对一个字段加减语句 update table set a = a+1 这样是对的 但是如果这样设置: select a from table 取出数据后a为1 update table set a =2 这样会导致如果在select和update之间有其他事务操作修改这个字段的话,导致最后的设置可能出错。 |