合 MySQL的mysqlcheck命令和Analyze Table分析表
information_schema.FILES的total_extents为0
今天查询MySQL占用的磁盘文件大小的时候,返回了0,如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | MySQL [(none)]> select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name, -> substring(b.file_name,locate('/',b.file_name,3)+1,(LENGTH(b.file_name)-locate('/',b.file_name,3)-4)) as tb_name, -> b.file_name, -> (total_extents*extent_size) filesize_M, -> b.total_extents, -> b.extent_size -> from information_schema.FILES b -> where b.file_name like '%tpcc%'; +---------+------------+-----------------------+------------+---------------+-------------+ | db_name | tb_name | file_name | filesize_M | total_extents | extent_size | +---------+------------+-----------------------+------------+---------------+-------------+ | tpcc | customer | ./tpcc/customer.ibd | 0 | 0 | 1048576 | | tpcc | district | ./tpcc/district.ibd | 0 | 0 | 1048576 | | tpcc | history | ./tpcc/history.ibd | 0 | 0 | 1048576 | | tpcc | item | ./tpcc/item.ibd | 0 | 0 | 1048576 | | tpcc | new_order | ./tpcc/new_order.ibd | 0 | 0 | 1048576 | | tpcc | order_line | ./tpcc/order_line.ibd | 0 | 0 | 1048576 | | tpcc | orders | ./tpcc/orders.ibd | 0 | 0 | 1048576 | | tpcc | stock | ./tpcc/stock.ibd | 0 | 0 | 1048576 | | tpcc | warehouse | ./tpcc/warehouse.ibd | 0 | 0 | 1048576 | +---------+------------+-----------------------+------------+---------------+-------------+ 9 rows in set (0.05 sec) |
于是乎做了一次分析操作:
1 2 3 4 5 6 7 | ySQL [tpcc]> ANALYZE TABLE stock; +------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+---------+----------+----------+ | tpcc.stock | analyze | status | OK | +------------+---------+----------+----------+ 1 row in set (0.42 sec) |
再次查询:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | MySQL [tpcc]> select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name, -> substring(b.file_name,locate('/',b.file_name,3)+1,(LENGTH(b.file_name)-locate('/',b.file_name,3)-4)) as tb_name, -> b.file_name, -> (total_extents*extent_size) filesize_M, -> b.total_extents, -> b.extent_size -> from information_schema.FILES b -> where b.file_name like '%tpcc%'; +---------+------------+-----------------------+------------+---------------+-------------+ | db_name | tb_name | file_name | filesize_M | total_extents | extent_size | +---------+------------+-----------------------+------------+---------------+-------------+ | tpcc | customer | ./tpcc/customer.ibd | 0 | 0 | 1048576 | | tpcc | district | ./tpcc/district.ibd | 0 | 0 | 1048576 | | tpcc | history | ./tpcc/history.ibd | 0 | 0 | 1048576 | | tpcc | item | ./tpcc/item.ibd | 0 | 0 | 1048576 | | tpcc | new_order | ./tpcc/new_order.ibd | 0 | 0 | 1048576 | | tpcc | order_line | ./tpcc/order_line.ibd | 0 | 0 | 1048576 | | tpcc | orders | ./tpcc/orders.ibd | 0 | 0 | 1048576 | | tpcc | stock | ./tpcc/stock.ibd | 230686720 | 220 | 1048576 | | tpcc | warehouse | ./tpcc/warehouse.ibd | 0 | 0 | 1048576 | +---------+------------+-----------------------+------------+---------------+-------------+ 9 rows in set (0.05 sec) |
有值了,于是相对所有的表都做一次ANALYZE操作,可以用mysqlcheck命令:
1 2 3 4 5 6 7 8 9 | mysqlcheck -Aa -uroot -p to run analyze table for all databases and tables (including InnoDB) on a running server. Available in MySQL 3.23.38 and later. mysqlcheck命令参数说明: -A, --all-databases Check all the databases. This will be same as --databases with all databases selected. -a, --analyze Analyze given tables. |
过程:
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 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 | root@mysql5730:/var/lib/mysql/tpcc# mysqlcheck -A -uroot -plhr mysqlcheck: [Warning] Using a password on the command line interface can be insecure. ceshi2.ftb OK ceshi2.ftb2 OK db1.A OK db1.S2 OK db1.T OK db1.case_bin_test OK db1.case_bin_test1 OK db1.cb OK db1.nba OK db1.s1 OK db1.s2 OK db1.s22 OK db1.sbtest5 OK db1.sc OK db1.student OK db1.t OK db1.t2 OK db1.t_index OK db1.tb1 OK db1.ttt OK db1.tttt OK db2.card OK db2.case_bin_test OK db2.case_bin_test1 OK db2.course OK db2.person OK db2.score OK db2.scores_tb OK db2.st OK db2.student OK db2.teacher OK db3.case_bin_test OK db3.case_bin_test1 OK db3.case_bin_test2 OK db4.auth_runsta OK db4.insect OK db6.innodb_table OK db7.a OK db7.innodb_table OK db8.tb1 OK lhrdb.events_list OK lhrdb.grade OK lhrdb.innodb_table OK lhrdb.isam_table OK lhrdb.nba OK lhrdb.pet OK lhrdb.scores_tb OK lhrdb.t OK lhrdb.t1 OK lhrdb.t_rowid OK lhrdb.tb1 OK lhrdb.temp OK lhrdb.time OK lhrdb.with_pk_1000 OK lhrdb.with_pk_991 OK lhrdb.with_pk_992 OK lhrdb.with_pk_993 OK lhrdb.with_pk_994 OK lhrdb.with_pk_995 OK lhrdb.with_pk_996 OK lhrdb.with_pk_997 OK lhrdb.with_pk_998 OK lhrdb.with_pk_999 OK lhrdb.with_pk_$i OK lhrdb.without_pk_1000 OK lhrdb.without_pk_991 OK lhrdb.without_pk_992 OK lhrdb.without_pk_993 OK lhrdb.without_pk_994 OK lhrdb.without_pk_995 OK lhrdb.without_pk_996 OK lhrdb.without_pk_997 OK lhrdb1.events_list OK lhrdb1.grade OK lhrdb1.innodb_table OK lhrdb1.isam_table OK lhrdb1.nba OK lhrdb1.pet OK lhrdb1.scores_tb OK lhrdb1.t OK lhrdb1.t1 OK lhrdb1.t_rowid OK lhrdb1.temp OK lhrdb1.time OK lhrdb2.events_list OK lhrdb2.grade OK lhrdb2.innodb_table OK lhrdb2.isam_table OK lhrdb2.nba OK lhrdb2.pet OK lhrdb2.scores_tb OK lhrdb2.t OK lhrdb2.t1 OK lhrdb2.t_rowid OK lhrdb2.temp OK lhrdb2.time OK lhrdb2.with_pk_1000 OK lhrdb2.with_pk_991 OK lhrdb2.with_pk_992 OK lhrdb2.with_pk_993 OK lhrdb2.with_pk_994 OK lhrdb2.with_pk_995 OK lhrdb2.with_pk_996 OK lhrdb2.with_pk_997 OK lhrdb2.with_pk_998 OK lhrdb2.with_pk_999 OK lhrdb2.with_pk_$i OK lhrdb2.without_pk_1000 OK lhrdb2.without_pk_991 OK lhrdb2.without_pk_992 OK lhrdb2.without_pk_993 OK lhrdb2.without_pk_994 OK lhrdb2.without_pk_995 OK lhrdb2.without_pk_996 OK lhrdb2.without_pk_997 OK lhrdb3.events_list OK lhrdb3.grade OK lhrdb3.innodb_table OK lhrdb3.isam_table OK lhrdb3.nba OK lhrdb3.pet OK lhrdb3.scores_tb OK lhrdb3.t OK lhrdb3.t1 OK lhrdb3.t_rowid OK lhrdb3.temp OK lhrdb3.time OK lhrdb3.with_pk_1000 OK lhrdb3.with_pk_991 OK lhrdb3.with_pk_992 OK lhrdb3.with_pk_993 OK lhrdb3.with_pk_994 OK lhrdb3.with_pk_995 OK lhrdb3.with_pk_996 OK lhrdb3.with_pk_997 OK lhrdb3.with_pk_998 OK lhrdb3.with_pk_999 OK lhrdb3.with_pk_$i OK lhrdb3.without_pk_1000 OK lhrdb3.without_pk_991 OK lhrdb3.without_pk_992 OK lhrdb3.without_pk_993 OK lhrdb3.without_pk_994 OK lhrdb3.without_pk_995 OK lhrdb3.without_pk_996 OK lhrdb3.without_pk_997 OK mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.general_log OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK ocp0126.events_list OK ocp0126.innodb_table OK ocp0126.isam_table OK ocp0126.time OK orchestrator.access_token OK orchestrator.active_node OK orchestrator.agent_seed OK orchestrator.agent_seed_state OK orchestrator.async_request OK orchestrator.audit OK orchestrator.blocked_topology_recovery OK orchestrator.candidate_database_instance OK orchestrator.cluster_alias OK orchestrator.cluster_alias_override OK orchestrator.cluster_domain_name OK orchestrator.cluster_injected_pseudo_gtid OK orchestrator.database_instance OK orchestrator.database_instance_analysis_changelog OK orchestrator.database_instance_binlog_files_history OK orchestrator.database_instance_coordinates_history OK orchestrator.database_instance_downtime OK orchestrator.database_instance_last_analysis OK orchestrator.database_instance_long_running_queries OK orchestrator.database_instance_maintenance OK orchestrator.database_instance_peer_analysis OK orchestrator.database_instance_pool OK orchestrator.database_instance_recent_relaylog_history OK orchestrator.database_instance_stale_binlog_coordinates OK orchestrator.database_instance_tags OK orchestrator.database_instance_tls OK orchestrator.database_instance_topology_history OK orchestrator.global_recovery_disable OK orchestrator.host_agent OK orchestrator.host_attributes OK orchestrator.hostname_ips OK orchestrator.hostname_resolve OK orchestrator.hostname_resolve_history OK orchestrator.hostname_unresolve OK orchestrator.hostname_unresolve_history OK orchestrator.kv_store OK orchestrator.master_position_equivalence OK orchestrator.node_health OK orchestrator.node_health_history OK orchestrator.orchestrator_db_deployments OK orchestrator.orchestrator_metadata OK orchestrator.raft_log OK orchestrator.raft_snapshot OK orchestrator.raft_store OK orchestrator.topology_failure_detection OK orchestrator.topology_recovery OK orchestrator.topology_recovery_steps OK percona_schema.pt_upgrade OK sakila.actor OK sakila.address OK sakila.category OK sakila.city OK sakila.country OK sakila.customer OK sakila.film OK sakila.film_actor OK sakila.film_category OK sakila.film_text OK sakila.inventory OK sakila.language OK sakila.payment OK sakila.rental OK sakila.staff OK sakila.store OK sakila.t1 OK sakila.t2 OK sbtest.sbtest1 OK sbtest.sbtest10 OK sbtest.sbtest2 OK sbtest.sbtest3 OK sbtest.sbtest4 OK sbtest.sbtest5 OK sbtest.sbtest6 OK sbtest.sbtest7 OK sbtest.sbtest8 OK sbtest.sbtest9 OK select_lhrdb.card OK select_lhrdb.course OK select_lhrdb.person OK select_lhrdb.score OK select_lhrdb.student OK select_lhrdb.teacher OK sys.insect OK sys.sys_config OK testdb.student OK tpcc.customer OK tpcc.district OK tpcc.history OK tpcc.item OK tpcc.new_order OK tpcc.order_line OK tpcc.orders OK tpcc.stock OK tpcc.warehouse OK |
完成。
MySQL分析表、检查表和优化表
对表进行优化 ( 优化表主要作用是消除删除或者更新造成的空间浪费)
对表进行分析(分析关键字的分布, 分析并存储MyISAM和BDB表中键的分布)
对表进行检查(检查表的错误,并且为MyISAM更新键的统计内容)
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!