mysql 优化

超大分页问题

limit语法支持两个参数,offset和limit,前者表示偏移量,后者表示取前limit条数据.

例如:返回符合条件的前10条语句

1	select * from user limit 10 ## 返回符合条件的第11-20条数据 select * from user limit 10,20

从上面也可以看出来,limit n 等价于limit 0,n.

性能分析

实际使用中我们会发现,在分页的后面一些页,加载会变慢,也就是说:
select * from user limit 1000000,10 语句执行较慢.那么我们首先来测试一下.
首先是在offset较小的情况下拿100条数据.(数据总量为200左右).然后逐渐增大offset.

select * from user limit 0,100 ---------耗时0.03s 
select * from user limit 10000,100 ---------耗时0.05s 
select * from user limit 100000,100 ---------耗时0.13s 
select * from user limit 500000,100 ---------耗时0.23s 
select * from user limit 1000000,100 ---------耗时0.50s 
select * from user limit 1800000,100 ---------耗时0.98s

可以看到随着offset的增大,性能越来越差.
这是为什么呢?因为limit 10000,10的语法实际上是mysql查找到前10010条数据,之后丢弃前面的10000行,这个步骤其实是浪费掉的.

优化

用id优化

先找到上次分页的最大ID,然后利用id上的索引来查询,类似于

1	select * from user where id>1000000 limit 100.

这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是ID必须是连续的,并且查询不能有where语句,因为where语句会造成过滤数据.

用覆盖索引优化
mysql的查询完全命中索引的时候,称为覆盖索引,是非常快的,因为查询只需要在索引上进行查找,之后可以直接返回,而不用再回数据表拿数据.因此我们可以先查出索引的ID,然后根据Id拿数据.

1	select * from (select id from job limit 1000000,100) a left join job b on a.id = b.id;

耗时0.2秒.

数据库并发量

mysql的最大连接数默认是100
MySQL服务器的最大并发连接数是16384。受服务器配置，及网络环境等制约，实际服务器支持的并发连接数会小一些。

主要决定因素有：

服务器CPU及内存的配置。
网络的带宽。互联网连接中上行带宽的影响尤为明显。
扩展资料：
优化数据库结构：组织数据库的schema、表和字段以降低I/O的开销，将相关项保存在一起，并提前规划，以便随着数据量的增长，性能可以保持较高的水平,设计数据表应尽量使其占用的空间最小化，表的主键应尽可能短。

对于InnoDB表，主键所在的列在每个辅助索引条目中都是可复制的，因此如果有很多辅助索引，那么一个短的主键可以节省大量空间。仅创建需要改进查询性能的索引。
索引有助于检索，但是会增加插入和更新操作的执行时间。

InnoDB的ChangeBuffering特性：
InnoDB提供了changebuffering的配置，可减少维护辅助索引所需的磁盘I/O。大规模的数据库可能会遇到大量的表操作和大量的I/O，以保证辅助索引保持最新。
当相关页面不在缓冲池里面时，InnoDB的changebuffer将会更改缓存到辅助索引条目。

从而避免因不能立即从磁盘读取页面而导致耗时的I/O操作。当页面被加载到缓冲池时，缓冲的更改将被合并，更新的页面之后会刷新到磁盘。这样做可提高性能，适用于MySQL5.5及更高版本。

怎么解决高并发问题

限流算法目前程序开发过程常用的限流算法有两个：
漏桶算法和令牌桶算法。
漏桶算法漏桶算法的原理比较简单，请求进入到漏桶中，漏桶以一定的速率漏水。

当请求过多时，水直接溢出。

可以看出，漏桶算法可以强制限制数据的传输速度。如图所示，把请求比作是水滴，水先滴到桶里，通过漏洞并以限定的速度出水，当水来得过猛而出水不够快时就会导致水直接溢出，即拒绝服务。图片来自网络漏桶的出水速度是恒定的，那么意味着如果瞬时大流量的话，将有大部分请求被丢弃掉（也就是所谓的溢出）。令牌桶算法令牌桶算法的原理是系统以一定速率向桶中放入令牌，如果有请求时，请求会从桶中取出令牌，如果能取到令牌，则可以继续完成请求，否则等待或者拒绝服务。

这种算法可以应对突发程度的请求，因此比漏桶算法好。图片来自网络漏桶算法和令牌桶算法的选择两者的主要区别漏桶算法能够强行限制处理数据的速率，不论系统是否空闲。而令牌桶算法能够在限制数据的平均处理速率的同时还允许某种程度的突发流量。

如何理解上面的含义呢？漏桶算法，比如系统吞吐量是 120/s，业务请求 130/s，使用漏斗限流 100/s，起到限流的作用，多余的请求将产生等待或者丢弃。对于令牌桶算法，每秒产生 100 个令牌，系统容量 200 个令牌。正常情况下，业务请求 100/s 时，请求能被正常被处理。

当有突发流量过来比如 200 个请求时，因为系统容量有 200 个令牌可以同一时刻处理掉这 200 个请求。如果是漏桶算法，则只能处理 100 个请求，其他的请求等待或者被丢弃。

并发如何处理

mysql的最大连接数默认是100, 这个数值对于并发连接很多的数据库应用是远远不够的，当连接请求大于默认连接数后，就会出现无法连接数据库的错误，因此我们需要把它适当调大一些。

调节方法为：

