MySQL索引优化一

最新推荐文章于 2024-07-07 12:29:28 发布
置顶 最新推荐文章于 2024-07-07 12:29:28 发布
阅读量2.1k 收藏 3
点赞数 5
分类专栏： 性能优化系列 文章标签： mysql 索引 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zlypdlx/article/details/113895428
版权

MySQL索引

索引是什么

  • 索引是一种排好序的数据结构，目的是提高查找效率
  • 一般来说，索引本身也占内存，因此索引以文件的形式存储在磁盘上
  • 平常我们所说的索引一般都是B+Tree。当然还有hash索引等等

索引优劣势

  • 优势：提高检索效率，降低数据库IO成本。由于数据都已经排好序，降低了排序的成本，降低CPU的消耗
  • 劣势：索引也要占用空间、在INSERT/DELETE/UPDATE的时候需要同步更新索引

索引分类

  • 单值索引：索引只有一个列
  • 唯一索引：索引列的值必须唯一，允许空值
  • 复合索引：索引包含多个列

索引结构

建立索引情形

  • 频繁查询的字段
  • 与其它表关联的字段需要创建索引
  • 频繁更新的字段不适合创建索引(更新数据的时候需要更新索引)
  • 查询中存在排序的字段，排序若通过索引去访问则大大提高访问的效率
  • 查询中统计和分组的字段，因为group by之前需要先排序

性能分析(MySQL Query Optimizer)

  • Explain是什么？可以模拟查询sql语句，可以知道MySql是如何执行SQL，分析语句的性能瓶颈
  • Explain能够干什么？
    • 表的读取顺序
    • 数据读取的操作类型
    • 哪些索引可以使用
    • 哪些索引实际被使用
    • 表之间的引用
    • 每张表有多少行被优化器查询
  • 使用方式
-- 1、简单使用
explain select * from emp;

-- 2、explain扩展使用，可查看优化执行情况
explain extended select * from emp;
show warnings ;

-- 3、结合分区使用
explain partitions select * from emp;
  • 执行计划包含的内容(简单使用)：
    执行计划字段
  • id：表示查询执行顺序，代表select的个数，分为三种情况①id相同，从上往下执行，②id不同，id越大优先级越高，越先执行③id为null最后执行(画外音：union查询id为null)
  • select_type：查询的类型主要用于区分普通查询、联合查询、子查询，类型有
    • SIMPLE：简单查询。查询不包含子查询和union
    • PRIMARY：复杂查询中的最外层查询
    • SUBQUERY：在select中的子查询，不在from中
    • DERIVED：在from中的子查询，数据放在临时表中
    • UNION和UNION RESULT：存在union查询，union result获取结果
  • 下面列表展示了上面几种查询类型，可以看出在select_type为union result的时候id是空的
explain select (select name from dept where id = a.dept_id) deptName from (select * from emp) a union  select 1;

查询类型

  • table：表示访问的是哪个表，当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id
  • type：访问数据类型，从最优到最差为system > const > eq_ref > ref > range > index > ALL
  • system 表中只有一条数据，是const的特例，一般的情况下不会出现
  • const 查询条件为primary key或者unique key的时候匹配一条数据
  • eq_ref primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录，这个是const之后最好的链接类型了
explain select * from emp a left join dept d on a.dept_id = d.id;

  • ref 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。ref可以出现在简单的非唯一索引查询和联合的非唯一索引查询
  • range 范围扫描通常出现在 in(), between ,> ,<, >= ,<=等操作中。使用一个索引来检索给定范围的行
  • index 扫描全表索引，这通常比ALL快一些。（index是从索引中读取的，而all是从硬盘中读取）
-- index
explain select job_id from emp ;

  • all 即全表扫描，意味着MySQL需要从头到尾去查找所需要的行。
  • possible_keys ：这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。如果该列为null，可以创建索引来提高检索效率
  • key：实际使用的索引。如果为null，则表示没有使用索引。如果使用覆盖索引，则和extra中的using index一致
  • key_len：实际使用索引的字节数。(画外音：长度后面介绍，跟编码有关系)注意：索引最大长度是768字节，超过长度的索引会变为最左前缀。实际使用的时候需要尽可能的减少索引的长度，提高左边的识别度。因为索引占空间
  • ref：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名
  • rows：这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数
  • extra：查询的额外信息，类型以及描述如下表：
