当前位置: 代码迷 >> 综合 >> MySQL 5.7+ 实用技巧、最佳实践、索引等
  详细解决方案

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

热度:85   发布时间:2024-02-11 22:03:20.0

前言:本讲将讲解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;
  相关解决方案