数据库—MySQL调优

2、MySQL优化

2.1、MySQL的架构介绍

2.1.1、下载、配置

2.1.1.1、Linux中

• 下载GA（Generally Available）版本

• 查询：rpm -ga | grep -i mysql

• 删除：rpm -e RPM软件包名

• 安装：rpm -ivh RPM软件包名.rpm

• 启动/暂停服务：service mysql start/stop

• 设置密码：/usr/bin/mysqladmin -u root password 123456

• 连接：mysql -u root -p

• 设置开机自启动：chkconfig mysql on

• 查看设置自启动服务：ntsysv，有 * 代表会开机自启动

• 查看安装目录：ps -ef | grep mysql datadir即目录

• 配置文件目录中的 my-什么.cnf 是我们需要改的配置文件

2.1.2、常用命令

2.1.2.1、Linux中

• 展示数据库：show databases;

• 创建数据库：create database 数据库名字;

• 使用数据库：use 数据库名字

• 展示数据表：show tables

• 建表：create table 表名();

• 查看字符集：show variables like 'character%' 或用这个 '%char%' ;

• 修改字符集：vim 复制到/etc目录下的cnf文件

2.1.4、MySQL主要配置文件

• MySQL为了保持高效，很多功能都是默认关闭的

2.1.4.1、配置文件

• Windows中：my.ini

• Linux中：/etc/my.cnf

2.1.4.2、二进制日志log-bin

• 主要的作用：主从复制

• 在配置文件中：log-bin=D:/MySQL目录/data/mysqlbin，路径可自设

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

2.1.4.3、错误日志log-error

• 默认是关闭的，用于记录严重的警告和错误信息、每次启动和关闭的详细相信等

• 在配置文件中：log-err=D:/MySQL目录/data/mysqlerr，路径可自设

2.1.4.4、查询日志log

• 默认是关闭的，用于查询的sql语句、如果开启会降低MySQL的整体性能，因为记录日志也是消耗系统资源的

• 配合慢查询使用的，专门用来找慢查询

2.1.4.5、数据文件

2.1.4.5.1、文件路径

• Windows中：D:/MySQL安装路径/data，目录下可挑选很多库

• Linux中：默认路径是/var/lib/mysql，最好还是先看看当前系统中全部的库后再进去：ls -lF | grep ^d，找所有以目录开头的

2.1.4.5.2、frm文件

• 存放表结构，

2.1.4.5.3、myd文件

• 存放表数据，即my data

2.1.4.5.4、myi文件

• 存放表索引，即my index

2.1.5、MySQL整体架构

• MySQL的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上

• 插件式的存储引擎架构，将查询处理，和其他的系统任务以及数据的存储提取，相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎

2.1.6、MySQL常用存储引擎

• 查看当前的存储引擎：show variables like '%storage_engine%' 或 show engines;

• 阿里、淘宝用的是Percona，是对MySQL数据库进行改进，存储引擎更换成了xtradb，比innodb在性能和并发上做得更好

• AliSql+AliRedis

2.1.6.1、MyISAM与InnoDB

#

2.2、索引优化分析

• \G：变成json数据格式，key-value形式

• 索引的范围查找，会导致索引失效。即id>3 会导致索引失效

• 创建：CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));

• 修改：ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));

• 删除：DROP INDEX [indexName] ON mytable;

• 查看：SHOW INDEX FROM table_name;

2.2.1、性能下降导致SQL语句执行慢

• 可具体分为：执行时间长、等待时间长导致SQL语句变慢可能的原因：

  • SQL语句烂

  • 索引失效

  • 关联太多join（也有可能是设计缺陷或不得已的需求）

  • 服务器调优及各个参数的设置（缓冲、线程数等）

2.2.1.1、单值索引、复合索引

• 单值索引：create index idx_user_name on user(name)

• 复合索引：create index idx_user_nameEmail on user(name,email)

2.2.2、join查询

• 普通的笛卡尔积就是 from 两个表 ，左边的每一条连接右边的每一条，条数 = 左边条数 × 右边条数

• 内连接即inner join，只要公共部分，若有null，就不会显示，

• 而左/右 连接的话，就可以显示出null

• Oracle支持full join，而MySQL不支持，但是MySQL可以使用 全左 与 全右的并集，union。则全部无公共的也用 独左无右 union 独右无左

• select * from tbl_emp a left join tbl_dept b on a.deptId=b.id union

select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;

2.2.2.1、SQL执行顺序

