一、只为用于搜索、排序、分组的列建立索引

搜索：在 WHERE 条件中出现的列
排序：在 ORDER BY 子句中出现的列
分组：在 GROUP BY 子句中出现的列

二、只为基数多的列建立索引

列的基数指的是某一列不重复数据的个数。
列的基数大，表示列的数据分散，列的数据多，建立索引效果好。
列的技术小，表示列的数据集中，都是重复的数据，建立索引效果差。

三、索引列的类型尽量小

以整数类型为例，整数类型有 TINYINT、MEDIUMINT、INT、BIGINT。占用空间大小有小到到。
在建立索引的时候，能选择 TINYINT 类型的列做索引列，就选择该列，尽可能选择类型小的做索引列。
原因如下：

  • 数据类型越小，查询时进行的比较操作越快

  • 数据类型越小，索引占用的空间越少，每个页能够存放的记录更多，加载到内存的数据也越多，能够提高读写效率。

    四、特长字符串可以索引字符串值前缀

    针对字符串建立索引，对应 B+树有两个问题

  • 字符串越长，占用空间越大

  • 字符串越长，比较时间越长

语法：以 idx_name_birthday_phone_number 为例。 KEY idx_name_birthday_phone_number ( name( 10 ) ,birthday ,phone_number )

问题：索引列前缀导致排序无法走索引

以 idx_name_birthday_phone_number(name(10) , birthday , phone_number) 为例
索引先按照 name 排序，不过排序只根据 name 的前10个字符进行排序，并不是针对完整的 name 进行排序，这里的排序结果不等同于 按照 完整 name 数据的排序，所以无法使用索引进行排序。

五、避免冗余重复的索引

例如：设定主键，同时为主键设定唯一索引。

主键本身会建立聚簇索引，定义的唯一索引是重复的。

例如：联合索引和独立索引冗余重复

假设表结构如下

  1. CREATE TABLE table2(
  2.     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3.     name VARCHAR(100) NOT NULL,
  4.     birthday DATE NOT NULL,
  5.     phone_number CHAR(11) NOT NULL,
  6.     country varchar(100) NOT NULL,
  7.     PRIMARY KEY (id),
  8.     KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
  9.     KEY idx_name (name(10))
  10. );

索引 idx_name 与联合索引 idx_name_birthday_phone_number 重复了。

六、尽量索引覆盖

在使用二级索引查询时，查询的字段尽可能为二级索引页节点存储的字段。
例如：idx_name_birthday_phone_number(name,birthday,phone_number) 在查询时如果只需要”name，birthday，phone_number，id“ SQL 语句就只查询这些字段，这样可以免去回表的操作

七、索引只有单独使用时才有用

例如：SELECT FROM table2 WHERE name + ‘aa’ = ‘aaaa’ 。不走索引
SELECT FROM table2 WHERE name = ‘aa’ + ‘aa’ 走索引

## 不走索引
mysql> EXPLAIN SELECT * FROM table2 WHERE name + "aa" = 'aaaa' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.04 sec)

ERROR: 
No query specified

## 走索引
mysql> EXPLAIN SELECT * FROM table2 WHERE name = 'aa' + 'aa' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table2
   partitions: NULL
         type: ALL
possible_keys: idx_name_birthday_phone_number
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 3 warnings (0.04 sec)