高性能mysql 树_高性能mysql精要

1、explain 中 extra (using index ) 表示覆盖索引，sql优化中最好能使用覆盖索引，否则(二级索引)需要回表查询。所谓覆盖索引，是指要查询的列正好是索引，而条件也是这个索引之一

2、where 语句中 条件等于主键的 ，在内核索引层完成，条件等于非索引的，在服务层完成

3、读索引加共享锁，读主键加排他锁

4、当有连接表而其中一条语句中有含有where = 主键的，优化器会优先执行这条语句，从而减少查询的数量

5、最左前缀原则，即 a,b,c为组合索引 ，语句 where a='A' and b='B' and c>'C' 可以使用索引，顺序可以打乱，如 where b='B' and c>'C' and a='A' 。而where b='B' and a>'A' and c='C' 无法使用索引，因为 a>'A' 这里使用了大于号不满足最左前缀原则

6、 %like% 无法使用索引，而like%可以

7、索引采用b+树的原因：①b+树树高小于二叉树，b+树每一层等于物理内存中的一页，每次io都把所有数据读取出来，从而减少了访问io的次数。②叶子节点是一个单链表，在使用范围 between 、in等语句时速度很快

8、由于数据库的逻辑结构是根据索引排序，如果order by 不适用索引将会全表扫描，下面是一个order by 的优化例子：

①select cols from profiles where sex ='M' ORDER BY rating LIMIT 10;

其中 sex ,rating 联合索引

②使用覆盖索引

SELECT cols from profiles INNER JOIN ( SELECT primary key cols from profiles where x.sex='M' ORDER BY rating LIMIT 10000,10) AS x using primary key cols

这里子句使用了覆盖索引

9、分解关联查询

好处① 让缓存的效率更高②减少锁的竞争③查询效率本身可能会提高④可以减少冗余记录

10、mysql执行的基础：①客户端->②查询缓存->③解析器->④解析树->⑤预处理器->⑥查询优化器->⑦查询执行计划->⑧查询执行引擎->⑨api接口调用存储引擎->⑩数据->11结果返回

11、mysql在from 子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中(临时表时没有任何索引的，在编写复杂子查询和关联查询的时候需要注意),然后将这个临时表当一个普通表对待

12、mysql的执行计划是一颗左侧深度优先的树

13、mysql 对where 条件中包含IN()的子查询语句实现的很糟糕(但对IN()中包含列表则较好，特别是数据量大的时候)

14、Extra 字段显示"using index for group-by"表示使用松散索引扫描

15、最大值最小值优化：

select MIN(actor_id) from actor where first_name='PENELOPE' first_name无法使用索引，改写为:

select actor_id from actor using index(primary) where first_name ='PENELOPE' limit 1;先使用主键索引，在服务层找到符合的后直接停止，因为主键本身就是排序好的

16、优化count()

SELECT COUNT() FROM CITY WHERE ID >5 改写 为：

select (select count()from city-count()) from city where id<=5 子句是常量，父句数量少

17、优化关联查询:

①确保on 子句中的列上有索引，当表A和表B用c列关联的时候，如果优化器的关联顺序是B、A,那么就不需要在B表上对应列上建索引(因为B表是使用全表扫描)

②确保任何的group by 和order by 中的表达式只涉及到一个表中的列

18、优化子查询:

尽可能转换为关联查询(不是绝对的)

19、优化group by:

如果需要对关联查询做分组，并且是按照查找表中的某个列进行分组，那么通常采用查询表的标识列分组的效率会比其他的更高

select actor.first_name ,actor.last_name ,count() from film_actor inner join actor using(actor_id)

group by actor.first_name,actor.last_name 改写为:

select actor.first_name ,actor.last_name ,count(*) from film_actor inner join actor using(actor_id)

group by actor.actor_id

20、mysql 可重复读是如何实现的？

采用mvcc，是通过在每行记录后面保存两个隐藏的列来实现的，这两个列，一个保存了行的创建时间，一个保存了行的过期时间。存储的不是时间而是系统版本号。①只查找版本早于当前事务版本的数据行②行的过期版本要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行，在事务开始之前未过期