亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

為了賬號安全,請及時綁定郵箱和手機立即綁定

分析、優化、檢查、修復MySQL數據表

標簽:
MySQL

Summary: in this tutorial, we will introduce you to some very useful statements that allow you to maintain database tables in MySQL.

MySQL provides several useful statement that allows you to maintain database tables effectively. Those statements enable you to analyze, optimize, check, and repair database tables.

Analyze table statement

MySQL query optimizer is an important component of the MySQL server that creates an optimal query execution plan for a query. For a particular query, the query optimizer uses the stored key distribution and other factors to decide the order in which tables should be joined when you performing join, and which indexes should be used for a specific table.

However, the key distributions can be sometimes inaccurate e.g., after you have done a lot of data changes in the table including insert, delete or update. If the key distribution is not accurate, the query optimizer may pick a bad query execution plan that may cause a severe performance issue.

To solve this problem, you can run the ANALYZE TABLE statement for the table e.g., the following statement analyze the payments table in the sample database.

ANALYZE TABLE payments

mysql analyze table statement

If there is no change to the table since the ANALYZE TABLE statement ran, MySQL will not analyze the table again. If you run the above statement again:

ANALYZE TABLE payments

mysql analyze table again

It is saying that the table is already up to date.

Optimize table statement

While working with the database, you do a lot of changes such as insertupdate and delete data in the table that may cause the physical storage of the table fragmented. As a result, the performance of database server is degraded.

MySQL provides you with a statement that allows you to optimize the table to avoid this defragmenting problem. The following illustrates how to optimize a table:

OPTIMIZE TABLE table_name

It is recommended that you execute this statement for the tables that are updated frequently. For example, if you want to optimize the orders table to defragment it, you can perform the following statement:

OPTIMIZE TABLE orders

mysql optimize table statement

Check table statement

Something wrong can happen to the database server e.g., the server was shutdown unexpectedly, error while writing data to the hard disk, etc. These situations could make the database operate incorrectly and in the worst case it can be crashed.

MySQL allows you to check the integrity of database tables by using the CHECK TABLE statement. The following illustrates the syntax of the CHECK TABLE statement:

CHECK TABLE table_name

The CHECK TABLEstatement checks both table and its indexes. For example, you can use the CHECK TABLEstatement to check the orderstable as follows:

CHECK TABLE orders

mysql check table statement

The CHECK TABLE statement only detects problems in a database table but it does not repair them. To repair the table, you use the REPAIR TABLE statement.

Repair table statement

The REPAIR TABLE statement allows you to repair some errors occurred in database tables. MySQL does not guarantee that the REPAIR TABLE statement can repair all errors that the tables may have. The following is the syntax of the REPAIR TABLE statement:

REPAIR TABLE table_name

Suppose there are some errors in the orderstable and you need to fix them, you can use the REPAIR TABLEstatement as the following query:

REPAIR TABLE employees

MySQL returns what it has done to the table and shows you whether the table was repaired or not.

mysql repair statement

 

In this tutorial, you have learned some very handy statements to maintain database tables in MySQL.

原文链接:http://outofmemory.cn/mysql/administration/mysql-database-table-maintenance-statements

點擊查看更多內容
TA 點贊

若覺得本文不錯,就分享一下吧!

評論

作者其他優質文章

正在加載中
  • 推薦
  • 評論
  • 收藏
  • 共同學習,寫下你的評論
感謝您的支持,我會繼續努力的~
掃碼打賞,你說多少就多少
贊賞金額會直接到老師賬戶
支付方式
打開微信掃一掃,即可進行掃碼打賞哦
今天注冊有機會得

100積分直接送

付費專欄免費學

大額優惠券免費領

立即參與 放棄機會
微信客服

購課補貼
聯系客服咨詢優惠詳情

幫助反饋 APP下載

慕課網APP
您的移動學習伙伴

公眾號

掃描二維碼
關注慕課網微信公眾號

舉報

0/150
提交
取消