高性能mysql学习笔记

原创 2018年04月14日 23:38:45

简要记录一下 高性能mysql 的学习. 书的链接: http://shop.oreilly.com/product/0636920022343.do

1.条件语句后面不要加运算表达式,举例如下:

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
因为这里有个计算表达式,数据库不会优化,所以开发者应该改成 actor_id =4;

2.改变数据的默认值: modify the .frm value rather than the original table, which can avoid table duplication

3.对字符串的prefix index
对于BLOB or TEXT,整个字符串太长,不能对整个字符串做index,解决方法是 Prefix indexes(只index某个长度的index),
而这个长度需要根据统计信息,统计的思想是:用尽量短的长短,使通过这个长度的index,找到尽量少的结果(减少最后匹配次数)。 举例如下:
数据库存有apple,average,application,现在要搜索apple。 
假设index长度为1,先开始根据a搜到apple,average,application,然后再从三个里面逐一匹配
如果index长度为2,先开始根据ap搜到apple,application,然后再从两个里面逐一匹配。

MySQL cannot use prefix indexes for ORDER BY or GROUP BY queries

4.suffix index
对某些数据(email address)suffix index会很有效,但mysql不支持,解决方案是把字符逆序存进去

5.不同版本的mysql对query有不同执行方案,举例如下:
mysql> SELECT film_id, actor_id FROM sakila.film_actor
-> WHERE actor_id = 1 OR film_id = 1;

在index了actor_id 和film_id的情况下,mysql 5.0以前是不会对查询做任何优化,只会做table scan
但5.0以后版本会union of two index scans(先分别index,然后union)

但要注意的是,union of two index scans不一定比 table scan效率好(因为如果两个index scans返回大量结果(书上叫not selective),那么merge的cost就很高)
一个可能的解决方案是 使用single index on multiple column,但这个方案会带来问题(1.索引太大 2.建索引时间长)

single index on multiple column 是使用multicolumn b tree,应该把可选择高的column放在最前(将第一次查询的返回结果尽量减少)

6.不同数据库引擎的(innoDB MyISAM) 区别:聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)
http://www.tuicool.com/articles/VramY3Y
InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上
MyISM使用的是非聚簇索引,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键, 叶子节点都是指针, 指向行数据【1】

多个单列索引和复合索引区别:
因为数据库查询时候只能执行一个索引,所以多个单列索引不会对组合的搜索词有帮助
复合索引是指在第一列排序的前提下,再对第二列排序,所以对组合关键词的搜索有帮助,但前提是符合索引的第一列要是where里面的第一关键词【2】

covering index:在index加上行其他的数据,
用处一: 在innodb的 second index很有用,因为在second index搜完还要在first index上面搜。
在covering index帮助下,直接在second index上搜完就好
用处二:数据排序,直接遍历index

【1】:http://www.tuicool.com/articles/M3IrE33
【2】:http://www.cnblogs.com/gudi/archive/2014/10/29/4058411.html

7.索引

因为建索引需要cost维护(同步更新),所以要衡量是否建索引
建索引考虑两点:
1.可选择性(所建的列是否有很多不同的值)
2.使用频率(是不是很多query都用得上)
3.考虑多个query而不是单个query


举例,假设要对一个人建table,属性如下:(sex, country, region, city, age).
对于sex这属性,可选择性低,但很多query会以sex区分,所以应建立索引 sex 

假设现在有索引(sex,country),但如果query仅仅包括country,而没有包括sex,那么如何加快query?
方法一:单独建country的索引表,但增加维护的cost
方法二:在query里面加上 in('m','f'),那么query会利用索引(sex,country)。因为如果query仅包含country,country不符合prefix原则,不能使用(sex,country)索引

8.实时性不高的query

对于某些实时性要求不高的query,例如查七天没登录的用户,直接从lastonline与now时间来计算的话,会导致查询(range query)很慢.
解决方法是:设置一个active属性,设置一个定时任务在后台定时更新维护

9.解决数据库频繁的短连接

引入连接池来管理连接的原因:
1.创建数据库连接有时间开销,2.频繁创建短连接会导致不够时间关闭连接(例如for循环里不断创建并关闭,关闭不够创建得快),导致连接达到上限。
解决方案:提前创建连接,限制连接上限



《高性能MySQL》学习笔记二

今天打算继续学习《高性能MySQL》的第四章,有关数据库的优化。Schema与数据类型优化前置知识:如何设计数据库? 关于这方面我是在CSDN博客上找文章看的。选择优化的数据类型三大原则:(简单、小...
  • u012723607
  • u012723607
  • 2017年08月09日 15:35
  • 191

《高性能MySQL》读书笔记--多版本并发控制算法

1.AUTOCOMMITMysql采用默认自动提交,可以通过如下命令查看和修改:mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';+------------...
  • xifeijian
  • xifeijian
  • 2015年04月25日 22:45
  • 7259

【学习笔记】高性能MySQL(第三版)——第1章:MySQL架构与历史

MySQL并不完美,却足够灵活,能够适应高要求的环境,例如Web类应用。同时,MySQL既可以嵌入到应用程序中,也可以支持数据仓库、内容索引和部署软件、高可用的冗余系统、在线事务处理系统(OLTP)等...
  • qq157962718
  • qq157962718
  • 2016年03月28日 01:08
  • 873

高性能MySQL学习笔记(1) —— MySQL架构

MySQL逻辑架构 MySQL逻辑架构图如下: 这里分为三层: 1.连接层:连接与线程处理,这一层并不是MySQL独有,一般的基于C/S架构的都有类似组件,比如连接处理、授权认证、安全等。 ...
  • a327369238
  • a327369238
  • 2016年09月02日 11:20
  • 744

《GPU高性能编程CUDA实战》学习笔记(六)

第6章 常量内存与事件
  • w09103419
  • w09103419
  • 2016年09月11日 21:29
  • 576

高性能MySQL_第3版(中文)pdf

下载地址:网盘下载 内容简介 编辑 《高性能MySQL》是分享MySQL实用经验的图书。它不但可以帮助MySQL初学者提高使用技巧,更为有经验的MySQL DBA...
  • cf406061841
  • cf406061841
  • 2017年06月03日 23:12
  • 5112

性能优化——记高性能MySQL

MySQL服务器逻辑架构 第一层,客户端/服务器。负责连接,授权,安全等。每个客户端连接都会在服务器拥有一个线程。解析器解析查询并创建解析树,然后优化(重写查询,选择索引等)节奏执行,select语...
  • qq_29423883
  • qq_29423883
  • 2017年08月10日 17:17
  • 540

【读书笔记】Linux高性能服务器编程(第一篇 第四章)

摘要祭BUG
  • ChenxiCBLOG
  • ChenxiCBLOG
  • 2015年06月24日 16:26
  • 681

《高性能MySQL》读书笔记(上)

《High Performance MySQL》真是本经典好书,从应用层到数据库到硬件平台,各种调优技巧、常见问题全都有所提及。数据库的各种概念技巧平时都有接触,像索引、分区、Sharding等等,但...
  • dc_726
  • dc_726
  • 2014年12月06日 19:31
  • 6697
收藏助手
不良信息举报
您举报文章:高性能mysql学习笔记
举报原因:
原因补充:

(最多只允许输入30个字)