授权
show grants; //查看当前用户的权限
grant all privileges on test.* to jack@'%' identified by '123'; //为用户名jack密码123，授予test数据库所有权限
revoke all privileges on *.* from jack@'%'; //收回用户jack@用户地址(%)，所有库的所有权限
索引
SHOW INDEX FROM my_table; //查看my_table的索引
CREATE [UNIQUE] INDEX idx_name ON my_table(col1,col2) //为my_table表的col1,col2字段，添加[唯一]索引idx_name
ALTER TABLE my_table ADD PRIMARY KEY(my_id) //为my_table表的my_id字段，添加唯一主键索引
ALTER TABLE my_table ADD INDEX idx_name(my_cloumn) //为my_table表的my_cloumn字段，添加普通索引idx_name
ALTER TABLE my_table ADD UNIQUE uq_name(my_cloumn) //为my_table表的my_cloumn字段，添加唯一索引uq_name
ALTER TABLE my_table ADD FULLTEXT ft_name(my_cloumn) //为my_table表的my_cloumn字段，添加全文索引ft_name
DROP INDEX idx_name ON my_table; //删除my_table表的idx_name索引
加锁
lock table mylock read; //为mylock表加读锁
lock table mylock write; //为mylock表加写锁
unlock tables; //释放锁
日志引擎
show engines; //查看MySQL支持的存储索引
show variables like '%storage_engine%' //查看当前使用的存储引擎
show variables like 'slow_query_log%'; //查看是否开启慢日志
set global slow_query_log = 1; //开启慢查询日志，重启MySQL失效
show variables like 'long_query_time%'; //查看慢查询日志的阈值，默认是10s超过这个值就会记录
set global long_query_time = 3; //设置慢查询日志的阈值为3s，重启MySQL失效
show global status like '%slow_queries%'; //查看系统中存在慢sql的条数
show variables like 'profiling%'; //查看 Show Profile 是否开启
set profiling=on; //开启 Show Profiles
show variables like 'log_bin%'; //查看binlog状态
set global log_bin_trust_function_creators = 1; //添加可以信任存储函数创建者
show profiles; //查看最近15(默认)条sql执行
show profile cpu, block io for query Query_ID; //查看具体某一条的sql执行情况
调优
show [full] processlist; //查看有哪些线程在运行
show open tables from test; //在test库中的表缓存被打开的非TEMPORARY表，in_use被多少连接使用
show status like 'key%'; //myIsam中cache索引命中率，key_buffer_read_hits=(1-key_reads/key_read_requests)
show status like 'innodb_buffer_pool_read%'; //innodb的buffer命中率(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)
show status like 'Qcache%'; //query cache命中率，query_cache_hits =Qcache_hits/(Qcache_hits+Qcache_inserts)
show status like '%connections%'; //查看服务器的总连接数Connections和最大连接数Max_used_connections
show status like 'thread%'; //连接缓存>90%合理，thread_cache_hits = (1- threads_created/connections) * 100 %;
show status like 'created_tmp%'; //查看临时表 Created_tmp_disk_tables / Created_tmp_tables<25%
