我心中的MySQL,如何优化MySQL

我心中的MySQL DBA,我心中MySQLDBA

  原文网址链接:

  MySQL是一个跨平台的开源关系型数据库管理系统,目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。随着MySQL在互联网上被广泛使用,在数据库领域的地位爆炸式的提升,BAT等各互联网的去IOE,大量的使用MySQL作为核心数据库,MySQL DBA的市场需求量日益剧增,好的MySQL DBA更是各个互联网公司抢手的人才。越来越多的IT人才开始转型做DBA,那么作为一个DBA,首先要明白作为一个DBA的核心目标也是终极目标:保证数据库管理系统的稳定性、安全性、完整性和高性能。

  MySQL DBA大概可以分为两种:一种是开发DBA,一种是运维DBA,这里说的MySQL DBA是指运维DBA。一般意义上的DBA指的是运维DBA,只是负责数据库的运营和维护,包括数据库的安装、监控、备份、恢复等基本工作,而高级或资深 DBA职责比这个大得多,需要覆盖产品从需求设计、测试到交付上线的整个生命周期,在此过程中不仅要负责数据库管理系统的搭建和运维,更要参与到前期的数据库设计,中期的数据库测试和后期的数据库容量管理和性能优化。

  对于运维DBA来说,主要的职责为以下几点:

  1) 监控:对数据库服务运行的状态进行实时的监控,包括数据库会话、数据库日志、数据文件碎片、表空间监控、用户访问监控等,随时发现数据库服务的运行异常和资源消耗情况;输出重要的日常数据库服务运行报表以评估数据库服务整体运行状况,发现数据库隐患;监控对于DBA来说是至关重要的,是DBA的第三只眼 睛,利于监控可以自动处理一些常规的故障,提前发现并解决潜在隐患,监控对于运维是必备的技能,如果你不懂监控,那么你就不是一个称职的运维人员;

  2)备份:制定和实施数据库备份计划,灾难出现时对数据库信息进行恢复,维护适当介质上的存档或者备份数据。对数据库的备份策略要根据实际要求进行更改,数据的日常备份情况进行监控。备份大概分为两种:一种是定时、计划性的备份,包含全备和增量备份,常用的工具一般为mysqldump以及xtrabackup为代表的第三方备份工具;一种是执行DML时更新和删除的备份,这种备份一般有以下几种备份方式:一种为create tablexxx_mmddhhmm select的方式备份到表中,一种是mysqldump加上条件做备份,还有一种是select into oufile等几种方式,防止执行错误时可以快速的恢复。在日常对数据库的操作中,特别作为一个DBA,永远要给自己留一个后手,保证在需要进行数据恢复的时候可以随手拈来。

  3)安全审计:为不同的数据库管理系统用户规定不同的访问权限,以保护数据库不被未经授权的访问和破坏。例如,允许一类用户只能检索数据,而另一类用户可能拥有更新数据和删除记录的权限。

  4)故障处理:对数据库服务出现的任何异常进行及时处理,尽可能避免问题的扩大化甚至中止服务。这之前DBA需要针对各类服务异常,如机房/网络故障、程序bug等问题制定处理的预案,问题出现时可以自动或手动执行预案达到止损的目的。在故障处理中,DBA的反应至关重要,可能在分分钟钟的时间内,小的故障引发大的故障,对业务的影响更大,这个非常考验DBA的应急能力和应变能力。对于常见的故障,比如连接数突然增多、数据库服务器空间的清理、主从同步延迟、CPU使用过高等,可以和研发一起制定相应的预案,并且和监控结合,当达到一定的阀值,使用相应的脚本自动处理,这样在一定程度上减少DBA的工作量、提高故障处理效率,更重要的是可以为DBA争取一定的时间,防止故障升级对业务带来更大的影响。

  5)容量管理:包括数据库规模扩张后的资源评估、扩容、机房迁移、流量调度等规划和具体实施。

  6)数据库性能优化:产品对外提供服务最重要的一点是用户体验,用户体验中非常重要的是产品的可用性和响应速度。而如何用最合理的资源支持产品提供高可用和高速度的用户体验,这也是DBA的重要职责。在多年的DBA生涯中,个人对数据库故障的总结为:80%的甚至更高的故障来自变态的SQL语句,10%左右的来自数据库架构方面设计的不合理,10%左右的来自硬件、OS层面。那么基本的优化重点就就是对SQL的优化,这个需要给研发人员进行MySQL开发规范进行培训,制定严格的SQL准入规则,审核每一条上线的SQL语句,在SQL源头上堵住大部分,然后就是对慢SQL的监控分析,定期的发给研发进行整改,并督促研发在限定的时间内整改。这部分的工作较为繁琐平淡,但是执行好了,可以杜绝很多不必要的故障,保证业务的高效、稳定运行,这一部分工作非常考验DBA的基础理论知识,考验DBA的责任性和工作的毅力。

  个人认为,一个好的DBA所需要具备的基本技能包含:理解数据备份/恢复与灾难恢复、DBA常用工具集的使用、知道如何快速寻找答案、知道如何监控和优化数 据库性能、尽可能实现自动化、容量监控与规划、索引设计、数据库设计、数据库安全性、持续不断地学习和研究新版本、可以单独的编写独立的或者系统的功能脚本。这些技能基本涵盖MySQL基础理论知识、OS层面知识、硬件知识、网络知识、代码编写能力、架构设计规划能力、监控、运维理念、运维自动化等。因此,做一个DBA,不单纯的数据库层面的,基本知识涵盖很多层面,因此可以说DBA综合知识极高的复合型专业人才。

  性格决定命运,态度决定一切,DBA也是这样,性格决定高度,态度决定层次。有些DBA做了很多年,依然是边缘人,成不了技术中坚,归根到底可能与本身的性格有关,不思进取,工作不主动,说多少做多少,做事邋遢,不上心,能凑合就凑合,时间观念不强等,有一部分人认为公司一个月给我1000块的工资,我干嘛去干1001块的活?只要不出人为事故就行,天灾人祸自认倒霉,这样的性格在个人职业生涯中不会有突出的成就。遇到过有些DBA都出书了,理论等技能肯定是没问题的,但是做事毛糙,考虑问题时不全面,时不时的出点小事故,有时候做事总觉得过得去就行,面试的时候给人的印象可能是极好的,可是工作起来就能看出问题了,每在一个公司做DBA业务呆的时间都不长,毕竟群众的眼睛是雪亮的,每个公司、每个领导都是需要能干活、能把活干漂亮的人。一个优秀的DBA,我心中的DBA应该是具有完美主义的人,应该具备的硬性素质包含工作认真细致,勤于思考,良好的沟通能力、具有团队合作精神,自我驱动能力或者说工作主动性强,坚韧的意志力和沉着的心态,好奇心和自信心强等;基本技能是基础,DBA必备的,只是基础而已,如果谈优秀的话,那么良好的性格和态度决定你可以在职业生涯中能走多远。

  大道理大家都懂,真正能实实在在落实到实际的不多,也正是这些为数不多的人,成为业内的中坚,所谓的大牛!最后想说的是,各个行业中没有最牛,只有更牛,学无止境,在学习和实践中完善、强化自己的各方面能力,不断的提升自己。踏踏实实做事,堂堂正正做人,改变不了别人,尽最大的努力,做最好的自己!!

