MySQL系列11—简单优化

我们知道MySQL是一种关系型数据库的管理系统，而其中关系型数据库讲的是保存一种现实中的关系，比如说某个班级有多个学生，这就可以构成一张学生表，表中维护的多个学生的信息。而学生关联着多种信息，比如年龄、班级等，那么我们该怎么设计出一张表呢？见下方的四大范式：

1. 结构优化

1.1 四大范式

• 1NF：字段不可再分,否则就不是关系数据库。
  • 比如：我就是一个学生，当前班级是19级计专班，但是在数据库中不能这样存储，因为这里的班级是年级+专业组成的，当我要查询年级或专业的时候，就查不到了。所以要求字段必须不可再分。
• 2NF：在第一范式的基础上更进一层，确保每一个非主属性完全函数依赖于任何一个候选码。
  • 比如：我选修了数据结构这门课，这门课有名字，编号等。那么能不能写入学生表呢？因为此门课并不依赖于主键，那么在操作中则会存在某些问题
    • 插入异常：如果在数据库中插入学生，但是还没有选课，那么课程就为null，可能并不允许为空
    • 删除异常：如果学生要删除某门课，而有时候此课程为主属性，造成整个元组被删除
    • 修改复杂：如果多个学生选择了一门课，当修改课程信息时，要修改多条数据
• 3NF：在第二范式的基础上更进一层,目标是确保每个非主属性既不传递依赖于码，也不部分依赖于码，即直接依赖于码
  • 比如：我是属于信工学院的，而信工学院包含地址、联系电话等，那么能保存吗？不能，因为按这种插入方式，可能在一张表中每条记录都保存了相同的学院信息，此时存在大量的数据冗余，造成修改复杂等。
• BCNF：在第三范式的基础上，消除主属性对候选码的部分依赖和传递依赖
  • 比如：现在有学生S，教师T和课程J，有如下依赖关系：（S, T） -> J，（S,J）->T,J->T，

反范式化

实际上并不是所有的数据库设计都会应用高等级范式，虽然高等级范式保证数据存在更少的数据冗余，但是这也带来了数据库操作性能的降低，比如在查询时需要连接多个表。

因此常采用反范式化来适当的增加冗余，以达到优化查询效率的目的。如果以前需要连接其他表查询的字段直接插入到查询表，以空间换取时间。

1.2 分区表

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

通过实现分区表，事务操作/锁的粒度被限制在分区而不是段，大大提升了数据库的性能。

分区表的优点：

1. 和单个磁盘或者文件系统分区相比，可以存储更多数据
2. 优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
3. 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
4. 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
5. 分区数据保存在多个文件中提高容错

1.2.1 分区表的操作

select查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert操作

当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表

delete操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

update操作

当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录所在的分区，然后取出数据并更新，再判断更新后的数据应存放的分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

有些操作时支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作

虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。

1.2.2 分区类型

• range分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区
• list分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区
• hash分区：基于给定的分区个数，把数据分配到不同的分区
• key分区：类似于hash分区

1.2.3 分区实例

range分区

范围分区表的分区方式是：每个分区都包含行数据且分区的表达式在给定的范围内，分区的范围应该是连续的且不能重叠，可以使用values less than运算符来定义。

# 建表时分区
CREATE TABLE teacher
(id VARCHAR(20) NOT NULL ,
NAME VARCHAR(20),
age VARCHAR(20),
birthdate DATE NOT NULL,
salary INT
)
PARTITION BY RANGE(YEAR(birthdate))
(
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1990),
PARTITION p3 VALUES LESS THAN maxvalue
);

# 建表后分区
ALTER TABLE teacher 
partition by range(year(birthdate))
(
partition p1 values less than (1970),
partition p2 values less than (1990),
partition p3 values less than maxvalue
);

List分区

create table student
 (id varchar(20) not null ,
 studentno int(20) not null,
 name varchar(20),
 age varchar(20)
 )
 partition by list(studentno)
 (
 partition p1 values in (1,2,3,4),
 partition p2 values in  (5,6,7,8),
 partition p3 values in (9,10,11)
 );

