数据库索引的设计与优化(一)

1、索引的原理

首先要先了解索引是什么,例如这样一个查询:select * from table1 where id=10000。如果没有索引,必须遍历整个表,直到ID等于10000的这一行被找到为止;有了索引之后(必须是在ID这一列上建立的索引),即可在索引中查找。由于索引是经过某种算法优化过的,因而查找次数要少的多。可见,索引是用来定位的

2、索引的分类

索引分为聚簇索引非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引则不同;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。
MyISAM是非聚簇索引,B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。
InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,如果查找Name,会先用Name查找存储数据的id,再根据id获取整行数据。

mysql的索引根据功能分为单列索引主键索引,唯一索引,普通索引)和组合索引

  1. 主键索引:是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。
  2. 唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
  3. 普通索引:最基本的索引,它没有任何限制,用于加速查询。
  4. 组合索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合。

举个例子:建一个表CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );为了进一步榨取MySQL的效率,建立组合索引,将name,city,age建到一个索引里:ALTER TABLE mytable ADD INDEX name_city_age(name(10),city,age);

建表时,username的长度为16,这里用10.根据实际情况名字的长度一般不会超过10,这样会加速索引查询速度,减少索引文件的大小,提高INSERT的更新速度。

建立这样的组合索引,相当于分别建立了下面三组索引:
username;username city; username city age 根据MySQL组合索引最左前缀原则,所以并没有city age的组合。

3、索引的优化

1、Explain优化查询检测

explain 可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。
使用方法在select语句前加上explain:

Explain select * from logs where false;

mysql 在执行一条查询前,会对发出的每条SQL进行分析,决定是否使用索引或全表扫描。如果发送一条上述查询语句,Mysql是不会执行查询操作的,因为经过sql分析器的分析后Mysql已经清楚不会有任何语句符合操作。
实例

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; 
-- 结果: 
id: 1 

select_type: SIMPLE -- 查询类型(简单查询、联合查询、子查询) 

table: user -- 显示这一行的数据是关于哪张表的 。

