mysql索引基础总结

索引

索引优缺点

​ 优点： ①通过使用唯一性索引，保证数据库某字段唯一。

​ ②加快数据检索速度，减少数据查询时间，有分组和排序是，减少分组和排序的时间。

​ ③加速表与表之间的连接。

​ 缺点：①索引占用额外的物理空间。

​ ②当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

​ ③创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引实现方式

​

创建索引原则

​ ①查询使用频率高的字段创建索引。排序、分组、联合查询频率高的字段创建索引。

​ ②索引不宜过多，选择合适的字段创建索引。例如①。

​ ③需要多个列设置索引时，可以采用联合索引，创建联合索引时需要注意，只有在第一列的字段使用时，索引才会生效。

​ 例：index index_name (A, B, C, D)； 在sql语句中使用到A时，索引才会生效。

​ ④尽量使用数据量少的索引，对于一个varchar（500）进行全文检索使用的时间比varchar（20）花费的时间长。

​ ⑤频繁更新的字段尽量不使用索引。

索引失效

​ ①like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效

​ ②当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

​ ③在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的

​ ④对索引字段进行计算操作、字段上使用函数

​ ⑤违背最左匹配原则：对于多列索引（复合索引），条件中没有索引的第一个字段，则不会使用索引

查看sql执行计划

使用explain检测索引是否被启用，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引

explain select * from table where column = '';

id:选择标识符
select_type:表示查询的类型(SIMPLE、UNION等)
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引，key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明，有三个值：
using index代表使用覆盖索引，不用回表using where代表数据库引擎返回结果后mysql server还会再次筛选。（引擎就是innodb这种，要注意区分引擎和mysql server的区别。）using condition index代表使用二级索引不够还要回表，但回表之前会过滤此二级索引能过滤的where条件