MySQL 5.7+ 实用技巧、最佳实践、索引等_综合

前言：本讲将讲解MySQL的实用技巧.
本讲内容***不适合***对sql语法以及关系型数据库毫无基础的初学者。

参考内容
https://github.com/michaljuhas/sample-database
https://github.com/michaljuhas/SQL-training-advanced

使用平台:wampserver x64+mysql 5.7.x
0.导入数据
命令行导入数据，大家看着选，我用的是source+sql文件路径的方式导入

1.代码书写风格

注释
缩进, tab
空行
关键词打头阵
1 = 1 (这里相当于一个关节点，便于debug使用的)
别名的可读性至关重要
使用 ``

SELECT`xxxx`,`xxx`,`xxx`FROM`yyy`,`yyy`,`yyy`WHERE 1=1AND zzz

永远禁止SELECT *
列名加后缀表意：_id, _date,_idx, _flag,_dt, _amt,
_cate等表意内容，这之前公司内部需要形成标准然后再操作.编码是个学问：比如HR表示人力资源部门，FIN表示财务部门等等，也可以以自然数表示
batch delete:加上limit比不加上快很多.而且，limit对于优化性能很有效

delete from `xxx` where `yy` is not null limit 10000;

2.索引
排序之后搜索更快，自不必说，先上个小例子：

show index from `sample_staff`.`salary`EXPLAIN SELECT *
FROM `sample_staff`.`salary`
WHERE 1 = 1AND `salary`.`salary_amount` > 100000AND `salary`.`from_date` >= '1998-01-01'
LIMIT 1;

explain会看到已经调用了index，Using index condition; Using where

index类型
primary:有了主键mysql自动创建
unique：唯一值，如果这些列有重复值，则会报错
single
multiple(最多16列的多列index,比如，一个unique index包含多个列，这种情况下，多个列依次进行排序。如此顺序形成了一种层次。如果查询语句使用index定义的全部列，则这些列在查询语句中的书写顺序不重要（该有的都需要有，但是顺序可以随意）；然而，如果查询语句使用到了index中的部分列,比如，index 基于 col1， col2， col3。。。多个列，查询语句中where跳过第一个列，index会失效。其逻辑很简单：col2是基于col1的排序后才进行的排序，跳过col1的排序结果，无法对col2进行有意义的排序了。但是这之后的结尾部分可以不全，比如where只有col1， col2，没有col3. 总结一句话：顾头不顾腚)，这些顺序就重要了。
concatenated
partial:一个col的某些字节，比如姓名中的前4个字节做索引:

alter table xxxx add index(name(4))

这种写法的优势在于压缩了index的大小，缺点在于结果不精确，比如:

select * from xxx where name='smith';

这里由于name的前4个作为index，所以会返回smitty, smita, smithy等前4个事smit的名字。另外的好处是，partial index建立速度快,查询速度却不尽如意人，测试证明，全数据的索引比partial的快很多。

use index (select * from table use index(index_name)) 这个用处在于table存在多个可选的index时，可以指定其中一个index。
force index:实践中与use index 差别不大
ignore index：强制忽略某个index

针对unique index，就不允许重复值存在，于是就有了如下修改重复值的语句：

insert into xxxx
ON DUPLICATE KEY UPDATEyyy, yyy, ...

函数导致index失效的问题, 例如：

where 'abc' = lower(`name`);

对于多个index独立并存时，可以使用union all来对结果进行连接

...
where name='YY' and id between 100 and 200;

假设这里有2个独立的index，分别基于name和id，我们就修改成

...
where name='YY' 
union all
...
where id between 100 and 200;

Patition
对table进行分割,便于提高查询速度,便于后续删除不需要的老数据

create table xxx(`id` int, `to_date` date)partition by range(year(`to_date`)subpartition by hash(to_days(`to_date`))subpartitions 2 (partition p0 values less than (1900),partition p1 values less than (2000),partition p2 values less than maxvalue)
)

