mysql使用经验(2)

劲松

--查数据文件的大小
select table_name,engine,ROUND(data_length/1024/1024,2) total_size_mb,table_rows
from information_schema.tables
where table_schema='dbname' and table_name like 'tablename';

--mysql查索引名称
select CONSTRAINT_NAME,COLUMN_NAME 
from information_schema.KEY_COLUMN_USAGE
where TABLE_SCHEMA='dataname' and TABLE_NAME='tablename';

--触发器默认变量的限制
通过关键字OLD.column_name获得的值不能通过SET命令修改，
但是关键字NEW获得的值能通过SET NEW.column_name=VALUE方式修改；

--修改用户密码
UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='username';

--主从相同的server-id時 ,mysql error log
110710 15:50:23 [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

--mysql 5.1的编译技巧模块静态加载
./configure --prefix=/usr/local/mysql-5.1
--with-extra-charsets=complex
--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static

--mysql数据文件存放于内存文件系统
mount -t ramfs -o maxsize=10240m ramfs /data/mysqldatafile/
定时备份binlog复制到磁盘，并flush

--强制查询使用索引：
select id from t with(index(索引名)) where num=@num

--任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

--or操作符条件应尽量向 union / between ... and 转化

--查看分区表的设置
SELECT partition_name part,partition_expression expr,partition_description descr,table_rows
FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='cla_ldp_click_partition';

--Mysql 5.1的模块Flashcache:
通过将数据缓存在SSD硬盘加速MySQL的内核模块

--以磁盘空间换取插入效率
concurrent_insert　= 1 改成 2 并定期OPTIMIZE TABLE

--mysql 删除全部数据并将自增列归零
1.delete from tablename;
  alter table tablename auto_increment=1;
 
2. truncate table tablename;