Mysql基础篇--面试优化数据库对象和使用技巧
Mysql基础篇--面试优化数据库对象和使用技巧
正则表达式
是用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。
序列 | 序列说明 |
|---|---|
^ | 在字符串开始处进行匹配 |
$ | 在字符串结尾进行匹配 |
. | 匹配任意单个字符，包括换行符 |
[…] | 匹配中括号的任意字符 |
[^…] | 匹配不在中括号任意字符 |
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 检索的聚合信息，还能够检索本组类的整体聚合信息。
mysql> select date_format(payment_date,'%Y-%m'),staff_id,sum(amount) from payment group by date_format(payment_date,'%Y-%m'),staff_id;
+-----------------------------------+----------+-------------+
| date_format(payment_date,'%Y-%m') | staff_id | sum(amount) |
+-----------------------------------+----------+-------------+
| 2005-05 | 1 | 2621.83 |
| 2005-05 | 2 | 2202.60 |
| 2005-06 | 1 | 4776.36 |
| 2005-06 | 2 | 4855.52 |
| 2005-07 | 1 | 14003.54 |
| 2005-07 | 2 | 14370.35 |
| 2005-08 | 1 | 11853.65 |
| 2005-08 | 2 | 12218.48 |
| 2006-02 | 1 | 234.09 |
| 2006-02 | 2 | 280.09 |
+-----------------------------------+----------+-------------+
10 rows in set (0.05 sec)
下面是没有使用with rollup，明显可以看到多出的信息
mysql> select date_format(payment_date,'%Y-%m'),staff_id,sum(amount) from payment group by date_format(payment_date,'%Y-%m'),staff_id with rollup;
+-----------------------------------+----------+-------------+
| date_format(payment_date,'%Y-%m') | staff_id | sum(amount) |
+-----------------------------------+----------+-------------+
| 2005-05 | 1 | 2621.83 |
| 2005-05 | 2 | 2202.60 |
| 2005-05 | NULL | 4824.43 |
| 2005-06 | 1 | 4776.36 |
| 2005-06 | 2 | 4855.52 |
| 2005-06 | NULL | 9631.88 |
| 2005-07 | 1 | 14003.54 |
| 2005-07 | 2 | 14370.35 |
| 2005-07 | NULL | 28373.89 |
| 2005-08 | 1 | 11853.65 |
| 2005-08 | 2 | 12218.48 |
| 2005-08 | NULL | 24072.13 |
| 2006-02 | 1 | 234.09 |
| 2006-02 | 2 | 280.09 |
| 2006-02 | NULL | 514.18 |
| NULL | NULL | 67416.51 |
+-----------------------------------+----------+-------------+
16 rows in set (0.04 sec)
使用bit group functions做统计
假设选择有一个任务，一个超市里面要记录每个用户每次来超市都购买的了那些商品。假设超市只有面包，牛奶，啤酒，饼干，通常我们会怎么办，
- 建立一张购物清单(用户，商品id)，再建立购买商品的信息(价格，数量),这种数据表过于复杂.
- 也可以使用单张表，一个字段记录购买商品的id，但是如果这样购买商品过多，需要很大的空间存储.
- 另一种就是，使用一个字段表示顾客购买的商品的信息，但是这个字段是一个数值型，而不是一个字符型，该字段存储一个十进制数据，当他转成二进制的时候，那么每一位代表一种商品，如果所在位是1，代表购买过，0代表没有购买过，比如一个数字5，他的二进制就是0101，依次从右到左代表面包，牛奶，饼干，啤酒，那么0101，就说明买了面包和饼干.
举例说明
mysql> create table order_rab (id int ,customer_id int ,kind int );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into order_rab values (1,1,5),(2,1,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into order_rab values (3,2,3),(4,2,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
初始化1和2用户，查看两位顾客买了那些品种的商品
mysql> select customer_id ,bit_or(kind) from order_rab group by customer_id;
+-------------+--------------+
| customer_id | bit_or(kind) |
+-------------+--------------+
| 1 | 5 |
| 2 | 7 |
+-------------+--------------+
2 rows in set (0.01 sec)
5的二进制是0101，代表在本超市购买过面包和饼干，7的二进制0111，代表买过面包，牛奶，饼干，
同理可以统计使用bit_and 统计每个顾客每次开都会购买什么商品。
mysql> select customer_id ,bit_and(kind) from order_rab group by customer_id;
+-------------+---------------+
| customer_id | bit_and(kind) |
+-------------+---------------+
| 1 | 4 |
| 2 | 0 |
+-------------+---------------+
2 rows in set (0.00 sec)
4的二进制是0100，代表每次都会买饼干，0代表每次都会买不同的商品
优化数据库对象
优化表的数据类型
在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冗余一个用户名称，这样就可以有更好的性能.
范规范化，好处就是降低连续操作的需求，降低外码和索引的数目，还可能减少表的数据，但是，他带来的问题就是数据完整性问题，需要维护多种表的同一个字段，查询速度快，更新速度慢，
常用的范规范技术
- 增加冗余列，即在多个表中有同一个字段，避免在查询进行连表查询
- 增加派生列，即增加的列是其他表数据计算出来的
- 重新组表，即如果多个用户查看两个表连接出来的数据，放到另外一个新表中。
- 分割表，水平拆分，垂直拆分，
逆规范化保证数据完整性手段
- 批处理维护，是指对冗余列或派生列积累一定时间后，运行批量处理作业进行派生列更改
- 使用应用逻辑完成，这就要在同一个事物中，对涉及的表进行增删改，因为同一个逻辑在必须在所有应用中使用和维护，容易遗漏，不易于维护
- 使用触发器，对数据任何修改，立刻触发对派生列的响应的修复，触发器是实时的。
使用中间表提高统计查询速度
- 中间表复制源表部分数据，并且与原表相隔离。在中间表做统计不会影响线上用户产生负面影响
- 中间表可以灵活的的添加索引，或增加临时用的字段，从而达到提高统计查询效率和辅助统计查询作用。