mysql+性能优化+命令_MySQL 性能优化及常用命令

MYSQL - INNODB 的优化

INNODB 存储引擎实现了行级锁定，虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定要更高一些，但是在整体并发处理能力方面是要远远优于MYISAM的表级锁定的。当系统并发量较高的时候，INNODB的整体性能和MYISAM相比就会有明显的优势了。但是当使用不当的时候，可能会让INNODB的整体性能表现不仅不比MYISAM高，甚至可能会更差。优化MYISAM要做好以下的工作。

MySQL的优化、(1) 尽可能让所有的数据检索都通过索引来完成，从而避免INNODB因为无法通过索引加锁而升级为表级锁定。

(2) 合理设计索引，让INNODB在索引键上面加锁尽可能准确，尽可能地缩小锁定范围，避免造成不必要的锁定而影响其它QUERY的执行。

(3) 尽可能减少基于范围的数据检索过滤条件，避免因为间隙带来的影响而锁定了不该锁定的记录。

(4) 尽量控制事务大小，减少锁定的资源量和锁定时间长度。

系统锁定争用情况查询

执行语句 SHOW STATUS LIKE 'table%'; 查询表级锁定争用状态变量，说明如下：

table_locks_immediate : 产生表级锁定的次数；

table_locks_waited : 出现表级锁定争用而发生等待的次数；

如果 table_locks_waited 状态值比较高，那么说明系统中表级锁定争用的现象比较严重了。

执行语句 SHOW STATUS LIKE 'innodb_row_lock%'; 查询行级锁定争用状态变量，说明如下：

innodb_row_lock_current_waits : 当前正在等待锁定的数量；

innodb_row_lock_time : 从系统启动到现在锁定总时间长度；

innodb_row_lock_time_avg : 每次等待所花平均时间；

innodb_row_lock_time_max: 锁定最长的一次所花的时间；

innodb_row_lock_waits : 从系统启动到现在总共等待次数；

Query 语句优化

MySQL Query Qotimizer ，其主要的功能是通过计算分析系统中收集的各种统计信息，为客户端请求的Query给出最优的执行计划，也就是最优化的数据检索方式。Query语句的优化思路和原则主要体现在下面几个方向：

(1) 优化更需要优化的 QUERY；

高并发低消耗的QUERY对整个系统的影 响远比低并发高消耗的大。

(2) 定位优化对象的性能瓶颈；

IO 还是 CPU，可以借用 PROFILING 的功能找出瓶颈。

(3) 明确优化目标；

根据需求分析。

(4) 从EXPLAIN入手；

可以参考 MYSQL QUERY QOTIMIZER 提出的方案。

(5) 多使用PROFILE;

(6) 永远用小结果集驱动大的结果集；

优化 JOIN QUERY 的时候，最基本的原则就是 小结果集驱动大结果集，通过这个原则来减少嵌套循环中的循环次数，以减少IO总量及CPU运算次数。

(7) 尽可能在索引中完成排序；

(8) 只取自已需要的COLUMNS；

(9) 仅仅使用最有效的过滤条件；

(10) 尽可能避免复杂的 JOIN 和子查询；

前四点可以理解为QUERY优化的一个基本思路，后面部分则为优化的基本原则。

EXPLAIN 的信息解释

SELECT_TYPE 所使用的查询类型

DEPENDENT SUBQUERY ：子查询内层的第一个SELECT，依赖于外部查询的结果集

DEPENDENT UNION： 子查询中的UNION，且为UNION中从第二个SELECT开始的后面所有SELECT，同样依赖于外部查询的结果集。

PRIMARY：子查询中的最外层查询，注意并不是主键查询。

SIMPLE：除子查询或UNION之外的其它查询。

SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。

UNCACHEABLE SUBQUERY：结果集无法缓存的子查询。

UNION：UNION语句中第二个SELECT开始后面的所有SELECT，第一个SELECT为PRIMARY。

