mysql优化

MySQL着重注意的点

1.insert into

insert into table_name(column_name…) values (column_value…)

每个列必须提供一个值， 如果某个列没有值，应该使用NULL值（前提该表允许对该列指定空值);
各个列必须以它们在表定义中出现的次序填充;(insert 高度依赖于特定次序的SQL语句)
最安全的写法是 列出所有的column_name (除该列允许NULL值或给出默认值)

注：数据检索中，加入关键字LOW_PRIORITY，降低优先级，可以提高整体性能。

2.insert select

注：不一定要求列名匹配，但是列的位置要一 一对应

3.upate set column_name = column_value… where …(更新行)

用update语句更新多行，并且在更新是出现错误，除非用ignore关键词，即 update ingnore…

注：不要省略where字句，可以默认为update语句一定含有where关键字

更新表 alter table

1.加列 alter table column add column_name column_type;
2.删除列 alter table column drop column_name column_type;

使用alter table 在进行改动前做一个完整的备份，否则会丢失数据。

4.delete from table where … /drop table/rename table

清除数据可以用 truncate table…
清除表格 使用drop table
重命名表格 rename table

注：不要省略where字句，可以默认为delete语句一定含有where关键字 使用update，delete要仔细检查where语句是否正确，以求结果的严谨。

5. null值

null值与空串不同。null值是没有值，不是空串。如果指定 ’ ’ (两个单引号，其间没有字符)，这在not null列中是允许的。空串是一个有效的值，不是无值。null值用关键字NULL而不是空串指定。

6.指定默认值

MySQL不允许使用函数作为默认值，它只支持常量，其不能为null。

7.引擎ENGINE

InnoDB 不支持全文本搜索，支持事务处理
MyISAM 不支持事务处理，支持全文本搜索
可以通过多个表使用不同引擎实现相应的功能，但是外键不能跨引擎。

进阶

1.视图(create view)：主要用于数据检索（select）

视图的规则
1.命名唯一
2.视图检索数据的select语句中含有order by，则视图order by 会被覆盖
3.不能索引，也不能有关联的触发器或默认值
show create view 查看视图
drop view 删除视图
create or replace view 更新视图

应用场景

用于隐藏复杂的sql语句，即sql语句过于复杂，不能通过阅读理解的话，可以通过视图简化理解难度。

原语句:（以下笛卡尔积关联查询，禁止使用，以免出现事故）

视图处理后：

注：如视图出现分组(group by、having),联结,子查询,并,聚集函数(MAX()等),distinct,计算，不能进行视图更新操作。

2.使用存储过程 (重中之重)

存储过程的好处： 简单、安全、高性能。

1.BEGIN 和END语句限定存储过程体

2.变量名 所有MySQL变量必须以@开始 即:@column_name,@param

完整例子：

2.1使用游标(只能用于存储过程)

由于业务上涉及到游标的使用，因此在此节点着重讲解游标的作用。

通过数据库检索操作得到一组结果集，需要在其基础上前进或后退一行或多行，这时候就需要用到游标(cursor)。
值得注意的是: 它不是一条select语句，而是检索出来的结果集。
使用之前，需要进行声明 declare
结束游标，必须关闭游标 close

通过以下存储过程理解

fetch语句分别访问每一行

create PROCEDURE processorders(); //创建存储过程
BEGIN
DECLARE o INT //下标
DECLARE ordernumbers CURSOR //定义游标
FOR
SELECT order_num FROM orders;
OPEN ordernumbers; //打开游标
FETCH ordernumbers into o //检索第一行
CLOSE ordernumbers; //关闭游标
END

触发器（delete、insert、update）

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT ‘Product added’；
仅支持表，视图不支持(临时表也不支持)
最多六个(更新前后、插入前后、删除前后)
DROP TRIGGER newproduct

demo

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

CREATE TRIGGER updatevender BEFORE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

事务处理

管理必须按顺序执行的MySQL操作，以保证数据库不包含不完整的操作结果。
利用事务处理，可以保证一组操作不会中途停止，如果发生错误，则进行回退以恢复数据库到某个已知且安全的状态。

注: 事务–指一组sql语句
回退–指撤销指定sql语句的过程
提交–指将未存储的sql语句结果写入数据库表
保留点–指事务处理中设置的临时占位符，对其进行回退

事务开始
START TRANSACTION
事务回退（回退START TRANSACTION）
ROLLBACK
注：只能回退插入、更新、删除
事务提交
COMMIT
注：当COMMIT或ROLLBACK语句执行后，事务会自动关闭
保留点
回退到某一事务上
SAVEPOINT delete1;
ROLLBACK TO delete1;
注: 保留点越多越好，可通过RELEASE SAVEPOINT释放保留点
set autocommit = 0 不自动提交更改

全球化和本地化

重要术语: 字符集、编码、校对；
show character set
show collation;

用户权限