Mysql 索引的创建和注意事项-蒲公英云

Mysql 索引的创建和注意事项

小灰灰 2023-02-18 08:03 86阅读 0赞

索引创建

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

主键索引（聚集索引） 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不回去查数据，通过索引返回数据

索引规范

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

索引失效

禁止使用属性隐式转换
SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，phone是varcahr类型，需要添加引号

禁止在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')