Mysql Sql语句优化,索引失效的情况说明(2)

上一篇：Mysql书写规范 、Mysql的书写顺序执行顺序、Mysql使用技巧(1)

1.索引介绍

2.索引命名规范

•	非唯一索引必须按照“idx_表名称_字段名称[_字段名]”进行命名
•	唯一索引必须按照“uniq_表名称_字段名称[_字段名]”进行命名
•	索引名当前库唯一

3. 合理使用like模糊查询

Like查询时：‘%str’索引失效,全表扫描,而‘str%’索引不失效:

Eg:	SELECT * FROM `prestorerecord` WHERE orderno LIKE '%13622018040100000%'

关键词: % orderno %，由于orderno前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%

那么如何解决这个问题呢？答案：使用全文索引。

创建全文索引的SQL语法是：
ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

使用全文索引的SQL语句是：
select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);

4.不会使用索引的情况

• 数据类型出现隐式转换不会使用索引，有些列类型是字符串，在写where条件时，需要将常量值用引号括起来。
• 复合索引，查询条件需要包括最左边部分，否则不会使用复合索引。即最左前缀法则。
• MySQL执行语句时会有优化器选择的过程，当全表扫描的代价小于索引的代价时，会使用全表扫描，所以此时需要更换一个筛选性更高的条件。
• 用or分开的条件，如果or前的列有索引，后面的没有索引，则不会使用索引。

5.查看索引的使用情况

1.如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，如果很低，说明增加
	索引得到的性能改善不高，因为索引并没有被经常使用。
2.Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读
	下一行的请求数。如果值比较大，说明正在进行大量的表扫描，则通常说明表索引不正确或写入的查询没有利用索引。

6.建立索引需注意

• 1)在表中建立索引，优先考虑where.group by使用到的字段。
• 2)同一个库中不能建立同名索引.
• 3)连表查询时,最好连接相关字段类型和长度一致.
• 4)创建索引的目的

7.索引在where条件or和in生效的情况

• 1)or两侧的都有索引时,索引生效;否则索引失效; 如图1,2;
• 2)where子句and相连的条件中,有一个条件是带索引的，索引生效.如图3;
• 3)where子句即使and中有的条件是使用了in 索引仍然有效.如图4;
  其实这就是mysql执行优化策略，会优先走索引（如果可以走索引的话），走最短最快的查询方式，这也就说明了where后面的条件不是从左到右或者从右到左的
  

上图是:索引关系

图1

图2

图3

图4

8.避免在where子句中对字段进行is null判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

9.避免在where子句中对字段进行表达式操作

select user_id,user_project from user_base where age*2=36;
对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：
select user_id,user_project from user_base where age=36/2;

10.避免隐式类型转换

where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。

11.对于联合索引来说，要遵守最左前缀法则

举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都
无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

12.必要时可以使用force index来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以
采用forceindex来强制优化器使用我们制定的索引。

13.注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

14.区分in和exists、not in和not exists

select * from 表A where id in (select id from 表B)
上面SQL语句相当于
select * from 表A where exists(select * from 表B where 表B.id=表A.id)

   区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
   关于not in和not exists，推荐使用not exists，不仅仅是效率问题。not in可能存在逻辑问题(这个我不确定)。

15.表之间的连接方式

a)交叉连接(CROSS JOIN)

  交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

• 1)隐式的交叉连接，没有CROSS JOIN。

SELECT * FROM USER u , country c ;

• 2)显式的交叉连接，使用CROSS JOIN

SELECT * FROM USER u CROSS JOIN country c;

b)内连接（INNER JOIN <==>join）

   连接表之间公共的部分

• 1)隐式的内连接，没有INNER JOIN

  SELECT * FROM USER u,country c WHERE u.id = c.user_id

• 2)显示的内连接，一般称为内连接，有INNER JOIN

SELECT * FROM USER u INNER JOIN country c ON u.id = c.user_id

c) 外连接（OUTER JOIN）

   以一个表为基准连接另一个表,左连以左表为基准,反之亦成立.全外连接(full outer join)mysql不支持

• 1)左外连接（LEFT [OUTER] JOIN）

SELECT * FROM USER u LEFT JOIN country c ON u.id = c.user_id

• 2)右外连接（RIGHT [OUTER] JOIN）

SELECT * FROM USER u RIGHT JOIN country c ON u.id = c.user_id

16.mysql中的整型长度含义

下一篇：Mysql 慢查询Sql分析 EXPLAIN分析慢SQL(3)