2.2.2.1.1、手写

• 人手写的时候是逐步从上向下，select from 然后跟一堆查询条件

2.2.2.1.2、机读

• 即机读从FROM开始

2.2.2.1.3、总结

2.2.2.2、join图

2.2.2.2.1、只要公共的join

2.2.2.2.2、要全左的join

2.2.2.2.3、要全右的join

2.2.2.2.4、要独左无右的join

2.2.2.2.5、要独右无左的join

2.2.2.2.6、要全部的join(MySQL不支持)

• MySQL不支持full join，但可以使用 全左 与 全右 的 union并集

2.2.2.2.7、要全部无公共的join

2.2.2.3、建表SQL

CREATE TABLE tbl_emp(

  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(20) DEFAULT NULL,

  deptId INT(11) DEFAULT NULL REFERENCES tbl_dept(id)

)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

2.2.3、索引简介

• 索引(index)是帮助MySQL高效获取数据的数据结构，即索引=数据结构

• 索引的目的：提高查询效率，类似于字典。

• 索引：排好序的 快速查找数据结构。

• 索引会影响到where后面的查找 和 order by后面的排序

• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（即指针）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

• 索引本身也很大，不可能全部存储在内存中，因此索引往往以 索引文件 的形式存储在磁盘上

• 如果没有特别指明，一般索引都是指B树（多路搜索树，并不一定是二叉树），还有哈希索引（hash index），其它还有聚集索引、次要索引、覆盖索引、复合索引、前缀索引、

• 通过 B树 快速找到 col2，通过数据表的col2快速找到物理地址，

• 大部分时候，例如商品购物车，删除表面上是删除，其实底层是update，是将标志位设为 非激活，并不会真正去delete掉

• 不删除：①方便分析记录 ②为了索引，因为真的删了，索引和树就会重建，乱改会导致 树残缺，所以一般需要夜晚维护索引

2.2.3.1、索引的优缺点

• 树的高度，就相当于查找次数的维度

2.2.3.1.1、优点

• 提高数据检索的效率，降低数据库的IO成本

• 降低数据排序的成本，降低CPU的消耗

2.2.3.1.2、缺点

• 索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

• 索引虽然提高了查询效率，但是同时也会降低更新表的速度。因为更新表的时候，MySQL不仅改数据，还会改索引。INSERT、UPDATE、DELETE

• 索引只是提高效率的一个因素，如果有大量数据，需要花时间研究建立最优秀的索引，或者优化查询

2.2.3.2、索引的分类

2.2.3.2.1、单值索引

• 一个索引只包含单个列，一个表可以有多个单列索引。

• 一般复合用得更多。一个表建立的索引最好不要超过5个。

2.2.3.2.2、唯一索引

• 索引列的值必须唯一，但允许有空值

2.2.3.2.3、复合索引

• 即一个索引包含多个列

2.2.3.2.4、索引的基本语法

• 创建：CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));

• 修改：ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));

• 删除：DROP INDEX [indexName] ON mytable;

• 查看：SHOW INDEX FROM table_name;

2.2.3.3、索引结构

2.2.3.3.1、BTree索引（重要）

• 检索原理：浅蓝色的是磁盘块，每个磁盘块包含深蓝色的数据项、黄色的指针。查找的次数就是树的高度。

  • 磁盘块1包含数据项17和35，小于17的根据P1指针去找，大于17小于35的根据P2去找，大于35的根据P3指针去找，以此类推

  • 真实的数据存在于叶子节点，比如3，5，9在数据表里面

  • 非叶子节点存储指引搜索方向的数据项，比如17，35不在数据表里面

• 假如要查找数据项29，首先会把磁盘块1由磁盘加载到内存，此时发生1次IO，在内存中使用二分查找确定29在17与35之间，锁定磁盘块1的P2指针，内存时间（相比于磁盘IO）非常短可忽略不计，然后再加载磁盘块3到内存，以此类推，一共3次IO。

• 三层的B+树可以表示上百万条数据，三次IO是很快的

2.2.3.3.2、Hash索引

2.2.3.3.3、full-text全文索引

2.2.3.3.4、R-Tree索引

2.2.3.4、哪些情况该建索引，哪些不建

• 总而言之：为经常查询、排序的字段建立索引

• 索引选择性越接近1，索引的效率越高。比如2000条，1980个数据不同，这种字段一定要建立索引，1980/2000约等于1

1. 主键自动建立唯一索引

2. 频繁作为查询条件的字段，应该创建索引

