mysql常用的优化方法

一条SQL执行很慢？

1.执行的时候遇到行锁，表锁
2.没有建索引，或者建了索引没有用到，需要去分析

怎么判断一个 mysql 中 select 语句是否使用了索引，可以在 select 语句前加上 explain，
比如 explain select * from tablename;返回的一列中，若列名为 key 的那列为 null，则没有使用索引，
若不为 null，则返回实际使用的索引名。让 select 强制使用索引的语法：
select * from tablename from index(index_name)；

3.数据库在刷新脏页，例如redolog写满了需要同步到磁盘。

MySQL 数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL。

慢查询日志功能开启：
slow_query_log ：是否开启慢查询日志功能（必填）

long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填）

log-slow-queries ：慢查询日志文件（不可填），自动在 \data\ 创建一个 [hostname]-slow.log 文件

配置：
set global slow_query_log = ON; # 开启这个功能

set GLOBAL long_query_time = 1;# 设置查询“超时”时间
当我们开启这个功能时候每一条sql语句如果执行时间超过了设置的时间，就会被写入到日志文件里面
在定位到我们有问题的sql文件以后：

常见索引类型

• 主键索引
  它是一种特殊的唯一索引，不允许有空值。
• 普通索引
  最基本的索引，它没有任何限制。
• 唯一索引
  普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。
• 联合索引
  多个列值的集合。
• 覆盖索引
  通过索引数据结构，即可直接返回数据，不需要回表
• 前缀索引
  char/varchar太长全部做索引存在浪费且效率差
  Blob/text类型不能整列作为索引，所以需要前缀索引
  无法利用前缀索引完成排序
• 索引为什么不可用
  通过索引扫描的记录数超过30%，变成全表扫描
  联合索引中，第一个索引列使用范围查询（这时用到部分索引）
  联合索引中，第一个查询条件不是最左索引列
  模糊查询条件最左以通配符%开始
  HEAP表使用HASH索引时，使用范围检索或者ORDER BY
  多表关联时，排序字段不属于驱动表，无法利用索引完成排序
  两个独立索引，其中一个用于检索，一个用于排序（只能用到一个）
  复杂SQL

如何对我们的sql进行优化

1.选择最适用的字段类型（数据库中的表越小，在它上面执行的查询也就会越快）
比如一个字符串，能够确定长度，就用char（6）而不要用char（255）这样给数据库增加不必要的空间，还有varchar长度是动态可变的（它更节省空间），char的效率比varchar快。
高重复的字段将其转换为数值类型。数值类型的处理比文本类型快了很多。
对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
2.对于limit分页的优化：
使用有索引的列来做order by操作。

3.使用索引注意项：
负向查询不能使用索引（尽量避免使用 != 或 not in或 <> 等否定操作符）

select name from user where id not in (1,3,4);
应该修改为:
select name from user where id in (2,5,6);

前导模糊查询不能使用索引

select name from user where name like '%zhangsan'
非前导则可以:
select name from user where name like 'zhangsan%'
建议可以考虑使用 Lucene 等全文索引工具来代替频繁的模糊查询。

尽量避免使用 or 来连接条件
如果明确知道只有一条记录返回

select name from user where username='zhangsan' limit 1
可以提高效率，可以让数据库停止游标移动。

不要让数据库帮我们做强制类型转换

select name from user where telno=18722222222
这样虽然可以查出数据，但是会导致全表扫描。
需要修改为
select name from user where telno='18722222222'

如果需要进行 join 的字段两表的字段类型要相同
不然也不会命中索引。
在字段上进行计算不能命中索引（不要在列上使用函数）

select name from user where FROM_UNIXTIME(create_time) < CURDATE();
应该修改为:
select name from user where create_time < FROM_UNIXTIME(CURDATE());

数据区分不明显的不建议创建索引
如 user 表中的性别字段，可以明显区分的才建议创建索引，如身份证等字段。
字段的默认值不要为 null
这样会带来和预期不一致的查询结果。