做旅游攻略的网站代码,手机如何做网页,深圳市交易中心,网站优化 方案前言
本文主要讲述不同SQL语句下#xff0c;索引的生效情况。
关于索引的前置知识#xff0c;本文不再讲述。
SQL语句性能分析方法
查看不同类型SQL语句的执行频率
SHOW GLOBAL STATUS LIKE COM_______;慢查询日志
该日志记录了SQL执行时间超过指定参数的所有SQL语句。…前言
本文主要讲述不同SQL语句下索引的生效情况。
关于索引的前置知识本文不再讲述。
SQL语句性能分析方法
查看不同类型SQL语句的执行频率
SHOW GLOBAL STATUS LIKE COM_______;慢查询日志
该日志记录了SQL执行时间超过指定参数的所有SQL语句。
# 若要开启慢查询日志需要在.cnf配置文件中设置
slow_query_log 1# 设置记录时间为2s执行时间超过2s的SQL语句将会被记录
long_query_time 2若是在Linux系统中慢查询日志的位置/var/lib/mysql/localhost-slow.log profile变量
通过profile可以让我们知道每条SQL的执行时间都消耗在什么地方
# 查看是否支持profile
SELECT have_profiling;# 开启profile
SET profiling 1;# 执行了一些SQL语句......# 查看profile总体给出query_id、SQL语句、消耗时间
show profiles;# 查看特定SQL语句的CPU耗时情况
show profile cpu for query query_id;explain
可以获取MySQL如何执行select语句的信息包括select语句执行过程中表如何连接和连接的顺序。
# 调用方式直接在常规select语句前边加explain索引使用 | 常规索引
select * from table_1 where name xxx;若name有创建常规索引走常规索引查询
若name没有创建索引不走索引查询耗时更长
假设查询条件中有and条件
and前后的列都单独创建有索引在查询的时候只会选用一个列的索引进行查询。and前后的列创建有联合索引在查询的时候走联合索引。 上述示例中当name字段不是聚簇索引会产生回表查询。
回表查询指当使用非聚簇索引查询的时候若索引的列无法满足查询要求时会在使用非聚簇索引查询到主键的时候再走一遍聚簇索引查询需要的数据。
索引使用 | 联合索引
最左前缀原则
在使用联合索引的时候需要满足最左前缀法则。
最左前缀原则是指在使用联合索引的时候按照定义联合索引的时候的列前后关系进行分析
从最左列开始分析当某一列不在查询条件中该列及其右边的列的索引将失效特殊情况若最左列不在查询条件中则联合索引全部失效
在创建联合索引的时候假设参与列从左到右依次为A、B、C相当于创建了以下这些索引
列A的单列索引列A和列B的联合索引列A和列B和列C的联合索引
所以有了最左前缀原则的出现。
# 假定profession、age、status三列建立了联合索引# 联合索引全部生效
explain select * from tb_user where profession 软件工程 and age 31 and status 0;# 联合索引生效2个
explain select * from tb_user where profession 软件工程 and age 31;# 联合索引生效1个
explain select * from tb_user where profession 软件工程;
# 联合索引生效1个
explain select * from tb_user where profession 软件工程 and status 0;# 联合索引不生效
explain select * from tb_user where age 31;# 联合索引全部生效
# 最左匹配是指按照定义联合查询时候列的左右来匹配的在sql语句中的位置不影响只要都体现就行
explain select * from tb_user where age 31 and profession 软件工程 and status 0;范围查询
当联合索引的列中有的列出现了范围查询。
当范围查询中没有等号出现例如、 范围查询的列右边的列索引失效当范围查询中有等号出现例如、 右边的列索引仍然生效
【右边的列】仍然指定义联合索引时候的左右位置而不是在SQL中where条件书写的先后位置
# 只有profession、age两列的索引生效
explain select * from tb_user where profession 软件工程 and age 30 and status 0;# 当范围查询出现等号后续列索引仍然有效
# 即业务允许的情况下尽可能使用类似于或这样的范围查询语句
explain select * from tb_user where profession 软件工程 and age 30 and status 0;索引使用 | 覆盖索引
覆盖索引就是查询过程使用了索引并写需要返回的结果列在该索引中都能找到。
使用覆盖索引就要减少select *的使用。
# 假定profession、age、status创建了联合索引。# 做到了覆盖索引
# 需要返回的结果在索引中都有联合索引属于二级索引叶子节点挂的值就是行数据的主键在该表中主键就是id
explain select id, profession from tb_user where profession 软件工程 and age 31 and status 0;# 没做到覆盖索引需要回表查询即走聚集索引
# 先走联合索引二级索引找到数据的主键id然后走聚集索引找到对应的数据。
explain select * from tb_user where profession 软件工程 and age 31 and status 0;索引使用 | 前缀索引
如果字段类型是字符串有的时候该列创建的索引将非常长浪费时间影响查询效率。这时候就可以用前缀索引用前n个字符创建索引。
前缀索引只能用于字符串类型的数据。
create index on table_name(column_name(n));索引使用 | 指定特定的索引
如果一个列既参与了联合索引的创建也单独创建了索引。在用该列作为条件查询的时候选用哪一个索引是由MySQL确定的。
但是可以认为指定MySQL选用哪一个索引。
# 建议选用某个索引MySQL执行的时候仍然可以选择自己认为最优的索引执行
explain select * from table_name [use index(index_name)] where xxxxxx;# 忽略不使用某个索引
explain select * from table_name [ignore index(index_name)] where xxxxxx;# 强制选用某个索引
explain select * from table_name [force index(index_name)] where xxxxxx;索引使用 | 索引下推
参考文章https://javaguide.cn/database/mysql/mysql-index.html#%E6%9C%80%E5%B7%A6%E5%89%8D%E7%BC%80%E5%8C%B9%E9%85%8D%E5%8E%9F%E5%88%99
索引下推是MySQL提供的一种索引优化功能可以减少回表次数提高查询效率。
简单来说索引下推的原理就是将部分服务层负责的事情交给存储引擎层来处理。