类型描述
Using index查询列被索引覆盖，避免回表查询，是查询性能好的表现
Using where查询列未被索引覆盖，需要回表查询
Using where Using index查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，无法通过索引查找到数据
Null查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引
Using index condition查询列不完全被索引覆盖，where条件是前导列的一个范围
Using temporary使用临时表来处理查询，这个时候需要优化查询
Using filesort对结果使用一个外部索引排序，而不是按索引次序从表里读取行，这个时候需要优化查询
mysql> -- extra
mysql> -- using index
mysql> explain select job_id from emp where job_id = 1;
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | idx_job_id_name | idx_job_id_name | 9       | const |    3 | Using index |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> -- using where
mysql> explain select * from emp where name = '1';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> -- Using where Using index
mysql> explain select job_id,name from emp where name = 'frank';
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key             | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | emp   | index | NULL          | idx_job_id_name | 139     | NULL |    8 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> -- Null
mysql> explain select * from emp where job_id = 1;
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | emp   | ref  | idx_job_id_name | idx_job_id_name | 9       | const |    3 | NULL  |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> -- Using index condition
mysql> explain select * from emp where job_id > 100;
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | emp   | range | idx_job_id_name | idx_job_id_name | 9       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> -- Using temporary
mysql> explain select distinct name from emp ;
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+------------------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows | Extra                        |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+------------------------------+
|  1 | SIMPLE      | emp   | index | idx_job_id_name | idx_job_id_name | 139     | NULL |    8 | Using index; Using temporary |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

mysql> -- Using filesort
mysql> explain select name from emp order by name;
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key             | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | emp   | index | NULL          | idx_job_id_name | 139     | NULL |    8 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
  • 画外音：索引的前导列针对联合索引中后面的索引

索引最佳实践

  • 全值匹配我最爱
  • 最佳左前缀原则(但是skip scan例外)–带头大哥不能少，中间兄弟不能断(只能用到部分的索引)
  • 不要再索引上进行任何计算
  • 索引不能使用范围右边的列
  • 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句
  • mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描（如果覆盖索引则可以显示出来）
  • is null,is not null 也无法使用索引
  • like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作
  • 字符串不加单引号索引失效
  • 少用or,用它连接时很多情况下索引会失效
mysql> -- 最佳左前缀法则(job_id,name)
mysql> explain select job_id from emp where job_id = 1;
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | idx_job_id_name | idx_job_id_name | 9       | const |    3 | Using index |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select job_id from emp where name = 'frank';
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key             | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | emp   | index | NULL          | idx_job_id_name | 139     | NULL |    8 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> -- 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
mysql> explain select * from emp where job_id + 1 = 3;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> -- 存储引擎不能使用索引中范围条件右边的列
mysql> explain select * from emp where job_id > 100 and name = 'frank';
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | emp   | range | idx_job_id_name | idx_job_id_name | 9       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> -- 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句
mysql> explain select id, name, job_id,salary, dept_id from emp where job_id > 100;
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | emp   | range | idx_job_id_name | idx_job_id_name | 9       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select job_id from emp where job_id > 100;
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | emp   | range | idx_job_id_name | idx_job_id_name | 9       | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> -- mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描（如果覆盖索引则可以显示出来）
mysql> explain select name,emp.salary from emp where job_id != 1;
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | idx_job_id_name | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> -- is null,is not null 也无法使用索引
mysql> explain select emp.salary,job_id,name from emp where job_id is not null;
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | idx_job_id_name | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> -- like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作
mysql> explain select name from emp where name like 'frank%';
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key             | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | emp   | index | NULL          | idx_job_id_name | 139     | NULL |    8 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
--下面两个比较简单
mysql> -- 字符串不加单引号索引失效
mysql> -- 少用or,用它连接时很多情况下索引会失效
  • 最后借用一张图来表示联合索引的最佳实践
    联合索引最佳实践

总结

  • 索引(index)跟新华字典的索引目录类似，提高了检索的效率，在实践过程中也有很多需要注意的地方。平常大家可以多多的通过执行Explain来查看执行计划，分析SQL，写出高效的SQL，当然如果只是上面的内容还不够大家在日常工作中使用的，接下来还有多篇关于MySQL优化，欢迎关注！

欢迎可以关注spring-cloud系列 openfeign hystrix，也可以关注JAVA-打怪升级系列
你的点赞和关注是我创作的最大动力，有什么不足和错误的地方欢迎留言！可以微信搜索关注【小二说码】

