effective mysql系列_《Effective MySQL之SQL语句最优化》读书笔记——乱七八糟系列（给自己看）...

该书区别于诸如《MySQL技术内幕——InnoDB存储引擎》等书的一大特色是该书主要讲的是MySQL数据库中的索引技术，并分别讲了InnoDB、MyISAM、Memory三个存储引擎。其中重点当然是InnoDB。该笔记记得是乱七八糟，只适合自己看，记此笔记也是为了以后自己快速查阅回忆看这本书的内容，看起来会有断章取义的可能。

识别性能的问题：show full processlist

索引的用法：

优化数据访问

保持数据的完整性(主要通过外键技术)

表连接优化

结果排序

聚合操作

唯一键可以包含NULL值，并且每个NULL值都是唯一的(即NULL！=NULL)。

三个查看表信息的语句：

show create table

show table status

information_schema tables

索引技术：

B-树

B+树

R-树

散列表

索引的实现：存储引擎实现、数据结构技术

索引的类型：

唯一键

非主码索引

全文本索引

空间索引(R-)

目前的MySQL默认的存储引擎中，只有InnoDB存储引擎支持外键(Foreign key)且不需要存在对应的索引。但是考虑性能应该加上外键索引。

相对于MyISAM，在InnoDB中，数据是按照有序的方式存储的。

当某列声明索引后，在DB中，就存在B+树结构，查询时返回root地址即可。

B+树的索引节点中仅仅包含其子树跟节点中的最大和最小关键字。(这个特别重要，只有这样才能在外存的整页加载带内存是才能比较，B+树经典结构)

InnoDB中聚簇主码是用B+树存储的，但是非主码索引使用的是B-树存储的，但是此B-树不同于MyISAM中的B-树，InnoDB中非主码索引存储的是主码的实际值，MyISAM存储的是包含主码值得数据的指针。

填充因子：

由于InnoDB用聚簇主码存储数据，底层信息占用的磁盘空间的大小很大程度上取决于页面的填充因子。主要用来估计潜在的磁盘存储空间需求。

排序/非排序主码对填充因子的影响

按序排列的主码

用16K页面的15/16作为填充因子。例：id自增

例：id自增

不按序排列的主码

默认情况下，InnoDB会在插入初始数据的时候为每个页面分配50%作为填充因子。例如：学号Sno等现实中的主码。

例如：学号Sno等现实中的主码

聚集索引：

定义：根据数据行的键值在表中排列存储数据行。每表至多一个。

只有表中包含了聚簇索引时，表中的数据行才按照排序的顺序存储。

若没有聚簇索引，则其数据行存储在一个称为堆得无序结构中。

在实际的存储空间中，按序排列的主码空间小；不按序排列的主码空间大。

主码索引和聚集索引的区别：

一般情况下，当建立主键之后，会自动为主键建立聚集，唯一索引。

但是也可以把主键的聚集索引去掉，可以为其他属性建立聚集索引，但是唯一索引不能去。

并且一张表只能有一个聚集索引。

InnoDB内部的散列索引：

InnoDB存储引擎在聚簇B+树索引中存储主码。但是InnoDB内部是使用内存的散列表更高效的进行主码查找(Bloom Filter布隆过滤器)。

单列索引

ALTER TABLE

ALTER TABLE

possible_keys & key & Cardinality基数

例：select * from music where type='BAND' and founded='1980'

设两列均有索引。这就牵扯到索引选择的问题。

这时候就需要Cardinality。他会表明唯一值得个数，越大越代表区分度，故优先选择cardinality比较大的索引。(自动优化问题)——因为列值大就代表区分度高，不相同的值比较多，可以第一次就缩小成很小的集合。

使用索引值进行字符串进行模式匹配的问题：

1、利用通配符%可以做模式匹配的操作。例如：like 'Queen%'

2、如果查找的词是以通配符开头，则MySQL不会使用索引。例如：like '%Queen%'

技巧：使用反序值。reverse_email like REVERSE(‘%.com’)。就可以支持索引了。

3、MySQL中不支持基于索引的函数。

例如：WHERE UPPER(name) =UPPER('Alice')

唯一索引：

完整性。

”短路操作“——告诉优化器只有一行返回，从而避免了额外的索引扫描。

注：在可以为空的列上定义唯一索引也行，NULL！=NULL。三态逻辑。

多列索引：

别名：混合索引或者连接索引

说明：尽管索引的基数是唯一性的重要指标，但也会参考有关唯一性范围和容量的统计信息。

语法：

ALTER TABLE

ALTER TABLE

key_len=7:

这是用来确定索引值所使用的列的效率工具。

当使用多列索引时，交换列的顺序可能会创建更好的索引

其他用途：

最左边的列可当做单一列的索引来高效的使用。例如：group by order by.

应该经常评估多列索引是否比让优化器索引(合并效率更高)

索引造成的影响：

DML影响：重复索引(删多余－针对多列索引第一列情况)；索引的使用(删无效－针对不使用的情况)

DDL影响：

磁盘空间的影响：填充因子，非主码索引。

MySQL的限制和不足：

基于开销的优化器；

指定QEP：

索引的统计信息

基于函数的索引没实现：一个表上面多个索引

创建更好地索引：(覆盖索引、局部索引)

当QEP中Extra列显示using index时候，并不意味着访问底层数据时使用了索引，而是表示只有这个索引才是满足查询所有要求的。

覆盖索引：

