mysql base funny sql

767 3072 65535
256*3-1 1024*3 2^16-1

tcprstat

top10 sql:

mysqldumpslow -s at -t 10 slow3306.log > slow3306_BillWu_20140402_A.txt

mysqlsla -log-type slow -top 10 slow3306.log >  slow3306_BillWu_20140402_B.txt

#-sf "+select" 可加

show full processlist\G;

system ls

Analyze Table

Optimize Table

Check Table

Repair Table(mysiam)

如果你的MySQL是有备库的，如果你只希望在主库上执行的话，那么可以加上关键字NO_WRITE_TO_BINLOG（或者LOCAL，意思完全相同）。

对于InnoDB，会将optimize命令映射为ALTER TABLE命令，该命令会重建数据表，更新索引统计信息、回收主键索引中空间。

GRANT ALL PRIVILEGES ON *.* TO monty@123.45.67.89 IDENTIFIED BY 'something' WITH GRANT OPTION; monty 是用户名123.45.67.89是ipsomething是密码WITH GRANT OPTION 表示可以传递授权

grant select,update,insert,delete,drop,create on mysql.* to shubtest@'localhost' identified by 'shubtest';

#线上修改logbin模式

set global tx_isolation='READ-COMMITTED';
set global binlog_format=STATEMENT;

select current_user();  user();

create user 'chen'@'127.0.0.1' identified by '123456';

show  grants;

rename  user 'chen'@'127.0.0.1'  to 'chenzhongyang'@'127.0.0.1';

#匿名用户

insert into user(host,user,password) values('%','','');

flush privileges; 

select * into b from a where 1<>1;

insert into b (d, e, f) select a, b, c from a;

create table b (select a, b, c from a);

create temporary table a (...);

归档数据：

creaet table ttt(select ...)

导出

导出數據库為dbname某张表(test)结构及表數據（不加-d）
    mysqldump -uroot -pdbpasswd dbname test>db.sql;

/apps/svr/mysql5/bin/mysqldump --socket=/tmp/mysql3306.sock   --net_buffer_length=1048576 --log-error=/tmp/a.ere --single-transaction  --default-character-set=utf8  -u dba -p -B  数据库名 > /tmp/xxx.sql

select * from xi_table into outfile ’d:\test.xls’;

mysqldump db_name >backup-file.sql

mysql>source news.sql;  ##在mysql 命令行里执行

mysql --socket=/tmp/mysql3306.sock -udba -p123456 vipshop < orders_test11.sql

crontab -l

crontab -e

vmstat 5 
iostat -dx 5

update mysql.user set password=password('123456');

flush privileges;

insert into m_shop_config set parent_id = 2,code = 'online_pay_url',type = 'text',value = 'http://mapi.vip.com/vips-mobile/router.do',sort_order = 1

///Cgroups 

///Cobar

//Amoeba

STRAIGHT_JOIN

count(distinct xxx)/count(*)  

select count(distinct left(order_sn,2))/count(*) from crm_cust_appeal

select a,char_length(a),length(a) from t;

show status like '%last_query%';

max_user_connections

max_connections
max_connect_errors

show status like '%thread%'

排序（order by ，group by）查询上都是无法使用前缀索引

索引使用次数:

select * from information_schema.INDEX_STATISTICS where TABLE_SCHEMA='tms' and INDEX_NAME='IDX_O_S_C_A';

key status:

show status like ‘Handler_read%’;   

Mysql 的日期与字符是相同的，所以不需要做另外的转换

InnoDB Transaciton:::Dirty Page(Buffer Pool) -> Double Write Buffer -> Redo Log -> Disk

show variables like 'log_%';

log-bin=/var/lib/mysql/mysql-bin-log

show binary logs;

show binlog events;

desc extended

show warnings

show variables like '%table_size'

show variables like "tmpdir";

Created_tmp_disk_tables/Created_tmp_tables<5%

Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，如果 Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些.

show global status like 'handler_read%';  
show global status like 'com_select';  

计算表扫描率：

表扫描率 ＝ Handler_read_rnd_next / Com_select

如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。

innodb_flush_log_at_trx_commit=1

Innodb_log_wait   #redo log空间占满时,会自动flush一部分到磁盘.此计数器会自增一次

检测到系统空闲的时候，会flush，每次64 pages

inet_aton('192.168.1.200')    ip地址存储

inet_ntoa()

