mysql优化介绍2

正则表达式

是用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。

序列	序列说明
^	在字符串开始处进行匹配
$	在字符串结尾进行匹配
.	匹配任意单个字符，包括换行符
[…]	匹配中括号的任意字符
[^…]	匹配不在中括号任意字符
a*	匹配零个或多个a 包括空串
a+	匹配一个或多个a 不包括空串
a?	匹配零个或一个a
a1|a2	匹配a1或a2
a(m)	匹配m个a
a(m,)	匹配m个a或更多a
a(m,n)	匹配m到n个a
a(,n)	匹配零个或n个a
(….)	将多个元素组成一个整体

1.使用^匹配是否以a开头

mysql> select 'abcdefg' regexp '^a';
+-----------------------+
| 'abcdefg' regexp '^a' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

2.使用$匹配是否以g结尾

mysql> select  'abcdefg' regexp 'g$';
+-----------------------+
| 'abcdefg' regexp 'g$' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

3.使用.匹配单个字符

mysql> select 'abcedfg' regexp '.h' ,'abcdefg' regexp '.f';
+-----------------------+-----------------------+
| 'abcedfg' regexp '.h' | 'abcdefg' regexp '.f' |
+-----------------------+-----------------------+
|                     0 |                     1 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

4.[..]匹配是否符合里面的任意字符

mysql> select 'abcdefg' regexp "[cd]";
+-------------------------+
| 'abcdefg' regexp "[cd]" |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

5.[^..]匹配不符合里面的任意字符

mysql> select  'abcdefg' regexp "[^xyz]" ,'x' regexp "[^xyz]"

+---------------------------+---------------------+
| 'abcdefg' regexp "[^xyz]" | 'x' regexp "[^xyz]" |
+---------------------------+---------------------+
|                         1 |                   0 |
+---------------------------+---------------------+
1 row in set (0.00 sec)

使用rand()提取随机行

可以利用order by 子句完成随机抽取某些行的功能，他的原理就是order by rand()能够数据随机排序。

mysql> select  *  from category order by rand() limit 5;
+-------------+-----------+---------------------+
| category_id | name      | last_update         |
+-------------+-----------+---------------------+
|          15 | Sports    | 2006-02-15 04:46:27 |
|           2 | Animation | 2006-02-15 04:46:27 |
|           5 | Comedy    | 2006-02-15 04:46:27 |
|          14 | Sci-Fi    | 2006-02-15 04:46:27 |
|           9 | Foreign   | 2006-02-15 04:46:27 |
+-------------+-----------+---------------------+
5 rows in set (0.00 sec)

使用group by 的with rollup

使用group by 的with rollup 子句可以检索更多分组聚合的信息，不仅仅获取group by 检索的聚合信息，还能够检索本组类的整体聚合信息。

没有使用with rollup情况，

mysql> select cid,parent_cid,sum(sort_order) from nb_dim_item_cat group by cid,parent_cid limit 5;
+--------+------------+-----------------+
| cid    | parent_cid | sum(sort_order) |
+--------+------------+-----------------+
| 11     | 0          |              31 |
| 1101   | 0          |              29 |
| 110201 | 11         |               8 |
| 110202 | 11         |               5 |
| 110203 | 11         |               4 |
+--------+------------+-----------------+
5 rows in set (0.18 sec)

下面是有使用with rollup，明显可以看到多出的信息

mysql> select cid,parent_cid,sum(sort_order) from nb_dim_item_cat group by cid,parent_cid with rollup limit 6;
+--------+------------+-----------------+
| cid    | parent_cid | sum(sort_order) |
+--------+------------+-----------------+
| 11     | 0          |              31 |
| 11     | NULL       |              31 |
| 1101   | 0          |              29 |
| 1101   | NULL       |              29 |
| 110201 | 11         |               8 |
| 110201 | NULL       |               8 |
+--------+------------+-----------------+
6 rows in set (0.79 sec)

优化数据库对象

优化表的数据类型

在mysql中可以使用procedure analyse()对当前表进行分析，该函数可以对数据库表的数据类型提出优化建议，用户可以根据实际情况是否进行优化

mysql> select *  from  film procedure analyse()\G
*************************** 1. row ***************************
             Field_name: sakila.film.film_id
              Min_value: 1
              Max_value: 1000
             Min_length: 1
             Max_length: 4
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 500.5000
                    Std: 288.6750
      Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL

从结果可以看到，film_id 列的Min_value,Max_value,Min_length,Max_length,根据这些统计值，可以对列做出优化，同时也给出了优化建议 optimal_fileldtype.

拆分表提高标的访问效率

拆分是指对数据表进行拆分，做过针对MyISAM类型的表进行，那么有两种拆分方式

1.垂直拆分，

即把主列和其他一些列放到一个表，然后把主类和另外的列放到一个表，如果一个表中一些列常用，而另一些列不常用，则可以进行垂直拆分，垂直拆分是的数据行变小，一个数据也就能够存放更多的数据，减少了I/O次数，但是管理冗余，查询所有需要join操作

2.水平拆分

根据一个列或多个列把数据放到独立的表，通常有以下几种情况。

• 表很大，分割后可以降低在查询需要读的数据和索引的页数，同时也降低了索引层数，提高查询速度

• 表的数据本来就具有独立性，例如记录各个地区的数据或不同时期的数据，特别是有些数据常用，有些不常用的数据

• 需要把数据放到多个介质上

水平拆分增加了复杂度，他通常查询是需要多个表明，查询多有需要union操作，在多数据应用，这种复杂性会超过他到来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加2-3倍数据量，也就增加了访问索引层磁盘的次数。水平拆分要考虑数据量的增长速度。

逆规范化

一般我们设置数据表的时候，都要满足规范化，如果数据的规范化越高，他的数据表关系就越复杂。这样就会产生连接操作比较繁杂，影响数据的查询，所以，对于查询多的应用，我们要按照情况进行相应的逆规范化。

比如，我要查询用户每个月的账单，我们就会设计一张表包含姓名和属性信息如A，另外一种表用户的编号和他对应的账单如B，查询我们需要的数据，此时由于表B里面没有用户的姓名，这个时候我们就要连表查询，但是我们如果在表B冗余一个用户名称，这样就可以有更好的性能.

范规范化，好处就是降低连续操作的需求，降低外码和索引的数目，还可能减少表的数据，但是，他带来的问题就是数据完整性问题，需要维护多种表的同一个字段，查询速度快，更新速度慢，

常用的范规范技术

1. 增加冗余列，即在多个表中有同一个字段，避免在查询进行连表查询

2. 增加派生列，即增加的列是其他表数据计算出来的

3. 重新组表，即如果多个用户查看两个表连接出来的数据，放到另外一个新表中。

4. 分割表，水平拆分，垂直拆分，

逆规范化保证数据完整性手段

1. 批处理维护，是指对冗余列或派生列积累一定时间后，运行批量处理作业进行派生列更改

2. 使用应用逻辑完成，这就要在同一个事物中，对涉及的表进行增删改，因为同一个逻辑在必须在所有应用中使用和维护，容易遗漏，不易于维护

3. 使用触发器，对数据任何修改，立刻触发对派生列的响应的修复，触发器是实时的。

使用中间表提高统计查询速度

1. 中间表复制源表部分数据，并且与原表相隔离。在中间表做统计不会影响线上用户产生负面影响

2. 中间表可以灵活的的添加索引，或增加临时用的字段，从而达到提高统计查询效率和辅助统计查询作用。