mysql千万级数据库读取优化汇总

mysql千万级数据库读取优化     2018/7/16

 

https://www.cnblogs.com/wangning528/p/6388538.html

------------------------------------------------------------------------------

1.优化查询分析工具

 

1.1.查询数据库性能

show status like ‘connections’      # 连接服务器次数

                uptime                             # 服务器上线时间

                slow_queries                   #慢查询次数

                com_select                     #查询操作次数

                com_insert                     #插入次数

                com_delete                    #删除次数

explain返回项:

key本次查找中用到索引

rows本次查询扫描行数(内循环次数)

1.2.分析查询语句

explain SELECT * FROM db_futures.m1801 where name='m1801';

create index index_name on m1801(name);

explain SELECT * FROM db_futures.m1801 where name='m1801';#索引的影响

1.3.分析检测优化表

analyze table tb_name,..         #分析表

check table tb_name,…;          #检查表

optimize table tb_name,…;     # 优化表 适用 text, blob类型

1.4.查询高速缓存

show variables like ‘%query_cache%’;

1.5.查询状态show full processlist

状态有sleep、query、locked、analyzing and statistics、coping to tmp table、

Sorting result、sending data。

------------------------------------------------------------------------------

2.查询优化 

 

2.优化查询

MySQL查询性能的优化涉及库表结构、建立合理的索引、设计合理的查询。

库表结构包括如何设计表之间的关联、表字段的数据类型等。

-------------------------------------------------------------------------------

2.1.Mysql查询优化处理过程

l  优化关联查询,确保on或using子句中列上有索引。确保group by和order by的

表达式只涉及一个表中的列,这样MySQL才有可能使用索引来优化整个过程。

l  优化group by和distinct。MySQL使用同样的方法优化这两类查询,通常是利用索

引的顺序性进行优化。

无法使用索引,group by使用临时表或者文件排序来做分组。

l  优化limit分页,使用延迟关联的方式来优化limit分页;

l  优化UNION查询。MySQL通过创建并填充临时表的方式来执行UNION查询,

因此需要手工的将where、limit、order by等子句“下推”到UNION的各个子查询中,除非确实需要服务器消除重复的行,否则一定要使用UNION all

-----------------------------------------------------------------------------

2.2.优化查询参数(all,index,range,ref,eq_ref,const/system从左到右效率依次增强)

1).all  全表扫描避免,应使用字段

   explain select  *  from tb_1

explain select  id,name  from tb_1

2). index  索引全扫描

explain  select  title  from  tb_1

3)函数,参数不要用字段(全盘扫描)

select id from t where substring(name,1,3)=’abc’

select id from t where name like ‘abc%’

select sum(t1.c1) from t1 where (select count(*)from t2 where t2.c2=t1.c2>0)

select sum(t1.c1) from t1 where exists(select * from t2 where t2.c2=t1.c2)#效率高

select id from t where num=@num  #子句使用参数全表扫描

select id from t with(index(索引名)) where num=@num #改为强制查询使用索引

select id from t where num/2=100 #避免where 子句对字段进行表达式操作

select id from t where num=100*2

where 子句中“=”左边函数、算术运算或表达式运算,无法使用索引。

count()查询该列不为null的行数,如果为count(*)则查询总行数

4). range 索引范围扫描

常见<,>,between,exists;(不用!=,is null或,in全表扫描)

explain  select  *  from  tb_1 where id>10

不用or(全表扫描)

select id from t where num=10 or num=20

select id from t where num=10 union all select id from t where num=20

不用in(全表扫描)

select id from t where num in(1,2,3)

select id from t where num between 1 and 3

select num from a where num in(select num from b)

select num from a where exists(select 1 from b where num=a.num)

   不用null 值判断(全表扫描)

select id from t where num is null

select id from t where num=0# num上设置默认值0表没有null值

5). ref 使用非唯一扫描或唯一索引的前缀扫描

explain select * from  tb_1  where  Id=3  #(Id不唯一)常见leftjoin

select *from tb_1 where name like ‘l%’;

6). eq_ref 类似于ref,唯一索引

索引值表中只有唯一的一条记录(多表使用外键或唯一键进行条件关联)

