MySQL常用技巧
正则表达式的使用
| 序列 | 序列说明 |
| ^ | 字符串的开始处进行排序 |
| $ | 在字符串的末尾处进行匹配 |
| . | 匹配任意单个字符串，包括换行服 |
| […] | 匹配括号内的任意字符 |
| {FNXX==XXFN} | 匹配不出括号内任意字符 |
| a* | 匹配零个或多个a(包括空串) |
| a+ | 匹配一个或多个a（不包括字符串) |
| a? | 匹配一个或零个a |
| a1\ | a2 |
| a(m) | 匹配m个a |
| a(m,) | 匹配m个或更多a |
| a(m,n) | 匹配m到n个a |
| a(,n) | 匹配0到n个a |
| (…) | 将模式元素组成单一元素 |
使用
select 'abcdefg' regexp '^a'; .....
如果range()提取随机行
随机抽取某些行
select * from categrory order by rand() limit 5;
利用group by的with rollup 子句
使用group by的with rollup可以检索更多分组聚合信息：
select date_from(payment_date, '%Y-%M'), staff_id, sum(amount) from payment group by date_formate(payment_date, '%Y-%M'), staff_id;
用BIT GROUP FUNCTIONS做统计
使用GROUP BY语句和BIT_AND、BIT_OR函数完成统计工作，这两个函数的一般用途就是做数值之间的逻辑。
优化数据库对象
优化表类型
表需要使用何种数据类型工具应用来判断，虽然考虑字段的长度会有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样既浪费磁盘的存储空间，同时在应用操作时也浪费物理内存MySQL，可以使用函数procedure analyse对当前的表进行分析
select * from tb1_name procedure analyse(); select * from tb2_name procedure analyse(16,256);
输出的每一类信息都对数据表中的列的数据类型提出优化建议。第二语句告诉procedure anaylse不要为那些包含的值多余16个或256个字节的enum类型提出建议，如果没有这个限制，输出的信息可能很长。
ENUM定义通常很难阅读，通过输出信息，可以将表中的部分字段修改为效率更高的字段。
拆分
重置拆分
把主码和一些列放到一个表，然后把住码和另外的列放到另一个表。
- 好处
可以将常用的列放在一起，不常用的列放在一起，使得数据行变少，一个数据页可以存放更多的数据，在查询时会减少I/O次数，缺点：管理冗余，查询所有数据需要用join操作
水平拆分
根据一列或多列数据把数据行放到两个独立的表中：水平拆分会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要UNION操作，缺点：只要索引关键字不大，则在索引查询时，表中增加了2-3倍的数据量，查询时也增加了读一个索引的磁盘次数，所有说拆分要考虑数据量的增长速度。
常用场景
- 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度
- 表中的数据本来就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，而有些数据不常用
- 需要把数据存放在多个介质上：如账单：最近三个月数据存在一个表中，3个月之前的数据存放在另一个表，成功一年的可以存储在单独的存储介质中。
逆规范化
数据库设计时需要满足规范化，但是规范化程度越高，产生的关系就越多。
关系越多直接结果就是表直接的连接操作越频繁，而表连接的操作是性能较低的操作，直接影响到查询的数据。
反规范化的好处在于降低连接操作的需求，降低外码和索引的数目，还可以减少表的树木，相应带来的问题可能出现数据的完整性问题。加快查询速度，但是降低修改速度。好的索引和其他方法经常能够解决性能问题，而不必采用反规范这种方法
采用的反规范化技术
- 增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作
- 增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增加的派生列其他作业是在查询时减少连接操作，避免使用集函数
- 重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表查询组成一个表来减少连接而提高性能
- 分割表
维护数据的完整性
- 批处理维护是指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或修改存储过程对复制或派生列进行修改，这只能对实时性要求不高的情况下使用
- 数据的完整性也可由应用逻辑来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏。特别是在需求变化时，不易于维护
- 使用触发器，对数据的任何修改立即触发对复制列或者派生列的相应修改，触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护，一般来说，是解决这类问题比较好的方法
中间表
对于数据量较大的表，在其上进行统计查询通常会效率很低，并且还要考虑统计查询是
否会对在线的应用产生负面影响。通常在这种情况下，使用中间表可以提高统计查询的效率
session 表记录了客户每天的消费记录，表结构如下：
CREATE TABLE session ( cust_id varchar(10) , --客户编号 cust_amount DECIMAL(16,2), --客户消费金额 cust_date DATE, --客户消费时间 cust_ip varchar(20) –客户IP 地址 )
由于每天都会产生大量的客户消费记录,所以session 表的数据量很大,现在业务部门有
一具体的需求：希望了解最近一周客户的消费总金额和近一周每天不同时段用户的消费总金
额。针对这一需求我们通过2 种方法来得出业务部门想要的结果。
方法1：在session 表上直接进行统计,得出想要的结果。
select sum(cust_amount) from session where cust_date>adddate(now(),-7);
方法2：创建中间表tmp_session，表结构和源表结构完全相同。
CREATE TABLE tmp_session ( cust_id varchar(10) , --客户编号 cust_amount DECIMAL(16,2), --客户消费金额 cust_date DATE, --客户消费时间 cust_ip varchar(20) –客户IP 地址 ) ;
insert into tmp_session select * from session where cust_date>adddate(now(),-7);
转移要统计的数据到中间表,然后在中间表上进行统计，得出想要的结果。
在中间表上给出统计结果更为合适,原因是源数据表(session 表)
cust_date 字段没有索引并且源表的数据量较大，所以在按时间进行分时段统计时效率
很低，这时可以在中间表上对cust_date 字段创建单独的索引来提高统计查询的速度。
中间表在统计查询中经常会用到，其优点如下:
- 中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不
会对在线应用产生负面影响. - 中间表上可以灵活的添加索引或增加临时用的新字段,从而达到提高统计查询
效率和辅助统计查询作用。
