索引上优化细节

索引优化不能孤立的考虑,首先你需要熟悉查询优化以及服务器调优,然后再决定索引.


数据类型:
更小通常更好.简单就好.尽量避免NULL.


由于DECIMAL需要额外的空间和计算开销,只有在需要对小数进行精确计算的时候才使用DECIMAL,比如金融数据.


很少发生更新的时候,通常适合VARCHAR.
CHAR适合存储用户密码的MD5哈希值.


ENUM和SET列适合用来保存订单的状态,产品的类别或性别这样的信息.


IP地址应该用无符号的整型来保存,INET_ATON()和INET_NTOA()函数可以在IP地址和整数之间互相转换.


索引基础知识:
如果索引了多列数据,那么列的顺序非常重要,因为MySQL只能高效地搜索索引的最左前缀. 创建一个双列索引和两个单列索引是不一样的.
索引是存储引擎层实现的,而不是服务器层.索引各个引擎对索引的实现有所不同.
索引类型:B-TREE索引,哈希索引,空间索引(R-Tree), 全文索引.


B-TREE索引按顺序保存了索引的列,它对搜索范围数据很有用.
如果查找没有从索引列的最左边开始,它就没什么用处.不能跳过索引中的列,这和函数参数一样.


目前,只有MEMORY存储引擎支持显式的哈希索引, 哈希索引对使用了每一列的精确查找有用.
mysql不能适用哈希索引进行排序.因为他是散列的.
InnoDB有一个特别的功能,叫自适应哈希索引.这是自动为频繁访问对象加的一个类似哈希的内存索引.


MYISAM支持空间索引,空间索引不会要求WHERE子句适用索引的最左前缀.


如果查询中没有隔离索引的列,MYSQL通常不会使用索引."隔离"列意味着它不是表达式的一部分,也没有位于函数中.例如下面查询不能适用id上的索引:
select id from actor where di + 1 = 5;


前缀索引.有时需要索引很长的字符列,策略就是适用前缀索引,关于前缀的长度有个选择率.这个值能接近0.31基本就可以了,可以在同一个查询中针对不同的前缀长度进行计算,这对于大表非常有用.:
select count(distinct left(city, 3))/count(*) as sel3,
       count(distinct left(city, 4))/count(*) as sel4,
       count(distinct left(city, 5))/count(*) as sel5,
       count(distinct left(city, 6))/count(*) as sel6,
       count(distinct left(city, 7))/count(*) as sel7,
from sakila.city_demo;
前缀索引也有缺点,不能在ORDER BY 或GROUP BY查询中使用.


聚集索引是一种存储数据的方式,"聚集"指实际的数据行和相关的键值都保存在一起.当前,SolidDB,和InnoDB是唯一支持聚集索引的存储引擎.


可以在CREATE TABLE 时用PACK_KEYS控制索引压缩的方式.


重复索引是类型相同,以同样的顺序在同样的列上创建的索引.应该避免创建重复索引,并且在发现它的时候把它移除掉.


索引对INNODB有很重要的作用,因为它会让查询锁定更少的列.


在任何可能的地方,都要试着扩展索引,而不是新增索引.因为容易维护.