3. 查询中与其他表关联的字段，外键关系建立索引

4. 频繁更新的字段，不该建立索引

5. where 条件里不会用不到的字段，不该建立索引

6. 单键/组合索引的选择问题（高并发下倾向于建立复合索引）

7. 查询中排序（order by）的字段，排序字段若通过索引访问，会大大提高排序效率

8. 查询中统计或分组（group by）的字段

9. 表记录太少（3百万左右），不该建立索引

10. 经常增删改的表，不该建立索引。因为虽然可以提高查询效率，但是会降低更新效率，因为不仅要保存数据，还要保存索引文件

11. 多条记录的数据重复且分布平均的表字段，不该建立索引。因为假如性别、国籍的字段，全班的都是中国，就没必要建立索引了，没实际作用，性别也是一样，该字段只有T和F，机率约为50%，

2.2.4、性能分析

2.2.4.1、Query Optimizer（查询优化器）

• MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算，分析系统中收集到的统计信息，为客户端请求的Query提供MySQL认为最优的执行计划（检索方式），但可能不是DBA认为最优的

2.2.4.1.1、Query语句优化流程

• 客户端向MySQL请求一条Query，命令解析器模块 完成请求分类，区别出是SELECT的转发给MySQL Query Optimizer，优化器先对整条语句进行优化，处理掉一些常量表达式的预算（换算成常量值），然后对Query语句中的查询条件简化和转换（去掉无用或显而易见的条件，结构调整），然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划，如果没有Hint或Hint信息不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行相应的计算分析，然后得出最后的执行计划

2.2.4.2、常见瓶颈

• CPU：CPU饱和的时候一般发生在：数据装入内存或从磁盘上读取数据的时候

• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

• 服务器硬件的性能瓶颈：top、free、iostat、vmstat来查看系统的性能状态

2.2.4.3、Explain关键字（EXPLAIN）

2.2.4.3.1、是什么

• 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。可以分析你的 查询语句 或 表结构 的性能瓶颈

• 即可以查看执行计划

2.2.4.3.2、能干嘛（即表头字段的作用）

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可被使用

• 哪些索引实际上被使用

• 表之间的引用

• 每张表有多少行被优化器查询

2.2.4.3.3、怎么用

• explain + SQL语句

• 里面包含执行计划包含的信息，即你这个SQL语句的执行操作的顺序，即下列表头，id、type、key重要

2.2.4.3.4、id（explain第1个字段）

• 是select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。所以id其实是组，id大的先执行，id相同的上至下顺序

• 有三种情况：若为NULL，则最后执行

1. id相同：执行顺序由上至下。产生在同一级

2. id不同：如果是子查询（SUBQUERY），id的序号会递增，id值越大的越先被执行。产生在子查询

3. id相同和不同，同时存在。id值大的先执行，id值相同的上至下顺序执行，derived代表派生的，derived2就代表：这个表是id为2的表的派生表（其实就是我们说的虚表）。

2.2.4.3.5、select_type（explain第2个字段）

• 是查询的类别，主要用于区分：普通查询、联合查询、子查询 等查询

• 有6种：

1. SIMPLE：简单的select查询，查询中不包含子查询或UNION

2. PRIMARY：查询中若包含任何复杂的子查询部分，最外层则被标记为此

3. SUBQUERY：就是 from 或 where 等后面的子查询

4. DERIVED：在 from 列表中包含的子查询被标记为DERIVED（被派生的），MySQL会递归执行这些子查询，把结果放在这些派生表（其实就是临时表）中，即table是derived的，但临时表会增加系统负担

5. UNION：若第2个select出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层的SELECT将被标记为：DERIVED

6. UNION RESULT：从UNION表获取结果的select，即union结果的合并

2.2.4.3.6、table（explain第3个字段）

• 即这一行的数据是哪张表的

2.2.4.3.7、type（explain第4个字段）

• 显示查询使用了何种类型，一般来说，得保证查询至少达到range级别，最好能达到ref级别，

• 常见的最好到最差：system > const > eq_ref -> ref > range > index > ALL

• 全部的：system > const > eq_ref -> ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• 有7种：

1. system：单表单行，表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，也可以忽略不计，用不到

2. const：表示通过 索引1次 就找到了，const用于比较 primary key 或 unique 索引，因为只匹配一行数据，所以很快。

   • 例如将主键放在where列表中，MySQL就能将该查询转换为一个常量