not in () ... 可优化成 join on <>

not in() 可优化成 in()

1. select id,group_concat(name order by name desc) from aa group by id;
   Using index for group-by(松散索引扫描)  // Using index(紧凑索引扫描)
   

CREATE INDEX IDX_has_sync_credtm ON INF_POS_PAYMENT_S (has_sync,created_dtm_loc)

[mysqld]
basedir=F:/APMServ5.2.6/MySQL5.1
datadir=F:/APMServ5.2.6/MySQL5.1/data
port=3306
interactive_timeout=240
wait_timeout=240
log="F:/log/mysql.log"
log_slow_queries="F:/log/mysql_slow.log"
long_query_time=1

log_queries_not_using_indexes=on

mysqlslap  --concurrency=500 --iterations=8 --create-schema=test --query=/root/test.sql

 show status like

show varialbes like

show engine innodb status

show table status like 'test1%'

show open tables; //name locked

information_schema.innodb_lock_waits

show procedure status;

show create procedure tttt.;

create procedure testACB()BEGINDECLARE i int;set i = 1;while i < 100 DOset i = i + 1;select i;end while;end;

时间:

TIMESTAMPADD(DAY, -93, now()) 

date_add(now(),interval -1 month)

STR_TO_DATE('2014-03-05 01:20:54', '%Y-%m-%d %H:%i:%s')

select ifnull(xxx,1) as res ...

select IF(lk.DELAYED_QTY/lk.CUST_QTY!=0&&lk.REJECTED_QTY/lk.CUST_QTY!=0,lk.DELAYED_QTY/lk.CUST_QTY, null) as aa from ...

 ALTER TABLE t1 MODIFY id2 INT(5) ZEROFILL;

批量更改:

UPDATE tms_sms_templet set templet_text=REPLACE(templet_text,'vipshop.com','vip.com') where templet_text LIKE '%vipshop.com%';


冗余索引:innodb中，主键索引总会在二级索引后面。
前缀索引

哈希索引:add key(aaa(7))

索引顺序：select count(distinct aaa)/count(*), count(*) from bbbb.

范围条件rage: in 可以，相当于多个=,  但<>有限制..多个范围条件，只能使用一个

or/in  in里数据量大时用in,  因为in里会先排序再二分法查找是否满意，时间O(log n), or 时间是O(n)

max min不会使用索引，用limit代替

在同一表上查询和更新：update .. inner join .. set ..

用count(*)  

count(color='blue' or null) as bbb from... sum(if(color='blue',1.0)) as bbbb from 

使用变量避免重复查询。。sele .. where @AA = 2344; select @AA

 

innodb_io_capacity 参数默认是 200,单位页。设置的大小取决于你的硬盘的IOPS,即每秒的输入输出量(或读写次数)。


set global innodb_stats_on_metadata = OFF;

analyze table t

show index from t

>mysqld -install mysqlndb

stop the mysql password,..    my.ini ----- skip-grant-tables

>mysql -uroot --port 3308   ##port  easy to forget

update user set host = '%' where user ='root';  flush privileges;    ##remove connected

mysqlslap  --concurrency=50,100,200 --iterations=1 --engine=myisam,innodb,ndb --number-of-queries=200 --debug-info -P 3308

check table COLUMNS

repair table COLUMNS

select table_name,engine,ROUND(data_length/1024/1024,2) total_size_mb,table_rows from information_schema.tables

-> where table_schema = ‘test’ and table_name like ‘iplog_%’

SELECT @@global.tx_isolation;

SELECT @@tx_isolation;

SELECT * FROM t LOCK IN SHARE MODE;

alter table task add index FK36358557817420 (creator_id);

alter table task add constraint FK36358557817420 foreign key (creator_id) references user (id);

alter table snc_report_info add groupid bigint(20) unsigned;

1. alter table testNoPk   
2.    add primary key PK_testNoPK (id);

ALTER TABLE `attributes` DROP FOREIGN KEY `attributes_ibfk_1`; 

ALTER TABLE `snc_report_info` DROP  KEY `report_info_key`; 

 

show engines;
//show the defult engine

alter table table_name engine=InnoDB;

show variables like '%storage_engine%';  //engines

show variables like 'char%';

SHOW TABLE STATUS

show variables like 'version';  //check mysql version

\s   status  select version()     mysql --help | grep Distrib 
 

show variables like 'log_%'; //logs

show master status; 