explain  select  *  from  tb_1  a, tb_1_record  b  where  a.id=b.id

7). const/system 单表中的主键或者唯一键查询

explain  select  title  from  tb_1 where id=10

 8)句的最后加上limit

------------------------------------------------------------------------------

2.3.使用update建议

l  尽量不要修改主键字段。

l  当修改varchar型字段时,尽量使用相同长度内容的值代替。

l  尽量最小化对于含有update触发器的表的update操作。

l  避免update将要复制到其他数据库的列。

l  避免update建有很多索引的列。

l  避免update在where子句条件中的列。

------------------------------------------------------------------------------

2.4.建立合理的索引 

l  覆盖索引:

使用索引覆盖扫描,把所有需要的列都放入索引;

查询列为索引时只用从索引中就能够取得

l  索引字段上避免使用函数。函数使索引失效

l  选择条件上放置索引,比如where,order by

l  索引列有大量数据重复SQL查询可能不会去利用索引

l  索引数量适当<7;索引降低了 insert 及 update 的效率

l  避免更新 clustered 索引数据列,簇索引数据列顺序是表记录物理存储顺序

l  条件总复合索引作必须用到索引中第一个字段,否则该索引将不会被使用,

并且应尽可能的让字段顺序与索引顺序相一致。

-------------------------------------------------------------------------------

2.5.语句优化

1)      复杂查询

u  切分查询:将大查询切分成小查询

u  分解关联查询,优化多表查询

 对每个表进行一次单表查询然后将结果在应用程序中进行关联。

select *from tb_score join tb_student on tb_score.sid=tb_student.id join tb_class

on tb_student.classid=tb_class.id where tb_class.name=’计算机1班‘;

分解步骤:

select id from tb_class where name=‘计算机1班’;#id 1

select id from tb_student where clasid=1;            #id 1   5

select * from tb_score where sid in(1,5);

u  值优化

              select name from m1801 where open>(select avg(age) from student_extra);

u  链表查询:更改为增加中间表;

2)      临时存储过程

最后显式删除,先 truncate table后 drop table避免系统表的长时间锁定。

3)      存储过程和触发器

开始设置 set nocount on ,结束设置 set nocount off 。

无需在执行存储过程和触发器的每个语句后向客户端发送done_in_proc 消息。

4)      避免使用游标(数据>1万行)基于游标或临时表应先寻找基于集的解决方案

5)      避免大事务操作,提高系统并发能力。

6)      尽量不要使用select into语句。导致表锁定,其他用户无法访问该表。

------------------------------------------------------------------------------

2.6.数据库表结构

1)      数据类型:

用 varchar/nvarchar 代替 char/nchar

尽量使用数字型字段

2)      表字段

使用单独汇总表;多字段表分解成多个表;

3)      表关联

u  尽量用表变量代替临时表。

u  避免频繁创建和删除临时表

u  重复引用大型表或常用表中的某个数据集时使用临时表。

u  一次性事件使用导出表

u  新建临时表一次性插入大数据量时用 select into ,避免造成大量 log;

数据量不大时应先create table后insert。

-----------------------------------------------------------------------------

3.数据库 MySQL分区

   在MySQL中,表空间就是存储数据和索引的数据文件。 

   分区操作: 

将现有的主键和索引先删除 

重现建立id,uid 的联合主键

再以 uid 为键值进行分区。

再以 uid 字段上进行建立索引。

  优化

1)尽量使数据库一次性写入Data File 

2)减少数据库的checkpoint 操作 

3)程序上尽量缓冲数据,进行批量式插入与提交

4)减少系统的IO冲突

-----------------------------------------------------------------------------

4.数据库变量值:

   innodb_autoextend_increment=128M (5M)

       避免由于频繁自动扩展Data File而导致 MySQL 的checkpoint 操作;

   innodb_log_buffer_size=16M (8M)

   innodb_log_file_size=128M (8M);

-----------------------------------------------------------------------------

5.附录:索引

4.1.B-Tree索引

  表A定义:

create table A(id int auto_increment primary key, name varchar(10), age tinyint,

 sex enum('男','女'), birth datatime, key(name,age,sex));

#id为主键,并在name,age,sex列上建立了索引。

全值匹配:指和索引中的所有列进行匹配

