求教，mysql千万级数据多表查询做分页该如何优化

查询指定的记录最好通过Id进行in查询来获得真实的数据其实不是最好而是必须，也就是你应该先查询出复合的ID列表,通过in查询来获得数据

我们来做一个测试ipdatas表：

CREATE TABLE `ipdatas` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`uid` INT(8) NOT NULL DEFAULT '0',

`ipaddress` VARCHAR(50) NOT NULL,

`source` VARCHAR(255) DEFAULT NULL,

`track` VARCHAR(255) DEFAULT NULL,

`entrance` VARCHAR(255) DEFAULT NULL,

`createdtime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

`createddate` DATE NOT NULL DEFAULT '0000-00-00',

PRIMARY KEY (`id`),

KEY `uid` (`uid`)

) ENGINE=MYISAM AUTO_INCREMENT=67086110 DEFAULT CHARSET=utf8;

这是我们做的广告联盟的推广ip数据记录表，由于我也不是mysql的DBA所以这里咱们仅仅是测试

因为原来里面有大概7015291条数据

这里我们通过jdbc的batch插入6000万条数据到此表当中“JDBC插入6000W条数据用时:9999297ms”；

大概用了两个多小时,这里面我用的是batch大小大概在1w多每次提交,还有一点是每次提交的数据都很小，而且这里用的myisam数据表，因为我需要知道mysql数据库的大小以及索引数据的大小结果是

ipdatasMYD 399 GB (4,288,979,008 字节)

ipdatasMYI 128 GB (1,377,600,512 字节)

这里面我要说的是如果真的是大数据如果时间需要索引还是最好改成数字字段,索引的大小和查询速度都比时间字段可观。

步入正题:

1全表搜索

返回结构是67015297条数据

SELECT COUNT(id) FROM ipdatas;

SELECT COUNT(uid) FROM ipdatas;

SELECT COUNT() FROM ipdatas;

首先这两个全表数据查询速度很快,mysql中包含数据字典应该保留了数据库中的最大条数

查询索引条件

SELECT COUNT() FROM ipdatas WHERE uid=1; 返回结果时间:2分31秒594

SELECT COUNT(id) FROM ipdatas WHERE uid=1; 返回结果时间:1分29秒609

SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回结果时间:2分41秒813

第二次查询都比较快因为mysql中是有缓存区的所以增大缓存区的大小可以解决很多查询的优化，真可谓缓存无处不在啊在程序开发中也是层层都是缓存

查询数据

第一条开始查询

SELECT FROM ipdatas ORDER BY id DESC LIMIT 1,10 ; 31毫秒

SELECT FROM ipdatas LIMIT 1,10 ; 15ms

总结优化如下:

1、主键就是聚集索引

2、只要建立索引就能显著提高查询速度

3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度

　(四)其他书上没有的索引使用经验总结

1、用聚合索引比用不是聚合索引的主键速度快

2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个

4 、日期列不会因为有分秒的输入而减慢查询速度

(五)其他注意事项

1 不要索引常用的小型表

2 不要把社会保障号码(SSN)或身份z号码(ID)选作键

3 不要用用户的键

4 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)

5 使用系统生成的主键

　二、改善SQL语句

1、Like语句是否属于SARG取决于所使用的通配符的类型

2、or 会引起全表扫描

3、非 *** 作符、函数引起的不满足SARG形式的语句

4、IN 的作用相当与OR

5、尽量少用NOT

6、exists 和 in 的执行效率是一样的

7、用函数charindex()和前面加通配符%的LIKE执行效率一样

8、union并不绝对比or的执行效率高

9、字段提取要按照“需多少、提多少”的原则，避免“select ”

10、count()不比count(字段)慢

11、order by按聚集索引列排序效率最高

12、高效的TOP

。从外在条件来说，优化mysql涉及优化硬件、优化磁盘、优化 *** 作系统、选择应用编程接口等。

二、优化硬件

如果你需要庞大的数据库表(2G)，你应该考虑使用64位的硬件结构，像Alpha、Sparc或即将推出的IA64。因为MySQL内部使用大量64位的整数，64位的CPU将提供更好的性能。

对大数据库，优化的次序一般是RAM、快速硬盘、CPU能力。

更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。

如果不使用事务安全(transaction-safe)的表或有大表并且想避免长文件检查，一台UPS就能够在电源故障时让系统安全关闭。

对于数据库存放在一个专用服务器的系统，应该考虑1G的以太网。延迟与吞吐量同样重要。

浅谈一下Cognos处理大数据的思路，仅针对1021以下的版本，对于1021当中引入的hadloop等分布式数据仓库等不做介绍。我们主要从一个一般中等项目当中，用怎样的思路来优化我们的查询。

我们主要从3个思路来思考大数据的处理

一、数据库层次

现在主流的Cognos项目，主要的开发模式还是基于rolap的dmr报表建模。因此，数据库的优化就显得由为重要。主要通过以下几个方面优化我们的数据库：

(1)维度id，维度层次id等关键减缩字段建立索引建立、维护。

(2)根据数据量的大小，按时间等进行分区优化。

(3)高速缓冲表MQT的使用

(4)表空间、缓冲池设置等

(5)数据库性能优化

二、Cognos Server优化

Cognos优化包括对配置文件的优化，集群的搭建，服务和日志的开启等基于cognos 软件安装，配置的优化，主要包括以下几个方面：

21 apache 配置优化

Timeout（超时）/MaxKeepAliveRequests（最大的请求数）/KeepAliveTimeout（请求超时）的优化配置

22Cognos自带tomcat配置调优

(1)可修改TOMCAT配置文件CRN_ROOT\tomcat\conf\serverxml。其参数集中在行：

可以对maxProcessors（最大进程数）/AcceptCount(最大连接数) ConnectionTimeout（连接超时）进行修改

(2)文件路径：CRN_ROOT\tomcat\conf\webxml

可以对session-timeout进行修改

23Cognos sever配置文件优化

231 reportservicexml优化

文件路径：CRN_ROOT\ webapps\p2pd\WEB-INF\services\ reportservicexml

注：修改文件后，重启服务后配置生效。

包括以下参数 max_process(交互报表处理进程数，和cpu有关) inger_process(交互报表初始化进程数，和cpu优关)

max_non_affine_connections_per_process(交互报表所占线程数) idle_process_check_interval_ms(空闲检测时间)

queue_time_limit_ms(报表服务队列时间限制) async_wait_timeout_ms(Dispatcher请求等待同步时间)

232 batchreportservicexml

文件路径：CRN_ROOT\ webapps\p2pd\WEB-INF\services\ batchreportservicexml

注：修改文件后，重启服务后配置生效。

包括以下参数 max_process(服务批量报表处理所占进程数) linger_process(服务批量报表处理初始化进程数)

max_non_affine_connections_per_process(服务批量报表处理所占线程数) idle_process_check_interval_ms(空闲进程检测时间间隔)

idle_process_max_idle_ticks(空闲进程检测标记) queue_time_limit_ms(批量报表处理排队时间限制) async_wait_timeout_ms(Dispatcher请求等待同步时间)

233 CQEConfigxml

主要是与数据库参数设置，文件路径：CRN_ROOT\configuration\ CQEConfigxmlsample

注：将CQEConfigxmlsample文件名修改为CQEConfigxml后，重启服务后配置生效。

可以修改以下参数：Timeout(应用数据库连接超时设置) PoolSize(应用数据库连接池最大连接数设置) queryReuse(查询缓冲设置)

2013-07-08 0

分享

答案对人有帮助，有参考价值1

曾力 - Cognos讲师、Cognos独立顾问、数据仓库架构师 2013-07-08 回答

234 ppds_cfgxml

主要进行缓存和日志参数设置，文件路径：\cognos\c8\configuration\ ppds_cfgxml

注：重启服务后配置生效。

可以修改以下参数：ReadCacheSize(可减少用户访问时服务器的磁盘IO。提高访问速度。) pcQueryLogFile(建议生产环境关闭该日志的跟踪，一般默认也是关闭状态)

24 Cognos content store优化

241优化内容库连接服务

内容库最好外配为db2 oracle等数据库，不要用自带的derby因为项目中的日志信息会非常多，严重影响内容库的效率。

Cognos Administration，在系统下选择选择对应的服务，选择ContentManagerService的属性，设置相应的连接参数信息。

242日志优化

适当开启各个cognos服务的日志级别，越高级的级别对应更详细，更明确的日志，但也会影响整个系统的效率。

这是一把双刃剑，需要适当调整。日志级别设置得越高，就越降低系统性能。通常情况下，您可以将级别设置为

“最小”或“基本”来收集错误，或设置为“请求”来收集错误和警告。

25提高访问数据库速度

Cognos和数据库间参数在cer\bin\cogdmini文件中,(根据版本不同是安装目录的数字，根据连接的数据库不同，是对应数据库名称的关键字)

以oracle数据库为例,参数在cogdmorini文件中，打开这个文件查找字符串Fetch Number of Rows=去掉这行前面的分号，将10改成2000；

这样这行就成了Fetch Number of Rows=2000，表示是每次从数据库取2000条数据。其他数据库基本上都有类似的配置。用以提高从数据库中提取数据的速度。

26加大缓存

cer\bin\Cerini(根据版本不同是安装目录的数字)：

SortMemory=5120

（这里 SortMemory 单位是 2kbytes，5120代表 2k x 5120 = 10M）（技巧：一般 SortMemory 取空闲内存的十分之一到八分之一大小）

27修改cognos configuration中的参数来优化

在cognos configuration中有很多参数可以优化来提高整体软件的运行效率，比如增加内存、增加查询缓存

28分布式部署

分布式部署可以大大提升Cognos服务器的负载能力，同时容错保护功能可以使服务器更为稳定的运行，很好的支持大用户量的并发使用。

2013-07-08 0

答案对人有帮助，有参考价值1

曾力 - Cognos讲师、Cognos独立顾问、数据仓库架构师 2013-07-08 回答

3报表设计优化

Cognos报表作为一个工具，在非cube模式下，最终我们执行报表查询的时候，我们的报表发送到数据库进行查询的本质还是sql，所以，在我们制作一张报表的时候，我们要尽可能的利用fm,rs当中的功能，优化报表最终执行生成的SQL实现整个报表的优化。而CUBE模式下，我们更多要考虑配置、存放和数据库大小所造成的影响，下面我会细细说来。

2013-07-08 0

答案对人有帮助，有参考价值1

曾力 - Cognos讲师、Cognos独立顾问、数据仓库架构师 2013-07-08 回答

31 FM建模优化

311手写SQL定制查询主题

右键点击查询主题的菜单项Edit Definition…可以进入SQL语句编写框，调整查询主题的SQL语句。默认情况下，这里的SQL语句为Cognos SQL类型。如果需要编写应用数据库可以直接运行的本地SQL需要将这里的SQL类型进行设置。点击右上方的Options按钮，选择SQL Settings标签页，选择SQL Type为Native。这个时候，我们手写SQL就非常注重这个SQL的优化，尽量避免SELECT ,用EXISTS替代IN,多使用DECODE来进行判断，条件语句注意点等常用SQL优化策略，编写对应的SQL

312尽量使用特定数据的数据库函数

在菜单项Actions中选择Specify Package Function List…指定报表定制中可以使用的数据库函数列表。将除应用数据库意外的其他数据库类型从Selected function sets中选到Available function sets中，尽量使用特定数据库的自带函数可以提高查询效率。

313表关联设定

在建立表关联尽量避免使用外关联关系（包括左外关联、右外关联、全外关联）。外关联的使用会使数据库的查询压力骤增，从而影响前端报表的生成。在星型结构、雪花型结构的数据仓库模型中，尽量按照一对一、一对多的关联关系设定维表与实事表之间的关联，Cognos Server会依照这里的关联关系自动优化提交给数据库的SQL语句。如果关联关系中出现了环状连接关系，可以通过别名表或是快捷键的方式解决环状连接问题

314Edit Governors查询性能设置

在菜单项Project中选择Edit Governors，可以设置查询的查询性能

Report table limits 该属性设置报表中运行SQL所涉及的TABLE数量

Data retrieval limits 该属性设置报表中运行SQL返回结果的数量

Query execution time limits 该属性设置报表中运行SQL的执行时间

Large text items limit 该属性设置报表中运行SQL返回大文字块的字符数量限制

2013-07-08 0

答案对人有帮助，有参考价值1

曾力 - Cognos讲师、Cognos独立顾问、数据仓库架构师 2013-07-08 回答

32 RS报表调优

321报表函数的使用

在报表函数的使用上，尽可能使用应用数据库能够解析的本地数据库函数，函数列表中的通用函数，在处理时会将函数放在报表服务器进行运算，从而增大了报表服务器的性能开销。

322 观察查询的SQL

我们选择查询页面，GENERATE SQL/MDX观察这个报表生成的SQL并进行不断优化，

33332 RS报表调优

321报表函数的使用

在报表函数的使用上，尽可能使用应用数据库能够解析的本地数据库函数，函数列表中的通用函数，在处理时会将函数放在报表服务器进行运算，从而增大了报表服务器的性能开销。

322 观察查询的SQL

我们选择查询页面，GENERATE SQL/MDX观察这个报表生成的SQL并进行不断优化，

333查询字段、查询表顺序调整

根据数据库的优化策略，可能需要将查询字段的顺序进行调整，可以在Data Items窗口中进行设置。查询SQL语句中，From关键字后面的表顺序是按照select关键字后出现的字段顺序进行设置的。在为表顺序进行设置时，属性为Identifier或Attribute的字段比属性为Fact的字段在为表排序时的优先级要高，即，先以Identifier、Attribute字段的出现顺序为表进行排序，如果没有上述两类字段，才以Fact字段的出现顺序为表进行排序。

334聚合前后设置过滤条件

将过滤条件的Application属性设置为After aggregation或Before aggregation可以调整过滤条件在聚合前或是聚合后生效。After aggregation生成过滤条件的SQL语句使用的是关键字having，而Before aggregation生成过滤条件的SQL语句使用的是关键字where。

335取消报表自动分组提高明细报表查询速度

如果报表要展现明细数据，不想使用任何汇总，我们可以到此报表对应的查询中将自动分组属性定义为否。修改地方：对象的属性Auto Group & Summarize可以设置当前SQL语句的查询中是否加入distinct、sum、group by这样的关键字。默认情况下，该属性设置为Yes，可以根据查询情况关掉此开关项，减少SQL语句的复杂度。

336自动排序设置

在Query的Auto-sort属性中可以为查询设置是否自动排序。如果选择是，则会在生成的SQL语句中自动加入Order By关键字，排序字段将自动根据数据项的属性进行设置（如果查询字段的usage属性为Attribute、Identifier则排序，如果为Fact则不排序）；如果选择否、则不排序；如果选择最小，则根据数据项的排序属性进行排序设置。默认值为最小。

337报表Processing设置

在Query的Processing属性中可以为查询设置SQL的处理设置。Cognos Report Studio会将报表的所有设置首先转换为Cognos SQL提交给报表服务器，服务器在进行必要处理后，会将SQL语句转换为应用数据库本地执行的SQL语句，进行数据库处理。为提高报表的处理速度，要尽可能的将报表的处理运算放在数据库进行，以保证其运行速度。将该属性设置为Database only会将报表页面生成的Cognos SQL不经报表服务器处理全部转换为数据库能够执行的本地数据库SQL，如果将该属性设置为Limited Local，则将报表页面生成的Cognos SQL先进行必要的报表服务器运算，然后再将剩余的部分提交给数据库进行本地SQL的处理。默认值为Framework中为Datasource对象的设置的queryProcessing属性。

338使用With子句

在Query的Use SQL With Clause属性中可以为查询设置是否使用With子句。部分数据库例如Oracle支持With关键字，当查询中嵌套子查询时，可以通过With子句的使用，减轻报表服务器对Cognos SQL的处理，从而提升报表的运行性能。如果将该属性设置为Yes，则允许使用With关键字，查询中生成的Native SQL将出现With子句；如果将该属性设置为No，虽然拒绝使用With关键字。默认值为Framework中Edit Governors下的Use WITH clause when generating SQL属性设置。

339报表服务器本地缓存设置

在Query的Use Local Cache属性中可以为查询设置是否使用本地缓存。如果将该属性设置为Yes，则启用服务器的本地缓存，服务器将为查询结果保存在session中，当用户在浏览器内再次打开同一张报表时，查询结果将取自缓存，从而减轻了数据库的负载压力；如果将该属性设置为No，则禁用服务器的本地缓存，查询结果全部取自数据库的实时数据。默认值为Framework中Edit Governors下的Allow usage of local cache属性设置。

我用的是finereport，比这个方便

I 硬件配置优化

CPU选择：多核的CPU，主频高的CPU

内存：更大的内存

磁盘选择：更快的转速、RAID、阵列卡，

网络环境选择：尽量部署在局域网、SCI、光缆、千兆网、双网线提供冗余、0000多端口绑定监听

II *** 作系统级优化

使用64位的 *** 作系统，更好的使用大内存。

设置noatime,nodiratime

[zhangxy@dowload_server1 ~]$ cat /etc/fstab

LABEL=/ / ext3 defaults,noatime,nodiratime 1 1

/dev/sda5 /data xfs defaults,noatime,nodiratime 1 2

优化内核参数

netipv4tcp_keepalive_time=7200

netipv4tcp_max_syn_backlog=1024

netipv4tcp_syncookies=1

netipv4tcp_tw_reuse = 1

netipv4tcp_tw_recycle = 1

netipv4neighdefaultgc_thresh3 = 2048

netipv4neighdefaultgc_thresh2 = 1024

netipv4neighdefaultgc_thresh1 = 256

netipv4confdefaultrp_filter = 1

netipv4confdefaultforwarding = 1

netipv4confdefaultproxy_arp = 0

netipv4tcp_syncookies = 1

netcorenetdev_max_backlog = 2048

netcoredev_weight = 64

netipv4tcp_rmem = 4096 87380 16777216

netipv4tcp_wmem = 4096 65536 16777216

netipv4tcp_rfc1337 = 1

netipv4tcp_sack = 0

netipv4tcp_fin_timeout = 20

netipv4tcp_keepalive_probes = 5

netipv4tcp_max_orphans = 32768

netcoreoptmem_max = 20480

netcorermem_default = 16777216

netcorermem_max = 16777216

netcorewmem_default = 16777216

netcorewmem_max = 16777216

netcoresomaxconn = 500

netipv4tcp_orphan_retries = 1

netipv4tcp_max_tw_buckets = 18000

netipv4ip_forward = 0

netipv4confdefaultproxy_arp = 0

netipv4confallrp_filter = 1

kernelsysrq = 1

netipv4confdefaultsend_redirects = 1

netipv4confallsend_redirects = 0

netipv4ip_local_port_range = 5000 65000

kernelshmmax = 167108864

vmswappiness=0

加大文件描述符限制

Vim /etc/security/limitsconf

加上

soft nofile 65535

hard nofile 65535

文件系统选择 xfs

/dev/sda5 /data xfs defaults,noatime,nodiratime 1 2

III Mysql设计优化

III1存储引擎的选择

Myisam：数据库并发不大，读多写少,而且都能很好的用到索引，sql语句比较简单的应用，TB数据仓库

Innodb：并发访问大，写 *** 作比较多，有外键、事务等需求的应用，系统内存较大。

III2命名规则

多数开发语言命名规则：比如MyAdress

多数开源思想命名规则：my_address

避免随便命名

III3字段类型选择

字段类型的选择的一般原则：

根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。

只分配满足需求的最小字符数，不要太慷慨。

原因：更小的字段类型更小的字符数占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。

III31 整型：

见如下图:

类型

字节

最小值

最大值

(带符号的/无符号的)

(带符号的/无符号的)

TINYINT

1

-128

127

0

255

SMALLINT

2

-32768

32767

0

65535

MEDIUMINT

3

-8388608

8388607

0

16777215

INT

4

-2147483648

2147483647

0

4294967295

BIGINT

8

-9223372036854775808

9223372036854775807

0

18446744073709551615

根据满足需求的最小整数为选择原则，能用INT的就不要用BIGINT。

用无符号INT存储IP，而非CHAR(15)。

III32 浮点型：

类型

字节

精度类型

使用场景

FLOAT(M,D)

4

单精度

精度要求不高，数值比较小

DOUBLE(M,D)（REAL）

8

双精度

精度要求不高，数值比较大

DECIMAL(M,D)(NUMERIC）

M+2

自定义精度

精度要求很高的场景

III33 时间类型

类型

取值范围

存储空间

零值表示法

DATE

1000-01-01~9999-12-31

3字节

0000-00-00

TIME

-838:59:59~838:59:59

3字节

00:00:00

DATETIME

1000-01-01 00:00:00~9999-12-31 23:59:59

8字节

0000-00-00 00:00:00

TIMESTAMP

19700101000000~2037年的某个时刻

4字节

00000000000000

YEAR

YEAR(4):1901~2155 YEAR(2)：1970~2069

1字节

0000

III34 字符类型

类型

最大长度

占用存储空间

CHAR[(M)]

M字节

M字节

VARCHAR[(M)]

M字节

M+1字节

TINYBLOD，TINYTEXT

2^8-1字节

L+1字节

BLOB，TEXT

2^16-1字节

L+2

MEDIUMBLOB，MEDIUMTEXT

2^24-1字节

L+3

LONGBLOB，LONGTEXT

2^32-1字节

L+4

ENUM('value1','value2',)

65535个成员

1或2字节

SET('value1','value2',)

64个成员

1,2,3,4或8字节

注：L表示可变长度的意思

对于varchar和char的选择要根据引擎和具体情况的不同来选择，主要依据如下原则:

1 如果列数据项的大小一致或者相差不大，则使用char。

2 如果列数据项的大小差异相当大，则使用varchar。

3 对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间。

4 对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针），所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空 间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利。

5 表中只要存在一个varchar类型的字段，那么所有的char字段都会自动变成varchar类型，因此建议定长和变长的数据分开。

III4编码选择

单字节 latin1

多字节 utf8(汉字占3个字节，英文字母占用一个字节)

如果含有中文字符的话最好都统一采用utf8类型，避免乱码的情况发生。

III5主键选择原则

注：这里说的主键设计主要是针对INNODB引擎

1 能唯一的表示行。

2 显式的定义一个数值类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途。

3 MySQL主键应该是单列的，以便提高连接和筛选 *** 作的效率。

4 主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。

5 尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。

6 MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。

7 MySQL主键应当有计算机自动生成。

8 主键字段放在数据表的第一顺序。

推荐采用数值类型做主键并采用auto_increment属性让其自动增长。

III6其他需要注意的地方

NULL OR NOT NULL

尽可能设置每个字段为NOT NULL，除非有特殊的需求，原因如下：

1 使用含有NULL列做索引的话会占用更多的磁盘空间，因为索引NULL列需要而外的空间来保存。

2 进行比较的时候，程序会更复杂。

3 含有NULL的列比较特殊，SQL难优化，如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。

索引

索引的缺点：极大地加速了查询，减少扫描和锁定的数据行数。

索引的缺点：占用磁盘空间，减慢了数据更新速度，增加了磁盘IO。

添加索引有如下原则：

1 选择唯一性索引。

2 为经常需要排序、分组和联合 *** 作的字段建立索引。

3 为常作为查询条件的字段建立索引。

4 限制索引的数据，索引不是越多越好。

5 尽量使用数据量少的索引，对于大字段可以考虑前缀索引。

6 删除不再使用或者很少使用的索引。

7 结合核心SQL优先考虑覆盖索引。

8 忌用字符串做主键。

反范式设计

适当的使用冗余的反范式设计，以空间换时间有的时候会很高效。

IV Mysql软件优化

开启mysql复制，实现读写分离、负载均衡，将读的负载分摊到多个从服务器上，提高服务器的处理能力。

使用推荐的GA版本，提升性能

利用分区新功能进行大数据的数据拆分

V Mysql配置优化

注意：全局参数一经设置，随服务器启动预占用资源。

key_buffer_size参数

mysql索引缓冲，如果是采用myisam的话要重点设置这个参数，根据（key_reads/key_read_requests）判断

innodb_buffer_pool_size参数

INNODB 数据、索引、日志缓冲最重要的引擎参数，根据（hit riatos和FILE I/O）判断

wait_time_out参数

线程连接的超时时间，尽量不要设置很大，推荐10s

max_connections参数

服务器允许的最大连接数，尽量不要设置太大，因为设置太大的话容易导致内存溢出，需要通过如下公式来确定：

SET @k_bytes = 1024;

SET @m_bytes = @k_bytes 1024;

SET @g_bytes = @m_bytes 1024;

SELECT

(

@@key_buffer_size + @@query_cache_size + @@tmp_table_size+

@@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size+

@@innodb_log_buffer_size+

@@max_connections

( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size+

@@join_buffer_size + @@binlog_cache_size + @@thread_stack

) )

/ @g_bytes AS MAX_MEMORY_USED_GB;

thread_concurrency参数

线程并发利用数量，(cpu+disk)2,根据(os中显示的请求队列和tickets)判断

sort_buffer_size参数

获得更快的--ORDER BY,GROUP BY,SELECT DISTINCT,UNION DISTINCT

read_rnd_buffer_size参数

当根据键进行分类 *** 作时获得更快的--ORDER BY

join_buffer_size参数

join连接使用全表扫描连接的缓冲大小，根据select_full_join判断

read_buffer_size参数

全表扫描时为查询预留的缓冲大小，根据select_scan判断

tmp_table_size参数

临时内存表的设置，如果超过设置就会转化成磁盘表，根据参数(created_tmp_disk_tables)判断

innodb_log_file_size参数(默认5M)

记录INNODB引擎的redo log文件，设置较大的值意味着较长的恢复时间。

Ø innodb_flush_method参数(默认fdatasync)

Linux系统可以使用O_DIRECT处理数据文件，避免OS级别的cache，O_DIRECT模式提高数据文件和日志文件的IO提交性能

innodb_flush_log_at_trx_commit(默认1)

表示每秒进行一次log写入cache，并flush log到磁盘。

表示在每次事务提交后执行log写入cache，并flush log到磁盘。

表示在每次事务提交后，执行log数据写入到cache，每秒执行一次flush log到磁盘。

VI Mysql语句级优化

1 性能查的读语句，在innodb中统计行数,建议另外弄一张统计表，采用myisam，定期做统计一般的对统计的数据不会要求太精准的情况下适用。

2 尽量不要在数据库中做运算。

3 避免负向查询和%前缀模糊查询。

4 不在索引列做运算或者使用函数。

5 不要在生产环境程序中使用select from 的形式查询数据。只查询需要使用的列。

6 查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。

7 where子句尽可能对查询列使用函数，因为对查询列使用函数用不到索引。

8 避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。

9 所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。

10 联表查询的时候，记得把小结果集放在前面，遵循小结果集驱动大结果集的原则。

11 开启慢查询，定期用explain优化慢查询中的SQL语句。

优化“mysql数据库”来提高“mysql性能”的方法有：

1、选取最适用的字段属性。

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2、使用连接（JOIN）来代替子查询(Sub-Queries)。

MySQL从41开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

3、使用联合(UNION)来代替手动创建的临时表。  

MySQL 从40的版本开始支持UNION查询，它可以把需要使用临时表的两条或更多的SELECT查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

4、事务。

要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的 *** 作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都 *** 作成功，要么都失败。

5、锁定表。

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。

6、使用外键。

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引 

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。

8、优化的查询语句 

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

以上就是关于求教，mysql千万级数据多表查询做分页该如何优化全部的内容，包括:求教，mysql千万级数据多表查询做分页该如何优化、如何优化数据库、如何优化mysql数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！