Mysql 随心笔记

创建索引后但不生效：

1. %放在索引字段前面  select * from user like '%123'

2. where 子句里对有索引列使用了函数  select * from user where reverse(name)='123'

3.类型不一致，name 是字符串  select * from user where name =123

4.!=(主键除外) select * from user where name !=123

5.非主键字符串用>

6.组合索引，但是其他有字段不是索引字段

7.or 条件有未索引字段

8.where子句中，对索引字段使用数学运算

9.如果Mysql估计使用全表扫描比索引快，也不使用索引

 

SQL优化

1.单表优化

a.索引不能跨列使用（最佳的做前缀），保持索引的定义和使用的顺序一致性　　

b.索引要逐步优化

c.将含 in 的范围查询放到where 最后 ，in有可能会使索引失效

Using where ：需要回原表查询

Using index ：不需要回原表

 2.多表优化

 a.小表驱动大表

 两表left join 连接 on 后面 为 小表.Id=大表.Id

给小表Id添加索引

 

3.避免索引失效原则

a. 复合索引，不要跨列或无序使用（最佳左前缀）

b. 复合索引，尽量使用全索引匹配

c. 不要在索引字段进行任何操作（计算、函数、类型转换），否则索引失效

d. 复合索引不能使用不等于（!= <>）或is null  （is not null），否则自身以及右侧所有索引全部失效

e. like 尽量以“常量”开头，不要以“%”开头，否则所以失效  

select * from user where username like '%aa%'

优化 select username from user where username like '%aa%'   索引覆盖

f. 尽量不要使用类型转换（显式或隐式），否则索引失效

g.尽量不要使用or，否则索引失效

4.常见优化方法

a. 如果主查询数据量大，则使用in    如果子查询数据量大，则使用exist

b. order by 优化

选择使用单路、双路；调整buffer的容量

避免使用select *  ，写实际的字段名称

保证排序的一直性（都是升序或者降序）

5.SQL排查 - 慢查询日志 ：Mysql 提供的一种日志记录，用于记录MySQL中响应时间超过阈值的sql语句

默认是关闭状态

 

 

 

 

 

Mysql 常见函数

1.having的用法

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。我的理解就是真实表中没有此数据，这些数据是通过一些函数生存。

案例：显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。
相反，having子句可以让我们筛选成组后的各组数据