【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 > 100;  

           误区：   在cat_id和price上都加上索引

           原因：只能用上这2个索引中的其中一个，独立索引，同时只能用上1个

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

例如：

            误：组合索引，索引发挥作用，需要满足左前缀要求。

索引失效原因：

【优化总结口诀】

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

全值匹配我最爱

建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，不跳过索引中间的列。（带头大哥不能死，中间兄弟不能断）

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

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

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

若中间索引列用到了范围（>、<、like等），则后面的索引列全失效

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

范围查找时，若将替换转换成索引列的话，不仅会用到Using index索引级别还会是ref,key_len也短，效果更佳，数量大的时候非常明显。

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

 问题：解决like'%字符串%'索引不被使用的方法？？

使用覆盖索引可以解决这个问题，索引会使用。

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

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

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

 

理性索引：

1. 查询频繁
2. 区分度高
3. 长度小
4. 尽量能覆盖常用查询字段

索引小技巧：

如果索引为URL？

若为URL，可以将内容倒过来存储，并建立索引，这样左前缀区分度大。

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

伪哈希函数：

用crc函数来构造伪哈希列。 

把字符串的列，转成整型，来降低索引的长度。

延迟关联：

 大数据量下的分页效果

   select field from table limit 30,10

1.从业务上去优化

 百度，翻页超不过100页

2.用索引来查询

3.延迟关联

  

索引碎片与维护

在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.

我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.

比如: 方法一：表的引擎为innodb , 可以 alter table xxx engine innodb（为innodb，还改为innodb有啥用？会重新规整你的数据）

方法二：optimize table 表名 ,也可以修复.

注意:

 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.

 这个过程,如果表的行数比较大,也是非常耗费资源的操作.所以,不能频繁的修复.

 如果表的Update操作很频率,可以按周/月,来修复.

 如果不频繁,可以更长的周期来做修复.

in 、exists的比较

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

  in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

 

count

误区：查询非常快

是比较快的，但是仅限于查询表的“所有行”比较快，因为Myisam对行数进行了存储。是比较快的。

一旦有了条件的查询，速度就不再快了，尤其是where条件的的列上没有索引。

select count(*) from lx_com where id>=100;  (1000多万行用了6.X秒)

小技巧:

select count(*) from lx_com; 快

select count(*) from lx_com where id<100; 快

select count(*) frol lx_com -select count(*) from lx_com where id<100; 快

select (select count(*) from lx_com) - (select count(*) from lx_com where id<100)

order by

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。

 MySQL支持二种方式的排序，FileSort和Index,Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

  ORDER BY满足两情况，会使用Index方式排序:

•    ORDER BY语句使用索引最左前列
•    使用where子句与OrderBy子句条件列组合满足索引最左前列

 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

双路排序

MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输，  从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。 取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。

  单路排序

  从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

   由于单路是后出来的，总体而言好过双路

 

group by

注意：

1.分组用于统计，而不用筛选数据。

比如：统计平均分，最高分适合，但用于筛选重复数据，则不适合。

2.where高于having,能写在where限定的条件就不要去having限定了。

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

 

JOIN

【Mysql】SQL中的join语句比较

小结果集驱动大结果集

保证被驱动表上join条件字段已经被索引：left join（右表上要加上索引）、right join（左表上要加上索引）