Mysql索引常见问题汇总

Q1:数据库有哪些索引?优缺点是什么?

1.B树索引:大多数数据库采用的索引(innoDB采用的是b+树)。能够加快访问数据的速度,尤其是范围数据的查找非常快。缺点是只能从索引的最左列开始查找,也不能跳过索引中的列,如果查询中有某个列用到了范围查询,则右边所有列都无法使用索引优化查找。

2.哈希索引:基于哈希表实现。在MySQL中,只有Memory引擎显式的支持哈希搜索。哈希查找的速度非常快,但哈希索引只包含哈希值和行指针,不存储字段值,所以不能用索引中的值来避免读取行,也不能进行排序。由于哈希索引使用的是索引列的全部内容来计算哈希值的,所以不支持部分所有列匹配查找。哈希只支持等值比较,不支持任何范围查询。一旦哈希冲突很多的话,维护成本非常高。innoDB支持“自适应哈希索引”(adaptive hash index)。

3.全文索引:全文索引是一种特殊类型的索引,它查找的是文本中的关键字,而不是比较索引的值。最初只能在MyISAM上使用,5.6.24以后innoDB也支持了全文索引。全文索引的查询要使用Match....against,在相同的列上同时创建全文搜索和基于值的B-Tree索引不会有冲突。

4.空间数据索引(R-tree索引),MyISAM支持R树索引,好处是无需前缀查询,会从所有纬度来索引数据,可以用作地理数据的存储;缺点是必须使用MySQL的GIS相关函数如MBRCONTAINS( )等来维护数据,但由于MySQL中的GIS并不完善,因此大多数人不会使用这个特性。

Q2:为什么不实用二叉查找树或者红黑树作为数据库索引。

二叉树在处理海量数据时,树的高度太高,虽然索引效率很高,达到logN,但会进行大量磁盘io,得不偿失。而且删除或者插入数据可能导致数据结构改变变成链表,需要增进平衡算法。而红黑树,插入删除元素的时候会进行频繁的变色和旋转(左旋,右旋),很浪费时间。但是当数据量很小的时候,完全可以放入红黑树中,此时红黑树的时间复杂性比b树低。因此,综上考虑,数据库最后选择了b树作为索引。

Q3:B tree和B+ tree应用场景:

1.B树常用于文件系统,和少部分数据库索引,比如mongoDB。

2.B+树主要用于mysql数据库索引。

Q4:B+ tree对比B tree的优点

B树的每个节点除了存储指向 子节点的索引外,还要存储data域,因此单一节点指向子节点的索引并不是很多,树的高度较高,磁盘io次数较多。B+树的高度更低,且所有data都存储在叶子节点,叶子节点都处于同一层,因此查询性能稳定,便于范围查找。

Q5:多列排序时使用索引的坑

A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

根据Mysql文档的说明,创建索引的时候,可以加上asc或者desc,例如:add index idx(a asc,b desc).但是实际Mysql是会忽略的(好坑。。。)好像8.0版本之后支持desc了。

这会有什么影响呢?

假如有列test1和test2,都是int类型。

我们创建索引``idx1(test1,test2),
假如我们要按test1和test2排序,例如SQL

explain select * from table order by test1 ,test2 limit 1;

可以使用索引的排序:

  • order by test1
  • order by test1 desc
  • order by test1,test2
  • order by test1 desc,test2 desc

不可以使用索引的排序:

  • order by test1,test2,desc
  • order by test1 desc,test2

因为索引不支持desc,所以多列的索引是按全部列的升序存储的。所以只排序一列,全部列升序,全部列降序,都能用索引。但是第一列用升序,第二列用降序,或者第一列降序,第二列用升级,都不能使用索引。

以上就是Mysql索引常见问题汇总的详细内容,更多关于MySQL 索引的资料请关注我们其它相关文章!

时间: 2020-10-15

分析Mysql表读写、索引等操作的sql语句效率优化问题

