MySQL字符串索引&脏页刷盘

字符串加索引的方式？

对于字符串进行添加索引，我们除了对整个字符串加索引以外，还可以添加前缀索引。

什么是前缀索引？

前缀索引的好处？

使用前缀索引，定义好长度，可以做到即节省空间，又不用额外增加太多的查询成本。

前缀索引的弊端？

前缀索引会使覆盖索引失效，额外增加回表的消耗，如果前缀索引的长度选择区分度不高，会额外导致扫描行数增加。

其他给字符串加索引的方式？

什么是脏页？

MySQL在更新数据的时候会写redo log并且更新内存以后就会返回，数据文件并不会立即更新，这就是所谓的WAL机制。

当内存被更新以后，内存中的数据页就会和磁盘上的数据页存在不一致的情况，该内存也就被称为 脏页 。

内存中的数据被写入磁盘以后，内容变为一致，此时该内存页就被称为干净页。

什么叫刷脏页？

内存数据页中的内容被写入磁盘数据页中的过程称为刷脏页。

什么时候会刷脏页？

InnoDB如何控制刷脏页的频率？

首先确认InnoDB所在主机的IO能力，此时需要用到数据库的innodb_io_capacity参数，该参数推荐设置为磁盘的IOPS。磁盘的IOPS可以通过fio工具进行测试。

InndoDB刷脏页主要考虑以下两个因素：

MySQL会根据F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

脏页比例

参数innodb_max_dirty_pages_pct是脏页比例的上限，MySQL 8.0中是90%。

当前脏页比例可以通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total计算获得，具体sql计算指令如下：

连坐机制

InnoDB在刷脏页的时候，如果该脏页旁边的是页也是脏页，会同时把相邻的脏页刷掉。

该刷脏页行为由参数innodb_flush_neighbors控制:

对于机械硬盘，开启连坐会减少随机IO的消耗，但对于SSD，没必要开启该参数。

在实际开发中使用数据库时，难免会遇到一些大表数据，对这些数据进行查询时，有时候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数据查询更加快速的数据结构，我们在新建数据库的时候，如果设置了某个字段的Primary key主键，那么数据库会默认为我们的主键字段创建一个唯一索引(Unique Index)的东西，所以你就不需要再为此字段创建mysql数据库索引了，当然了，如果你想设置别的字段索引，那么就要额外加入该字段的数据库索引了。

一：Mysql数据库索引的创建

1)如何为我们的mysql数据库添加索引呢？很简单，请看下面的创建mysql普通索引格式：

CREATE INDEX [index name] ON [table name]([column name])

参数说明：NameDescription

index name索引名称.

table name需要添加索引的表名称.

column name需要添加索引的列名称.

例如我们要创建一个索引，就可以使用如下的mysql命令运行即可：CREATE INDEX myindex ON mytable(aut_id)

2)唯一索引的创建，和mysql普通索引差不多，只需要在INDEX关键词前面加入UNIQUE关键词即可，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id)

我们也可以使用USING BTREE关键字，B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id) USING BTREE

二：查看mysql数据库索引

我们可以使用以下命令查询刚刚新建的mysql数据库索引，“from mytable”代表查询该数据库表里面有哪些索引，mysql命令如下：show index from mytable

创建成功之后，会在“key_name”字段中显示刚刚创建的数据库索引名称，如图所示：

三：删除mysql数据库索引

我们也可以使用mysql命令删除某张表的索引，命令如下：drop index english on mytable

四：查询sql语句是否使用了mysql索引

我们上面常见好mysql数据库索引之后，如果想要知道我们在执行sql语句时是否使用了数据库索引，就要在sql语句前面加入“EXPLAIN”关键词，命令如下所示：EXPLAIN SELECT * FROM `allword` where english='America'

如果使用到了我们创建的字段索引，就会看到如图所示的字段都不会是NULL空值，如下：

在有些情况下mysql索引会失效，也就是在执行到sql语句时没有使用到我们创建的数据库表字段索引，可能有以下这些情况：

1：使用了OR关键字查询

2：或者LOWER()，UPPER()函数，

3：还有一种就是使用了LIKE关键字查询，像(like '%XX'或者like '%XX%')这样的语句，但是像(like 'XX%')这种情况的mysql表索引是不会失效的。

---