MySQL数据库索引小记

背景：测试小姐姐说我们的项目有些功能速度慢，我回头一想 ：查询速度慢 加个索引嘛！于是跑到测试库中加索引，我加，我加，我加加加。。。唉！索引不匹配啊，咋回事，索引失效的原因我竟然忘了大半，本菜鸡可是有追求的，于是开启了寻找索引失效历程。。。把这些东西写下来，算是对我自己的一个安慰，以后再忘了，也不至于到处撒网，还捞不着鱼。。。

索引的创建、删除、修改、查询

创建索引（普通索引）

创建索引可以在创建表的时候创建索引，也可以后来修改表结构来添加索引，还可以直接创建。

直接创建

CREATE INDEX 索引的名字 ON 你的表名(表中的列名(长度))；
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

CREATE INDEX sexIndex ON test(sex(1))

修改表结构

我最喜欢的还是修改表结构这一种。没办法，只是因为在人群中多看了你一眼，却没能忘掉你容颜，梦想着偶然能有一天…… 大体就是这个意思。
ALTER table 你的表名 ADD INDEX 索引的名字(表中的列名)； 列名可以写多个，中间使用逗号分隔。

ALTER TABLE test ADD INDEX nameIndex(sname);

创建索引（唯一索引）

唯一索引的数据一定要唯一，不然会报下面的那个错。（方法就是普通索引的INDEX前加一个unique关键字）

ALTER TABLE test ADD UNIQUE INDEX nameIndex(sname);

删除索引

删除索引可以使用修改表结构 来删除或者直接删除。

直接删除

DROP INDEX 索引的名字 ON 你的表名;

DROP INDEX sexIndex ON test

修改表结构

ALTER TABLE 你的表名 DROP INDEX 索引的名字

ALTER TABLE test DROP INDEX nameIndex

查询索引

SHOW INDEX FROM 你的表名;
其中index 可以使用key来替换，但是很明显这两个关键字的作用是不一样的。在这里就不做详细叙述了。

SHOW KEYS FROM test

修改索引

你想什么呢？是不是以为啥都可以缝缝补补接着使啊，索引这里根本就没有修改操作，想修改可以，先删除，再建一个就OK了。

Non_unique : 如果索引不能包括重复词，则为0，否则为1。
Seq_in_index ： 索引中的列序列号，从1开始。
Collation ： 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality ：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part ：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed ：指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null ：如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type ： 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
字段描述摘自 添加链接描述

索引失效

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) ，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
2.对于多列索引，不是使用的第一部分，则不会使用索引，最左前缀匹配原则，中间不能断，索引建的顺序是一二三，where后面就得是一二三，顺序不能乱。
比如： ALTER TABLE test ADD INDEX newIndex(sname, sex…);那么搜索顺序就必须是这个顺序。
下图是正确匹配情况(划掉的部分，不方便放出来)。

后来在某一索引列上加了函数处理。

可以看出没匹配到。搜索的row 也不一样。明显增多。
不用索引的话，是这样，

　　3.like查询以%开头，前导模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)，想用的话使用覆盖索引即可。
　　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引（就是这个原因，导致我索引失效）。
　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
　　6.使用不等于（！= 或者<>）不能使用索引
　　7.使用 is null 或者 is not null 也不能使用索引
　　8.不要在索引列上做任何操作，做什么操作都是破坏索引。
　　部分摘自添加链接描述