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

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

热门文章

暂无图片
编程学习 ·

那些年让我们目瞪口呆的bug

程序员一生与bug奋战&#xff0c;可谓是杀敌无数&#xff0c;见怪不怪了&#xff01;在某知识社交平台中&#xff0c;一个“有哪些让程序员目瞪口呆的bug”的话题引来了6700多万的阅读&#xff0c;可见程序员们对一个话题的敏感度有多高。 1、麻省理工“只能发500英里的邮件” …
暂无图片
编程学习 ·

redis的下载与安装

下载redis wget http://download.redis.io/releases/redis-5.0.0.tar.gz解压redis tar -zxvf redis-5.0.0.tar.gz编译 make安装 make install快链方便进入redis ln -s redis-5.0.0 redis
暂无图片
编程学习 ·

《大话数据结构》第三章学习笔记--线性表(一)

线性表的定义 线性表&#xff1a;零个或多个数据元素的有限序列。 线性表元素的个数n定义为线性表的长度。n为0时&#xff0c;为空表。 在比较复杂的线性表中&#xff0c;一个数据元素可以由若干个数据项组成。 线性表的存储结构 顺序存储结构 可以用C语言中的一维数组来…
暂无图片
编程学习 ·

对象的扩展

文章目录对象的扩展属性的简洁表示法属性名表达式方法的name属性属性的可枚举性和遍历可枚举性属性的遍历super关键字对象的扩展运算符解构赋值扩展运算符AggregateError错误对象对象的扩展 属性的简洁表示法 const foo bar; const baz {foo}; baz // {foo: "bar"…
暂无图片
编程学习 ·

让程序员最头疼的5种编程语言

世界上的编程语言&#xff0c;按照其应用领域&#xff0c;可以粗略地分成三类。 有的语言是多面手&#xff0c;在很多不同的领域都能派上用场。大家学过的编程语言很多都属于这一类&#xff0c;比如说 C&#xff0c;Java&#xff0c; Python。 有的语言专注于某一特定的领域&…
暂无图片
编程学习 ·

写论文注意事项

参考链接 给研究生修改了一篇论文后&#xff0c;该985博导几近崩溃…… 重点分析 摘要与结论几乎重合 这一条是我见过研究生论文中最常出现的事情&#xff0c;很多情况下&#xff0c;他们论文中摘要部分与结论部分重复率超过70%。对于摘要而言&#xff0c;首先要用一小句话引…
暂无图片
编程学习 ·

安卓 串口开发

上图&#xff1a; 上码&#xff1a; 在APP grable添加 // 串口 需要配合在项目build.gradle中的repositories添加 maven {url "https://jitpack.io" }implementation com.github.licheedev.Android-SerialPort-API:serialport:1.0.1implementation com.jakewhart…
暂无图片
编程学习 ·

2021-2027年中国铪市场调研与发展趋势分析报告

2021-2027年中国铪市场调研与发展趋势分析报告 本报告研究中国市场铪的生产、消费及进出口情况&#xff0c;重点关注在中国市场扮演重要角色的全球及本土铪生产商&#xff0c;呈现这些厂商在中国市场的铪销量、收入、价格、毛利率、市场份额等关键指标。此外&#xff0c;针对…
暂无图片
编程学习 ·

Aggressive cows题目翻译

描述&#xff1a; Farmer John has built a new long barn, with N (2 < N < 100,000) stalls.&#xff08;John农民已经新建了一个长畜棚带有N&#xff08;2<N<100000&#xff09;个牛棚&#xff09; The stalls are located along a straight line at positions…
暂无图片
编程学习 ·

剖析组建PMO的6个大坑︱PMO深度实践

随着事业环境因素的不断纷繁演进&#xff0c;项目时代正在悄悄来临。设立项目经理转岗、要求PMP等项目管理证书已是基操&#xff0c;越来越多的组织开始组建PMO团队&#xff0c;大有曾经公司纷纷建造中台的气质&#xff08;当然两者的本质并不相同&#xff0c;只是说明这个趋势…
暂无图片
编程学习 ·

Flowable入门系列文章118 - 进程实例 07

1、获取流程实例的变量 GET运行时/进程实例/ {processInstanceId} /变量/ {变量名} 表1.获取流程实例的变量 - URL参数 参数需要值描述processInstanceId是串将流程实例的id添加到变量中。变量名是串要获取的变量的名称。 表2.获取流程实例的变量 - 响应代码 响应码描述200指…
暂无图片
编程学习 ·

微信每天自动给女[男]朋友发早安和土味情话

微信通知&#xff0c;每天给女朋友发早安、情话、诗句、天气信息等~ 前言 之前逛GitHub的时候发现了一个自动签到的小工具&#xff0c;b站、掘金等都可以&#xff0c;我看了下源码发现也是很简洁&#xff0c;也尝试用了一下&#xff0c;配置也都很简单&#xff0c;主要是他有一…
暂无图片
编程学习 ·

C语言二分查找详解

二分查找是一种知名度很高的查找算法&#xff0c;在对有序数列进行查找时效率远高于传统的顺序查找。 下面这张动图对比了二者的效率差距。 二分查找的基本思想就是通过把目标数和当前数列的中间数进行比较&#xff0c;从而确定目标数是在中间数的左边还是右边&#xff0c;将查…
暂无图片
编程学习 ·

项目经理,你有什么优势吗?

大侠被一个问题问住了&#xff1a;你和别人比&#xff0c;你的优势是什么呢? 大侠听到这个问题后&#xff0c;脱口而出道&#xff1a;“项目管理能力和经验啊。” 听者抬头看了一下大侠&#xff0c;显然听者对大侠的这个回答不是很满意&#xff0c;但也没有继续追问。 大侠回家…
暂无图片
编程学习 ·

nginx的负载均衡和故障转移

#注&#xff1a;proxy_temp_path和proxy_cache_path指定的路径必须在同一分区 proxy_temp_path /data0/proxy_temp_dir; #设置Web缓存区名称为cache_one&#xff0c;内存缓存空间大小为200MB&#xff0c;1天没有被访问的内容自动清除&#xff0c;硬盘缓存空间大小为30GB。 pro…
暂无图片
编程学习 ·

业务逻辑漏洞

身份认证安全 绕过身份认证的几种方法 暴力破解 测试方法∶在没有验证码限制或者一次验证码可以多次使用的地方&#xff0c;可以分为以下几种情况︰ (1)爆破用户名。当输入的用户名不存在时&#xff0c;会显示请输入正确用户名&#xff0c;或者用户名不存在 (2)已知用户名。…