Mysql索引及索引的优化

最新推荐文章于 2022-07-12 14:39:43 发布
最新推荐文章于 2022-07-12 14:39:43 发布
阅读量94 收藏
点赞数
分类专栏： Mysql数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42262357/article/details/97528685
版权

一、索引是什么？

索引创建在表上，对数据库表中的一列或多列的值进行排列的一个结果。它是表中一列或多列值得集合和相应的指向表中物理标识这些值得数据页的逻辑指针清单。
优点：提高查询效率
缺点：过多的索引会导致CPU使用率降低
由于数据的改动会影响索引的改动，过多的索引会引起磁盘IO频繁造成CPU的负载太重

二、索引的种类

普通索引：没有任何限制条件，可以给任意类型的字段添加普通索引
唯一性索引：使用unique修饰的字段，值不能重复的，主键索引就隶属于唯一性索引
主键索引：使用primary key修饰的字段自动创建索引
单列索引：在一个字段上创建的索引
多列索引：在表的多个字段上创建的索引
全文索引：使用FULLTEXT参数可以设置全文索引，只支持char、varchar、text类型的字段上，常用于数据量比较大的字符创类型上，可以提高查询速度，只有MYISAM存储引擎支持

三、索引的创建和删除的SQL

1、创建表的时候创建索引

create table table_name(
id int,
name varchar,
index(id)
);

2、删除索引

drop index 索引名 on 表名;

3、在已经创建的表上添加索引

1.create [unique|fulltext|primary key|spatial] 
index 索引名（idx_name） on 表名（属性名）;


2. alter table 表名 add [unique|fulltext|primary key|spatial] 
index 索引名(属性);

四、索引执行的过程

1.创建对应的表：Student
Student(SID,Sname,Sage,Ssex)
–SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别

2.添加数据

+-----+----------+------+------+
| SID | Sname    | Ssex | Sage |
+-----+----------+------+------+
|   1 | zhaolei  | nan  |   20 |
|   2 | qiandian | nan  |   20 |
|   3 | sunfen   | nan  |   21 |
|   4 | wulan    | nv   |   18 |
+-----+----------+------+------+

3.查询姓名为"zhaolei"的同学信息
select * from Student where Sname=‘zhaolei’;

mysql> explain select * from Student where Sname='zhaolei'\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: Student
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra: Using where
    1 row in set (0.01 sec)

通过explain分析SQL的执行过程：possible_keys、key和执行过程中使用索引相关的字段，可以看到查询没命中索引。
rows: 4 表示是查询了4条数据，即进行了全表查询。当数据量比较大（400万），全表查询的效率是很低的。

给name字段添加索引
create index idx_name on Student(Sname);

mysql> explain select * from Student where Sname='zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 27
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

添加索引之后，再次查询"zhaolei"信息只在表中查询一行数据就可以得到了，而且可以看到：
当前执行命中的idx_name的索引的。

注意：explain命令：可以查看SQL的执行计划，分析SQL是否是正确使用索引

五、SQL和索引的优化原理

1、单表查询执行过程和执行优化-简单查询

查询sid=1的学生信息
explain select * from Student where SID = 1;

mysql> explain select * from Student where SID = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: const
possible_keys: PRIMARY,SID
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

可以看到：possible_keys: PRIMARY,SID，在SQL执行过程用主键索引进行查找key: PRIMARY，在主键索引上一条数据就可以查到结果

通过name=‘zhaolei’;
explain select * from Student where Sname = ‘zhaolei’;

mysql> explain select * from Student where Sname = 'zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 27
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

where 后跟的是 Sname = ‘zhaolei’,分析能够通过索引idx_name辅助索引树找到name='zhaolei’对应的主键
，到主键索引上查询主键为1找到对应的一列数据返回

explain select SID from Student where Sname=‘zhaolei’;

mysql> explain select SID from Student where Sname='zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 27
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

可以分析，where 后跟的是 Sname = ‘zhaolei’,分析能够通过索引idx_name辅助索引树找到name='zhaolei’对应的主键
，select 查询的是SID，在辅助索引就已经满足条件了，所以不需要在主键索引上再次查找

