MySql索引

什么是索引

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,记录数据表里的一列或若干列对应数据项的指针，目的在于提高数据库表数据查询效率，通俗来说类似一本书的目录，能够快速定位查询的条目。

索引的类型

在MySql表中创建的索引类型分为五种：主键索引、普通索引、唯一索引、全文索引、空间索引。
在中创建两个或更多个列上的索引被称作复合索引或联合索引

主键索引

主键索引是一种特殊的唯一索引，不允许重复，不允许有空值,一个表只能有一个主键。创建主键索引根据两个或更多个列上的，称之为复合主键或联合主键。

创建方式：

1. 创建表指定主键列

   CREATE TABLE tablename ( [...], PRIMARY KEY (指定的列名) ); 

2. 修改表加入主键

   ALTER TABLE tablename ADD PRIMARY KEY (指定的列名); 

外键索引

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。把当前表的数据与另一个表关联起来的列。
外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录关联。
外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；
外键的缺点：级联控制在数据库层，外建存在影响更新数据，适合单机低并发，不利于分表分库等分布式应用扩展。业务数据生成顺序，未必一定可以先生成外键的值，再生成明细数据

阿里开发手册：【强制】不得使用外键与级联，一切外键概念必须在应用层解决

外键的使用条件

1. 存储引擎InnoDB
2. 外键关系的两个表的列必须是数据类型相同（可以是相似的，可以相互转换类型的列比如int和tinyint可以，而int和char则不可以）

外建的定义语法

在创建外键约束时，必须先创建外键约束所依赖的表，并且该列为该表的主键或唯一约束的键

1. 创建外键约束的方式

   CREATE TABLE 表名
       (
           [列]
           ...
           CONSTRAINT 外键约束名 FOREIGN KEY  (指定当前表外建的类) 
           REFERENCES 外键依赖的表 (关联表的列)
           [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
           [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
       );
   
   参数说明：    
   ON DELETE、ON UPDATE表示事件触发限制，可设参数：
   RESTRICT（限制外表中的外键改动）
   CASCADE（跟随外键改动）
   SET NULL（设空值）
   SET DEFAULT（设默认值）
   NO ACTION（无动作，默认的）
   

2. 修改表的方式

   ALTER TABLE 表名
       ADD CONSTRAINT 外键约束名
       FOREIGN KEY (column1, column2,...column_n) 
       REFERENCES 外键所依赖的表 (column1,column2,...column_n)
       [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
       [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
   

   注意： 表已经存在数据，创建方式及联的时候数据关联不上创建失败。选择创建外建的时机要合适（业务场景、表使用情况，数据量大且频繁操作的表）

3. 撤销外键约束

   ALTER TABLE Orders
   DROP FOREIGN KEY 外建名
   

普通索引

普通索引是MySql里最基本的索引，没有任何限制，在任何一列或多列上都能进行创建。

创建索引：

1. 创建表指定

   CREATE  INDEX index_name
       ON tbl_name (key_part,...)
   
   key_part: {col_name [(length)] | (expr)} [ASC | DESC]  指定升序或降序排序
   

2. 修改表添加索引

   ALTER mytable ADD INDEX [indexName] ON  (key_part,...)
   key_part: {col_name [(length)] | (expr)} [ASC | DESC]  指定升序或降序排序

注意： 如果select ..... for update 没有索引/主键就会进行锁表。如果普通索引对应多行，则锁住一个范围，引发锁等待或死锁

唯一索引

全文索引的索引类型为UNIQUE,与普通索引类似，不同的就是：普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引：

CREATE UNIQUE INDEX indexName ON tab_name(列名..)

ALTER tab_name ADD UNIQUE [indexName] ON (列名..)

全文索引

全文索引的索引类型为FULLTEXT,适合在进行模糊查询的时候使用

注意：分词越小，数据越大。

空间索引

TODO

索引的数据结构

TODO

索引优化

TODO

索引的优缺点

优点

1. 索引减小了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机IO变成顺序IO
4. 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
5. 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

缺点

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
3. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
   对于非常小的表，大部分情况下简单的全表扫描更高效；
4. 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

因此应该只为最经常查询和最经常排序的数据列建立索引。

MySQL里同一个数据表里的索引总数限制为16个。