应用场景
非常小的表：不建议使用索引，简单的全表扫描更高效
中到大型表：使用索引
特大型表：定位单条记录速度很慢且开销很大，建议使用分区、分表代替
类型
一、B-Tree 索引（按照顺序存储索引列，适合查找范围数据）
1. 对以下查询有效 （索引列（firstname，lastname，age））
加索引的列全部匹配 （例： 匹配firstname，lastname，age）
只匹配索引的第一列或前几列 （例：匹配firstname，lastname）
只匹配索引某一列的前一部分（例：匹配firstname的前两个字母）
匹配某一列并范围匹配另外一列 （例：匹配 firstname为Allen，lastname以字母K开头）
查询中的 ORDER BY、GROUP BY 操作，也能够打到 B-Tree索引
2. 对以下查询无效 （索引列（firstname，lastname，age））
不是按照索引最左边开始查询 （例：匹配lastname为Iverson，firstname为Allen 或者 单独查找 age）
不能跳过索引中的列 （例：匹配firstname，age）
查询中有某个列的范围查询，则其右边所有列都无法使用索引优化 （例：firstname='Allen' AND lastname LIKE 'I%' AND age = 26）
二、哈希索引（只有Memory引擎支持，索引列：firstname，lastname，age）
索引速度非常快
哈希索引用户无法order by 排序
哈希索引不支持部分索引列 （例：firstname='Allen' ）
哈希索引只支持等值比较查询（例：=、in( )、<=>）,不支持范围查询（例：age>19）
哈希索引不适用于值比较单一的列（例：性别列）
哈希索引存储的是列的哈希值
当需索引的列值很长时（例：url列），建议使用HASH索引，如果使用的B-Tree索引，存储的内容就会很大，而HASH索引是存储哈希值
哈希冲突：不同的索引列值，却有相同的哈希值
三、空间数据索引（MyISAM表支持空间索引）
用作地理数据存储，此类索引无须前缀查询
四、全文索引（只能在MyISAM表中创建）
全文索引查找的是文本中的关键词，而不是直接比较索引中的值，此类索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配，全文索引适用于MATCH AGAINST操作
索引策略
一、有时候需要索引很长的字符列，这会让索引变得大且慢。可以使用模拟哈希索引和使用前缀索引两种策略
1、模拟哈希索引
思路：还是使用B-Tree进行查找，但是使用哈希值而不是键本身进行索引查找。需要做的就是在查询的WHERE子句中手动指定使用哈希函数，伪哈希索引的缺陷是需要在插入和更新时，维护哈希列的哈希值。（不建议使用SHA1()和MD5()作为哈希函数，因为这两个函数计算出来的哈希值非常长）
例： 普通B-Tree索引
SELECT id FROM url WHERE url ='http://www.baidu.com';
B-Tree + HASH 索引（模拟哈希索引）
① 删除原来url列上的索引；
② 新增一个被 B-Tree 索引的列 url_crc ，使用 CRC32将url转化成哈希值；
③ 查询方式：
SELECT id FROM url WHERE url_crc=CRC32('http://www.baidu.com') AND url ='http://www.baidu.com';（注：一定需要匹配 url ='http://www.baidu.com'，因为可能出现哈希冲突，不同的索引列值有相同的哈希值，具体见《高性能MySQL》150）
④ 利用触发器，在 insert 和 update 操作时，将url转化成哈希值
2、使用前缀索引（B-Tree索引）
基数：不重复的索引值
索引的选择性：不重复的索引值和数据表的记录总数的比值
索引的选择性越高，查询效率越高。为了提高查询效率，需要选择合适的前缀长度，以保证较高的选择性，同时又不能太长（以便节约空间）。
例（ 见《高性能MySQL》城市例子 154 155 156，为城市表的city字段建立索引）：
一、查找出现频次前10的城市
二、查找3个前缀字母出现频次
通过对比数据可知，每个前缀都比原来城市出现的次数更多，因此唯一前缀比唯一城市要少得多。我们需要增加前缀长度，直到前缀出现的次数接近完整列出现的次数。当前缀长度增加为7时，前缀出现的次数接近于完整列出现的次数，所以设置前缀长度为7，索引效率是最高的。 那我们是怎么知道前缀长度为7位时，索引效率最高呢，当然不可能一个一个地去试，要想得到最合适、效率最高的前缀长度，只需要计算不同长度的选择性，最接近完整列选择性的长度，一般就是最合适的长度。如下：
一、获取完整列的选择性
二、获取不同前缀长度的选择性
前缀索引页有它的缺点，MySQL无法使用前缀索引做 ORDER BY和 GROUP BY，也无法使用前缀索引做覆盖扫描。
二、选择合适的索引列顺序（B-Tree索引）
需执行的语句：
SELECT * FROM payment WHERE staff_id =2AND customer_id =584;
需设置索引：staffid 、customerid
考虑： 多列索引（staffid 、customerid）的顺序
根据全局基数和选择性 （见《高性能MySQL》城市例子 159 160 161）决定多列索引的顺序
查找出两个字段的选择性和全局基数
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,（选择性）
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,（选择性）
COUNT(*)FROM payment\G
哪个字段选择性更高，则将此字段作为索引的第一列 注：此方法也有它的缺陷，当某些条件值的基数比正常值高的时候，问题就来了。如在某些应用程序中，对于没有登录的用户，都将其用户名记录为 guset，guset就成为了一个特殊用户，一旦查询涉及这个用户，那么和其他正常用户的查询就不一样。
网友评论