3. eq_ref：唯一性索引扫描。对于每个索引键，表中只有一条记录与之匹配。常见于 主键索引 或 唯一索引 扫描。

   • 即这个索引对应的字段值必须唯一，通过这个索引去找，一定只能找到一条记录与他匹配，无论这个索引是单值还是复合。用=

4. ref：非唯一性索引扫描。返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的所有行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

   • 即这个索引对应的字段值不唯一，通过这个索引去找，有1-n条记录与之匹配，无论这个索引是单值还是复合。用=

5. range：只检索给定范围的记录，使用一个索引来选择行。explain的key列显示使用了哪个索引。

   • 一般就是在where语句中出现了between、<，>，in等的查询。这种查询比全表扫描好，因为它固定了开始于索引的某一点，结束于另一点，不需要全部扫描。

6. index：Full Index Scan，index于ALL的区别为：index类型只遍历索引树，这通常比ALL快，因为索引文件比数据文件小，也就是说虽然它们都是读取全表，但是index从索引读取，而all从硬盘读取

   • 即你使用的select，要读的字段是索引，但是你没有指定where

7. ALL：Full Table Scan，就是全表扫描，IO特别大

2.2.4.3.8、possible_keys（explain第5个字段）

• 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段中，若存在索引，则该索引将被列出，但不一定被查询实际使用，即这个是可能的，即 应到人数

2.2.4.3.9、key（explain第6个字段）

• 实际使用的索引。若为NULL，则没有使用索引，若查询中使用了覆盖索引，则该索引仅出现在key列表中。即这个是 实到人数

2.2.4.3.10、key_len（explain第7个字段）

• 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

• key_len显示的值是索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得到的，不是通过表内检索出来的

• 但是精确度和key_len的长度是矛盾的，一般来说key_len越大，越精确，但是使用 复合索引和单值索引 确实是有key_len的区别，但是效率却一样。

2.2.4.3.11、ref（explain第8个字段）

• 显示索引的哪一列被使用了，如果可能的话，是一个常数。

• 即哪些列或常量被用于查找索引列上的值

• 即如果是固定条件，那就是常量const

• 如果是不值比较，那就是 数据库名.数据表名.字段名

2.2.4.3.12、rows（explain第9个字段）

• 根据表统计信息及索引选用情况，大致估算出找到所需的记录，必须要读取的记录行数

• 这个肯定是越小越好。单值索引 效率不如 复合索引、覆盖索引

2.2.4.3.13、Extra（explain第10个字段）

• 包含不适合在其他列中显示但十分重要的额外信息

• 主要是前三个，前两个降低效率，第三个效率不错

• 索引能使用到的方式：查询、排序

• 所以复合索引，只要查询和排序 都一一使用到，就会提高效率

• 小心group by：使用group by必须将 复合索引 的 所有字段 按顺序 写在group by后面

• 有8种：

1. Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。即MySQL中无法利用索引完成的排序操作称为——文件排序。

   • 即已经使用复合索引中的一个进行查询了，本来想用索引排序，但是复合索引太多，使用复合索引进行排序的话，必须按顺序使用到复合索引中所有的字段，但是用户在中间把梯子截了一段，那么就无法使用复合索引进行排序了，只能在内部把数据排序一次。最好不要用到

   • 比如：explain select col1 from t1 where col1='zs' order by col3;

   • explain select col1 from t1 where col1='zs' order by col2,col3;

   • 上面两个语句，第一个使用到了文件排序，第二个没使用到。后者性能更高。所以提示我们，我们想使用复合索引又进行查询，又排序的时候，都得使用到，不管是分开在where中或order by中

2. Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by。所以小心order by和group by

3. Using index：表示相应的select中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。但是如果同时出现Using where，表明这个索引 是被用来执行 索引键值的查找；若没有出现，表面索引用来读取而非执行查找动作。

4. Using where：表明使用了where进行过滤

5. Using join buffer：使用了连接缓存，用于判断是否要调整配置文件中的 join buffer 大小

6. Impossible where：where 子句的值总是false，不能用来获取任何元组。即： XXX where name="zs" and name="ls" 不可能两个都等于

7. select tables optimized away：在没有group by子句的情况下，基于索引优化做的操作

8. distinct：优化distinct操作，在找到第一匹配的元组后就停止找同样的值的动作。

2.2.6、索引优化

2.2.6.1、索引分析

• 多使用explain查看可调优的地方，多建立索引尝试优化分析

• 索引名默认：idx表名简写多字段名