小二说码
关注
  • 5
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
mysql索引优化有几种_mysql索引优化
weixin_29000119的博客
02-04 1829
索引类型从物理存储角度上，索引可以分为聚集索引和非聚集索引。1.聚集索引(Clustered Index)聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引。2.非聚集索引(Non-clustered Index)非聚集索引并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符 row-locator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过这个指...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL索引优化
mz474920631的博客
06-16 1万+
这里说一下几种常见优化索引的方法：前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。不过，前缀索引有一定的局限性，例如：覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表
mysql 索引优化
GuoWangWang11的博客
07-12 3755
mysql索引优化
MySQL索引优化总结
何哥的博客
09-30 6488
前言：相信大家都知道索引可以大大提高MySQL的检索速度，但是真正在平时工作中写SQL，真的会考虑到这条SQL如何能够用上索引提升执行效率？本篇博客详细的介绍了索引优化的20个原则，只要在工作中能够随时应用到，相信你写出的SQL能够命中索引，效率杠杠的。 一、索引的分类 1.1、普通索引、主键索引和唯一索引 1.2、聚集索引和非聚集索引 1.3、联合索引最左匹配原则 1.4、索引覆盖和回表 1.5、前缀索引 1.6、索引合并 1.7、索引下推 二、索引优化规则 查询的条...
MySQL索引优化（超详细）
weixin_55816781的博客
05-10 6539
MySQL索引优化，涉及到索引介绍，性能分析，查询优化，慢查询日志。
mysql索引优化
LJJ1338的博客
09-10 1956
SQL如下： CREATE TABLE IF NOT EXISTS staffs( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(24) NOT NULL DEFAULT "" COMMENT'姓名', age INT NOT NULL DEFAULT 0 COMMENT'年龄', pos VARCHAR(20)...
mysql索引优化助记口诀
张步云的专栏
12-25 2979
口诀 不等not null还有or，索引全中也无效； 大等小等is null，全索命中才有效； 除此全匹最高效，最左匹配要记牢； 大小跳索百分号，后面用索也无效； 索无函数和计算，才能保证索有效。 解释 sql中只要包含了!= not null or ，会导致sql中全部索引失效。但是 is null >= <= 不会。 >= <= is null只会有两种情况：只有在命中了全部索引，也就是组合索引中的索引全用到了才会都有效并且是全部有效，否则就全无效。 组合索引中除了上面的特例外，
MySQL索引优化的几种方式
weixin_45750855的博客
11-11 4839
索引高性能优化 1. 使用独立的索引列 ​ 错误的查询语句会使得索引无效… select user_id from user where user_id-1=4; 第一个例子中，对索引列user_id进行了一次方程运算，然后进行查询，然而这样的操作MySQL无法自动识别，导致主键索引失效了。 select date_col from t where TO_DAYS(CURRENT_DATE) -TO_DAYS(data_col)<=15; select date_col from t where
MySql索引优化
12-14
在列上创建索引可以优化查询的速度，但缺点是减慢写入数据的速度。 　创建索引的优点： 　第一，通过创建性索引，可以保证数据库表中每一行数据的性。 　第二，可以大大加快数据的检索速度，这也是创建索引的主要...
Mysql索引优化解决方案.doc
03-07
"Mysql索引优化解决方案" MySQL索引是帮助MySQL高效获取数据的数据结构。MySQL在存储数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种引用（指向）表中的数据，这样我们就可以通过数据结构上实现...
mysql索引优化.xmind
05-10
mysql索引优化思维导图
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
MySQL 结构的优化方案
boluomi的博客
07-05 796
主要是指三方面，即，这些结构如果不合理，在某些场景下也会影响数据库的性能，因此优化时也可以从结构层面出发。一般在项目的库表设计之初就要考虑，当性能瓶颈出现时再调整结构，就为时过晚。
零基础学习MySQL---库的相关操作
顾得泉的博客
07-04 1203
本文主要对于MySQL库的相关操作进行了详解。
MySQL之备份与恢复(十)
最新发布
Cover_sky的博客
07-07 1031
有各种各样的好的和不是那么好的备份工具。我们喜欢对LVM使用mylvmbackup做快照备份，使用Percona Xtrabackup(开源)或MySQL Enterprise Backup(收费)做InnoDB热备份。不建议对大数据量使用mysqldump,因为它对服务器有影响，并且漫长的还原时间不可预知。有一些备份工具已经出现多年了，不幸的是有些已经过时。最明显的例子是Maatkkit的mk-parallel-dump。它从没有正确运行，甚至被重新设计过好几次还是不行。
Mysql——子查询
weixin_59369438的博客
07-05 393
子查询Where子查询 单行单列 多行单列Form子查询 多行多列查询比SMITH工资高的人select * from emp where sal > (select sal from emp where ename = 'SMITH');查询比平均工资高的人select * from emp where sal > (select avg(sal) from emp);查询和经理工资相同的员工信息select * from emp where sal in (select sal from emp

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值