Mysql 优化

选择合适的数据类型

• 更小的更好： 正确的选择最小的存储结构。最小的通常更快并且占用磁盘，内存的空间会让cpu 的处理更短。但当无法确定类型的大小时，最好不要这么做。后期的修改会很痛苦。
• 使用简单类型： 简单类型通常使用更少的cpu 周期。比如 整形比字符型操作的cpu 代价更低。因为字符型会使用Mysql 的字符集和排序规则，而这些整形中是没有的。如果需要使用时间类型，最好用mysql 中的 date ,time ,datetime 。这样也是一种优化。
• 结果集中最好不要有null : 对于mysql 来说，null 值很难优化，除非必要，否则最好不要存在。因为当获取null 时mysql 会去给每个null 值 增加额外字段。 null 列会使用更多的存储空间，获取索引和索引统计时会做较为复杂的比较。
• 针对于MyISAM 最好不要使用MD5() ,SHA1(),UUID() 函数产生字符串，这些会随机分配任意空间，导致INSERT 和SELECT 语句很慢。 INSERT 的时候写到的索引位置不同，会造成页分裂和磁盘随机访问 。select 变慢也是因为逻辑上相同的行会在物理上分配在不同的地方。
• 最好使用无符号整数存储IP地址。

mysql 中的类型

整数类型

整数可使用的类型： TINYINT , SMALLINT, MEDIUMINT, INT ,BIGINT。 对应8，16,24,32,64 存储空间。
mysql 的整形设计 int（11） 不会限制值得合法性，只会显示字符的个数不同，int(1) 和 int(11) 的存储空间是相同的。

---

实数类型

带了小数的数字。如 DECIMAL,FlOAT,DOUBLE

• DECIMALL : 可以指定小数点的前后允许的最大位数。但这会增加列的空间消耗。小数点前的会用打包放入一个二进制字符串中，小数点后也会这样，而且小数点本身也会占用一个字节。DECIMALL是一种存储方式，在计算时会转换成double. 所以在存储时，其他的浮点类型都会比 DECIMALL 使用更少的空间。如果需要精确的度数比较高， 可以考虑使用BIGINT 代替DECIMALL 的小数点形式。 如需要精确万分之一， 那么就使用结果乘上一万。

---

字符串类型

varchar 和 char 最主要的两个字符串类型。

• varchar : 可变长字符串。通常varchar 在存储的时候需要额外的一个或两个字节，这个字节的变化是看存储量是否超过255 ，如超就2 个字节标识，否则就使用1个。如： varchar(10) 实际使用的是 11个存储空间， varchar(1000) 实际使用的是 1002个空间。当可变长的存储空间不够时，MyISAM 是拆分不同的片段存储， InnoDB 是分裂页。
• char ： 定长字符串。根据分配的长度进行存储。最好是用在存储短字符或者长度一致的字符。因为短，所以可以使用更少的碎片空间， 不会像varchar 造成过多的碎片空间。在效率上 char 类型比 varchar 类型更快。
  varchar 和 char 都会删除存储数据的字符串后面的空格。

---

BLOB 和TEXT

BLOB 和TEXT 都是用在存储很大的数据而设计的字符串。采用二进制和字符串存储。
BLOB 存储二进制，没有排序规则
TEXT: 有排序规则
BLOB 和TEXT 最好避免使用，如果是非用不可的情况下，最好的查询的情况时，使用SUBSTRING() 去截取字符串，order by 里也要写，并且要保证足够短。这样做的目的是为了使用内存表而不是让mysql 自己去创建使用磁盘表。磁盘会比内存运行慢。(当EXPLAIN 执行显示 Using temporary 说明使用了隐式临时表。超过 max_heap_table_size 或 tmp_table_size 后，会从内存临时表变为磁盘临时表)

---

枚举类型

会按照 数字- 字符串的 形式映射关系。 主要是一些不重复的常用字符可以考虑使用。

