资源预览内容
第1页 / 共30页
第2页 / 共30页
第3页 / 共30页
第4页 / 共30页
第5页 / 共30页
第6页 / 共30页
第7页 / 共30页
第8页 / 共30页
第9页 / 共30页
第10页 / 共30页
亲,该文档总共30页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
数据库技术分享,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的大小,默认4M或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. show 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 工具查看执行计划,数据库慢SQL,如何分析慢SQL: explain 工具查看执行计划 type执行效率,从左至右,最差到最好,数据库慢SQL,如何分析慢SQL: explain 工具查看执行计划,数据库慢SQL,如何分析慢SQL: explain 工具查看执行计划 样例:,数据库慢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,小样例:,提问交流时间,谢谢观看! 2020,
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号