MYSQL:看书笔记

一、开发

1.1视图

简单：不需要关心后面表的结构、关联条件和筛选条件，是过滤好的符合条件的结果集。

安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列。

数据独立：源表增加列对视图没有影响，源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

1.创建视图

CREATE [OR REPLACE] VIEW view_name AS select_statement

WITH [CASCADED|LOCAL] CHECK OPTION

句中WITH [CASCADED|LOCAL] CHECK OPTION决定了是否允许更新数据使记录不再满足视图的条件,LOCAL只要满足本视图条件就可更新,默认为CASCADED,满足所有针对该视图的所有视图的条件.

2.修改视图

ALTER [OR REPLACE] VIEW view_name AS select_statement

WITH [CASCADED|LOCAL] CHECK OPTION

3.MySQL视图的一些限制:FROM后面不能包含子查询

以下视图不可更新

(1)包含以下关键字:聚合函数(SUM,MAX,MIN,COUNT等),DISTINCT,

GROUP BY,HAVING,UNION 或者UNION ALL

(2)常量视图

(3)SELECT中包含子查询

(4)JOIN

(5)FROM一个不能更新的视图

(6)WHERE字句的子查询引用了FROM字句中的表

1.2 存储过程和函数

存储过程和函数是事先经过编辑并存储在数据库中的一段SQL语句的集合。

区别:函数必须有返回值,存储过程没有,存储过程的参数可以使用IN,OUT,INOUT, 函数的参数只能是IN类型的。

技巧:DELIMITER $$|其他字符，将结束符从”;”改为”$$”,在存储过程中的”;”不会被解释为结束符号。

1.创建存储过程

CREATE PROCEDURE sp_name([in|out|inout] 参数 type)

BEGIN

...

END

2.调用存储过程

CALL sp_name()

3.变量的使用

定义:DECLARE var_name[,...] type [DEFAULT value]

赋值:SET var_name = expr;

通过查询赋值：SELECT col_name INTO var_name table_expr 查询结果只能有一条

4.常用操作

(1)show procedure status 显示数据库中所有存储的存储过程基本信息

(2)show create procedure sp_name显示某一个mysql存储过程的详细信息

5.流程控制

(1)顺序结构
(2)分支结构
if  ...  case

(3)循环结构
for循环 while循环 loop循环(LEAVE语句来退出循环) repeat until循环(满足条件,退出循环)

ITERATE语句:跳过当前循环剩下的语句,直接进入下一循环,类似于java的continue

注：区块定义，常用
begin
......
end;
也可以给区块起别名，如：
lable:begin
...........
end lable;
可以用leave lable;跳出区块，执行区块以后的代码

1.3事件调度器

MySQL5.1后新增功能,理解为时间触发器,类似于Linux的任务调度器crontab.

   CREATE EVENT event_name

ON SCHEDULE 指定事件在何时执行及执行频次

DO 执行的具体操作或事件

eg:CREATE EVENT myevent

ON SCHEDULE

EVERY 5 SECOND

DO

INSERT INTO my_event(id,time)VALUES('succ',NOW());

SHOW EVENTS;查看调度器状态

SHOW VARIABLES LIKE '%scheduler%';查看事件调度器状态

SET GLOBAL event_scheduler = 1;打开调度器

ALTER EVENT myevent DISABLE;禁用调度器

SHOW PROCESSLIST; 查看进程

DROP EVENT myevent2;删除调度器

 

1.4触发器

1.创建

DELIMITER $$

CREATE TRIGGER trigger_name AFTER|BEFORE   INSERT|UPDATE|DELETE

ON tbl_name FOR EACH ROW

BEGIN

...;

END;

$$

DELIMITER ;

注意:触发器只能创建在永久表上,不能在临时表上创建.

对于有重复记录,需要进行UPDATE的INSTER,出发顺序是:BEFORE INSERT>>BEFORE UPDATE>>AFTER UPDATE

2.删除触发器

DROP TRIGGER trigger_name;

一次可以删除一个触发程序

3.限制

不能在触发器中使用事务的语句;

不能调用将数据返回给客户端的存储程序,

不能采用CALL语句的动态SQL,

允许存储程序通过参数将数据返回触发程序.

二、引擎、索引和函数

1.MyISAM和InnoDB存储引擎

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引,MyISAM存储引擎的表,索引前缀长度可达到1000字节长,InnoDB存储引擎的表,索引的前缀长度最长是767字节.

MyISAM是默认的,不支持事物、外键,优势是访问速度快,对事物完整性没有要求或以SELECT、INSERT为主的应用用此引擎.

InnoDB具有提交，回滚，崩溃恢复能力的事物安全，写的效率低，会占用更多的磁盘空间以保留数据和索引。支持外键约束。

2.设计索引原则

创建索引:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[USING index_type]

ON tbl_name(index_col_name[(length)] [ASC|DESC],...);

  (1)搜索的索引列,不一定是所要选择的列.最适合索引的列是出现在WHERE子句中的列,或连接子句中指定的列,而不是出现在SELECT关键字后的选择列表中的列.

  (2)使用唯一索引

  (3)使用短索引,

  (4)利用最左前缀,

  (5)不要过度索引,对于InnoDB存储引擎的列,记录默认会按照一定的顺序保存,有明确定义的主键,则按照主键顺序保存.

3.HASH索引使用要注意:

  (1)HASH索引只用于使用=或<=>操作符的等式比较

  (2)优化器不能使用HASH索引来加速ORDER BY操作

  (3)Mysql不能确定在两个值之间大约有多少行,如果将一个MYISAM表改为HASH索引的MEMORY表,会影响一些查询的执行效率

  (4)只能使用整个关键字来搜索一行

4.BTREE索引