• show index from 表名;

• 新建索引：

  1. ALTER TABLE 表名 ADD INDEX 索引名(字段名1,字段名2);

  2. CREATE INDEX 索引名 ON 表名(字段名1,字段名2);

• 删除索引：DROP INDEX 索引名 ON 表名;

2.2.6.1.1、单表

• 如果复合索引导致索引失效，则将需要范围查找的索引字段去掉，即本来建立的索引是abc，但是b是范围搜索，那么就删除abc，建立索引ac

2.2.6.1.2、两表

• 两表间使用join时，要将索引建在外表上，或者说使用SQL语句时，要将建索引的表作为外表

• 即左连接建在右表上，右连接建在左表上。因为基表全都有，而外表是关键点，所以要建立在外表上。

2.2.6.1.3、三表

• 除了基表，外表都建立索引，比如A left join B left join C，那就在B、C上都建立索引

2.2.6.2、索引失效（应该避免，很重要）

• 注意：索引使用在 查找、排序，这两个阶段，这两个阶段的索引使用是分开的，但是索引等级是会覆盖的。仔细看图

• 总结：

  • 覆盖索引 是相对于 select 的概念，where中没有覆盖索引

  • order by、group by、select 都是排序，别把order by与where混淆

  • select中使用索引的个数和顺序不重要，但是 order by、group by 中的顺序很重要，因为排序不可换顺序，所以order by 中想使用索引，必须：字段顺序正确，带头大哥加上顺序小弟，where中的常量可以给order by帮忙搭梯子

  • 并且一升一降也用不上索引，比如：explain select * from tableA order by age ASC, brith DESC; 虽然age和brith有复合索引，但用不上，因为一上一下，一升一降。同上同下才能使用到索引

  • 当 排序字段 为常量时，即在 where 中给了常量值，这个时候order by 排序的时候，这个字段的顺序就不需要在意了

  • where 语句与 order by 语句 条件列组合满足最左前列可使用index

  • where使用带头大哥，type是ref，小弟有范围查找，变range。老版本使用带头大哥，extra会有Using where

  • where未使用带头大哥，type是ALL，extra有Using where

  • select是*，type是index，

  • select使用索引字段中无顺序的一个或多个，type是index，extra有Using index

  • where中有小弟范围查找，但是select中指定索引字段，会让小弟的范围查找不使用索引，type从 range 升级成 ref

  • type会被高等级覆盖，即where中type是ref，select中是index，那结果就是ref where使用带头大哥，select使用*

where使用带头大哥，select使用指定

where不使用带头大哥，select使用指定

覆盖索引

• 索引 字段范围查找，会导致语句变成range，并且此字段后的索引失效

• 复合索引使用条件：（如果全都匹配，就是最佳左前缀法则）

  1. 索引中 最左的字段必须存在，否则就使用不到此索引

  2. 索引中 最好要跳过字段。即在 带头大哥在 的情况下，确实能使用到索引，但是索引在跳过的地方就中断了，即就算你使用了后面的索引字段，MySQL也无法使用到，只能顺序使用索引字段

• 全值匹配我最爱

• 带头大哥不能死

• 中间兄弟不能断

• 范围之后全失效

• 索引不要做操作

• 用不等于全失效

• 少用*且少用NULL

• like %开头，自己及之后全失效

• like %结尾，索引无影响

• varchar必加单引号

• 少用or则少失效

2.2.6.2.1、全值匹配我最爱

• 即使用到索引中全部的列，效率最高，顺序不重要，可以打乱

2.2.6.2.2、最佳左前缀法则

• 如果索引了多个字段，必须遵守最左前缀法则。即查询从索引的最左字段开始，并且不可跳过索引中的列

  • 带头大哥不能死

  • 中间兄弟不能断

2.2.6.2.3、不在索引列上做任何操作（计算、函数、自动或手动的类型转换），会导致索引失效而转向全表扫描

• 即不要对索引的字段做任何操作

• LEFT(name,4)：从左边开始，取name的4位

• 字符串不加 '' 就会发生 自动的类型转换

2.2.6.2.4、存储引擎不能使用索引中范围条件右边的列

• 第二句语句，用到了两个索引，但是第二个变成范围条件，导致第二个后面的索引失效。

• 比如：in、like、between and、<、>

2.2.6.2.5、尽量使用覆盖索引（查询列都是索引字段），减少select *

• 选取固定列，比*好，因为Using index 是使用了 覆盖索引，更好

