背景:测试小姐姐说我们的项目有些功能速度慢,我回头一想 :查询速度慢 加个索引嘛!于是跑到测试库中加索引,我加,我加,我加加加。。。唉!索引不匹配啊,咋回事,索引失效的原因我竟然忘了大半,本菜鸡可是有追求的,于是开启了寻找索引失效历程。。。把这些东西写下来,算是对我自己的一个安慰,以后再忘了,也不至于到处撒网,还捞不着鱼。。。
索引的创建、删除、修改、查询
创建索引(普通索引)
创建索引可以在创建表的时候创建索引,也可以后来修改表结构来添加索引,还可以直接创建。
直接创建
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.不要在索引列上做任何操作,做什么操作都是破坏索引。
部分摘自添加链接描述