mysql优化
优化的第一步是要弄清楚优化的对象,该对象有两个,数据存储和计算逻辑,优化就要从这两点着手。
数据存储
mysql最常用的数据引擎是InnoDB,数据引擎的意思是它负责怎么存储数据和取出数据,InnoDB采用了B+Tree索引来排列数据,重点介绍B+Tree。
磁盘和内存可以看成一个一维的向量,但一维向量内的元素可以相互记录位置,快速定位彼次,如同门牌号一样;B+Tree就是这种环境下产生的排列方式,它的基本原理是将一维的矩阵元素,拉伸至从上到下的树状,除了底层的节点存储数据,上层的节点都靠虚拟节点实现,且为了利用磁盘预读,相邻的节点之间互相引用。这样排序的过程可以使数据查找的过程,由线性变为对数性,以空间换时间大幅加快查找的速度。
数据存储时遵循最小原则,只分配需要利用的最小的空间,减少IO和磁盘的消耗,如varchar()和int()的宽度就要尽可能小。
选用列不要太多,符合设计规范,将事实表和维度表,规划清楚,弄清楚最常用的数据是哪一块,再在星型和雪花型中做取舍。
使用索引,索引避免建在重复值多的列,应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上
计算逻辑
查询过程有3部分:服务端连接处理SQL、客户端提交SQL并解析、读取数据和计算,优化也是从这3块展开。
服务端连接
服务是通过监听端口实现通信,内部再通过多线程复用连接,来提高并发量,这块的优化,要使线程的开销和IO的开销取得平衡位置,若存在大量短连接,就增大线程数,若存在大量长连接,就减少线程数
客户端提交SQL并解析
优化的目标是使逻辑最优,利用最少的cpu资源和IO来达到目的。SQL进行解析使会解释成一个树状的结构,它的运行过程就如同折纸一样,读取数据一步步折,知道达到想要的目的。遵循着这样的原则:数据流在最开始的尽可能最少,以便于减少多余的计算和IO
读取数据和计算
查询缓存,查询缓存,是否有Result,适合读多的场景,写多的场景不适合,控制缓存大小,指定要缓存的语句
筛选数据时,最先筛选逻辑判断型,再进行数据筛的最多的部分
优先使用索引,索引能加快判断和查找的过程,值得一提的是Like只有前缀才走索引,联合索引也要注意使用顺序
在建有索引的字段上尽量不要使用函数进行操作,这样会先计算该字段,再查找,索引也就没有意义
DECIMAL少用,计算代价高,可以把数字乘以一万,变成Bigint
解决冲突
使用事务
使用事务包裹所有的sql语句,要么全部执行,要么都不执行,当多个用户操作数据库时十分有效,事务使用begin,commit将sql包裹
BEGIN;
sql语句
COMMIT;
使用锁
加读锁时,其他用户只能读不能写,加写锁时,其他用户被阻塞,直到获取到锁
能使用索引的时候,mysql会使用行锁,不能使用则用表锁
LOCK TABLE t1 WRITE,t2 READ
sql语句
UNLOCK TABLES;
查看执行计划
mysql> explain select ………
用来查看sql的执行计划,优化sql常用。
更多的优化细节见【1】