mysql调优及点滴记录

关于“select count(*)”和“select count(col)”的效率比较（参考http://hi.baidu.com/wrjgg/item/f5b823b4e1dca79918469729）：

    1.任何情况下SELECT COUNT(*) FROM tablename是最优选择；

    2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = 'value’ 这种查询；

    3.杜绝SELECT COUNT(COL) FROM tablename的出现

1.关于多列合在一起作为unique key的情况需要注意：如果columnA、columnB、columnC合在一起作为unique key，那么三项都不要“可为空”，因为如果columnA可为空，则会出现多条columnA为空、columnB和columnC分别相等的记录（数据库不报错）

2.使用--log-slow-queries –-long-query-time=2查看查询比较慢的语句。然后使用explain分析查询，做出优化(log_queries_not_using_indexes记录未使用索引的查询)

3.尝试避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题

4.尽量不要让列可为null，不利于mysql优化

5.对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。

   如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。

   但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

6.建索引的几个要点：

    1>复合索引

        比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

        如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

    2>索引不会包含有NULL值的列

        只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    3>使用短索引

        对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    4>排序的索引问题

        mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

    5>like语句操作

        一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    6>不要在列上进行运算

         select * from users where YEAR(adddate) ...

    7>不使用NOT IN

        NOT IN不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替。

7.主键和唯一索引的区别（django１.４还不支持composite primary key，有一些扩展包提供该功能，但是他们建立的所谓的“组合主键”到了数据库层面居然是唯一索引，所以才对这两个概念有些迷惑）

  主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

  主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

  唯一性索引列允许空值，而主键列不允许为空值。

  主键列在创建时，已经默认为非空值 + 唯一索引了。

　主键可以被其他表引用为外键，而唯一索引不能。

  一个表最多只能创建一个主键，但可以创建多个唯一索引。

  主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

  在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

8.innodb表修改索引（已验证的是删索引）会很慢，myisam表就很快（show processlist跟踪到的过程：copy to tmp table; repair by sorting; rename table）

9.innodb表的数据不在mysql数据目录下：mysql数据目录下只有一个非常小（十几KB左右）的frm文件，没有MYD文件和MYI文件 （http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html）

InnoDB creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use theinnodb_data_home_dir option. For example, to use two files namedibdata1 andibdata2 but create them in the/ibdata directory, configureInnoDB like this:

