0%

MySQL系统调优

可以使用下面几个工具来做基准测试:

MySQL索引结构

mysql的查询速度主要看磁盘io的时间,所以需要尽可能的减少磁盘io的次数,这也是为什么会选用数才作为存储结构的原因O(logN)

MySQL内置的存储引擎对各种索引技术有不同的实现方式,包括:B+Tree,R-Tree索引、hash索引、full-text全文索引

  • Innodb中使用的是B+Tree索引,并不是找到一个给定键值的具体行,而是找到被查找的数据行所在的页,然后通过把页读入到内存,在内存中进行查找,最后得到要查找的数据
  • MyISAM中使用的是FullText全文索引, InnoDB在5.6之后支持,5.7之后使用ngram插件开始支持中文
  • Memory中使用的是Hash索引

索引结构

为什么不使用二叉树

二叉树可能会出现链表的方式存储,这样磁盘IO次数就变成了O(N)

为什么不使用红黑树

红黑树确实可以保证O(logN)的次数,但是其只有两个子节点,数据大的话树的深度太高了

为了减少深度,采用了B树,B树可以有N个孩子节点

B-Tree

阅读全文 »

内存溢出和内存泄漏

内存溢出

内存溢出(OutOfMemoryError,简称OOM)是指没有空闲内存,且垃圾收集器也无法提供更多的内存

出现OOM的原因有两种:

  • java虚拟机设置的堆内存不够
  • 代码中创建了大量大对象,并且长时间不能被垃圾收集器收集

内存泄漏

可达性分析算法来判断对象是否是不再使用的对象,本质是判断一个对象是否还被引用,内存泄漏是指对象不会被程序用到了,但是GC又无法回收这些对象的情况,内存泄漏会逐渐的占用内存,直至耗尽所有内存,最终出现OOM

阅读全文 »

MySQL查询缓存

MySQL在查询的时候首先会查询缓存,如果缓存命中的话就直接返回结果,不需要解析sql语句,也不会生成执行计划,更不会执行;如果没有命中缓存,则再进行SQL解析以及进行查询,并将结果返回(也同时将结果放入到缓存中)

MySQL查询过程

缓存查找是利用对大小写敏感的哈希查找来实现的,Hash查找只能进行全值查找(sql完全一致),如果缓存命中,检查用户权限,如果权限允许,直接返回,查询不被解析,也不会生成查询计划,由于在缓存更新的时候会对数据加锁,所以对于读写比较频繁的系统,建议关闭缓存

阅读全文 »

MySQL系统配置

内存相关

  • sort_buffer_size 定义了每个线程排序缓存区的大小,当索引无法满足需要的顺序信息时会用到,执行计划中出现using filesort,MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存(直接分配该参数的全部内存)(innodb_sort_buffer_sizemyisam_sort_buffer_sizesort_buffer_size),可以根据sort_scan、sort_rows、sort_range、sort_merge_passes状态来检查执行了多少个排序操作

    通过show global status like 'sort%'来查看排序情况。

    sort_merge_passes的步骤如下:mysql先会尝试在内存中进行排序,使用的内存大小sort_buffer_size决定,如果该值不够大则把所有的记录读到内存中,mysql会把每次在内存中排序的结果存到临时文件中,等mysql找到所有记录后,再把临时文件中的记录做一次排序,此时会增加sort_merge_passes的值

  • join_buffer_size 定义了每个线程所使用的连接缓冲区的大小,如果一个查询关联了多张表,当索引无法满足连接条件时,会用到这个缓冲区,执行计划中如果有using join buffer,可以通过检查select_scan状态(表示第一张表执行完整扫描的连接数量)和select_full_range_join状态(表示使用范围搜索的连接数量)来判断是否需要设置更大的值

  • read_buffer_size 用于表的顺序扫描,表示每个线程分配的缓冲区大小,在进行全表扫描时,mysql会按照数据的存储顺序依次读取数据块,每次读取的数据块首先会暂存在read_buffer_size中,当buffer空间被写满或者全部数据读取结束后,再将buffer中的数据返回给上层调用者,提高效率,其必须是4k的倍数

  • read_rnd_buffer_size MySQL每个线程的随机读缓冲区大小,MySQL有查询按任意顺序读取行需要时会为其分配内存,进行排序查询时,利用该缓冲区来暂存读取的数据,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值

上述四个参数配置是按照每个线程进行分配的,所分配的内存还要乘以线程数

  • innodb_buffer_pool_size 定义了Innodb所使用缓存池的大小,存储数据和索引页,对其性能十分重要,必须足够大,但是过大时,使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中,这个值越大越好,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。物理内存的60%~70%,典型的值是5-6GB(8GB内存),20-25GB(32GB内存),100-120GB(128GB内存)

  • innodb_additional_mem_pool_size:InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,当数据库对象非常多的时候,适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率,当过小的时候,MySQL会记录Warning信息到数据库的错误日志中,这时就需要该调整这个参数大小,小于2G内存的机器,推荐20M;32G内存以上推荐为100M

  • innodb_buffer_pool_instances 缓冲池实例个数

  • key_buffer_size MyISAM为索引键设置缓冲区大小,使用时才真正分配,索引缓冲区所有线程共享,增加索引缓冲区可以得到更好处理的索引,由于数据是依赖存储操作系统缓存的,所以要为操作系统预留更大的内存空间

    即使开发使用的表全部是Innodb表,也要为MyISAM预留内存,因为MySQL系统使用的表仍然是MyISAM表

    通过 show global status like 'key_read%'查看key_buffer_size的使用情况

    未命中缓存的概率 key_cache_miss_rate = Key_reads / Key_read_requests * 100%,该值在0.1%以下都很好,如果在0.01%以下,则可适当减少key_buffer_size

阅读全文 »