Hash分区

create table user (
  id int(20) not null,
  role varchar(20) not null,
  description varchar(50) 
)
partition by hash(id) 
partitions 10;

Key分区

create table role( id int(20) not null,name varchar(20) not null)
partition by linear key(id)
partitions 10;

分区操作

# 添加分区
alter table user add partition(partition p4 values less than MAXVALUE);

# 删除分区
alter table student drop partition p1;

# 创建子分区
create table role_subp(id int(20) not null,name int(20) not null)
partition by list(id)
subpartition by hash(name)
subpartitions 3
(
  partition p1 values in(10),
  partition p2 values in(20)
)

# 符合分区
alter table user
reorganize partition p1,p3 into
(partition p1 values less than (1000));

1.3 分库分表

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

分库分表包括分库和分表两个部分，在生产中通常包括：垂直分库、水平分库、垂直分表、水平分表四种方式。

• 垂直分表： 将一个表按照字段分成多表，每个表存储其中一部分字段。
  • 举例：一件商品有商品描述、具体信息等属性，将访问频率高的放在一张表（商品描述），访问频率低的放在另一张表
  • 优势1：为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响。
  • 优势2：充分发挥热门数据的利用效率，比如大字段独立放一个表
• 垂直分库：垂直分表只解决了单一表过大的问题，但在此表上的所有操作还是竞争同一个物理机的CPU等资源，因此需要根据业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用
  • 举例：淘宝网存储多种信息，比如店家信息，商品信息、个人信息等。这时候就可以进行分库
  • 优势1：能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 优势2：高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈
• 水平分表：将某一表的所有记录行划分到同一个数据库的多张表中，每个表只有这个表的部分数据
  • 举例：按照ID的奇偶性将数据分到两张表，然后根据ID奇偶性来访问
  • 优势1：优化单一数据量过大而产生的性能问题
  • 优势2：避免IO争抢并减少锁表的几率
• 水平分库：把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上，类似水平分表。
  • 优势1：解决了单库大数据，高并发的性能瓶颈
  • 优势2：提高了系统的稳定性及可用性

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。

垂直拆分的优缺点

• 可以使得数据变小，一个数据块（block）就能存放更多的数据，在查询时就会减少I/O次数
• 可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起
• 数据维护简单
• 主键出现冗余
• 会引起表连接join操作，增加cpu开销
• 依然存在单表数据量多大问题
• 事务处理复杂

水平拆分的优缺点

• 不存在单库大数据和高并发的性能瓶颈

• 应用端改造较少

• 提高了系统的稳定性和负载能力

• 分片事务一致性难以解决

• 跨节点Join性能差，逻辑复杂

• 数据多次扩展难度跟维护量极大

#2. 数据类型优化

在大量数据的情况下，更小的数据类型能节省大量存储空间。最好使用可以存下你的数据的最小的数据类型，尽量少用text等大数据类型，非要使用可以考虑分表（将text分到另一个表）。

• 尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
• 使用枚举或整数代替字符串类型。
• 尽量使用TIMESTAMP而非DATETIME。
• 单表不要有太多字段，建议在20以内。
• 用整型来存IP，可以利用MySQL自带的函数进行转换、日期时间一般用MySQL自建时间类型而不是字符串类型、电话一般存为char类型
  • 节省空间，不管是数据存储空间，还是索引存储空间
  • 便于使用范围查询（BETWEEN…AND），且效率更高
• 表字段避免null值出现，null值很难进行查询优化且占用额外的索引空间，推荐默认数字0代替null。此外包含NULL的复合索引是无效的。

3. 查询优化

MySQL优化的实质就是加快SQL语句的查询速度，在速度和查询复杂度之间做一个折衷。因此，我们需要根据慢查询语句快速定位原因，并进行改进。

在了解查询优化之前，先了解一下存储引擎的执行计划

3.1 MySQL中的select执行顺序

SQL Select 语句完整的执行顺序：

