加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

生产环境MySQL表的维护：check、optimize和analyze

(2016-09-21 14:26:29)
分类： LAMP/LNMP开发
生产环境MySQL表的维护：check、optimize和analyze
 
 ㈠ optimize
        
        optimize可以回收空间、减少碎片、提高I/O
        目前支持的存储引擎有：InnoDB、MyASIM和ARCHIVE
        
        如果是Replication环境、可加NO_WRITE_TO_BINLOG(或者LOCAL、意思完全相同)、比如：
        optimize local table table_name;
        
        以下是一个简单测试：
 
[plain] 
[mysql@odd employees]$ ls -alh t.ibd  
-rw-rw---- 1 mysql dba 24M 05-22 16:48 t.ibd  
  
未optimize前、有24M  
  
mysql> optimize table t;  
+-------------+----------+----------+-------------------------------------------------------------------+  
| Table       | Op       | Msg_type | Msg_text                                                          |  
+-------------+----------+----------+-------------------------------------------------------------------+  
| employees.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |  
| employees.t | optimize | status   | OK                                                                |  
+-------------+----------+----------+-------------------------------------------------------------------+  
2 rows in set (3.82 sec)  
  
--对于InnoDB的表、上面的内容并非报错、这是MySQL会帮你映射到:alter table table_name engine='InnoDB';  
--MyISAM不会有这种情况  
  
[mysql@odd employees]$ ls -alh t.ibd  
-rw-rw---- 1 mysql dba 14M 05-22 16:49 t.ibd  
    
optimize后、剩14M  
 
㈡ check
        
        检查表或视图的有无错误
        支持表引擎有：InnoDB和MyISAM
        
        下面简单模拟一个测试：
 
[plain] 
mysql> check table t;  
+-------------+-------+----------+----------+  
| Table       | Op    | Msg_type | Msg_text |  
+-------------+-------+----------+----------+  
| employees.t | check | status   | OK       |  
+-------------+-------+----------+----------+  
1 row in set (0.63 sec)  
  
--没有错误的情况是这样的  
  
--用vim打开t.frm随意编辑两把  
  
mysql> check table t\G;  
*************************** 1. row ***************************  
   Table: employees.t  
      Op: check  
Msg_type: Error  
Msg_text: Incorrect information in file: './employees/t.frm'  
*************************** 2. row ***************************  
   Table: employees.t  
      Op: check  
Msg_type: error  
Msg_text: Corrupt  
2 rows in set (0.00 sec)  
  
--报错了  
 
 ㈢ analyze
        
        用于收集优化器统计信息、和tuning相关、
        这个命令对 MyISAM、BDB、InnoDB 存储引擎的表有作用
        如果不想记录到binlog、也可加关键字local或者另外一个
 
[plain] 
mysql> analyze table t\G;  
*************************** 1. row ***************************  
   Table: employees.t  
      Op: analyze  
Msg_type: Error  
Msg_text: Incorrect information in file: './employees/t.frm'  
*************************** 2. row ***************************  
   Table: employees.t  
      Op: analyze  
Msg_type: error  
Msg_text: Corrupt  
2 rows in set (0.00 sec) 


随着mysql的长期使用，肯定会出现一些问题，一般情况下mysql表无法访问，就可以修复表了，优化时减少磁盘占用空间。方便备份。

REPAIR TABLE `table_name` 修复表
OPTIMIZE TABLE `table_name` 优化表

REPAIR TABLE 用于修复被破坏的表。
OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。

多数时间并不需要运行OPTIMIZE TABLE，只需在批量删除数据行之后，或定期（每周一次或每月一次）进行一次数据表优化操作即可，只对那些特定的表运行。

MySQL定期分析检查与优化表

 
 
定期分析表
 
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
 
本语句用于分析和存储表的关键字分布。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当。
 
MySQL使用已存储的关键字分布来决定，当您对除常数以外的对象执行联合时，表按什么顺序进行联合。 
mysql> analyze table a;
+--------+---------+----------+-----------------------------+
| Table  | Op      | Msg_type | Msg_text                    |
+--------+---------+----------+-----------------------------+
| test.a | analyze | status   | Table is already up to date | 
+--------+---------+----------+-----------------------------+
1 row in set (0.00 sec)
 
定期检查表
 
CHECK TABLE tbl_name [, tbl_name]  [option] 
 
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
 
检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表，关键字统计数据被更新。
mysql> check table a;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.a | check | status   | OK       | 
+--------+-------+----------+----------+
1 row in set (0.00 sec)
 
CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。
我们为上面的表a创建一个视图 
mysql> create view a_view as select * from a;
Query OK, 0 rows affected (0.02 sec)
 
然后CHECK一下该视图，发现没有问题
mysql> check table a_view;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.a_view | check | status   | OK       | 
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
 
现在删掉视图依赖的表
mysql> drop table a;
Query OK, 0 rows affected (0.01 sec)
 
再CHECK一下刚才的视图，发现报错了
mysql> check table a_view\G;
*************************** 1. row ***************************
   Table: test.a_view
      Op: check