show master logs;

mysqlbinlog mail-bin.000001 

mysqlbinlog mail-bin.000001 | tail     //view log file

 

 

set session transaction isolatin level repeatable read;

 

 

select @@profiling;  //check the run plan arg , if on or off

set profiling=1;  set profiling=1;

show profiles/G;

show profile for query 1;

show profile cpu,block io for query 1;

 

 //show cached variables

show global status like 'qcache%';

 

 

flush privileges; //after update psword in user table

select CONCAT('My', 'S', 'QL');//mysql

select aa || bb //oracle 

 

sql:

SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table;

可以在定义为NOT NULL的列内插入0或空字符串，实际是NOT NULL
可以在定义为NOT NULL的列内插入0或空字符串，实际是NOT NULL
SELECT CASE WHEN units is not null THEN 'history' ELSE 'history_uint' end historyTName from items where hostid = 10001 and key_ = 'vfs.fs.size[/,free]'

source test.sql; // mysql run sql file;

@test.sql; //oralce run sql file;

view procedure/function:  

select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

show procedure status;

show create procedure proc_name;

select 'sdfsdfsd32fsdf' regexp '^fs';

myslq procecure in shell plus:

DELIMITER //  mysql>  mysql> CREATE PROCEDURE p7 (IN b INTEGER(12))      ->      -> begin      ->      -> declare a INTEGER(12);      ->      -> set a=5;      ->      -> INSERT INTO t VALUES (a);      ->      -> SELECT s1*a FROM t WHERE b<=s1;      ->      -> End      ->      -> //

Oracle : / in end

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

 INSERT INTO table (a,b,c) VALUES (1,2,3)

    -> ON DUPLICATE KEY UPDATE c=c+1;

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

注释：当MySQL决定如何在表中查找行并决定如何进行联合时，使用USE INDEX、IGNORE INDEX和FORCE INDEX只会影响使用哪些索引。当分解一个ORDER BY或GROUP BY时，这些语句不会影响某个索引是否被使用

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id

    ->          LEFT JOIN table3 ON table2.id=table3.id;

mysql> SELECT * FROM table1 USE INDEX (key1,key2)

    ->          WHERE key1=1 AND key2=2 AND key3=3;

mysql> SELECT * FROM table1 IGNORE INDEX (key3)

    ->          WHERE key1=1 AND key2=2 AND key3=3;

如果表t2为空表，则结果为TRUE。因此，当表t2为空表时，以下语句为TRUE：

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

但是，当表t2为空表时，本语句为NULL：

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

NOT IN是<> ALL的别名。因此，以下两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

行子查询

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);

SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

SELECT * FROM t1 WHERE (column1,column2) = (1,1);

  SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

使用此查询：

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

代替此查询：

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

使用行子查询，代替关联子查询。举例说明，使用此查询：

·                SELECT * FROM t1

·                WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

代替此查询：

SELECT * FROM t1

WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1

AND t2.column2=t1.column2);

·         Use NOT (a = ANY (...)) rather than a <> ALL (...).

·         Use x = ANY (table containing (1,2)) rather than x=1 OR x=2.

·         Use = ANY rather than EXISTS.

·         对于只返回一行的无关联子查询，IN的速度慢于＝。举例说明，使用此查询：

·                SELECT * FROM t1 WHERE t1.col_name

·                = (SELECT a FROM t2 WHERE b = some_const);

代替此查询：

SELECT * FROM t1 WHERE t1.col_name

IN (SELECT a FROM t2 WHERE b = some_const);

当使用MyISAM存储引擎时，MySQL使用极快速的表锁定，以便允许多次读或一次写。

每个MyISAM表最大索引数是64。 这可以通过重新编译来改变。每个索引最大的列数是16个。

//查询缓存

SHOW VARIABLES LIKE 'have_query_cache';

SELECT BENCHMARK(1000000,1+1)；

显示MySQL在该系统上在0.32秒内可以执行1,000,000个简单的+表达式运算。

MySQL用一遍扫描多次联接（single-sweep multi-join）的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。

《高性能Mysql(第2版)》看到的，varchar(255)与varchar(80)都是保持可变的字符串，当使用ROW_FORMAT=FIXED创建MyISAM表时，会为每行使用固定的长度空间，这样设置不同的varchar长度值时，存储相同数据所占用的空间是不一样。

