导图社区 MySQL高级架构索引优化知识点学习总结

MySQL高级架构索引优化知识点学习总结

MySQL高级架构索引优化知识点学习总结，包括mysql的架构介绍、索引优化分析、查询截取分析、MySql锁机制、主从复制等内容。

编辑于2022-11-04 14:22:02 广东

MySQL架构
MySQL高…
MySQL高级
索引优化

凝

MySQL高级架构索引优化知识点学习总结

社区模板帮助中心，点此进入>>

凝

相似推荐
大纲

MySQL高级架构索引优化知识点学习总结

1. mysql的架构介绍

Mysql简介

概述

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。 MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 Mysql是开源的，所以你不需要支付额外的费用。 Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。 Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。 MySQL使用标准的SQL数据语言形式。 Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。 MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

Mysql高手是怎样炼成的

数据库内部结构和原理

数据库建模优化

数据库索引建立

SQL语句优化

SQL编程

mysql服务器的安装配置

数据库的性能监控分析与系统优化

各种参数常量设定

主从复制

分布式架构搭建、垂直切割和水平切割

数据迁移

容灾备份和恢复

shell或python等脚本语言开发

对开源数据库进行二次开发

MysqlLinux版的安装

mysql5.5

下载地址

官网下载地址：http://dev.mysql.com/downloads/mysql/

拷贝&解压缩

检查工作

检查当前系统是否安装过mysql

执行安装命令前，先执行查询命令 rpm -qa|grep mysql 如果存在mysql-libs的旧版本包如下：请先执行卸载命令：rpm -e --nodeps mysql-libs

检查/tmp文件夹权限

由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限执行：chmod -R 777 /tmp

安装

在mysql的安装文件目录下执行： rpm -ivh MySQL-server-5.5.54-1.linux2.6.x86_64.rpm rpm -ivh MySQL-client-5.5.54-1.linux2.6.x86_64.rpm

查看MySQL安装版本

或者可以执行 mysqladmin --version命令，类似java -version如果打出消息，即为成功。通过vim 查看 mysql组和mysql组

mysql服务的启+停

启动：关闭：

首次登录

安装完成后会提示出如下的提示：在mysql首次登录前要给 root 账号设置密码启动服务后，执行命令 /usr/bin/mysqladmin -u root password '123123' 然后通过 mysql -uroot -p123123进行登录

MySQL的安装位置

在linux下查看安装目录 ps -ef|grep mysql

参数路径解释备注 --basedir /usr/bin 相关命令目录 mysqladmin mysqldump等命令 --datadir /var/lib/mysql/ mysql数据库文件的存放路径 --plugin-dir /usr/lib64/mysql/plugin mysql插件存放路径 --log-error /var/lib/mysql/jack.atguigu.err mysql错误日志路径 --pid-file /var/lib/mysql/jack.atguigu.pid 进程pid文件 --socket /var/lib/mysql/mysql.sock 本地连接时用的unix套接字文件 /usr/share/mysql 配置文件目录 mysql脚本及配置文件 /etc/init.d/mysql 服务启停相关脚本在linux下查看安装目录 ps -ef|grep mysql

自启动mysql服务

修改字符集问题

尝试插入输入：原因是字符集问题 1 查看字符集 show variables like 'character%'; show variables like '%char%'; 看看出现的结果：默认的是客户端和服务器都用了latin1，所以会乱码。 2 修改my.cnf 在/usr/share/mysql/ 中找到my.cnf的配置文件，拷贝其中的my-huge.cnf 到 /etc/ 并命名为my.cnf mysql 优先选中 /etc/ 下的配置文件然后修改my.cnf: [client] default-character-set=utf8 [mysqld] character_set_server=utf8 character_set_client=utf8 collation-server=utf8_general_ci [mysql] default-character-set=utf8 3、重新启动mysql 但是原库的设定不会发生变化，参数修改之对新建的数据库生效 4、已生成的库表字符集如何变更修改数据库的字符集 mysql> alter database mytest character set 'utf8'; 修改数据表的字符集 mysql> alter table user convert to character set 'utf8'; 但是原有的数据如果是用非'utf8'编码的话，数据本身不会发生改变。

Mysql配置文件

主要文件

二进制日志log-bin

主从复制及备份恢复(了解)

log-bin 中存放了所有的操作记录(写？)，可以用于恢复。相当于 Redis 中的 AOF my.cnf中的log-bin配置(默认关闭)

错误日志log-error

默认是关闭的,记录严重的警告和错误信息，每次启动和关闭的详细信息等。

慢查询日志log

可自定义“慢”的概念:0-10秒之间的一个数。慢查询日志会将超过这个查询事件的查询记录下来，方便找到需要优化的 sql 。用于优化sql语句是使用。

默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的

数据文件

两系统

windows

....\MySQLServer5.5\data目录下很多数据库文件

linux

默认路径：/var/lib/mysql

可在配置文件中更改 /usr/share/mysql/ 下的 my-huge.cnf

每个目录代表一个同名的库

Myisam存放方式

show create table mydb 查看创建 mydb 表的基本信息，其中包括了数据引擎。自带的库 mysql 库中所有的表都是以 MyIsam 引擎存的。通过 myisam 引擎存的表都是一式三份，放在库同名的文件夹下 /var/lib/mysql

frm文件(framework)

存放表结构

myd文件(data)

存放表数据

myi文件(index)

存放表索引

innodb存放方式

ibdata1

Innodb引擎将所有表的的数据都存在这里面 /usr/share/mysql/ibdata1 而frm文件存放在库同名的包下

frm文件

存放表结构

单独存放

set innodb_file_per_table=on create table mmm (id int(20) auto_increment ,name varchar(20),primary key(id)); 设在为 on 后单独以 table名.ibd 的文件名存储

如何配置

windows

my.ini文件(配置文件)

Linux

/etc/my.cnf文件(配置文件)

Mysql 的用户与权限管理

MySQL的用户管理

创建用户

创建用户 create user zhang3 identified by '123123'; 表示创建名称为zhang3的用户，密码设为123123；

了解user表

查看用户 select host,user,password,select_priv,insert_priv,drop_priv from mysql.user; select * from user\G; 将 user 中的数据以行的形式显示出来(针对列很长的表可以采用这个方法 ) host ：表示连接类型 % 表示所有远程通过 TCP方式的连接 IP 地址如 (192.168.1.2,127.0.0.1) 通过制定ip地址进行的TCP方式的连接机器名通过制定i网络中的机器名进行的TCP方式的连接 ::1 IPv6的本地ip地址等同于IPv4的 127.0.0.1 localhost 本地方式通过命令行方式的连接，比如mysql -u xxx -p 123xxx 方式的连接。 User:表示用户名同一用户通过不同方式链接的权限是不一样的。 password ：密码所有密码串通过 password(明文字符串) 生成的密文字符串。加密算法为MYSQLSHA1 ，不可逆。 mysql 5.7 的密码保存到 authentication_string 字段中不再使用password 字段。 select_priv , insert_priv等为该用户所拥有的权限。