上次我们说到mysql的一些sql查询方面的优化,包括查看explain执行计划,分析索引等等.今天我们分享一些 分析mysql表读写.索引等等操作的sql语句. 闲话不多说,直接上代码: 反映表的读写压力 SELECT file_name AS file, count_read, sum_number_of_bytes_read AS total_read, count_write, sum_number_of_bytes_write AS total_written, (sum_number

mysql索引必须了解的几个重要问题

本文讲述了mysql索引必须了解的几个重要问题.分享给大家供大家参考,具体如下: 1.索引是做什么的? 索引用于快速找出在某个列中有一特定值的行.不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行. 表越大,花费的时间越多.如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据. 大多数MySQL索引(PRIMARY KEY.UNIQUE.INDEX和FULLTEXT)在B树中存储.只是空间列类型的索引使用R-树,并且MEMORY

mysql中索引与FROM_UNIXTIME的问题

零.背景 这周四收到很多告警,找DBA看了看,发现有个慢查询. 简单收集一些信息后,发现这个慢查询问题隐藏的很深,问了好多人包括DBA都不知道原因. 一.问题 有一个DB, 有一个字段, 定义如下. MySQL [d_union_stat]> desc t_local_cache_log_meta; +----------------+--------------+------+-----+---------------------+ | Field | Type | Null | Key |

分析MySQL中索引引引发的CPU负载飙升的问题

收到一个mysql服务器负载告警,上去一看,load average都飙到280多了,用top一看,CPU跑到了336%,不过IO和内存的负载并不高,根据经验,应该又是一起索引引起的惨案了. 看下processlist以及slow query情况,发现有一个SQL经常出现,执行计划中的扫描记录数看着还可以,单次执行耗时为0.07s,还不算太大.乍一看,可能不是它引发的,但出现频率实在太高,而且执行计划看起来也不够完美: mysql> explain SELECT count(1) FROM a

php mysql索引问题

显然这是一个凡是使用MySQL的朋友都会遇到的问题. 回忆一下当初在配置环境时提到的MySQL GUI工具,对了,就是它,大家可以到http://www.mysql.com中找到它.因为sunec也只是一名程序爱好者,远没有达到程序员水平,所以借助一些工具的帮助还是很有必要的~相信一些朋友也和我一样~ 用GUI工具创建表,建立REF字段,把REF设置为主键,定义类型为int,并在AUTO INC选项上打勾.好了,在MySQL端的工作就结束了. 接下去就交给PHP了,用之前专题中介绍过的inser

解决MySQL中IN子查询会导致无法使用索引问题

今天看到一篇关于MySQL的IN子查询优化的案例, 一开始感觉有点半信半疑(如果是换做在SQL Server中,这种情况是绝对不可能的,后面会做一个简单的测试.) 随后动手按照他说的做了一个表来测试验证,发现MySQL的IN子查询做的不好,确实会导致无法使用索引的情况(IN子查询无法使用所以,场景是MySQL,截止的版本是5.7.18) MySQL的测试环境 测试表如下 create table test_table2 ( id int auto_increment primary key, p

Mysql索引性能优化问题解决方案

mysql 创建的优化就是加索引,可是有时候会遇到加索引都没法达到想要的效果的情况, 加上了所以,却还是搜索的全数据,原因是sql EXPLAIN SELECT cs.sid, -- c.courseFrontTitle, -- c.imgBig, cs.studyStatus, coi.fee, -- act.PROC_INST_ID_ AS processId, cs.createDTM, cs.payStatus, cs.isCompleted, cs.saleChannel, cs.is

MySQL批量插入和唯一索引问题的解决方法

MySQL批量插入问题 在开发项目时,因为有一些旧系统的基础数据需要提前导入,所以我在导入时做了批量导入操作 ,但是因为MySQL中的一次可接受的SQL语句大小受限制所以我每次批量虽然只有500条,但依然无法插入,这个时候代码报错如下: nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576). You can change this va

MySql批量插入优化Sql执行效率实例详解

MySql批量插入优化Sql执行效率实例详解 itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志. updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},

MySQL批量插入数据脚本

MySQL批量插入数据脚本 #!/bin/bash i=1; MAX_INSERT_ROW_COUNT=$1; while [ $i -le $MAX_INSERT_ROW_COUNT ] do mysql -uroot -proot dbname -e "insert into tablename (name,age,createTime) values ('HELLO$i',$i % 99,NOW());" d=$(date +%M-%d\ %H\:%m\:%S) echo &qu

Yii框架批量插入数据扩展类的简单实现方法

本文实例讲述了Yii框架批量插入数据扩展类的简单实现方法.分享给大家供大家参考,具体如下: MySQL INSERT语句允许插入多行数据,如下所示: INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); 那么要实现批量插入,主要的任务就是按照列顺序,把数据组装成上述格式即可,可以使用sprintf和vsprintf函数来实现. 下面是一个实现批量插入的Yii扩展类的简单示例(支持VARCHAR类型数据): <?php /** *

javascript使用for循环批量注册的事件不能正确获取索引值的解决方法

本文实例讲述了javascript使用for循环批量注册的事件不能正确获取索引值的解决方法.分享给大家供大家参考.具体分析如下: 可能不少朋友会遇到一个问题,那就是当使用for循环批量注册事件处理函数,然后最后通过事件处理函数获取当前元素的索引值的时候会失败,先看一段代码实例: 复制代码 代码如下: <!DOCTYPE html> <html> <head> <meta charset=" utf-8"> <meta name=&q

MySQL无法创建外键的原因及解决方法

关联2张表时出现了无法创建外键的情况,从这个博客看到,问题出在第六点的Charset和Collate选项在表级和字段级上的一致性上.我的2张表的编码charset和collate不一致,2张表都执行执行SQL语句: alter table 表名 convert to character set utf8; 完美解决问题: ps:下面看下MySQL无法创建外键.查询外键的属性 MyISAM 和InnoDB 讲解 InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各

EF(EntityFramework) 插入或更新数据报错的解决方法

报错信息:Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and ha

window系统mysql无法输入和无法显示中文的解决方法

第一步:使用记事本打开mysql安装目录下的"my.ini"文件. # MySQL client library initialization. [client] port=3306 [mysql] default-character-set=utf8 看看default-character-set 是不是 utf8不是的话 改为utf8即可!(以前的版本可能没有这句话 直接加上就好了!) 第二步:在mysql数据库cmd中输入:show variables like'%char%';

MySql中表单输入数据出现中文乱码的解决方法

 MySQL会出现中文乱码的原因在于 1.server本身设定问题,一般来说是latin1 2.建库建表时没有制定编码格式. MySql中表单输入数据出现中文乱码的解决方法: 1.建库的时候 CREATE DATABASE test CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; 2.建表的时候 CREATE TABLE content ( text VARCHAR(100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

使用Python脚本实现批量网站存活检测遇到问题及解决方法

做渗透测试的时候,有个比较大的项目,里面有几百个网站,这样你必须首先确定哪些网站是正常,哪些网站是不正常的.所以自己就编了一个小脚本,为以后方便使用. 具体实现的代码如下: #!/usr/bin/python # -*- coding: UTF-8 -*- ''' @Author:joy_nick @博客:http://byd.dropsec.xyz/ ''' import requests import sys f = open('url.txt', 'r') url = f.readline