DBA,我心中MySQLDBA 原文网址链接: MySQL是一个跨平台的开源关系型数据库管理系统,目前...

如何优化MySQL insert性能,优化mysqlinsert

对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一个很重要的问题就是插入时间长。我们就有一个业务系统,每天的数据导入需要4-5个钟。这种费时的操作其实是很有风险的,假设程序出了问题,想重跑操作那是一件痛苦的事情。因此,提高大数据量系统的MySQL insert效率是很有必要的。

图片 1

经过对MySQL的测试,发现一些可以提高insert效率的方法,供大家参考参考。

  1. 一条SQL语句插入多条数据。

常用的插入语句如:

  1. INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);
  2. INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);

修改成:

  1. INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因有两个,一是减少SQL语句解析的操作, 只需要解析一次就能进行数据的插入操作,二是SQL语句较短,可以减少网络传输的IO。

这里提供一些测试对比数据,分别是进行单条数据的导入与转化成一条SQL语句进行导入,分别测试1百、1千、1万条数据记录。

图片 2

  1. 在事务中进行插入处理。 把插入修改成:

  2. START TRANSACTION;

  3. INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);
  4. INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);
  5. ...
  6. COMMIT;

使用事务可以提高数据的插入效率,这是因为进行一个INSERT操作时,MySQL内部会建立一个事务,在事务内进行真正插入处理。通过使用事务可以减少创建事务的消耗,所有插入都在执行后才进行提交操作。