设置密码

修改当前用户的密码: set password =password('123456') 修改某个用户的密码: update mysql.user set password=password('123456') where user='li4'; flush privileges; #所有通过user表的修改，必须用该命令才能生效。

修改用户

修改用户名： update mysql.user set user='li4' where user='wang5'; flush privileges; #所有通过user表的修改，必须用该命令才能生效。

删除用户

drop user li4 ; 不要通过delete from user u where user='li4' 进行删除，系统会有残留信息保留。

权限管理

授予权限

授权命令： grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’; 该权限如果发现没有该用户，则会直接新建一个用户。比如 grant select,insert,delete,drop on atguigudb.* to li4@localhost ; #给li4用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。 grant all privileges on *.* to joe@'%' identified by '123'; #授予通过网络方式登录的的joe用户，对所有库所有表的全部权限，密码设为123. 就算 all privileges 了所有权限，grant_priv 权限也只有 root 才能拥有。给 root 赋连接口令 grant all privileges on *.* to root@'%' ;后新建的连接没有密码，需要设置密码才能远程连接。 update user set password=password('root') where user='root' and host='%';

收回权限

授权命令： revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名@用户地址 ; REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost; #若赋的全库的表就收回全库全表的所有权限 REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost; #收回mysql库下的所有表的插删改查权限对比赋予权限的方法。必须用户重新登录后才能生效

查看权限

查看当前用户权限 show grants; 查看某用户的全局权限 select * from user ; 查看某用户的某库的权限 select * from db; 查看某用户的某个表的权限 select * from tables_priv;

通过工具远程访问

1、先 ping 一下数据库服务器的ip 地址确认网络畅通。 2、关闭数据库服务的防火墙 service iptables stop 3、确认Mysql中已经有可以通过远程登录的账户 select * from mysql.user where user='li4' and host='%'; 如果没有用户,先执行如下命令： grant all privileges on *.* to li4@'%' identified by '123123'; 4、测试连接：

Mysql的一些杂项配置

大小写问题

SHOW VARIABLES LIKE '%lower_case_table_names%' windows系统默认大小写不敏感，但是linux系统是大小写敏感的默认为0，大小写敏感。设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和DB进行查找。设置2，创建的表和DB依据语句上格式存放，凡是查找都是转换为小写进行。设置变量常采用 setlower_case_table_names = 1；的方式，但此变量是只读权限，所以需要在配置文件中改。当想设置为大小写不敏感时，要在my.cnf这个配置文件 [mysqld] 中加入 lower_case_table_names = 1 ，然后重启服务器。但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则更改后将找不到数据库名。在进行数据库参数设置之前，需要掌握这个参数带来的影响，切不可盲目设置。

(生产环境)sql_mode

MySQL的sql_mode合理设置 sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。使用 set sql_mode=ONLY_FULL_GROUP_BY; 的方式设置会将之前的设置覆盖掉同时设置多个限制：set sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO'; sql_mode常用值如下： ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中 NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。 STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制 NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零 NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。 ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户 NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常 PIPES_AS_CONCAT：将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似 ANSI_QUOTES：启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符 ORACLE：设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

Mysql逻辑架构介绍

总体概览

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。 1.连接层最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。 2.服务层 2.1Management Serveices & Utilities：系统管理和控制工具 2.2 SQL Interface: SQL接口接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface 2.3 Parser: 解析器 SQL命令传递到解析器的时候会被解析器验证和解析。 2.4 Optimizer: 查询优化器。 SQL语句在查询之前会使用查询优化器对查询进行优化。用一个例子就可以理解： select uid,name from user where gender= 1; 优化器来决定先投影还是先过滤。 2.5 Cache和Buffer：查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等缓存是负责读，缓冲负责写。 3.引擎层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB 4.存储层数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

查询说明

查询流程图：首先，mysql的查询流程大致是： mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中(一模一样的sql才能命中)，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。然后，mysql默认使用的BTREE索引，并且一个大致方向是:无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

Mysql存储引擎

查看命令

1 如何用命令查看 #看你的mysql现在已提供什么存储引擎: mysql> show engines; #看你的mysql当前默认的存储引擎: mysql> show variables like '%storage_engine%';

各个引擎简介

1、InnoDB存储引擎 InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况 2、MyISAM存储引擎 MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。 3、Archive引擎 Archive存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。 Archive表适合日志和数据采集类应用。适合低访问量大数据等情况。根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。 4、Blackhole引擎 Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。 5、CSV引擎 CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。 CSV引擎可以作为一种数据交换的机制，非常有用。 CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。 6、Memory引擎如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快，如redis) 7、Federated引擎 Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

MyISAM和InnoDB

对比项 MyISAM InnoDB 主外键不支持支持事务不支持支持行表锁表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作缓存只缓存索引，不缓存真实数据不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响表空间小大关注点性能事务默认安装 Y Y 用户表默认使用 N Y 自带系统表使用 Y N innodb 索引使用 B+TREE myisam 索引使用 b-tree innodb 主键为聚簇索引，基于聚簇索引的增删改查效率非常高。

阿里巴巴、淘宝用哪个

Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好, 阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。 AliSql+AliRedis

2. 索引优化分析

性能下降SQL慢执行时间长等待时间长

查询数据过多

能不能拆，条件过滤尽量少

关联了太多的表，太多join

join 原理。用 A 表的每一条数据扫描 B表的所有数据。所以尽量先过滤。

没有利用到索引

索引针对列建索引。但并不可能每一列都建索引索引并非越多越好。当数据更新了，索引会进行调整。也会很消耗性能。且 mysql 并不会把所有索引都用上，只会根据其算法挑一个索引用。所以建的准很重要。

单值

复合

条件多时，可以建共同索引(混合索引)。混合索引一般会偶先使用。有些情况下，就算有索引具体执行时也不会被使用。

服务器调优及各个参数设置（缓冲、线程数等）(不重要DBA的工作)

常见通用的Join查询

SQL执行顺序

手写

机读

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：

总结

Join图

共有与独有(理解)

什么叫共有，什么叫独有？共有：满足 a.deptid = b.id 的叫共有 A独有: A 表中所有不满足 a.deptid = b.id 连接关系的数据同时参考 join 图

建表SQL

