Mysql索引详解

Mysql共有以下几种索引：
主键索引：它 是一种特殊的唯一索引，不允许有空值。
普通索引：最基本的索引，没有任何限制
唯一索引：与”普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

一个表主能有一个主键列，但是可以有多个普通索引列，主键列要求所有的内容必须唯一，而普通列不要求内容必须唯一。

主键创建：

mysql> use test;
mysql> create table student(
    -> id int(4) not null AUTO_INCREMENT,
    -> name char(20) not null,
    -> age tinyint(2) NOT NULL default '0',
    -> dept varchar(16) default NULL,
    -> primary key(id),   #主键索引
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> alter table student add index index_name(name);   #给name列创建索引

mysql> alter table student change id id int primary key auto_increment; #创建主键索引

数据量很大的时候，不适合建立索引，会影响用户访问，曾经400-500万条记录的表，建立索引，花了90-180秒，尽量选择在业务低谷时建立索引。

1、对字段的前n个字符创建索引
当遇到表中比较大的列时，列内容的前n个字符在所有内容中已经接近唯一时，这是可以对列的前n个字符建立索引，而无需对整个列建立索引，这样可以节省创建索引占用的系统空间，以及降低读取和更新维护索引小号的系统资源。
对字段的前n个字符创建普通索引的语法

create index index_name on test(name(8));

2、为表的多个字段创建联合索引

注意：按照条件查询列数据时，联合索引是有前缀生效特性的
Index(a,b,c)仅a，ab,abc三个查询条件列可以走索引，b,bc,ac,,c无法使用索引

如果查询数据的条件是多列时，可以为多个查询的列创建联合索引，甚至可以为多列的前n个字符换件联合索引，演示如下：

mysql> create index ind_name_dept on student(name,dept);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from student\G;
*************************** 1. row ***************************
        Table: student
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: student
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: student
   Non_unique: 1
     Key_name: age_name
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: student
   Non_unique: 1
     Key_name: ind_name_dept
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: student
   Non_unique: 1
     Key_name: ind_name_dept
 Seq_in_index: 2
  Column_name: dept
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.00 sec)

ERROR: 
No query specified

查看表的唯一值：

mysql> select distinct count(user) from mysql.user;
+-------------+
| count(user) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

创建唯一索引

mysql> create unique index uni_ind_name on student(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | UNI | NULL    |                |
| age   | tinyint(2)  | NO   | MUL | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

索引小结：

1、索引类似书籍的目录，会加速查询数据的速度
2、要在表的列（字段）上创建索引
3、索引会加快查询速度，但是也会影响更新的速度，因为更新要维护索引数据
4、索引列并不是越多越好，要在频繁查询的表语句where后的条件列上创建索引
5、小表或重复值很多的列上不可以创建索引，要在达标以及重复值少的条件列上创建索引
6、多个列联合索引有前缀生效特性
7、当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引
8、索引从工作方式区分，有主键，唯一，普通索引
9、索引类型会有BTREE和hash（适合做缓存）