[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

10.select * from a left join b on a.id = b.id;  左联接，结果以a.*，b.*显示，左侧（a.*）保留，右侧（b.*）用NULL填充

      select * from a right join b on a.id = b.id;  右联接，结果以a.*，b.*显示，左侧（a.*）用NULL填充，右侧（b.*）保留

      select * from a inner join b on a.id = b.id; 内联接（相等联接），等价于select * from a,b where a.id = b.id;

11.select * from a where a.id > 1 and a.id < 10; select * from a where a.id < 10 and a.id > 1; 后者效率比较高，因为id<10把范围缩小了，整个执行会更快

12.mysql分区和分表（merge存储引擎，或者业务层面实现）的侧重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。http://blog.51yip.com/mysql/1029.html

分表：业务层面控制分表、merge存储引擎分表     http://blog.51yip.com/mysql/949.html

分区：range分区、list分区、hash分区、key分区（KEY takes only a list of one or more column names. Beginning with MySQL 5.1.5, the column or columns used as the partitioning key must comprise part or all of the table's primary key, if the table has one.http://dev.mysql.com/doc/refman/5.1/en/partitioning-key.html）、子分区     http://blog.51yip.com/mysql/1013.html

./configure --help |grep -A 3 Partition

mysql> show variables like "%part%";  
+-------------------+-------+  
| Variable_name     | Value |  
+-------------------+-------+  
| have_partitioning | YES   |  
+-------------------+-------+  
1 row in set (0.00 sec)  

range分区：
CREATE TABLE IF NOT EXISTS `user` (  
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
 `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
 `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',  
 PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 PARTITION BY RANGE (id) (  
	PARTITION p0 VALUES LESS THAN (3),  
	PARTITION p1 VALUES LESS THAN (6),  
	PARTITION p2 VALUES LESS THAN (9),  
	PARTITION p3 VALUES LESS THAN (12),  
	PARTITION p4 VALUES LESS THAN MAXVALUE  
 ); 
 
 list分区：（如果有主銉的话，分区时主键必须在其中，不然就会报错。一般情况下，一个张表肯定会有一个主键，这算是一个分区的局限性吧。）
 CREATE TABLE IF NOT EXISTS `list_part` ( 
 `id` int(11) NOT NULL  COMMENT '用户ID', 
 `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
 `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'  
 ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 PARTITION BY LIST (province_id) (  
	PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
	PARTITION p1 VALUES IN (9,10,11,12,16,21),  
	PARTITION p2 VALUES IN (13,14,15,19),  
	PARTITION p3 VALUES IN (17,18,20,22,23,24)  
 );

根据官方文档和个人测试，mysql merge貌似存在下述问题：（2013-04-24）

对merge后的表做insert时数据只能插入到一张表里面，没法做均匀分布，所以一般由业务层面直接往子表插数据。You can optionally specify an INSERT_METHOD option to control how inserts into the MERGE table take place. Use a value of FIRST or LAST to cause inserts to be made in the first or last underlying table, respectively.

merge的不同子表中的数据没法保证统一的unique约束，需要业务层面的程序自己控制

13.like做模糊搜索效率非常低下（遍历所有记录），需要的话可用全文索引替换：http://db.090.net/2012/03/mysql%E5%85%A8%E6%96%87%E7%B4%A2%E5%BC%95/

14.查询的时候只好准确提供符合列类型的数据做检索，不要寄希望于Mysql很智能的检查和转换我们提供的数据

mysql> show create table wom_account\G
*************************** 1. row ***************************
       Table: wom_account
Create Table: CREATE TABLE `wom_account` (
  ...
  `provider` smallint(6) NOT NULL,
  `uid` varchar(64) NOT NULL,
 UNIQUE KEY `provider` (`provider`,`uid`),
) ENGINE=MyISAM AUTO_INCREMENT=2518615 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> explain * FROM wom_account WHERE provider=1 AND uid IN ('2840978914');
+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref         | rows | Extra |
+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
|  1 | SIMPLE      | wom_account | const | provider,uid  | provider | 196     | const,const |    1 |       |
+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid IN (2840978914);  
+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows   | Extra       |
+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | wom_account | ref  | provider,uid  | provider | 2       | const | 489995 | Using where |
+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid = 2840978914;   
+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows   | Extra       |
+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | wom_account | ref  | provider,uid  | provider | 2       | const | 489995 | Using where |
+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid = '2840978914';
+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref         | rows | Extra |
+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
|  1 | SIMPLE      | wom_account | const | provider,uid  | provider | 196     | const,const |    1 |       |
+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
1 row in set (0.00 sec)

15.distinct排重的是select出来的列组成的整个记录行

mysql> select a,b from x where a = 1 or b = 1;         
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    2 |
|    1 |    3 |
|    1 |    3 |
|    2 |    1 |
|    2 |    1 |
|    3 |    1 |
|    3 |    1 |
+------+------+
9 rows in set (0.00 sec)

mysql> select distinct a,b from x where a = 1 or b = 1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    2 |    1 |
|    3 |    1 |
+------+------+
5 rows in set (0.00 sec)

16.对查询结果做union

昨天(2013.3.7)遇到一个从交互表x中查与1交互的用户：初始解决办法是用“select b from x where a = 1 ”和“select a from x where b = 1”的结果在内存排重后统计，真是奇慢无比，可以想象光是传数据就很耗时

改进的办法是用union对查询结果做联接，这样虽然还是要对两个子查询的产生的大数据结果做合并，但是少掉了网络传输时间，并且Mysql内部做的排重和排序肯定比我们业务层要快

union默认是union distinct

mysql> select b from x where a = 1 ;
+------+
| b    |
+------+
|    1 |
|    2 |
|    2 |
|    3 |
|    3 |
+------+
5 rows in set (0.00 sec)

mysql> select a from x where b = 1 ;  
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    2 |
|    3 |
+------+
5 rows in set (0.00 sec)

mysql> select b from x where a = 1 union select a from x where b = 1;
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

mysql> select b from x where a = 1 union all select a from x where b = 1;
+------+
| b    |
+------+
|    1 |
|    2 |
|    2 |
|    3 |
|    3 |
|    1 |
|    2 |
|    3 |
|    2 |
|    3 |
+------+
10 rows in set (0.00 sec)

17.获取刚插入的一条新纪录的自增ID,随便查了一下就得到下述三种方式 （2014.5.29）

select last_insert_id();  这里不用担心多条连接并发操作使得获取id错误，每条连接会独立保留该连接上次插入纪录对应的自增id.（已使用多个mysql client做过测试！）

http://stackoverflow.com/questions/933565/most-efficient-way-to-get-table-row-count

$result = mysql_query("SHOW TABLE STATUS LIKE 'table_name'");
$row = mysql_fetch_array($result);
$nextId = $row['Auto_increment'];
mysql_free_result($result);

SELECT TABLE_ROWS
FROM information_schema.tables 
WHERE table_name='the_table_you_want' -- Can end here if only 1 DB 
  AND table_schema = DATABASE();      -- See comment below if > 1 DB

下面参考http://www.tyhs.net.cn/article.asp?articleid=64

                http://blog.csdn.net/maria57/article/details/2963410（这篇文章分析很详细，innodb和myisam不同引擎的设置也都有讲到）

索引

1) MySQL只会使用前缀，例如key(a, b) …where b=5 将使用不到索引。

2) 要选择性的使用索引。在变化很少的列上使用索引并不是很好，例如性别列。

3) 在Unique列上定义Unique index。

