Mysql优化实例以及重要知识

用excel批量生成insert语句，放入sql文件，在navicat中执行sql。加大数据库压力。

1、当数据量为w级别时，置顶贴存入缓存就会变得缓慢。同样精华也是如此，此时需要对topping/essence字段创建索引，这样就不用全表查询，效率提高。

CREATE INDEX toppingIndex on question(topping)
CREATE INDEX essenceIndex on question(essence)

同理listByUserId(user.getId(),page,size); 对creator加索引也有很好的效果，原理都是加单列普通索引，不展示。

2.查找相关标签问题时，去除本问题 - >主键索引失效，再用like进行tag模糊查找，效率比较低。

对几万条优化：

后者比前者快17%左右，还是不错的。

select * from QUESTION where id !=5 and tag like '%学习%' 

CREATE INDEX tagIndex on question(tag)
SELECT * FROM question a,(select id from QUESTION where tag like '%学习%') b WHERE a.id = b.id AND a.id !=5  

但如果是十万百万，建议分tag表关联，或者用ES中间件，0.1s以下是可以接受的。

---

3.大数据量进行分页操作，10000条数据，按发时间倒序，找9000条开始后面的10个则会浪费前面以及找到的数据所画的时间。

显然后者通过联表查询关联id，利用id主键索引查询会更快。

EXPLAIN SELECT * FROM question ORDER BY question.gmt_create DESC LIMIT 9000,7

EXPLAIN select * from question a,(select id from question ORDER BY question.gmt_create DESC limit 9000,7) b WHERE a.id =b.id

要点概述：

锁

Mylsam引擎下的读锁和写锁表锁

innodb既有表锁也有行锁

读锁

• 只能读，不能改，且只能读当前锁住的表。

• 不同服务端都可以加，互不干扰。（共享锁）

lock table XX read;

写锁

lock table XX write;
unlock table;

• 写锁是互斥锁，只能加一个

• 一个服务端加了以后，可以读，也可以写

• 一个加了以后，其他服务就不能读也不能写。

行锁

开销大，会出现死锁，但并发性好。

简单来说就是事务还未提交时不能对这个事务所在行进行操作，但是可以对当前的表的其他的锁操作。

日志

• 错误日志

• 二进制日志，8.0之前需要手动修改配置文件-> 重启服务即可。

  • binlog日志

  • 查询日志

  • 慢查询日志（查询时间超过某个值则记录下来，用户后期优化）

优化

• 从设计上优化

• 查询优化

• 索引优化

• 存储优化

定位低效率的SQL语句：

慢查询日志记录（配置文件开启慢查询日志、修改时间阈值）

show processlist命令查看

explain语句分析执行计划

id：越大优先级越高，越先执行；若相同则表示没有优先级之分，则从上到下执行。

select_type:查询类型：主查询，primary；子查询；derived,from中包含子查询；union，联合查询。

type（快慢关键）

System：系统表

const:唯一索引/主键索引根据常量查询。

eq_ref:连表查询，左表有主键列且左表每一行和右表每一行一对一的关系。

ref:左表普通索引可能会重复，不是一对一的。

range：范围查询

index：索引列全部扫描，查索引列。

ALL：最差；

key_len

实际使用的键长度，有公式：例如int，主键不为null则4，可以为null则4+1。

show profiles

查看sql语句的消耗时间

trace：查看sql优化器内部步骤。

怎么使用索引优化

• 创建组合索引

  • 采用全值匹配就与查询字段顺序无关了

  • 最左匹配法则：查询必须包含最左则索引，不能跳跃使用，否则索引失效。只要包含了从左到右按顺序的索引，查询时候的书写顺序则无所谓。

  • 范围查询的后面索引列会失效，只有前面的索引生效。

  • 索引上使用运算则会失效

  • 字符串类型，不加 ‘ ’ 也会失效，有时候mysql也会进行类型转换。

• 尽量使用覆盖索引，避免使用 * 号，用什么查什么。不然都得过磁盘有IO操作。

• 使用OR会使得索引失效

• LIKE模糊查询会使索引失效

  • ”XX%“用索引

  • ”%XX“失效

  • ”%XX%“失效：解决：使用索引列，不要用*

• 如果全表比索引快，优化器就不用索引了。（重复的太多了）

• 索引会根据数据优化，少的用索引，查询的条件就是大多数的话就不用了。

• 普通索引：in：使用；not in：不使用

• 逐渐索引：in/not in 都使用。

• 一个表有多个单例索引，只会有一个最优的生效（优先选择重复少的和上面not in一个逻辑）。（所以尽量用复合索引）

sql优化

大批量插入

• innodb按主键顺序插入比较快。（主键有序）

  所以load加载数据时尽量保证文件中主键有序，可以提高效率。

• 关闭唯一校验

insert优化

执行一个insert就会与数据库连接/断开，一个一个来会太频繁

解决：1.关闭事务提交，批量插入再提交。

2.多条一起插入

3.插入数据主键尽量有序。

order By优化

1.尽量不要select *

2.order By后面多个排序字段，尽量排序方式相同，且字段和组合索引顺序相同。不然效率变低。

• filesort优化

  提高 max_length_for_sort_data和sort_buffer_size优先再内存计算排序会快一点。

子查询优化

联合查询效率大于子查询

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

limit优化

1.在索引上完成排序分页操作

--慢
select * from tb_ user limit 900000,10; 
--快
select * from tb_user a, (select id from tb_ _user order by id limit 900000,10) b where a.id = b.id; 

2.如果主键是自增的

--超级快
select * from tb_ user where id > 90000 limit 10;