MySql学习笔记（二）- 索引的设计和使用

作为开发人员，数据库的索引是我们再熟悉不过的了。那么实话真的会了吗，在项目开发中随便定义一个int、varchar后边跟个primary key或者加个index就好了么？考虑到这些咋还真的需要看看专业的人都是怎么做的。

在mysql中索引是提升性能的常用工具，一般来说我们就是通过让where的条件字段变成索引，或者如果where条件比较多的话一般将前边的条件做成索引。这样就可以通过索引快速筛选大量数据，然后在小范围的结果集中通过全表扫描的方式进行目标检索。

在mysql中每个列都可以被索引，但是对于每种表类型来说，索引的数量和索引的本身字段长度都有限制。比如myisam、innodb、memory等每个表至少支持16个索引。索引长度至少256字节。mysisam和innodb表的默认索引为Btree索引，支持前缀索引，也就是对一个字段的前多少个字符建立的索引，对于myisam表来说索引的前缀可达1000字节，而对innoDb来说其前缀索引最长为767字节。

mysql中还支持全文索引，这种索引可以做全文检索使用，但是全文索引只限于char、varchar和text字段列。

默认情况下，memory使用的是hash索引，但也支持btree索引

如何对一个字段创建索引？

-- 对test表的name字段的前5个字段检索索引
create index name_index on test(name(5))
-- 当然，我们也可以看看我们的索引在检索的时候是否被使用了
explain select * from test where name!=''

-- 如果您要删除索引
drop index name_index on test;

索引创建的原则

1.不一定要选择所有选择的列，最适合索引的列是出现在where字句中的列，而不是select中的列

2.使用唯一索引，考虑某个列值得分散。索引的列的基数越大索引效果越好。例如存放出生日期的列具有不同值，很容易区分，而使用性别的话区分度不大。

3.使用短索引，如果对字符串进行索引，应该指定一个前缀长度，比如一个字段varchar(300)，但是前10到20字段就能区分，那么在前10到20个字符上建立索引就可以区分很多数据，而且还可以节省索引空间。磁盘io也会变少，而且mysql在内存中可以存储更多的缓存，所以检索会变快。

4.利用最左前缀，在创建一个n列索引的时候，实际上创建了mysql可利用的n个索引，多列索引可起多个索引的作用，但是匹配的时候都是通过最左进行的。

5.不要过度索引，每个索引都要占据磁盘空间，并降低写操作的性能，在修改表的时候索引也要同步更新甚至重构，所以索引越多会带来一定的损耗。所以说如果一个索引经常不用那么这个索引就是负担。所以的创建索引要以优化检索为目的，但不能损耗插入的性能。

6.innodb表默认是按照一定顺序存储的，也就是说如果指定了主键则按照主键顺序进行保存，如果没有主键但是有唯一索引，那么innodb会按照唯一索引的顺序进行存储，如果都没有的话，innodb会生成一个内部列并按这个列的顺序进行存储。检索的时候按主键或者内部列是最快的。所以innodb表最好指定主键，另外innodb表的普通索引也会存储主键的键值，所以在设置主键的时候尽量选用短数据类型，从而减轻索引的磁盘空间，提高索引的缓存效果。

Btree和Hash索引

memory可以选择btree和hash索引，但是hash索引的使用要注意哦！因为hash索引是通过计算得出的具体数据地址，索引hash索引肯定不能使用= 或者》= 和《=等运算符，当然也不能使用order by进行排序。hash索引只能通过关键字来检索一行数据。对于in查询来说，btree和hash索引并没有什么差别。在对范围进行查询的时候，只能使用btree索引。如果对hash索引采用范围查询，那么将是全表扫描式的检索。要注意的是memory表的默认索引模式为hash索引。

大多数的mysql索引，比如primary key、unique、index、fulltext等都是在btree中存储，除此之外memory还支持hash索引。