--查数据文件的大小
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;
阅读(874) | 评论(0) | 转发(0) |