Home >Database >Mysql Tutorial >小贝_mysql表优化_MySQL

小贝_mysql表优化_MySQL

WBOY
WBOYOriginal
2016-06-01 12:59:011012browse

mysql表优化

简要:
一、分析表
二、检查表
三、优化表
四、表数据导入
五、锁表操作

一、分析表

1.1、命令:

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]TABLE tbl_name [, tbl_name] ...

1.2、作用:

本语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信息,使得sql能够生成正确的行计划

1.3、不足:

在分析期间,会对表进行读锁操作

1.4、例子:

\

 

二、检查表

2.1、命令

CHECK TABLE tbl_name [, tbl_name] ...[option] ...

option = {FOR UPGRADE | QUICK | FAST |MEDIUM | EXTENDED | CHANGED}

2.2、作用:

检查一个或多个表是否有错误,便于排错

2.3、不足:

在进行检查表期间,需要给执行当前操作者更多的权限或优先级

2.4、例子:

 

 

\

三、优化表空间

3.1、命令

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]TABLE

tbl_name [, tbl_name] ...

3.2、作用

如果已经删除了表的一大部分,或者已经对含有可变长度的表进行了很多的改动,则需要做定期的优化。这个命令可以将表中的空间碎片进行合并,进行回收。

3.3、不足:

依赖表的存储引擎。只能针对innodb和myisam存储引擎才可以使用

3.4、例子:

3.4.1、表结构以myisam为例

Create Table: CREATE TABLE `t` (

`id` int(10) unsigned DEFAULT NULL,

`name` char(3) DEFAULT NULL,

`age` char(5) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

3.4.2、查看表t大小

\

 

3.4.3、删除表数据t

\

 

3.4.4、优化表

\

 

 

四、表数据导入

4.1、针对myisam非唯一索引

格式: alter table 表名 disable keys;

loading data

alter table 表名 enable keys

(备注: disable keys和enable keys用来打开或关闭myisam表中非唯一索引的更新,提高导入速度。)

4.1.1、例子:

创建表t

\

4.1.2、插入测试数据

\

4.1.3、导出数据并清空数据

\

4.1.4、未关闭索引

\

4.1.5、关闭索引

\

 

4.2、针对myisam唯一索引

格式: set unique_checks=0

loading data

setunique_checks=1

(备注: unique_checks是myisam表进行唯一索引的校验,当其为0时,则是关闭,为1时,则为开启。因此,可以在导入数据时,让其关闭。导入成功后再进行改期,提高导入速度。)

4.2.1、例子:

\

4.2.2、插入测试数据

\

4.2.3、导出数据并清空数据

\

4.2.4、未关闭唯一索引校验

\

4.2.5、关闭唯一索引校验

\

 

4.3、针对innodb表

格式: set autocommit=0;

loadingdata

setautocommit=1;

导入数据前先执行setautocommit=0,关闭自动提交事务,在导入结束后执行set autocommit=1,恢复自动提交,从而提高导入效率。

五、锁操作

5.1、myisam表读、写锁

5.1.1、读锁定

5.1.1A、作用: 一旦进行读锁定,则当前表就不允许删、插入、修改等操作

5.1.1B、例子:

a、测试表:

\

b、读锁定

\

c、当前终端执行删除、修改、插入操作

\

d、另外的终端执行删除操作

\

(出现等待状态)

e、解锁操作

\

(当前终端解锁)

 

(新的终端才完成删除操作)

5.1.2、myisam写锁定

5.1.2A、作用: 一旦进行写锁定,则只允许当前终端执行增删改查操作,新的终端则不允许,必须等待第一个终端执行完,才可以

5.1.2B、例子:

第一个终端\

第二个终端\

解锁:

第一个终端:\

第二个终端:\

 

5.1.3、表锁定是否会影响视图

5.1.3A、例子

\

对表t进行读锁

\

(执行读锁定的终端截图)

\

(新终端)

\

(旧终端解锁)

\

(新终端)

总结: a、myisam表的写锁定比读锁定更严格

b、一般情况下很少去显式地去对表进行读和写锁定。myisam会自动进行锁定

c、原始表读写锁定会影响视图

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn