MySQL点滴

MySQL点滴

一.mysql的复合索引,最多可以包括16列,超过该列,将提示索引超出限制

二.mysql的索引,无论是单个索引,还是复合索引,都规定长度为1000个字节.utf8编码为3个字节,即长度(或总长度)不能超过333个字符.


三.mysql中的hash函数
mysql可选的hash函数有crc32(),SHA1,MD5()等等。对于数据量在几十万的数据推荐crc32()
可以对某个较长的字段建立hash冗余索引,比如字段较长的url,比较适合这种情形.

SELECT * FROM table WHERE url_hash = CRC32('http://www.domain.com') AND url='http://www.domain.com';

php中也有crc32函数可用,不过该函数获取到的是带有符号的整数,需要去除符号.

四.InnoDB索引采用聚集索引,第二索引保存了行的主键值.这有可能使得第二索引比较大,对通常的MyISAM引擎中,ID,索引列查询是无法使用覆盖索引,但InnoDB可以做到使用覆盖索引.

五.ORDER BY无须定义索引的最左前缀的一种情况是前导列,如果where和join子句为这些列定义了常量,就能弥补该缺陷.

在(name,age,bir)上有索引
select * from where name='jo' order by age asc,bir asc;
即可使用索引

六.为消除数据碎片,可使用OPTIMIZE TABLE;

七.加速ALTER TABLE
ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT 5;
这个命令修改了.frm文件并且没有改动表,速度非常快

八.高效加载MyISAM索引的诀窍是禁用键,加载数据,启用键
mysql>alter table tablename disable keys;
------------load data
mysql>alter table tablename enable keys;

九.如果用explain解释sql语句,extra中列是using index表明使用了覆盖索引,using where表明在服务端而不是引擎过滤了行.前者效率优于后者

十.max_packet_size这个配置参数决定了发送到客户端的数据有多大

十一.mysql执行顺序(DBMS使用者角度)
1、from子句组装来自不同数据源的数据;   
2、where子句基于指定的条件对记录行进行筛选;   
3、group by子句将数据划分为多个分组;   
4、使用聚集函数进行计算;   
5、使用having子句筛选分组;   
6、计算所有的表达式;   
7、使用order by对结果集进行排序。

十二.IN和EXISTS的区别
IN是把外表和内表做hash连接,EXISTS是对外表做loop循环,每次循环再对内表循环
如果两表大小相当,IN和EXISTS区别不大
假设两个表中一个比较小,一个是大表,则子查询表大的用EXISTS,子查询表小的用IN

无论如何应该确认大表上的数据能够用到索引

not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not exists 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。

十三.IGNORE INDEX 命令可以禁止一些索引

十四.优化连接
A,确保ON或USING上有索引
B,确保GROUP BY或ORDER BY只引用一个表中的列

十五.优化LIMIT和OFFSET
一个提高效率的简单技巧是在覆盖索引上进行偏移,即
SELECT * FROM table INNER JOIN (SELECT id FROM table ORDER BY id DESC LIMIT 0,10) AS lim USING(id)

十六.优化UNION
重要的是始终使用UNION ALL,除非需要消除重复行

十七.最常见的排序规则是字母大小写或二进制编码,常用结尾是_cs,_cl,bin

十八,打开skip_name_resolve可以提高mysql性能,这意味着只能解析ip,localhost或IP地址通配符

十九.查看操作系统可以使用两个工具,iostat和vmstat
a.vmstat 每5s生成一个报告
b.iostat -dx 5 每5s生成一个报告,%util表明磁盘使用比


二十.安全的清除二进制日志
可以设置expire_logs_days告诉mysql要过多久才清除日志
再附加cron任务
0 0 * * * /usr/bin/mysql -e "PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N(多少天) DAY"

expire_logs_days的设置在服务器重启后,或者mysql轮转日志之后才生效.所以如果二进制日志还没轮转,也没填满,就不会被清除.

二十一.在脚本中的mysql的导出数据和恢复
mysqldump备份或SELECT INTO OUTFILE
mysqlimport


二十二,用show open tables可以看到打开的表


二十三,用myadmin debug可以看到是那个线程锁定了表

posted @ 2014-02-05 17:17  rorshach  阅读(244)  评论(0编辑  收藏  举报