数据库
mysql数据库中怎么创建索引?
一、mysql数据库中怎么创建索引?
在满足语句需求的情况下,尽量少的访问资源是数据库设计的重要原则,这和执行的 SQL 有直接的关系,索引问题又是 SQL 问题中出现频率最高的,常见的索引问题包括:无索引(失效)、隐式转换。1. SQL 执行流程看一个问题,在下面这个表 T 中,如果我要执行 需要执行几次树的搜索操作,会扫描多少行?
这分别是 ID 字段索引树、k 字段索引树。
这条 SQL 语句的执行流程:
1. 在 k 索引树上找到 k=3,获得 ID=3002. 回表到 ID 索引树查找 ID=300 的记录,对应 R33. 在 k 索引树找到下一个值 k=5,ID=5004. 再回到 ID 索引树找到对应 ID=500 的 R4
5. 在 k 索引树去下一个值 k=6,不符合条件,循环结束
这个过程读取了 k 索引树的三条记录,回表了两次。因为查询结果所需要的数据只在主键索引上有,所以必须得回表。所以,我们该如何通过优化索引,来避免回表呢?2. 常见索引优化2.1 覆盖索引覆盖索引,换言之就是索引要覆盖我们的查询请求,无需回表。
如果执行的语句是 ,这样的话因为 ID 的值在 k 索引树上,就不需要回表了。
覆盖索引可以减少树的搜索次数,显著提升查询性能,是常用的性能优化手段。
但是,维护索引是有代价的,所以在建立冗余索引来支持覆盖索引时要权衡利弊。
2.2 最左前缀原则
B+ 树的数据项是复合的数据结构,比如 的时候,B+ 树是按照从左到右的顺序来建立搜索树的,当 这样的数据来检索的时候,B+ 树会优先比较 name 来确定下一步的检索方向,如果 name 相同再依次比较 sex 和 age,最后得到检索的数据。
可以清楚的看到,A1 使用 tl 索引,A2 进行了全表扫描,虽然 A2 的两个条件都在 tl 索引中出现,但是没有使用到 name 列,不符合最左前缀原则,无法使用索引。所以在建立联合索引的时候,如何安排索引内的字段排序是关键。评估标准是索引的复用能力,因为支持最左前缀,所以当建立(a,b)这个联合索引之后,就不需要给 a 单独建立索引。原则上,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。上面这个例子中,如果查询条件里只有 b,就是没法利用(a,b)这个联合索引的,这时候就不得不维护另一个索引,也就是说要同时维护(a,b)、(b)两个索引。这样的话,就需要考虑空间占用了,比如,name 和 age 的联合索引,name 字段比 age 字段占用空间大,所以创建(name,age)联合索引和(age)索引占用空间是要小于(age,name)、(name)索引的。
2.3 索引下推
以人员表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是26岁的所有男性”。那么,SQL 语句是这么写的
通过最左前缀索引规则,会找到 ID1,然后需要判断其他条件是否满足在 MySQL 5.6 之前,只能从 ID1 开始一个个回表。到主键索引上找出数据行,再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown),可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。这样,减少了回表次数和之后再次过滤的工作量,明显提高检索速度。
2.4 隐式类型转化
隐式类型转化主要原因是,表结构中指定的数据类型与传入的数据类型不同,导致索引无法使用。所以有两种方案:
修改表结构,修改字段数据类型。
修改应用,将应用中传入的字符类型改为与表结构相同类型。
3. 为什么会选错索引3.1 优化器选择索引是优化器的工作,其目的是找到一个最优的执行方案,用最小的代价去执行语句。在数据库中,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
3.2 扫描行数
MySQL 在真正开始执行语句之前,并不能精确的知道满足这个条件的记录有多少条,只能通过索引的区分度来判断。显然,一个索引上不同的值越多,索引的区分度就越好,而一个索引上不同值的个数我们称为“基数”,也就是说,这个基数越大,索引的区分度越好。
MySQL 使用采样统计方法来估算基数:采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:
on 表示统计信息会持久化存储。默认 N = 20,M = 10。
off 表示统计信息只存储在内存中。默认 N = 8,M = 16。
由于是采样统计,所以不管 N 是 20 还是 8,这个基数都很容易不准确。所以,冤有头债有主,MySQL 选错索引,还得归咎到没能准确地判断出扫描行数。
可以用 来重新统计索引信息,进行修正。
3.3 索引选择异常和处理1. 采用 force index 强行选择一个索引。2. 可以考虑修改语句,引导 MySQL 使用我们期望的索引。3. 有些场景下,可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。
二、mysql添加索引mysql如何创建索引?
在MySQL中,创建索引的方法有两种:使用命令行工具或者使用MySQL图形化工具。以下是两种方法的详细步骤:
方法一:使用命令行工具
1. 登录到MySQL服务器。在命令行中输入以下命令:
```css
mysql -u 用户名 -p
```
其中,用户名是您的MySQL用户名。执行此命令后,系统将提示您输入密码。
2. 选择要创建索引的数据库。使用以下命令选择要创建索引的数据库:
```perl
use 数据库名;
```
其中,数据库名是您要创建索引的数据库名称。
3. 创建索引。使用以下命令创建索引:
```sql
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
```
其中,表名是要添加索引的表名称,索引名是您为索引指定的名称,列名是要添加索引的列名称。
例如,如果您要在名为"users"的表的"email"列上创建一个名为"idx_email"的索引,可以使用以下命令:
```sql
ALTER TABLE users ADD INDEX idx_email (email);
```
方法二:使用MySQL图形化工具
1. 启动MySQL图形化工具(如phpMyAdmin或MySQL Workbench)。
2. 连接到您的MySQL服务器。输入服务器地址、用户名和密码。
3. 选择要创建索引的数据库和表。在图形化工具中,您可以通过单击数据库名称来选择它,然后选择您要创建索引的表。
4. 创建索引。在工具栏或右键菜单中,选择"Alter Table"(更改表)选项。在弹出的对话框中,选择要添加索引的列,并设置索引名称和其他选项。单击"Apply"(应用)按钮以创建索引。
5. 等待图形化工具完成操作。在操作完成后,您可以验证索引是否成功创建。您可以通过执行以下查询来检查是否已成功创建索引:
```sql
SHOW INDEX FROM 表名;
```
其中,表名是您要检查索引的表名称。
三、mysql数据库删除记录重建索引吗?
会重建索引的,如果你删除的数据恰巧有索引指向它,索引就会重建
四、MySQL 索引排序规则?
索引的顺序要遵循三个规则
1.要遵循最左前缀 无论是多个还是一个列的索引 都不应该跳过最左列 如果在查询语句当中 没有使用最左前缀的字段 就不会使用索引
2.不能跨越索引列
3.索引进行模糊查询 范围查询 ,右边的所有列都无法使用索引优化
五、MySQL建立索引目的?
MySQL建立索引最简单的目的就是对数据库的访问会快一点。一个表,如果没有索引,数据量少点的时候你不会觉得数据库本身的的性能问题,但是随着数据量的显著增加,比如超过一万条记录之后,可能你就会遇到数据库操作的性能问题了,这个时候,你建立索引就会显著的改善数据库的写入性能。
六、mysql like 索引失效?
索引查询失效的几个情况:
1、like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效。
3、组合索引,不是使用第一列索引,索引失效。
4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
5、在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的,所以这样的操作不能使用索引,可以用其他的办法处理。
七、mysql索引底层原理?
一、定义
索引定义:索引(Index)是帮助MySQL高效获取数据的数据结构。本质:索引是数据结构。
二、B-Tree
m阶B-Tree满足以下条件:1、每个节点至多可以拥有m棵子树。2、根节点,只有至少有2个节点(要么极端情况,就是一棵树就一个根节点,单细胞生物,即是根,也是叶,也是树)。3、非根非叶的节点至少有的Ceil(m/2)个子树(Ceil表示向上取整,如5阶B树,每个节点至少有3个子树,也就是至少有3个叉)。4、非叶节点中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An],,其中n表示该节点中保存的关键字个数,K为关键字且Ki<Ki+1,A为指向子树根节点的指针。5、从根到叶子的每一条路径都有相同的长度(叶子节点在相同的层)
B-Tree特性:
1、关键字集合分布在整颗树中;2、任何一个关键字出现且只出现在一个节点中;3、每个节点存储date和key;4、搜索有可能在非叶子节点结束;5、一个节点中的key从左到右非递减排列;6、所有叶节点具有相同的深度,等于树高h。
B-Tree上查找算法的伪代码如下:
三、B+Tree
B+Tree与B-Tree的差异在于:1、B+Tree非叶子节点不存储data,只存储key;2、所有的关键字全部存储在叶子节点上;3、每个叶子节点含有一个指向相邻叶子节点的指针,带顺序访问指针的B+树提高了区间查找能力;4、非叶子节点可以看成索引部分,节点中仅含有其子树(根节点)中的最大(或最小)关键字;
四、B/B+树索引的性能分析
依据:使用磁盘I/O次数评价索引结构的优劣主存和磁盘以页为单位交换数据,将一个节点的大小设为等于一个页,因此每个节点只需一次I/O就可以完全载入。根据B树的定义,可知检索一次最多需要访问h个节点渐进复杂度:O(h)=O(logdN) dmax=floor(pagesize/(keysize+datasize+pointsize))一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小(通常不超过3,3层可存大约一百万数据)B-Tree中一次检索最多需要h-1次I/O(根节点常驻内存)B+Tree内节点不含data域,因此出度d更大,则h更小,I/O次数少,效率更高,故B+Tree更适合外存索引。
五、MySQL索引实现1、MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址; MyISAM主索引和辅助索引在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复;
2、InnoDB的数据文件本身就是索引文件,叶节点包含了完整的数据记录,这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键。 InnoDB的辅助索引data域存储相应记录主键的值而不是地址; 辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录;
3、页分裂问题
如果主键是单调递增的,每条新记录会顺序插入到页,当页被插满后,继续插入到新的页;
如果写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便为新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。
如果频繁的页分裂,页会变得稀疏并被不规则地填充,所以最终数据会有碎片。
六、总结
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助
1、为什么不建议使用过长的字段作为主键?
2、为什么选择自增字段作为主键?
3、为什么常更新是字段不建议建立索引?
4、为什么选择区分度高的列作为索引?区分度的公式是count(distinct col)/count(*)
5、尽可能的使用覆盖索引
七、优化LIMIT分页查询
SELECT * FROM table where condition LIMIT offset , rows ;上述SQL语句的实现机制是: 1、从“table”表中读取offset+rows行记录。 2、 抛弃前面的offset行记录,返回后面的rows行记录作为最终的结果。覆盖索引:select a.id, sid, parent_s_id from cashpool_account_relationship a join (select id from cashpool_account_relationship LIMIT 1000000,10)b on a.id = b.id;select id, sid, parent_s_id from cashpool_account_relationship where id >=(select id from cashpool_account_relationship LIMIT 1000000,1) LIMIT 10;
八、Q&A
1、InnoDB支持hash索引吗?--马欣InnoDB是支持hash索引的,不过其支持的hash索引是自适应的,InnoDB存储引擎会根据表的使用情况自动为表生成hash索引,不能人为干预是否在一张表中生成hash索引。2、InnoDB主键索引的叶节点含完整的数据记录,那主键索引文件要比数据文件大吗?--徐财厚1).在Innodb 引擎中,主键索引中的叶子结点包含记录数据,主键索引文件即为数据文件。2).在 tables 表中统计的data_length数据为主键索引大小,index_length 为统计的这个表中所有辅助索引(二级索引)索引的大小。
八、mysql索引生效时间?
mysql建立索引是立即生效的,语句生效后索引也就建立成功了,可以建立索引进行快速的查询和检索
九、mysql默认索引类型?
Hash索引:将索引字段转化为hashcode,在对hashcode进行排序。仅支持Memory引擎。
1. 普通索引:最基本的索引,它没有任何限制,用于加速查询。
2. 唯一索引unique:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
3. 主键索引: 是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。
4. 空间索引Spatial :空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建
十、mysql索引匹配规则?
1.最左前缀匹配原则, mysql会一只向右匹配直到遇到范围查询(>, <, between, like)就停止匹配, 比如a=1 and b=2 and c>3 and d=4 如果建立了(a,b,c,d)顺序的索引, d是用不到索引的, 如果建立(a,b,d,c)的索引, 则都可以使用到, a,b,d的顺序可以任意调整.
2.= 和 in 可以乱序, 比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序, mysql 的查询优化器会帮你优化成索引可以识别的形式.
热点信息
-
在Python中,要查看函数的用法,可以使用以下方法: 1. 使用内置函数help():在Python交互式环境中,可以直接输入help(函数名)来获取函数的帮助文档。例如,...
-
一、java 连接数据库 在当今信息时代,Java 是一种广泛应用的编程语言,尤其在与数据库进行交互的过程中发挥着重要作用。无论是在企业级应用开发还是...
-
一、idea连接mysql数据库 php connect_error) { die("连接失败: " . $conn->connect_error);}echo "成功连接到MySQL数据库!";// 关闭连接$conn->close();?> 二、idea连接mysql数据库连...
-
要在Python中安装modbus-tk库,您可以按照以下步骤进行操作: 1. 确保您已经安装了Python解释器。您可以从Python官方网站(https://www.python.org)下载和安装最新版本...