首页 / MYSQL / MySQL调优4---索引

MySQL调优4---索引

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL调优4---索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3957字，纯文字阅读大概需要6分钟。

内容图文

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分类

1.PRIMARY KEY 主键索引：一个表只能有一个主键，不允许有空值。
2.UNIQUE INDEX 唯一索引：索引列的值必须唯一，但允许有空值。
3.INDEX 普通索引：最基本的索引，它没有任何限制
4.FULLTEXT 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
5.INDEX 组合索引：多个字段上创建的索引

索引的作用

1.快速查找，减少服务器需要扫描的数据量
2.减少IO操作，避免临时表

索引的数据结构

1.Memory存储引擎使用哈希表。
优点：快，在内存中。缺点：不能持久化，重启没有了。
2.Mylsam和InnoDB使用B+树
优点：
1.B+树每个叶子节点可以包含更多的节点（相比于二叉树，平衡二叉树，红黑树等），既降低了树的高度，又将数据范围变为多个区间，加快了检索速度。
2.非叶子节点仅存储key值（B树非叶子节点也存储数据），叶子节点存储key值和数据（Mylsam存储引擎存储的是数据文件的地址）。
3.叶子节点两两之间指针连接，顺序查询性能更高。

聚簇索引和非聚簇索引

聚簇索引

数据和相邻的键值存储在一起；访问速度更快，在同一个树中；使用覆盖索引查询可以直接取出数值。
MySQL调优4---索引 - 文章图片

非聚簇索引

数据文件跟索引文件分开存放，先查询到索引，再去对应的文件位置查询数据
MySQL调优4---索引 - 文章图片

索引监控

show status like 'Handler_read%';
Handler_read_first：读取索引第一个条目的次数
Handler_read_key：通过index获取数据的次数
Handler_read_last：读取索引最后一个条目的次数
Handler_read_next：通过索引读取下一条数据的次数
Handler_read_prev：通过索引读取上一条数据的次数
Handler_read_rnd：从固定位置读取数据的次数
Handler_read_rnd_next：从数据节点读取下一条数据的次数

数值越大，说明索引利用率更高

索引使用注意事项

1.回表

主键查询不会触发回表，其他索引会触发回表。
因为innodb的主键索引树叶子结点上保存的是全行数据，主键查询可以一次定位到数据的行数，取出数据内容；
而其他索引需要两次查询，第一次查询到主键值，再根据主键值查询出对应的数据。

2.覆盖索引

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

3.索引覆盖

将需要查询的结果字段建立组合索引
能够一次取出索要查询的结果字段，不需要回表

4.最左匹配

索引查询从最左边开始匹配
组合索引需要考虑索引的顺序和分组的合理性，如下，建立a、b、c三个索引，查询结果如表所示
MySQL调优4---索引 - 文章图片

5.前缀索引

在一个很长的字符串上建立索引，选取长度过长会消耗太多的内存，让索引查询效率变慢，所以需要选择一个合适的长度。
可以截取字符串的前几位和总数做对比，取出一个合适的长度。
前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。

6.索引下推

mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制

SET optimizer_switch = 'index_condition_pushdown=off';

索引下推适合INNODB引擎的二级索引。即第一次用第一级索引取出符合条件的部分数据，然后第二级别索引继续过滤条件查询，最终结果再回表根据主键查询。如下SQL：
现有student表建有组合索引（name，score），要求查询分数在90分以上且姓赵的同学。

SELECT *FROM student WHERE name LIKE '赵%' AND score>90；

先查询出姓赵的同学，然后根据分数过滤拿到主键id，根据主键id回表查询
Like：匹配模式必须要左边确定不能以通配符开头

7.union all,in,or都能够使用索引，但是推荐使用in

必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引

8.范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列，范围条件是：<、<=、>、>=、between

9.强制类型转换会全表扫描

本来有一个字符串的字段建立了索引，查询的时候字符串后面匹配一个整型值，会导致全表扫描。
oracle数据库类型不匹配会报错

10.阿里规范，join表最好不超过三张，非要使用在关联条件上创建索引

11.更新频繁的字段不是和建索引，索引维护消耗资源，增删改都会重新更新索引。更新索引时会有页分裂和合并等IO操作。

一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

12.合理使用索引，并不是越多越好。

单表索引建议控制在5个以内
单索引字段数不允许超过5个（组合索引）
能使用limit的时候尽量使用limit

MySQL调优4---索引

标签：sele 利用默认对比 sql primary 存储引擎包含访问

本文系统来源：https://www.cnblogs.com/farmersun/p/12490140.html

内容总结

以上是互联网集市为您收集整理的MySQL调优4---索引全部内容，希望文章能够帮你解决MySQL调优4---索引所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/520166.html

来源：【匿名】

【上一篇】springboot项目前端页面中文搜索时，数据库查询为空（mysql）【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL调优4---索引】教程文章相关的互联网学习教程文章

mysql 索引与优化like查询

详见：http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt1011. like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。 2. like keyword% 索引有效。 3. like %keyword% 索引失效，也无法使用反向索引。 ==================================================================== 1. 使用下面的函数来进行模糊查询，如果出现的位...

MySQL索引背后的之使用策略及优化(高性能索引策略)【图】

本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。示例数据库　　为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图(引用自MySQL官方手册)：　　图12　　MySQL官方文档中关于此数据库的页面...

