煮酒听雨

MySQL--索引

编辑时间：2020-01-20 编辑：忘尘阅读：384

####索引类型
- 主键索引
- 唯一索引
- 普通索引
- 组合索引（复合索引）
- 全文索引

####创建索引
```mysql
#使用ALTER TABLE语句
ALTER TABLE 表名 ADD 索引类型（unique,primary key,index,fulltext）[索引名]（字段名）

#使用CREATE INDEX语句，可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引。如果字段是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
CREATE INDEX index_name ON table_name (column_list)；
CREATE INDEX index_name ON table_name (column_list(length))；
CREATE UNIQUE INDEX index_name ON table_name (column_list)；

```
####删除索引
```mysql
#使用ALTER TABLE或DROP INDEX语句
drop index index_name on table_name ;
alter table table_name drop index index_name ;

alter table table_name drop primary key ;
```
<br/>

####索引创建原则 （小总结）
- 索引不会包含有`NULL`的列 `（在数据库设计时尽量不要让字段的默认值为NULL）`
 - 只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的；

- 需要作为表查询条件、排序、分组和联合查询操作的字段建立索引

- 根据情况创建复合索引
 - 注意最左前缀匹配原则，mysql会一直向右匹配直到遇到【范围查询】(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

- 尽量选择【区分度高】的列作为索引 `（唯一性强的字段）`
 - 区分度的公式=count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

- 尽量的扩展索引，不要新建索引 `(扩展索引)`
 - 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

- 尽量使用数据量少的索引，可以有效减少索引的磁盘占用提高查询效率
 - 如果索引字段的值很长，最好使用值的前缀来索引
 
- 避免创建过多索引，索引额外占用磁盘空间、降低写操作效率 `(限制索引的数目)`
 - 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间

>注意：mysql查询只使用一个索引 ，如果要对多个字段使用索引，建立复合索引！

<br/>

####造成索引失效的情况
- 如果将要使用的索引列不是复合索引列表中的第一部分，则不会使用索引 (复合索引遵循最左前缀原则)
- 在索引列进行运算导致索引失效,进行运算包括(+、-、*、/、! 等) 
- 在索引列上使用mysql的内置函数
- like查询以%开头, 前导模糊查询不能利用索引(like '%XX'或 like '%XX%')
- 如果条件中有or，即使其中有条件带索引也不会使用,要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
- 如果列类型是字符串，那一定要在条件中将数据值使用引号,否则不使用索引
- 在WHERE子句中使用NOT IN 、<>、！= 操作符 ，`但 <、<=、=、>、>=、BETWEEN、IN是可以用索引`
- 应尽量避免在WHERE子句中对字段进行NULL值判断
 - `在字段not null的情况下，is null和is not null都不会走索引`
- 如果Mysql估计使用索引比全表扫描更慢，会放弃使用索引
- 随着表的增长，where条件出来的数据太多，大于30％，使得索引失效（会导致CBO计算走索引花费大于走全表）
- 如果在RDER BY的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化
	- `如：SELECT * FROM t ORDER BY YEAR(date);`
- 在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用

<br/>

#####题外：
<a target="_blank" href='https://blog.csdn.net/cxu123321/article/details/107861422'>Mysql在字段为允许null时，存在null值，会不会影响索引使用？</a>
考察两个问题：
1. 当某一列有为null值的数据时，该列的索引是否还能生效
2. is null或者is not null是否能使用到索引

来说两句吧