• 并且选取固定列后，就会不去使用范围索引，但是type变成了ref，即虽然age这个索引字段没使用到，但是使用到了覆盖索引（只使用第一个索引字段，第二个范围索引字段不去使用），且这条语句的级别更高。

2.2.6.2.6、不等于（!= 或 <>）会无法使用索引导致全表扫描

2.2.6.2.7、is null、is not null 也无法使用索引

2.2.6.2.8、like 以通配符开头，索引失效会变成全表扫描

• like %放右边能使用到索引

• 可以如此理解：%开头，无法确定方向，所以只能使当前索引字段失效，而%结尾，因为可以确定方向，所以自己及之后的索引都不会失效， 'k%kk%' 也是%结尾

2.2.6.2.9、字符串不加单引号，索引失效

• 不给字符串加 ''，虽然也能查找到，因为MySQL隐式地做了类型转换，但是类型转换会导致索引失效

2.2.6.2.10、少用 or，用它连接时 索引失效

2.2.6.3、解决 like '%字符串%' 时索引失效

• 使用覆盖索引，就不会导致 like '%字符串%' 时索引失效

• 即where用不到索引，那么就让select用到索引

• 还有就算，千万别忘记， 主键也是索引

• 单用 nama、age 也能使用到

2.2.6.4、一般性建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引

• 在选择组合（复合）索引的时候，过滤性好的字段，越靠左越好

• 在选择组合索引的时候，尽量选择能够包含当前query语句中 where 子句中更多字段的索引

• 尽可能通过分析统计信息 和 调整query写法来达到选择适合索引的目的

2.2.6.4.1、join语句优化建议

• 尽可能减少join语句中的NestedLoop的循环总次数，即永远用小结果集驱动大结果集，即用小表驱动大表

• 优先优化NestedLoop的内层循环，即优化子句

• 保证join语句中 被驱动表 上的join条件字段已经被索引。即 on 的得建

• 当无法保证 被驱动表 的join条件字段被索引，且内存资源充足 前提下，不要太吝啬 JoinBuffer的设置。即资源足的话，JoinBuffer调大点

2.2.6.4.2、注意order by 可能产生Using filesort

• 定值、范围、排序，一般order by 是给个范围

• 且order by有严格顺序，否则order by空中楼阁会产生Using filesort，

• order by 中的常量，则不会在意其顺序

2.2.6.4.3、注意group by 可能产生 Using temporary

• group by基本都需要排序，会有临时表产生 #

2.3、查询截取分析

2.3.1、慢查询分析

2.3.1.1、分析

1. 观察，至少跑一天，看看产生的慢SQL情况

2. 开启慢查询日志，设置阙值，比如超过5秒的就算慢SQL，并将它抓出来

3. explain + 慢SQL分析

4. show profile（一般人到第3步就结束了，第4步是 运维经理、DBA，进行数据库服务器的参数调优）

2.3.1.2、总结

1. 慢查询的开启并捕获

2. explain + 慢SQL分析

3. show profile 查询SQL在MySQL服务器里面的执行细节和生命周期情况

4. SQL数据库服务器的参数调优

2.3.2、查询优化

2.3.2.1、永远用小表驱动大表

• 类似嵌套循环 Nested Loop。

• 比如：A是1-5，B是1-100，那么A去匹配B，只需要5次；而B去匹配A，则需要100次。

• exists就是判断后面的语句是否有数据，后面的语句其实就是在判断，此时的A的id是否等于B的id，不是的话就让A下一条数据来匹配。

• exists(子查询)，它只会返回true或false；子句中的select可以是1，也可以是其他的，因为重要的是B.id=A.id

2.3.2.2、order by 关键字优化

2.3.2.2.1、order by子句，尽量使用index方式排序，避免使用filesort

• MySQL支持两种排序：Filesort、Index，index效率高，是扫描索引来完成排序

2.3.2.2.2、order by两种情况下会使用index排序

• order by 语句使用索引最前列

• 使用where 语句与 order by 语句 条件列组合满足最左前列

2.3.2.2.3、尽可能在索引列上完成排序操作，遵守最佳左前缀法则。因为索引已经帮我们排序了，所以最好使用索引，这样就不需要再来一次排序了

2.3.2.2.4、如果不在索引列上，那么filesort会使用两种算法

单路总体上比双路好，但是单路会取出所有字段，所以可能第一次取的大小超过了sort_buffer的容量，那么就要多次IO，那么就反而没双路快了

