索引性能验证
1、无索引列的查询
在where条件中查询没有添加索引的列，性能会比较差。我们可以先在sqlyog中打开表t_user的数据，然后复制一个名字出来进行查询。
/*无索引列的查询，索引不会命中*/SELECT * FROM t_user WHERE NAME = 'ZYWMUoLMAu';
上面的SQL执行时间大约在0.4秒左右，耗时较长。
2、主键列查询
主键默认是唯一索引，我们可以尝试进行查找一条记录。
/*主键列查询，索引会命中*/SELECT * FROM t_user WHERE id = 1;
上面的SQL执行时间大约在1毫秒左右，这说明主键上包含索引，性能提升非常大。
3、在NAME列上增加索引
在NAME上增加BTREE索引。
/*添加NAME列上的索引*/ALTER TABLE `t_user` ADD INDEX index_name ( `name` ) ;
随着数据量越大，索引的创建时间会越长。添加完索引之后，再进行SQL的查询。
/*在NAME列上有索引，索引会命中*/SELECT * FROM t_user WHERE NAME = 'ZYWMUoLMAu';
上面的SQL执行时间大约在10毫秒左右，这说明NAME列上索引已经生效。
4、范围查询的索引效果
我们在age列上进行范围查询，测试性能。
/*在age列上没有索引，索引不会命中*/SELECT * FROM t_user WHERE age > 100 AND age <200;
上面的SQL执行时间大约在0.4秒左右，性能较差。
在age列上添加BTREE索引,再进行测试。
/*在age列上有索引，索引会命中*/ALTER TABLE `t_user` ADD INDEX index_age ( `age` ) ;
上面的SQL执行时间大约在10毫秒左右，这说明age列上索引对于范围查询来说已经生效。
5、排序的索引效果
我们在address列上进行排序查询，测试性能，限制查询数据量为100。
/*在address列上没有索引，索引不会命中*/SELECT * FROM t_user ORDER BY address DESC LIMIT 100;
上面的SQL执行时间大约在1秒左右，性能较差。
在age列上添加BTREE索引,再进行测试。
/*在address列上有索引，索引会命中*/ALTER TABLE `t_user` ADD INDEX index_address ( `address` ) ;
上面的SQL执行时间大约在30毫秒左右，这说明address列上索引对于排序查询来说已经生效。
高性能的索引策略
1、独立的列：
如果查询中的列不是独立的，则Mysql就不会使用索引。独立的含义是指索引列不能是表达式的一部分，也不能是函数的参数。
/*使用了表达式，不是独立的列，不会命中索引*/SELECT * FROM t_user WHERE id + 0 = 1;
上面的SQL等价于id = 1但是Mysql的索引就会因此失效，执行时间大约在0.4秒左右。
2、like查询不能以%开头
如果查询中包含like查询以%为开头，则索引会失效。
/*LIKE查询以%为开头，不会命中索引*/SELECT * FROM t_user WHERE NAME LIKE '%ZYWMU%';
上面的SQL语句执行时间在0.5秒左右，然后我们将开头的%去掉，进行测试。
/*LIKE查询不以%为开头，会命中索引*/SELECT * FROM t_user WHERE NAME LIKE 'ZYWMU%';
当like查询不以%为开头之后，查询时间在0.02秒，证明索引已经命中，性能得到非常大的提升。
3.列类型是字符串，一定要在条件中将数据使用引号引用起来
尝试以下SQL，在name列有索引的情况下
/*在name列有索引，字符串未用引号引用，不会命中索引*/SELECT * FROM t_user WHERE NAME = 123;
未查找到数据，执行时间0.6秒，如果我们将数据使用引号引用
/*在name列有索引，字符串用引号引用，会命中索引*/SELECT * FROM t_user WHERE NAME = '123';
未查找到数据，执行时间0.01秒，证明索引已经命中。
4.最左匹配原则
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，删除所有索引，对列name、列address和列phone建一个联合索引
ALTER TABLE `t_user` ADD INDEX index_combile ( `name`, `address`,`phone`);
联合索引 index_combile实际建立了(name)、(name,address)、(name,address,phone)三个索引。所以下面的三个SQL语句都可以命中索引。
SELECT * FROM t_user WHERE address = 'KPSTOrpBMf' AND phOne= 'pjZvejKYKF' AND NAME = 'myGKHeCwAm'SELECT * FROM t_user WHERE NAME = 'myGKHeCwAm' AND address = 'KPSTOrpBMf' SELECT * FROM t_user WHERE NAME = 'myGKHeCwAm';
上面三个查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引
(name,address,phone)(name,address)(name)(phone,address,name)(phone,address)(phone)SELECT * FROM t_user WHERE address = 'KPSTOrpBMf' AND phOne= 'pjZvejKYKF' ->SELECT * FROM t_user WHERE phOne= 'pjZvejKYKF' and address = 'KPSTOrpBMf'
进行数据匹配。
索引的字段可以是任意顺序的，如：
/*无索引列的查询，索引不会命中*/SELECT * FROM t_user WHERE NAME = 'ZYWMUoLMAu';
Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。
联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。
ALTER TABLE `t_user` ADD INDEX index_combile ( `name`, `address`,`phone`);
扩展
1.为什么innodb表必须建主键？
建立主键之后，数据就可以挂在主键的索引下方叶子节点，这样查询起来会很快。如果不建立主键，mysql会选择一列数据不重复的列作为索引列。如果没有找到，那么就会建立隐藏列rowid，这样无形中增加了开销。
2.为什么推荐使用自增主键，而不是字符串。
使用索引时存在大量比对工作，整型比对比字符串比对快。另外存储空间小。
3.为什么不建议mysql单表数据超过2000W？
id为bigint 8字节，指针占6个字节，单个page16K，16 * 1024/14 = 1170
1个page页可以存放数据也可以存放指针 数据1K计算，可以存放16条
所以3层的b+树，第一层放指针，第二层也放指针，第三层放数据 总计 16 * 1170 * 1170 约是2200W，所以如果数据再多，就需要用到4层B+数，这样的查询效率会进一步降低。
使用explain语句查看索引的命中情况
我们可以使用explain语句查看SQL语句的执行计划，判断索引是否命中。
explain SELECT * FROM t_user WHERE address = 'KPSTOrpBMf' AND phOne= 'pjZvejKYKF' AND NAME = 'myGKHeCwAm';
显示结果如下：
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEt_user(NULL)ALL(NULL)(NULL)(NULL)(NULL)9960861.00Using whereEXPLAIN列的解释：
table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。
type
以下排序从上到下，性能由坏到好。
a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
b.index：Full Index Scan，index与ALL区别为index类型只遍历索引树
c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、等的查询
d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
g.NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引
所以由type可以进行分析，如果是ref、eq_ref那么索引命中且性能较好。
【文章转自中东服务器 http://www.558idc.com/dibai.html欢迎留下您的宝贵建议】