另外虽然通常情况下使用varchar(255)和varchar(80)保持'hello'占用的空间都是一样的，但使用长度较短的列却有巨大的优势。较大的列使用更多的内存，因为MySQL通常会分配固定大小的内存块来保存值。这对排序或使用基于内存的临时表尤其不好。同样的事情也会发生在使用文件排序或者基于磁盘的临时表的时候。

GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

UTF8: CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

load data infile 'c:\\users_groups.txt' into table users_

terminated by ',' lines terminated by '\n';

启动 ，监控执行慢的sql ,默认是大于10s

--log-slow-queries=show.log

log_query_time=5s

用mysqldumpslow分析log工具

mysqlbinlog 

mysqlbinlog mail-bin.000001 | tail

show master status 知道当前日志

show master logs 全部日志

mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003

mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql

mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql

mysql -e "source /tmp/statements.sql"

explain::

type:   效率 顺序

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

KEY `users_1` (`alias`) USING BTREE

UNIQUE KEY `user_name` (`suppliers_name`) USING BTREE ，修改为

UNIQUE KEY `user_name` USING BTREE  (`suppliers_name`) 

这是mysql 5.1，更高版本 更强大了

存储引擎

允许的索引类型

MyISAM

BTREE

InnoDB

BTREE

MEMORY/HEAP

HASH, BTREE

如果列有多个索引类型，当没有指定index_type时，第一个类型是默认值。

myisam里所有键的长度仅支持1000字节，innodb是767.

2. blob和text字段仅支持前缀索引.

3. 使用!=以及<>不等于的时候，mysql不使用索引.

4. 当在字段时候函数的时候，mysql无法使用索引；在join时条件字段类型不一致的时候，mysql无法使用索引；在组合索引里使用非第一个索引时也不使用索引.

5. 在使用like的时候，以%开头，即"%***"的时候无法使用索引；在使用or的时候，要求or前后字段都有索引.

有时候mysql query optimizer会认为使用索引并不是最优计划,所以不使用索引。可以在sql语句里可以用use,force index,当然有时候使用也不会比不用快,所以需要忽略掉index方法是ignore index.

NDB引擎：

在创建表时，在表的主键上用AUTO_INCREMENT选项，不可以。

不能将"关闭autocommit"作为缺省设置，否则在 innodb 表上执行的查询操作也将因为没有执行 commit 或者 rollback 而一直锁表！因此只能在需要时局部关闭 autocommit，并在操作完成后开启 autocommit！

Group By ，Order By优化
需要保证Group By、Order By涉及的列位于同一个表里，以使查询执行时利用该表的索引成为可能
对于无法使用索引的Group By查询，MySQL使用两种策略：临时表、FileSort；可以使用显式的SQL_BIG_RESULT或SQL_SMALL_RESULT来为查询优化器提供指示（hint）
MySQL默认以Group By里的字段进行排序。为了避免不必要的排序（和造成的Filesort），可以明确指定order by NULL

Covering Index，就是说不必查询表文件，单靠查询索引文件即可完成。具体到此例中就是建立一个复合索引“b, a”，当查询进行时，通过复合索引的“b”部分去定位，至于需要的数据“a”，立刻就可以在索引里得到，从而省略了表查询的过程

如何才能确认查询使用了Covering Index呢？很简单，使用explain即可！只要在Extra里出现Using index就说明使用的是Covering Index。

mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5; 可以被优化为：

mysql> SELECT film.film_id, film.description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);

//查询缓存

select @@query_cache_type

query_cache_size

FLUSH QUERY CACHE

二类可触发查询缓存数据全部清空的命令：

(1).     RESET QUERY CACHE；

(2).     FLUSH TABLES；

mysql -e 'SELECT * FROM imptest' test -u root -p

//mysql 数据导入

LOAD DATA INFILE "/home/Order.txt" INTO TABLE Orders(Order_Number, Order_Date, Customer_ID); //按格式导入

mysqlimport –prl –fields-enclosed-by=" –fields-terminated-by=, Meet_A_Geek Orders.txt   //按格式导入  //不用登陆导入

source  filename    //登陆导入

//oracle  是用 @filename  导入

1.导出整个数据库
 mysqldump -u 用户名 -p 数据库名 > 导出的文件名    
 mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.导出一个表
 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
 mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
  mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql

 -d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table 

