MySQL 基础及性能优化工具

数据库,用户及权限

常用用户管理操作

# 创建本地用户 abc
create user abc@localhost
# 创建内网能够访问的用户 abc
create 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 说明文档 查询对应列的解释。

posted on 2013-08-30 15:51 一是二 阅读(...) 评论(...) 编辑 收藏

公告

导航

统计

  • 随笔 - 15
  • 文章 - 0
  • 评论 - 145
  • 引用 - 0