mysql学习笔记

1、char和varchar
char是定长的，如果声明了一个char长度为（200）的字段，row数据为“hello”，则余下的位数由空格补齐

2.MySQL中可以使用timestamp和datetime来保存日期。
datetime表现格式为：YYYYMMDDHHMMSS
timestamp表现格式为2011-10-05 11:13:10
mysql提供了from_unixtime()函数把unix时间戳转为日期
unix_timestamp()则是把日期转为时间戳

3、IP地址在mysql的存储
PHP
echo ip2long('192.168.1.38');
输出：3232235814

MYSQL
SELECT INET_ATON('192.168.1.38');
输出：3232235814

两个函数返回的结果是一样的，都是A*256*256*256+B*256*256+C*256+D的算法
192*256*256*256+168*256*256+1*256+38 = 3 232 235 814

反过来，从int转换为IP地址分别是php的long2ip()和mysql的INET_ATON()。

mysql存储这个值是字段需要用int UNSIGNED。不用UNSIGNED的话，128以上的IP段就存储不了了。

传统的方法，创建varchar(15)，需要占用15个字节，而改时使用int只需要4字节，可以省一些字节。

php存入时：$ip = ip2long($ip);
mysql取出时：SELECT INET_ATON(ip) FROM table ...
php取出时，多一步：$ip = long2ip($ip);

转换以前的数据：

1.把以前的varchar()数据转换为int型的SQL语句：
UPDATE `hx_table` SET ip = INET_ATON(ip) WHERE INET_ATON(ip) is NOT NULL
2.把字段更改为int型：
ALTER TABLE `hx_table` CHANGE `ip` `ip` INT UNSIGNED NOT NULL
3.程序做相应修改上传，完成。

@@UPDATE@@20110310：

在32位的机子上，echo ip2long('192.168.1.38');由于超过32位的最大数，导致输出负数-1062731482。

有两种方法更新为正数：
$ip_long = bindec(decbin(ip2long($ip)));
或
$ip_long = = sprintf("%u", ip2long($ip));
因此一种是修改PHP程序，使其肯定存入正数。
另一种是将mysql的这个字段使用int，非UNSIGNED，使其可以存入负数。

discuz使用了char（15）来保存ip地址，很多人使用varchar（15）来表示ip地址

4、check table tablename来检查表错误
使用repair table tablename来修复表错误

5、使用primary key列来处理大偏移量
select collist from table order by id desc limit 500000,10这将耗费非常多的时间
select collist from table join (select id from table order by id desc limit 500000,10) using(id)这将能提升性能

6、更新索引统计
analyze table

7.create table2 tablecopy like table1;
建立表table2，使用table1的结构

8.在数据量很大的时候更改一个字段的默认值(假定表引擎为myisam)：
alter table modify column age int(3) not null default 5;速度则较慢导致表被重建
alter table alter column age set default 5;则只修改.frm文件

9.高效加载myisam表的技巧在于：禁用键、载入数据、启用键
alter table tablename disable keys;
....load data
alter table tablename enable keys;

10.使用profile来查看执行的sql语句的性能：
profile默认是关闭
set profiling=1
... do query
show profiles;
show profile cpu for query 1;查询query_id为1的查询占用cpu

11.join和union
可以将join理解为列的合并
将union理解为行的合并

12.inner join 和 outer join
默认为outer join
inner join称为内联，无left和right修饰，如果有on或using，受限于该条件取得数据，他们不会取得某列的值为空
outer join，有left和right修饰，left即取得左表全部数据，right，即取得右表全部数据。

13.union
union会排出重复数据，如果你确认没有重复使用union all将速度更快

14.对同一个表同时进行update和select
实际上无法为同一个表进行update的同时进行select
update time set ip=(select count(*) from time) where id=1;
使用表和子查询结果进行联接：
update time inner join(select id,count(*) as cnt from time) as time2 using(id) set time.ip=time2.cnt;

15.关于count
对于统计，应该使用count(*)来统计行数，如果使用count(col),col不为空，则count(col)被转换为count(*),count(*)则不会被展开为每列
对于where条件的查询：
select count(*) form subject1 where id>500;//性能差
select (select count(*) from subject1)-count(*) from subject1 where id<=500;//性能好
因为子查询被优化成了一个常量

16.mysql中if的使用
if(col=const,1,0) //如果col等于常量const，就取0值返回，否则返回1
示例:select sum(if(cid=1,0,1)) as urlclass from subject1;
上面sql语句等价于:select count(cid=1 or null) as urlcount from subject1;

17.优化联接:
在on或using的列上使用索引，在添加索引时要考虑顺序，比如联接表A和B的时候使用了列c，并且优化器按照从B到A的顺序联接，就不需要在B上添加索引
确保group by或order by只使用一个表中的列

18.子查询尽可能少用联接（join）

19.性能较好的分页语句
select id,title,content from table inner join(select id from table order by id desc limit 2000,10) as lim using(id);
或
select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

20.使用select col1,col2...而不是select *

21.自定义变量
赋值:set @one:=1;
使用select... where
注意：
a、自定义变量会禁止缓存查询
b、不能在使用文字常量或标示的地方使用它们如表名、列名和limit
c、在5.0版本以前，对大小写敏感
d、不能显式的生命变量类型，所以赋初值是做好的办法
e、:=运算符比其他运算符优先级低
f、未定义的变量不会造成语法错误
列出行号：
set @rownum:=0;
select id,@rownum:=@rownum+1 as rownum from subject1 limit 10;
结果如下：
+----+--------+
| id | rownum |
+----+--------+
| 18 |     11 |
| 20 |     12 |
| 21 |     13 |
| 24 |     14 |
| 26 |     15 |
| 32 |     16 |
| 33 |     17 |
| 37 |     18 |
| 39 |     19 |
| 48 |     20 |
+----+--------+
10 rows in set

22、查询缓存
查询缓存命中率的计算公式：Qcache_hits/(Qcache_hits+Com_select)，这个值大于30%以上可以认为是适合查询缓存。
使用：show status like '%com_select%';可以获得Com_select,这个值是数据库重启或被flush status以来的一个总数
Qcache_hits,是查询被命中的次数

show status like '%Qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 碎片
| Qcache_free_memory      | 33545576 | 空闲的内存
| Qcache_hits             | 0        | 命中的次数
| Qcache_inserts          | 0        | 插入了几个缓存
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        | 没有被缓存的查询次数
| Qcache_queries_in_cache | 0        | 在缓存中的查询
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

通用缓存优化方案：
使用较小的表
成批的写入操作
query_cache_size（缓存占用的内存，如果该值为0，表示禁用） 256M已经足够大了
对于写入任务多于查询任务的应用程序，应该考虑关闭查询缓存

23.视图
创建视图：
CREATE VIEW viewname AS select * from table where .... with check option
删除视图：
drop view viewname
可以考虑使用一个临时视图来解决问题，创建视图，程序执行完毕后删除视图

24.字符集
在php+mysql中使用mysql_set_charset('gbk',$link)来设置字符集比SET NAMES 更加安全

25、分区表
不要同时加上主键和对表进行分区，这将有可能导致性能下降
分区表语法：
alter table subjectpart partition by range(id) (
partition p_20000 values less than (20000),
...
);
在linux系统上可以将数据分布于不同的目录中:
alter table subjectpart3 partition by key(id)(
partition p0 data directory='/tmp/subjectData1/',
partition p1 data directory='/tmp/subjectData2/');

posted @ 2011-09-10 18:36 rorshach 阅读(139) 评论(0) 编辑收藏举报

刷新页面返回顶部

mysql学习笔记

公告