15.SQL中的安全问题
注入漏洞用preparedStatement和绑定变量值来解决
注意过滤危险字符
16.查看默认SQLmode命令 select @@sql_mode;
查看警告信息 show warnings;
设置SQL_mode set session|global sql_mode='xxxx';
ANSI 模式下插入非法日期 traditional 模式下不可以插入非法日期
17.常用技巧
多排序 select guojia,sum(lirun)from tongji group by
guojia,canpin;
所有组合信息 select guojia,canpin,lirun from tongji group by
guojia,canpin with rollup;
随机取几条 select * from tongji order by rand() limit 5;
正则表达式 select 'gongqingkui' regexp '^gong';
=>1
select 'gongqingkui' regexp 'i$';
select * from tongji where guojia regexp '^[cu]';
我常常想怎么用最少的位来表达信息,这里就有一个例子,使用位来表示购物的。它的优点就在于可以方便的求与和或的关系。
bit_or用于在分组统计中求有过什么,bit_and用于求且
select guojia,bit_or(lirun) from tongji group by guojia;
select guojia,bit_and(lirun) from tongji group by
guojia;
18.SQL优化
显示统计状态 show [global|session] status like
'Com_%';//session为当前用户 global为自开机以来的。
通过explain分析sql执行情况explain select * from leader where code
=10003\G
优化之:create index aaa on leader(code);
注意索引的前缀特性
索引使用情况 show status like 'Handler_read%';
handler_read_first 使用次数 handler_read_rnd_next
表示读取次数,越大越不好。
优化 check table leader;analyze table leader;optimize table
leader;
载入数据 load data infile 'd:/a.txt'into table u2.leader;
优化载入数据,关闭唯一索引,顺序主键导入,关闭唯一检验,关闭自动提交,批量提交插入数据。
用join代替嵌套查询。
explain select name_first from leader use index (aaa) where
code = 10003 希望用某个
索引
explain select name_first from leader force index (aaa) where
code = 10003 强制索引
19.优化数据库对象
select * from leader procedure analyse(16,256)\G
对当前表进行分析,字段冗余情况
通过拆分提高效率:垂直拆分:主码和一些列放到一个表,主码和另一些列放到另一个表
水平拆分:数据行分多表
逆规范化:增加冗余列,增加派生列(避免计算),重新组表(多表组合),分割表。
中间表提高查询速度:insert into e select id,name_first from leader where
code = 10001;
20.锁问题
show status like 'table_%';表级锁争用 table_locks_waited
值越大说明表级争用越大
加锁:lock table a read;lock table b write;解锁:Unlock
tables;
表的别名也必须加锁。加锁后的session不能读其他表
21.优化mysqlServer
显示数据库静态信息show variables;
显示数据库动态信息show status;
cmd下mysqladmin -uroot variables -p
帮助mysqld --verbose --help|more
22.磁盘IO问题
raid 0 1 10 4 5
使用Symbolic links分布IO
使用裸设备存放InnoDB的共享表空间
29.MySQL复制
关于切换主从服务
在所有从服务器s1,s2上停止IO进程。stop slave io_thread;再检查show
processlist;输出,确保has read all relay log;
在从库s1上停止服务stop slave;
在此从库s1重启为主库reset master;
在s2从库停止从库服务stop slave;启动为s1的从库change master to
master_host='s1';
开启s1作为S1的从机服务start slave;
通知客户端业务指向s1
删除主服务器master.info 和 relay-log.inf文件。
30.MySQL Cluster服务
准备寒假期间或者考试之后整个Cluster服务
*****************复制和导入备份
Mysqldump备份sims数据库:
cmd下: mysqldump –u root
–p sims > sims.sql
恢复:mysql –uroot –p sims
导出文本数据:select * from course into outfile
'c:/course.txt';
导入数据:load data infile
'c:/course.txt' into table course;