Mysql 索引的创建和注意事项

索引创建

索引的创建遵循一个原则，列的离散型越高，选择性就越好

• 主键索引（聚集索引） MySql中默认主键就是聚集索引，如果不指定主键的话，Mysql内部会创建一个隐藏的聚集索引。

  ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
  

• 唯一索引 唯一索引要求该字段的值是唯一的

  ALTER TABLE `table_name` ADD UNIQUE ( `column` )
  

• 普通索引

  ALTER TABLE `table_name` ADD INDEX IndexName(`column`(length))
  

• 全文索引

  ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
  

• 联合索引

  ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
  

索引遵循最左匹配原则，也就是说对索引中的关键字进行计算（对比），一定是从走到右依次进行，切不可跳过

联合索引使用

最常用的SQL如下

Select * from users where name = ? ;
Select * from users where phoneNum = ? ;
Select * from users where name = ? and phoneNum = ?;

索引解决方法

ALTER TABLE `users` ADD INDEX idx_name ( `name` )
ALTER TABLE `users` ADD INDEX idx_name ( `phoneNum` )
ALTER TABLE `users` ADD INDEX idx_name ( `name` ，`phoneNum` )

第一个索引是多余的索引。因为有最左原则，下面的联合索引可以实现对name索引。

Select id,name, phoneNum from users where name = ?;

name和phoneNum添加了索引，这条sql不回去查数据，通过索引返回数据

索引规范

1. 单表索引建议控制在5个以内
2. 单索引字段数不允许超过5个
3. 禁止在更新十分频繁、区分度不高的属性上建立索引（散列性低）
4. 建立组合索引，必须把区分度高的字段放在前面

索引失效

1. 禁止使用属性隐式转换
   SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不 能命中phone索引，phone是varcahr类型，需要添加引号
2. 禁止在WHERE条件的属性上使用函数或者表达式 函数或者表达式会导致索引失效

   SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15'会导致索引失效，导致全表扫描
   正确使用：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15
   00:00:00')
   

3. 禁止负向查询，以及%开头的模糊查询
   • 负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
   • %开头的模糊查询，会导致全表扫描
   • %结尾的模糊查询，如果占比比较高的，也会造成索引失效，会导致全表扫描