mysql常用操作&配置优化

一、基础操作

1、增

创建用户： create user 'zhangsan' @ '10.10.10.10' identified by '密码';  #示例
用户授权： grant all on *.* to 'zhangsan'@'%';          #示例
创建并授权用户： grant all on *.* to 'lisi'@'%' identified by '123';   #示例
新建库： create database xx；
创建表： CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE );
表中添加字段：　alter table 表 add 新增字段;   （后追first；或after；就是添加到首 或者xx之后）　　

2、删

删库： drop database xxx；
删表： drop table  xxx ；
删表内某条数据： delete from表 where id=N　;
删表内某字段：  alter table 表 drop 删除字段;　　

3、改　

改表内某数据数值：update 表 set  age=‘10’  where id=1   #示例
改表名： alter table 表1名 rename 表2名;
改表字段： alter table 表 change 原字段 新字段 char(40) not null default '匿名 ';  #（有特征的话带上） #示例
改表字段某参数： alter table 表 modify   age int(20);  #示例　　　　

4、查

查看库： show databases；
查看表： show tables；
查表结构; desc 表名称;
查状态： show status;  #连接数、查询次数、缓冲区使用情况等
查连接信息： SHOW PROCESSLIST;  #括每个连接的 ID、用户、主机、执行状态等
刷新表： FLUSH TABLES;
刷新查询缓存： FLUSH QUERY CACHE;
刷新主从复制： FLUSH TABLES WITH READ LOCK;　　　　　

二、常用配置优化点

调整缓冲区大小：

innodb_buffer_pool_size：     #增加 InnoDB 缓冲池的大小，该值设置为系统内存的大约 70-80%。
key_buffer_size：             #设置 MyISAM 的键缓冲区大小，该值设置为系统内存的小于 25%。
query_cache_size：            #启用查询缓存并设置缓存大小，可根据实际情况调整。

调整连接参数：

max_connections：     #根据服务器的处理能力调整最大连接数，避免设置过高导致资源耗尽。
wait_timeout：        #设置连接的空闲超时时间，适当数值可释放空闲连接。

配置日志参数：

log_slow_queries 和 long_query_time：  #启用慢查询日志，并设置慢查询的阈值时间。
log_queries_not_using_indexes：       #开启记录没有使用索引的查询语句。

适当调整并发参数：

innodb_thread_concurrency：      #根据系统 CPU 核心数调整 InnoDB 的线程并发度。
innodb_flush_log_at_trx_commit： #根据可接受的数据丢失程度配置事务日志的刷新频率。　　

配置正确的字符集与排序规则：　　

在 my.cnf 配置文件中设置适当的 character_set_server 和 collation_server

优化和修复表：

OPTIMIZE TABLE table_name;  #使用 OPTIMIZE TABLE 命令来优化表。这会重新组织表的物理存储，修复碎片并提高性能。
ALTER TABLE table_name ENGINE=InnoDB;  #使用 ALTER TABLE 命令来重建表。这可以修复表的结构问题，例如删除不再使用的列或索引。