4) 避免建立使用不到的索引。

5) 在Btree index中(InnoDB使用Btree)，可以在需要排序的列上建立索引。

6) 避免重复的索引。

7) 避免在已有索引的前缀上建立索引。例如：如果存在index(a，b)则去掉index(a)。

8) 控制单个索引的长度。使用key(name(8))在数据的前面几个字符建立索引。

9) 越是短的键值越好，最好使用integer。

10) 在查询中要使用到索引(使用explain查看)，可以减少读磁盘的次数，加速读取数据。

11) 相近的键值比随机好。Auto_increment就比uuid好。

12) Optimize table可以压缩和排序index，注意不要频繁运行。

13) Analyze table可以更新数据。

14)对于已建索引的字符串类型列如果用数值类型做查询（where columnName = 100）索引就用不上，数据量大的时候查询异常缓慢

服务器设置优化

MySQL默认的设置性能很差，所以要做一些参数的调整。这一节介绍一些通用的参数调整，不涉及具体的存储引擎(主要指MyISAM，InnoDB)

--character-set：如果是单一语言使用简单的character set例如latin1。尽量少用Utf-8，utf-8占用空间较多。

--memlock：锁定MySQL只能运行在内存中，避免 swapping，但是如果内存不够时有可能出现错误。

--max_allowed_packet：要足够大，以适应比较大的SQL查询，对性能没有太大影响，主要是避免出现packet错误。

--max_connections：server允许的最大连接。太大的话会出现out of memory。

--table_cache：MySQL在同一时间保持打开的table的数量。打开table开销比较大。一般设置为512。

   如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）

--query_cache_size： 用于缓存查询的内存大小。

   使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。

   根据MySQL用户手册，使用查询缓冲最多可以达到238％的效率。
   通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。

   如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；

   如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。

   此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
   与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。

       query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。

       query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。

       query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。

 检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。

--datadir：mysql存放数据的根目录，和安装文件分开在不同的磁盘可以提高一点性能。

--key_buffer_size：指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。

  比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。

  key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

  对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。

MyISAM优化要点

1) 声明列为NOT NULL，可以减少磁盘存储。

2) 使用optimize table做碎片整理，回收空闲空间。注意仅仅在非常大的数据变化后运行。

3) Deleting/updating/adding大量数据的时候禁止使用index。使用ALTER TABLE t DISABLE KEYS。

4) 设置myisam_max_[extra]_sort_file_size足够大，可以显著提高repair table的速度。

项目中sql优化时的会议记录：---> start

子查询尽量要避免，因为子查询的结果会存成临时表，而临时表是无法加索引的。

临时表只能逐行过滤

下面两种写法效果等同（“inner join”没有"left"和“right”的语法，它就是“inner join”）

select * from a inner join b on a.y = b.y;

select * from a,b where a.y = b.y;

"left join"默认是"left outer join" （outer,不是out）

左外连接，如果 a left out join b，那么如果在a中存在的记录如果在b中不存在，则b中对应的列的值为NULL，通过选择b中列为null的行就把结果选出来了

多个连接条件：http://dev.mysql.com/doc/refman/5.0/en/join.html

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

查看数据库版本：

SELECT version();

SELECT @@version;

《High.Performance.MySQL》（高性能MYSQL）需要阅读的章节：1、4、5、6、7

1.    client-server 结构

    一个客户连接在server端由一个线程处理（5.5的优化：线程池，一个线程可以服务于多个客户端）

    Query cache(sql语句缓存), Parser

    读写锁（写锁饥饿：写操作因为接连的读操作而拿不到锁，解决办法是序列化请求锁）

