MySQL索引

最新推荐文章于 2024-06-17 15:31:11 发布
最新推荐文章于 2024-06-17 15:31:11 发布
阅读量155 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36580691/article/details/103036355
版权

之前看了很多讲索引和优化的文章，自己也没咋用过。这里终于造了点数据。也来验证一下；
这里参考这篇文章https://blog.csdn.net/u012954706/article/details/81241049

第一部分

索引选取类型

  1. 越小的数据类型通常更好；越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
  2. 简单的数据类型更好：整型数据比起字符串处理开销更小，因为字符串的比较更复杂。
  3. 尽量避免null，应该制定列为not null，在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较 运算更加复杂

不适合建立索引的场景

  1. 对于在那些查询中很少使用或者参考的列不应该创建索引。因为这些列很少用到，因此有索引或者无索引，并不能提高查询速度。相反由于增加了索引，反而降低了系统的维护速度和增大了空间需求；
  2. 只有很少的数据值的列比如性别
  3. 数据类型为 text，image，bit
  4. 修改性能大于检索性能时，不应该创建索引
  5. 不会出现在where条件中的字段不应该建立索引

适合建索引的字段

  1. 表的主键（默认索引）外键
  2. 数据量超过300的表应该建立索引
  3. 经常与其它表进行连接的表，在连接字段上应该建立索引
  4. 经常出现在where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引
  5. 经常用到排序的列
  6. 经常用在范围内搜索的列上的索引

索引的优点

  1. 索引由数据库中一列或者多列组合而成，作用是提高对表中数据的查询速度
  2. 索引的优点是可以提高检索数据的速度

缺点

  1. 创建和维护索引需要消耗时间
  2. 会减慢写入速度
  3. 一个表的索引数最好不要超过6个

第二部分

1. SELECT 的时候，选择需要的列

SELECT * FROM user;
SELECT user_phone FORM user;

2. 主键是默认索引

-- id=1
SELECT * FROM `user` WHERE user_phone = '18756943707';
-- id=38w
SELECT * FROM `user` WHERE user_phone = '18697483918';
-- id=50w
SELECT * FROM `user` WHERE user_phone = '15089123074'; 
SELECT * FROM `user` WHERE user_id = 100000;
SELECT * FROM `user` WHERE user_id = 500000;


这里可以看见，主键查询的时间都差不多；
然后这里发现，不管手机号在哪查询时间都是一样的

关于limit 1

前面发现，手机号在哪个位置查询的时间都差不多，因为数据库并不知道有多少个符合条件的结果，所以进行了全表扫描

-- id=1
SELECT * FROM `user` WHERE user_phone = '18756943707' LIMIT 1;
SELECT * FROM `user` WHERE user_phone = '18756943707';
-- id=38w
SELECT * FROM `user` WHERE user_phone = '18697483918' LIMIT 1;
-- id=50w
SELECT * FROM `user` WHERE user_phone = '15089123074' LIMIT 1;

在这里插入图片描述
结果很明显，所以说，如果我们确定了数据只有一条，那么加一个limit 1

普通索引

SELECT * FROM `user` WHERE user_phone = '18756943707';
SELECT * FROM `user` WHERE user_id = 500000;

在这里插入图片描述
速度差不多，索引还是挺有用的

唯一索引

前面说普通索引不能有空值，我刚刚试了几个空值，感觉没啥影响啊，求解答
唯一索引允许一个空值

索引要求not null，解答2019-9-30
索引列

  1. 设置not null
  2. 不设置not null
    区别：不设置not null索引的key_len会多1个字节.数据少还好，上亿条记录就是上亿个字节
    查询null也不太方便
    end

联合索引

  1. 需要加索引的字段，要在where中
  2. 数据量少的字段不需要加索引
  3. 如果where条件中是or关系，加索引不起作用
  4. 符合最左原则
最左原则

联合索引又称符合索引，如果索引的 key index(a, b, c);那么可以支持 a| a, b| a, b, c|三种组合进行查找，但不支持 b, c 进行查找

这里我来试试

--创建一个联合索引
CREATE INDEX test_union ON `user`(user_phone, user_name, user_age);

