mysqlÏà¹Ø
ÏÔʾ¡¢ÉèÖÃ×Ô¶¯Ìá½» show variable like 'autocommit' set autocommit=1 or 0
ÉèÖÃÈÕÖ¾£º
ÔÚ/etc/mysql/my.cnfÖÐÉèÖÃÈçÏ¿ÉÔÙslow.logÖÐÊä³öÔËÐÐʱ¼ä³¬¹ýÒ»ÃëµÄsqlÓï¾ä
[mysqld]
log-slow-queries = /tmp/slow.log
long_query_time = 1
ÉèÖÃÈÕÖ¾Êä³öÔËÐг¬¹ý1ÃëµÄsqlÓï¾ä set global long_query_time = 1;
ÉèÖÃÈÕÖ¾Êä³ö·½Ê½ÎªÎļþ set global log_output=file;
ÉèÖÃgeneralÎļþ´æ·Å·¾¶ set global general_log_file='/tmp/general.log';
ÉèÖÃslowÎļþ´æ·Å·¾¶ set global slow_query_log_file='/tmp/slow.log';
ÆôÓÃgeneralºÍslow set global general_log=on;
set global slow_query_log=on;
°ÑûÓÐʹÓÃË÷ÒýµÄ²éѯ¼Ç¼µ½ÂýËÙ²éѯÈÕÖ¾ÖÐ SET GLOBAL log_queries_not_using_indexes = 1
¼ì²éÊÇ·ñÆôÓÃÁËÈÕÖ¾ show variables like 'log_bin';
ÏÔʾµ±Ç°ÈÕÖ¾ÎļþÃû show master status;
²éѯ long_query_time µÄÖµ show variables like 'long%';
·ÖÎöslow.logÎļþ¹¤¾ß
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
ÉÏÊöÃüÁî¿ÉÒÔ¿´³ö·ÃÎÊ´ÎÊý×î¶àµÄ20¸ösqlÓï¾äºÍ·µ»Ø¼Ç¼¼¯×î¶àµÄ20¸ösql¡£
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
Õâ¸öÊÇ°´ÕÕʱ¼ä·µ»ØÇ°10ÌõÀïÃ溬ÓÐ×óÁ¬½ÓµÄsqlÓï¾ä¡£
show status like 'last_query_cost'; ÉÏÒ»´Î²éѯÔì³É¶àÉÙ´ÎËæ¼´¶ÁÈ¡
ÖÜÆÚÐÔÔËÐÐshow status²¢ºÍÇ°Ò»´Î×÷±È½Ï mysqladmin extended -r -i 10
Ö÷´Ó·þÎñÆ÷ͬ²½
show slave status\G;
start slave;
stop slave;
¼ì²é·þÎñÆ÷¶Ë¿ÚʹÓÃÇé¿ö -i: ¶Ë¿ÚºÅ£¬-p ½ø³ÌºÅ
lsof -i:8009 | wc -l
mysql»º´æ
have_query_cache ÊÇ·ñÖ§³Ö²éѯ»º´æÇø “YES”±íÊÇÖ§³Ö²éѯ»º´æÇø
query_cache_limit ¿É»º´æµÄSelect²éѯ½á¹ûµÄ×î´óÖµ 1048576 byte /1024 = 1024kB ¼´×î´ó¿É»º´æµÄselect²éѯ½á¹û±ØÐëСÓÚ1024KB
query_cache_min_res_unit ÿ´Î¸øquery cache½á¹û·ÖÅäÄÚ´æµÄ´óС ĬÈÏÊÇ 4096 byte Ò²¼´ 4kB query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
Qcache_free_memory ±íʾ²éѯ»º´æÇøÏÖÔÚ»¹ÓжàÉٵĿÉÓÃÄÚ´æ
Qcache_hits ±íʾ²éѯ»º´æÇøµÄÃüÖиöÊý£¬Ò²¾ÍÊÇÖ±½Ó´Ó²éѯ»º´æÇø×÷³öÏìÓ¦´¦ÀíµÄ²éѯ¸öÊý
Qcache_inserts ±íʾ²éѯ»º´æÇø´
Ïà¹ØÎĵµ£º
·þÎñÆ÷¶Ë´úÂë,amfphpµÄservicesĿ¼Ï½¨Á¢readData.phpÎļþ,´úÂëÈçÏÂ
<?php
class ReadDB
{
function getData()
&nb ......
Ê×ÏÈ£ºÈ·¶¨Êý¾Ý¿âµÄ±àÂëΪutf8£¬Õý³£´¢´æÖÐÎÄ£¨ÓÃMySQL-Front²é¿´ÖÐÎÄÊÇ·ñÕý³££©
È»ºó£ºServices²éѯMySQLʱʹÓÃ"SET NAMES 'utf8'"£¬ÇÒͨ¹ý"amfphp/browser"ÔËÐп´¿´ÊÇ·ñÖÐÎÄ£¬Èç¹ûÂÒÂëÔòÐÞ¸Ägateway.phpÎļþ£¬È¥µô $gateway->setCharsetHandler("utf8_decode", "ISO-8859-1", "ISO-8859-1");
×îºó£ºÔËÐÐFlex³ÌÐòÏÔ ......
MySQL ×Ö·û´®½ØÈ¡º¯Êý£ºleft(), right(), substring(), substring_index()¡£»¹ÓÐ mid(), substr()¡£ÆäÖУ¬mid(), substr() µÈ¼ÛÓÚ substring() º¯Êý£¬substring() µÄ¹¦Äܷdz£Ç¿´óºÍÁé»î¡£
1. ×Ö·û´®½ØÈ¡£ºleft(str, length)
mysql> select left('sqlstudy.com', 3);+-------------------------+| left('sqlstudy.com', 3) | ......
mysqlÊýÖµ·¶Î§
tinyint -128~127 0~255
smallint -32768~32767 0~65535
mediumint -8388608~8388607 0~16777215
int -2147483648~2147483647 0~4294967295
bigint -9223372036854775808~9223372036854775807 0~18446744073709551615
javaÖÐ
byte ......
ÏÈ°´ÕÕÏÂÃæµÄ±í½á¹¹´´½¨mysql_order_by_testÊý¾Ý±í£¬ÎÒÃÇÓÃʵÀýÒ»µãÒ»µã¸æËßÄ㣬MySQL order byµÄÓ÷¨¡£
¡¡¡¡ORDER BY uid ASC
¡¡¡¡°´ÕÕuidÕýÐò²éѯÊý¾Ý£¬Ò²¾ÍÊÇ°´ÕÕuid´ÓСµ½´óÅÅÁÐ
¡¡¡¡ORDER BY uid DESC
¡¡¡¡°´ÕÕuidÄæÐò²éѯÊý¾Ý£¬Ò²¾ÍÊÇ°´ÕÕuid´Ó´óµ½Ð¡ÅÅÁÐ
¡¡¡¡ÎÒÃÇÀ´¿´
SELECT * from mysql_order_by_test OR ......