基于MySql 多表分页查询优化技术

通过逻辑判断避免多表扫描及 union 操作, 根据页面偏移量快速定位查 询所要涉及的表, 从而有效避免不必要的操作, 提高 了查询效率

本算法先根据 指定的条件确定查询所要涉及的分表, 再根据查询条
件算出各个表中符合条件的记录数; 然后将各个分表
中符合条件的记录数相加得到符合条件的总记录数,
从而根据页面大小计算记录总页数. 根据用户页面传
过来的页数计算偏移量, 通过偏移量和页面的大小以
及各个表中的符合条件的记录数做比较, 得出需要返
回给用户的数据存在于哪张表中, 然后从该表中返回
给用户指定偏移量以及指定数量的记录即可.

本算法通过逻辑判断精确地得出了数据所在的表, 使每次查
询只需要查询一张表. 在极端的情况下每个表只有少
量的数据需要查询, 多张表才能获取一页的数据, 通过采用精准的单表查询过滤符合条件的数据返回给用
户. 由于当符合条件的数据量比较小时执行计划会合
理利用内存操作以获得较高的查询效率. 通过以上分
析可以看出, 本算法避免了不必要的多表扫描和union.

http://www.c-s-a.org.cn/csa/ch/reader/create_pdf.aspx?file_no=20160830&flag=1&year_id=8&quarter_id=

原文下载:

Mysql使用虚拟列建索引提升查询性能

使用虚拟列绕过最左匹配,减少查询行数,提升性能

假设有一张t_people表,有组合索引(name, age),若name是「李」开头的有许多,则以下查询依然会很慢:

select * from t_people where name like '李%' and age = 8;

解决方法:可以创建一个「虚拟列」(Mysql 5.7+版本支持)name_first,存储name的第一个字,并使用此虚拟列建组合索引:

alter table t_people add name_first varchar(2) generated always as (left(name, 1)), add index(name_first, age);

修改后的查询语句:

select * from t_people where name_first = '李' and age = 8;

在数据表里筛选出每一人的时间最新的一条记录

mysql取分组后最新的一条记录,下面两种方法.一种是先筛选 出最大和最新的时间,在连表查询.一种是先排序,然后在次分组查询(默认第一条),就是最新的一条数据了

方法一:【连表查询,推荐】
#select * from t_assistant_article as a, (select max(base_id) as base_id, max(create_time) as create_time from t_assistant_article as b group by base_id ) as b where a.base_id=b.base_id and a.create_time = b.create_time

方法二:(全表扫描,不推荐)
#select base_id,max(create_time), max(article_id) as article_id from t_assistant_article as b group by base_id
select * from (select * from t_assistant_article order by create_time desc) as a group by base_id

【摘自:http://bbs.csdn.net/topics/390867651】

数据量较大时的分段查询优化

数据表数据较多时,为了让内存够用,需要控制每次查出的数据数量。

通常情况下,我们可以用limit 结合 offset来分段查询。但当在查询过程中,有新数据产生时,offset偏移量会出现问题,导致分段查询不准。

建议方式,使用limit 结合 id 来分段查询。如,

 

mysql的半同步复制(Semi-synchronous Replication)

在Mysql5.5之前,Mysql的复制是异步复制,主库和从库的数据之间存在一定的延迟,这样存在一个隐患:当在主库上写入一个事物并提交成功,而从库尚未得到主库推送的Binlog日志时,主库宕机了,例如主库可能因为磁盘损坏、内存故障等造成主库上该事务Binlog丢失,此时从库就可能损失这个事务,从而造成主从不一致。

为了解决这个问题,Mysql5.5引入了半同步复制机制。在Mysql5.5之前的异步复制时,主库执行完Commit提交操作后,在主库写入Binlog日志后即可成功返回客户端,无需等待Binlog日志传送给从库,如下图, 继续阅读“mysql的半同步复制(Semi-synchronous Replication)”

MySql计数器,如网站点击数,如何实现高性能高并发的计数器功能

要点:

1.将计数器在单独表中存储;

2.使用随机数缓解高并发时造成的锁表等待延时;

3.使用mysql的duplicate key 特性。 继续阅读“MySql计数器,如网站点击数,如何实现高性能高并发的计数器功能”