1. 双路排序：即两次扫描磁盘，最终得到数据。读取行指针和orderby列，对它们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。

   • 从磁盘取到排序字段，在buffer（缓冲区）进行排序，再从磁盘取其他字段。两次IO，非常费时。所以在MySQL4.1之前使用的双路排序现在变成了单路排序

2. 单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出。避免了第二次IO，并且随机IO变成顺序IO，但是会使用更多空间，因为每一行都在内存里

2.3.2.2.5、对于单路排序的优化策略

1. 增大 sort_buffer_size 参数的设置

2. 增大 max_length_for_sort_data 参数的设置

2.3.2.3、group by 关键字优化

• group by的实质是先排序，后分组，按照索引建立的最佳左前缀原则

• 当无法使用索引列的时候，group by使用的算法和order by的一样

• where 高于 having，能写在where限定的条件就别去having中限定

2.3.3、慢查询日志

• MySQL有慢查询日志，超过long_query_time值的SQL语句，就会被记录到慢查询日志中。默认是10秒

• 默认情况下，MySQL未开启慢查询日志，因为影响性能

• 显示是否开启：SHOW VARIABLES LIKE '%slow_query_log';

• 开启：set global slow_query_log=1; 但这个重启失效

• 阙值：SHOW [global] VARIABLES LIKE 'long_query_time%'; 记住：阙值是 > 而非 >=

• 设置阙值：set global long_query_time=3; 设置后重启才显示

• 语句睡眠：select sleep(5);

• 查看慢查询语句数量：show global status like '%Slow_queries%';

2.3.3.1、查看慢查询日志

cd /var/lib/mysql

cat  xxx-slow.log

2.3.3.2、配置

2.3.3.3、日志分析工具：mysqldumpslow

2.3.4、批量数据脚本

• 函数有返回值，存储过程没有返回值

• DELIMITER将指定符号作为结束标志，如DELIMITER $$、DELIMITER ;

2.3.4.1、设置参数log_bin_trust_function_creators

2.3.4.2、创建函数

• DELIMITER $$设置MySQL以后的语句使用 $$来结束，而非 ; 了。因为下面这个函数的 ; 太多了，所以使用这个

2.3.4.3、创建存储过程

2.3.4.4、使用存储过程

CALL inser_emp(100,20);

2.3.5、show profiles

• 是MySQL提供用来分析当前会话中语句执行的资源消耗情况。可用于SQL的调优测量

• 官网：http://dev.mysql/doc/refman/5.5/en/show-profile.html

• 默认情况下，处于关闭状态，并保存最近15次的运行结果

2.3.5.1、分析步骤

2.3.5.1.1、是否支持

• 查看：Show variables like 'profiling%';

2.3.5.1.2、开启功能

• set profiling=on;

2.3.5.1.3、运行SQL

2.3.5.1.4、查看结果

• 运行完SQL后查看最近的15条SQL：show profiles;

• 有Query_ID（语句序号）、Duration（持续时间）、Query（语句）

2.3.5.1.5、诊断SQL

• show profile cpu,block io for query 想看的Query_ID;

• 可查看语句的具体花费时间的地方

2.3.5.1.6、需要注意的诊断结论（四个）

• Copying to tmp table 也就是 Copying to tmp table on disk

2.3.6、全局查询日志（只允许在测试环境使用）

• 只允许在测试环境使用，永远不能在生产环境使用

2.3.6.1、配置启用

2.3.6.2、编码启用

#

2.4、MySQL的锁机制

• 锁是计算机协调多个进程或线程并发访问某一资源的机制

• 锁的分类都是对数据操作进行分类

• 使用语句进行 锁定分析

• 查看当前数据库的隔离级别：show variables like 'tx_isolation';

• MyISAM展示表状态，分析表锁定：show status like 'table%';

• InnoDB表状态，分析表锁定：show status like 'innodb_row_lock%';

2.4.1、锁的分类

2.4.1.1、从读/写分

• 读锁（共享锁）：对于同一数据，可共读。

• 写锁（排他锁）：对于同一数据，写时不可读写

2.4.1.2、从粒度分

• 表锁（偏读）、行锁（偏写）、页锁

• 关注：开销、加锁速度、死锁、粒度、并发性能

2.4.1.2.1、表锁（偏读）

• 偏向MyISAM引擎，开销小，加锁快，无死锁，但是锁定粒度大，发生锁冲突的概率最高，并发度最低

2.4.1.2.2、行锁（偏写）