-- 这三个字符串实际 是 1-fish, 2-apple, 3-dog 而存入内部存储的 实际是前面的数字
mysql> CREATE TABLE enum_table(
    ->  e ENUM('fish','apple','dog') NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO enum_table(e) Values('fish'),('apple'),('dog');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 默认会添加对应的数字
mysql> select e+0 from enum_table;
+-----+
| e+0 |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
3 rows in set (0.00 sec)

mysql> select e from enum_table;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |
+-------+
3 rows in set (0.00 sec)

因为枚举会按内存存储中的整数进行排序，后面可以借助FIELD() 函数，但是需要手动改动比较麻烦，如果场景中需要有排序的需求， 最好开始的时候就制定先后顺序。

---

日期类型

DateTime： 提供 1000 到 9999的时间范围， 精度为秒，会把YYYYMMDDHHMMSS转换成整数型变成可排序的时间。
TIMESTAMP: 提供 1970年1月1日 到 2038年。依赖时区

---

位数据类型

bit ： 字符串类型
set： 需要保存ture/false 的值，并对内部集合进行打包。

---

范式

范式也是一种性能优化的方式，尤其是在密集场景时。

• 范式化的更新操作通常比反范式更快。
• 当数据较好的范式后，就只有少量的重复数据，所以只用修改更少的数据。
• 范式化的表通常更小，可以更好的使用内存，所以执行操作会更快
• 减少数据的冗余也就会在查询中更少的使用去重的功能，也是一种后期的查询优化。

但是范式也会增加表与表的关联性，造成至少一次的关联。从管理和使用上都需要更繁琐的操作，所以有可能使一些索引策略失效。

反范式

范式会造成关联问题， 所以有了反范式。可以简单的理解成非关联表，把所有的数据放在一张表中，形成一个单表，而单表可以更好的使用索引策略。当没有使用索引时，造成的最差结果就是全表扫描。在没有关联条件的束缚下，反范式的表会比范式表查询更快。

索引

索引也被称为键， 一种存储引擎快速查找记录的数据结构。当表中的数据量越来越大时，索引对性能的影响就越发重要。但是数据量小且负载低时，不正当的使用索引可能会对性能造成影响。

• 索引首先会按照值进行查询，然后返回所有包含目标值得查询。
  列：select name from table where id =3;
  当id 列有索引就会匹配当前列下所有是3的值，并返回出来。

• 覆合索引效率最好的是最左前缀列，创建两个一列的索引 和使用一个包含两个列的索引效率是大不一样的。

• 索引大大减少了服务器需要扫描的数量

• 索引可以帮助服务器避免排序和临时表

• 索引可以把随机I/O 变顺序I/0

三星索引：

• 一星： 所以把所有相关记录放在一起
• 二星： 索引中的数据顺序和查找中的排列顺序一致
• 三星： 索引中包含查询中需要的全部列

mysql 支持的索引类型

B-Tree 索引 （b树索引）

B树通常所有的值都是按照顺序存储的，每个叶子到根的距离都是相等的。B树索引会通过比对当前指针进行切换，比较节点的值，合适就返回，不合适就继续找，直到查到叶子结点也没有。

B树会按照创建表的顺序来进行匹对排序。 如一个表的建表语句是：
create table table{
id ···，
name ,
date
}
那么存储引擎在创建时，会先按照id 排序， 如果id 相同。就按name 排序，如果id ，name 都相同，会按时间排序。

使用场景

• 全值匹配 ： 索引中所有列进行匹配。
• 匹配最左前缀 ： 只使用索引的第一列
• 匹配列前缀： 匹配某列的靠头部分
• 匹配范围值

B树索引失效的情况

• 不按最左侧使用索引
• 跳过索引中的某个列，最终只能是有最左侧索引
• 查询中某个列是范围查询，则范围查询后面的查询不能使用到索引。

哈希索引

哈希索引基于哈希表实现，存储引擎会为所有索引列算出一个哈希码，再把这些值给定一个指针存入。
哈希索引查询的过程： 第一步是计算要查询内容的哈希值，根据这个值去找对应的指针，再根据对应的指针查找链表下的内容，也就是比对内容是不是和要查的内容一致。（这里建议最好去看下哈希表的结构和增加删除的过程）

哈希索引的限制

• 哈希索引只能包含哈希值和指针，而不能存值
• 哈希索引不是按照索引值顺序存储的
• 哈希索引不支持部分索引的匹配查询
• 哈希索引访问虽然快，但是会有哈希冲突的问题，所以有些维护代价是比较高的。

InnoDB 引擎中自带 自适应哈希索引，当innoDB 注意某个索引被经常使用时，会在内存中基于B 树创建一个哈希索引，这样B树 也会有哈希索引的优点。这个特点不受配置，是内部行为，但是可以关闭。

创建自定义哈希索引
当引擎不支持哈希索引时，就可以模拟。也就是创建伪哈希索引，需要在where条件中指定哈希函数。
删除老索引，新增一个url_crc 索引后。
如：

select id from url where url  = "www.innode.com"
 and url_crc = CRC32("www.innode.com")

---

空间数据索引

MyISAM 支持空间索引，与B-Tree 索引不同，这类索引无需前缀查询，可以从所有维度来索引数据

---

索引策略

1. 独立的列： 索引列不能是表达式的一部分，也不能是函数的参数。如： where id +1 =5 ; 这种属于用户行为，mysql 无法自动解析这个过程

2. 索引前缀和索引选择： 把较长的字符串切成短小的前缀字符串进行索引

3. 多列索引

4. 聚簇索引：在同一个结构中保存了B树的索引和数据行，因为mysql 无法把数据行放在两个不同的地方，所以一个表只能有一个聚簇索引。InnoDB 会通过唯一的非空索引代替，但是如果没有这样的索引时，InnoDB 会隐式的生成一个主键作为聚簇索引。聚簇索引会提高性能，但也能导致性能问题
   优点：

   • 可以把相关的数据保存在一起。可以根据生成的聚簇id 进行查询，这样只用进行少量的查找就能获取想要的信息，如果没有使用聚簇索引，最坏的情况下会造成每个数据都扫描一次。
   • 访问速度更快
   • 覆盖索引会直接使用页的主键值

   缺点：

   • 聚簇索引会最大限度的提高I/O 的应用，如果数据都在内存中。有没有使用聚簇索引都无所谓了
   • 插入速度依赖插入顺序
   • 更新聚簇索引的代价很高
   • 插入新数据时可能会造成页分裂
   • 二级索引访问需要两次，而非一次

---

覆盖索引

Mysql 的索引也可以直接获取列的数据，这样就不需要回表再获取数据行。而这种索引包含所有要查询的字段的值，就称为覆盖索引。
好处：

• 索引的条目远小于数据行，如果可以直接获取，那么就减少了数据访问量。
• 覆盖索引配合非聚簇索引会更有效，非聚簇索引只保存了主键值，如果只能能使用二级主键覆盖，就可以减少对主键索引就行二次查询

---

索引扫描排序

Mysql 有两种扫描方式，通过排序扫描，和通过索引扫描。 如果EXPLAIN 出来的 type 值为 index 说明用了索引做排序。扫描索引虽然快，但是如果索引不能覆盖查询所需要的全部列，那就不得不查询一次索引记录再和表里的行做对应，因此索引顺序读取数据通常比顺序全表扫描慢。
当索引的列顺序和ORDER BY 子句的顺序方向一致时，mysql才能使用到索引来对结果排序。
如： 在 table 表中创建 table_date， inventory_id,customer_id 这么一个索引，那么当order by 时 table_date = ‘2022-09-10’ order by inventory_id 时就是使用了索引排序。mysql 会把 时间类型在存储上转成常量存储，而排序也是用这个常量排序的。 但当table_date， inventory_id,customer_id 任何一个字段查询变成范围时，索引排序都会失效。

---

冗余索引

Mysql 允许创建多个重复的索引，并且会单独维护重复的索引，这很消耗性能。重复索引是相同的列，相同的顺序创建的相同类型的索引。当然如果索引类型不同，就不算是重复索引。

---

未使用的索引

一些服务器永远都使用不到的索引，这种索引完全是累赘。通过Percona Server 打开 userstates 服务器，等服务器运行一段时间后， 查询 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查看每个索引的使用频率。

---

减少索引和数据的碎片

索引可能会造成碎片化。B树是随机访问才能定位到叶子页，所以随机访问是不可避免的。如果叶子在屋里上的分配是连续紧密的，那么查询效率就会更好。表的数据存储也有可能碎片化，

• 行碎片： 数据存储在多个片段中，即便是通过索引访问，也可能会降低效率。
• 行间碎片: 逻辑上顺序的页，磁盘上不是顺序存储的。
• 剩余空间碎片： 数据页没有填充满，导致的浪费资源。

对于MyISAM 以上三种都可能发生，InnoDB不会出现小行碎片。
可以通过 OPTIMIZE TABLE 或导出再导入的方式重新分配数据空间。不支持OPTIMIZE TABLE 的引擎， 可以通过 ALTER TABLE 操作表

ALTER TABLE <table> ENGINE = <engine>;

---

查询性能优化

大多数慢查询的都是因为查询的数量比较多，减少数量量的访问就可以进行优化。

查询执行的过程：

• 语法解析器和预处理： mysql 解析sql 语句是，会生成对应的解析树。解析器会校验sql 语法和查询。预处理则是检查数据表数据列是否存在，和解析名词是否合法。
• 查询优化器： 当语法被认为是合法的以后，会由优化器转成对应的执行计划。一条查询会有多个执行计划，最后返回的内容都是一致的。优化器的作用就是找到执行计划里最优的一条。

select * from table

-- 查看优化器的执行成本
SHOW STATUS LIKE 'Last_query_cost';
-- 返回值也就是数据页随机查找的数量， 这些数量表面上面那个select 语句需要花费多少数据页才能查出来。

分解查询

把一条独立的语句分解成多个小查询。在事务型引擎下，当使用一个大的事务往往要比使用多个小事务查询要更费事和消耗性能。
如：

 select * from a 
 join b on a.id =b.id 
 join c on b.name = c.name
 where a.id =1 

-- 切换成
select * from a where id =1 
select * from b where id =1 
select * from c where id =1 

以上分解的优势：

• 缓存的效率更高。需要应用程序可以方便缓存单表的结果。
• 将查询分解多个，执行单个查询可以减少锁竞争
• 在应用层做关联，可以更容易对数据库拆分，更容易做到高性能和可扩展。
• 可以减少冗余数。对单表查询一次，那么就只需要缓存一次。

语句优化

IN()

IN(): 如果是少量常数，效率会很高。如果里面有子查询，会转换成连接查询并降低效率。
例：

select * from table where table_id IN (select * from table1 where table1_id = 1)

-- mysql InnoDB 会转换成
select * from table where EXISTS (select * from table1 where table1_id = 1 AND table1_id = table_id )

使用EXPLAIN 会发现多出来一个子查询， 使用 EXPLAIN EXTENDED 可以查看具体的子查询变成什么样子了

优化方式

• 使用内连接或外连接的方式进行重写。

关联子查询

并非所有的关联子查询的效率都会慢于连接表。需要具体的使用EXPLAIN 查看。

尽量少用MIN() 和 max()

使用min() 和 max 都可能造成全表扫描。如果要查 max（id）,id 是主键索引。也会造成全表扫描。可以通过查询以后 使用SHOW STATUS 的计数器来证明这点。

优化

-- 可以使用 limit 优化,单要有主键索引下的情况下，并且id 是数字。
-- 如原句：
select max(id) from table where name = '张三';
--改
select id from table where name = '张三' limit 1;

为什么要这么改？
innoDB 在 5.6 以后优化了limit 字段，获取数据时，不会全表扫描。而id 是有主键索引的。会直接从索引中取数据，name 的条件也可以从索引中直接检索，这样就减少的查询表数据的条数。

优化关联

• 创建索引时要考虑关联顺序。 如果表A 和 表B 关联条件是C，那么如果优化器中的关联顺序是B , A 。 这样只需要给表A 创建C 索引。 B 就不需要索引， 如果创建了相同的索引就会造成浪费甚至给Mysql 增加负担。
• Group BY 和 ORDER BY 在使用时尽量使用一个表的字段。Mysql 可能会索引优化
• 升级注意，升级过后的语句的关联语句和 运算符优化级都有可能有意想不到的结果。

自定义用户变量

自定义用户变量是用于存储内容的临时容器。在连接mysql 的整个过程中都会存在。
主要用set 来指定。
缺点：

• 自定义变量查询时，不能使用查询缓存。
• 不能在使用常量和标识符的地方使用自定义变量。如表名
• 自定义变量只在一次连接中有有效，不能用来连接通信
• 不能显式的自定义变量
• Mysql 可能会在某些场景下把自定义变量优化掉
• 赋值的顺序和时间并非是固定的
• 自定义变量不会产生语法错误

使用自定义变量查看更新条数

INSERT INTO T1(A1,A2) VALUES(1,1),(2,1),(3,1)
   	ON DUPLICATE KEY UPDATE
   	A1 = VALUES(A1) + (0 * (@X := @X+1) );