explain select Sage from Student where Sname=‘zhaolei’;

mysql> explain select Sage from Student where Sname='zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 27
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

可以分析，where 后跟的是 Sname = ‘zhaolei’,分析能够通过索引idx_name辅助索引树找到name='zhaolei’对应的主键
，select 查询的是Sage，Sage对应在主键索引上，需要通过主键索引来获取到当前一列值中的Sage。

2、单表查询执行过程和执行优化-简单查询+排序或者分组

创建订单表：
orderlist（）;
userid 用户ID
productid 商品ID
date 时间;

create table order(
userid int not null,
productid int not null,
date datatime
);



| userid | productid | date                |
+--------+-----------+---------------------+
|      1 |         1 | 2019-03-30 10:56:20 |
|      2 |         1 | 2019-03-30 14:56:20 |
|      1 |         2 | 2019-03-30 13:56:20 |
|      2 |         4 | 2019-03-30 12:56:20 |
|      1 |         5 | 2019-03-30 11:56:20 |
+--------+-----------+---------------------+

查询userID=1按照日期升序排序
select * from orderlist where userid=1 order by date;
同一个SQL查询过程中，只会命中一个索引

idx_id_date on orderlist(userid,date)|(date, userid);

eg:表 a\b都创建了索引
select * from test where a=1 and b= 1 and c=1;
select * from test where b=1 and c= 1 and a=1;

联合索引（a,b）
select * from test where b=1 ;
select * from test where a=1 ;
select * from test where a=1 and b = 1;

3、多表查询-连接查询的执行过程以及优化

创建两个表及其数据如下：

mysql> select * from Student;
+-----+----------+------+------+
| SID | Sname    | Ssex | Sage |
+-----+----------+------+------+
|   1 | zhaolei  | nan  |   20 |
|   2 | qiandian | nan  |   20 |
|   3 | sunfen   | nan  |   21 |
|   4 | wulan    | nv   |   18 |
+-----+----------+------+------+
4 rows in set (0.00 sec)

SC：

+------+------+-------+
| SID  | CID  | score |
+------+------+-------+
|    1 |    1 |    80 |
|    1 |    3 |    87 |
|    2 |    2 |    89 |
|    6 |    1 |    66 |
|    3 |    3 |    88 |
|    2 |    2 |    98 |
+------+------+-------+

联表查询
explain select * from Student a inner join SC b on a.SID=b.SID\G

mysql> explain select * from Student a inner join SC b on a.SID=b.SID\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: PRIMARY,SID   索引没有用上，进行全表搜索
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b  大表
         type: ref
possible_keys: idx_id  命中索引，提高查询效率    
          key: idx_id
      key_len: 5
          ref: test.a.SID
         rows: 1
        Extra: NULL
2 rows in set (0.00 sec)

在使用多表进行联合查询时，MYSQL会首先判断那个表小，
表小指的是行数少，连接查询时：小表总是要整表搜索的，建索引是没有用的。
大表创建索引可以提高查询效率。小表决定了查询次数，大表决定了查询时间

查询score=88分的用户信息
explain select * from Student a inner join SC b on a.SID = b.SID where b.score = 88\G
select * from Student where SID in (select SID from SC);

六、索引优化总结

1、何时使用索引（每次查询只寻找一个索引）

（1）主键，unique字段；
（2）和其他表做连接的字段需要加索引
（3）在where里使用＞，≥，＝，＜，≤，is null和between等字段；
（4）使用不以通配符开始的like，where A like ‘China%’；
（5）聚集函数MIN()，MAX()中的字段；
（6）order by和group by字段；

2、何时不使用索引

（1）表记录太少；
（2）数据重复且分布平均的字段（只有很少数据值的列）；
（3）经常插入、删除、修改的表要减少索引；
（4）text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立引）；
（5）MySQL能估计出全表扫描比使用索引更快时，不使用索引；

3、索引何时失效

（1）组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；
（2）like未使用最左前缀，where A like ‘%China’；
（3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；
（4）or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
（5）如果列类型是字符串，要使用引号。例如where A=‘China’，否则索引失效（会进行类型转换）；
（6）在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

