MySQL优化查询相关

【查询优化相关】

1.如何定位相关慢的查询：

   a.可以开启慢查询日志，也可以使用show profiles 去记录相关查询到一个临时表再分析。  

   b.show processlist  看看有没有大量等待的进程之类的。

   c. desc 或者 explain 详细看一下某条sql的索引使用情况，影响行数等。

 

2.如何进行优化:

  a.查询时，用对索引，减少大量无用的行数扫描。 以及所查即所需，尽量不要使用select * ，制定好使用的列，避免大量冗余数据扫描。以及大量重复查询，在应用层做一下缓存。

 b.分解部分复杂的查询变成相关少量的多条， 可以提高缓存效率，单挑查询减少锁的竞争等。

 c. 特定类型相关优化，尽量不用子查询，用关联或者拆分代替，count(*)，加*号会不扫描列，只扫描行。

 

 

【具体索引优化】

1.insert多次插入的时候选择批量插入。

2.%开头的Like无法使用索引。

3.数据类型隐式转换无法使用索引。int 转 string 这种

4.如果mysql使用索引时比全表扫描慢则不使用索引，扫描行数超过30%一般就会不用索引了。（也是避免了频繁的普通索引和聚集索引的切换）

5.大的文本或超长字段不要建索引。

6.垂直分表，水平分表相关，这时候一般可以考虑人为生成主键id，单要维持顺序。

7.避免null字段 原因如下：

（1）所有使用NULL值的情况，都可以通过一个有意义的值的表示，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性。

（2）NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。(null -> not null性能提升很小，除非确定它带来了问题，否则不要当成优先的优化措施)

（3）NULL值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp

（4）NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错

 

 

【聚集索引和其他索引】

1.Mysql 主键索引和其他 索引的区别：

a. 主键索引天生唯一，而且不能有null值，唯一索引虽然也唯一，但是可以为null。

b.主键索引属于聚集索引，存储结构中存了主键索引的id以及行记录（B+树的叶子节点，叶子节点也就是没有子节点的节点。），而其他索引的存储结构中，存的是索引内容以及主键聚集索引的值，查的时候通过索引再结合主键索引指向的位置获取数据。

c.（再顺带说一下为什么主键一般自增，因为自增的话就不用去改变节点的结构，只需要在最后追加就行，不然就要不断调整数据的物理地址分页等，加大io损耗）。

 

 

【其他】

1. count(1) 和 count(*) 和 count(name) 的区别

count(1) 和 count(*) 无任何区别,因为()里的不是字段名 也非null。 如果count(null)  则直接等于0 不扫表了。

count(name) name是字段名，这样的话如果name字段为null的就不会计算在总数中。