Mysql 常用操作

database相关：

create database hly;

drop tablespace hly;

show databases;

use hly;

 

在MySQL 中，数据库对应操作系统下的数据目录。数据库中的每个表至少对应数据库目录

中的一个文件（也可能是多个，这取决于存储引擎）。因此，所使用操作系统的大小写敏感

性决定了数据库名和表名的大小写敏感性

 

查看ddl语句

show create table emp \G;

show table status like 'priv';

 

mysql可以指定表内字段排序：

alter table emp add birth date after ename;

alter table priv modify privilege_type after is_grantable;

 

 

mysql的if函数等

select if(salary>2000,'high','low') from salary;

执行sql文本：

mysql –u root –p gbkhly<creategbkhly.sql

正则匹配

select 'abcdefg' REGEXP '^a';

 

mysql库级别函数：

DATABASE() 返回当前数据库名

VERSION() 返回当前数据库版本

USER() 返回当前登录用户名

INET_ATON(IP) 返回IP 地址的数字表示

INET_NTOA(num) 返回数字代表的IP 地址

PASSWORD(str) 返回字符串str 的加密版本

MD5() 返回字符串str 的MD5 值

select * from sales2 order by rand() limit 5;

group by 多个列with rollup 查询统计

 

用户权限相关：

grant all privileges on hly.* to root@'%' identified by 'mysql';

grant all on *.* to dba@'localhost'; 授权所有库dba

grant all privileges on testdb to dba@'localhost'; 授权库dba

grant select(user_id,username) on smp.users to mo_user@'%'; 授权部分列

show grants; 查看当前用户授权

show grants for mycat@’%’; 查看其它用户授权

user_privileges; 用户权限

table_privileges;表权限

schema_privileges; 数据库权限

column_privileges;

 

环境变量

show variables like '%password%';

set global validate_password_length=3;

show variables like ‘innodb%’;引擎的参数可以这样查找

 

SQL_MODE( ANSI TRADITIONAL )

 

会话相关

show processlist;

kill id;

show status like 'Com_%';当前session中所有统计的数值

show global status like 'Connections';

show global status like 'Uptime';

show global status like 'Slow_queries';慢查询个数 show variables like '%slow%';

show status like 'Handler_read%'; 查看索引使用情况

show status like 'table%';查看表锁等状态信息

show status like 'innodb_row_lock%';查看innodb 行锁信息

select current_user();查看当前会话

 

引擎相关

show engines \G;

create table

 

表空间

Mysq默认采用共享表空间方式（也是单个文件）。

通过调整innodb_file_per_table参数调整表空间是共享模式或单表一个表空间模式

 

 

 

导入导出相关：

mysqldump -u root -p --default-character-set=gbk -d hly>createhly.sql （导出表结构，更换字符集）（-d no data）

mysqldump -u root -p --quick --no-create-info --extended-insert --default-character-set=latin hly>data.sql （导出数据）(--quick用于转储大表，使用原有的字符集导出数据)

 

 

 

 

事务和锁相关（SET AUTOCOMMIT、START TRANSACTION、COMMIT 和ROLLBACK）

（1）默认Mysql是autocommit模式。

（2）savepoint test ---> rollback to savepoint test  分段提交或回滚事务。

（3）分布式事务也是支持。通过xa start 'test','db1'; xa end 'test','db1'; xa prepare 'test','db1'; xa recover \G; xa commit 'test','db1';

（4）Myisam支持表锁，共享读锁和独占写锁，。读不阻塞读，读阻塞其他session写，写阻塞读也阻塞写。

 

 

优化相关：

show global status like 'Slow_queries';慢查询个数 show variables like '%slow%';

show status like 'Handler_read%'; 查看索引使用情况

analyze table sales; check table sales; optimize table sales; 定期分析、检查优化表

explain select * from sales2 use index (ind_sales2_id) where id = 3\G; 使用SQL hint与Oracle hint不同

explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G;

explain select * from sales2 force index (ind_sales2_id) where id > 0\G;

SELECT * FROM tbl_name PROCEDURE ANALYSE();

SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

 

 

参数相关：

mysqld --verbose --help|more 查看所有参数含义

 

影响Mysql性能的重要参数

1. 索引缓存（MyIsam独有）

key_buffer_size索引块（Index Blocks）缓存的大小

单独设置索引缓存

set global hot_cache2.key_buffer_size=200*1024; global标识对每个session均有效

cache index sales,sales2 in hot_cache2;

load index into cache sales ; 预装表sales的索引进默认缓存

1. table_open_cache

打开表缓存的个数。可以通过show status like open_tables 和show status like opened_tables查看这个参数设置是否合适。

1. innodb_buffer_pool_size

InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小 （可以设置到物理内存的80%）

1. innodb_flush_log_at_trx_commit

来控制缓冲区中的数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机。（0 表示每秒写日志cache到文件，1表示提交的时候日志cache会写到文件并且刷新磁盘，2表示提交事务提交的时候写到文件，每秒向磁盘刷新操作），默认为1

1. innodb_additional_mem_pool_size

这个参数是InnoDB 存储引擎用来存储数据库结构和其他内部数据结构的内存池的大小，其默认值是1MB

1. innodb_lock_wait_timeout

用于在出现类似情况的时候等待指定的时间后回滚。系统默认值是50 秒，用户可以根据应用的需要进行调整。

1. innodb_support_xa 是否支持分布式事务，默认是1
2. innodb_log_buffer_size 

日志缓存大小，每秒会刷新一次，默认1Mb

1. innodb_log_file_size

 

Mysql 的Innodb可以和Oracle一样，采用数据缓存机制来cache索引和数据，可以直接使用裸设备，这样效率比调用操作系通IO更高。MyISAM不可以

（1）修改MySQL配置文件，在innodb_data_file_path参数中增加裸设备文件名并指定

newraw属性：

......

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

......

（2）启动MySQL，使其完成分区初始化工作，然后关闭MySQL。此时还不能创建或修

改InnoDB表。

（3）将innodb_data_file_path中的newraw改成raw：

......

class=programlisting[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

......

（4）重新启动即可开始使用。