查找name='Jone' and age=13 and sex='男'的人;

匹配最左前缀:指用索引的第一列name

如where name='Jone',该查询只使用了索引的第一列

匹配列前缀:匹配索引列值的开头

如where name like 'J%',查找名字以J开头的人;

匹配范围值:例如查找年龄在10-30之间的Jone,

where name='Jone' and age between 10 and 30;

只访问索引的查询:

select中选择字段都是索引字段,不需要访问数据行提高查询速度。

如不按索引最左列进行查找无法使用索引,如仅查找表A中年龄为15岁的人;

不能跳过索引中的列如查表A名字Jone男性,索引只能用name列无法用sex列;

查询中索引的某列是范围查询,则该列后的查询条件将不能使用索引。

-----------------------------------------------------------------------------

4.2.Hash索引与B-Tree的区别:

Hash索引指包含哈希值(根据key中的列计算)和行指针,而B-Tree存储的是列值。所以Hash不能使用索引来避免读取数据行;

Hash索引数据不是按照索引值顺序存储的,所以无法用于排序;

Hash索引不支持部分索引列匹配查找,因为值是根据索引中全部列计算出来的;

Hash索引只支持等值比较查询,包括=、in()、<=>。不支持范围查询。

-----------------------------------------------------------------------------

4.3.索引的优点

索引不仅仅可以让服务器快速定位到表的指定位置

B-Tree索引按列顺序存储数据,用Order by和group by操作避免排序和临时表

B-Tree索引中存储索引列的值,当select值在索引中避免访问数据行

高性能的索引策略

独立列:索引不能用在表达式,函数。

select * from A where id+1=5; 无法用主键索引。

前缀索引和索引选择性:

索引很长的字符串,可以索引开始的部分字符来节约空间提高效率

多列索引:

在多列上创建索引不等同于给这些列的每一列单独建立索引。

单列索引低于多列索引。

多列索引中索引列顺序:

多列B-Tree中索引首先按照最左列进行排序,其次是第二列等等。

聚簇索引:

不是一种单独的索引类型,而是一种数据存储方式, InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行,一个表只能有一个聚簇索引。

可以把相关数据保存在一起。 数据访问更快。

使用覆盖索引扫描的查询可以直接使用页节点中的主键值;

B-Tree索引插入速度严重依赖于插入顺序。

按照聚簇索引列中值的顺序插入数据到InnoDB表中速度最快的;

更新聚簇索引列的值代价很高,

因为会强制InnoDB将每个被更新列所在的行移动到新的位置;

插入新的行可能面临“页分裂”的问题。

聚簇索引可能导致全表扫描变慢,

尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。

覆盖索引:

如果索引包含(覆盖)所有需要查询的字段值,我们就称之为覆盖索引。

当查询是一个索引覆盖查询时,Extra列可以看到Using index的信息。

因为InnoDB表中非聚簇索引中存储主键值,所以我们先根据条件获取主键值,然后再根据主键值进行查询,这种方式叫做延迟关联。

使用索引扫描来做排序。如果EXPLAIN出来的type列值为index,说明MySQL使用了索引扫描来做排序。扫描索引本身是很快的,但是如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就回表查询一次对应的行。这基本都是随机I/O,因此按索引顺序读取的速度通常要比顺序地全表扫描慢,尤其是I/O密集型的工作负载时。因此MySQL设计索引时应尽可能的满足排序和查找。只有索引列顺序和order by子句的顺序完全一致,并且所有列的排序方向都一致时,MySQL才能使用索引来对结果做排序。如果查询关联多张表,则只有order by子句引用的字段全部为第一个表时,才能使用索引排序。

-----------------------------------------------------------------------------

4.4.总结

  选择合适的索引以避免单行查找,尽可能的使用原生顺序,尽可能使用索引覆盖查询。我们通过响应时间来对查询进行分析,找出消耗时间最长的查询或者给服务器带来压力最大的查询,然后检查查询的schema、SQL和索引结构,判断是否有查询扫描了太多的行,是否做了很多额外的排序或者使用了临时表,是否使用了随机I/O访问数据,或者太多回表查询哪些不在索引中的列的操作。

-----------------------------------------------------------------------------

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值