UNION RESULT：UNION中的合并结果。

TABLE 显示这一步所访问的数据库的表的名称

TYPE 对表使用的访问方式，主要包含如下集中类型。

ALL：全表扫描。

CONST：读常量，最多只会有一条记录匹配，由于是常量，实际上只需要读一次。

EQ_REF：最多只会有一条匹配结果，一般通过主键或唯一键索引来访问。

FULLTEXT：进行全文索引

查看数据库状态：

show full processlist;

show table status\G; #查看所有表信息

show create table table_name #查看table_name的建表信息

show columns from table_name #查看table_name的字段信息

show innodb status;

show engines; #显示存储引擎的状态信息

show variables; #显示MySQL系统变量的值

show index from table_name; #显示table_name的索引信息

show open tables; #列举在表缓存中当前被打开的非TEMPORARY表

show databases; #查看有多少数据库在服务器中

SHOW TABLES; 显示当前库的所有表

表操作命令：

检查表 CHECK TABLE table_name;

修复表 REPAIR TABLE table_name;

优化表 OPTIMIZE TABLE table_name;

分析表 ANALYZE TABLE table_name;

清空表 TRUNCATE table_name;

删除表 DROP table_name;

重命名表 ALTER TABLE table1 RENAME table2;

改变一个字段的属性，同时重命名字段名 ALTER TABLE table_name CHANGE old_name new_name VARCHAR(80) NOT NULL;

不改变字段名的字段属性修改 ALTER TABLE table_name MODIFY field_name VARCHAR(100) NOT NULL;

在表开头添加一个字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL FIRST;

在表结尾添加一个字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL;

在某个字段之后添加一个字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL AFTER field_x;

删除表中的一个字段 ALTER TABLE table_name DROP field_name;

创建索引 CREATE INDEX index_name table_name (column_name);

删除索引 ALTER TABLE table_name DROP INDEX index_name;

创建表分区：

create table kanzher_rss_article_(

aid bigint not null,

rid bigint not null

)

partition by range(aid) (

partition p0 values less than (2000000),

partition p1 values less than (4000000),

partition p2 values less than (6000000),

partition p3 values less than (8000000),

partition p4 values less than (10000000),

partition p5 values less than (12000000),

partition p6 values less than (14000000),

partition p7 values less than (16000000),

partition p8 values less than (18000000),

partition p9 values less than (20000000)

);

数据库同步设置

设置MASTER表

1，设置从表复制权限：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xjjgooweb1234' WITH GRANT OPTION;

或者：GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO slave@192.168.1.2 IDENTIFIED BY 'password';

2，设置my.ini(my.cnf)：

[mysqld]

server-id = 1 #不能和Slave端设置重名

log-bin = proxy-bin #设置日志名，从该处同步到Slave端。

binlog-do-db = proxy #设置同步的数据库名

3，重启mysql Master数据库

4，查看 Master 数据库状态 show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| proxy-bin.000001 | 106 | proxy | |

+------------------+----------+--------------+------------------+

1 row in set (0.02 sec)

5，Master 数据库已经正常启动。

show binlog events; 查看复制日志

设置 SLAVE 表

6，设置my.ini(my.cnf)：

[mysqld]

server-id = 2

master-host = Master 服务器IP

master-user = user

master-password = password

master-port = 3306

master-connect-retry = 30 --断线或者开机后重试间隔时间

replicate-do-db = proxy --slave 同步 master 的数据库

7，删除日志目录下的master.info|relay-log.info文件，重启 mysql Slave 数据库

8，查看 slave 数据库状态

SHOW SLAVE STATUS\G;

其中 Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，表明 Slave 的 I/O 和 SQL 线程都在正常运行。

到此主从库搭建成功。

1.主辅库同步主要是通过二进制日志来实现同步的。

2.在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件。因为master.info记录了上次要连接主库的信息，如果不删除，即使my.cnf里进行了修改，也不起作用。因为读取的还是master.info文件里的信息。