MySQL 8.0 隐藏索引【图】

隐式索引最明显的一个作用类似索引回收站。例如数据库长时间运行后，会积累很多索引，做数据库优化时，想清理掉没什么用的多余的索引，但可能删除某个索引后，数据库性能下降了，发现这个索引是有用的，就要重新建立。对于较大的表来说，删除、重建索引的成本是很高的，如果在清理索引时能先放入回收站，确认没影响后再彻底删除，有影响的话就恢复回来，这样就方便多了，把索引设置为隐藏/显示就可以实现这个需求。索引的隐藏不...

MySQL之left join表查询中发生字符集转换导致表索引失效【代码】【图】

一、出现的场景研发leader突然给了1条sql说这条sql在测试环境库db-stage执行非常的慢，放到线上db-read从库上执行非常的快。而且线上库从库的表的数据远多于测试环境库的表数据。让我分析下是什么问题？？二、sql内容如下 select odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id left join db_order.t_orders o on oid.order_id=o.order_id w...

MySQL索引背后的数据结构及算法原理

摘要本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引，至于哈希索引和全文索引本文暂不讨论。文章主要内容分为三个部分。第一部分主要从数据结构及算法理论层面讨论M...

MYSQL索引

mysql的4种常用索引类型：唯一索引，主键索引，全文索引，以及普通索引。普通索引（INDEX）：普通索引为索引的核心部分，无限制，目的就是加快数据的访问速度，而主键索引，唯一索引，全文索引则是在普通索引延伸出来的有规则限制的索引，方便用于某些特定的业务场景。主键索引（PRIMARY）：可以理解主键（常用的那个id主键），每一张表都必须有一个主键，且不允许为空。唯一索引（UNIQUE）：内容具备唯一性，像手机号，身份证这种...

mysql测试索引在表中的作用【代码】

实验内容单表中的索引使用1．建表 create table t_book(id int,a1 varchar(200),a2 varchar(200),a3 varchar(200),a4 varchar(200),a5 varchar(200),a6 varchar(200),a7 varchar(200),a8 varchar(200),a9 varchar(200)) 2．写存储过程向表中插入较大数据//或其他方式使数据增大（1）绑定变量： PREPARE stmt1 FROM‘insert into t_book(id,a1,a2,a3,a4,a5,a6,a7,a8,a9)values(?,?,?,?,?,?,?,?,?,?)‘; （2）存储过程创建：delimite...

mysql中索引的使用【图】

索引是加速查询的主要手段，特别对于涉及多个表的查询更是如此。本节中，将介绍索引的作用、特点，以及创建和删除索引的语法。13.4.1 使用索引优化查询索引是快速定位数据的技术，首先通过一个示例来了解其含义及作用，详细的介绍请参考第14章。1．索引示例假设对于10.3节所建的表，各个表上都没有索引，数据的排列也没有规律，如表13.3所示。表13.3 没有索引的students表...

Mysql如何为表字段添加索引？？？

1.添加PRIMARY KEY（主键索引）：ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) ：ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) ：ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) ：ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `col...

MySql分区后创建索引加速单表查询和连表查询【代码】【图】

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。本文链接：https://blog.csdn.net/konkon2012/article/details/96482548为了加快查询，我们通常根据Where条件创建索引！那么分区后再创建索引，那就应该更快了！我们依据订单表和订单商品表举例，先创建表结构：CREATE TABLE `zstb_orders` ( `order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `org_id` INT(10) UNSIGNED NOT NU...

mySql---剖析InnoDB索引原理【图】

摘要：本篇为参考别人的文章（http://blog.csdn.net/voidccc/article/details/40077329）1 各种树形结构本来不打算从二叉搜索树开始，因为网上已经有太多相关文章，但是考虑到清晰的图示对理解问题有很大帮助，也为了保证文章完整性，最后还是加上了这部分。先看看几种树形结构： 1 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结...

mysql索引整理

一.索引：1.创建一般索引 create index index_name on table_name(columnname[...]); 2.分析sql执行语句在select 之前加explain eg:explain select * from table_name;3.mysql从5.5.5之后默认采用innerdb为默认的存储引擎,该引擎支持的两种常见索引：b+树索引和hash索引，hash索引的效率高于b+树，但hash索引只支持=类型。b+树可以支持范围的eg:>< ，b+树一般三层即可表示，即访问3次io可找到数值。二.常见丢失索引的情况1.左前缀...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / MySQL调优4---索引

MySQL调优4---索引

内容导读

内容图文

索引分类

索引的作用

索引的数据结构

聚簇索引和非聚簇索引

聚簇索引

非聚簇索引

索引监控

索引使用注意事项

1.回表

2.覆盖索引

3.索引覆盖

4.最左匹配

5.前缀索引

6.索引下推

7.union all,in,or都能够使用索引，但是推荐使用in

8.范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列，范围条件是：<、<=、>、>=、between

9.强制类型转换会全表扫描

10.阿里规范，join表最好不超过三张，非要使用在关联条件上创建索引

11.更新频繁的字段不是和建索引，索引维护消耗资源，增删改都会重新更新索引。更新索引时会有页分裂和合并等IO操作。

12.合理使用索引，并不是越多越好。

内容总结

内容备注

内容手机端

【MySQL调优4---索引】教程文章相关的互联网学习教程文章

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程