• from 子句组装来自不同数据源的数据；
• where 子句基于指定的条件对记录行进行筛选；
• group by 子句将数据划分为多个分组；
• 使用聚集函数进行计算；
• 使用 having 子句筛选分组；
• 计算所有的表达式；
• select 的字段；
• 使用 order by 对结果集进行排序。

3.2 SQL优化

• 使用连接（join）来代替子查询：因为子查询时mysql会创建临时表，而join则不会，因为子查询时mysql会创建临时表，而join则不会

• 拆分大的delete或insert语句：因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。因此需要尽可能拆分

• 不做列运算：因为mysql做列运算会进行全表扫描

• OR改写成IN：OR的效率是O(n)级别，IN的效率是O(logn)级别，in的个数建议控制在200以内。
• 不用函数和触发器，在应用程序实现。
• 少用JOIN。
• 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5。

3.3 锁设置原则

对于MyISAM的表锁，主要讨论了以下几点：

• 在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
• MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
• 由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，本文主要讨论了以下几项内容：

• InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
• 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量 使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

3.4 MySQL执行计划

在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。此时可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

id

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

id号分为三种情况：

​ 1、如果id相同，那么执行顺序从上到下

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;

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

explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

​ 3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

select_type Value	Meaning
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION.
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

--sample:简单的查询，不包含子查询和union
explain select * from emp;

--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;

--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno in (select distinct deptno from dept);

--DERIVED: from子句中出现的子查询，也叫做派生类，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
 explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
 
--uncacheable union:表示union的查询结果不能被缓存：sql语句未验证

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集
1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名

​ 2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表

​ 3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情况下，得保证查询至少达到range级别，最好能达到ref

--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain  select empno from emp;

--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

--index_subquery：利用索引来关联子查询，不再扫描全表
explain select * from emp where emp.job in (select job from t_job);

--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
 explain select * from emp e where e.deptno in (select distinct deptno from dept);
 
--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来

--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
explain select * from emp e where  e.mgr is null or e.mgr=7369;

--ref：使用了非唯一性索引进行数据的查找
 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;

--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
 
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

possible_keys

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

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key_len

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

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

ref

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

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

explain select * from emp;

extra

包含额外的信息。

--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;

--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

--using where:使用where进行条件过滤
explain select * from t_user where id = 1;

--using join buffer:使用连接缓存，情况没有模拟出来

--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;

3.4 避免临时表

临时表是MySQL用于存储一些中间结果集的表，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。为什么会产生临时表？一般是由于复杂的SQL导致临时表被大量创建。

临时表分为两种，一种是内存临时表，一种是磁盘临时表。内存临时表采用的是memory存储引擎，磁盘临时表采用的是innodb存储引擎。

使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。

一般使用explain查看执行计划，Extra列看到Using temporary就意味着使用了临时表

直接使用临时表的场景：

• 表包含TEXT或者BLOB列；
• GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
• 使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；

如何避免临时表

• 创建索引：在ORDER BY或者GROUP BY的列上创建索引；
• 分拆很长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。

查询当前创建临时表的大小

show gloabl status like 'created_tmp%';

Variable_name   Value
Created_tmp_disk_tables 67842	# 磁盘临时表
Created_tmp_files   1430	# 临时表文件
Created_tmp_tables  327575257	# 所有临时表

服务器对临时表的配置

show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');

Variable_name   Value
max_heap_table_size 67108864 # 指定用户创建的内存表的最大大小
tmp_table_size  2097152	# 指定系统创建的内存临时表最大大小

3.5 索引优化

实际上，mysql优化器（optimizer）说了才算，它会计算使用索引和不使用索引的成本，择优选择生成执行计划。而我们能做的就是尽可能的让它能使用上索引，当索引成本过高时，优化器自动选择成本低的执行计划。

3.5.1 建立索引

1.在用于where判断order排序和join on的字段上创建索引。
2.索引个数不要过多（索引占用磁盘空间，而且增删改的时候需要维护索引的B+树）。
3.离散度低的字段，比如性别，不要建立索引。
4.频繁更新的值，不要作为主键或索引。
5.联合索引把离散度高的值放在前面
6.不建议用无序的字段作为索引，如UUID，身份证号等。因为你插入数据的时候是乱序的，不知道分配在哪个页上，如果插入到的是写满的页，就会导致页的分裂。最好使用自增或者递增的值作为索引。
7.过长的字段建立前缀索引（短索引），不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