这里也提供了测试对比,分别是不使用事务与使用事务在记录数为1百、1千、1万的情况。

图片 3

性能测试: 这里提供了同时使用上面两种方法进行INSERT效率优化的测试。即多条数据合并为同一个SQL,并且在事务中进行插入。

图片 4

从测试结果可以看到,insert的效率大概有50倍的提高,这个一个很客观的数字。

注意事项:

1. SQL语句是有长度限制,在进行数据合并在同一SQL中务必不能超过SQL长度限制,通过max_allowed_packet配置可以修改,默认是1M。

2. 事务需要控制大小,事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项,超过这个值会日志会使用磁盘数据,这时,效率会有所下降。所以比较好的做法是,在事务大小达到配置项数据级前进行事务提交。

insert性能,优化mysqlinsert 对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一个很重要的问题就是插入时间...

深度解析mysql登录原理

使用mysql数据库的第一步必然是建立连接登录,然后在上面执行SQL命令。无论是通过mysql的客户端,还是通过C-API,JDBC标准接口连接数据库,这个过程一定少不了。今天我们聊一聊mysql登陆具体过程,里面会涉及client与server的交互,并通过tcpdump抓包给大家展现这一过程。

 

TCP握手协议

 

远程连接数据库,mysql采用TCP协议通信,第一步是建立连接,即TCP的3次握手。mysql server端有一个监听线程等待client请求,client发起请求后,首先发一个sync包到服务端,服务端发一个ack包作为对客户端sync包的响应,同时发一个sync包到客户端,最后客户端再发一个ack包作为对服务端的响应。通过3次握手,TCP连接才算真正建立起来,这个时候mysql服务端会分配一个连接供客户端使用。记住,TCP的3次握手都是由TCP的协议栈完成,应用程序并无感知。但是,到目前为止,整个mysql连接过程还只完成了第一步--建立TCP连接。

 

下面附上TCP建立连接和断开连接的原理图。

图片 5

MYSQL握手协议

 

TCP连接建立成功后,mysql客户端与mysql服务端开始进行通讯,进行mysql认证过程。(1)服务端首先会发一个握手包到客户端,(2)然后客户端向服务端发送认证信息(用户名,密码等),(3)服务端收到认证包后,会检查用户名与密码是否合法,并发送包告知客户端认证信息。如果合法,则登陆成功,否则,登陆失败。连接报错。有时候,我们通过show processlist看到User处于unauthenticated user ,这说明此时连接过程正处于第1步和第2步之间,服务端等待客户端发认证信息的过程中。

 

15922528 unauthenticated user   10.xx.2.74:53216 NULL   Connect NULL   Reading from net NULL

15923418 unauthenticated user   connecting host NULL   Connect NULL   login   NULL 

tcpdump抓包验证

 

下面我们通过tcpdump抓网络包来验证我们的原理。由于测试在生产环境中进行,为了避免生产网段的IP泄露,对IP作了替换,但不影响分析过程。具体而言,10.aa.zz.142.10556代表客户端,10.bb.yy.104.3306代表服务器端,3306是服务器的监听端口号。

 

(1).在客户端上打开tcpdump命令,监听与10.bb.yy.104.3306的通信网络包,命令如下:

 

 

tcpdump -S -nn -tttt -i eth0 host 10.bb.yy.104 and port 3306 and tcp -c 100

-S 将tcp的序列号以绝对值形式输出,而不是相对值。 

-nn 不进行端口名称的转换。

-tttt 在每一行中输出由date处理的默认格式的时间戳。

-i eth0 指定监听的网络接口

host 10.bb.yy.104 and port 3306  设置监听10.bb.yy.104:3306的网络包

-c 100 表示监听100包就结束。

 

(2).在客户端上,利用mysql命令远程连接服务端10.bb.yy.104,

 