可使用>,<,>=,<=,BETWEEN,!=,<>,LIKE'pattern'('pattern'不以通配符开始)

5.字符串常用函数

  (1)CONCAT(S1,S2,...Sn):将传入的参数连接为一个字符串

  (2)INSERT(str,x,y,instr):将字符串str从第x位置开始,y个字符长的子串替换为字符串instr

  (3)LOWER(str)和UPPER(str):将字符串转换为小写或大写

  (4)LEFT(str,x)和RIGHT(str,x):分别返回字符串最左边/右边的x个字符,如果第二个参数是NULL,将不返回任何字符串

  (5)REPEAT(str,x):返回str重复x次的结果

  (6)TRIM(str):去掉目标字符串的开头和结尾的空格

6.数值函数

  (1)ABS(x):返回x的绝对值

  (2)CEIL(x):返回大于x的最小正整数

  (3)FLOOR(x):返回小于x的最大正整数

  (4)MOD(x,y):返回x/y的模

  (5)RAND():返回0-1内的随机数

  (6)ROUND(x,y):返回参数x的四舍五入的有y位小数的值

  (7)TRUNCATE(x,y):返回数字截断为y位小数的结果

三、优化

3.1了解各种sql的执行频率

show [session|global] status命令,默认”session”(当前连接)级别,global(自数据库上次启动至今).

show status like 'Com_%';所有统计参数值

show status like 'Innodb_rows_%';针对Innodb存储引擎

show status like 'Uptime';服务器工作时间

show status like 'Connections';尝试连接mySql服务器的次数

show status like 'Slow_queries';慢查询次数

通过explain 分析低效sql的执行计划, explain extended;

语法:explain + sql语句+ \G

3.2使用索引的典型场景

1.匹配全值,对索引中所有列都指定具体值.

2.匹配值的范围查询,对索引的值能够进行范围查找.

3.匹配最左前缀

4.仅仅对索引进行查询,当查询的列都在索引的字段中时,查询效率更高.

5.匹配列前缀,仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查找.

6.能够实现索引匹配部分精确而其他范围进行范围匹配.

7.如果列名是索引,使用_name is null 就会使用索引(区别于Oracle).

3.3存在索引但不能使用索引的典型场景

1.以%开头的LIKE查询不能使用B-Tree索引

2.数据类型出现隐式转换的时候不会使用索引,特别是列类型是字符串,在where条件中把字符常量值用引号引起来.

3.复合索引情况下,假如查询条件不包含索引列最左边部分,不满足最左边原则,不会使用复合索引.

4.如果MSQL估计使用索引比全表扫描更慢,则不适用索引.筛选性越高越容易使用到索引,越低越不容易使用到索引.

5.用or分割开的条件,前面条件中的列有索引,后面没有,那么涉及到的所有索引都不会使用.

6.前缀索引的缺点:在ORDER BY 和GROUP BY操作时无法使用.

3.4定期分析、检查、优化表

1.分析表

ANALYZE TABLE tbl_name[,tbl_name]...;

2.检查表

CHECK TABLE tbl_name[,tbl_name]...;

3.优化表

OPTIMIZE TABLE tbl_name[,tbl_name]...;

3.4.1优化表的数据类型

SELECT * FROM sc PROCEDURE ANALYSE(16,256);

ALTER TABLE tbl_name MODIFY 列 类型(length);

3.5常用SQL的优化

3.5.1大批量插入数据

1.Innodb类型是的表是按照主键的顺序保存的,将导入的数据按照主键顺序排列,可以有效提高导入效率.

2.导入数据前执行SET UNIQUE_CHECKS=0,关闭唯一性校验,导入结束

SET UNIQUE_CHECKS=1,恢复唯一性校验.

3.SET AUTOCOMMIT=0,关闭自动提交,完成后打开.

3.5.2 优化INSERT语句

1.同时从同一使用者插入:使用insert into values(1,2),(1,2),(1,6)...

2.从不同客户插入很多行,使用INSERT DELAYED

3.将索引文件和数据文件在不同的磁盘上存放

4.从一个文本文件装载一个表时,使用LOAD DATA INFILE.

3.5.3优化ORDER BY语句

1.尽量减少额外的排序,通过索引直接返回有序的数据.where条件和order by使用相同的索引,并且ORDER BY 的顺序和索引顺序相同,并且ORDER BY的字段都是升序或者都是降序.

2.尽量使用SELECT 具体字段名称,而不是SELECT * ,这样可以减少排序区的使用,提高SQL性能.

3.5.4优化嵌套查询

1.子查询效率不如关联查询(JOIN)

3.5.5优化OR条件

1.含有OR的查询子句,若要使用索引,每个条件都得用到索引,没有索引可考虑增加.

3.5.6 使用SQL提示

1.USE INDEX

为Mysql提供参考的索引列表

eg:select count(*) from tbl_name use index(index_name);

2.IGINRE INDEX  忽略一个或多个索引

eg:select count(*) from tbl_name ignore index(index_name);

3.FORCE INDEX 强制使用指定索引

eg:select * from tbl_name force index(index_name) where id>1;

3.6 正则表达式的使用(REGEXP)

 

 

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

1.中间表复制源表部分数据,与源表相隔离,在中间表操作不会对在线应用有其他影响.

2.中间表可以灵活添加索引或临时字段,提高统计查询效率或辅助统计查询的作用.

3.8逆规范化

1.增加冗余列

2.增加派生列

3.重新组表

4.分割表

注意:逆规范化需要维护数据的完整性,常用方法:

1.批处理维护

2.应用逻辑

3.触发器

3.9应用优化

1.使用连接池

2.避免同一数据做重复检索

3.使用查询缓存

4.增加CACHE层

5.利用MySQL复制分流查询 操作

6.采用分布式数据库架构