4.SQL 语句的优化

1)利用慢查询日志找出响应较慢的SQL语句
2)explain语句查看这些较慢语句的执行计划（包括可能用到的索引和真正使用的索引）
3)利用like“%”查询找出冗余索引，对其进行分析优化 4)拆分大表，增加中间表（依赖三大范式） 第一范式：字段不可分割。
第二范式：所有字段不能依赖于非主属性 第三范式：所有字段必须依赖于主键列
2.MySQL Server服务器的参数进行优化(可以配置服务器线程的数量, 查询缓存)
3.对应用的优化
a.减少和MySQL Server建立连接和断开链接的额外消耗，引入连接池
b.减少MySQL Server的磁盘I/O操作，利用redis和memcached缓存机制

sdzrx
关注
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
SQL 语句优化
03-03 838
Sql优化是一项复杂的工作，以下的一些基本原则是本人看书时所记录下来的，很明确且没什么废话：1． 索引的使用：（1）.当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。（2）.避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。如：低效：select * fr
mysql哪种索引查询最快_02.你猜MySQL哪种索引快？
weixin_35731754的博客
01-19 2544
猜想MySQL哪种索引快？聚簇索引 or 非聚簇索引？试验用表# 这是一个普通的表小tCREATE TABLE `t` (`id` int(10) NOT NULL COMMENT '大家好，我是主键',`a` int(11) DEFAULT NULL COMMENT '大家好，我是字段a，我是一个普通索引',`b` int(11) DEFAULT NULL COMMENT '我是一个字段，我没有...
MySQL函数find_in_set优化使用
weixin_33725239的博客
08-14 9257
1、find_in_set()问题 find_in_set会使用全表扫描，导致查询效率很低 2、改进之前的语句 select * from `persons` where `logout` = '0' and FIND_IN_SET(unitcode, getChildList('%', 1)); ---query time ---2.5s 复制代码3、改进之后的语句 select * from ...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL索引优化
mz474920631的博客
06-16 1万+
这里说一下几种常见优化索引的方法：前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。不过，前缀索引有一定的局限性，例如：覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表
mysql 索引优化
GuoWangWang11的博客
07-12 3846
mysql索引优化
mysql索引优化
LJJ1338的博客
09-10 2006
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索引优化总结
何哥的博客
09-30 7473
前言：相信大家都知道索引可以大大提高MySQL的检索速度，但是真正在平时工作中写SQL，真的会考虑到这条SQL如何能够用上索引提升执行效率？本篇博客详细的介绍了索引优化的20个原则，只要在工作中能够随时应用到，相信你写出的SQL能够命中索引，效率杠杠的。 一、索引的分类 1.1、普通索引、主键索引和唯一索引 1.2、聚集索引和非聚集索引 1.3、联合索引最左匹配原则 1.4、索引覆盖和回表 1.5、前缀索引 1.6、索引合并 1.7、索引下推 二、索引优化规则 查询的条...
mysql索引优化助记口诀
张步云的专栏
12-25 3008
口诀 不等not null还有or，索引全中也无效； 大等小等is null，全索命中才有效； 除此全匹最高效，最左匹配要记牢； 大小跳索百分号，后面用索也无效； 索无函数和计算，才能保证索有效。 解释 sql中只要包含了!= not null or ，会导致sql中全部索引失效。但是 is null >= <= 不会。 >= <= is null只会有两种情况：只有在命中了全部索引，也就是组合索引中的索引全用到了才会都有效并且是全部有效，否则就全无效。 组合索引中除了上面的特例外，
Mysql索引步骤及优化
12-14
MySQL索引是数据库管理系统中用于加速数据检索的重要工具。它的工作原理类似于书籍的目录，能够快速定位到数据表中的特定记录。索引分为聚簇索引和非聚簇索引两类。聚簇索引根据数据的物理存储位置进行排序，而非聚...
MySQL索引原理及慢查询优化
12-14
总的来说，优化MySQL的慢查询需要结合索引原理、查询优化技巧、数据库设计和运维实践。通过对业务需求的理解，我们可以制定合适的索引策略，改进SQL语句，从而提升数据库的整体性能，满足高并发、大数据量的业务需求...
MySQL索引原理及慢查询优化1
08-08
MySQL索引原理及慢查询优化是数据库管理中的重要主题，尤其是在高并发、大数据量的互联网环境中，优化查询性能对于系统的整体效能至关重要。MySQL作为广泛使用的开源关系型数据库，其索引机制和查询优化技巧是开发者...
考研复习-英语二真题考试题集-带答案
09-14
英语二考研真题复习资料，带答案版
2024中美独角兽公司发展分析报告.pdf
09-14
全球各大洲独角兽企业分布、中美独角兽企业对比（数量、估值、新增及退榜情况、行业分布、所在城市）、
C++ 中的异步编程模型是什么
09-14
在C++中，异步编程模型是处理并发任务、提高程序性能和响应性的关键技术。以下是C++中实现异步编程的几种主要方式： 每种异步编程模型都有其适用场景和优缺点。选择合适的模型可以提高代码的可读性、可维护性和性能。随着C++标准的不断发展，异步编程模型也在不断进化，为开发者提供了更多的工具和选择。 在实际开发中，应根据具体需求选择合适的异步编程模型。例如，对于简单的异步任务，回调函数可能是最直接的选择；而对于需要结构化错误处理和结果获取的复杂异步任务，std::async和std::future可能更合适；在需要高效资源管理的场景下，线程池是一个不错的选择；而对于需要编写大量异步代码的现代应用程序，协程提供了一种更简洁、更直观的解决方案。 总之，C++中的异步编程模型是多核和高并发环境下提高程序性能的重要工具。通过合理使用这些模型，开发者可以构建出更高效、更可靠的软件系统。
正则表达式Regex是一种文本模式.docx
最新发布
09-14
正则表达式（Regular Expression，简称Regex或Regexp）是一种文本模式，包括普通字符（例如，a 到 z 之间的字母）和特殊字符（称为"元字符"）。正则表达式使用单个字符串来描述、匹配一系列符合某个句法规则的字符串。正则表达式是强大的文本处理工具，广泛用于搜索、编辑或操作文本和数据。 基本组成 普通字符：大多数字符，包括所有大写和小写字母、所有数字、所有标点符号和一些其他符号，都是普通字符。正则表达式中的普通字符表示它们自身。例如，正则表达式test会匹配字符串"test"。 特殊字符（元字符）：一些字符在正则表达式中具有特殊的意义，如^、$、.、*、+、?、{、}、[、]、|、\等。这些特殊字符用于表示在搜索文本时要匹配的一个或多个字符。例如，.匹配除换行符之外的任何单个字符。 字符类：字符类允许你指定一组字符中的任何一个字符。例如，[abc]匹配"a"、"b"或"c"中的任意一个字符。你也可以使用范围，如[a-z]匹配任何小写字母。 预定义字符类：正则表达式提供了一些预定义字符类，用于匹配常见的字符集合。例如，\d匹配任何数字（等价于[0-9]），\s匹配任
基于struts+sqlserver网络购物系统毕业课程源码设计+论文资料
09-14
编号：150 系统功能： 展示网站最新的商品信息。 展示网站特价的商品信息。 为用户提供修改个人资料和查看在网站操作情况的平台。 提供用户在网站上购物的平台。 展示网站发布的公告信息。 展示商品的销量排行。 展示网站的友情链接信息。 对商品详细信息以及分类信息进行管理。 对用户基本资料、交易制度、消费情况及留言信息进行管理。 对用户提交的订单进行管理。 对管理员信息、网站公告信息、商业资讯信息及友情链接信息进行管理。 系统运行稳定，具有强大的数据处理能力。 操作注意事项 （1）本系统的用户名为：admin，密码为：admin （2）admin管理员的信息不能删除。 （3）用户注册登录后，可进行商品购买、商品信息查看以及订单查询操作。 （4）后台登陆地址：http://localhost:8080/WebShopping/bg-land.jsp。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值