mysql索引怎么执行 mysql索引是如何实现的

mysql数据库,索引是怎么使用的

MySQL支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响。通常来说,可以遵循以下一些指导原则:

创新互联主营祥符网站建设的网络公司,主营网站建设方案,成都APP应用开发,祥符h5成都小程序开发搭建,祥符网站营销推广欢迎祥符等地区企业咨询

(1)越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。

(2)简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。

(3)尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

Mysql建立索引经验

在实际开发中使用数据库时,难免会遇到一些大表数据,对这些数据进行查询时,有时候SQL会查询得特别慢,这时候,有经验的老师傅会告诉你,你看一下哪几个字段查的多,加一个索引就好了。

那么,怎么合理地建立索引呢?这里分享一下我的一些经验,如有不妥之处,欢迎批评指正。

1、不要盲目建立索引 , 先分析再创建

索引虽然能大幅度提升我们的查询性能,但也要知道,在你进行增删改时,索引树也要同样地进行维护。所以,索引不是越多越好,而是按需建立。最好是在一整块模块开发完成后,分析一下,去针对大多数的查询,建立联合索引。

2、使用联合索引尽量覆盖多的条件

这是说在一个慢sql里假如有五个where ,一个 order by ,那么我们的联合索引尽量覆盖到这五个查询条件,如果有必要,order by 也覆盖上 。

3、小基数字段不需要索引

这个意思是,如果一张表里某个字段的值只有那么几个,那么你针对这个字段建立的索引其实没什么意义,比如说,一个性别字段就两种结果,你建了索引,排序也没什么意思(也就是索引里把男女给分开了)

所以说,索引尽量选择基数大的数据去建立,能最大化地利用索引

4、长字符串可以使用前缀索引

我们建立索引的字段尽量选择字段类型较小的,比如一个varchar(20)和varchar(256)的,我们在20的上面建立的索引和在256上就有明显的差距(字符串那么长排序也不好排呀,唉)。

当然,如果一定是要对varchar(256)建立索引,我们可以选择里面的前20个字符放在索引树里(这里的20不绝对,选择能尽量分辨数据的最小字符字段设计),类似这样KEY index(name(20),age,job) ,索引只会对name的前20个字符进行搜索,但前缀索引无法适用于order by 和 group by。

5、对排序字段设计索引的优先级低

如果一个SQL里我们出现了范围查找,后边又跟着一个排序字段,那么我们优先给范围查找的字段设置索引,而不是优先排序。

6、如果出现慢SQL,可以设计一个只针对该条SQL的联合索引。

不过慢SQL的优化,需要一步步去进行分析,可以先用explain查看SQL语句的分析结果,再针对结果去做相应的改进。explain的东西我们下次再讲。

PS:在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返回执行计划的信息,而不是 执行这条SQL。

简介mysql之mysql语句执行流程

1.一条查询语句如何执行?

2.一条更新语句如何执行?

3.innodb的redolog是什么?

4.什么是写缓冲

5.写缓冲一定好吗?

6.什么情况会引发刷脏页

关于一条mysql查询语句在mysql中的执行流程

如select name from test where id=10;

1.连接器---先与mysql服务端连接器建立连接,若查询缓存命中则直接返回 (查询缓存的弊端:查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。)

2.分析器---词法分析告诉服务端你要干什么(我要找 test表中id为10的名字) ( 其中sql语法错误在这块暴露 )

3.优化器---服务端会思考该怎么执行最优(索引的选择)

4.执行器---检查用户对库对表的权限

5.存储引擎--存储数据,提供读写接口

以update a set name=1 where id=1;

主要区别在于在查询到数据之后(select name from a where id=1),如果是innodb引擎它会进行日志的两阶段提交:

1.开启事务,写入redolog(innodb引擎特有),并更新内存

3.写入binlog,提交事务,commit

我们知道mysql数据存储包含内存与磁盘两个部分,innodb是按数据页(通常为16k)从磁盘读取到内存中的(剩余操作在内存中执行),当要更新数据时,若目标数据的数据页刚好在内存中,则直接更新。不在呢?

