MySQL中常用命令

授权

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_nameALTER 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_nameALTER TABLE my_table ADD UNIQUE uq_name(my_cloumn)               //为my_table表的my_cloumn字段，添加唯一索引uq_nameALTER TABLE my_table ADD FULLTEXT ft_name(my_cloumn)             //为my_table表的my_cloumn字段，添加全文索引ft_nameDROP 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 Profilesshow 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_connectionsshow 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%