2.    事务,ACID（原子性、一致性、隔离性、持久性）

    隔离级别：

        READ UNCOMMITED, 可读到没提交的数据（脏数据），隔离级别最低

        READ COMMITED, 只读他人提交后的数据（同一个事务中两次相同读取的时间之间他人可以更新该数据）

        REPEATABLE READ, 可重复读（两次读取的数据一致，同一个事务中两次相同读取的时间之间他人不可以更新该行数据，但是可能会增加新的行）（幻读，phantom read, range select, find new records）

            T1：

                read R1

                ... Tn: UPDATE R1(N) INSERT R2(Y)

                read R1

        SERIALIZABLE， 序列化

            T1：

                read R1

                ... Tn: UPDATE R1(N) INSERT R2(N)

                read R1

3.    MVCC

    R1: data (insert version; delete version)

    R1

    R2

    R3

    ...

    Rn

    insert version <= current_version

    delete version == undifined OR delete version > current_version   

4.SHOW TABLE STATUS LIKE 'user'; 查看表user的状态

5.select count(*) from a; 没有where条件且引擎是MYISAM时速度很快，数据表有字段跟踪行数

  select count(id) from a; 统计的是id不为空的行数

6.推荐用Oracle InnoDB替代开源社区的InnoDB(5.5后默认是前者)

7.varchar(5)是5个字符，不是5个字节（5个字符可能是15个字节...）

8.MYISAM修复比较慢，因为没有日志要扫描整个文件；MYISAM索引是压缩的，比较小一些；表级锁

  INNODB可以热备、支持事务、恢复快，行级锁

9.程序性能瓶颈：CPU 磁盘IO 内存 网络IO

10.create table b like a; 创建和a一样结构的新表b

11.用B+树存索引而不是用二叉树：优化磁盘读写

12.聚集索引（Clustered Indexes）：数据和索引存在一起，找到索引就找到了数据

    inno DB支持聚集索引,但只支持对primary key做聚集索引

    InnoDB:(如果primary key很大的话索引文件就会很大、占内存空间和磁盘空间；primary key需要尽量按照递增的顺序产生，不要随机生成)

        primary key: index + data

        second index: index + primary key

    MyISAM:

        primary key: index + pointer to data

        second index: index + pointer to data

13.对order by的列设置了索引，数据从磁盘读出来的就会变成有序的，不需要再在内存里面做排序

14.    replace into

    insert into t values (..) on duplicate key update ...

15.union 和 union all，再确定不重复的情况下最好用后者（无排序和排重操作，速度快）

项目中sql优化时的会议记录： end<---

mysl小知识点：

1.mysql支持正则查询，比如下面这语句就是统计wom_account_data_sina.uid非纯数字的记录数

select count(*) from wom_account_data_sina where uid not regexp '^[0-9]+$';

2.主从复制见另一篇文章 http://blog.csdn.net/xiarendeniao/article/details/10714283

3.授权了某个新用户newUser@'%'访问mysql，结果从本机连上来没有看到目标数据库？以前被这个小问题坑过一次，需要指定ip地址（not "localhost" or ''127.0.0.1"），否则真正登录上去的是newUser@localhost，那么按照mysql的默认配置只能看到test数据库，没有其他权限

4.诡异的小问题，一台机器起了多个mysql在不同的端口上提供服务，结果不论指定-P等于哪个端口都进到默认的数据库里面去了：需要指定-h127.0.0.1，而不是-hlocalhost

Unix平台Unix上的Mysql客户端能够以两种不同的方式连接到mysqld服务器：通过文件系统中的文件（默认为/tmp/mysql.sock）使用Unix套接字进行连接，或通过端口号使用TCP/IP进行连接。Unix套接字文件的连接速度比TCP/IP快，但仅能在与相同计算机上的服务器相连时使用。如果未指定指定主机名或指定了特殊的主机名localhost，将使用Unix套接字。http://dev.mysql.com/doc/refman/5.1/zh/problems.html(A.2.2.)

5.对于Mysql InnoDB表，“select count(*) from tableA;”，不做where条件过滤直接查询会非常慢，而MyISAM表没有这个问题 http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

6.and 优先级大于 or http://stackoverflow.com/questions/16249865/priority-of-and-and-or-operator-in-mysql-select-query 好的编写习惯是涉及到and or && || 的时候都用上小括号（&& || 是c里面的）

