MySQL 常见SQL优化、索引优化

索引优化

索引区分度

前言

索引长度直接影响索引文件的大小，影响增删改的速度，并间接影响查询速度（占用内存多）

针对列的值，从左往右截取部分来建立索引（前缀索引）

1. 截的越短，重复度越高，区分度越小，索引效果越差。
2. 截的越长，重复度越低，区分度越大，索引效果越好。但是需要更多的空间存储索引文件。增删改变慢。

所以我们需要在区分度 / 长度两者间取得一个平衡。我们可以截取不同的长度，并测试其区分度。

公式

单列区分度计算：select count(distinct (列名))/ count(*) from table; 获取单列最大区分度
索引区分度计算：select count(distinct left(列名,截取长度))/ count(*) from table; 计算指定索引长度区分度

区分度值位于 0.0000~1.0000 之间，该值越大即该字段的区分度越大！
char，varchar类型，length 可以小于字段实际长度；如果是 blog和 text类型，必须指定 length.

演示

区分度计算 占用差 索引建立

优先采用区分度高的列建立索引，索引列截取长度可适当延长一定范围

常见计算规则

1. 不同的字符集（表的字符集），一个字符占用的字节数不同。
   • latin1: 1character = 1byte
   • gbk: 1character = 2byte
   • utf8: 1character = 3byte
   • utf8mb4: 1character = 4byte
2. 所有的索引字段，如果没有设置not null，则需要加一个字节。
3. 定长字段，int占四个字节、date占三个字节、char(n)占n个字符。
4. 对于可变字段varchar(n)，则有n个字符+两个字节。

• char(n) = (Character Set：utf8mb4=4/utf8=3/gbk=2/latin1=1) * n(列长度) + 1(允许null)
• varchar(n) = (Character Set：utf8mb4=4/utf8=3/gbk=2/latin1=1) * n(列长度) + 2（固定值） + 1(允许null)

---

未完待续

SQL优化

• 待写作