CREATE TABLE `t_dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `t_emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_dept_id` (`deptId`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO t_dept(deptName,address) VALUES('华山','华山'); INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳'); INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山'); INSERT INTO t_dept(deptName,address) VALUES('武当','武当山'); INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶'); INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺'); INSERT INTO t_emp(NAME,age,deptId) VALUES('风清扬',90,1); INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1); INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1); INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2); INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2); INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3); INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3); INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4); INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5); INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);

7种JOIN

1 A、B两表共有 select * from t_emp a inner join t_dept b on a.deptId = b.id; 2 A、B两表共有+A的独有 select * from t_emp a left join t_dept b on a.deptId = b.id; 3 A、B两表共有+B的独有 select * from t_emp a right join t_dept b on a.deptId = b.id; 4 A的独有 select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; 5 B的独有 select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null; 6 AB全有 #MySQL Full Join的实现因为MySQL不支持FULL JOIN,下面是替代方法 #left join + union(可去除重复数据)+ right join SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id UNION SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id 这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。 7 A的独有+B的独有 * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL UNION SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

增加掌门字段

ALTER TABLE `t_dept` add CEO INT(11) ; update t_dept set CEO=2 where id=1; update t_dept set CEO=4 where id=2; update t_dept set CEO=6 where id=3; update t_dept set CEO=8 where id=4; update t_dept set CEO=9 where id=5; 求各个门派对应的掌门人: select * from t_dept as b left join t_emp as a on b.CEO=a.id; 求所有当上掌门人的平均年龄: select avg(a.age) from t_emp a inner join t_dept b on a.id=b.CEO ;

join 的理解例题

两者区别：思想上的区别：子查询理解：①先知道需要查询并将数据拿出来(若from 后的表也是一个子查询结果)。②在去寻找满足判断条件的数据(where,on,having 后的参数等)。而这些查询条件通常是通过子查询获得的。子查询是一种根据结果找条件的倒推的顺序。比较好理解与判断例题中：“人物”在t_emp 表中，所以第一个from 是t_emp 表。(也可以直接将子查询方法 from 后面(因为本题中的子查询中也有select 的数据),所以任然需要上述的推导过程) join理解：执行完第一步后的结果为一张新表。在将新表与 t_emp 进行下一步的 left join 关联。先推出如何获得条件，再像算数题一样一步一步往下 join。可以交换顺序，但只能是因为条件间不相互关联时才能交换顺序。 join 比子查询难一点 join 能用到索引，但是子查询出来的表会使索引失效。 ***求所有人物对应的掌门: t_dept 表 id deptName address CEO 1 华山派华山 2 ... t_emp 表 id name age deptId 1 风清扬 90 1 ... 1.使用子查询(不推荐，影响后续用索引) 步骤：a.创建子查询查询出每个门派对应的ceo b. 根据t_emp 对应的 deptId 关联子查询表查询出所有人物对应的 ceo SELECT a.name,f.deptName,f.name FROM t_emp a LEFT JOIN (SELECT d.`id`,e.`name` ,d.`deptName` FROM t_dept d LEFT JOIN t_emp e ON d.`CEO`=e.`id`) f ON a.deptId = f.id 2.使用join(推荐) 步骤:a. 关联出每个人物对应的门派 b.通过门派的 ceo 关联对应的掌门 SELECT e.`name`, d.`deptName`,f.`name` ceo FROM t_dept d RIGHT JOIN t_emp e ON d.`id` = e.`deptId` ##第一步 --->得到关联了部门的一张新的联合表 LEFT JOIN t_emp f ON d.`CEO`=f.`id` ##第二步 --->通过新的联合表中的数据与另一张表关联 SELECT d.`deptName`, e.`name` CEO,d.`id`,f.name FROM t_dept d LEFT JOIN t_emp e //上述两个 join 交换了顺序并不影响执行。前提是两个 join 间不是依赖关系。且都跟 ON d.`CEO`=e.`id` LEFT JOIN t_emp f ON f.deptId = d.`id`

索引简介

是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

你可以简单理解为“排好序的快速查找数据结构”。

详解(重要)

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。二叉树弊端之一：二叉树很可能会发生两边不平衡的情况。 B-TREE: (B:balance) 会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定。(myisam使用的方式) B-TREE弊端：(插入/修改操作多时，B-TREE会不断调整平衡，消耗性能)从侧面说明了索引不是越多越好。 B+TREE:Innodb 所使用的索引

结论

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

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

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

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

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

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

mysql索引结构

BTree索引

原理图

【初始化介绍】一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3， P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。【查找过程】如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

Myisam普通索引

关于时间复杂度

同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。 1 N logN 分别表示数据与查询次数之间的关系。常数 1*c 表示查询最快的方式。查询次数不随数据的增加而增加变量 N 表示查询次数随数据数量的增加而增加对数 logN 表示查询次数与数据数量成对数关系。介于常数与 N 之间。 n*logN 表示使用的复合方法。

B+Tree索引

原理图

B+TREE 第二级的数据并不能直接取出来，只作索引使用。在内存有限的情况下，查询效率高于 B-TREE B-TREE 第二级可以直接取出来，树形结构比较重，在内存无限大的时候有优势。

innodb的普通索引

B树和B+树的区别

B+Tree与B-Tree 的区别：结论在内存有限的情况下，B+TREE 永远比 B-TREE好。无限内存则后者方便 1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。(一次查询可能进行两次i/o操作) 2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？ 1) B+树的磁盘读写代价更低 B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。 2) B+树的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值进错的存储在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。聚簇索引的好处：按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。聚簇索引的限制：对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。（参考聚簇索引的好处。）这里说明了主键索引为何采用自增的方式：1、业务需求，有序。2、能使用到聚簇索引

full-text全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。 CREATE TABLE `article` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `content` text, PRIMARY KEY (`id`), FULLTEXT KEY `title` (`title`,`content`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 不同于like方式的的查询： SELECT * FROM article WHERE content LIKE ‘%查询字符串%’; 全文索引用match+against方式查询： SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’); 明显的提高查询效率。限制： mysql5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。 5.7以后官方支持中文分词。随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr,elasticSearch等专门的搜索引擎所替代。

Hash索引

Hash索引只有Memory, NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。 NoSql采用此中索引结构。

R-Tree索引

R-Tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。相对于b-tree，r-tree的优势在于范围查找。

mysql索引分类

主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

语法

随表一起建索引： CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id) ); unsigned (无符号的) 使用 AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。 CREATE TABLE customer2 (id INT(10) UNSIGNED ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id) ); 单独建主键索引： ALTER TABLE customer add PRIMARY KEY customer(customer_no); 删除建主键索引： ALTER TABLE customer drop PRIMARY KEY ; 修改建主键索引：必须先删除掉(drop)原索引，再新建(add)索引

单值索引

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

索引建立成哪种索引类型？根据数据引擎类型自动选择的索引类型除开 innodb 引擎主键默认为聚簇索引外。 innodb 的索引都采用的 B+TREE myisam 则都采用的 B-TREE索引

语法

随表一起建索引： CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name) ); 随表一起建立的索引索引名同列名(customer_name) 单独建单值索引： CREATE INDEX idx_customer_name ON customer(customer_name); 删除索引： DROP INDEX idx_customer_name ;

唯一索引

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

语法

随表一起建索引： CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_no) ); 建立唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。单独建唯一索引： CREATE UNIQUEINDEX idx_customer_no ON customer(customer_no); 删除索引： DROP INDEX idx_customer_no on customer ;

复合索引

不理解

复合索引与单值索引有什么区别？复合索引：create index idx_no_name on emp(no,name); // no 与 name 有同一个索引 idx_no_name 单值索引：create index idx_no on emp(no); create index idx_name on emp(name); 疑惑：同一数据引擎，都是采用一样的索引类型(B-TREE或B+TREE),复合索引与单值索引的区别是什么？该怎么使用？

即一个索引包含多个列

在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引) 当表的行数远大于索引列的数目时可以使用复合索引

语法

随表一起建索引： CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_name), KEY (customer_no,customer_name) ); 单独建索引： CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 删除索引： DROP INDEX idx_no_name on customer ;

基本语法

创建

ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))

删除

DROP INDEX [indexName] ON mytable;

查看

SHOW INDEX FROM table_name\G

table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment customer 0 primary 0 id A 0 (NULL) (NULL) BTREE customer 1 custm_no 1 custm_no A 0 (NULL) (NULL) YES BTREE non_unique: 是否是唯一索引 1：是 0：不是 seq_in_index:列在索引中的序列。针对符合索引(一个索引对应多个列)。针对同一个复合索引按照创建复合索引时的顺序进行排序 collation: cardinality: sub_part: packed: Null:是否允许 null 值 comment: index_comment:

使用ALTER命令

有四种方式来添加数据表的索引： ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。 ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。 ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

哪些情况需要创建索引

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

2. 频繁作为查询条件的字段应该创建索引(where 后面的语句)

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

A 表关联 B 表：A join B 。 on 后面的连接条件既 A 表查询 B 表的条件。所以 B 表被关联的字段建立索引能大大提高查询效率因为在 join 中，join 左边的表会用每一个字段去遍历 B 表的所有的关联数据，相当于一个查询操作

4. 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)

5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

group by 和 order by 后面的字段有索引大大提高效率

6. 查询中统计或者分组字段

哪些情况不要创建索引

1. 表记录太少

2. 经常增删改的表

Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

3. Where条件里用不到的字段不创建索引

索引建多了影响增删改的效率

4. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

性能分析

MySQL常见瓶颈

CPU

SQL中对大量数据进行比较、关联、排序、分组

最大的压力在于比较

IO：

实例内存满足不了缓存数据或排序等需要，导致产生大量物理 IO。

查询执行效率低，扫描过多数据行。

锁

不适宜的锁的设置，导致线程阻塞，性能下降。

死锁，线程之间交叉调用资源，导致死锁，程序卡住。

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

Explain

是什么(查看执行计划)

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

官网介绍

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

能干嘛

表的读取顺序

哪些索引可以使用

数据读取操作的操作类型

哪些索引被实际使用

表之间的引用

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

怎么玩

Explain + SQL语句

执行计划包含的信息

建表脚本

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id)); CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id)); CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id)); CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id)); INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000))); INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000))); INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000))); INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

各字段解释

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况

id相同，执行顺序由上至下

id相同，执行顺序由上至下此例中先执行where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id 。而 t2.id 的结果建立在 t2.id=t3.id 的基础之上。

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，因为衍生的表没有建立索引）

select_type

有哪些

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

1. SIMPLE

简单的 select 查询,查询中不包含子查询或者UNION

2. PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

3. DERIVED

在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。

DERIVED 既查询通过子查询查出来的临时表

4. SUBQUERY

在SELECT或WHERE列表中包含了子查询

5. DEPENDENT SUBQUERY

在SELECT或WHERE列表中包含了子查询,子查询基于外层

dependent subquery 与 subquery 的区别依赖子查询：子查询结果为多值子查询：查询结果为单值

6. UNCACHEABLE SUBQUREY

无法被缓存的子查询

图1 中的 @@ 表示查的环境参数。没办法缓存

7. UNION

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

UNION RESULT 两个语句执行完后的结果

8. UNION RESULT

从UNION表获取结果的SELECT

table

显示这一行的数据是关于哪张表的

type

访问类型排列

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL system>const>eq_ref>ref>range>index>ALL 一般来说，得保证查询至少达到range级别，最好能达到ref。

显示查询使用了何种类型，从最好到最差依次是： system>const>eq_ref>ref>range>index>ALL

system

表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

const

表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref

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

ref

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

range

只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index

Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

all

Full Table Scan，将遍历全表以找到匹配的行

index_merge

在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中

ref_or_null

对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

index_subquery

利用索引来关联子查询，不再全表扫描。

unique_subquery

该联接类型类似于index_subquery。子查询中的唯一索引

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

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

key

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引和查询的select字段重叠

这句话生涩难懂啊，什么鸡巴鬼对比下图两个 sql 语句。和 key 的值：当查询具体某一字段时，且那个字段有索引时，key 值会显示为索引。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

EXPLAIN SELECT * FROM emp WHERE emp.deptno=109 AND emp.`ename`='AvDEjl' 如何计算总结一下：char(30) utf8 --> key_len = 30*3 +1 表示 utf8 格式需要 *3 (跟数据类型有关) 允许为 NULL +1 ，不允许 +0 动态类型 +2 (动态类型包括 : varchar , detail text() 截取字符窜) 第一组：key_len=deptno(int)+null + ename(varchar(20)*3+动态 =4+1+20*3+2= 67 第二组：key_len=deptno(int)+null=4+1=5

key_len字段能够帮你检查是否充分的利用上了索引

RESET QUERY CACHE ; EXPLAIN SELECT emp.deptno,COUNT(*) c FROM emp WHERE emp.ename LIKE 'a%' AND emp.deptno=109 GROUP BY emp.deptno HAVING c >2 ORDER BY c DESC 同样的使用了索引但是索引的涉及的字段却不同。下图可知，充分的利用了索引的查询效率会更高。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows

rows列显示MySQL认为它执行查询时必须检查的行数。

越少越好

Extra

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

1. Using filesort

出现filesort的情况：优化后，不再出现filesort的情况：(给 ename 加上了索引) 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度分情况：当通过前面的查询语句筛选大部分条件后，只剩下很少的数据。using filesort 性能影响不大。需要综合考虑

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

2. Using temporary

优化前存在 using temporary 和 using filesort 你他妈怎么优化的? 建立索引？在 group by 的情况下发生的。 create index idx_deptno_ename on emp(deptno,ename) 后解决优化前存在的 using temporary 和 using filesort 不在，性能发生明显变化：

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

3. USING index

sing where，如果ing where，表

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找; 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

覆盖索引(Covering Index)

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。①一个索引 ②包含了(或覆盖了)[select子句]与查询条件[Where子句]中 ③所有需要的字段就叫做覆盖索引。上句红字理解： select id , name from t_xxx where age=18; 有一个组合索引 idx_id_name_age_xxx 包含了(覆盖了)，id,name,age三个字段。查询时直接将建立了索引的列读取出来了，而不需要去查找所在行的其他数据。所以很高效。 (个人认为：在数据量较大，固定字段查询情况多时可以使用这种方法。) 注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

4. Using where

表明使用了where过滤

5. using join buffer

出现在当两个连接时驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下。给驱动表建立索引可解决此问题。且 type 将改变成 ref

使用了连接缓存：

6. impossible where

where子句的值总是false，不能用来获取任何元组

7. select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在innodb中：在Myisam中： myisam 中会维护总行数 (还有其他参数)这个参数，所以在执行查询时不会进行全表扫描。而是直接读取这个数。但会对增删产生一定的影响。根据业务情况决定谁好谁坏 innodb 中没有这个机制。

查询优化

使用索引

建表SQL

CREATE TABLE staffs ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (24) NULL DEFAULT '' COMMENT '姓名', age INT NOT NULL DEFAULT 0 COMMENT '年龄', pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位', add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间' ) CHARSET utf8 COMMENT '员工记录表' ; INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW()); INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW()); INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW()); INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW()); SELECT * FROM staffs; ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

案例(索引失效)

索引失效针对的组合索引？若一个字段上有多种索引呢？某一索引失效，可以继续使用其他索引不影响。

1. 全值匹配我最爱

索引 idx_staffs_nameAgePos 建立索引时以 name ， age ，pos 的顺序建立的。全值匹配表示按顺序匹配的 EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'; EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25; EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

2. 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

and 忽略左右关系。既即使没有没有按顺序由于优化器的存在，会自动优化。经过试验结论建立了 idx_nameAge 索引 id 为主键 1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。既 select 后的字段有索引，where 后的字段也有索引，则无关执行顺序。 2.除开上述条件才满足最左前缀法则。 EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev'; EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

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

EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

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

范围若有索引则能使用到索引，范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)

5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

6. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

索引 idx_nameAgeJob idx_name 使用 != 和的字段索引失效( != 针对数值类型。针对字符类型前提 where and 后的字段在混合索引中的位置比比当前字段靠后 where age != 10 and name='xxx' ,这种情况下，mysql自动优化，将 name='xxx' 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)

7. is not null 也无法使用索引,但是is null是可以使用索引的

8. like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

like ‘%abc%’ type 类型会变成 all like ‘abc%’ type 类型为 range ，算是范围，可以使用索引

问题：解决like '%字符串%'时索引不被使用的方法？？

CREATE TABLE `tbl_user` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR(20) DEFAULT NULL, `age` INT(11) DEFAULT NULL, email VARCHAR(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; #drop table tbl_user INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com'); INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com'); INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com'); INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com'); INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com'); #before index EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'; EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%'; EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%'; EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%'; EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%'; EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'; EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'; EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'; EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%'; #create index CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age); #DROP INDEX idx_user_nameAge ON tbl_user #after index EXPLAIN SELECT * FROM tbl_user WHERE NAME =800 AND age = 33;

9. 字符串不加单引号索引失效

底层进行转换使索引失效，使用了函数造成索引失效

10. 少用or,用它来连接时会索引失效

11. 小总结

假设index(a,b,c) Where语句索引是否被使用 where a = 3 Y,使用到a where a = 3 and b = 5 Y,使用到a，b where a = 3 and b = 5 and c = 4 Y,使用到a,b,c where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N where a = 3 and c = 5 使用到a，但是c不可以，b中间断了 where a = 3 and b > 4 and c = 5 使用到a和b， c不能用在范围之后，b后断了 where a = 3 and b like 'kk%' and c = 4 Y,使用到a,b,c where a = 3 and b like '%kk' and c = 4 Y,只用到a where a = 3 and b like '%kk%' and c = 4 Y,只用到a where a = 3 and b like 'k%kk%' and c = 4 Y,使用到a,b,c

热身case

题目SQL

【建表语句】 create table test03( id int primary key not null auto_increment, c1 char(10), c2 char(10), c3 char(10), c4 char(10), c5 char(10) ); insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5'); insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5'); insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5'); insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5'); insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5'); select * from test03; 【建索引】 create index idx_test03_c1234 on test03(c1,c2,c3,c4); show index from test03; 问题：我们创建了复合索引idx_test03_c1234 ,根据以下SQL分析下索引使用情况？ explain select * from test03 where c1='a1'; explain select * from test03 where c1='a1' and c2='a2'; explain select * from test03 where c1='a1' and c2='a2' and c3='a3'; explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4'; 1） explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4'; 2） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3'; 3） explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4'; 4） explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3'; 5） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3; c3作用在排序而不是查找 6） explain select * from test03 where c1='a1' and c2='a2' order by c3; 7） explain select * from test03 where c1='a1' and c2='a2' order by c4; 出现了filesort 8） 8.1 explain select * from test03 where c1='a1' and c5='a5' order by c2,c3; 只用c1一个字段索引，但是c2、c3用于排序,无filesort 8.2 explain select * from test03 where c1='a1' and c5='a5' order by c3,c2; 出现了filesort，我们建的索引是1234，它没有按照顺序来，3 2 颠倒了 9） explain select * from test03 where c1='a1' and c2='a2' order by c2,c3; 10） explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3; 用c1、c2两个字段索引，但是c2、c3用于排序,无filesort explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2; 本例有常量c2的情况，和8.2对比 explain select * from test03 where c1='a1' and c5='a5' order by c3,c2; filesort 11） explain select * from test03 where c1='a1' and c4='a4' group by c2,c3; 12） explain select * from test03 where c1='a1' and c4='a4' group by c3,c2; Using where; Using temporary; Using filesort

一般性建议

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

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)

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

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

单表查询优化

建表SQL

CREATE TABLE IF NOT EXISTS `article` ( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `author_id` INT(10) UNSIGNED NOT NULL, `category_id` INT(10) UNSIGNED NOT NULL, `views` INT(10) UNSIGNED NOT NULL, `comments` INT(10) UNSIGNED NOT NULL, `title` VARBINARY(255) NOT NULL, `content` TEXT NOT NULL ); INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (1, 1, 1, 1, '1', '1'), (2, 2, 2, 2, '2', '2'), (1, 1, 3, 3, '3', '3'); SELECT * FROM article;

案例

#查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。 EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1; #结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。 #开始优化： # 1.1 新建索引+删除索引 #ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` ); create index idx_article_ccv on article(category_id,comments,views); DROP INDEX idx_article_ccv ON article # 1.2 第2次EXPLAIN EXPLAIN SELECT id,author_id FROM `article` WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1; #结论： #type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。 #但是我们已经建立了索引,为啥没用呢? #这是因为按照 BTree 索引的工作原理, # 先排序 category_id, # 如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。 #当 comments 字段在联合索引里处于中间位置时, #因comments > 1 条件是一个范围值(所谓 range), #MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。 # 1.3 删除第一次建立的索引 DROP INDEX idx_article_ccv ON article; # 1.4 第2次新建索引 #ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ; create index idx_article_cv on article(category_id,views); # 1.5 第3次EXPLAIN EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1; #结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。 DROP INDEX idx_article_cv ON article;

