索引能优化数据库查询性能，但索引也有代价：

  • 添加索引后，每次写入数据行，都需要更改索引。因此会降低表的写入性能。
  • 其次，索引会占用磁盘空间，多个索引会占用更大的空间。

挑选索引应符合以下原则：

  1. 为用于搜索、排序或分组的列创建索引，尔对于用作输出显示的列（SELECT的结果）则不用。也就是说，最好是那些出现在WHERE子句，连接子句，或者出现在ORDER
    BY    或GROUP BY子句中的列
  2. 考虑数据列基数：列的基数是指列中非重复值的个数。例如对于性别的列，只包含’M’和’F’，索引操作几乎没用。如果当两者出现的频率大致一样，当搜索时，查询优化程序会跳过索引去执行全表扫描。
  3. 尽量选用较小的数据类型。短小值可以让比较操作更快，加快索引查找速度、缩小空间开销，减少磁盘的I/O请求。
  4. 根据上一条原则，若想要对字符串进行索引，应尽可能指定前缀长度，这样可以节约大量索引空间——同时主要尽可能保证较多的基数。
  5. 利用最左索引。当创建复合索引时，应合理利用索引中最左侧的任意数列集合。
  6. 挑选合适的索引类型：大部分存储引擎都会选择默认的索引实现。但有时候也允许用户自行选择。比如散列值适合完成精确匹配（等于/不等于），但在查找范围值（大于小于/LIKE）时表现不佳。
  7. 使用慢查询日志找出那些性能低劣的查询。注意这个慢取决于服务器的负载。高负载时会写入更多的查询。

合理地使用索引进行查询：

  1. 使用相同的数据类型进行比较：例如，INT/INT比较，或者BIGINT/BIGINT比较会比INT/BIGINT更快。同理可以认为CHAR(10)与VARCHAR(10)是同类型的，但与CHAR(12)是不同类型的。
  2. 让索引列在表达式中单独出现：如WHERE col*2 < 4和WHERE col < 4/2在结果上等效，但对第一行，MySQL会检索每一行的col值，进行计算后与4比较，因此不会利用索引。而第二行可以利用索引的优势。
    再如：SELECT * FROM table WHERE YEAR(date_col） < 1990中，表达式也不会把1990与索引值比较，而是取出每一行的数据计算YAER()后比较。如果想利用索引，可以使用明确日期值如SELECT * FROM table WHERE date_col < 1990-01-01
  3. 避免在LIKE模式的开始位置使用通配符：优化程序会查看匹配模式的文字初始部分，并使用索引来找到匹配的行。但这种优化方式不能用于REGEXP运算符的模式匹配，REGEXP表达式绝不会被优化。
  4. 如果子查询运行的很慢，考虑用连接操作替换子查询：由于子查询是较晚出现的功能，因此优化程序对子查询的支持稍弱。
  5. 避免过多的自动类型转换：自动类型转换可能会导致数据都转换成更高精度（更长）的数据类型，从而降低效率。更糟糕的是，涉及类型转换的比较可能阻止MySQL使用索引（类似第二条）

优化查询的思路

  1. 多用数字运算，少用字符串运算：如比较运算时，数字之间比较可以在一次运算内完成，而字符串需要依次比较多个字节。
    如果字符串类型的取值个数有限，那么可以选用ENUM或SET类型，这样可以获得数字运算带来的好处。
  2. 避免使用较大的数据类型。
  3. 把数据列声明成NOT NULL。因为在查询处理期间，MySQL不需要检查该列是否为NULL，而不用再把NULL当做一种特例检查。
  4. 使用PROCEDURE ANAYLYSE()：运行后可以看到，对适用于表里各个列的优化数据类型给出了建议。
  5. 定期使用OPTIMIZE TABLE整理表碎片，
  6. 使用合成索引：可以通过MD5()函数或SHA1()、CRC32()根据表里的其他列计算出一个散列值并存储到一个单独的列里。不过，该技术仅适用于精确匹配查询。合成散列索引对BLOB或TEXT列非常有用。
  7. 把BLOB或TEXT剥离成一张单独的表，在某些场合，从而使这个表的其他列转换成固定长度的形式，能减少主表的碎片，提高检索效率。