type: range -- 区间索引(在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。 

possible_keys: birthday  -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。  

key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。 

key_len: 4 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好。

ref: const -- 显示哪个字段或常数与key一起被使用。  

rows: 1 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。 

Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using

select_type

  1. simple:简单select(不使用union或子查询)
  2. primary:最外面的select
  3. union:union中的第二个或后面的select语句
  4. dependent union:union中的第二个或后面的select语句,取决于外面的查询
  5. union result:union的结果
  6. subquery:子查询中的第一个select
  7. dependent subquery:子查询中的第一个select,取决于外面的查询
  8. derived:导出表的select(from子句的子查询)

其它查询结果项
其中type

  1. 如果是Only index,这意味着信息只用于索引树中的信息检索出来的,比扫描整个表要快
  2. 如果是where used,就是使用上了where的限制
  3. 如果是impossible where 表示用不着where,一般就是没查出来什么啥
  4. 如果此信息显示Using filesort或者Using temporary的话会很吃力,where和order by的索引经常无法兼顾,如果按照where来确定索引,那么在order by时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算
2、创建索引的技巧
  1. 维度高的列创建索引
  2. 数据列中不重复值出现的个数,这个数量越高,维度就越高
  3. 要为纬度高的列创建索引,如性别和年龄,那年龄的维度就高于性别,性别这样的列不适合创建索引,因为维度过低
  4. 对where,on,group by,order by中出现的列使用索引
  5. 对长度较小的数据列使用索引,这样会使索引的文件更小,同时内存中可以装载更多的索引键
  6. 为较长的字符串使用前缀索引
  7. 不要过多的创建索引,除了增加额外的磁盘空间外,对DML操作的速度影响很大,因为每增删改查一次就要重新建立索引
  8. 使用组合索引,可以减少文件索引的大小,在使用时速度要优于多个单列索引
3、什么样的sql不走索引
  1. 索引列参与了计算
  2. 索引列使用了函数运算
  3. LIKE中%在后边是走索引的,在前面不走索引
  4. 正则表达式不走索引
  5. 字符串与数字比较不使用索引,例如where ‘a’=“1” --走索引;where ‘a’=1 --不走索引
  6. 条件中使用or,除非使用的所有字段都建立索引,否则不会使用索引,建议尽量避免使用or关键字
  7. 如果mysql估计使用全表扫描比使用索引快,则不使用索引

4、索引的弊端

不能盲目创建索引,只为查询频繁的列创建索引,创建索引会使查询操作变得更加快速,但会降低增删改查操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新。
但是,在互联网应用汇中,查询语句远远大于DML语句,甚至可以占到80%~90%,也不用过于在意,只是在大量数据导入时,可以考虑先删除索引,再批量插入数据,最后再添加索引。

文章有参考:https://www.runoob.com/w3cnote/mysql-index.html

热门文章

暂无图片
编程学习 ·

Java输出数组的内容

Java输出数组的内容_一万个小时-CSDN博客_java打印数组内容1. 输出内容最常见的方式// List<String>类型的列表List<String> list new ArrayList<String>();list.add("First");list.add("Second");list.add("Third");list.ad…
暂无图片
编程学习 ·

母螳螂的“魅惑之术”

在它们对大蝗虫发起进攻的时候&#xff0c;我认认真真地观察了一次&#xff0c;因为它们突然像触电一样浑身痉挛起来&#xff0c;警觉地面对限前这个大家伙&#xff0c;然后放下自己优雅的身段和祈祷的双手&#xff0c;摆出了一个可怕的姿势。我被眼前的一幕吓到了&#xff0c;…
暂无图片
编程学习 ·

疯狂填词 mad_libs 第9章9.9.2

#win7 python3.7.0 import os,reos.chdir(d:\documents\program_language) file1open(.\疯狂填词_d9z9d2_r.txt) file2open(.\疯狂填词_d9z9d2_w.txt,w) words[ADJECTIVE,NOUN,VERB,NOUN] str1file1.read()#方法1 for word in words :word_replaceinput(fEnter a {word} :)str1…
暂无图片
编程学习 ·

HBASE 高可用

为了保证HBASE是高可用的,所依赖的HDFS和zookeeper也要是高可用的. 通过参数hbase.rootdir指定了连接到Hadoop的地址,mycluster表示为Hadoop的集群. HBASE本身的高可用很简单,只要在一个健康的集群其他节点通过命令 hbase-daemon.sh start master启动一个Hmaster进程,这个Hmast…
暂无图片
编程学习 ·

js事件操作语法

一、事件的绑定语法 语法形式1 事件监听 标签对象.addEventListener(click,function(){}); 语法形式2 on语法绑定 标签对象.onclick function(){} on语法是通过 等于赋值绑定的事件处理函数 , 等于赋值本质上执行的是覆盖赋值,后赋值的数据会覆盖之前存储的数据,也就是on…
暂无图片
编程学习 ·

Photoshop插件--晕影动态--选区--脚本开发--PS插件

文章目录1.插件界面2.关键代码2.1 选区2.2 动态晕影3.作者寄语PS是一款栅格图像编辑软件&#xff0c;具有许多强大的功能&#xff0c;本文演示如何通过脚本实现晕影动态和选区相关功能&#xff0c;展示从互联网收集而来的一个小插件&#xff0c;供大家学习交流&#xff0c;请勿…
暂无图片
编程学习 ·

vs LNK1104 无法打开文件“xxx.obj”

写在前面&#xff1a; 向大家推荐两本新书&#xff0c;《深度学习计算机视觉实战》和《学习OpenCV4&#xff1a;基于Python的算法实战》。 《深度学习计算机视觉实战》讲了计算机视觉理论基础&#xff0c;讲了案例项目&#xff0c;讲了模型部署&#xff0c;这些项目学会之后可以…
暂无图片
编程学习 ·

工业元宇宙的定义与实施路线图

工业元宇宙的定义与实施路线图 李正海 1 工业元宇宙 给大家做一个关于工业元宇宙的定义。对于工业&#xff0c;从设计的角度来讲&#xff0c;现在的设计人员已经做到了普遍的三维设计&#xff0c;但是进入元宇宙时代&#xff0c;就不仅仅只是三维设计了&#xff0c;我们的目…
暂无图片
编程学习 ·

【leectode 2022.1.15】完成一半题目

有 N 位扣友参加了微软与力扣举办了「以扣会友」线下活动。主办方提供了 2*N 道题目&#xff0c;整型数组 questions 中每个数字对应了每道题目所涉及的知识点类型。 若每位扣友选择不同的一题&#xff0c;请返回被选的 N 道题目至少包含多少种知识点类型。 示例 1&#xff1a…
暂无图片
编程学习 ·

js 面试题总结

一、js原型与原型链 1. prototype 每个函数都有一个prototype属性&#xff0c;被称为显示原型 2._ _proto_ _ 每个实例对象都会有_ _proto_ _属性,其被称为隐式原型 每一个实例对象的隐式原型_ _proto_ _属性指向自身构造函数的显式原型prototype 3. constructor 每个prot…
暂无图片
编程学习 ·

java练习代码

打印自定义行数的空心菱形练习代码如下 import java.util.Scanner; public class daYinLengXing{public static void main(String[] args) {System.out.println("请输入行数");Scanner myScanner new Scanner(System.in);int g myScanner.nextInt();int num g%2;//…
暂无图片
编程学习 ·

RocketMQ-什么是死信队列?怎么解决

目录 什么是死信队列 死信队列的特征 死信消息的处理 什么是死信队列 当一条消息初次消费失败&#xff0c;消息队列会自动进行消费重试&#xff1b;达到最大重试次数后&#xff0c;若消费依然失败&#xff0c;则表明消费者在正常情况下无法正确地消费该消息&#xff0c;此时…
暂无图片
编程学习 ·

项目 cg day04

第4章 lua、Canal实现广告缓存 学习目标 Lua介绍 Lua语法 输出、变量定义、数据类型、流程控制(if..)、循环操作、函数、表(数组)、模块OpenResty介绍(理解配置) 封装了Nginx&#xff0c;并且提供了Lua扩展&#xff0c;大大提升了Nginx对并发处理的能&#xff0c;10K-1000K Lu…
暂无图片
编程学习 ·

输出三角形

#include <stdio.h> int main() { int i,j; for(i0;i<5;i) { for(j0;j<i;j) { printf("*"); } printf("\n"); } }
暂无图片
编程学习 ·

stm32的BOOTLOADER学习1

序言 最近计划学习stm32的BOOTLOADER学习,把学习过程记录下来 因为现在网上STM32C8T6还是比较贵的,根据我的需求flash空间小一些也可以,所以我决定使用stm32c6t6.这个芯片的空间是32kb的。 #熟悉芯片内部的空间地址 1、flash ROM&#xff1a; 大小32KB&#xff0c;范围&#xf…
暂无图片
编程学习 ·

通过awk和shell来限制IP多次访问之学不会你打死我

学不会你打死我 今天我们用shell脚本&#xff0c;awk工具来分析日志来判断是否存在扫描器来进行破解网站密码——限制访问次数过多的IP地址&#xff0c;通过Iptables来进行限制。代码在末尾 首先我们要先查看日志的格式&#xff0c;分析出我们需要筛选的内容&#xff0c;日志…
暂无图片
编程学习 ·

Python - 如何像程序员一样思考

在为计算机编写程序之前&#xff0c;您必须学会如何像程序员一样思考。学习像程序员一样思考对任何学生都很有价值。以下步骤可帮助任何人学习编码并了解计算机科学的价值——即使他们不打算成为计算机科学家。 顾名思义&#xff0c;Python经常被想要学习编程的人用作第一语言…
暂无图片
编程学习 ·

蓝桥杯python-数字三角形

问题描述 虽然我前后用了三种做法&#xff0c;但是我发现只有“优化思路_1”可以通过蓝桥杯官网中的测评&#xff0c;但是如果用c/c的话&#xff0c;每个都通得过&#xff0c;足以可见python的效率之低&#xff08;但耐不住人家好用啊&#xff08;哭笑&#xff09;&#xff09…