mysql –h10.bb.yy.104 –P3306 –uxxx –pxx

 

登陆成功后,然后直接执行exit,退出

 

(3)分析tcpdump抓取的网络包,重点分析建立TCP连接,MYSQL认证和TCP断开连接的过程。如下图,图中第1部分是TCP连接建立的过程,第2部分是MYSQL认证的过程,第3部分是登陆成功后,发送基本元数据信息的过程,第4部分是断开连接的过程。通过图中的标示,我们可以清晰的看到TCP建立连接的3次握手,MYSQL认证以及TCP断开连接的4次挥手过程。

图片 6

建立连接

 

这个过程主要体现在第一部分,客户端10.aa.zz.142.10556,首先发一个编号为1491894492的SYN包,服务端收到后,发送了1491894492 1的ACK包,并发送了一个2727774925的SYN包,最后客户端再发送一个2727774925 1的包进行应答。

 

MYSQL认证

 

这个过程主要体现在第二部分,服务端10.bb.yy.104:3306首先发一个认证包给客户端,然后客户端再发送包含用户密码的认证包给服务器,验证成功后,服务端最后给客户端一个应答,那么整个认证过程就结束了,至于第3部分是服务端与客户端相互发送的一些元数据信息,比如版本信息之类的。

 

断开连接

 

这个过程主要体现在第四部分,客户端发起exit命令时,开始触发这个动作。客户端首先发一个编号为1491894724的FIN包,然后服务器发送一个1491894724 1的ACK包作为应答,并发送一个编号为2727775120的FIN包,最后客户端发送2727775120 1作为应答,整个过程结束。

 

数据包标记解析

 

S=SYN   发起连接标志,一般用于建立TCP连接

 

P=PUSH 传送数据标志,一般用于传输数据

 

F=FIN   关闭连接标志,一般用于关闭TCP连接

 

ack     表示应答包

 

RST= RESET  异常关闭连接

 

.表示没有任何标志

 

源码实现

 

用于MYSQL认证代码主要集中在函数native_password_authenticate中,具体调用层次为:login_connection->check_connection->acl_authenticate->do_auth_once->native_password_authenticate,函数逻辑很简单,就是调用write_packet往客户端发一个认证包,然后调用read_packet等待客户端返回包含用户名、密码等信息的包,最后解析包中的信息进行密码验证,成功后,会在调用Protocol::send_ok发一个认证成功网络包,这个过程可以在图中的第二步全部体现。底层socket通信代码主要集中在sql/net_serv.cc中,具体而言读采用接口my_net_read,写采用接口my_net_write。

 

问题

 

(1).Unix socket方式登陆与TCP方式登陆有什么区别和联系?

 

Unix socket是实现进程间通信的一种方式,mysql支持利用Unix socket来实现客户端-服务端的通信,但要求客户端和服务端在同一台机器上。对于unix socket而言,同样也是一种套接字,监听线程会同时监听TCP socket和Unix socket,接受到请求然后处理,后续的处理逻辑都是一致的,只不过底层通信方式不一样罢了。

 

mysql  -h127.0.0.1 –P3306 –uxxx –pxxx  [TCP通讯方式]

mysql  -uxxx –pxxx –S/usr/mysql/mysql.sock  [unix socket通信方式]

(2).监听socket是否与通信socket公用一个端口?

 

我们知道,服务端一直有一个监听socket在3306端口监听,等待新进来的客户请求,一旦一个请求过来,服务端会重新创建一个新的通信socket,这个新的socket专门用于与这个客户通信,而监听socket则继续监听。虽然是2个套接字,但监听socket和通信socket都是同一个端口,通过netstat可以确认这个问题。

 

(3).连接超时参数connect_timeout在何时作用?

 

这个参数实质就是在MYSQL认证过程起作用,如果在这个过程中,客户端超过connect_timeout时间仍然没有发送密码认证包过来,则会主动断开连接。

使用mysql数据库的第一步必然是建立连接登录,然后在上面执行SQL命令。无论是通过mysql的客户端,还是通过C-API,...

本文由ca88手机版登录发布于亚洲城ca88手机版官网,转载请注明出处:我心中的MySQL,如何优化MySQL

TAG标签: ca88手机版登录
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。