MYSQL数据库技术分享.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2019/12/5,#,数据库技术分享,1,内容,提要,数据库参数介绍,慢,SQL,定位与分析,SQL,优化,FAQ,数据库参数优化,MYSQL,参数分成几块：,Client-,会被,MySQL,客户端应用读取,-,端口设置等,mysqld-MySQL,服务端设置,-,内存连接等,mysqldump-,运行,mysqldump,的时候会读取,.,数据库参数优化,内存部分：,innodb_buffer_pool_size,缓冲池字节大小，,InnoDB,缓存表和索引数据的内存区域，我们做的,99%,的操作都要跟这个打交道，理论上缓冲越大，效率越高，你设置越大,你在存取表里面数据时所需要的磁盘,I/O,越少,.,#,在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的,80%,(Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total),innodb_additional_mem_pool_size,这个参数用来设置,InnoDB,存储的数据目录信息和其它内部数据结构的内存池大小。应用程序里的表越多，你需要在这里分配越多的内存。对于一个相对稳定的应用，这个参数的大小也是相对稳定的，也没有必要预留非常大的值。如果,InnoDB,用光了这个池内的内存，,InnoDB,开始从操作系统分配内存，并且往,MySQL,错误日志写警告信息。默认值是,1MB,，当发现错误日志中已经有相关的警告信息时，就应该适当的增加该参数的大小,innodb_log_buffer_size,磁盘速度是很慢的，直接将,log,写道磁盘会影响,InnoDB,的性能，该参数设定了,log buffer,的大小，默认,4,M,或,8M,，一般设置,1,秒的写入量即可,key_buffer_size,myisam,引擎存放索引数据文件的，因为现在我们默认都使用,INNODB,所以很少用,数据库参数优化,文件部分：,datadir=/data/mysqldata,设置我们的数据库文件存放位置,character-set-server=utf8,设置字符集,innodb_file_per_table,一个表一个文件独立，不设置，所有数据文件一个，在表数量很多的环境下,I/O,负载会很高,数据库参数优化,连接部分：,skip-name-resolve,都会向,DNS,做反向地址查询，查不到只有等超时失败后才能连接，这个选项就是禁止,DNS,反响查询,back_log,参数的值指出在,MySQL,暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值,max_connections,数据库能承受最大连接数,sort_buffer_size,连接级别的，每个连接都需要分配这么多，不要太大,1M,max_allowed_packet,设置,Server,接受的数据包的大小，,粗略的估算一下一个查询返回的最大大小,thread_cache_size,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能,.256-512,数据库参数优化,慢日志：,slow_query_log,=ON,开启慢日志,long_query_time,设置运行时间超过几秒的,SQL,会被记录下来,slow_query_log_file,慢日志位置名称,log_queries_not_using_indexes,是否记录没有使用索引的,sql,数据库参数优化,磁盘,IO,：,以下,两个参数是控制,MySQL,磁盘写入策略以及数据安全性的关键参数,sync_binlog,当事务提交之后，,MySQL,以什么频率进行磁盘同步指令刷新,binlog_cache,中的信息到磁盘。,=,0,当事务提交之后，,MySQL,不做,fsync,之类的磁盘同步指令刷新,binlog_cache,中的信息到磁盘，而让,Filesystem,自行决定什么时候来做同步，或者,cache,满了之后才同步到磁盘,=,n,当每进行,n,次事务提交之后，,MySQL,将进行一次,fsync,之类的磁盘同步指令来将,binlog_cache,中的数据强制写入磁盘。,0,性能最好，,1,数据最安全，在繁忙系统两者写性能会相差,3-5,倍,innodb_flush_log_at_trx_commit,以什么方式刷新日志到磁盘,=,0,，,log buffer,将每秒一次地写入,log file,中，并且,log file,的,flush(,刷到磁盘,),操作同时进行,.,该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。,=,1,，每次事务提交时,MySQL,都会把,log buffer,的数据写入,log file,，并且,flush(,刷到磁盘,),中去,.,=,2,，每次事务提交时,MySQL,都会把,log buffer,的数据写入,log file.,但是,flush(,刷到磁盘,),操作并不会同时进行。该模式下,MySQL,会每秒执行一次,flush(,刷到磁盘,),操作。,0,性能最好，,1,数据最安全,数据库参数优化,数据库参数优化,如何查看参数设置：,数据库慢,SQL,如何知道有慢,SQL,了：,设置慢,SQL,参数，查看慢日志,数据库慢,SQL,如何知道有慢,SQL,了：,查看慢日志：,Time,运行时间点，,Userhost,运行账户和服务器,IP,，,Query_time,这条,sql,运行了多长时间,数据库慢,SQL,如何知道有慢,SQL,了：,Mysqldumpslow,工具,：参数,-s,是表示按照何种方式排序，,c、t、l、r,分别是按照语句,query,次数、,query time、lock time、rows sent,的总数来排序,数据库慢,SQL,如何知道有慢,SQL,了：,2.s,how processlist/show full processlist;,id#ID,标识，要,kill,一个语句的时候很有用,user#,当前连接用户,host#,显示这个连接从哪个,ip,的哪个端口上发出,db#,数据库名,command#,连接状态，一般是休眠（,sleep,），查询（,query,），连接（,connect,）,time#,连接持续时间，单位是秒,state#,显示当前,sql,语句的状态,info#,显示这个,sql,语句,State,包括（,Checking table,，,Closing tables,，,Copying to tmp table on disk,，,Creating tmp table,，,Killed,。,）,数据库慢,SQL,如何知道有慢,SQL,了：,数据库慢,SQL,如何分析慢,SQL,：,explain,工具查看执行计划,数据库慢,SQL,如何分析慢,SQL,：,explain,工具查看执行计划,数据库慢,SQL,如何分析慢,SQL,：,explain,工具查看执行计划,数据库慢,SQL,如何分析慢,SQL,：,explain,工具查看执行计划,type,重要的项,显示连接使用的,类型,说明,system,表仅有,一行。,这是,const,连接类型的一个特例。,const,const,用于用常数值比较,PRIMARY KEY,时。,当查询,的表仅有一行时,使用,System,。,ref,非唯一索引扫描，返回匹配某个单独值得所有行，常见于非唯一索引扫描,eq_ref,唯一性索引扫描，对于每个索引键，表中只有一条记录匹配，常见于主键或唯一索引扫描,all,Mysql,会遍历全表,index,遍历整个索引树进行查找,range,索引范围扫描，常见于,between,数据库慢,SQL,如何分析慢,SQL,：,explain,工具查看执行计划,type,执行效率，从左至右，最差到最好,数据库慢,SQL,如何分析慢,SQL,：,explain,工具查看执行计划,数据库慢,SQL,如何分析慢,SQL,：,explain,工具查看执行计划,样例：,项,说明,extra,列出不适合在其他列中显示的重要信息,数据库慢,SQL,如何分析慢,SQL,：,explain,工具查看执行计,数据库慢,SQL,如何分析慢,SQL,：,Show profile,explain,是看执行计划，了解,SQL,是如何被拆解运行的，那么,profile,则是,查看一条,SQL,到底是在哪里损失了性能。,数据库慢,SQL,如何分析慢,SQL,：,Show profile,数据库慢,SQL,如何分析慢,SQL,：,Show profile,数据库慢,SQL,优化慢,SQL,原则：,尽量简单，拆分大,SQL,建立合适的索引,避免对索引列进行计算,WHERE,声明类型要和比较类型一致,先优化运行次数最多的,SQL,数据库慢,SQL,小样例：,提问交流时间,