浙江住房和城乡建设部网站,程序员就是做网站的吗,外贸免费自助建站平台,北京 网站 外包一、MySQL优化 从上图可以看出SQL及索引的优化效果是最好的#xff0c;而且成本最低#xff0c;所以工作中我们要在这块花更多时间。 服务端参数配置#xff1b; max_connections3000 连接的创建和销毁都需要系统资源#xff0c;比如内存、文件句柄#xff0c;业务说的支持…一、MySQL优化 从上图可以看出SQL及索引的优化效果是最好的而且成本最低所以工作中我们要在这块花更多时间。 服务端参数配置 max_connections3000 连接的创建和销毁都需要系统资源比如内存、文件句柄业务说的支持多少并发指的是每秒请求数也就是QPS。 一个连接最少占用内存是256K最大是64M如果一个连接的请求数据超过64MB比如排序就会申请临时空间放到硬盘上。 如果3000个用户同时连上mysql最小需要内存3000*256KB750M最大需要内存3000*64MB192G。 如果innodb_buffer_pool_size是40GB给操作系统分配4G给连接使用的最大内存不到20G如果连接过多使用的内存超过20G将会产生磁盘SWAP此时将会影响性能。连接数过高不一定带来吞吐量的提高而且可能占用更多的系统资源。
max_user_connections2980 允许用户连接的最大数量剩余连接数用作DBA管理。
back_log300 MySQL能够暂存的连接数量。如果MySQL的连接数达到max_connections时新的请求将会被存在堆栈中等待某一连接释放资源该堆栈数量即back_log如果等待连接的数量超过back_log将被拒绝。
wait_timeout300 指的是app应用通过jdbc连接mysql进行操作完毕后空闲300秒后断开默认是28800单位秒即8个小时。
interactive_timeout300 指的是mysql client连接mysql进行操作完毕后空闲300秒后断开默认是28800单位秒即8个小时。
innodb_thread_concurrency64 此参数用来设置innodb线程的并发数默认值为0表示不被限制若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍如果超过配置并发数则需要排队这个值不宜太大不然可能会导致线程之间锁争用严重影响性能。
innodb_buffer_pool_size40G innodb存储引擎buffer pool缓存大小一般为物理内存的60%-70%。
innodb_lock_wait_timeout10 行锁锁定时间默认50s根据公司业务定没有标准值。
innodb_flush_log_at_trx_commit1 redo log 保存策略
sync_binlog1 binlog 保存策略
sort_buffer_size4M 每个需要排序的线程分配该大小的一个缓冲区。增加该值可以加速ORDER BY 或 GROUP BY操作。 sort_buffer_size是一个connection级的参数在每个connectionsession第一次需要使用这个buffer的时候一次性分配设置的内存。 sort_buffer_size并不是越大越好由于是connection级的参数过大的设置高并发可能会耗尽系统的内存资源。例如500个连接将会消耗500*sort_buffer_size(4M)2G。
join_buffer_size4M 用于表关联缓存的大小和sort_buffer_size一样该参数对应的分配内存也是每个连接独享。
二、8.0新特性
1、新增降序索引 2、group by 不在隐式排序 3、增加隐藏索引 4、新增函数索引 5、innodb存储引擎select for update跳过锁等待 对于select ... for share(8.0新增加查询共享锁的语法)或 select ... for update 在语句后面添加NOWAIT、SKIP LOCKED语法可以跳过锁等待或者跳过锁定。 在5.7及之前的版本select...for update如果获取不到锁会一直等待直到innodb_lock_wait_timeout超时。 在8.0版本通过添加nowaitskip locked语法能够立即返回。如果查询的行已经加锁那么nowait会立即报错返回而skip locked也会立即返回只是返回的结果中不包含被锁定的行 6、新增innodb_dedicated_server自适应参数 能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_sizeinnodb_log_file_size等参数会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题前提是服务器是专用来给MySQL数据库的如果还有其他软件或者资源或者多实例MySQL使用不建议开启该参数不然会影响其它程序 7、undo文件不在使用系统表空间就是不存在ibdata1文件中有单独的undo_001文件中 8、死锁检测控制可以不开启死锁检测,默认还是开启 9、binlog日志过期时间精确到秒 10、新增窗口函数不用group by就能使用聚合函数 11、默认字符集由latin1变为utf8mb4 在8.0版本之前默认字符集为latin1utf8指向的是utf8mb38.0版本默认字符集为utf8mb4utf8默认指向的也是utf8mb4。 12、系统表全部换成innodb表 13、元数据存储变动 MySQL 8.0删除了之前版本的元数据文件例如表结构.frm等文件全部集中放入mysql.ibd文件里 14、解决5.7自增主键的bug 5.7自增主键不持久化每次都查询表中最大的id1去生成这样如果删除最大id重启数据库再插入记录id会是之前有过的还有一种情况如果修改自增主键mysql也是感知不到的有可能会主键冲突 5.8解决这个问题主键值持久在redolog中修改主键mysql也能感知到解决了这个bug 15、DDL原子化 比如之前drop table t1,t2 执行不支持事务也是因为5.8把系系统表都改为innodb的原因drop可以支持事务 16、参数修改持久化 MySQL 8.0版本支持在线修改全局参数并持久化通过加上PERSIST关键字可以将修改的参数持久化到新的配置文件mysqld-auto.cnf中重启MySQL时可以从该配置文件获取到最新的配置参数。set global 设置的变量参数在mysql重启后会失效
三、基础SQL (1)用户 新建用户 create user name identified by password; 更改密码 set password for namepassword(mima); 查看用户权限 show grants for name; 给name用户test数据库的所有权限 grant select on db_name.* to name; 去除权限
revoke select on db_name.* to name; 登录到MySQL服务器 mysql -u root -p 连接到指定的MySQL服务器 mysql -u root -p datebase_name 登录到远程MySQL服务器 mysql -h remote_mysql_host_ip -u root -p
(2)数据库 查看数据库 show databases; 创建数据库 create datebase db_name; 使用数据库 use db_name; 删除数据库 drop datebase db_name; (3)表 创建表 CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键id, age int(11) NOT NULL COMMENT 年龄, name varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 名字, PRIMARY KEY (id) USING BTREE, UNIQUE INDEX age(age) USING BTREE, INDEX name(name) USING BTREE ) ENGINE InnoDB AUTO_INCREMENT 1 CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT Dynamic; 复制表 create table test2 select * from test; 部分复制 create table test2 select id, name from test; 创建临时表 create temporary table test3(和创建普通表一样) 查看数据库中可用的表 show tables; 查看表结构 describe test; show columns in test; 增 insert into test (age, name) values(1, 1),(1,2); insert into test(age, name) select age, name from test; 删 delete from test where id 1; 改 update test set age 1, name 1 where id 1; 查 select * from test where id 1;(where 也可以是正则表达式) 修改字段结构 alter table test modify column name varchar(32) NOT NULL COMMENT名字; 增加字段 alter table test add column remark(512) NULL DEFAULT NULL COMMENT备注 after name; 删除字段 alter table test drop remark; 创建索引 alter table test add unique uk_age(age); alter table test add index idx_name(name);
删除索引
alter table test drop index index _name; 删除表 drop table if exists test; 清空表数据 truncate table test; 表重命名 alter table test rename new name; rename table oldname to newname; 创建视图 create view test1 as select * from test where id 1; (4)储存过程 创建储存过程 create procedure pro( in num int, out total int) begin select sum(age) into total from test where id num; end; //in 为传入参数out为返回参数 into 为保存变量 储存过程的调用 call pro(13, total); select total; 显示当期的储存过程 show procedure status; 删除存储过程 drop procedure pro; 储存过程例子 delimiter ;; create procedure insert_emp() begin declare i int; set i1; while(i100000)do insert into employees(name,age,position) values(CONCAT(zhuge,i),i,dev); set ii1; end while; end;; delimiter; call insert_emp(); (5)触发器 支持触发器的语句有 delete insert update 创建触发器 在MySQL中可以使用CREATE TRIGGER语句来创建触发器。CREATE TRIGGER语句的基本语法如下 CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_body; 其中trigger_name是触发器的名称trigger_time是触发器的执行时间可以是BEFORE或AFTERtrigger_event是触发器的事件可以是INSERT、UPDATE或DELETEtable_name是触发器所在的表名trigger_body是触发器的SQL语句。 例如下面的触发器在employee表上创建了一个BEFORE INSERT的触发器当有新的员工加入时自动将入职时间设置为当前时间 CREATE TRIGGER set_join_date BEFORE INSERT ON employee FOR EACH ROW BEGIN SET NEW.join_date NOW(); END; (6)系统 查询MySQL系统配置 show variables like %log_error%;
