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访问数据，或者太多回表查询哪些不在索引中的列的操作。

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