现实中，决定partition设计中使用到的数值分布很难保证均匀分布，比如，上例中的year，可能1900之前的没有值，而2000的占99%，那么这种partition就没有了意义。除了range这种‘均匀’（期待是均匀的）之外，可以用list根据具体数值进行parition。

partition之后，相应得，

from `table` parition(`parition_name`)

批处理文件相关
变量
多用于函数与procedure中，这部分的语法特征已经与其他高级语言比较接近。
@@全局变量，@session变量， declare 局部变量 int

create procedure `table`.`pre_test`(in_var int
)
begindeclear p_var int;set p_var = 1;select in_var + p_var;
endcall pre_test(4);

window 函数
5.7中没有引入sqlserver , oracle中的一些window函数,目前支持的有row_number(), dense_rank(),rank()函数等。

自定义函数
自定义函数也算必将常用的mysql的功能，只有几个提醒：
delimiter 建议永远加上，哪怕是多余的。

delimeter //
create function `fc_test`()
..delimiter;

建议名字大写而且以FC_为前缀。

stored procedure
效率高：因为编译，所以快速，写法很像自定义函数

create procedure `test`()
begin...
endcall test();

Event事件
可以给mysql定义event，来定时进行执行一些任务，比如：

create event `myevent`
on schedule every 1 second
starts now()
end now()+interval 1 minute
comment 'test event'
do begin....end

show variables like ‘event_schedule’;查看是否启动了scheduler。如果是0
set global event_scheduler = 1;

show events 查看当前schema内的内容。

补充一个例子：
用到的数据：

create table theMessages
(   id INT AUTO_INCREMENT PRIMARY KEY,userId INT NOT NULL,message VARCHAR(255) NOT NULL,updateDt DATETIME NOT NULL,KEY(updateDt)
);INSERT theMessages(userId,message,updateDt) VALUES (1,'message 123','2015-08-24 11:10:09');
INSERT theMessages(userId,message,updateDt) VALUES (7,'message 124','2015-08-29');
INSERT theMessages(userId,message,updateDt) VALUES (1,'message 125','2015-09-03 12:00:00');
INSERT theMessages(userId,message,updateDt) VALUES (1,'message 126','2015-09-03 14:00:00');

第一个event，每天跑一次：

DROP EVENT IF EXISTS `delete7DayOldMessages`;
DELIMITER $$
CREATE EVENT `delete7DayOldMessages`ON SCHEDULE EVERY 1 DAY STARTS '2015-09-01 00:00:00'ON COMPLETION PRESERVE
DO BEGINDELETE FROM theMessages WHERE datediff(now(),updateDt)>6; -- not terribly exact, yesterday but <24hrs is still 1 day-- Other code hereEND$$
DELIMITER ;

第二个例子，10分钟一次

DROP EVENT IF EXISTS `Every_10_Minutes_Cleanup`;
DELIMITER $$
CREATE EVENT `Every_10_Minutes_Cleanup`ON SCHEDULE EVERY 10 MINUTE STARTS '2015-09-01 00:00:00'ON COMPLETION PRESERVE
DO BEGINDELETE FROM theMessages WHERE TIMESTAMPDIFF(HOUR, updateDt, now())>168; -- messages over 1 week old (168 hours)-- Other code here
END$$
DELIMITER ;

查看一下自定义的event

SHOW EVENTS FROM my_db_name; -- List all events by schema name (db name)
SHOW EVENTS; 
SHOW EVENTS\G; -- <--------- I like this one from mysql> prompt

游标
上学时的教科书上提及游标性能不行，不利于批处理，不建议使用。这里就把游标的部分放到procedure中，然后编译后获得性能提升

declare cursor_dates cursor forselect xxxfrom yyylimit 100;open cursor_dates;
loop_usrs:loopfetch cursor_dates into v_date;
end loop loop_users;
close cursor_dates;