1、CPU
数据库一般是IO密集型的,其性能瓶颈在于IO。数据库服务器的选购偏内存,CPU可以适当降低其要求。
InnoDB存储引擎相关后台线程:
show engine innodb status\G;
InnoDB insert buffer thread 线程主要是更新非唯一索引的索引数据,避免频繁随机的读写索引文件。(索引数据存放在表默认表空间中)
log thread : InnoDB的默认指的就是redo日志,log thread 就是写 redo 日志的线程。
read thread | write thread 读、写日志线程(文件 《----》内存)
根据CPU适当调节innodb_read_io_threads与innodb_write_io_threads
2、内存
内存的大小最能直接反应数据库的性能。InnoDB存储引擎既缓存数据,又缓存索引(存放在数据文件中,表空间),并放入一个很大的缓冲池中(InnoDB Buffer Pool)。 【innodb_buffer_pool的大小如果能等于数据文件的大小,则能缓存所有的数据文件,性能会得到极高的提升】(innodb_buffer_pool_size),如何判断当前数据库的内存是否达到了瓶颈了呢。
mysql提供 show global status 命令查看系统运行状况监控,(INFORMATION_SCHEMA GLOBAL_STATUS临时表)。
关注如下参数:
innodb_buffer_pool_reads: 从物理磁盘读取页的次数
innodb_buffer_pool_read_ahead:预读的次数
innodb_buffer_pool_read_ahead_evicted:预读的页,但没有被读取就从缓冲区被替换的也的数量,一般用来判断预读的效率。
innodb_buffer_pool_read_requests:从缓冲池中读取页的次数(可以理解为缓冲命中次数)
innodb_data_read:总共读入的字节数
innodb_data_reads:发起读请求的次数
预读的效率:(1 - (innodb_buffer_pool_read_ahead / innodb_buffer_pool_read)) * 100 %
InnoDB缓存命中率: innodb_buffer_pool_read_requests / ( innodb_buffer_pool_read_requests + innodb_buffer_pool_reads + innodb_buffer_pool_read_ahead ) * 100 %,
InnoDB缓存命中率通常要保持在99%以上,才表明内存无压力,这样性能杠杠的。
平均每次读取的字节数= innodb_data_read/innodb_data_reads
内存大于数据文件,并不意味着没有IO操作,Innodb会定时刷脏(将被修改的页刷写到数据文件),并且每次
事务提交,会写redo日志。
3、磁盘对数据库性能的影响
数据库是IO密集型应用,推荐使用raid10(磁盘冗余阵列)
4、SQL语句优化
SQL语句的优化点基本上会落到是否能利用索引这个问题上来。
1)养成好的SQL使用习惯
a、禁止使用 select *,应写需要的数据字段。
b、尽量使用exists代替in
2)尽量利用索引来加速SQL数据的查询效率
以下情况将无法利用索引:
a、不支持 != 表达式,如果使用不等于,无法利用到索引。索引的存储结构是B+树,支持等值查询与范围查询。
b、like '%a%'无法利用索引,但'a%'可以使用索引。
原因分析,索引的存储结构是B+树,叶子节点存放数据,数据本身是排序的,输入值必须是一个明确的值,方便对比,a%开头的查询,是可以用a当查询关键字首先定位记录的,但%a%无法定位。
c、复合索引,查询条件必须包含第一列(最左列)
道理是一样的,索引的优势就是天生是排序的,查询类似于二分查询(每比较一次,就会缩短查询范围)。
例如有两列的复合索引类似( 1, 1 ) ( 1, 3 ) ( 2, 1 ) ( 2, 5 ) ( 3, 1 ) 只有包含第一列,整个二元序列就是有序的,但如果剔除第一列,那序列为 1,3,1,5,1,无序序列的查找,复杂度为o(n)。
d、对字段使用表达式或函数允许,将无法使用索引
3)索引建立原则
a、列高选择性(重复率低)
b、频繁查询的列
c、order by 或 group by字段(利用索引的有序性)
5、MySQL执行计划解读
MySQL提供explain查看查询语句的执行计划,使用案例如下:
EXPLAIN SELECT
t.id,
t.order_no,
t.total_price,
t.seller_id,
t.creator,
t.create_time
FROM
(
SELECT
a.id,
a.order_no,
a.total_price,
a.seller_id,
a.creator,
a.create_time
FROM
es_order a
WHERE
a.seller_id = 24
LIMIT 20,
10
) t
INNER JOIN es_order_item b ON b.order_id = t.id
相关字段:id、select_type、table、type、prossible_keys、key、ken_len、ref、rows、Extr
1) id : 执行顺序,数字越大,先执行。
2) select_type: (只是标记为查询类型)
simple : 查询中不包含子查询或union
primary:若查询中包含任何复杂的子查询,最外层查询标记为:PRIMARY
subquery:子查询
derived:驱动表,不是真实的物理表,但不同于临时表
union:
union result
3)type:访问类型,重点关注
取值如下:all、index、range、ref、 eq_ref、 const system、null
all : 全表扫描
index: Full Index Scan,index与all的区别为index只遍历索引树。
例如:(select a.id from es_order a)
range:索引范围扫描,常见于索引字段上的 between > < like( like 'a%')
ref : 非唯一索引的等值匹配,例如a.seller_id=24 (seller_id上建有索引)
eq_ref:唯一索引的等值匹配
const、system:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。
explain select a.id,a.name from es_order a where a.id=8078
null : 不需要访问表,索引等。
4)possible_keys
指出mysql能使用哪个索引能在表中找到行,查询涉及到的字段若存在索引,则会列出,但不一定使用
5)key (重点关注)
使用到的索引
6)key_len (索引用到的长度)
7 )ref 索引匹配的情况(列)【无需太关注】
8)rows
本次查询,预计需要扫描的行数
9)Extra 额外的说明信息
using index (covering Index)
using where
表示mysql服务器从存储引擎收到记录后,进行“后过滤”,如果未使用索引,提醒
Using temporary
表示使用了临时表空间(排序、分组)
Using filesort
Mysql中无法利用索引完成的排序操作成为文件排序。
6、数据库设计心得
1)数据库的表的设计方法论为理解ER关系
一对一、一对多、多对对(分拆成两个1对多关系,也就是引入一个关系实体)
2)适当打破数据库第三范式
适当增加冗余字段,减少表的连接操作。
举例:订单表中有商品ID,由于订单反映的当时下单时商品的信息,我们完成可以增加一个冗余字段,商品名称goods_name,这样在展示订单信息
时,无需join商品信息表。(冗余字段的增加可能会带来数据的不一致性,所以要结合实际情况,不能为了冗余而冗余)
3)数据库字段类型选择
尽量选择满足需求的最小长度。比如订单状态,可以使用tinyint,而无需使用int。这样的直接好处就是减少数据行的长度,每页能存储更多的数据
行,IO效率得到提高。(性能是一个积累的过程)
4)表的垂直分割
将一个表的信息分割成多个表,这样主要是保证频繁使用的主表每行的数据尽量少,每页存放更多数据行,提高IO读效率。
表分割后,如果每次查询主表,都需要连接查询从表,那就没有必要进行垂直分割。
举例:
比如一条资讯信息,咨询的内容使用的类型为(text),在实际使用时,一般是先出资讯列表,然后点击到每一条资讯时,才去查询资讯内容,此时
可以将咨询内容单独分割出一张表,在咨询主表中冗余一个字段(摘要),存放咨询内容的部分信息,用于列表展示。
反例:
比如一条评论,有评论内容,类型为text,此时就没必要单独将评论内容抽取出来,因为每次显示评论时,基本上都要查看全部的评论内容,如果
分开,会进行多边连接,性能反而受影响。
5)表设计阶段应该考虑索引的建立
应根据系统相关需求,在创建表时建立必要的索引。
欢迎加笔者微信号(dingwpmz),加群探讨,笔者优质专栏目录:
1、源码分析RocketMQ专栏(40篇+)
2、源码分析Sentinel专栏(12篇+)
3、源码分析Dubbo专栏(28篇+)
4、源码分析Mybatis专栏
5、源码分析Netty专栏(18篇+)
6、源码分析JUC专栏
7、源码分析Elasticjob专栏
8、Elasticsearch专栏
9、源码分析Mycat专栏