mysql使用經(jīng)驗(2)

勁松

--查數(shù)據(jù)文件的大小
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';

--觸發(fā)器默認(rèn)變量的限制
通過關(guān)鍵字OLD.column_name獲得的值不能通過SET命令修改，
但是關(guān)鍵字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的編譯技巧模塊靜態(tài)加載
./configure --prefix=/usr/local/mysql-5.1
--with-extra-charsets=complex
--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static

--mysql數(shù)據(jù)文件存放于內(nèi)存文件系統(tǒng)
mount -t ramfs -o maxsize=10240m ramfs /data/mysqldatafile/
定時備份binlog復(fù)制到磁盤，并flush

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

--任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計算表達式等等，查詢時要盡可能將操作移至等號右邊。

--or操作符條件應(yīng)盡量向 union / between ... and 轉(zhuǎn)化

--查看分區(qū)表的設(shè)置
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:
通過將數(shù)據(jù)緩存在SSD硬盤加速MySQL的內(nèi)核模塊

--以磁盤空間換取插入效率
concurrent_insert　= 1 改成 2 并定期OPTIMIZE TABLE

--mysql 刪除全部數(shù)據(jù)并將自增列歸零
1.delete from tablename;
  alter table tablename auto_increment=1;
 
2. truncate table tablename;