聚集索引、非聚集索引、聚集索引组织表、堆组织表、Mysql/PostgreSQL对比、联合主键/自增长、InnoDB/MyISAM（引擎方面另开一篇）
参考了多篇文章，分别记录，如下。
下面是第一篇的总结 http://www.jb51.net/article/76007.htm：
在MySQL中，InnoDB引擎表是(聚集)索引组织表(clustered index organize table)，而MyISAM引擎表则是堆组织表(heap organize table)。
聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序；
而非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
IOT表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。
而HOT表数据写入的顺序是按写入时间顺序存储的。
IOT表相比HOT表的优势是：
范围查询效率更高;
数据频繁更新(聚集索引本身不更新)时，更不容易产生碎片;
特别适合有一小部分热点数据频繁读写的场景;
通过主键访问数据时快速可达;
IOT表的不足则有：
数据变化如果是离散为主的话，那么效率会比HOT表差;
HOT表的不足有：
索引回表读开销很大;
大部分数据读取时随机的，无法保证被顺序读取，开销大;
每张InnoDB表只能创建一个聚集索引，聚集索引可以由一列或多列组成。
InnoDB是聚集索引组织表，它的聚集索引选择规则是这样的：
首先选择显式定义的主键索引做为聚集索引;
如果没有，则选择第一个不允许NULL的唯一索引;
还是没有的话，就采用InnoDB引擎内置的ROWID作为聚集索引;
可以看到，在这个索引结构的叶子节点中，节点key值是主键的值，而节点的value则存储其余列数据，以及额外的ROWID、rollback pointer、trx id等信息。
结合这个图，以及上面所述，我们可以知道：在InnoDB表中，其聚集索引相当于整张表，而整张表也是聚集索引。
主键必然是聚集索引，而聚集索引则未必是主键。
MyISAM是堆组织表，它没有聚集索引的概念。
以下是这一篇的总结 http://www.jb51.net/article/76702.htm：
作为免费又高效的数据库，mysql基本是首选。良好的安全连接，自带查询解析、sql语句优化，使用读写锁（细化到行）、事物隔离和多版本并发控制提高并发，完备的事务日志记录，强大的存储引擎提供高效查询（表记录可达百万级），如果是InnoDB，还可在崩溃后进行完整的恢复，优点非常多。 首先，mysql的查询流程大致是：mysql客户端通过协议与mysql服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，有一系列预处理，比如检查语句是否写正确了，然后是查询优化（比如是否使用索引扫描，如果是一个不可能的条件，则提前终止），生成查询计划，然后查询引擎启动，开始执行查询，从底层存储引擎调用API获取数据，最后返回给客户端。怎么存数据、怎么取数据，都与存储引擎有关。然后，mysql默认使用的BTREE索引，并且一个大方向是，无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。 mysql通过存储引擎取数据，自然跟存储引擎有很大关系，不同的存储引擎索引也不一样，如MyISAM的全文索引，即便索引叫一个名字内部组织方式也不尽相同，最常用的当然就是InnoDB了（还有完全兼容mysql的MariaDB，它的默引擎是XtraDB，跟InnoDB很像），这里写的是InnoDB引擎。而索引的实现也跟存储引擎，按照实现方式分，InnoDB的索引目前只有两种：BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引，InnoDB的BTREE索引，实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引。 结构中，每一层节点均从左往右从小到大排列，key1 < key2 < ... < keyN，对于小于key1或在[key1，key2)或其他的值的节点，在进入叶子节点查找，是一个范围分布，同时，同一层节点之间可直接访问，因为他们之间有指针指向联系（MyISAM的BTREE索引没有）。每次搜索是一个区间搜索，有的话就找到了，没有的话就是空。 对于InnoDB，它指向的就是表数据，因为InnoDB的表数据本身就是索引文件，这是与MyISAM索引的显著区别，MyISAM的索引指向的是表数据的地址（val指向的是类似于0x7DFF..之类）。 InnoDB的索引节点val值直接指向表数据，即它的叶子节点就是表数据，它们连在一起，表记录行没有再单独放在其他地方，叶子节点（数据）之间可访问。
一些注意点：
如果非自增或不是整数索引，如非自增整数、类似MD5的字符串，以他们作为索引值时，因为待插入的下一条数据的值不一定比上一条大，甚至比当前页所有值都小，需要跑到前几页去比较而找到合适位置，InnoDB无法简单的把新行插入到上一行后面，而找到并插入索引后，可能导致该页达到分裂因子阀值，需要页分裂，进一步导致后面所有的索引页的分裂和排序，数据量小也许没什么问题，数据量大的话可能会浪费大量时间，产生许多碎片。 主键总是唯一且非空，InnoDB自动对它建立了索引（primary key），对于非主键字段上建立的索引，又称辅助索引，索引排列也是顺序排列，只是它还附带一个本条记录的主键值的数据域，不是指向本数据行的指针。 在使用辅助索引查找时，先找到对应这一列的索引值，再根据索引节点上的另一个数据域---主键值，来查找该行记录，即每次查找实际经过查找了两次。 额外的数据域存储主键值的好处是，当页分裂发生时，无需修改数据域的值，因为即使页分裂，该行的主键值是不变的，而地址就变了。
创建索引的语法：
mysql> create index user_last_name_index on user(last_name);
索引类型的说明：
唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引）
主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引）
聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引
非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引
非联合索引，分析如下几种情况：
首先索引情况是：
mysql> show index from tx \G *************************** 1. row *************************** Table: tx Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: tx Non_unique: 1 Key_name: tx_num_index Seq_in_index: 1 Column_name: num Collation: A Cardinality: 9 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec) mysql> show index from user \G *************************** 1. row *************************** Table: user Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: user Non_unique: 1 Key_name: user_last_name_index Seq_in_index: 1 Column_name: last_name Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)
然后，explain sql语句的结果如下：
mysql> explain select * from tx where num = 3; +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | tx | ref | tx_num_index | tx_num_index | 9 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select num from tx where num = 3; +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | tx | ref | tx_num_index | tx_num_index | 9 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select id from tx where id = 3; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | tx | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from user where last_name = 'gates'; +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------------+ | 1 | SIMPLE | user | ref | user_last_name_index | user_last_name_index | 138 | const | 1 | Using where | +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------------+ 1 row in set (0.01 sec)
mysql> explain select last_name from user where last_name = 'gates';
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | user | ref | user_last_name_index | user_last_name_index | 138 | const | 1 | Using where; Using index |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
关于Using index和Using where，下面的解释比较清楚：
Extra:using index 也就意味着，现在直接访问索引就中足够获取到所需要的数据，不需要索引回表， using index也就是平常说的覆盖索引扫描。只访问必须访问的数据 ，在一般情况下，减少不必要的数据访问能够提升效率 Extra :using where 表示优化器需要通过索引回表查询 两种都存在：（我的理解）要输出的列都在索引里。不需要再回原表拿数据。using where 只是表示了一个lookup的条件，而不是行为。
Using where只是过滤元组，和是否读取数据文件或索引文件没有关系。 英文原文： Using index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index. Using where A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. 但MySQL手册在Using index处又说： If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups.
复合索引：
包含一列的索引称为单列索引，多列的称为复合索引，因为BTREE索引是顺序排列的，所以比较适合范围查询，
但是在复合索引中，还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。
再回来谈谈联合索引（复合索引）。先建表：
mysql> create table staffs( -> id int primary key auto_increment, -> name varchar(24) not null default '' comment '姓名', -> age int not null default 0 comment '年龄', -> pos varchar(20) not null default '' comment '职位', -> add_time timestamp not null default current_timestamp comment '入职时间' -> ) engine= InnoDB charset utf8 comment '员工记录表'; Query OK, 0 rows affected (0.07 sec) mysql> select * from staffs; Empty set (0.00 sec) mysql> alter table staffs add index idx_nap(name, age, pos); Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0
注意以上有一种新的加索引的方式。用alter table 而不是之前的 create index.
mysql> insert into staffs(name, age, pos) values('bill', 20, 'ceo');
Query OK, 1 row affected (0.10 sec)
mysql> explain select * from staffs;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
注：无索引，无Extra
mysql> explain select * from staffs where name = 'bill' and age = 20 and pos = 'ceo';
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+
| 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 140 | const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)
注：有索引，Extra有where
mysql> explain select * from staffs where name = 'bill' and age = 20;
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
| 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 78 | const,const | 1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
注：用了索引中前2个字段
mysql> explain select * from staffs where name = 'bill' and pos = 'ceo';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 74 | const | 1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)
注：索引中1，3字段使得只用了索引中第1个字段
mysql> explain select * from staffs where age = 20; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
注：索引中第2个字段，用不了索引。索引必须从第1个字段开始。 mysql> explain select * from staffs where name = 'b%'; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 74 | const | 1 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
注：第1个字段 字符开头%，可以用到索引。 mysql> explain select * from staffs where name = '%b'; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 74 | const | 1 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
注：第1个字段，%开头字符串，可以用到索引（有的文章说不能用，亲测可以，可能是InnoDB的缘故）。
mysql> explain select * from staffs where name = '%b' and age = 20;
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
| 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 78 | const,const | 1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
注：第1个字段，%开头字符串，不影响使用2个以上字段作为索引。
mysql> explain select * from staffs where name > 'bill'; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | idx_nap | idx_nap | 74 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
注：>可以用到索引 mysql> explain select * from staffs where name >= 'bill'; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | idx_nap | idx_nap | 74 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.01 sec)
注：>=可以用到索引 mysql> explain select * from staffs where name > 'bill' and age = 20; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | idx_nap | idx_nap | 74 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.01 sec)
注：> 之后，第二个字段不能再被使用加到索引里 mysql> explain select * from staffs where name >= 'bill' and age = 20; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | idx_nap | idx_nap | 78 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
注：>= 之后，第二个字段可以被使用加到索引里
以下，是进一步的巩固。
mysql> explain select * from staffs where name >= 'bill' and age > 20; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | idx_nap | idx_nap | 78 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name >= 'bill' and age >= 20; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | idx_nap | idx_nap | 78 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name = 'bill' and age > 20; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | idx_nap | idx_nap | 78 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name >= 'bill' and age >= 20 and pos > 'ceo'; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | idx_nap | idx_nap | 140 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
下面，对于索引使用顺序换了一下。有的文章说就用不了索引了，但是InnoDB亲测，完全可以用到索引：
mysql> explain select * from staffs where age = 20 and name = 'bill' and pos = 'ceo'; +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 140 | const,const,const | 1 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec)
注：顺序相当于 b,a,c，可以用到索引 mysql> explain select * from staffs where age = 20 and pos = 'ceo' and name = 'bill'; +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 140 | const,const,const | 1 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec)
注：顺序相当于b,c,a，可以用到索引
mysql> explain select * from staffs where pos = 'ceo' and age = 20 and name = 'bill';
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+
| 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 140 | const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)
注：顺序相当于c,b,a，可以用到索引
一些联合索引对于查询是否会使用到的总结：
以上为常见的使用索引的方式，有这么些情况不能用或不能全用，有的就是上面情况的反例，以key(a, b, c)为例
1. 跳过列，where a = 1 and c = 3，最多用到索引列a；where b = 2 and c = 3，一个也用不到，必须从最左列开始
2. 前面是范围查询，where a = 1 and b > 2 and c = 3，最多用到 a, b两个索引列；
3. 顺序颠倒，where c = 3 and b = 2 and a = 1，一个也用不到；(注，上面提到了，测试结果不是这样)
4. 索引列上使用了表达式，如where substr(a, 1, 3) = 'hhh'，where a = a + 1，表达式是一大忌讳，再简单mysql也不认。有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查所有有订单记录的数据，再在程序中去筛选以'cp1001'开头的订单，而不是写sql过滤它；
5. 模糊匹配时，尽量写 where a like 'J%'，字符串放在左边，这样才可能用得到a列索引，甚至可能还用不到，当然这得看数据类型，最好测试一下。（注：InnoDB放前后都可以用到索引）
对于like，不会使用索引：
mysql> explain select * from staffs where id like '1%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
有order操作的情况：
mysql> explain select * from staffs where name = 'a' order by age; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 74 | const | 1 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
注：有order不影响where使用的索引 mysql> explain select * from staffs order by name; +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort | +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec)
注：直接order 非主键 不使用索引。（name换成age也不使用） mysql> explain select * from staffs order by id; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | staffs | index | NULL | PRIMARY | 4 | NULL | 2 | | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+ 1 row in set (0.01 sec)
注：order主键，使用索引
mysql> explain select * from staffs order by name,age,pos;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
注：联合索引都写全了，也不使用索引
覆盖索引，应该不会读数据文件了：
前面说到，mysql索引从结构上只有两类，BTREE与HASH，覆盖索引只是在查询时，要查询的列刚好与使用的索引列完全一致，mysql直接扫描索引，然后就可返回数据，
大大提高效率，因为不需再去原表查询、过滤，这种形式下的索引称作覆盖索引，比如key(a,b)，查询时select a,b from tab where a = 1 and b > 2，
本质原因：BTREE索引存储了原表数据。 聚簇索引也不是单独的索引，前面简要写到，BTREE索引会把数据放在索引中，即索引的叶子页中，包括主键，主键是跟表数据紧挨着放在一起的，
因为表数据只有一份，一列键值要跟每一行数据都紧挨在一起，所以一张表只有一个聚簇索引，对于mysql来说，就是主键列，它是默认的。
哈希索引：
简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，也是对索引列计算一个散列值（类似md5、sha1、crc32），然后对这个散列值以顺序（默认升序）排列，同时记录该散列值对应数据表中某行的指针，当然这只是简略模拟图 哈希索引的结构决定了它的特点： 1. hash索引只是hash值顺序排列，跟表数据没有关系，无法应用于order by； 2. hash索引是对它的所有列计算哈希值，因此在查询时，必须带上所有列，比如有(a, b)哈希索引，查询时必须 where a = 1 and b = 2，少任何一个不行； 3. hash索引只能用于比较查询 = 或 IN，其他范围查询无效，本质还是因不存储表数据； 4. 一旦出现碰撞，hash索引必须遍历所有的hash值，将地址所指向数据一一比较，直到找到所有符合条件的行。
上面explain结果里面key_len的解释：
前面提到通过explain的key_len字段，可大致估计出用了哪些列，索引列的长度跟索引列的数据类型直接相关，一般，我们说int是4字节，bigint8字节，char是1字节，考虑到建表时要指定字符集，比如utf8，还跟选的字符集有关（==!），在utf8下边，一个char是3字节。 比如对于 varchar(24)的索引，key_len就是72，但是貌似实际是74，加上了2. int的key_len是4,放在第二位，合起来是78.放在第一位就是4. 第三位var(20)，长度60，也是加上2得到62. 跟前面78合起来，就是140.
新的一篇 http://www.jb51.net/article/81779.htm
我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，
遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。
注意，like还是用不到索引的：
mysql> explain select * from staffs where id like 1;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
一次查询能用多个索引吗?
不能
in能够用到索引，not in不行：
mysql> explain select * from staffs where id in (2, 3); +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where id not in (2, 3); +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
另外，like x, like x%, like %x 都不能用到索引
mysql> explain select * from staffs where id like 'a'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where id like 'a%'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where id like '%a'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.01 sec)
!=是可以用到索引的：
mysql> explain select * from staffs where id != 3;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
用or的话，即使其中存在索引，也用不到：
mysql> explain select * from staffs where id = 1 or name='a';
+----+-------------+--------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | PRIMARY,idx_nap | NULL | NULL | NULL | 2 | Using where |
+----+-------------+--------+------+-----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
NULL带来的问题：
NULL 的问题
NULL会导致索引形同虚设，所以在设计表结构时应避免NULL 的存在（用其他方式表达你想表达的NULL，比如 -1？）
我试了，存在null还是会使用索引：
mysql> select * from staffs; +----+------+-----+-----+---------------------+ | id | name | age | pos | add_time | +----+------+-----+-----+---------------------+ | 1 | bill | 20 | ceo | 2016-10-21 20:02:26 | | 2 | mark | 21 | cfo | 2016-10-21 22:29:50 | | 3 | tom | 22 | | 2016-10-21 23:36:21 | +----+------+-----+-----+---------------------+ 3 rows in set (0.00 sec) mysql> explain select * from staffs where name='a' and age = 10 and pos = 'coo'; +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_nap | idx_nap | 140 | const,const,const | 1 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec)
下面是这篇 http://blog.csdn.net/xqy1522/article/details/6750252
堆表（heap table）数据插入时时存储位置是随机的，主要是数据库内部块的空闲情况决定，获取数据是按照命中率计算，全表扫表时不见得先插入的数据先查到。
索引表（iot）数据存储是把表按照索引的方式存储的，数据是有序的，数据的位置是预先定好的，与插入的顺序没有关系。
索引表的查询效率逼堆表高（相当于查询索引的效率），插入数据的速度比堆表慢。
Oracle支持堆表，也支持索引组织表
PostgreSQL只支持堆表，不支持索引组织表
Innodb只支持索引组织表
下面是这篇 http://www.infoq.com/cn/news/2013/12/mysql-vs-postgresql/
Postgresql 与 Mysql的对比：
使用快速的索引序列访问方法（ISAM），不支持ACID。经过早期快速的发展之后，MySQL开始支持更多的存储引擎，并通过InnoDB引擎实现了ACID。
MySQL还支持其他存储引擎，提供了临时表的功能（使用MEMORY存储引擎），
通过MyISAM引擎实现了高速读的数据库，此外还有其他的核心存储引擎与第三方引擎。
PostgreSQL是完全由社区驱动的开源项目，由全世界超过1000名贡献者所维护。它提供了单个完整功能的版本，
而不像MySQL那样提供了多个不同的社区版、商业版与企业版。 PostgreSQL基于版权：PostgreSQL基于自由的BSD/MIT许可，组织可以使用、复制、修改和重新分发代码，只需要提供一个版权声明即可。 Mysql基于版权：其核心代码基于GPL许可，对于那些不想使用GPL许可的开发者与厂商来说还有商业许可可供使用。
可靠性是PostgreSQL的最高优先级。它以坚如磐石的品质和良好的工程化而闻名，支持高事务、任务关键型应用。
数据一致性与完整性也是PostgreSQL的高优先级特性。
PostgreSQL是完全支持ACID特性的，它对于数据库访问提供了强大的安全性保证，充分利用了企业安全工具，如Kerberos与OpenSSL等。
应用对比：
MySQL与PostgreSQL都出现在一些高流量的Web站点上：
MySQL：Slashdot、Twitter、Facebook与Wikipedia
PostgreSQL：Yahoo使用了一个修改的PostgreSQL数据库来处理每天数以亿计的事件，还有Reddit和Disqus
MySQL在一个领域上要比PostgreSQL更进一步，那就是它的触角延伸到了嵌入式领域，这是通过libmysqld实现的。
PostgreSQL不支持嵌入式应用，依然坚守在传统的客户端/服务器架构上。
特性对比
MySQL通常被认为是针对网站与应用的快速数据库后端，能够进行快速的读取和大量的查询操作，不过在复杂特性与数据完整性检查方面不太尽如人意。
PostgreSQL是针对事务型企业应用的严肃、功能完善的数据库，支持强ACID特性和很多数据完整性检查。
他们二者都在某些任务上具有很快的速度，MySQL不同存储引擎的行为有较大差别。
MyISAM引擎是最快的，因为它只执行很少的数据完整性检查，适合于后端读操作较多的站点，不过对于包含敏感数据的读/写数据库来说就是个灾难了，
因为MyISAM表最终可能会损坏。
MySQL提供了修复MySQL表的工具，不过对于敏感数据来说，支持ACID特性的InnoDB则是个更好的选择。
与之相反，PostgreSQL则是个只有单一存储引擎的完全集成的数据库。
你可以通过调整postgresql.conf文件的参数来改进性能，也可以调整查询与事务。PostgreSQL文档对于性能调优提供了非常详尽的介绍。
MySQL与PostgreSQL都是高可配置的，并且可以针对不同的任务进行相应的优化。他们都支持通过扩展来添加额外的功能。
标准兼容性
PostgreSQL旨在实现SQL兼容性（当前标准是ANSI-SQL:2008）。 MySQL则兼容大部分SQL，不过还有自己的扩展，可以支持NoSQL特性，这在参考手册中都有介绍。 注：在MySQL5.6.2版本增加了通过Memcached协议直接访问原生Innodb API的功能。 参考：http://www.cnblogs.com/sunli/archive/2011/05/11/mysql-nosql.html 每种方式都有优缺点。兼容标准会让数据库管理员、数据库开发者与应用开发者更舒服一些，因为这意味着他们只需学习一套标准、一套特性和命令即可。
这会节省时间，提升效率，也不会被锁定在特定的厂商上。 支持使用非标准的自定义功能的人们认为这样可以快速采用新的特性，而不必等待标准进程完成。ANSI/ISO标准在不断演化，因此标准兼容性也是个变化的目标：
知名的关系型数据库Microsoft SQL Server、Oracle与IBM DB2也只是部分兼容于标准。
结论：
虽然有不同的历史、引擎与工具，不过并没有明确的参考能够表明这两个数据库哪一个能够适用于所有情况。
很多组织喜欢使用PostgreSQL，因为它的可靠性好，在保护数据方面很擅长，而且是个社区项目，不会陷入厂商的牢笼之中。
MySQL更加灵活，提供了更多选项来针对不同的任务进行裁剪。
很多时候，对于一个组织来说，对某个软件使用的熟练程度要比特性上的原因更重要。
联合主键、自增长 http://blog.sina.com.cn/s/blog_7a9af9b10101epem.html：
Mysql多列可以被设为联合主键。
如果需要对主键设成自增长，因为自增长是列维度的，所以单独设置；
如果自增长设的不是第一列，那么对于第一列增加时，当前列自动变成1；
对于InnoDB引擎，不允许联合主键中设置自增长；因为规定自增长的列，必须为key。
对于MyISAM引擎，运行，并且行为如上面描述。
关于数据库的引擎，比如Innodb，MyIsam的技术细节，对比等，另开一篇描述。