SELECT * FROM `user` WHERE user_phone = '18657322009';
SELECT * FROM `user` WHERE user_phone = '18047537377' AND user_name = '柳桃' AND user_age=34;
SELECT * FROM `user` WHERE user_name = '柳桃' AND user_age = 34 AND user_phone = '18047537378';
SELECT * FROM `user` WHERE user_phone = '18047537377' OR user_name = '柳桃';
SELECT * FROM `user` WHERE user_name = '李永以';

求解答

上面的回答：MySQL查询优化器 2019-9-28日
索引的概念和作用
  1. 索引是一种使记录有序化的技术，它可以指定按照某列/某几列预先排序，从而大大提高查询效率
  2. 索引的主要作用是加快数据查找，提高数据库性能
MySQL索引优化规则
  1. 前导模糊查询不能使用索引,非前导模糊查询则可以使用索引 例如
-- 这里我只给`name`建立了一个索引， `user_phone`没有建立索引
SELECT * FROM `user` WHERE user_name = '李永以';
SELECT * FROM `user` WHERE user_phone = '18047537377';
SELECT * FROM `user` WHERE user_name = '李%';
SELECT * FROM `user` WHERE user_name LIKE '%以';
SELECT * FROM `user` WHERE user_phone LIKE '18047537377';

查询结果如下

  1. union, in, or 都能命中索引，建议使用in

