mysql常用的优化工具_MySQL 基础及性能优化工具

数据库，用户及权限

常用用户管理操作

#创建本地用户 abccreate user abc@localhost

#创建内网能够访问的用户 abccreate user abc@'192.168.1.0/255.255.255.0'

#创建用户时指定密码create user abc@localhost identified by 'abc'

#创建用户时指定加密的密码create user abc@localhost identified by password '*90E462C37378CED12064BB3388827D2BA3A9B689'

#删除用户drop user abc@localhost

#将数据库 shop 所有表权限授予本地 abc 用户grant all on shop.* to abc@localhost

#将数据库 shop 所有表权限授予本地 abc 用户并指定用户密码grant all on shop.* to abc@localhost identified by 'abcde'

#重命名用户 abc@localhost 为 xyz@localhost(权限与重命名之前相同)rename user abc@localhost to xyz@localhost

#废除用户在数据库 shop 上的所有权限revoke all on shop.* from abc@localhost

#修改用户的密码set password for abc@localhost = password('abcdefg')

#修改用户的密码，使用经过加密的密码set password for abc@localhost = '*90E462C37378CED12064BB3388827D2BA3A9B689'

#修改当前用户的密码set password = password('root')

#删除用户drop user abc@localhost

#查看用户信息select user, host, password from mysql.user

#通过直接修改数据库的方式修改密码，操作完之后，需要刷新一下权限update mysql.user set password=password('abc')

where user='abc' and host = '%'

flush privileges

常用数据库创建操作

#查看 sql 语句的帮助help

#查看 select 语句的帮助help select

#创建 UTF-8 字符集的数据库create database db_name character set = utf8

#使用 db_name 数据库use db_name

#删除数据库drop database db_name

导入，导出及进程管理

mysqladmin 常用命令

#创建一个 UTF-8 编码的数据库mysqladmin -uroot -p --default-character-set=utf8 create db_name

#删除远程的数据库mysqladmin -uroot -p -h192.168.1.8 drop db_name

#修改 root 用户密码mysqladmin -uroot -p password 123456

#查看数据库状态mysqladmin -uroot -proot -h192.168.1.8 status

#查看数据库是否存活mysqladmin -uroot -proot -h192.168.1.8 ping

#查看数据库的设置mysqladmin -uroot -proot -h192.168.1.8 variables

#查看数据库服务器中的线程mysqladmin -uroot -proot -h192.168.1.8 processlist

#杀死数据库 ID 为 3, 4 的线程mysqladmin -uroot -proot -h192.168.1.8 kill 3,4

#关闭数据库mysqladmin -uroot -proot -h192.168.1.8 shutdown

mysqldump 常用命令

#备份多个数据库的建表语句(-d 表示不备份数据)mysqldump -uroot -proot -h192.168.100.60 -d --default-character-set=utf8 --B db_name1 db_name2 > a.sql

#备份单个数据库mysqldump -uroot -proot -h192.168.100.60 -d --default-character-set=utf8 db_name1 > a.sql

#备份数据库的建表语句及数据mysqldump -uroot -proot -h192.168.100.60 --default-character-set=utf8 db_name1 > a.sql

#mysqldump 常用参数：#-A 备份所有数据库#-c 使用完整的 insert 语句#-B 指定备份的数据库#-n 不加入 create databases 语句#-t 不加入 create table 语句，在备份数据的时候用到#-d 不加入 insert 语句，在备份数据库结构的时候用到#-R 备份存储过程，默认是没有备份存储过程的#--tables 指定备份的表#--triggers 备份触发器

存储过程

设置变量，查看变量

-- 设置系统变量，系统变量可以加 @@ 前缀set @@sort_buffer_size = 1000000

set profiling = 1

-- 查看变量select @@profiling

-- 在全局范围内设置系统变量set global sort_buffer_size = 1000000

-- 在 session 中设置系统变量

-- 没有加 global/session 以最后一次设置的 global/session 为准set session sort_buffer_size = 1000000

-- 设置用户自定义变量，用户自定义变量加 @ 前缀set @myvar = 1

创建存储过程，取序列号，其中包含加锁机制。

delimiter //

create procedure `sequence`(out `ret` bigint)

comment '获取序列号'

begin

declare lockstatus int;

if get_lock('seq_lock',60) then

update file_seq set cur_value = cur_value+1 where code = 'file';

select cur_value into ret from file_seq where code = 'file';

select release_lock('seq_lock') into lockstatus;

end if;

end //

delimiter ;

实现更为复杂的取序列号函数：

delimiter //

drop function if exists sequence;

create function `sequence`(xcode varchar(128)) returns bigint

comment '获取序列号' not deterministic reads sql data

begin

declare lockstatus int;

declare ret bigint;

declare db_date varchar(20);

if get_lock('seq_lock',60) then

select cur_value into ret from t_sequence where code = xcode;

if ret is null then

insert into t_sequence(code, cur_value) values(xcode, 0);

end if;

update t_sequence set cur_value = cur_value+1 where code = xcode;

select cur_value into ret from t_sequence where code = xcode;

return ret;

select release_lock('seq_lock') into lockstatus;

end if;

end //

delimiter ;

SQL 性能优化

SQL 性能优化主要是针对查询。对于查询，可以通过两个辅助工具来协助分析 SQL 语句：

profile 用于查看一条 SQL 语句的详细执行过程

explain 用于查看 select 语句的执行计划

使用 profile 首先需要打开 profiling，然后执行 SQL 语句，再查询 profile，如下：

select @@profiling;

set profiling = 1;

select * from table_name;

show profile;

show profiles;

show profile for query 1;

set profiling = 0;

通过 show profile 可以查看对应 sql 的执行过程，每个过程花费时间是多少，进而有且于分析 SQL 可以在哪些方面进行优化。

使用 explain 可以了解查询语句的执行计划。以下是一个查询语句的执行计划举例：

以上各列的解释如下：

id 查询语句顺序

select_type 查询语句的类型

table 查询的表名

type 关联类型

system 对应的表只有一行记录

const 对应的表只查询到一行记录

possible_keys 可能用到的索引

key 在执行时实际使用的索引

key_len key的长度

ref 与主键进行比较的列

rows 有多少行会被查询比较

extra 关于查询过程的附加信息

在进行 Mysql 优化时，可以有针对性地从 mysql 说明文档 查询对应列的解释。