MYSQL数据库技术分享

数据库技术分享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的性能，该参数设定了logbuffer的大小，默认4M或8M，一般设置1秒的写入量即可key_buffer_sizemyisam引擎存放索引数据文件的，因为现在我们默认都使用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连接级别的，每个连接都需要分配这么多，不要太大1Mmax_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，logbuffer将每秒一次地写入logfile中，并且logfile的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。=1，每次事务提交时MySQL都会把logbuffer的数据写入logfile，并且flush(刷到磁盘)中去.=2，每次事务提交时MySQL都会把logbuffer的数据写入logfile.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次flush(刷到磁盘)操作。0性能最好，1数据最安全数据库参数优化数据库参数优化如何查看参数设置：数据库慢SQL如何知道有慢SQL了：1.设置慢SQL参数，查看慢日志数据库慢SQL如何知道有慢SQL了：1.查看慢日志：Time运行时间点，User@host运行账户和服务器IP，Query_time这条sql运行了多长时间数据库慢SQL如何知道有慢SQL了：Mysqldumpslow工具：参数-s,是表示按照何种方式排序，c、t、l、r分别是按照语句query次数、querytime、locktime、rowssent的总数来排序数据库慢SQL如何知道有慢SQL了：2.showprocesslist/showfullprocesslist;id#ID标识，要kill一个语句的时候很有用user#当前连接用户host#显示这个连接从哪个ip的哪个端口上发出db#数据库名command#连接状态，一般是休眠（sleep），查询（query），连接（connect）time#连接持续时间，单位是秒state#显示当前sql语句的状态info#显示这个sql语句State包括（Checkingtable，Closingtables，Copyingtotmptableondisk，Creatingtmptable，Killed。。。）数据库慢SQL如何知道有慢SQL了：数据库慢SQL如何分析慢SQL：1.explain工具查看执行计划数据库慢SQL如何分析慢SQL：1.explain工具查看执行计划数据库慢SQL如何分析慢SQL：1.explain工具查看执行计划数据库慢SQL如何分析慢SQL：1.explain工具查看执行计划type重要的项,显示连接使用的类型说明system表仅有一行。这是const连接类型的一个特例。constconst用于用常数值比较PRIMARYKEY时。当查询的表仅有一行时,使用System。ref非唯一索引扫描，返回匹配某个单独值得所有行，常见于非唯一索引扫描eq_ref唯一性索引扫描，对于每个索引键，表中只有一条记录匹配，常见于主键或唯一索引扫描allMysql会遍历全表index遍历整个索引树进行查找range索引范围扫描，常见于between数据库慢SQL如何分析慢SQL：1.explain工具查看执行计划type执行效率，从左至右，最差到最好数据库慢SQL如何分析慢SQL：1.explain工具查看执行计划数据库慢SQL如何分析慢SQL：1.explain工具查看执行计划样例：项说明extra列出不适合在其他列中显示的重要信息数据库慢SQL如何分析慢SQL：1.explain工具查看执行计数据库慢SQL如何分析慢SQL：2.Showprofileexplain是看执行计划，了解SQL是如何被拆解运行的，那么profile则是查看一条SQL到底是在哪里损失了性能。数据库慢SQL如何分析慢SQL：2.Showprofile数据库慢SQL如何分析慢SQL：2.Showprofile数据库慢SQL优化慢SQL原则：1.尽量简单，拆分大SQL2.建立合适的索引3.避免对索引列进行计算4.WHERE声明类型要和比较类型一致5.先优化运行次数最多的SQL数据库慢SQL小样例：提问交流时间