union能够命中索引,直接告诉MySQL怎么做，MySQL耗费的cpu最少，但一般不这么写SQL[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wa0MP99h-1573556576969)(http://blog.xukai.ink/wp-content/uploads/2019/09/BD61F6D884DB78A4AF489535FA76D945.gif)]网上说的，我也不知道是不是这样

SELECT * FROM `user` WHERE user_age = 4
UNION ALL
SELECT * FROM `user` WHERE user_age = 5;

in 能够命中索引，查询优化耗费的cpu比union all多，但是可以忽略不计，一般情况建议使用in

SELECT * FROM `user` WHERE user_age IN(4, 5);

or 新版的MySQL（具体我也不知道哪个是新版MySQL）能够命中索引，查询优化耗费的cup比in多，不建议频繁使用or

SELECT * FROM `user` WHERE user_age = 4 OR user_age = 5;

在这里插入图片描述

  1. 负向条件查询不能使用索引，可以优化为in查询
-- 负向查询条件有
!=，<>，NOI IN，NOT EXISTS，NOT LIKE 等

这一条，我也不太清楚
在这里插入图片描述

  1. 范围列可以用到索引
<、<= 、 >、 >=、 between等

根据我的测试，emmm，貌似是用到了索引

SELECT * FROM `user` WHERE user_age BETWEEN 10 AND 20; -- 加索引 0.215s；删掉索引 0.227s  我也不太清楚，我好难
  1. 把计算放到业务层，而不是数据库层（在字段上进行计算，不能命中索引）
SELECT * FROM `user` WHERE (2019 - user_age) > 2000;
SELECT * FROM `user` WHERE user_age < 18;

这个，emmmm在这里插入图片描述
在这里插入图片描述

select * from order where deadline <= curdate();

-- 可以优化为
select * from order where deadline <= '2019-01-01 00:00:00';

传入的SQL相同，才可以利用查询缓存

  1. 强制类型转换，会全表扫描
select * FROM `user` WHERE user_phone = 15297450539;
select * FROM `user` WHERE user_phone = '15297450539';


8. 更新频繁，数据区分不高的字段不宜建立索引

  1. 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库的性能
  2. ‘性别’这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
  3. 一般区分度在80%以上的时候可以建立索引，区分度可以用count(distinct(列名)) / count(*)来计算
SELECT count(DISTINCT(user_age)) / count(*) user_age,count(DISTINCT(user_id)) / count(*) user_id,count(DISTINCT(user_phone)) / count(*) user_phone FROM `user`;

结果：

user_ageuser_iduser_phone
0.00021.00001.0000

原来是这个样子，我说上面的user_age怎么没啥用样的

今天先这么多了2019-9-23， 怎么一转眼就到了0点了，真快

掘金看看

小菜鸡__
关注
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
哪些情况适合建索引？哪些不适合？
chengjianghao的博客
02-25 3374
哪些情况适合建索引？ 1. 主键自动建立唯一索引。 2. 频繁作为查询条件的字段应该创建索引。 3.查询中与其他表关联的字段，外键关系建立索引。 4. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引文件。 5. where条件里用不到的字段不创建索引。 6. 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）。 7. 查询中排序的字段，排序字段...
不宜创建索引的情形
飞鸟之家
08-26 1135
<br />1.对于经常插入，修改和删除数据的表，不宜创建过多的索引。<br />   因为表中的数据发生变化时，索引都要做相应调整，索引越多，需要花费的时间也越多。<br /> <br />2.对于数据量比较小的表，不必创建索引。<br />  因为查询优化器在搜索索引时所花费的时间可能会大于遍历全表的数据所需要的时间。
MySQL索引&InnoDB存储引擎&聚簇索引、回表、索引下推、覆盖、前缀索引、最左匹配，索引失效、创建索引时机、不宜创建的列& SQL优化&查询执行过程&Index Key&Table filter
Be_insighted的博客
08-14 1059
MySQL之InnoDB存储引擎&各种索引说明 当前数据库版本： 存储引擎 存储引擎：基于表而非数据库 MySQL数据库不同于其他数据的一个重要特点： 插件式的表存储引擎 存储引擎表： InnoDB： 支持事务、行锁、外键、非锁定读、聚簇索引、 MyISAM：不支持事务、表锁、支持全文检索 查看mysql支持的引擎： -- 查看支持的存储引擎 SHOW ENGINES ​ -- 查看默认存储引擎 SHOW VARIABLES LIKE 'sto...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
mysql 索引
liulanba的博客
10-25 1万+
在 MySQL 中创建索引可以大大提高查询效率，加快数据检索速度，他的索引包含 普通索引和唯一索引，以及主键索引，联合索引。
MySQL 索引
weixin_68840588的博客
06-17 4466
介绍MySQL索引的相关知识点
Mysql 索引
Impassive_y的博客
04-26 1万+
图片来源网络，侵删 索引 Mysql 的索引类型有很多种，Hash索引，B树索引，B+树索引和全文索引。Mysql有多种存储引擎，每个存储引擎对索引的支持可能会不同。 What Mysql 索引是能改善数据库表随机访问速度的一种数据结构。可以通过指定一个或者多个指定列的方式创建索引。 所以索引是一种数据结构。 Why 当数据量达到一定程度的时候，如果根据顺序扫描全部的数据，会出现数据查询效果低，查询时间长，导致用户体验不佳。为了提高数据的访问的效率，引入了索引。 How 创建索引的时候，需要指定至少一列数据
MySQL索引详解
m0_73594607的博客
07-20 3832
本篇文章详细讲解了MySQL中索引的知识, 包括他的原理, 优缺点, 使用, 以及的底层数据结构.
分享几道关于MySQL索引的重点面试题
01-19
但MySQL 索引你真的懂吗？这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则？ 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话，采用B+树的结构，...
快速了解MySQL 索引
12-14
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引分...
ssm智能家政保洁预约系统最新完整版程序+论文.zip
10-18
基于SSM框架的智能家政保洁预约系统，是一个旨在提高家政保洁服务预约效率和管理水平的平台。该系统通过集成现代信息技术，为家政公司、家政服务人员和消费者提供了一个便捷的在线预约和管理系统。 系统的主要功能包括： 1. **用户管理**：允许消费者注册、登录，并管理他们的个人资料和预约历史。 2. **家政人员管理**：家政服务人员可以注册并更新自己的个人信息、服务类别和服务时间。 3. **服务预约**：消费者可以浏览不同的家政服务选项，选择合适的服务人员，并在线预约服务。 4. **订单管理**：系统支持订单的创建、跟踪和管理，包括订单的确认、完成和评价。 5. **评价系统**：消费者可以在家政服务完成后对服务进行评价，帮助提高服务质量和透明度。 6. **后台管理**：管理员可以管理用户、家政人员信息、服务类别、预约订单以及处理用户反馈。 系统采用Java语言开发，使用MySQL数据库进行数据存储，通过B/S架构实现用户与服务的在线交互。系统设计考虑了不同用户角色的需求，包括管理员、家政服务人员和普通用户，每个角色都有相应的权限和功能。此外，系统还采用了软件组件化、精化体系结构、分离逻辑和数据等方法，以便于未来的系统升级和维护。 智能家政保洁预约系统通过提供一个集中的平台，不仅方便了消费者的预约和管理，也为家政服务人员提供了一个展示和推广自己服务的机会。同时，系统的后台管理功能为家政公司提供了强大的数据支持和决策辅助，有助于提高服务质量和管理效率。该系统的设计与实现，标志着家政保洁服务向现代化和网络化的转型，为管理决策和控制提供保障，是行业发展中的重要里程碑。
东方财富网抓取上市公司爬虫-EMSpider.zip
最新发布
10-18
东方财富网抓取上市公司爬虫-EMSpider
卫生健康系统-java-基于springBoot智能推荐的卫生健康系统（毕业论文+开题）
10-18
本基于智能推荐的卫生健康系统有管理员和用户两个角色。用户功能有个人中心，健康视频管理，视频类型管理，科室类型管理，医生信息管理，在线预约管理，我的收藏管理等。管理员功能有个人中心，用户管理，健康视频管理，视频类型管理，科室类型管理，医生信息管理，在线预约管理，健康论坛管理，我的收藏管理，留言板管理，系统管理等。因而具有一定的实用性。 本站是一个B/S模式系统，采用Spring Boot框架，MYSQL数据库设计开发，充分保证系统的稳定性。系统具有界面清晰、操作简单，功能齐全的特点，使得基于智能推荐的卫生健康系统管理工作系统化、规范化。
pxe无人值守安装centos
10-18
pxe无人值守安装centos
毕业设计-基于微博情感分析系统Python实现源码+说明文档.zip
10-18
毕业设计_基于微博情感分析系统Python实现源码+说明文档.zip，该项目是个人毕设项目，答辩评审分达到98分，代码都经过调试测试，确保可以运行！欢迎下载使用，可用于小白学习、进阶。该资源主要针对计算机、通信、人工智能、自动化等相关专业的学生、老师或从业者下载使用，亦可作为期末课程设计、课程大作业、毕业设计等。项目整体具有较高的学习借鉴价值！基础能力强的可以在此基础上修改调整，以实现不同的功能。 毕业设计_基于微博情感分析系统Python实现源码+说明文档.zip毕业设计_基于微博情感分析系统Python实现源码+说明文档.zip毕业设计_基于微博情感分析系统Python实现源码+说明文档.zip毕业设计_基于微博情感分析系统Python实现源码+说明文档.zip毕业设计_基于微博情感分析系统Python实现源码+说明文档.zip毕业设计_基于微博情感分析系统Python实现源码+说明文档.zip毕业设计_基于微博情感分析系统Python实现源码+说明文档.zip
mysql索引
05-29
MySQL索引是一种数据结构，可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率，降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法： 1. 索引类型 MySQL支持多种类型的索引，包括B树索引、哈希索引、全文索引等。其中，B树索引是最常用的一种，也是默认的索引类型。B树索引可以用于精确匹配和范围查询，而哈希索引主要用于等值查询，全文索引则用于文本检索。 2. 索引创建 可以在创建表时指定索引，例如： ``` CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), INDEX idx_email (email) ); ``` 也可以在已有的表上添加索引，例如： ``` ALTER TABLE users ADD INDEX idx_name (name); ``` 3. 索引使用 查询语句中可以使用WHERE子句和ORDER BY子句来利用索引，例如： ``` SELECT * FROM users WHERE email = 'example@example.com'; SELECT * FROM users WHERE name LIKE 'John%' ORDER BY id DESC; ``` 需要注意的是，索引并不是越多越好，过多的索引会占用过多的磁盘空间并降低写操作的性能。因此，需要根据实际情况选择合适的索引。同时，还需要定期对索引进行维护，包括优化查询语句、删除不必要的索引等。 4. 索引优化 MySQL提供了一些工具来优化索引，例如EXPLAIN命令可以帮助分析查询语句的执行计划，找出慢查询和不必要的全表扫描。可以使用OPTIMIZE TABLE命令来优化表的索引和碎片，从而提高查询性能。还可以使用缓存来避免频繁的查询操作，例如使用Memcached或Redis等缓存工具。 以上就是MySQL索引的一些基本概念和使用方法，需要根据实际情况进行选择和优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值