4.导入数据库
  常用source 命令
  进入mysql数据库控制台，
  如mysql -u root -p 
  
  mysql>use 数据库

  然后使用source命令，后面参数为脚本文件（如这里用到的.sql）

set names utf8
  mysql>source d:\wcnc_db.sql

可以在定义为NOT NULL的列内插入0或空字符串，实际是NOT NULL

prepareStatement

MySQL默认预编译是关闭的（了解即可）

MySQL的驱动默认情况下是不会预编译SQL的，这需要我们把useServerPrepStmts参数设置为true，这样MySQL驱动就打开了预编译功能。可以在url中设置这个参数：

url=jdbc:mysql:///mydb1?useServerPrepStmts=true

create table snc_report_info (
infoid bigint(20) NOT NULL AUTO_INCREMENT,
reportid bigint(20) ,
keyname varchar(64) DEFAULT NULL,
fieldname varchar(64) DEFAULT NULL,
PRIMARY KEY (infoid),
CONSTRAINT report_info_key FOREIGN KEY(reportid) REFERENCES snc_report(reportid) on delete cascade on update cascade 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果in和or所在列没有索引的话，性能差别就很大了。

因此在给in和or的效率下定义的时候，应该再加上一个条件，就是所在的列是否有索引或者是否是主键。如果有索引或者主键性能没啥差别，如果没有索引，性能差别不是一点点！

in 优于 or

SELECT * FROM a LEFT JOIN b ON a.id=b.id 
-> UNION 
-> SELECT * FROM a RIGHT JOIN b ON a.id=b.id;

mysql秘密：

mysql  多重主键 中 如: primary key(id1, id2)

这多个key中只能有一个 作为外键连接

而 Oracle  没有这个限制

MySQL使用环境变量TMPDIR的值作为保存临时文档的目录的路径名。假如未配置TMPDIR，MySQL将使用系统的默认值，通常为/tmp、/var/tmp或/usr/tmp。假如包含临时文档目录的文档系统过小，可对mysqld使用“—tmpdir”选项，在具备足够空间的文档系统内指定1个目录。

　　在MySQL 5.1中，“—tmpdir”选项可被配置为数个路径的列表，以循环方式使用。在Unix平台上，路径用冒号字符“:”隔开

High Performence Mysql 3

benchmark()

sysbench--oltp/fileio

http-load

执行时间/等待时间

New Relic

Mysql Enterprise Monitor

long_query_time=0

tcpdump

5.5 Performance Schema/ 5.6提供了查询级别的细节数据       //SHOW VARIABLES LIKE 'performance_schema';

*show processlist

*show status

show profile

show global status

show innodb status

show table status

用TIMESTAMP类型取代INT和DATETIME

MySQL要求一个行的定义长度不能超过65535

表一对一， 小数据跟大数据分离 

alter table `test`.`xiaodi` change `name` `name` varchar(65535) character set latin1 collate latin1_swedish_ci default '' NULL 

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

由此可见mysql里的TEXT or BLOBs会到另外的存储行

（如果某个表的text字段很多建议建表时加上 row_format = dynamic ，因为默认不是的）

日志 数据引擎  ARCHIVE

日志数据用ARCHIVE引擎, 空间比myisam省, 比innodb更省, insert在数据量大时比其他引擎要快得多, select too, But,   no key,  even though primary key

show variables like 'max_connections'

show global status like 'Max_used_connections'

MySQL的查询缓冲是区分大小写的。

而且查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格(因为，查询缓冲并不自动截取首尾空格)。

select @@query_cache_type

set session uery_cache_type=off;

show variables like 'have_query_cache';

select @@global.query_cache_size;

set @@global.query_cache_size=1000000;

select @@global.query_cache_limit;

1.    SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

还可以将my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。
1.    SELECT SQL_CALHE * FROM TABLE1

如果这种商品的数量非常多，差不多占了SALES表的记录的50%或更多。那么使用SALE_DATE字段上索引来计算平均数就有些慢。因为如果使 用索引，就得对索引进行排序操作。当满足条件的记录非常多时(如占整个表的记录的50%或更多的比例)，速度会变慢，这样还不如对整个表进行扫描。因 此，MySQL会自动根据满足条件的数据占整个表的数据的比例自动决定是否使用索引进行查询。
对于MySQL来说，上述的查询结果占整个表的记录的比例是30%左右时就不使用索引了，这个比例是MySQL的开发人员根据他们的经验得出的。然而，实际的比例值会根据所使用的数据库引擎不同而不同。

Memory不支持Blob 和Text类型，除非order by sustring(column, length)

mysql 能存储最小时间粒度是 秒（但可用微秒进行临时运算），MariaDB是 微秒

UUID -> unhex() -> binary(16)  检索时hex()

mysql会在索引中存储null值，而oracle不会

SELECT vale1, value2 into Table2 from Table1 (目标表Table2不存在)

Insert into Table2(field1,field2,...) select value1,value2,... from Table1

0-200 tinyint unsigned

ip 用整型

避免null

itmestamp 只使用datetime的一半存储空间

如row_format=fixed  浪费空间

varchar  －－－update 时有碎片问题，myisam会拆成不同的片段，innoDB则分裂页

汇总表、缓存表、计数器表   update ... set cnt = cnt+1 where slot = rand() * 100

drop table if exists my_new,my_old;

create table my like my_table

rename talbe my_old to my_new, my_old1 to my_new1

BLOB类型区分大小写，TEXT类型不区分大小写

InnoDB存储引擎的表空间由段（segment），区（extent），页（page）组成

INNODB的引擎对段的管理是由引擎本身完成的，不是每个对象都有段，表空间是有分散的页和段组成的。

区：64个连续的page组成，每个page16K，sql server 8K

对于大的数据InnoDB引擎，每次最多可以申请4个区，来保证数据的顺序性

对无WHERE子句的COUNT(*)操作的不同：MyISAM中保存了该值，直接读取，InnoDB需要作全表扫描

一个页由多个行组成，因为是索引组织表，所以一个页最少要有2个行。

页是innoDB存储引擎最小的磁盘空间单位。一个页的大小为固定的16KB。

innodb为每个空表分配了6个页大小的空间用

为一个列添加一条索引innodb就会给再申请16KB大小的磁盘空间，也就是1个页大小的空间使用

innodb在新建一个表的时候会用零散的36个页存储数据，如果数据量大于36页就会开始申请更大的空间存储数据。申请64个连续的页大小的空间。以后随着数据的增加继续申请64个页的空间。

不同于mysql, Oracle不支持ENUM数据类型,只能通过使用基于文本的check约束或者创建外键关联表来实现

默认情况下,Oracle不会自动提交

Oracle没有区分TIME与DATE类型,Oracle的Date类型实际上就是一个DATETIME类型(但是比MySQL支持一个更大的日期范围)

任何值和NULL拼接后都为NULL,所有与NULL进行的数学操作都返回NULL,引入NULL后，逻辑不易处理

mysql 索引在数据引擎层实现
5.1之前Archive不支持任何索引，之后才开始支持单个自增列索引
myisam使用前缀压缩技术使得索引更小，但innodb则按照原数据格式
B-Tree索引列是顺序存储的，适合查找范围数据，
匹配前缀；不能跳过索引；如查询中有某个列的范围查询，则右边的列都无法使用优化


索引帮助服务器避免排序和临时表；把随机IO变为顺序IO；
利用前缀索引：left(..., 3)   (后缀索引，就同理反转)
创建前缀索引：add key (city(3))
先计算选择性：select count(distinct left(city,3))/count(*), ... from ...
缺点：无法使用前缀索引做order by,group by 覆盖扫描


只有memory能显式支持哈希索引(默认),同时也支持B-Tree，支持非唯一哈希索引，如多个哈希值相同，以链表


方式保存
哈希索引无法排序，不支持部分索引列查找，只支持等值比较查询，不支持范围查询
NDB集群引擎 支持唯一哈希索引
多字符串索引改为哈希索引/增加一列，CRC32()
R-Tree 空间数据索引 少用
全文索引和B-Tree一起创建不会有冲突


mysql5.0后 能利用"索引合并", 利用多个单列索引(5.0前用 ... union all...)
innodb(聚簇索引)
myisam(非聚簇索引)
using index
覆盖索引－－〉延迟关联， 只能用于B-Tree
不能在索引中like操作，底层存储引擎API的限制


mysql 两种排序算法？
分解关联查询
Archive引擎 没有存储任何统计信息
exsits 代替 in
group by,order by只涉及一个表中的列，才可能使用索引优化
优化limit 关联/分页记住值，不用offset
变量返回行号




mysql 并不支持像pgsql update returning 更新行时返回此行的信息
select sleep(1000)  //秒
kill query
mysqladmin processlist/mysqladmin kill
show processlist/kill qid

不惜一切代价避免使用Swap交换分区

将操作系统和数据分区分开

把MySQL临时空间和复制日志与数据放到不同的分区

在Linux中设置swappiness的值为0 – 在数据库服务器中没有理由缓存文件，这是一个服务器或台式机的优势

使用 XFS 文件系统 – 一种比ext3更快、更小的文件系统，并且有许多日志选项， 而且ext3 已被证实与MySQL有双缓冲问题

切勿强制杀死一个MySQL进程 – 你会损坏数据库和正在运行备份的程序

UTF 8和UTF16都低于latin1执行效率

避免在整个表上使用count(*),它可能锁住整张表。(估计是innodb)

为了 避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现

在执行ALTER TABLE时使用除了RENAME以外的选项，则MySQL会创建一个临时表。即使数据并不需要进行复制（例如当您更改列的名称时），MySQL也会这么操作。对于MyISAM表，您可以通过把myisam_sort_buffer_size系统变量设置到一个较高的值，来加快重新创建索引（该操作是变更过程中速度最慢的一部分）的速度。

alter table ,MODIFY是Oracle对ALTER TABLE的扩展

ALTER TABLE t1 CHANGE b b BIGINT NOT NULL

ALTER TABLE t1 MODIFY b BIGINT NOT NULL

FULLTEXT索引只能对CHAR, VARCHAR和TEXT列编制索引，并且只能在MyISAM表中编制

SPATIAL索引只能对空间列编制索引，并且只能在MyISAM表中编制

BLOB和TEXT列不能被赋予默认值。

INSERT DELAYED

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table

mysqlimport

生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

在MySQL中，CROSS JOIN从语法上说与INNER JOIN等同（两者可以互相替换。在标准SQL中，两者是不等同的。INNER JOIN与ON子句同时使用，CROSS JOIN以其它方式使用。

INNER JOIN和，（逗号）在无联合条件下是语义相同的：两者都可以对指定的表计算出笛卡儿乘积（也就是说，第一个表中的每一行被联合到第二个表中的每一行）。

USE KEY、IGNORE KEY和FORCE KEY是USE INDEX、IGNORE INDEX和FORCE INDEX的同义词

词语SOME是ANY的别名

show variables like "have_%"

对于MyISAM和HEAP表，在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对NOT NULL表达式也这样做。

锁定表可以加速用多个语句执行的INSERT操作：
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
这样性能会提高，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行，就不需要锁定。
对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。

MyISAM:
对于索引块，维护一个称之为键高速缓冲(或键高速缓冲区)的特殊结构。该结构包含大量块缓存区，其中放置了最常用的索引块。
对于数据块，MySQL不使用特殊缓存。而使用原生的操作系统文件系统的缓存。
可以使用key_buffer_size系统变量控制 键高速缓冲的大小。如果该变量设置为零，不使用键高速缓冲。如果key_buffer_size值太小不能分配最小数量的块缓存区(8)，也不使用 键高速缓冲。

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定，对BDB表进行页级锁定，对InnoDB表进行行级锁定。

InnoDB使用行锁定，BDB使用页锁定。对于这两种存储引擎，都可能存在死锁。

为达到最高锁定速度，除InnoDB和BDB之外，对所有存储引擎，MySQL使用表锁定(而不是页、行或者列锁定)。
对于InnoDB和BDB表，如果你用LOCK TABLES显式锁定表，MySQL只使用表锁定。对于这些表类型，我们建议你根本不要使用LOCK TABLES，因为InnoDB使用自动行级锁定而BDB使用页级锁定来保证事务隔离。

InnoDB是唯一支持外键的

sql 中尽量不使用current_date(之类的，不能用查询缓存

子查询和存储过程也不支持查询缓存

oracle varchar2 最大存4000字节

sql server 最大8000字节

mysql 最大65535字节(65532)

唯一性自动生成:

自动生成,生成规则:前缀+日期+流水号
前缀:1181
流水号:自增长111111-999999
(如:118120140101222222) (8字节,刚好18位数字,bigint 最大19位,unsigned bigint最大20位)

MySQL在一个查询中，只能使用一个索引，明确这一点非常重要。
你可能建立了若干索引，但MySQL要么全都不用，要么，只能选择其中一个。
然而，如果分成子查询，则可以在不同的子查询中使用不同的索引。