MySQL的索引总结

最新推荐文章于 2024-04-19 13:07:55 发布

CWJ的博客

最新推荐文章于 2024-04-19 13:07:55 发布

阅读量295

点赞数

分类专栏： mysql 文章标签： mysql

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.youkuaiyun.com/chenweijiSun/article/details/106251056

版权

mysql 专栏收录该内容

5 篇文章

订阅专栏

什么是索引？

索引(Index)是帮助MySQL高效获取数据的数据结构。可以简单理解为：快速查找排好序的一种数据结构，可以提高数据检索的效率，降低数据库的IO成本。MySQL的索引主要分为主键索引(PRIMARY KEY)，唯一索引(UNIQUE) ，普通索引(INDEX)和全文索引(FULLTEXT) 。MySQL索引主要有两种结构：B+Tree索引和Hash索引，我们常用的索引结构是B+Tree索引。

主键索引

主键索引(PRIMARY KEY)的叶子节点保存着主键即对应行的全部数据。在InnoDB里，主键索引也被称为聚簇索引、聚集索引（clustered index）, 主键索引主要有以下特点：

主键索引列不允许有空值
一个表最多只能创建一个主键索引
主键索引可以有多列
主键索引可以被其他表引用为外键

什么是聚集索引？

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

二级索引（非主键索引）

按照定义，除了聚集索引以外的索引都是非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

而二级索引也成为非聚集索引，二级索引树中的叶子结点保存着索引值和主键值，当使用二级索引进行查询时，需要进行回表操作。如何通过sql语句来区分主键索引和二级索引的查询？

在这里插入图片描述
上面图展示有一个主键索引（id）和一个普通索引（name），那么：
select * from t_user where id = 1 即主键索引查询方式，则只需要搜索id这棵索引树
select * from t_user where name = 张三 即普通索引查询方式，则需要先搜索 name索引树，得到id的值为3，再到 id索引树搜索一次。这个过程也称为回表，也就是说，基于二级索引（非主键索引）的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询，前面已经说了主键索引，接下来说说非主键索引主要有哪些：

唯一索引

索引列的值必须唯一，但允许有空值。如果是联合索引，则列值的组合必须唯一。ps：说说唯一索引和主键索引的区别？

普通索引

普通索引允许在索引所在的列插入重复值和空值。单值索引就是常见的普通索引：一个索引只包含单个列，一个表可以有多个单值索引

联合索引

联合索引又叫复合索引，即一个覆盖表中两列或者以上的索引，看看什么情况下会使用联合索引：

需要加索引的字段，要在where条件中
数据量少的字段不需要加索引
如果where条件中是OR关系，加索引不起作用
符合最左原则
联合索引可以调用到覆盖索引，可以减少树的搜索次数，不再需要回表查整行记录，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

覆盖索引

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中（联合索引），可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数。

全文索引

FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列

最左前缀原则

顾名思义就是最左优先原则，以index （a,b,c）为例建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

索引下推

如果在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器

索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
关闭索引下推可以使用如下命令 set optimizer_switch='index_condition_pushdown=off

如何创建索引

以下是2种创建索引方式的基本语法：

create [unique] index indexname on tablename(columnname(length));

alter table tablename add index indexname (columnname(length));

举个例子：

添加主键索引

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

添加唯一索引

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

添加全文索引

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

添加普通索引

ALTER TABLE `table_name` ADD INDEX index_name (`column` )

添加组合索引

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

如何查看索引

show index from tablename;

如何删除索引

drop index indexname on tablename;

索引优化

哪些情况需要建索引

主键，唯一索引
经常用作查询条件的字段需要创建索引
经常需要排序、分组和统计的字段需要建立索引
查询中与其他表关联的字段，外键关系建立索引

哪些情况不要建索引

表的记录太少，百万级以下的数据不需要创建索引
经常增删改的表不需要创建索引
数据重复且分布平均的字段不需要创建索引，如 true,false 之类。
频发更新的字段不适合创建索引
where条件里用不到的字段不需要创建索引

SQL执行慢的原因

硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引）
数据过多（分库分表）
服务器调优及各个参数设置（调整my.cnf）