MySQL语句优化原则

1. 小表驱动大表(针对查询),可以减少IO

2. 最左前缀法则(针对索引),索引了多列的时候,要确保第一个列一定要存在,且中间的也不能断,不然就查询不到,百分号放在左边会全表扫描,且索引失效,要放右边

3. 避免频繁创建和删除临时表，以减少系统表资源的消耗

4. 应尽量避免全表扫描, 以下的操作可能会导致全表扫描:

    )在 where 子句中使用!=或<>操作符,可能会引起全表扫描

    )在 where 子句中对字段进行 null 值判断,可能会引起全表扫描

    )在 where 子句中使用 or 来连接条件,可能会引起全表扫描

    )使用like前置百分号查询,一定会引起全表扫描

    )in 和 not in 也要慎用,可能会引起全表扫描

    )在 where 子句中使用参数,可能会引起全表扫描

    )在 where 子句中对字段进行表达式操作,可能会引起全表扫描

    )在where子句中对字段进行函数操作,可能会引起全表扫描

---

MySQL之索引优化

---

什么是索引：排序的快速查找的数据结构  简称 搜索+排序

索引的优势:提高检索效率,降低IO成本; 通过索引对数据排序,降低数据排序成本,降低CPU消耗.

索引的劣势:索引要占内存空间; 会降低降低更新表的速度,因为更新表时,MySQL不仅要保存数据,还要更新添加的索引列的字段.

为什么需要索引：Mysql的主键唯一标识,也就是索引,且是按照BTREE,但是假如需求是查身份证,怎么才能快速定位到某个身份证号呢,这就涉及到了给相关字段建索引,这也是优化的方向!

怎么建索引：

1. 使用ALTER TABLE命令去增加索引：

ALTER TABLE table_name ADD INDEX index_name (column_list)//添加普通索引，索引值可出现多次。

ALTER TABLE table_name ADD UNIQUE (column_list)//这条语句创建的索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。

ALTER TABLE table_name ADD PRIMARY KEY (column_list)//该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE table_name ADD FULLTEXT index_name(olumu_name);该语句指定了索引为FULLTEXT，用于全文索引。

2. 使用CREATE INDEX命令创建：

//标准语句：

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

//针对上述数据库：

CREATE INDEX classify_index  ON commodity_list (Classify_Description)

---

看查询的性能及详细信息

关键字:explain

explain的用法:explain select * from emp;

出现的结果：

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra

着重关注type，key，rows，Extra

type:system>const>eq_ref>ref>range>index>ALLALL相当于全表检索,性能最差,也恰恰是最多的,index也是全表检索,但是检索的是索引,一般我们要优化到range,最好能够到ref.再之上的就是一次就查到的那种,我们不予考虑.

key:key是实际上用到的索引,possible_keys是理论上用到的索引,我们只看key就好.

rows:所读取的行数

Extra:

Extra这里不能出现Using filesort和Using temporary,一旦出现了,就表示非常不好了,必须优化或者直接瘫痪了.

Extra中的Using index表示覆盖索引;

Extra中的Using where表示使用了where过滤

Extra中的using join buffer表示使用了连接缓存

Extra中的impossible where表示where子句的值总是false，不能用来获取任何元组

---

MySQL怎么避免索引失效

借鉴一位大神的一段话...

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

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

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

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

不等空值还有OR，索引影响要注意；

VAR引号不可丢， SQL优化有诀窍.

---