• 对查询频次较高，且数据量比较大的表建立索引。
• 索引字段的选择，最佳候选列应当从where子句的条件中提取
• 使用唯一索引，区分度越高，使用索引的效率越高。
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引

3.5.2 避免索引

1.在索引列上使用函数replace、substr、concat、sum、count、avg。。。等表达式。
2.字符串类型不加引号，出现隐式转换，导致用不上索引。
3.like查询以%开头。
4.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
5.如果mysql的优化器感觉全表扫描要比使用索引快,则不使用索引。

3.5.3 索引失效场景

• 使用模糊查询时，在左边没有通配符时，才会使用索引，如"2%"
• 如果出现OR(或者)运算，要求所有参与运算的字段都存在索引，才会使用到索引。（MyISAM专属）
• 对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。
• 当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。
• 如果列类型是字符串，要使用双引号。如where name = 'cao'会失效（隐式转换）
• 使用一些操作符，如聚簇函数、!=、not in、in等
• 当值为null时，也不使用索引

3.6 慢查询与showProfile

为了找到慢SQL，一般需要先开启慢查询日志，设置

开启慢查询定位：

SHOW VARIABLES LIKE "%query%"；	# 查看慢查询日志
SET GLOBAL slow_query_log = ON；	# 开启慢查询日志
SET @@long_query_time=10；	# 设置超过10s即为慢查询 

SELECT * FROM student；

根据配置的慢查询日志记录文件/var/lib/mysql/iZ2zebm9lsglek26h07uyeZ-slow.log ,查看以上的命令操作。

# Time: 2019-09-20T09:17:49.791767Z
# User@Host: root[root] @ localhost []  Id:   150
# Query_time: 0.002549  Lock_time: 0.000144 Rows_sent: 1  Rows_examined: 4079
SET timestamp=1566292669；
select * from city where Name = 'Salala'；

show profile

假如通过explain还是无法定位sql运行慢的问题，则需要分析sql在数据传输、网络、连接是否是死锁等需要使用比explain更加细粒度的查询和排查show profiles；

通过其可以看到更小的数据问题。一般来说通常看一下几个参数：

• converting heap to MyISAM：查询结果太多，内存都不够用，切换到磁盘了
• Creating tmp table：创建了临时表，数据被拷贝到临时表，用完再删除数据
• Copying to tmp table on disk： 把内存中临时表复制到磁盘，危险操作
• locked：加锁

此外还有其他操作，

• show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否 锁表等，可以实时地查看 SQL的执行情况，同时对一些锁表操作进行优化。
• trace分析优化器执行计划

4. 系统配置优化

MySQL是运行在操作系统上的数据库管理软件，其必不可少的要使用系统资源，在某些情况下，使用资源的默认值并不能满足实际业务需要。因此，我们需要根据实际需要修改系统配置，已达到最好的工作场景。

常见配置的修改：

• 设置MySQL使用过程中的常用参数，如最大连接数等。
• 设置MySQL使用系统资源的限制，如打开文件数、使用线程数、缓冲区大小等等

5. 硬件资源优化

5.1 主从复制

在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

5.1.1 主从复制是什么

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

5.1.2 主从复制的优点

• 分离读写，提高业务的并发量
• 做数据的热备份
• 扩展系统架构，以提升系统的吞吐量。

5.1.3 主从复制的原理

• master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
• slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
• 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

主从复制的流程：

• 从库会生成两个线程,一个I/O线程,一个SQL线程
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行

5.2 读写分离

MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

读写分离提升性能的原因：

• 物理服务器增加，机器处理能力提升。拿硬件换性能。
• 主从只负责各自的读和写，极大程度缓解X锁和S锁争用。
• slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
• slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
• master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。
• slave可以单独设置一些参数来提升其读的性能。
• 增加冗余，提高可用性。