指定满足了查询中给定表用到的所有的列。where子句、order by、group by以及select语句中的所有列。

在InnoDB中，主码的值会被附加在非主码索引的每个对应记录的后面。

select语句中，使用索引的列会比没使用索引的列查的快。

技巧：不要所有的查询都是用select * from table，而是需要啥查询啥。一是I/O开销，而是查询开销。

覆盖索引适合那些很多主码较小长度和外键约束的大型规范化约束来说是理想的方式。

SQL优化的生命周期：

1、截取SQL语句；

2、识别并分类有问题的SQL语句

3、确认SQL语句的当前操作

4、分析SQL语句和辅助信息

5、优化SQL语句

6、验证SQL语句的优化结果。

截取SQL语句的途径：

全面查询日志：实验室使用

慢查询日志：实用

二进制日志

应用程序代码

插件

TCP/IP分析

性能优化之隐藏秘籍——索引管理优化

1、整合DDL语句。就是几个SQL写在一个中

2、去除重复的索引；

3、删除无效的索引；

4、删除不同的索引。

索引列的改进：

数据类型：

BIGINT-->INT UNSIGNED

DATATIME-->TIMESTAMP

ENUM

NULL-->NOT NULL

隐含的变化

列的类型：

IP：varchar(15)-->int unsigned——INET_NOTA( INET_ATON(@IP) )

MD5:VARCHAR(32)-->char(32)-->BINARY(16)

Effective MySQL之SQL语句最优化——读书笔记之二

第二章,基本的分析命令 本章简单介绍了如下几个基本的MySQL分析命令: EXPLAIN命令 生成QEP不是确定的 QEP不会绑定给一个SQL或者存储过程,而是在执行的时候根据实际情况生成 可以通过Q ...

Effective MySQL之SQL语句最优化——读书笔记之一

第一章,DBA5分钟速成 本章知识点如下: 寻找运行慢SQL的语句: show full processlist查看所有正在执行的进程及执行的语句耗时: 命令后面\G可以让命令按行显示(默认是按列). ...

《[MySQL技术内幕：SQL编程》读书笔记

读书笔记 2019年3月31日23:12:11 严禁转载!!! 这本书是我比较喜欢的一位国内作者姜承尧, ...

《Troubleshooting SQL Server》读书笔记-CPU使用率过高(下)

读书笔记-CPU使用率过高(下) 第三章 High CPU Utilization. CPU使用率过高的常见原因 查询优化器会尽量从 ...

Mysql 常用 SQL 语句集锦

Mysql 常用 SQL 语句集锦 基础篇 //查询时间,友好提示 $sql = "select date_format(create_time, '%Y-%m-%d') as day fr ...

MySQL数据库sql语句的一些简单优化

1.查询条件的先后顺序 有多个查询条件时,要把效率高能更精确筛选记录的条件放在后边.因为MySQL解析sql语句是从后往前的(不知是否准确). 例: select a.*,b.* from UsrIn ...

mysql下sql语句 update 字段=字段+字符串

mysql下sql语句 update 字段=字段+字符串   mysql下sql语句令某字段值等于原值加上一个字符串 update 表明 SET 字段= 'feifei' || 字段; (postgr ...

MySQL数据库SQL语句基本操作

一.用户管理: 创建用户: create user '用户名'@'IP地址' identified by '密码'; 删除用户: drop user '用户名'@'IP地址'; 修改用户: renam ...

随机推荐

SequoiaDB 笔记

SequoiaDB 笔记 这几天翻了翻SequoiaDB的代码,记了点笔记.不保证下面内容的正确性(肯定有错的地方) 个人观感 优点 代码还不错,设计也算简洁. EDU和CB的使用让整个系统变得简单很 ...

SQLSERVER - Mysql 调试 笔记

//性能SET STATISTICS IO on;  SET STATISTICS TIME on; //Mysql 切分字符串 CREATE  PROCEDURE proc_split(    in ...

HTML常用命名和CSS reset代码【收集总结】

CSS命名规则 头:header 内容:content/containe 尾:footer 导航:nav 侧栏:sidebar 栏目:column 页面外围控制整体布局宽度:wrapper 左右中:l ...

perl静态编译DBD

编译DBD 项目中经常使用perl,但perl在连接数据库时,需要依赖DBI,DBD驱动,但默认安装DBD驱动时,需要依赖数据库的lib库. 比如perl连接MySQL,需要安装MySQL clien ...

git的基本使用方式

git!git!git!这是一个版本控制工具,本地仓库的话就是一个离线的版本控制工具,为了解决文件回滚和多副本的问题出来的,远程仓库的云端叫github. 这是目前最先进的分布式版本控制系统,下面记录 ...

Atcoder Tenka1 Programmer Contest 2019

C 签到题,f[i][0/1]表示以i结尾最后一个为白/黑的最小值,转移显然. #include using namespace std; ; ]; char ...

JS学习笔记Day11

一.什么是事件对象(黑匣子) (一)事件: onclick ondblclick (二)当绑定对象的事件被触发时,所发生的所有详细信息都会保存在一个地方,这个地方称为事件对象 二.如何获取事件对象 ( ...

C# Winform 登录中的忘记密码及自动登录

本地保存登录账号实现忘记密码及自动登录 #region 删除本地自动登录及记住密码信息 /// /// 删除本地自动登录及记住密码信息 ///

Chrome 扩展

http://www.cnblogs.com/coco1s/p/8004510.html

Android SDK代理服务器解决国内不能更新下载问题