将这个更新操作(也可能是插入) 缓存在change buffer中 (redolog也会记录这个change buffer操作)等到下一次查询要用到这些数据时,再执行这些操作,改变数据(称为合并操作记录称为merge)。

innodb_change_buffer_max_size

innodb_change_buffering

先介绍两个概念

因为redolog是环形日志,当redolog写满时,就需要“擦掉”开头的一部分数据来达到循环写,这里的擦掉指,指将redolog日志的checkpoint位置从 CP推进到CP‘ ,同时将两点之间的脏页刷到磁盘上(flush操作),此时系统要停止所有的更新操作(防止更新操作丢失)

1.系统内存不足。当要读取新的内存页时就要淘汰一些数据页,如果淘汰的正好是脏页,就要执行一次flush操作

2.Mysql认为系统处于“空闲状态”

3.正常关闭Mysql

上述后两者场景(系统空闲和正常关闭)对于性能都没太大影响。

当为第一种redolog写满时,系统无法执行更新操作,所有操作都会堵塞

当为第二种内存不够用时,如果淘汰脏页太多,影响mysql响应时间

后两者刷脏页会影响性能,所以Mysql需要有刷脏页控制策略,可以从以下几个设置项考虑

1.设置innodb_io_capacity告诉innodb所在主机的IO能力

mysql索引

二叉搜索树、N叉树

页分裂:B+树的插入可能会引起数据页的分裂,删除可能会引起数据页的合并,二者都是比较重的IO消耗,所以比较好的方式是顺序插入数据,这也是我们一般使用自增主键的原因之一。

页分裂逆过程:页合并,当删除数据后,相邻的两个数据页利用率很低的时候会做数据页合并

主键索引:key:主键,value:数据页,存储每行数据

非主键索引:key:非主键索引,value:主键key,导致回表

最左匹配:优先将区分度高的列放到前面,这样可以高效索引,

最左匹配原则遇到范围查询就停止匹配,范围查询(、、between、like)为什么?因为出现范围匹配后,后面的索引字段无法保证有序,局部有序失去,顺序失去则无法提高查询效率

SELECT * FROM table WHERE a IN (1,2,3) and b 1;

如何建立索引?

还是对(a,b)建立索引,因为IN在这里可以视为等值引用,不会中止索引匹配,所以还是(a,b)!

索引组织表

索引用页存储:key【10】-point【6】,通过调整key大小,当页大小固定的情况下,通过调整key大小,使得N叉树变化;

如key 10, point 6则单个索引16字节,页大小为16k,则页面总共可以存储1024个索引,即N大小

覆盖索引: 二级索引的信息已经存在想要的列,例如主键

如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。

索引下推优化:可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

整理索引碎片,重建表:alter table T engine=InnoDB

  首先是看key的大小,另外是数据页的大小,如果需要改变N,则需要从这两个方面做改动;

一个innoDB引擎的表,数据量非常大,根据二级索引搜索会比主键搜索快,文章阐述的原因是主键索引和数据行在一起,非常大搜索慢,我的疑惑是:通过普通索引找到主键ID后,同样要跑一边主键索引,对于使用覆盖索引的情况下,使用覆盖索引可以直接解决问题

mysql索引是如何实现的

索引的创建很简单,可以网上查下相关信息,在这里只是说下索引需要注意的地方,索引分为很多不同的类型,一般咱们说的是B_Tree索引,这里就只说B_Tree,如果是哈希索引,可以网上找相关资料。

.B_Tree适用于:

1.全值匹配

全值匹配是指和索引中的所有列进行匹配。

2.匹配最左前缀

匹配左左前缀即只使用索引的第一列

3.匹配列前缀

匹配某一列开头部分(指的第一列)。

4.匹配范围值

5.精确匹配某一列并范围匹配另一列

6.只访问索引的查询

只需访问索引,无需访问数据行。

.B_Tree限制

1.如果不是按照索引的最左列开始查找,则无法使用索引。

2.不能跳过索引中的列。

3.如果查询中有某个列的范围查询,则其右边左右列无法使用索引优化查找。


当前名称:mysql索引怎么执行 mysql索引是如何实现的
网页路径:http://pwwzsj.com/article/hioidg.html