数据库检测脚本

情景描述

有个项目的数据表随着时间的推移，已经达到500多万行记录了，类似的情况在其他产品中也可能存在，当前期预估不足时，我们需要有个手段来检测后期的数据大小。

方案设计

编写脚本，获取数据库中各个表的信息，对于超过预警值的，给予提示。
另外，对于数据库规范(比如索引)的检测，也可以加入该脚本中。

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
<?php
/**
 * 检查mysql数据库是否符合我们的规范
 * 包括索引的命名
 */

$configs = array(
	'host' => 'xxx',
	'username' => 'xxx',
	'password' => 'xxx',
	'dbname' => 'xxx'
);

define('COLOR_RED', 4);// 红色
define('COLOR_GREEN', 2); // 绿色
define('ROW_WARNING_LIMIT', 1000000); // 行长度的预警值
define('TABLE_WARNING_SIZE', 200); // 表大小的预警值，单位为M

$help = <<<EOF
参数说明：
-i:检查索引规范
-r:检查行数大小
-s:检查表的大小
示例：
php checkMySQLRule.php -i	检查索引规范
php checkMySQLRule.php -r	检查行数大小
php checkMySQLRule.php -s	检查表的大小
php checkMySQLRule.php -i -r -s	检查索引规范、行数大小和表大小
EOF;
$opt = getopt('irs');
if (empty($opt)) {
	die("请输入需要检查的项目\n{$help}\n");
}
mysql_connect($configs['host'], $configs['username'], $configs['password']);
mysql_select_db($configs['dbname']);
mysql_query('set names utf8');
$query = mysql_query("show tables");
$tables = array();
while ($row = mysql_fetch_assoc($query)) {
	foreach ($row as $value) {
		$tables[] = $value;
	}
}

if (isset($opt['i'])) {
	run($tables, 'checkIndex');
}
if (isset($opt['r'])) {
	run($tables, 'checkRowLength');
}
if (isset($opt['s'])) {
	run($tables, 'checkTableSize');
}


resetColor();

/**
 * 处理所有的表
 */
function run($tables, $funcName)
{
	foreach ($tables as $tableName) {
		$funcName($tableName);
	}
}


function changeColor($colorNum)
{
	system("tput setf {$colorNum}");
}

function resetColor()
{
	system("tput setf 9");
}

/**
 * 检查索引是否符合规范
 */
function checkIndex($tableName)
{
	$hasCtime = false;
	$hasMtime = false;
	$query = mysql_query("show index from `{$tableName}`");
	if (!$query) {
		changeColor(COLOR_RED);
		echo $tableName;
		resetColor();
		echo " 索引存在异常\n";
		return false;
	}
	while ($row = mysql_fetch_assoc($query)) {
		if ('PRIMARY' == $row['Key_name']) {
			continue;
		}
		if (false === strpos($row['Key_name'], '_')) {
			changeColor(COLOR_RED);
			echo "{$tableName} ";
			resetColor();
			echo "存在不合法的索引名称：";
			changeColor(COLOR_GREEN);
			echo "{$row['Key_name']}\n";
			resetColor();
			continue;
		}
		if ('idx_ctime' == $row['Key_name']) {
			$hasCtime = true;
		}
		if ('idx_mtime' == $row['Key_name']) {
			$hasMtime = true;
		}
	}
	if (!$hasCtime) {
		echo "{$tableName} 缺少idx_ctime索引\n";
	}
	if (!$hasMtime) {
		echo "{$tableName} 缺少idx_mtime索引\n";
	}
}
/**
 * 检查行长度
 */
function checkRowLength($tableName)
{
	$query = mysql_query("select count(*) as rowlength from `{$tableName}`");
	$result = mysql_fetch_assoc($query);
	if ($result['rowlength'] >= ROW_WARNING_LIMIT) {
		changeColor(COLOR_RED);
		echo "{$tableName} 数据总行数为 {$result['rowlength']}，已达到预警值，请注意！\n";
		resetColor();
		return true;
	}
	echo "{$tableName} 数据总行数为 {$result['rowlength']}\n";
}

/**
 * 检查表大小
 * 单位为M
 */
function checkTableSize($tableName)
{
	$sql = "select (DATA_LENGTH / 1024 / 1024) as size "
		. "from information_schema.`TABLES` where  TABLE_NAME= '{$tableName}'";
	$query = mysql_query($sql);
	$result = mysql_fetch_assoc($query);
	$result['size'] = sprintf("%0.2f", $result['size']);
	if ($result['size'] >= TABLE_WARNING_SIZE) {
		changeColor(COLOR_RED);
		echo "{$tableName} 数据总大小为 {$result['size']}M，已达到预警值，请注意！\n";
		resetColor();
		return true;
	}
	echo "{$tableName} 数据总大小为 {$result['size']}M\n";
}