Msg_type: Error
Msg_text: Table 'test.a' doesn't exist
*************************** 2. row ***************************
   Table: test.a_view
      Op: check
Msg_type: Error
Msg_text: View 'test.a_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
   Table: test.a_view
      Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.00 sec)
 
ERROR: 
No query specified
 
定期优化表
 
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] 
 
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
对于MyISAM表，OPTIMIZE TABLE按如下方式操作：
如果表已经删除或分解了行，则修复表。
如果未对索引页进行分类，则进行分类。
如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。 
mysql> OPTIMIZE table a;
+--------+----------+----------+-----------------------------+
| Table  | Op       | Msg_type | Msg_text                    |
+--------+----------+----------+-----------------------------+
| test.a | optimize | status   | Table is already up to date | 
+--------+----------+----------+-----------------------------+
1 row in set (0.00 sec)
 
****
需要注意的是无论是ANALYZE，CHECK还是OPTIMIZE在执行期间将对表进行锁定，因此请注意这些操作要在数据库不繁忙的时候执行
****
 
 

2012-11-22 13:04 by 轩脉刃, 1318 阅读, 5 评论, 收藏, 编辑

show table status

mysql官方文档在

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

这里的rows行是表的行数，但是实际上是不准的。myisam是准的，其他的存储引擎是不准的。要准确的行数就需要使用count(*) 来获取了。

mysql执行大批量删除

执行大批量删除的时候注意要使用上limit

因为如果不用limit，删除大量数据很有可能造成死锁

如果delete的where语句不在索引上，可以先找主键，然后根据主键删除数据库

ps: 平时update和delete的时候最好也加上limit 1 来防止误操作

optimize、Analyze、check、repair维护操作

l optimize 数据在插入，更新，删除的时候难免一些数据迁移，分页，之后就出现一些碎片，久而久之碎片积累起来影响性能，这就需要DBA定期的优化数据库减少碎片，这就通过optimize命令。

如对MyisAM表操作：optimize table 表名

对于InnoDB表是不支持optimize操作，否则提示“Table does not support optimize, doing recreate + analyze instead”，当然也可以通过命令：alter table one type=innodb; 来替代。

l Analyze 用来分析和存储表的关键字的分布，使得系统获得准确的统计信息，影响 SQL 的执行计划的生成。对于数据基本没有发生变化的表，是不需要经常进行表分析的。但是如果表的数据量变化很明显，用户感觉实际的执行计划和预期的执行计划不 同的时候，执行一次表分析可能有助于产生预期的执行计划。

Analyze table 表名

l Check检查表或者视图是否存在错误，对 MyISAM 和 InnoDB 存储引擎的表有作用。对于 MyISAM 存储引擎的表进行表检查，也会同时更新关键字统计数据

l Repair optimize需要有足够的硬盘空间，否则可能会破坏表，导致不能操作，那就要用上repair，注意INNODB不支持repair操作

生成乱序的id

方法：

使用预设表

比如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是定长，varchar变长 
varchar除了设置了数据之外，还多使用1两个字节定义了数据实际长度。

char会在后面空余的行填充上空字符串

myisam建议使用char。myisam中有个静态表的概念。使用char比使用varchar的查询效率高很多。

innodb建议使用varchar。主要是从节省空间的方面考虑

多个TimeStamp设置默认值

一个表中至多只能有一个字段设置CURRENT_TIMESTAMP

对于下面的需求：

一个表中，有两个字段，createtime和updatetime。

1 当insert的时候，sql两个字段都不设置，会设置为当前的时间

2 当update的时候，sql中两个字段都不设置，updatetime会变更为当前的时间

这样的需求是做不到的。因为你无法避免在两个字段上设置CURRENT_TIMESTAMP

 

解决办法有几个：

1 使用触发器。

2 将第一个timestamp的default设置为0

3 老老实实在sql语句中使用时间戳。

http://www.cnblogs.com/yjf512/archive/2012/11/02/2751058.html

查询数据表有多少行，多少容量

不要使用select count(*)

使用show table status like ‘table_name’  但是innodb的话会有50%左右的浮动，是个预估值

AUTO_INCREMENT的设置

1 不要设置为int，请设置为unsinged int，auto_increment的范围是根据类型来判定的

2 auto_increment数据列必须要有索引，并且保证唯一性。

3 auto_increment必须有NOT NULL属性

4 auto_increment可以使用

UPDATE table SET seq = LAST_INSERT_ID(seq -1)

mysql的表示时间的字段用什么类型

表示时间可以使用timestamp和datetime来使用

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不支持外键，innodb支持；

如果你使用创建外键的命令对myisam的表操作，操作不会返回失败，但是是没有外键关联建立起来的。

对一个字段加减语句

经常有需求对一个字段加减会使用

update table set a = a+1

这样是对的

但是如果这样设置：

select a from table

取出数据后a为1

update table set a =2

这样会导致如果在select和update之间有其他事务操作修改这个字段的话，导致最后的设置可能出错。


喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有