入坑mysql第一篇
explain Sql 进行对于sql的效率展示
运行后的部分字段:
id : 每个被独立执行的操作标识,标识对象被操作的顺序,id值越大,先被执行,如果相同,执行顺序从上到下
select_type:查询中每个select 字句的类型
partitions:匹配的分区信息(对于非分区表值为NULL)
type:从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
key:索引
key_len:被优化器选定的索引键长度,单位是字节
rows:要执行查询的元组数
extra:执行计划的重要补充信息,当此列出现Using filesort , Using temporary 字样时就要小心了,很可能SQL语句需要优化
常用标识
primary key 主键, auto_increment 自增(适用于整数), unsigend 非负数,commit ‘’ 备注,character set name
指定字符集
常用命令
insert into tableName set value1 = ‘’, value2 =’’;
insert into talbeName(value1,value2) select value1t, value2t from tableName2
// 添加数据以唯一键数据为准当存在的时候就根据唯一进行修改
insert into talbeName (value1, value2) values (value1t, value2t) on duplicate key update value1 = values(value1) , value2 = values(value2);
// 将两表数据连接起来
union
select value1, value2 from tableName1 union (select value1,value2 from tableName2);
MAX,min
触发器
// new:当触发插入和更新事件时可用,指向的是被修改后的记录
old: 当触发删除和更新事件时可用,指向的是被修改前的记录
// delimiter 是改变结束符号 现在改为|
delimiter |
CREATE
TRIGGER trigger_name
trigger_time trigger_event ON tbl_name FOR EACH ROW
[trigger_order]
Begin
trigger_body
end |
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
// 用于多个触发器,follows,precedes 表示的是当前触发器是后面的触发器的后面还是前面
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
delimiter
具有一致性,如果错误就会回滚
OLD是只读的,而NEW则可以在触发器中使用 SET 赋值 可以在触发器执行主体中根据条件new进行重新
赋值
// 并不推荐使用触发器;原因:1. 触发器对于数据插入修改性能有影响,比如添加500数据可能1s,但要触发500次触发器,就会有500*x s 的时间,所以对于高并发的系统,触发器和存储过程都不要使用;2. 触发器难以维护,因为没有参数和调用,所以查看代码并不能知道,对于非DBA开发人员容易忽略。而且是隐式调用,当进行迁移或者框架修改, 触发器如果复杂就必然有级联,级联会导致出现问题或者进行迁移时候对于业务实现十分难以理解;3.触发器对于性能并没有提升,只是在业务实现上不需要在软件有那么多代码
存储过程
dml,ddl,if-then-else,while-do
create
procedure sp_name([proc_parameter[,…]])
[characteristic …] begin routine_body end
proc_paramter:例如: in @in_to int
[ in | out | inout ] param_name type
characteristic:
commit ‘string’
| language sql
| [not] deterministic
| {contains sql | no sql | reads sql data | modifies sql data }
| sql security {definer | invoker}
in : 只做输入参数,修改后并不会影响到外面的原参数
out:只做输出参数,当在存储过程中没有赋值,在其中调用为null不会用外面的参数,修改后会影响外界参数
inout:输入输出参数,未赋值钱可以用外界的值,修改后也会影响外界的参数。
// 调用参数
call sp_name[(传参)]
常用命令
show procedure status WHERE db = ‘cyb_os’;
show create procedure cyb_os.cz_create_code;
declare x1 varchar(5) default ‘values’;
if 条件 THEN
执行语句
ELSE
执行语句
END if;
case 变量
when value1 THEN
执行语句
when value2 then
执行语句
else
执行语句
end case;
while 条件 do
执行语句
end while;
repeat
执行语句
until 结束条件
end repeat;
loop_lable:loop
执行语句
if 条件 then
leave loop_lable;
end if;
end loop;
JDBC
索引
1.用于查询效用大于修改效用的表,因为索引加快查询,但也会消耗空间和加大修改时间,因为需要改索引
2.索引列最好要小,而且类型int比String类型要好,节约空间和减少优化复杂度
3.索引最好不要有null值虽然唯一索引允许有,但null对于优化查询时间有影响
4.数据量要较大,而且需要是where后面的条件列才可以加,因为只有是where才能起到加速筛选的作用
5.值种类很少的不要加,这样并不能优化多少数度,如性别列
6.text,image,bit这种要么数据量大,要么值小的列也不用加
// 一般使用索引
7.经常出现在where,用于联表条件的列
8.经常用于排序,范围内搜索的列
索引创建要谨慎,因为insert或update要重构索引,比较影响写入性能,对于要求写入数据量大的表并不是很适合
普通索引 // 加速查询
create index index_name on table_name(column_name)
alter table_name add index index_name(column_name)
主键索引 :添加主键就是主键索引 而且一个表只有一个主键
唯一索引 // 加速查询 可以为null 但是值要唯一
create unique index index_name ion table_name(column_name)
alter table_name add unique index index_name(column_name)
全文索引
仅可以适用于MyISAM引擎的数据表;作用于CHAR、VARCHAR、TEXT数据类型的列。
添加多列索引 多个列组合索引
// 删除索引
drop index_name on table_name;
// 显示
show index from table_name;
建立索引的时候
MySQL只对<,<=,=,>,>=,BETWEEN,IN使用索引
某些时候的LIKE也会使用索引。
在LIKE以通配符%和_开头作查询时,MySQL不会使用索引。
不要在列上进行运算 索引会失效