高性能的mysql

了解mysql查询过程

1. 客户端发送一条sql请求到缓存中
2. 命中缓存的话，直接返回结果
3. 经过sql解析器，优化器，分解成查询计划
4. 通过存储结构的api，查询计划调用api返回结果，如果命中缓存的机制，也需要缓存这部份数据

• 返回的数据，有可能是多个数据包，但不可能只保留一部分的数据包
• 使用了tcp的协议来返回数据包，也就是说是有顺序的
• 查询语句太长的话，也就说客户端发送的包数据太大，会导致服务端卡死，可以修改max_allowed_packet参数来约定包的大小
• 尽量避免使用*和多使用limit关键字

查询缓存

预先定义一部分sql语句可以缓存数据，通过sql_cache和sql_no_cache来设置
这一部分数据，会缓存到一个hashmap表中，下一次同样的sql语句，就不需要再次解析sql语句等操作

• 需要sql语句和缓存中定义的完全一直，多一个空格，有注释等都会导致匹配不上
• 查询语句有包含自定义函数，内置方法，临时表，系统表，用户的变量都会匹配不上
• 读写操作都会导致性能的消耗，写操作会将之前的缓存数据都清空
• 读操作由于每次sql语句都会检查一次，并且得到结果还需要缓存，这也需要造成系统的额外消耗

查询机制

1. mysql解析sql语句，转化成查询计划，寻找最优的一个
2. 最优的并不代表是时间最短的，可以查看show status like 'last_query_cost';来获取上次查询语句的消耗

执行查询计划

• 通过查询计划，调用底层的api接口来返回数据

返回结果

• 有可能第一个结果生成就开始返回，这样可以节约资源

优化查询

设计表阶段

1. 针对not null字段，只有对该列进行索引了才有效，不然和其他列查询起来是一样的效率
2. 整数型设置宽度没啥用，int都是使用了32位来存储，也就是说int(1)和int(100)是一样的效果
3. 使用浮点数时，可以将浮点数转换成bigint，例如对该值乘以1百万才存储进去，可以节省mysql对浮点数精确计算的消耗
4. timestamp类型用4个字节来存储，因此只能保存1970-2038年的数据，而datetime类型使用了8个字节，能使用的范围更广
5. 表的列不要太多，查询计划调用api来获取数据，在服务器层需要将这些数据按照列来解码，这个过程很消耗资源
6. 不要对大表来执行alter table，因为这个过程实际上是删除老表，把数据存入新表，再重新建索引，比较耗时间

索引

索引一般使用B树算法来执行，InnoDB使用了B+树,平衡二叉树由于树的高度不会大于1，因此，查询的效率比二叉搜索树更快

• 二叉搜索树
  

• 平衡二叉树
  

B树

二叉搜索树，左节点 < 根节点 < 右节点，这样的机制能使索引查找最大最小值能容易

B+树

多分支的平衡树

• innodb使用该算法，所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page），非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
• B+树对叶子节点数据存储满的清空下，会使用拆分的操作，也就是从中间把叶节点拆开，n/2 + 1的关键字保存到根节点(如果根节点还有空间的话，否则根节点继续拆分),然后就可以将新数据存储到位置
• 如果当前叶节点的数据满，但左右两边的叶节点还有空间，会首先来左右移动位置，以方便存储数据

1. mysql不会使用非独立的列，例如select * from xx where id + 1=4，这个sql语句无法使用索引
2. 前缀索引，如果当前列数据很长，那么只索引部分字段也可以
3. 多列索引，在多个列建立独立的索引，并不会带来过多的优化，sql语句中包含多个and筛选，这多列做成一个索引优于建立多个独立索引；如果是or的状况的话，一般直接扫描全表来的更快一点；多列索引对关键字的顺序要求比较高，把选择性更高的关键字放在第一个位置会加快查询效率
4. 要避免多个范围查询，对这些范围所在的列做索引并没达到预期的目的，mysql并不支持利用索引来进行多个范围的查询，例如select user.* from user where login_time > '2017-04-01' and age between 18 and 30;
5. 如果要查询的值都在索引列中，那么使用覆盖索引效率会更高；索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量；索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多
6. 对于大多数情况，扩展索引优于创建新索引
7. 删除长期未使用的索引

特定类型优化

count(*)

1. 如果不需要准确值，那么直接explain命令得到近似值也可以
2. 统计行数，使用count(*)意义更清晰

union查询

1. 都是把数据全部存入临时表，然后再从临时表读取数据，返回客户端
2. all关键字如果不加，默认是distinct，会增加消耗
3. 更方便的做法是直接返回子查询的结果

limit

1. 如果间隔量太大，使用覆盖索引来做会优化更高

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
替换成(使用title做索引)：

SELECT film.film_id,film.description

FROM film INNER JOIN (

    SELECT film_id FROM film ORDER BY title LIMIT 50,5

) AS tmp USING(film_id);

详细资料