MySQL索引使用总结

索引(index)

官方定义：一种提高MySQL查询效率的数据结构
优点：加快查询速度
缺点：
1.维护索引需要消耗数据库资源
2.索引需要占用磁盘空间
3.增删改的时候会影响性能

索引分类

索引和数据库表的存储引擎有关，不同的存储引擎，表的存储方式是不同的，这也就导致应用索引的时候有所不同，具体可以参考：https://blog.csdn.net/weixin_42209881/article/details/134660475
以InnoDB引擎为例,共有以下几种索引：
主键索引：建表时设置主键时，数据库会自动建立索引。

单值索引(单列索引,普通索引)：一个索引只包含单个列，一个表可以有多个单值索引。关键字NORMAL

唯一索引：索引列的值必须唯一，但允许为null，这也是和主键索引的区别。关键字 UNIQUE

复合索引：多个列组合在一起，共同创建一个索引。

全文(Full text)索引：MySQL5.7的版本之前，只支持MYISAM引擎，之后的支持InnoDB引擎。在定义索引的列上支持全文查找,允许这些索引列中插入重复值和空值。可以在(char,varchar,text)这些列上创建，关键字FULLTEXT

空间索引:MySQL5.7的版本之前，也是只支持MYISAM引擎，之后的支持InnoDB引擎。是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有 4 种：geometry(几何体)、point(点)、linestring(线)、polygon(多边形)。创建空间索引的列，必须将其声明为 not null。关键字SPATIAL

创建索引

主键索引不用创建，在建表之后，会自动创建一个索引
比如通过这个sql查询一下索引

show  index  from device_info

普通索引可以建表的时候创建，也可以用sql创建，

CREATE TABLE tableName( 
  age INT NOT NULL,  
  columnName  columnType,
  INDEX [indexName] (columnName(length)) 
);

CREATE INDEX indexName ON tableName (columnName(length))

ALTER TABLE tableName ADD INDEX indexName(columnName)

通用语法：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]  

• UNIQUE 、FULLTEXT 和SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
• INDEX 与KEY 为同义词，两者的作用相同，用来指定创建索引；
• index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
• col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC 或DESC 指定升序或者降序的索引值存储。
  复合索引创建：

CREATE TABLE test3(
    id INT(11) NOT NULL,
    name CHAR(10) NOT NULL,
    age INT(5) NOT NULL,
    INDEX mulindex(id,age)
)

索引的使用原则

• 索引并非越多越好
  由于当表中的数据更改的同时，索引也会进行调整和更新，并且会影响插入和更新所以索引并非越多越好。

• 避免对经常更新的表进行过多的索引
  避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对于经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

• 数据量小的表最好不要使用索引
  数据量小，不影响性能时，就不要使用索引了，有可能造成相反的结果。

• 尽量选择区分度高的列作为索引，在不同值较多的列上建立索引，不同值很少的列上不要建索引

• 当唯一性是某种数据本身的特征时，指定唯一索引
  使用唯一索引须能确保定义的列的数据完整性，以提高查询速度。

• 在频繁进行排序或分组的列上建立索引
  在频繁进行 group by 或 order by 操作的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

• 尽量的扩展索引，不要新建索引。

• 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

注意事项

索引有个最左前缀匹配原则，在使用复合索引时，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的顺序可以任意调整。
并且 “=” 和 “in” 在使用 and 时可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的内部的查询优化器会自动进行优化，匹配索引