mysql的索引和优化

Mysql的索引和优化

docker 安装 mysql5.7 :

https://www.dahuangzi.work/#/essaydetail/MjI=

索引：

索引是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

为什么用索引：

如果你的字典没有目录，你给我查一个字，我看看。

一、索引分类：

mysql 的索引分为 单例索引（主键索引、唯一索引、普通索引）和 组合索引。

• 单例索引：一个索引只包含一个列，一个表可以有多个单例索引。
• 组合索引：一个组合索引包含两个或两个以上的列。

1、主键索引：

primary key：在创建表选择主键的时候，默认生成主键索引，是一种特殊的唯一索引（不允许有空值）。

2、 唯一索引：

Unique：设置唯一索引的字段不能有重复的数据。

3、普通索引：

Normal：

• 用来加速数据访问速度而建立的索引。
• 多建立在经常出现在查询条件的字段和经常用于排序的字段。
• 被索引的数据列允许包含重复的值

4、组合索引：

一个索引列中包含一个以上的字段

二、索引方法（索引的两大类型Hash与Btree）：

—说索引就必须说的 （存储引擎）

*不同的引擎对于索引有不同的支持：

Innodb和MyISAM默认的索引是Btree索引；
Mermory默认的索引是Hash索引。

*MYISAM引擎：非聚集索引

因为索引文件myi和数据文件myd是分开的，myi里面存储的是myd文件的指针,而且主键索引和非主键索引没有层次关系，因为存储的都是指向myd文件的指针

*Innodb引擎：聚集索引

只有一个idb文件，在主键索引和非主键索引有层次的关系，因为主键索引存储的是真是的信息，而非主键索引存储的是主键的信息

我们在mysql中常用两种索引算法BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。

1、Btree

BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量。

Btree索引中的最左匹配原则

Btree是按照从左到右的顺序来建立搜索树的。比如索引是(name,age,sex)，会先检查name字段，如果name字段相同再去检查后两个字段。

所以当传进来的是后两个字段的数据（age，sex），因为建立搜索树的时候是按照第一个字段建立的，所以必须根据name字段才能知道下一个字段去哪里查询。

传进来的是（name，sex）时，首先会根据name指定搜索方向，但是第二个字段缺失，所以将name字段正确的都找到后，然后才会去匹配sex的数据。
select * from user where sex=0 ； false
select * from user where sex=0 and name=’’； true

2、Hash

所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，只能用于对等比较，例如=,<=>（相当于=）操作符

三、建立索引的规则：

1、利用最左前缀：Mysql会一直向右查找直到遇到范围操作（>，<，like、between）就停止匹配。比如a=1 and b=2 and c>3 and d=6；此时如果建立了（a,b,c,d）索引，那么后面的d索引是完全没有用到，当换成了（a,b,d,c）就可以用到。

2、不能过度索引：在修改表内容的时候，索引必须更新或者重构，所以索引过多时，会消耗更多的时间。

3、尽量扩展索引而不要新建索引

4、最适合的索引的列是出现在where子句中的列或连接子句中指定的列。

5、不同值较少的列不必要建立索引（性别）。

mysql不推荐用UUID做主键

• UUID字段很长，而且不是int类型的，存储的数据量就会大大减少，索引效率不高。
• UUID因为不是自增的，在插入索引的时候，会对原有的叶片就行维护并分裂的成本特别高。

注意：

一个表不要创建过多的索引，索引是方便了查询，同样它也是有维护的代价，和占用的空间。 添加和删除都有维护索引的消耗。

个人博客： https://www.dahuangzi.work/ "