太原网站设计,深圳外贸10强公司,福州网络营销公司,资源网站的建设方案转载自 Mysql调优你不知道这几点#xff0c;就太可惜了
一、Mysql的逻辑分层
Mysql分为#xff1a;连接层、服务层、引擎层、存储层。
当客户端向服务端发起操作请求的时候#xff0c;执行过程是这样的#xff1a;
1、客户端端与Mysql服务端的连接层建立连接#xff…转载自 Mysql调优你不知道这几点就太可惜了
一、Mysql的逻辑分层
Mysql分为连接层、服务层、引擎层、存储层。
当客户端向服务端发起操作请求的时候执行过程是这样的
1、客户端端与Mysql服务端的连接层建立连接根据请求类型去选择相应的服务层的请求接口。 二、SQL优化
1、Insert的优化
在执行insert操作时经常遇到插入多条数据的时候例如 管理员在同时添加多名用户的时候 在某种数据结构比较复杂的情况下添加数据 在1对n的表结构的情况下经常会遇到这种插入多次子表的情况。那么程序开发人员在开发时候首先想到的是利用for循环进行插入子表数据
第一种情况合并插入
例如我想插入三条利用for循环在循环3次才能执行那么就需要这样执行
insert into st(name,password) values(zhangsan,123456);
insert into st(name,password) values(lisi,123456);
insert into st(name,password) values(wangwu,123456);
怎么才能改进呢mysql的sql有一个语法可以支持如下
insert into st(name,password) values(zhangsan,123456),(lisi,123456),(wangwu,123456);
只有三条可能看不出来那么接下来做一个测试复制了50次遍。 测试结果1以单个插入的的方式插入了50条数据用了0.077s 测试结果2插入了271条数据用了0.077s
插入3241条用了0.044s
一个插入了50条数据用了0.077s一个插入了271条数据用了0.077s。
很明显的对比。
第二种情况事务手动提交
开启事务事务提交改为手动提交。
start TRANSACTION; #先开启事务insert into st(name,password) values(zhangsan,123456);insert into st(name,password) values(lisi,123456);insert into st(name,password) values(wangwu,123456);
COMMIT; #最后提交 测试结果开启事务后插入50条数据用了0.040s比没开启事务插入数据快了将近一半。 第三种情况主键顺序
在插入大批量的数据时建议归类、有序的插入数据。
st(id key,name)
insert into st(name,id) values(zhangsana,10);
insert into st(name,id) values(lisi,3);
insert into st(name,id) values(wangwu,2);
insert into st(name,id) values(wangwub,8);
insert into st(name,id) values(wangwua,34);
优化后进行排序按主键的顺序
insert into st(name,id) values(2,wangwu);
insert into st(name,id) values(3,lisi);
insert into st(name,id) values(8,wangwub);
insert into st(name,id) values(10,zhangsana);
insert into st(name,id) values(34,wangwua); 2、order by的优化
环境准备 准备测试表
CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,username varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,password varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,age varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,sex varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,email varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT23 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;准备测试数据
INSERT INTO user VALUES (1, zhangsan, zhangsan123, 30, 男, zhangsan163.com);
INSERT INTO user VALUES (2, lisi, lisi, 21, 男, lisi163.com);
INSERT INTO user VALUES (3, wangwu, wangwu, 34, 男, wangwu163.com);
INSERT INTO user VALUES (4, zhaoqi, zhaoqi, 32, 男, zhaoqi163.com);
INSERT INTO user VALUES (5, wuliu, wuliu, 33, 男, wuliu163.com);
INSERT INTO user VALUES (6, xiaoming, xiaoming, 51, 男, xiaoming163.com);
INSERT INTO user VALUES (7, xiaozhang, xiaozhang, 23, 男, xiaozhang163.com);
INSERT INTO user VALUES (8, xiaoli, xiaoli, 30, 男, xiaoli163.com);
INSERT INTO user VALUES (9, xiaozheng, xiaozheng, 13, 男, xiaozheng163.com);
INSERT INTO user VALUES (10, xiaohua, xiaohua, 54, 男, xiaohua163.com);
INSERT INTO user VALUES (11, xiaozeng, xiaozeng, 66, 男, xiaozeng163.com);
INSERT INTO user VALUES (12, xiaozhao, xiaozhao, 12, 男, xiaozhao163.com);
INSERT INTO user VALUES (13, xiaoa, xiaoa, 32, 男, xiaoa163.com);
INSERT INTO user VALUES (14, xiaob, xiaob, 13, 男, xiaob163.com);
INSERT INTO user VALUES (15, xiaoc, xiaoc, 32, 男, xiaoc163.com);
INSERT INTO user VALUES (16, xiaod, xiaod, 43, 男, xiaod163.com);
INSERT INTO user VALUES (17, xiaoe, xiaoe, 23, 男, xiaoe163.com);
INSERT INTO user VALUES (18, xiaof, xiaof, 65, 男, xiaof163.com);
INSERT INTO user VALUES (19, xiaog, xiaog, 30, 男, xiaog163.com);
INSERT INTO user VALUES (20, xiaoe, xiaoe, 30, 男, xiaoe163.com);
建立索引
#给id与age建立索引
create index index_user_salary_age on user(age,salary);
#查询表的索引
show index from testdb.user;
两种排序方式
1filesort排序
EXPLAIN select * from user ORDER BY age; 多字段排序 多字段升序或者降序都是走的全表扫描 可以从上面的例子中看出都是Using filesort全部走了全表扫描
效率是比较低的。
2index排序
通过using index排序
#在查询的时候只把加了索引的给查出来
EXPLAIN select id,age,salary from user ORDER BY age DESC,salary DESC; 如果想要其他字段也想走index排序的话也需要给这个字段加上索引 给username加索引 给username加索引后
3多字段排序 一个升序一个降序
EXPLAIN select id,age,salary,username from user ORDER BY age asc,salary DESC; 总结最好不要既有升序也有降序效率会降低。 颠倒排序的位置
如果位置有变化了也会影响效率。排序的位置最好和索引的顺序符合。 在优化排序的相关sql时尽量减少额外的字段排序通过索引直接返回有序的数据。where条件和Order by 使用相同的索引并且Order By的顺序和索引顺序相同并且Order by 的字段都是升序或者都是降序。
Filesort优化
1两次扫描算法
在MySQL4.1之前使用该方式排序。首先根据条件取出排序字段和行指针信息然后在排序区sort buffer中排序如果sort buffer不够则在临时表temporary table中存储排序结果。完成排序之后再根据行指针回表读取记录该操作可能会导致大量随机I/O操作。
2一次扫描算法
一次性取出满足条件的所有字段然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大但是排序效率比两次扫描算法高的多。 MySQL通过比较系统变量max_length_for_sort_data的大小和Qury语句取出的字段总大小来判定是否符合哪种排序算法如果max_length_for_sort_data更大则使用第二种优化之后 三、JOIN的用法
直接用代码表示的话不能很直观的看到效果为了方便我使用图片SQL的形式来讲解。
在实际的数据库应用中我们经常需要从多个数据表中读取数据这时我们就可以使用SQL语句中的连接JOIN在两个或多个数据表中查询数据。
JOIN 按照功能可分为如下三类 INNER JOIN内连接或等值连接获取两个表中字段匹配关系的记录 LEFT JOIN左连接获取左表中的所有记录即使在右表没有对应匹配的记录 RIGHT JOIN右连接与 LEFT JOIN 相反用于获取右表中的所有记录即使左表没有对应匹配的记录。
有以下几个表
学生表(students)student_id,student_name,sno,class_id; 学号姓名学号班级号
班级表(classes)student_id,class_name,class_id; 学号姓名班级 四、额外补充能量 利用存储过程大批量插入数据 1、创建存储过程
DELIMITER inData
CREATE PROCEDURE insertData()
BEGIN
SET i1;
WHILE i10000 DO
INSERT INTO st(name) VALUES(CONCAT(user,i)); #拼接USER 和i值
SET ii1; #防止成为死循环
END WHILE; #结束循环
END inData #结束自定义结束符
DELIMITER ;
2、查询存储过程
show create PROCEDURE insertData ;\G
3、使用存储过程
CALL insertData()
为什么要优化
随着数据量的增大 mysql服务性能差从而直接影响用户体验。
查询时结果显示的很慢等。
哪些方面可以优化
1、优化硬件、操作系统
2、优化MySQL服务器
3、优化DB设计
4、优化SQL语句
5、优化应用
1、优化硬件、操作系统
-CPU内存硬盘
Linux操作系统的内核优化
内核相关参数(/etc/sysctl.conf)
-网络TCP连接
-加快资源回收效率
-增加资源限制
-改变磁盘调度策略 2、优化MySQL服务器
最大连接数
max_connections2000
默认max_connections151 指定MySQL可能的连接数量
#指定MySQL可能的连接数量。当MySQL主线程在很短的时间内得到非常多的连接请求该参数就起作用之后主线程花些时间尽管很短检查连接并且启动一个新线程。
#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。
back_log1024
默认back_log80 索引块的缓冲区大小
key_buffer_size 32M
#索引块的缓冲区大小对MyISAM表性能影响最大的一个参数.决定索引处理的速度尤其是索引读的速度。默认值是8M通过检查状态值Key_read_requests
#和Key_reads可以知道key_buffer_size设置是否合理
默认key_buffer_size8M MySQL执行排序使用的缓冲大小
sort_buffer_size 16M
#是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度首先看是否可以让MySQL使用索引而不是额外的排序阶段。
#如果不能可以尝试增加sort_buffer_size变量的大小。
默认sort_buffer_size256K MYSQL读入缓冲区大小
read_buffer_size 64M
#是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。
#如果对表的顺序扫描请求非常频繁并且你认为频繁扫描进行得太慢可以通过增加该变量值以及内存缓冲区大小提高其性能。
默认read_buffer_size64K
Join操作缓存大小
join_buffer_size 16M
#应用程序经常会出现一些两表或多表Join的操作需求MySQL在完成某些 Join 需求的时候all/index join为了减少参与Join的“被驱动表”的
#读取次数以提高性能需要使用到 Join Buffer 来协助完成 Join操作。当 Join Buffer 太小MySQL 不会将该 Buffer 存入磁盘文件
#而是先将Join Buffer中的结果集与需要 Join 的表进行 Join 操作
#然后清空 Join Buffer 中的数据继续将剩余的结果集写入此 Buffer 中如此往复。这势必会造成被驱动表需要被多次读取成倍增加 IO 访问降低效率。
默认join_buffer_size256K
MySQL的随机读缓冲区大小
read_rnd_buffer_size 32M
#是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如按照排序顺序)将分配一个随机读缓存区。进行排序查询时MySQL会首先扫描一遍该缓冲以避免磁盘搜索
#提高查询速度如果需要排序大量数据可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间所以应尽量适当设置该值以避免内存开销过大。
默认read_rnd_buffer_size256K
缓存排序索引大小
myisam_sort_buffer_size 256M
#当对MyISAM表执行repair table或创建索引时用以缓存排序索引设置太小时可能会遇到” myisam_sort_buffer_size is too small”
myisam_sort_buffer_size102M
缓存空闲的线程以便不被销毁
thread_cache_size 384
#thread_cahe_size线程池线程缓存。用来缓存空闲的线程以至于不被销毁如果线程缓存在的空闲线程需要重新建立新连接
#则会优先调用线程池中的缓存很快就能响应连接请求。每建立一个连接都需要一个线程与之匹配。
默认thread_cache_size10
set global max_connections2000;#设置最大连接数
set global key_buffer_size512*1024*1024;#设置索引块缓冲区大小
set global sort_buffer_size128*1024*1024;#MySQL执行排序使用的缓冲大小
set global read_buffer_size64*1024*1024;#MYSQL读入缓冲区大小
set global join_buffer_size128*1024*1024;#Join操作缓存大小
set global read_rnd_buffer_size32*1024*1024;#MySQL的随机读缓冲区大小
set global myisam_sort_buffer_size256*1024*1024;#缓存排序索引大小
set global thread_cache_size384;#缓存空闲的线程以便不被销毁
set global innodb_buffer_pool_size1000*1024*1024;#内存
#查询SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size
3、优化DB设计
-参照范式进行设计1级范式
1NF 包含分隔符类字符的字符串数据。 名字尾端有数字的属性。 没有定义键或键定义不好的表。 2NF 多个属性有同样的前缀。 重复的数据组。 汇总的数据所引用的数据在一个完全不同的实体中。 BCNF- “每个键必须唯一标识实体每个非键熟悉必须描述实体。 4NF 三元关系实体:实体:实体。 潜伏的多值属性。如多个手机号。 临时数据或历史值。需要将历史数据的主体提出否则将存在大量冗余。 -建立合适的索引
建索引的目的
加快查询速度。 减少I/O操作通过索引的路径来检索数据不是在磁盘中随机检索。 消除磁盘排序索引是排序的走完索引就排序完成
1B-Tree 索引
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型
2Hash 索引
Hash 索引结构的特殊性其检索效率非常高索引的检索可以一次定位不像B-Tree索引需要从根节点到枝节点最后才能访问到页节点这样多次的IO访问所以Hash索引的查询效率要远高于B-Tree索引。
3什么时候可以建索引
1列无重复值可以建索引唯一索引和普通索引
2聚集索引和非聚集索引都可以是唯一的。因此只要列中的数据是唯一的就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。
3建了索引性能得到提高
4区分度高的列可以建索引比如表示男和女的列区分度就不高就不能建索引
5什么时候不可以建索引
1.频繁更新的字段不适合建立索引
2.where条件中用不到的字段不适合建立索引
3.表数据可以确定比较少的不需要建索引
4.数据重复且发布比较均匀的的字段不适合建索引唯一性太差的字段不适合建立索引例如性别真假值
5.参与列计算的列不适合建索引如select * from where amount110
6.查询返回的记录数不适合建立索引
7.查询的排序表记录小于40%不适合建立索引
8.查询非排序表的记录小于 7%不适合建立索引
9.表的碎片较多频繁增加、删除不适合建立索引 4、优化架构设计方案 加缓存 DNS轮询
通过在DNS-server上对一个域名设置多个ip解析来扩充web-server性能及实施负载均衡的技术 。 LVS负载均衡
Linux Virtual Server使用集群技术实现在linux操作系统层面的一个高性能、高可用、负载均衡服务器 。 nginx一个高性能的web-server和实施反向代理的软件
一个高性能的web-server和实施反向代理的软件