• 偏向InnoDB引擎，开销大，加锁慢，会出现死锁，但是锁定粒度小，发生锁冲突概率最低，并发度最高。InnoDB还支持事务

2.4.1.2.2、页锁

• 开销、加锁时间、锁定粒度介于表锁和行锁之间，会出现死锁，并发度一般

2.4.2、表锁、行锁、页锁

2.4.2.1、自加 表锁（MyISAM支持）

• 因为要测试表锁，所以指定MyISAM，否则默认是InnoDB

• 查看所有表的加锁情况：show open tables;

• 当前会话释放锁：unlock tables;

//表级  读（写）锁

lock  table  表1的名字  read(write),表2的名字 read(write)...  [其他];

2.4.2.1.1、表级读锁

• 会话1 给表A 加 表级读锁

• 即占有 表级读锁 后，得先 释放锁 才可干别的事

1. 会话1可读表A；会话2可读表A

2. 会话1不可读表B，会报错；会话2可读表B

3. 会话1不可改表A，会报错；会话2改表A 会阻塞 直至 表级读锁被释放，即得等到没人占有读锁，才可以写

2.4.2.1.2、表级写锁

• 会话1 给表A 加 表级写锁

1. 会话1可读表A；会话2读表A 会阻塞 直至 表级写锁被释放，

2. 会话1可改表A；会话2写表A 会阻塞 直至 表级写锁被释放，

3. 会话1不可读表B；会话2可读表B

2.4.2.1.3、表级锁总结

2.4.2.2、默认 行锁（InnoDB支持）

2.4.2.2.1、事务

2.4.2.2.2、并发事务带来的问题

1. 更新丢失（Lost Update）：最后的更新覆盖了其他的更新，或更新被回滚，产生了更新丢失

2. 脏读（Dirty Reads）：事务A读取到事务B 已修改但未提交 的数据

3. 不可重复读（Non-Repeatable Reads）：事务A两次读取同一数据源，但是数据不同（被改或删除）

4. 幻读（Phantom Reads）：事务A两次读取同一数据源，第二次读到了事务B的 已提交的新增数据

2.4.2.2.3、事务隔离级别（默认是可重复读级别，即默认有自动提交）

2.4.2.2.4、取消自动提交后的 行级读写锁

• 事务1改行A，不提交；事务2读行A，2看不见更新内容，至事务1提交

• 事务1改行A，提交；事务2在执行完某语句后未commit，然后事务2读行A，事务2看不见，直到事务2commit

• 事务A改行A；事务B改行A，事务B必须阻塞，直至事务Acommit

• 事务A改行A；事务B改行B。都正常

2.4.2.3、页锁

2.4.3、索引失效导致：行锁升级为表锁

• 建立了索引，但是使用时使用不当，使得索引失效，那么就会让行锁变表锁。尤其是在非自动提交的情况下，可明显看到效果

2.4.4、间隙锁的危害

• 因为d上没有索引，所以会走全表扫描，在一行一行的扫描过程中，不仅给每行加上锁，而且给两个值之间的间隙也加了间隙锁，这样就可以确保无法插入新的数据记录。

• 间隙锁是一个在索引记录之间的间隙上的锁，当使用范围条件的时候，就会加上间隙锁，锁上给定范围内的所有行记录

• 然后当使用这个索引的时候，默认会加间隙锁。例如：全部数据1-10没有2，但是间隙锁会保护1-10全部的记录

• 间隙锁的作用 保证某个间隙内的数据在锁定情况下不会发生任何变化

• 例子：表A字段a，b都有索引，a字段1-10，缺2。事务X修改记录，where a>=1 and a<=10，未提交；事务Y插入a字段为2的记录，事务Y会被阻塞，直至事务X提交

2.4.5、锁定某一行的某一操作

2.4.5.1、锁定步骤

1. begin; 锚定起点

2. SQL语句 for 操作(比如update)

   • select * from 表 where a=8 for update;

3. commit; 提交

2.4.6、优化建议

#

2.5、主从复制

2.5.1、复制的原理

• slave会从master读取binlog来进行数据同步

2.5.2、复制的原则

1. 每个slave只能有一个master

2. 每个slave只能有唯一的服务器ID

3. 每个master可有多个slave

2.5.3、复制的问题

• 延时

2.5.4、主从常见配置

• MySQL版本一致且后台以服务运行

• 主从都配置在 [mysqld]节点下，都是小写

• 还有很多，建议专门学习 #