文章目录
什么是索引?
索引(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)
索引失效怎么分析
- 先观察,开启慢查询日志,设置相应的阈值(比如超过3秒就是慢SQL),在生产环境跑上个一天过后,看看哪些SQL比较慢。
- Explain和慢SQL分析。比如SQL语句写的烂,索引没有或失效,关联查询太多
- Show Profile是比Explain更近一步的执行细节,可以查询到执行每一个SQL都干了什么事,这些事分别花了多少秒。
- 不遵守最左原则
- 尽量使用覆盖索引
- 使用不等于(!=或<>)会使索引失效
is null
或is not null
也无法使用索引like
通配符以%开头会使索引失效- 字符串不加单引号导致索引失效
- 少用or,用or连接会使索引失效
Explain分析索引
使用explain关键字可以模拟优化器执行sql查询语句,从而得知MySQL是如何处理sql语句。
一张图解释MySQL的Explain
Show Profile分析索引
参考文章:MySQL高级知识(十一)——Show Profile
参考文章
https://www.runoob.com/mysql/mysql-index.html
https://blog.csdn.net/GV7lZB0y87u7C/article/details/79969293
https://www.cnblogs.com/developer_chan/p/9208404.html