【Mysql】优化—索引优化

目录

索引的几大原则：

列的离散性：

最左匹配原则：

联合索引：

覆盖原则：

列选取原则：

字段类型优先级：

够用就行，不要慷慨（如，smallint,varchar(N)）

Enum列说明

索引常见误区：

 在where常用的列上都要单独加上索引

建立组合索引后，查询哪个列，索引都会生效

索引失效原因：

全值匹配我最爱

最佳左前缀法则

不在索引列上做任何操作（计算、函数、（自动或者手动）类型转换）

存储引擎不能使用索引范围条件右边的列（范围之后全失效）

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * 的使用

mysql在使用不等于（！=、<>）或like的左模糊（'%xxx'）时候无法使用索引，会导致全表扫描

IS NULL 和IS NOT NULL也无法使用索引

字符串不加单引号（‘ ’）索引失效

少用or，用它来连接时索引会失效

理性索引：

索引小技巧：

如果索引为URL？

如果索引长度太长怎么办？

延迟关联：

索引碎片与维护

in 、exists的比较

外表大用in，内表大用exists（小表驱动大表）

count

order by

group by

JOIN

---

前面的文章已经介绍了索引的内部结构：【MySQL】索引内部数据结构

查询有问题的SQL语句：【Mysql】优化-查询有问题的SQL

索引的几大原则：

列的离散性：

离散性越高，选择性就越好

最左匹配原则：

对索引关键字进行计算（对比），一定要从左往右一次进行，且不可跳过

联合索引：

覆盖原则：

 

列选取原则：

字段类型优先级：

整型 > date，time > enum，char,varchar > blob

原因：整型，time运算快，节省空间

整型: 定长,没有国家/地区之分,没有字符集的差异。

time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;

char 定长, 考虑字符集和(排序)校对集

varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.

text/Blob 无法使用内存临时表，一旦有字段用blog，那么就会到磁盘排序。磁盘上建临时表。

够用就行，不要慷慨（如，smallint,varchar(N)）

原因：大的字段浪费内存，影响速度

以varchar(10),varchar(300)存储的内容相同，但是在表联查的时候，varchar(300）要花更多内存。

以年龄为例 tinyint unsigned not null ，可以存储255岁，足够， 用int浪费了3个字节。（tinyint  ：1字节，int：4字节）

这里就用到【MySQL】索引内部数据结构 这篇博客里面：为啥一个节点为1页（16K）就够了？

字段越大，占用内存越多，存储的一个节点存储的关键字就越少，B+Tree的高度就越高，IO操作次数就越多

Enum列说明

Enum列的说明

• 1: enum列在内部是用整型来储存的
• 2: enum列与enum列相关联速度最快
• 3: enum列比(var)char 的弱势---在碰到与char关联时,要转化. 要花时间.
• 4: 优势在于,当char非常长时,enum依然是整型固定长度.
• 当查询的数据量越大时,enum的优势越明显.
• 5: enum与char/varchar关联 ,因为要转化,速度要比enum->enum,char->char要慢,

但有时也这样用-----就是在数据量特别大时,可以节省IO.

 

索引常见误区：

 在where常用的列上都要单独加上索引

例如：where cat_id =3 and price