一.索引的好处
a.索引大大减少了存储引擎需要扫描的数据量,所以扫描页数(一般一页是16k)就少.IO操作就更快.
b.索引可以帮助我们进行排序以避免使用临时表的IO消耗
c.索引可以把随机I/O变成顺序I/O
二.索引坏处
a.索引会增加写操作的成本
b.太多的索引会增加查询优化器的选择时间
c.每个额外的索引都要占用额外的磁盘空间
三.B-Tree索引
(1)
主键索引的叶子节点存的整行数据.在InnoDB里,主键索引页被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值.在InnoDB,非主键索引页被称为二级索引.
(2)
全值匹配
全值匹配指的是和索引中的所有列进行匹配,例如前面提到的索引可用于查找姓名为Cuba Allen ,出生与1960-01-01的人.
匹配最左前缀
前面提到的索引可用于查找所有姓为Allen的人,即只使用索引的第一列.如果不是按照索引的最左列开始查找,则无法使用索引.
匹配列前缀
也可以只匹配某一列的值的开头部分.例如前面提到的索引可用于查找所有以J开头的姓的人.这里也只使用了索引的第一列.
匹配范围值
例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人.这里也只使用了索引的第一列.
精确匹配某一列并范围匹配另外一列
前面提到的索引也可用于查找所有姓为Allen,并且名字是字符K开头(比如Kim,Karl等)的人.即第一列last_name全匹配,第二列first_name范围匹配.
只访问索引的查询
B-Tree通常可以支持"只访问索引的查询",即查询只需要访问索引,而无须访问数据行.(覆盖索引)
四.索引特性
(1).如果不是按照索引最左列开始查找,则无法使用索引.
(2).如果查询中有某个列的范围查询,则其右边的所有列都无法使用索引
(3）应尽量避免在 where 子句中使用not in ,!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
(4)查询频繁的列，在where，group by，order by，on从句中出现的列
(5)适用于where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
(6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤
(7).对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引; 如果没有索引，则应该考虑增加索引。
(8) 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
(9) 下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
对于使用 like 的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用 .若要提高效率，可以考虑全文检索。
(10) 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id
应改为:
select id from t where num=100*2
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
(11) 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
(12).虽然在object_name字段上创建了索引,但是由于使用了upper()函数,导致无法利用该索引.
->select * from t1 where upper(object_name)=‘EMP’;
解决方法: create index idx_object_name_upper on t1(upper(object_name));
(13)当MYSQL认为符合条件的记录在30%以上，它就不会再使用索引，因为mysql认为走索引的代价比不用索引代价大，所以优化器选择了自己认为代价最小的方式。事实也的确如此
(14)主键是B+树,当数据从中间插入,如果当其中的数据页满了,就需要申请一个新的数据页,然后挪动部分数据过去,也就是页分裂.当相邻的两个页删除的了数据,利用率很低之后,会做数据页合并.不管是分裂还是合并,都会影响性能,对存储占用也变得大了.所以一般会用NOT NULL PRIMARY KEY AUTO_INCREMENT.
(15)查询中的列不是独立的列,无法使用索引,如:
mysql> select actor_id from sakila.actor where actor_id +1 =5
五.索引建议
(1).varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
(2).业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
(3).区分度使用 count(distinct left(列名, 索引长度))/count(*)的区分度 来确定。
(4).Hash索引优化查询
explain select * from where title_md5(‘EGG IGBY’) AND title=‘EGG IGBY’\G (hash查询并使用过滤,防止hash冲突)
*只能处理键值的全值匹配查找.
*所使用的Hash函数决定着索引键的大小.
(5)索引的列的基数越大,索引的效果越好.
(6).利用索引优化锁 *索引可以减少锁定的行数. *索引可以加快处理速度,同时也加快锁的释放.
(7)修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多.所花的时间越多.
(8).如果有特殊目的需要的索引,比如每月例行维护程序,那么读者可以添加索引,运行程序,然后删除索引,下次需要时再如此重复一遍.
(9)Order by中的字段全部在关联表中的第一张表中.
(10).使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应 该这样做。例如，如果有一个 CHAR(200)列，如果在前 10 个或 20 个字符内，多数值是惟一 的，那么就不要对整个列进行索引。
(11).主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名
(12).使用子查询可以一次性地完成很 多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起 来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)替代。 是因为 MySQL 不需要在内存中创建临时表来完成这 个逻辑上的需要两个步骤的查询工作。
(13).很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
(14).应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
(15).尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。
(16)如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例:where a=? and b=? order by c; 索引:a_b_c
反例:索引中有范围查找，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
(17).在join表的时候使用相当类型的列，并将其索引
如果在程序中有很多JOIN查询，应该保证两个表中join的字段时被建立过索引的。这样MySQL颞部会启动优化JOIN的SQL语句的机制。注意：这些被用来JOIN的字段，应该是相同类型的。例如：如果要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
例如：
SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = “user_id”
两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。
(18).如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL 禁止排序
(19)在B-Tree基础上创建一个伪哈希索引.根据CRC哈希值做快速的证书比较就能找到索引条目,然后一一比较返回对应的行.
如:SELETE id FROM url WHERE url="http://www.mysql.com" AND url_crc=CRC32("http://www.mysql.com");
记住不要使用SHA1()和MD5()作为哈希函数.因为转换出来哈希值非常长.或者只截取前面部分.
(20)长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
六.覆盖索引
覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖,避免回表。
优点:
*可以优化缓存,减少磁盘IO操作.
*可以减少随机IO,变随机IO操作变为顺序IO操作.
*可以避免对Innodb主键索引的二次查询.
*可以避免MyISAM表进行系统调用. 无法使用覆盖索引的情况:
*存储引擎不支持覆盖索引.
*查询中使用了太多的列.
*使用了双%好的like查询.
select * from T where k between 3 and 5; 需要回表
select ID from T where k between 3 and 5; 这时只需要查ID的值,而ID 的值已经在K索引树上,所以不需要回表.也就是说,这个查询里面,索引K已经"覆盖"我们的查询需求,我们称为覆盖索引.
七.联合索引
如何选择索引列的顺序 :
*经常会被使用到的列优先放左边.
*选择性高的列优先放左边.
*宽度小的列优先放左边.
(1)mysql>alter table test add key id_a_b(a,b) ;
对于联合索引当条件为 a=1 and b=1 则使用索引 ，当a=1 时也使用索引 当单独使用b=1时则不使用索引。
(2)在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
八.索引操作
1.创建索引
索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。以下命令语句分别提示了如何创建主键索引（PRIMARY KEY），联合索引（UNIQUE）和普通索引（INDEX）的方法。
mysql>ALTER TABLE `table_name` ADD INDEX `index_name` (column list);
mysql>ALTER TABLE `table_name` ADD UNIQUE `index_name` (column list);
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY `index_name` (column list);
mysql>CREATE INDEX `index_name` ON `table_name` (column_list);
mysql>CREATE UNIQUE INDEX `index_name` ON `table_name` (column_list);
mysql>ALTER TABLE `article` ADD INDEX (`id`,`order_id`);给article表增加id索引，order_id索引
mysql>ALTER TABLE `article` ADD INDEX `id`;//给article表增加id索引
2.重建索引
重建索引在常规的数据库维护操作中经常使用。在数据库运行了较长时间后，索引都有损坏的可能，这时就需要重建。对数据重建索引可以起到提高检索效率。
mysql> REPAIR TABLE `table_name` QUICK;
3.查询数据库索引
mysql> SHOW INDEX FROM `table_name`;
4.删除索引
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：
mysql>DROP index `index_name` ON `table_name` (column list);
mysql>ALTER TABLE `table_name` DROP INDEX `index_name` (column list);
mysql>ALTER TABLE `table_name` DROP UNIQUE `index_name` (column list);
mysql>ALTER TABLE `table_name` DROP PRIMARY KEY `index_name` (column list);
在前面的三条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此也可不指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
5.唯一索引
->ALTER TABLE department
->ADD UNIQUE dept_name_idx (name);
6.多列索引 (注意查询的时候,字段顺序要跟索引一致)
->ALTER TABLE employee
->ADD INDEX emp_names_idx (lname,fname);
7.强制使用索引
mysql>SELECT * FROM TABLE1 FORCE INDEX (索引名或PRIMARY) ;
8.analyze table t;
如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以analyze table t 命令，可以用来重新统计索引信息。如果只是索引统计不准确，通过 analyze 命令可以解决很多问题