high performance mysql sakila_Mysql High Performance：Advanced Features

Storing Code

1. trigger/stored procedure/stored function;job(events)

2. 优点

节省带宽

集中化业务规则、加强一致性、

减轻release policy和维护

安全上有优势

server缓存执行计划

适用于定时job、不需要第三方支持

应用和db的开发人员分开

3. 缺点

Mysql没提供较好工具，开发调试困难

慢、原始：可用函数少，字符控制难，不易写复杂逻辑

增加应用复杂性

有安全隐患

将负载转移到db，不易扩展而且比app、web server昂贵

sp对资源控制不足，可能会导致server性能下降

Mysql对sp实现有限：执行计划基于连接、cursor被物化为临时表

profiile sp代码很困难

隐藏了复杂性，但可能影响性能

4. Mysql结构和查询优化器对如何使用stored routines和他们的效率如何有限制：

优化器不会使用DETERMINISTIC(whether a routine always produces the same result for given inputs)去优化一个query中的多个call

优化器不能估算执行sf的cost

每个连接都有自己的sp执行计划cache，多个连接同事调用会浪费资源

一般将sr做的小而简单，网络和解析就占比较大比例

5. tigger

不能返回value

good例子：模仿外键在不支持的engine上(MyISAM)

有限制：

每个事件(如after insert)只能用一个trigger

只支持行级：只支持for each row

通用的限制：

隐藏server的实际工作

难于调试

可能导致不易发现的死锁和锁等待

for eache row使他难于使用，因为太慢

不支持原子性，比如MyISAM是不会回滚的

innoDB使用trigger做外键，由于MVCC，BEFORE INSERT检查时没问题，但是其他事务在这之后可能会做DML操作

但是它很有用：在约束、系统维护、同步异常数据

6. Events：

CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK

DO

CALL optimize_tables('somedb');

7. 游标

只读、forward-only

将来可能提供updatable

在open时就已经执行了query

导致额外io，blob/text不能存于内存，会建立disk的临时表；如果数据大小大于tmp_table_size也会在disk建表

不支持客户端游标

8. Prepared Statements

Mysql4.1开始支持

client发送一个query原型；server解析，以特殊优化的结构存储，返回客户端一个stmt handler；客户端包通过声明stmt handler重复执行

更有效的原因

只需要解析一次

执行查询优化一次

通过二进制protocol传递参数，比ascii文本更有效(data只需3byte，ascii 10byte)；尤其是blob和text

仅需要传递参数，而不是整个query

？把参数存于buffer，不需要拷贝到内存

mysql_query()不会执行PS

9. Ps会做的优化

准备：eliminates negations, and rewrites subqueries.

第一次执行：简化嵌套join，将out join转化为inner join

每次执行

Prunes partitions

减少count() min() max()

移除常量子表达式

检测常量table

Propagates equalities

分析 优化ref range index_merge访问

优化join order

PS的SQL接口

可以用于sp构造动态sql

mysql> SET @sql := 'SELECT actor_id, first_name, last_name

-> FROM sakila.actor WHERE first_name = ?';

mysql> PREPARE stmt_fetch_actor FROM @sql;

mysql> SET @actor_name := 'Penelope';

mysql> EXECUTE stmt_fetch_actor USING @actor_name;

PS局限

仅适用于一个连接

5.1版本之前不能使用query cache

如果查询一次，不要使用，有额外负担

如果忘记deallocate会占用server资源