mysql优化
-一、硬件优化(优化的主要点)
01.CPU----最好是64位的,例:8-16颗CPU
02.内存----例:96G-128G,3-4个实例
03.disk----数量越多越好,性能:ssd(适合高并发业务)>sas(普通上线业务)>sata(适合线下)
RAID----部署合适的RAID
04.网卡----多块网卡bond,以及buffer,tcp优化
-二、软件优化
01.操作系统----x86_64
02.软件:mysql,编译优化
-三、my.cnf里面参数优化 (一般效果不是很明显)
01.使用调优工具
-四、sql语句的优化
a.抓取出慢查询sql
b.可以使用慢查询日志分析工具
(对于查询慢的select语句,可以使用explain查看查询;一般在唯一值或者重复比较少的列上面简历索引,如果多条件查询中的条件,全部都是唯一值少,重复较多,这时候可以采用联合索引,对于多个列一起进行建立索引)
c.晚上零点分析慢查询,发到核心开发,dba分析。
02.拆分大的复杂的sql语句
子查询,jion查询。
03.数据库是存储数据的地方不是计算数据的地方
04.对于LIKE "%XXXIII%"之类的前后都是%的查询,sql索引不起作用(一般是一些搜索会出现这种问题)
解决:a.从业务上,可以实现用户登录后再查询或者搜索,减少搜索次数
b.如果大量频繁的搜索,一般是爬虫在爬,这时就可以分析web日志,将频繁查询的IP封掉
c.配置主从同步,程序实现读写分离
d.在数据库前端加memcached缓存服务器
e.不用数据库进行查询,用搜索软件
-五、架构上的优化
01.业务拆分,搜索功能,like "%XXX%",一般不用mysql数据库
02.数据库前端必须加cache,例如:memcached
03.业务拆分,某些业务应使用nosql持久化存储
比如:粉丝关注、好友关系等
04.动态数据静态化
05.数据库集群与读写分离,一主多从
06.单表过多,进行拆库拆表
-六、流程,制度,安全优化
01.使用show status;
通过观察Queries(当前执行的查询数量)、Threads_connection(几个线程已经连接)、Threads_running(几个线程正在执行),并编写脚本,刷新观察是否周期性故障或波动;一般由于访问高峰或缓存崩溃引起。
可以加缓存或者更改缓存失效策略,使失效时间分散或者夜间失效。
02.使用show processlist
这个命令是显示当前所有连接的工作状态。
脚本中可以使用:mysql -uroot -e 'show processlist\G' |grep State|uniq|sort -rn >>proce.txt
然后:more proce.txt | sort|uniq -c |sort
查看一些state状态,特别注意copying to tmp table\sending data\sorting result一些状态
索引优化
索引类型和表类型优化
1.索引类型
2.B-tree索引的常见误区
3.聚簇索引
4. 索引覆盖
5. 例:3、4知识点解决此问题
高性能的索引策略
-一、索引长度与区分度
-二、伪哈希函数降低索引长度
-三、大量数据查询操作优化
-四、索引与排序
-五、重复索引和冗余索引
-六、索引碎片修复
开发中常用优化
-一、explain分析sql效果
01.id
02.select_type
03.table
04.type
05.Possible_keys
06.key
07.key_len
08.ref
09.rows
010.extra
-二、in型子查询陷阱
1.索引类型
1.1B-tree索引
1.2hash索引
hash索引的理论查询时间复杂度是O(1);
2.B-tree索引的常见误区
2.1在where条件后(比如where a=10 and b=20;),不需要a、b都加上索引,因为是独立的索引,同时只能用上一个;
2.2在多列上建立索引后,索引发挥作用将要满足前缀条件。
例如index(a,b,c);
例:一个表有一个联合索引(c1,c2,c3,c4):
where c1=x and c2=x and c4=x and c3=x;四个索引都用到,c4\c3顺序不影响,MySQL会自动优化。
where c1=x and c2=x and c4=x order by c3;c2下面的c3是有序的,c3也发挥作用了,不用查找的时候只有c1和c2发挥作用,而c3发挥作用是在排序上。
03.当索引c2,c3位置放反,则c2就是一个常量;当where c1=’a’ order by c3,c2,这时需要使用filesort
04.索引和order by的关系
3.聚簇索引
01.myisam的次索引和主索引都指向物理行
02.innodb的次索引指向对主键的引用
InnoDB的主索引文件上,直接存放该行数据,成为聚簇索引,次索引指向对主键的引用。
B-tree可能会有节点分裂,因为innodb的节点带有数据,索引分裂带来的影响可能会比较大;因此我们最好采用递增的整型来做主键;如果是无规律的数据,将会产生叶子的分裂,影响效率。
4.索引覆盖
如果查询的列敲好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再查找数据,这种查询速度相当快。
Extra:using index
5.例:3、4知识点解决此问题
Create table A{
id varchar(64) primary key,
Ver int,
}
在id、ver上面有联合索引,10000条数据,在表中有几个很长的字段:varchar(3000);
问题:select id from A order by id;特别慢,但是select id from A order by id,ver很快。
分析:
如果在myisam上:
可见两个查询速度是差不多的;
推断:
01.是innodb引擎
02.有多个比较长的列:如果是聚簇索引,导致沿ID排序时,要跨好多小文件块。
03.如果没有多长的几个char字段,差别也不会这么大。
高性能的索引策略
-一、索引长度与区分度
注:区分度达到0.1索引的性能就可以接受。
Key-len:14:因为一个汉子在utf8编码中有3个字节的长度,因为是varchar有变化,会有2个字节的变动,所以是4*3+2=14;如果是char,如果不指定索引长度,那么就是42字节的长度。
-二、伪哈希函数降低索引长度
对于前缀不易区分的列,如url:http://www.baidu.com,列的前几个字符都是不一样的,不易区分,解决:
01.把列的内容倒过来存储,并建立索引
02.伪哈希效果
(upda tb_name set crcurl=crc32(utl));
-三、大量数据查询操作优化
01.使用limit offset
Select id,name from tb_name limit 10000,10; //然后数字逐渐增大;
>show profiles;
>show profile from query 号数;
//可以发现,大多数时间都浪费在了,sending data上;因为limite 是先查询再跳过。
优化:可以限制查询的条数,limit不超过10000之类的。
02.先跳过,再查询
Select id,name from tb_name where id>50000 limit 10;
限制:01)需要保证数据物理上没有被删除过;02)数据不物理删除,只是逻辑删除。
03.延迟关联
Select lx_com.id,lx_com.name from lx_com inner join (select id from lx_com limit 50000000,10) as tmp on lx_com.id=tmp.id;
-四、索引与排序
排序可能出现两种情况:01.对于覆盖索引,直接在索引上查询时,就是有序的。02.先取出数据,形成临时表,做filesort文件排序,但文件可能在磁盘上,也可能在内存中。
-五、重复索引和冗余索引
01.重复索引
Alter table goods add index ck1(click_count);
Alter table goods add index ck2(click_count);
02.冗余索引
Alter table goods add index ck1(click_count);
Alter table goods add index cat_click(cat_id,click_count);
Index(x,y)和index(y,x)是不一样的。
-六、索引碎片修复
开发中常用优化
-一、explain分析sql效果
>explain select *****; Id: Select_type: Table: Type: Possible_keys: Key: Key_len: Ref: Rows: Extra:
1 row in set(0.00 sec)
|
01.id
SELECT识别符。这是SELECT查询序列号。
02.select_type
2.1 simple 它表示简单的select,没有union和子查询
2.2 primary 包含子查询或者派生查询
2.3非from子查询
2.4from型子查询
2.5 union / union result
03.table
查询的表名,未必肯定为表名,也可能是表的别名。
04.type
连接类型。
4.1 system
表仅有一行,这是const类型的特列,平时不会出现,这个也可以忽略不计
4.2 const
表最多有一个匹配行,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快
记住一定是用到primary key 或者unique,并且只检索出两条数据的 情况下才会是const,
4.4 ref 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。
4.5 ref_or_null 该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
4.6 index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
4.7 unique_subquery
4.8 index_subquery
4.9 range 给定范围内的检索,使用一个索引来检查行。
4.10 index 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)(扫描所有索引文件)
当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。
4.11 ALL 对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。(全表扫描)
05.Possible_keys
可能用到的索引列。
06.key
实际用到的索引列
07.key_len
key的长度。
08.ref
ref列显示使用哪个列或常数与key一起从表中选择行。ref列显示使用哪个列或常数与key一起从表中选择行。
简单来说就是,通过索引列,直接索引某一行。
09.rows
估计扫描了多少行。
010.extra
10.1 using index
该值表示相应的select操作中使用了覆盖索引(Covering Index).
10.2 using where
表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示:查询可受益与不同的索引。
10.3 using temperary
表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个:
1)内部临时表占用的空间超过min(tmp_table_size,max_heap_table_size)系统变量的限制
2)使用了TEXT/BLOB 列
10.4 using filesort
MySQL中无法利用索引完成的排序操作称为“文件排序”
10.5 range checked for each recond
-二、in型子查询陷阱
Select id,name from lx_com where id in (select id from idea);
#explain之后可以发现,先是全表查询lx_com,再全表查询idea,查询很慢。
改进:
Select ls_com.id,name from lx_com inner join idea on lx_com.id=ids.id;
#explain之后发现,先查询ids,然后查询ls_com(并且,type是eq_reg)