linux服务器中：改my.cnf中的值就行了
Windows服务器中（我用的）: 在文件“my.ini”中找到段 [mysqld],在其中添加一行max_cOnnections=200 ### 200可以更改为想设置成的值. 然后重启”mysql”服务。

/mysqladmin所在路径/mysqladmin -uroot -p variables输入root数据库账号的密码后可看到
| max_connections | 1000 |
其他需注意的：
在编程时，由于用mysql语句调用数据库时，在每次之执行语句前，会做一个临时的变量用来打开数据库，所以你在使用mysql语句的时候，记得在每次调用完mysql之后就关闭mysql临时变量。

另外对于访问量大的，可以考虑直接写到文本中，根据预测的访问量，先定义假若是100个文件文件名依次为1.txt,2.txt…100.txt。需要的时候，再对所有文本文件中的数据进行分析，再导入数据库。

如何处理并发问题

想要知道如何处理数据并发，自然需要先了解数据并发。什么是数据并发操作呢？就是同一时间内，不同的线程同时对一条数据进行读写操作。

在互联网时代，一个系统常常有很多人在使用，因此就可能出现高并发的现象，也就是不同的用户同时对一条数据进行操作，如果没有有效的处理，自然就会出现数据的异常。

而最常见的一种数据并发的场景就是电商中的秒杀，成千上万个用户对在极端的时间内，抢购一个商品。针对这种场景，商品的库存就是一个需要控制的数据，而多个用户对在同一时间对库存进行重写，一个不小心就可能出现超卖的情况。针对这种情况，我们如何有效的处理数据并发呢？
第一种方案、数据库锁从锁的基本属性来说，可以分为两种：一种是共享锁（S），一种是排它锁（X）。在MySQL的数据库中，是有四种隔离级别的，会在读写的时候，自动的使用这两种锁，防止数据出现混乱。

好了，锁说完了，但是，我们的数据库锁，并不能有效的解决并发的问题，只是尽可能保证数据的一致性，当并发量特别大时，数据库还是容易扛不住。

那解决数据并发的另一个手段就是，尽可能的提高处理的速度。因为数据的IO要提升难度比较大，那么通过其他的方式，对数据进行处理，减少数据库的IO，就是提高并发能力的有效手段了。
最有效的一种方式就是：缓存想要减少并发出现的概率，那么读写的效率越高，读写的执行时间越短，自然数据并发的可能性就变小了，并发性能也有提高了。

还是用刚才的秒杀举例，我们为的就是保证库存的数据不出错，卖出一个商品，减一个库存，那么，我们就可以将库存放在内存中进行处理。这样，就能够保证库存有序的及时扣减，并且不出现问题。这样，我们的数据库的写操作也变少了，执行效率也就大大提高了。当然，常用的分布式缓存方式有：Redis和Memcache，Redis可以持久化到硬盘，而Memcache不行，应该怎么选择，就看具体的使用场景了。

当然，缓存毕竟使用的范围有限，很多的数据我们还是必须持久化到硬盘中，那我们就需要提高数据库的IO能力，这样避免一个线程执行时间太长，造成线程的阻塞。那么，读写分离就是另一种有效的方式了当我们的写成为了瓶颈的时候，读写分离就是一种可以选择的方式了。我们的读库就只需要执行读，写库就只需要执行写，把读的压力从主库中分离出去，让主库的资源只是用来保证写的效率，从而提高写操作的性能。

如何增加并发数

#方法一：
进入MYSQL安装目录 打开MYSQL配置文件 my.ini 或 my.cnf查找 max_cOnnections=100 修改为 max_cOnnections=1000
服务里重起MYSQL即可
#方法二：
#MySQL的最大连接数默认是100客户端登录：
mysql -uusername -ppassword
#设置新的最大连接数为200：
mysql> set GLOBAL max_cOnnections=200
#显示当前运行的Query：
mysql> show processlist
#显示当前状态：
mysql> show status
#退出客户端：
mysql> exit
#查看当前最大连接数：
mysqladmin -uusername -ppassword variables

mysql服务器最大连接数值的设置范围比较理想的是：
服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，
如果在10%以下，说明mysql服务器最大连接上限值设置过高.

MySQL的max_connections参数用来设置最大连接（用户）数。每个连接MySQL的用户均算作一个连接。

MySQL无论如何都会保留一个用于管理员（SUPER）登录的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数为max_connections+1；
这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准；
增加max_connections参数的值，不会占用太多系统资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等；

随笔

MySQL服务器的最大并发连接数是16384。
题目：现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

SELECT
    u.device_id,
    university,
    SUM(IF(result IS NOT NULL, 1, 0)) AS questino_cnt,
    SUM(IF(result = "right", 1, 0)) AS right_question_cnt,
    SUM(IF(result = "wrong", 1, 0)) AS w_question_cnt
FROM
    user_profile u
    LEFT JOIN question_practice_detail q ON u.device_id = q.device_id
    AND MONTH(q.`date`) = "08"
WHERE 
    university = "复旦大学"
GROUP BY
    u.device_id;

select 
d.difficult_level,
SUM(IF(q.result = "right", 1, 0)) / COUNT(q.result) as correct_rate
from user_profile as u
RIGHT join question_practice_detail  as q
on u.device_id = q.device_id
left join question_detail as  d
on d.question_id = q.question_id
where  u.university = '浙江大学'
group by d.difficult_level

select 
count(DISTINCT device_id) as did_cnt,
count(question_id) as question_cnt
from question_practice_detail
where MONTH(`date`) = "08"

返回 MySql 系列