一 索引：
索引（在Mysql中也叫做键<key>），是存储引擎用于快速找到记录的一种数据结构，其先在索引中找到对应值，然后根据匹配索引记录找到对应的数据行
如果对多列进行索引（组合索引），列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找
索引对查询的速度有重要的影响，应该是对数据库查询性能优化最有效的手段了，它能轻易的将查询性能提高几个数量级，”最优“的索引有时比一个”好的“索引性能要好两个数量级。创建一个真正”最优“的索引经常要重写查询。假设数据库中一个表有10^6条记录，DBMS的页面大小为4k，并存储100条记录；如果没有索引，查询将扫描全表，最坏的情况下，如果所有的数据也都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms，则总共需要100s；如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30m
二 索引的类型：
索引是在存储引擎中实现的，而不是服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型
2.1普通索引（由关键字KEY或INDEX定义的索引）：
最基本的索引，没有任何限制，唯一任务是加快对数据的访问速度，因此，应该只为那些最常出现在查询（WHERE column=...）或排序条件（ORDER BY column）中的数据列创建索引，只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个Integer类型的数据列）来创建索引
创建索引：
CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length
修改表结构：
ALTER mytable ADD INDEX [indexName] ON (username(length))
创建表的时候直接指定：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)));
删除索引的语法：
DROP INDEX [indexName] ON mytable;
2.11唯一索引：
与普通索引类似，不同就是：普通索引允许被索引的数据列包含重复的值，而唯一索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一
创建索引：
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构：
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
创建表的时候直接指定：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
2.111主键索引：
是一种特殊的唯一索引，不允许有空值，一般在建表的同时创建主键索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
与此类似的，外键索引：如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件
2.2组合索引：
为表添加多个字段，并将其中某几列的字段作为一个索引：
建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：usernname,city,age usernname,city usernname，为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
2.B-Tree：
B是balance，一般用于数据库的索引，使用B-Tree结构可以显著的减少定位记录时所经历的中间过程
3.Hash索引：
4.R-Tree：
MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY
5.Full-Text：
全文索引是MyISAM的一个特殊索引类型，它查找的是文本中的关键词主要用于全文检索
三 索引的建立时机：
一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<、<=、=、>、>=，BETWEEN、IN，以及某些时候的LIKE才会使用索引
此时需要对city和age建立索引，由于mytable表的username也出现在JOIN语句中，也有对它建立索引的必要
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'
刚才提到只有某些LIKE才会需要建立索引，因为在通配符%和_开头作为查询时，MySQL不会使用索引，lLIKE操作一般会在全文索引中才会用到（InnoDB数据表不支持全文索引）
例如下句会使用索引： SELECT * FROM mytable WHERE username like'admin%'
而下句就不会使用： SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别
四 高效的索引的策略：
4.1 独立的列：
独立的列是指索引列不能是表达式的一部分，也不是是函数的参数
4.2 多列索引：
一个多列索引与多个列索引MYSQL在解析执行上是不一样的，如果在explain中看到有索引合并，应该好好检查一下查询的表和结构是不是已经最优
4.3 前缀索引和索引选择性：
索引的选择性越高则查询效率越高，因为选择性高的索引可以让MYSQL在查找时过滤掉更多的行。
唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的完整长度。
决窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。
前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MYSQL无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描
4.4 索引列排序：
MySQL查询只使用一个索引，因此如果WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引的，因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果最好给这些列创建复合索引
如和选择索引列顺序？
对于如何选择索引的顺序有一个经验法则：将选择性最高的列放在索引最前列。
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是最好的。然后，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。
这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
使用经验法则要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能（当使用前缀索引时，在某些条件值的基数比正常值高的时候）。
4.5 聚簇索引：
4.6 覆盖索引：
4.7 索引进行排序：
如果explain出来的type列的值为“index”，则说明MYSQL使用了索引扫描来做排序。利用索引进行排序操作是非常快的，因为只需要从一条索引记录移动到紧接着的下一条记录。
但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就回表查询一次对应的行，这基本上都是随机IO，因此按索引顺序读取的速度通常要比顺序地全表扫描慢，尤其是在IO密集型的工作负载时。
而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort文件排序
mysql> explain select actor_id from actor order by actor_id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 Extra: Using index 1 row in set (0.00 sec) mysql> explain select actor_id from actor order by password \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using filesort 1 row in set (0.00 sec) mysql> explain select actor_id from actor order by name \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: index possible_keys: NULL key: name key_len: 18 ref: NULL rows: 4 Extra: Using index 1 row in set (0.00 sec)
对于filesort，MySQL有两种排序算法
一次扫描算法(single pass)：
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。
在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。
当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”
4.8 索引加锁：
索引对InnoDB非常重要，因为它可以让查询锁更少的元组，这点十分重要，因为在MySQL5.0中，InnoDB直到事务提交时才会解锁
有两个方面的原因：首先，即使InnoDB行级锁的开销非常高效，内存开销也比较小，但不管怎么样，还是存在开销，其次，对不需要的元组的加锁，会增加锁的开销，降低并发性
InnoDB仅对需要访问的元组加锁，而索引能减少InnoDB访问的元组数，但是，只有在存储引擎层过滤掉哪些不需要的数据才能达到这种目的
一旦索引不允许InnoDB那样做，MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了：InnoDB已经锁住了那些元组，服务器无法加锁了
create table actor(
actor_id int unsigned NOT NULL AUTO_INCREMENT,
name varchar(16) NOT NULL DEFAULT '',
password varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY(actor_id),
KEY (name)
) ENGINE=InnoDB
insert into actor(name,password) values('cat01','1234567');
insert into actor(name,password) values('cat02','1234567');
insert into actor(name,password) values('ddddd','1234567');
insert into actor(name,password) values('aaaaa','1234567');
SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id < 4
AND actor_id <> 1 FOR UPDATE;
该查询仅仅返回2---3的数据，实际已经对1---3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询（而没有进行过滤操作，WHERE中第二个条件已经无法使用索引了）：
mysql> EXPLAIN SELECT actor_id FROM test.actor
-> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql>
SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;
如上所示，当使用索引时，InnoDB会锁住它不需要的元组。更糟糕的是，如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要
索引的缺点：
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE，因为在更新表时，MySQL不仅要保存数据，还要保存索引文件
建立索引会占用磁盘空间的索引文件，一般这个问题不大严重，但是如果在一个大表上创建了多种组合索引，索引文件会膨胀的很快
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就要花时间研究建立最优秀的索引，或优化查询语句，因此应该只为最经常查询和最经常排序的数据列建立索引，注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的效果
从理论上讲，完全可以为数据库表的每个字段分别建立一个索引，但MySQL把同一个数据表里的索引总数限制为16个
索引的优化：
数据类型：
MySQL支持很多数据类型，选择合适的数据类型存储数据度性能有很大的影响
越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快
简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂，在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址
尽量避免NULL：应该指定列为NOT NULL，除非想存储NULL，在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂，应该用0、一个特殊的值或者一个空串代替空值（只要列中包含有NULL值都不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此索引就是无效的）
使用短索引：对字符串进行索引，如果可能应该指定一个前缀长度
短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了
不要在列上进行运算：会导致索引失效而进行全表扫描
不要使用NOT IN，可以使用NOT EXISTS或者（外连接+判断为空）来代替；对于<>，用其他相同功能的操作运算代替，如z<>0 改为 z<0 or z>
标识符：
选择合适的标识符是非常重要的，选择时不仅应该存储类型，而且应该考虑MySQL是怎样进行运算和比较的，一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型
Integer：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT
String：尽量避免使用字符串作为标识符，他们消耗更大的空间，处理起来也很慢，而且，通常来说，String都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘、聚簇索引分裂（对于使用聚簇索引的存储引擎）
