mysql千万数据表管理界面

这段时间，系统一步步走来，用户数据由原来的上百万到现在的几千万，除了前台接口做了很多改变，管理界面的修改也不少，数据量上来后，一个小的需求可能就涉及到大量的改造。这里介绍下管理界面时候的查询改变。

服务介绍：

A表现在业务分，一张2000W，一张几百万,
还有一张1000W的用户表,更新操作较多，

数据库服务器，32G内存，16核，centos,mysql5.7

分布查询count 处理

在单表还是几百万的时候，分布数据查询就很慢了，10条数据的请求查了10来秒，而且还是有索引的情况下，

关键是那个count,之前的分页插件都是把count一起带过来的,而count会消耗掉10S中的9S，为了用户体验，我们做了两点操作：

• 把count异步化，即查10条数据时不去count,返回到界面后用ajax请求拿到count返回到界面。由于是异步，前台界面可能会被用户点多次，如果前面的查询比后面的返回晚，则count值会被覆盖，可以采用时间戳参数方式，返回时识别此次请求的时间戳是否一致，一致则用此count.
• count的查询请求采用参数合集作为key,值存count和时间进行缓存，1天失效，这样在第一次请求的时候需要count后续都直接取缓存,

缓存的key可以采用 支付接口常常使用的 数据摘要方式进行加密.即把
第一步，设所有发送或者接收到的数据为集合M，将集合M内非空参数值的参数按照参数名ASCII码从小到大排序（字典序），使用URL键值对的格式（即key1=value1&key2=value2…）拼接成字符串stringA.
第二步，对stringA进行MD5运算，再将得到的字符串所有字符转换为大写，得到sign值signValue。

分页查询缓慢

但过了一定阶段，对查询条件加了索引，但limit 还是有点慢,后来发现是 order 慢，业务上有时间，这个时间我们是存string的，采用这个来order发现效果不太理想，于是采用timestamp新建了一个字段，采用mysql自动更新的机制，发现效果比较好，速度也提了不少, 实际效果是，2000W那张表查询前面的页数也是毫秒查，无压力 。

一些其它情况

期间发生过几次情况,这里进行描述.

一个是加索引的情况

大表加索引是个比较头疼的问题,如果你的表只是insert还好，直接加即可，不会锁住，如果表有update,则要想想办法了，思路是用tmp 表和 原表 rename,然后给原表加索引，加完后再rename回来,完成数据的切换。具体可参考另一篇http://blog.csdn.net/mingover/article/details/78588208

orderby时缓慢(时间花在Creating sort index )

如果遇到limit 前几页的时候也会缓慢，而相应的索引也加了，有可能是order by 的字段没有在where里面引起的，可尝试把column加到where语句中.具体可参考http://blog.csdn.net/mingover/article/details/79066064

其它

• 联表的问题
  联表虽然有时好用，但业务分库后就是很大的问题，这些逻辑不好理，所以强烈建议是多个小查询 替换联表查。而且一般来说联表比代码逻辑可读性要差。建议是在代码层面提供更多的crud方法，让业务开发尽量少写sql,写也要注明单表查。
• 没加索引,导致mysql服务器压力巨大
  有一段时间发现mysql的cpu压力巨大，16核在高峰时会全占满，刚开始还以为是业务访问大，后来发现是有定时器的 几条sql引起的。虽然只有几条sql,但却占用了mysql的大量资源（时间主要花在Sending data),所以需要时刻注意会话中的长时间sql,可以采用“select * from information_schema.PROCESSLIST where state is not null and state !=” ORDER BY TIME DESC;”来查，也可以直接去访问 慢查询文件看看。
• sql的问题，
  count不能加 != ” ，发现加了这个后，1000S完不成一个查询(1000w的表), like 千万不能 前面加%.类似这些可以直接百度多了解一下.

结尾

关于大表的横向切分内总部讨论过多次，优先肯定业务切分，已经切过几次了，效果挺好，改动也小。但对于一些难以用业务分的，则要考虑hash切或时间切，时间切的难度相对小，hash的话改动较大，需要谨慎。如果数据库服务有压力,可考虑先业务分库.