关联查询优化

建表SQL

CREATE TABLE IF NOT EXISTS `class` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE IF NOT EXISTS `book` ( `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`bookid`) ); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

案例

# 下面开始explain分析 EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; #结论：type 有All # 添加索引优化 ALTER TABLE `book` ADD INDEX Y ( `card`); # 第2次explain EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; #可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。 #这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有, #所以右边是我们的关键点,一定需要建立索引。 # 删除旧索引 + 新建 + 第3次explain DROP INDEX Y ON book; ALTER TABLE class ADD INDEX X (card); EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

建议

1、保证被驱动表的join字段已经被索引

被驱动表 join 后的表为被驱动表 (需要被查询)

2、left join 时，选择小表作为驱动表，大表作为被驱动表。

但是 left join 时一定是左边是驱动表，右边是被驱动表

3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

mysql 自动选择。小表作为驱动表。因为驱动表无论如何都会被全表扫描？。所以扫描次数越少越好

4、子查询尽量不要放在被驱动表，有可能使用不到索引。

select a.name ,bc.name from t_emp a left join (select b.id , c.name from t_dept b inner join t_emp c on b.ceo = c.id)bc on bc.id = a.deptid. 上段查询中用到了子查询，必然 bc 表没有索引。肯定会进行全表扫描上段查询可以直接使用两个 left join 优化 select a.name , c.name from t_emp a left outer join t_dept b on a.deptid = b.id left outer join t_emp c on b.ceo=c.id 所有条件都可以使用到索引若必须用到子查询，可将子查询设置为驱动表，，因为驱动表的type 肯定是 all，而子查询返回的结果表没有索引，必定也是all

子查询优化

用in 还是 exists

实验

有索引大表驱动小表 select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept); select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno); ##用 exists 是否存在，存在返回一条记录，exists 是作为一个查询判断用，所以 select 后返回什么不重要。 select sql_no_cache sum(sal) from emp inner join dept on emp.deptno=dept.deptno; 有索引小表驱动大表 select sql_no_cache sum(e.sal) from (select * from emp where id select sql_no_cache sum(e.sal) from (select * from emp where id select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept); 有索引小驱动大表性能优于大表驱动小表无索引小表驱动大表 select sql_no_cache sum(e.sal) from (select * from emp where id select sql_no_cache sum(e.sal) from (select * from emp where id select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept); 无索引大表驱动小表 select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept); select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno); select sql_no_cache sum(sal) from emp inner join dept on emp.deptno=dept.deptno;

结论

有索引的情况下用 inner join 是最好的其次是 in ，exists最糟糕无索引的情况下用小表驱动大表因为join 方式需要distinct ，没有索引distinct消耗性能较大所以 exists性能最佳 in其次 join性能最差？无索引的情况下大表驱动小表 in 和 exists 的性能应该是接近的都比较糟糕 exists稍微好一点超不过5% 但是inner join 优于使用了 join buffer 所以快很多如果left join 则最慢

order by关键字优化

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

建表SQL

CREATE TABLE tblA( id int primary key not null auto_increment, age INT, birth TIMESTAMP NOT NULL, name varchar(200) ); INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc'); INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd'); INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def'); CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name); SELECT * FROM tblA;

Case

MySQL支持二种方式的排序，FileSort和Index，Index效率高. 它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序:

ORDER BY 语句使用索引最左前列

使用Where子句与Order BY子句条件列组合满足索引最左前列

where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

小总结

第二种中，where a = const and b > const order by b , c 不会出现 using filesort b , c 两个衔接上了但是：where a = const and b > const order by c 将会出现 using filesort 。因为 b 用了范围索引，断了。而上一个 order by 后的b 用到了索引，所以能衔接上 c

如果不在索引列上，filesort有两种算法： mysql就要启动双路排序和单路排序

双路排序

MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

多路排序需要借助磁盘来进行排序。所以取数据，排好了取数据。两次 io操作。比较慢单路排序，将排好的数据存在内存中，省去了一次 io 操作，所以比较快，但是需要内存空间足够。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序

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

结论及引申出的问题

由于单路是后出的，总体而言好过双路

但是用单路有问题

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

增大sort_buffer_size参数的设置

用于单路排序的内存大小

增大max_length_for_sort_data参数的设置

单次排序字段大小。(单次排序请求)

去掉select 后面不需要的字段

select 后的多了，排序的时候也会带着一起，很占内存，所以去掉没有用的

Why

提高Order By的速度 1. Order by时select * 是一个大忌只Query需要的字段，这点非常重要。在这里的影响是： 1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。 1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。 2. 尝试提高 sort_buffer_size 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 3. 尝试提高 max_length_for_sort_data 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.

分页查询的优化---limit

EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY deptno LIMIT 10000,40 那我们就给deptno这个字段加上索引吧。然并卵。优化：先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联：(查询的数据量小了后) EXPLAIN SELECT SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id 最后比较一下查询速度：优化前：优化后：实践证明： ①、order by 后的字段（XXX）有索引 ②、sql 中有 limit 时，当 select id 或 XXX字段索引包含字段时，显示 using index 当 select 后的字段含有 bouder by 字段索引不包含的字段时，将显示 using filesort

GROUP BY关键字优化

group by实质是先排序后进行分组，遵照索引建的最佳左前缀

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

where高于having，能写在where限定的条件就不要去having限定了。

去重优化

尽量不要使用 distinct 关键字去重：优化

t_mall_sku 表 id shp_id kcdz ------ ------ -------------------- 3 1 北京市昌平区 4 1 北京市昌平区 5 5 北京市昌平区 6 3 重庆 8 8 天津例子：select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 将产生重复数据， select distinct kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 使用 distinct 关键字去重消耗性能优化： select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) group by kcdz 能够利用到索引

3. 查询截取分析

慢查询日志

是什么

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

怎么玩

说明

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

查看是否开启及如何开启

默认

SHOW VARIABLES LIKE '%slow_query_log%';

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启 SHOW VARIABLES LIKE '%slow_query_log%';

开启

set global slow_query_log=1;

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。全局变量设置，对当前连接不影响对当前连接立刻生效如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）修改my.cnf文件，[mysqld]下增加或修改参数 slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件 slow_query_log =1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log 关于慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOW VARIABLES LIKE 'long_query_time%'; 可以使用命令修改，也可以在my.cnf参数里面修改。假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

Case

查看当前多少秒算慢

SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阙值时间

使用命令 set global long_query_time=1 修改为阙值到1秒钟的就是慢sql 修改后发现long_query_time并没有改变。

为什么设置后看不出变化？

需要重新连接或新开一个会话才能看到修改值。 SHOW VARIABLES LIKE 'long_query_time%';

或者通过set session long_query_time=1来改变当前session变量;

记录慢SQL并后续分析

实验一条慢sql 跟踪日志信息

查询当前系统中有多少条慢查询记录

show global status like '%Slow_queries%';

配置版

【mysqld】下配置： slow_query_log=1; slow_query_log_file=/var/lib/mysql/atguigu-slow.log long_query_time=3; log_output=FILE

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

s: 是表示按照何种方式排序；

c: 访问次数

l: 锁定时间

r: 返回记录

t: 查询行数

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

t:即为返回前面多少条的数据；

g:后边搭配一个正则匹配模式，大小写不敏感的；

工作常用参考

得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log 得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

批量数据脚本

往表里插入1000W数据

1. 建表

# 新建库 create database bigData; use bigData; #1 建表dept CREATE TABLE dept( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT "", loc VARCHAR(13) NOT NULL DEFAULT "" ) ENGINE=INNODB DEFAULT CHARSET=UTF8 ; #2 建表emp CREATE TABLE emp ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/ job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/ mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/ hiredate DATE NOT NULL,/*入职时间*/ sal DECIMAL(7,2) NOT NULL,/*薪水*/ comm DECIMAL(7,2) NOT NULL,/*红利*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/ )ENGINE=INNODB DEFAULT CHARSET=UTF8 ;

2. 设置参数log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETERMINISTIC...... # 由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。 show variables like 'log_bin_trust_function_creators'; set global log_bin_trust_function_creators=1; # 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法： windows下my.ini[mysqld]加上log_bin_trust_function_creators=1 linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

3. 创建函数,保证每条数据都不同

随机产生字符串

DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN ##方法开始 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; ##声明一个字符窜长度为 100 的变量 chars_str ,默认值 DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; ##循环开始 WHILE i SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); ##concat 连接函数，substring(a,index,length) 从index处开始截取 SET i = i + 1; END WHILE; RETURN return_str; END $$ #假如要删除 #drop function rand_string;

随机产生部门编号

#用于随机产生部门编号 DELIMITER $$ CREATE FUNCTION rand_num( ) RETURNS INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(100+RAND()*10); RETURN i; END $$ #假如要删除 #drop function rand_num;

4. 创建存储过程

创建往emp表中插入数据的存储过程

DELIMITER $$ CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; #set autocommit =0 把autocommit设置成0 ；提高执行效率 SET autocommit = 0; REPEAT ##重复 SET i = i + 1; INSERT INTO emp10000 (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num()); UNTIL i = max_num ##直到上面也是一个循环 END REPEAT; ##满足条件后结束循环 COMMIT; ##执行完成后一起提交 END $$ #删除 # DELIMITER ; # drop PROCEDURE insert_emp;

创建往dept表中插入数据的存储过程

#执行存储过程，往dept表添加随机数据 DELIMITER $$ CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8)); UNTIL i = max_num END REPEAT; COMMIT; END $$ #删除 # DELIMITER ; # drop PROCEDURE insert_dept;

5. 调用存储过程

dept

DELIMITER ; CALL insert_dept(100,10);

emp

#执行存储过程，往emp表添加50万条数据 DELIMITER ; #将结束标志换回 ; CALL insert_emp(100001,500000); CALL insert_emp10000(100001,10000);

6. 大量数据案例

#查询部门编号为101的，且员工编号小于100100的用户，按用户名称排序 EXPLAIN SELECT * FROM emp WHERE deptno =101 AND empno #结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。 #开始优化：思路：尽量让where的过滤条件和排序使用上索引但是一共两个字段(deptno,empno)上有过滤条件，一个字段(ename)有索引 1、我们建一个三个字段的组合索引可否？ create index idx_dno_eno_ena on emp(deptno,empno,ename); 我们发现using filesort 依然存在，所以ename 并没有用到索引。原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。所以 drop index idx_dno_eno_ena on emp; 但是我们可以把索引建成 create index idx_dno_ena on emp(deptno,ename); 也就是说empno 和ename这个两个字段我只能二选其一。这样我们优化掉了 using filesort。执行一下sql 速度果然提高了3倍。 ....... 但是如果我们建立 create index idx_dno_eno on emp(deptno,empno); 而放弃ename使用索引呢？果然出现了filesort，意味着排序没有用到索引。我们来执行以下sql 结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了近10倍。何故？原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的 empno 结论：当范围条件和group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

Show Profile

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

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

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

分析步骤

1. 是否支持，看看当前的mysql版本是否支持

Show variables like 'profiling'; 默认是关闭，使用前需要开启

2. 开启功能，默认是关闭，使用前需要开启

show variables like 'profiling'; set profiling=1;

3. 运行SQL

select * from emp group by id%10 limit 150000;

select * from emp group by id%20 order by 5

4. 查看结果，show profiles;

5. 诊断SQL，show profile cpu,block io for query n (n为上一步前面的问题SQL数字号码);

参数备注

6. 日常开发需要注意的结论

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

Creating tmp table 创建临时表

1 select * from emp group by id%20 limit 120000; 2 select * from emp group by id%20 order by 5

拷贝数据到临时表

用完再删除

Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！

locked

全局查询日志

配置启用

在mysql的my.cnf中，设置如下： #开启 general_log=1 # 记录日志文件的路径 general_log_file=/path/logfile #输出格式 log_output=FILE

编码启用

命令 set global general_log=1; #全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中 set global log_output='TABLE'; 此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看 select * from mysql.general_log;

尽量不要在生产环境开启这个功能。

4. MySql锁机制

概述

定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

生活购物

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类

从对数据操作的类型（读\写）分

读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。一种提高共享资源并发发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

表锁

行锁

三锁

表锁(偏读)

特点

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

案例分析

建表SQL

【表级锁分析--建表SQL】 create table mylock( id int not null primary key auto_increment, name varchar(20) )engine myisam; insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c'); insert into mylock(name) values('d'); insert into mylock(name) values('e'); select * from mylock; 【手动增加表锁】 lock table 表名字1 read(write)，表名字2 read(write)，其它; 【查看表上加过的锁】 show open tables; 【释放表锁】 unlock tables;

加读锁

我们为mylock表加read锁(读阻塞写例子) session_1 session_2 获得表mylock的READ锁定连接终端当前session可以查询该表记录其他session也可以查询该表的记录当前session不能查询其它没有锁定的表其他session可以查询或者更新未锁定的表当前session中插入或者更新锁定的表都会提示错误：其他session插入或者更新锁定表会一直等待获得锁：释放锁 Session2获得锁，插入操作完成：

加写锁

mylockwrite(MyISAM) session_1 session_2 获得表mylock的WRITE锁定待Session1开启写锁后，session2再连接终端当前session对锁定表的查询+更新+插入操作都可以执行：其他session对锁定表的查询被阻塞，需要等待锁被释放：在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞住。释放锁 Session2获得锁，查询返回：

结论

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。 MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）表独占写锁（Table Write Lock）锁类型他人可读他人可写读锁是否写锁否否结论：结合上表，所以对MyISAM表进行操作，会有以下情况： 1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。 2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

行锁(偏写)

特点

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

由于行锁支持事务，复习老知识

事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。 l 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 l 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。 l 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。 l 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失(Lost Update)

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读(Dirty Reads)

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读(Non-Repeatable Reads)

在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。一句话：一个事务范围内两个相同的查询却返回了不同数据。

幻读(Phantom Reads)

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。一句话：事务A读取到了事务B提交的新增数据，不符合隔离性。

事务隔离级别

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。常看当前数据库的事务隔离级别：show variables like 'tx_isolation';

案例分析

建表SQL

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb; insert into test_innodb_lock values(1,'b2'); insert into test_innodb_lock values(3,'3'); insert into test_innodb_lock values(4,'4000'); insert into test_innodb_lock values(5,'5000'); insert into test_innodb_lock values(6,'6000'); insert into test_innodb_lock values(7,'7000'); insert into test_innodb_lock values(8,'8000'); insert into test_innodb_lock values(9,'9000'); insert into test_innodb_lock values(1,'b1'); create index test_innodb_a_ind on test_innodb_lock(a); create index test_innodb_lock_b_ind on test_innodb_lock(b); select * from test_innodb_lock;

行锁定基本演示

行锁定基本演示 Session_1 Session_2 更新但是不提交，没有手写commit; Session_2被阻塞，只能等待提交更新解除阻塞，更新正常进行 commit命令执行下面试试1号会话更新a =1 下面试试2号会话更新a =9

无索引行锁升级为表锁

Session_1 Session_2 正常情况，各自锁定各自的行，互相不影响，一个2000另一个3000 由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁比如没加单引号导致索引失效，行锁变表锁被阻塞，等待。只到Session_1提交后才阻塞解除，完成更新

Select也可以加锁

读锁

select ..lock in share mode

共享锁(Share Lock) 共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。用法 SELECT ... LOCK IN SHARE MODE; 在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表（行？），而且这些线程读取的是同一个版本的数据。

写锁

select... for update

排他锁（eXclusive Lock）共享锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。用法 SELECT ... FOR UPDATE; 在查询语句后面增加 FOR UPDATE ，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

间隙锁危害

间隙锁带来的插入问题 Session_1 Session_2 阻塞产生，暂时不能插入 commit; 阻塞解除，完成插入【什么是间隙锁】当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”， InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。【危害】因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

案列结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

行锁分析

【如何分析行锁定】通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况 mysql>show status like 'innodb_row_lock%'; 对各个状态量的说明如下： Innodb_row_lock_current_waits：当前正在等待锁定的数量； Innodb_row_lock_time：从系统启动到现在锁定总时间长度； Innodb_row_lock_time_avg：每次等待所花平均时间； Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间； Innodb_row_lock_waits：系统启动后到现在总共等待的次数；对于这5个状态变量，比较重要的主要是 Innodb_row_lock_time_avg（等待平均时长）， Innodb_row_lock_waits（等待总次数） Innodb_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。最后可以通过 SELECT * FROM information_schema.INNODB_TRX\G; 来查询正在被锁阻塞的sql语句。

优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

尽可能较少检索条件，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度

锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。

涉及相同表的事务，对于调用表的顺序尽量保持一致。

在业务环境允许的情况下,尽可能低级别事务隔离

页锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

了解一下即可

5. 主从复制

复制的基本原理

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

三步骤+原理图

MySQL复制过程分成三步： 1 master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events； 2 slave将master的binary log events拷贝到它的中继日志（relay log）； 3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events

slave将master的binary log events拷贝到它的中继日志（relay log）

slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

复制的基本原则

每个slave只有一个master

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

每个master可以有多个salve

复制的最大问题

延时

一主一从常见配置

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

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

主机修改my.ini配置文件

1. [必须]主服务器唯一ID

server-id=1

2. [必须]启用二进制日志

log-bin=自己本地的路径/data/mysqlbin

log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin

3. [可选]启用错误日志

log-err=自己本地的路径/data/mysqlerr

log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr

4. [可选]根目录

basedir="自己本地路径"

basedir="D:/devSoft/MySQLServer5.5/"

5. [可选]临时目录

tmpdir="自己本地路径"

tmpdir="D:/devSoft/MySQLServer5.5/"

6. [可选]数据目录

datadir="自己本地路径/Data/"

datadir="D:/devSoft/MySQLServer5.5/Data/"

7. read-only=0

主机，读写都可以

8. [可选]设置不要复制的数据库

binlog-ignore-db=mysql

9. [可选]设置需要复制的数据库

binlog-do-db=需要复制的主数据库名字

从机修改my.cnf配置文件

[必须]从服务器唯一ID

[可选]启用二进制日志

因修改过配置文件，请主机+从机都重启后台mysql服务

主机从机都关闭防火墙

windows手动关闭

关闭虚拟机linux防火墙 service iptables stop

在Windows主机上建立帐户并授权slave

GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机器数据库IP' IDENTIFIED BY '123456';

Subtopic

flush privileges;

查询master的状态

show master status;

记录下File和Position的值

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

在Linux从机上配置需要复制的主机

CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='zhangsan',MASTER_PASSWORD='123456',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字;

CHANGE MASTER TO MASTER_HOST='192.168.124.3', MASTER_USER='zhangsan', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值;

启动从服务器复制功能

start slave;

show slave status\G

下面两个参数都是Yes，则说明主从配置成功！

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

主机新建库、新建表、insert记录，从机复制

如何停止从服务复制功能

stop slave;