7.INSERT INTO `tbl_account` (`vouchSumA`,`vipLevel`,`accountId`) VALUES ('50000-1,20000-1,100000-1,400000-1,200000-1,5000-1','15','828548814') ON
DUPLICATE KEY UPDATE `vouchSumA`=VALUES(`vouchSumA`),`vipLevel`=VALUES(`vipLevel`)

8.大小写

mysql配置大小写敏感的选项
    lower_case_file_system(http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lower_case_file_system)
        mysql定位数据目录时需要用这个选项，只读变量，不能在mysql运行期修改
        OFF 文件名大小写敏感
        ON 文件名大小写不敏感
    lower_case_table_names
        0 表名按照指定的名字存储，比较时大小写敏感(在wins等大小写不敏感的系统上做这个设置没用；wins默认1，mac默认2)
        1 表名按照小写名存储，比较时忽略大小写
        2 表名按照指定的名字存储，但用小写名做比较
        用innodb表时，在所有平台都应配置为1
    列名和索引名在任何平台都是大小写不敏感的(http://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html)，触发器是大小写敏感(who care)
    http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

对于数据的大小写，utf8_general_ci类型的列是大小写不敏感的，但是插入'a'和'A'到唯一索引的列会报错。_bin是大小写不敏感的。

下述例子utf8_test.name使用了utf8_test的字符集和collation，utf8_test指定了字符集utf8没有指定collation，那么就默认utf8的collation utf8_general_ci

_ci，case insensitive, 大小写不敏感，指的是where theColumn = 'x'的时候可以把'X'查出来，但是_ci带unique key的列是不能并存'x'和'X'的
mysql> show create table utf8_test\G
*************************** 1. row ***************************
       Table: utf8_test
Create Table: CREATE TABLE `utf8_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `k` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from utf8_test;
+----+------+
| id | name |
+----+------+
|  2 | a    |
+----+------+
1 row in set (0.00 sec)

mysql> insert into utf8_test(name) values ('A');       
ERROR 1062 (23000): Duplicate entry 'A' for key 'k'
mysql> select * from utf8_test where name = 'A';
+----+------+
| id | name |
+----+------+
|  2 | a    |
+----+------+
1 row in set (0.00 sec)

9.关于字符集，真是个麻烦（当然，麻烦、复杂这些词都是对不懂的人而言的）的话题，这两天（2015.7.29）被其他同事问倒了，所以稍微了解了一下，一会 单起一篇文章转载过来

先描述一下问题，user.userName是 character set utf8mb4 collate utf8mb4_bin ，然后当输入四字节字符(ios的emoji表情)做查询时报字符集混合的错误

SELECT nameCount, userCount from (SELECT COUNT(*) nameCount FROM user WHERE userName = "\xf0\x9f\x92\x97") t1, (SELECT COUNT(*) userCount FROM user WHERE accountId = 219197609 AND deleteTime = 0) t2;

这个SQL写很烂，不过为了不影响测试，我还是原样拿了他们的串来做测试。报错：

_mysql_exceptions.OperationalError: (1267, "Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")

改db/table/connection的字符集和collation，有时正确有时报错（找了篇文章看才发现这几项之间的关系），其实最科学的做法是：

SELECT nameCount, userCount from (SELECT COUNT(*) nameCount FROM user WHERE userName = _utf8mb4 "\xf0\x9f\x92\x97" COLLATE utf8mb4_bin) t1, (SELECT COUNT(*) userCount FROM user WHERE accountId = 219197609 AND deleteTime = 0) t2;

指定输入串的字符集和collation，这样不会被server/db/table/connection的字符集和collation影响（下面的跟主题无关，只是测试中用过的一些sql而已）

alter table user modify column userName varchar(32) character set utf8mb4 collate utf8mb4_bin; 
SET collation_connection = 'utf8_general_ci';
ALTER DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE user CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE user CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

10.今天（2015.8.20）GMT模拟程序遇到一个问题，清空数据表时如果用truncate会把AUTO_INCREMENT降为1，用delete则不会（所以逻辑中如果有要保证id绝对唯一，不光是表内数据唯一、跟历史数据相比也要唯一的需求，那么不要用truncate）

11.之前还没这么用过呢(2015.11.9)

mysql> select * from a;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
|    2 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from b;
+------+------+
| c1   | c3   |
+------+------+
|    1 |    3 |
|    3 |    9 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from a where not exists (select * from b where b.c1 = a.c1);        
+------+------+
| c1   | c2   |
+------+------+
|    2 |    4 |
+------+------+
1 row in set (0.00 sec)