建立sql索引应该遵循什么原则

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该 仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：在经常需要搜索的列上，可以加快搜索的速度；在作为 主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索 的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询 时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的 这些列具有下列特点：第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查 询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的 取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加 快检索速度。第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。第四，当修改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。

创建索引的方法和索引的特征

1.查询频繁

2.区分度高（离散度）

3.长度小

4.尽可能覆盖常用的查询字段

区分度高（离散度）：100万用户，性别基本为男/女各50万，区分度就很低了.

索引长度直接影响索引文件的大小，影响增删改的速度，并间接影响查询速度（占用内存多）。联合索引中，区分度大的字段放前面

对于一些较长但又需要经常查询的字段，可以截取前面部分作为索引

例子：比如成语表的成语字段长度为4-20左右。

针对该列中的值，从左往右截取部分来建索引

1：截的越短，重复度越高，区分度越小，索引效果越不好

2：截取的越长，重复度越低，区分度越高，索引效果越好，但是索引长度越大，带来的影响也越大-增删改慢，间接影响查询。

所以，需要再区分度+长度 两者取一个平衡。

方法：截取不同长度，并测试区分度,取一个合适的长度

select (  (select count(distinct left(`word`,1)) from dict)/( select count(*) from dict)  )

select (  (select count(distinct left(`word`,2)) from dict)/( select count(*) from dict)  )

select (  (select count(distinct left(`word`,3)) from dict)/( select count(*) from dict)  )

因为innodb 的左前缀原则，xxx%有效，而%xxx则无效。

对于左前缀区分度不高的字段，该如何建立索引

比如网址，前缀都是http://www

http://www.baidu.com

http://www.php.cn

http://www.w3school.com

技巧一：

将数据反过来存储，比如moc.udiab.www:ptth

技巧二：

使用伪哈希

添加一个url_crc32字段，

使用crc32算法将网址转为整形存储，查询的时候查询该网址的crc32值。

crc32是一种哈希算法，能把字符串算为32为整数。

crc32的计算结果可能有重复，但是概率不高，可以在查询后再做相应过滤

多列索引

考虑因素，列的查询效率，区分度，同时还要结合具体业务。

---