mysql 优化-总结

mysql 优化

一、mysql 数据格式

1、 compact 行格式

真实数据部分还有3行隐藏的列

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID，唯一标识一条记录，没有uniquekey的时候才有
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

2、页格式

名称	中文名	占用空间（字节）	描述
File Header	文件头部	38	页的一些通用信息
Page Header	页面头部	56	数据页专有的一些信息
Infimum + Supremum	最小记录和最大记录	26	两个虚拟的行记录
User Records	用户记录	不确定	实际存储的行记录内容
Free Space	空闲空间	不确定	页中尚未使用的空间
Page Directory	页面目录	不确定	页中的某些记录的相对位置
File Trailer	文件尾部	8	校验页是否完整

在InnoDB中，数据会存储到磁盘上，在真正处理数据时需要先将数据加载到内存，表中读取某些记录时，InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB，也就是说，当需要从磁盘中读数据时每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中。

二、mysql的锁机制

1、MySQL锁的基本介绍

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

​ 相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

按锁的粒度划分：

​ **表级锁：**开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
​ **行级锁：**开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

​ 从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

2、MyISAM表锁

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

建表语句：

CREATE TABLE `mylock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('4', 'd');

MyISAM写锁阻塞读的案例：

​ 当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁释放为止。

session1	session2
获取表的write锁定 lock table mylock write;
当前session对表的查询，插入，更新操作都可以执行 select * from mylock; insert into mylock values(5,‘e’);	当前session对表的查询会被阻塞 select * from mylock；
释放锁： unlock tables；	当前session能够立刻执行，并返回对应结果

MyISAM读阻塞写的案例：

​ 一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新就会出现锁等待。

session1	session2
获得表的read锁定 lock table mylock read;
当前session可以查询该表记录： select * from mylock;	当前session可以查询该表记录： select * from mylock;
当前session不能查询没有锁定的表 select * from person Table ‘person’ was not locked with LOCK TABLES	当前session可以查询或者更新未锁定的表 select * from mylock insert into person values(1,‘zhangsan’);
当前session插入或者更新表会提示错误 insert into mylock values(6,‘f’) Table ‘mylock’ was locked with a READ lock and can’t be updated update mylock set name=‘aa’ where id = 1; Table ‘mylock’ was locked with a READ lock and can’t be updated	当前session插入数据会等待获得锁 insert into mylock values(6,‘f’);
释放锁 unlock tables;	获得锁，更新成功

3、注意:

MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁，上例中的加锁时为了演示效果。

MyISAM的并发插入问题

MyISAM表的读和写是串行的，这是就总体而言的，在一定条件下，MyISAM也支持查询和插入操作的并发执行

session1	session2
获取表的read local锁定 lock table mylock read local
当前session不能对表进行更新或者插入操作 insert into mylock values(6,‘f’) Table ‘mylock’ was locked with a READ lock and can’t be updated update mylock set name=‘aa’ where id = 1; Table ‘mylock’ was locked with a READ lock and can’t be updated	其他session可以查询该表的记录 select* from mylock
当前session不能查询没有锁定的表 select * from person Table ‘person’ was not locked with LOCK TABLES	其他session可以进行插入操作，但是更新会阻塞 update mylock set name = ‘aa’ where id = 1;
当前session不能访问其他session插入的记录；
释放锁资源：unlock tables	当前session获取锁，更新操作完成
当前session可以查看其他session插入的记录

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 352   |
| Table_locks_waited    | 2     |
+-----------------------+-------+
--如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

3、InnoDB锁

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

我们写的sql 如果不手动通过begin,start transaction 方式开启一个事务，mysql会自动帮忙启动一个事务来执行这个sql。

2、并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

脏读： 一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”

不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

3、mysql隔离级别实现原理

MVCC

为什么可重复读隔离级别不会产生幻读而读以提交会产生幻读？

读已提交隔离级别

A事务select for update对主键行加锁，B插入一条记录-------->插入成功

A事务select for update对唯一索引行加锁，B插入一条记录-------->插入成功

A事务select for update对普通索引行加锁，查出来的记录有多个，B插入一条记录-------->插入成功

A事务select for update对无索引的行加锁，B插入一条记录-------->插入成功

可重复读隔离级别

A事务select for update对主键行加锁，B插入一条记录-------->插入成功

A事务select for update对唯一索引行加锁，B插入一条记录-------->插入成功

A事务select for update对普通索引行加锁，查出来的记录有多个，B插入一条记录-------->插入失败等待A事务提交

A事务select for update对无索引的行加锁，B插入一条记录-------->插入失败等待A事务提交

可重复读加了间隙锁锁住了查询出来的区间范围类的行记录，避免幻读

4、MVCC和间隙锁之间的关系

多版本并发控制（Multi-VersionConcurrency Control，Mvcc）是mysql的InnoDb存储引擎实现隔离级别的一种具体的方式，用于实现读已提交和可重复读这两种隔离级别。读未提交总是读取最新的数据行，无需使用MVCC。可串行化隔离级别需要对所有的行都加锁，仅仅依赖MVCC是无法实现的。MVCC可以认为是行级锁的一个变种，但是他在很多情况下都避免了加锁操作，因此开销更低。如此一来不同的事务在并发过程中， SELECT 操作可以不加锁而是通过 MVCC 机制读取指定的版本历史记录，并通过一些手段保证保证读取的记录值符合事务所处的隔离级别，从而解决并发场景下的读写冲突。

mvcc实现原理：undo log + read view （可读视图）

undo log 链 ：多个事务并行操作某行数据的情况下，不同事务对该行数据的 UPDATE 会产生多个版本，然后通过回滚指针组织成一条 Undo Log 链

read view 中记录了活跃事务的id列表；RR（可重复都）在事务第一个读的时候生成read view, 后面都复用此read view；RC(读已提交)在事务每次读的时候都会重新生成read view。读的时候都是比较当前事务的版本号和read view中活跃事务版本号id的大小，如果当前的访问的比活跃的最小的小，说明当前的版本已生效可以访问；如果当前比最大的还大说明之前还有活跃的事务，则遍历undo log链找可以访问的版本。

MVCC不能解决幻读问题，在可重复读的隔离级别下，使用MVCC+Next-Key Locks可以解决幻读的问题。Next-KeyLocks是InnoDB行锁和间隙锁的默认组合。间隙锁是在锁定索引记录间隙，确保索引记录的间隙不变。Next-KeyLocks当对数据进行条件范围检索的时候，对其范围内存在的值进行加锁，防止其他事务的插入操作来达到防止幻影读的目的。

5、InnoDB的行锁模式及加锁方法

锁的类型分类

共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
排他锁（x）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

​ mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

InnoDB行锁实现方式

​ InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

2、创建带索引的表进行条件查询，innodb使用的是行锁

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现冲突的。

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name=‘1’ for update
	select * from tab_with_index where id = 1 and name=‘4’ for update 虽然session2访问的是和session1不同的记录，但是因为使用了相同的索引，所以需要等待锁

4、总结

对于MyISAM的表锁，主要讨论了以下几点：
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，本文主要讨论了以下几项内容：
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

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

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

三、mysql索引

1、索引的定义

用于查找的关键字

2、索引的类型

主键索引：primary key

唯一索引：unique key 对字段进行唯一约束

普通索引：index

全文索引：关键字不是字段本身，而是从字段中提取出的关键词；普通索引使用整个字段作为关键字，而全文索引是自动提取出的关键词，核心是分词，不识别中文分词，索引全文索引基本不用，因此需要额外的分词工具支持中文分词，来支持全文索引。

聚集索引（聚簇索引）：索引关键字和记录一起存储；这种在Btree基础上改造的数据结构叫B+tree；innodb中除了主键索引是聚簇索引结构，其他非主键索引都市Btree结构。所以innodb中必须加一个id字段，避免后面主键的需改带来物理结构的改变。

非聚集索引：不会改变数据的物理结构

联合索引：多个字段组合成一个索引

前缀索引：取字段的前几个字作为索引关键字

覆盖索引：一种现象查询的数据可以直接从索引中获取

Hash索引：当索引被载入载入内存时，采取的结构就是hash结构，key value类型的hash结构。

Btree索引：磁盘数据索引结构

3、索引的使用场景

where ：where条件后面常用的查询条件建立索引

order by ：不用索引全表扫 ，外部排序

join ：不用索引，全表扫描，通过临时表+外部排序实现很慢

覆盖查询 ：一种现象，索引中有需要查询的数据时，直接从索引获取，不再查源数据；主要用于复合索引的场景

select first_name, last_name from user;

没有where，join，order by 但是依然走了索引

4、语法细节

[1] 字段需要独立出现在表达式的一侧

[2]like不能以通配符%开头；字符串比较时不能用包含逻辑%XX%；包含逻辑正确的解决方案是全文索引，第三方提供的全文索引完成。

[3]复合索引的非最左侧字段不能独立使用索引；复合索引主要用于多个字段同时出现的场景；如果多个字段经常同时出现那么对他们建立复合索引的效率比独立创建索引的效率高。

[4]or要使用时必须保证or两边的查询条件都能走索引，否则会走全表扫描。

[5]状态值不容易使用索引，状态值会导致一个值同时匹配大量的记录，mysql认为效率没全表扫描效率高会走全表扫描，因为大量的随机磁盘扫描开销>一次顺序的全表扫描开销

5、如何创建索引

建立基础索引：在where、join、order by字段上建立索引

优化策略：

[1]、如果条件经常一起出现，多字段索引可以升级为复合索引。

[2]、如果通过增加一两个字段可以出现索引覆盖，那么可以增加这一两个字段。

[3]、查询时，不会用到的索引应该删除，因为索引维护需要额外的开销。

[4]、对应长字段可以使用前缀索引index(field(10))：选取长度的策略：

select count(*)/select count(distinct left(password, 9))当这个值靠近select count(*)/select count(distinct password) 时那么这个长度就是适合的长度。

6、索引的物理结构

B树索引：索引存储在磁盘上，索引的数据库在磁盘上存储索引时用的都是B树索引；一个btree节点存储多少个索引关键字是由节点的大小和关键字的大小决定的，通常节点的大小是由计算机文件系统决定的，是固定的，一次性磁盘读取的内容就是一个节点的大小。

通常一个两层索引节点的Btree可以存储1000*1000=100W页的数据，假设一个索引节点可以存1000条记录的话。而在100万页的数据中找一页最多只需要查两趟，遍历2000条记录。

无论普通、唯一、主键、全文索引都是采用Btree索引。

1.1 B+tree

1.2 BST树和红黑树

1.3 B树和B+树的区别

B树每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

B+树只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。

后来，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。

7、覆盖索引

1、当发起一个被索引覆盖的查询时，在explain的extra列可以看到using index的信息，此时就使用了覆盖索引

mysql> explain select store_id,film_id from inventory\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

2、在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以进一步的进行优化，可以使用innodb的二级索引来覆盖查询。

例如：actor使用innodb存储引擎，并在last_name字段又二级索引，虽然该索引的列不包括主键actor_id，但也能够用于对actor_id做覆盖查询

mysql> explain select actor_id,last_name from actor where last_name='HOPPER'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

8、前缀索引实例说明

​ 有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

​ 一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

案例演示：

--创建数据表
create table citydemo(city varchar(50) not null);
insert into citydemo(city) select city from city;

--重复执行5次下面的sql语句
insert into citydemo(city) select city from citydemo;

--更新城市表的名称
update citydemo set city=(select city from city order by rand() limit 1);

--查找最常见的城市列表，发现每个值都出现45-65次，
select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

--查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看城市出现的次数
select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
--此时前缀的选择性接近于完整列的选择性

--还可以通过另外一种方式来计算完整列的选择性，可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了
select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7,
count(distinct left(city,8))/count(*) as sel8 
from citydemo;

--计算完成之后可以创建前缀索引
alter table citydemo add key(city(7));

--注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。 

9、索引优化分析案例

预先准备好数据

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `itdragon_order_list`;
CREATE TABLE `itdragon_order_list` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，默认自增长',
  `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
  `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
  `net` double DEFAULT NULL COMMENT '净收入(RMB)',
  `stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
  `order_status` int(11) DEFAULT NULL COMMENT '订单状态',
  `descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
  `finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
  `create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
  `order_level` int(11) DEFAULT NULL COMMENT '订单级别',
  `input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
  `input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');
INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');
INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');

逐步开始进行优化：

第一个案例：

select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
--通过查看执行计划发现type=all,需要进行全表扫描
explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

--优化一、为transaction_id创建唯一索引
 create unique index idx_order_transaID on itdragon_order_list (transaction_id);
--当创建索引之后，唯一索引对应的type是const，通过索引一次就可以找到结果，普通索引对应的type是ref，表示非唯一性索引赛秒，找到值还要进行扫描，直到将索引文件扫描完为止，显而易见，const的性能要高于ref
 explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 
 --优化二、使用覆盖索引，查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
 explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

第二个案例

--创建复合索引
create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

--创建索引之后发现跟没有创建索引一样，都是全表扫描，都是文件排序
explain select * from itdragon_order_list order by order_level,input_date;

--可以使用force index强制指定索引
explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
--其实给订单排序意义不大，给订单级别添加索引意义也不大，因此可以先确定order_level的值，然后再给input_date排序
explain select * from itdragon_order_list where order_level=3 order by input_date;

四、mysql执行计划

​ 在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。

​ 可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

​ 官网地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

1、执行计划中包含的信息

Column	Meaning
id	The SELECT identifier
select_type	The SELECT type
table	The table for the output row
partitions	The matching partitions
type	The join type
possible_keys	The possible indexes to choose
key	The index actually chosen
key_len	The length of the chosen key
ref	The columns compared to the index
rows	Estimate of rows to be examined
filtered	Percentage of rows filtered by table condition
extra	Additional information

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;

五、mysql读写分离

1、读写分离的介绍

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

​ MySQL读写分离能提高系统性能的原因在于：

​ 1、物理服务器增加，机器处理能力提升。拿硬件换性能。

​ 2、主从只负责各自的读和写，极大程度缓解X锁和S锁争用。

​ 3、slave可以配置myiasm引擎，提升查询性能以及节约系统开销。

​ 4、master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。

​ 5、slave可以单独设置一些参数来提升其读的性能。

​ 6、增加冗余，提高可用性。

2、读写分离的配置

1、硬件配置

master 192.168.85.11
slave  192.168.85.12
mycat  192.168.85.14

2、首先在master和slave上配置主从复制

3、进行proxy的相关配置

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"      slaveThreshold="100">
    <!-- 心跳检测 -->
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->
    <writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
        <!-- can have multi read hosts -->
        <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
    </writeHost>
    <!-- writeType=0 时，写操作只会发到第一个writehost上面，第一个挂了之后才会发给第二个-->
    <writeHost host="hostS1" url="localhost:3316" user="root" password="123456" />
</dataHost>

4、进行连接

连mycat

#mysql的命令行会出现无法连接的情况，所以建议使用客户端
mysql -uroot -p123 -h192.168.85.14 -P 8066

3、读写分离实现原理

[1] 通过中间框架组件实现-例如mycat

[2] 底层数据库读库和写库之间的数据同步通过数据库实现，binlog。

六、mysql主从复制原理

1、为什么需要主从复制？

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

2、做数据的热备

3、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

2、什么是mysql的主从复制？

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

3、mysql复制原理

原理：

​ （1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

​ （2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

​ （3）同时主节点为每个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语句逐一执行;

注意：

1–master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
2–slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
3–Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
4–Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）
5–master和slave两节点间时间需同步

具体步骤：

1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave

2、从库的IO线程和主库的dump线程建立连接。

3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。

4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。

5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中

6、从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

7、通过show master status 和show slave status 命令可以查看数据库状态。

4、mysql主从形式

（一）一主一从

（二）主主复制

（三）一主多从

（四）多主一从

（五）联级复制

5、mysql主从同步延时分析

​ mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高，slave的sql thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多，另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。

​ 解决方案：

​ 1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

​ 2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。

​ 3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。

​ 4.不同业务的mysql物理上放在不同机器，分散压力。

​ 5.使用比主库更好的硬件设备作为slave，mysql压力小，延迟自然会变小。

​ 6.使用更加强劲的硬件设备

mysql5.7之后使用MTS并行复制技术，永久解决复制延时问题------自学

七、负载均衡和高可用

mysql中通常采用一写多读的方案实现负载均衡。读操作往往多余写操作，写操作是需要同步的；负载均衡主要是针对读服务器实现。一般也是通过第三方框架组件实现的，如mycat

[1] 负载的算法：

轮询、加权轮询

八、范围分区

将一张表中的数据和索引分散到不同的文件中存储称为分区，划分出来的不同的文件就是不同的分区。

innodb: .ibd文件

myisam：.myd;.myi文件

通常一个表对应的数据和索引文件集中存储在这组文件中；

分区是将一个表中的大量记录分散存在不同的数据和索引文件中进行存储

innodb对应多个.ibd文件

myisam对应多个.myd,.myi文件

分区之后每个文件存储的记录就会减少，保证单独文件的处理效率。

分区对客户端是透明的，而服务器端进行处理，将数据分散到不同文件中；之所以生产环境不用分区而用分库和分表是因为将处理的压力分散到客户端减少mysql服务器端处理的压力。

1、范围分区介绍

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

​ 1、创建普通的表

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

​ 2、创建带分区的表，下面建表的语句是按照store_id来进行分区的，指定了4个分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
--在当前的建表语句中可以看到，store_id的值在1-5的在p0分区，6-10的在p1分区，11-15的在p3分区，16-20的在p4分区，但是如果插入超过20的值就会报错，因为mysql不知道将数据放在哪个分区

2、分区表的底层原理

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

​ 分区表的操作按照以下的操作逻辑进行：

​ select查询

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

​ insert操作

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

​ delete操作

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

​ update操作

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

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

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

3、分区算法

[1] hash算法

通常采用求余的方法，例如对id取模

partition by hash(id) partitions 10;

[2] key算法

和hash算法类似，都是求余分配，先要对字段算一个整数值，然后在求余

partition by key(subject) partitions 10;
primary key(id, other_column);

分区字段必须是主键的一部分

[3] rang算法

范围分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

[4] list算法

也是一种条件分区，表示列值的条件

PARTITION BY LIST (status) (
    PARTITION p0 VALUES IN (1,2),
    PARTITION p1 VALUES IN (3)
);

4、分区管理语法

list、range，可以删除或新增分区

alert table tbname drop partition XXX

hash、key分区可以修改任意分区的数量

alert table tbname add partition XXX  增加分区
alert table tbname coalesce partiotion 6 减少分区

5、注意事项

只有数据表中的数据很大时（千万，亿），时分区可以提升效率。

只有索引字段位分区字段时，分区效率才会比较明显，分区字段的选择很重要，业务逻辑需要根据分区字段进行相关的调整。例如会员编码，业务逻辑需要根据会员编码做相关调整。

九、分库分表

在应用层面，将应用程序数据分散到不同的表中进行存储。对比分区，分区是在mysql服务器层面处理，分表是在应用层面，这样就减少了数据库的压力。

1、分表的原因

[1] 数据库减压

[2] 分区算法的局限性

[3] 数据库支持不完善，5.1之后才支持分区

2、水平分表

例如券实体表分1536表，按会员编码分表。

3、垂直分表

每个表记录数量一致，但是字段不一样

十、其他

1、查询缓存

在my.cnf 中配置query_cache_type 0：关闭 1或2开启

缓存存在失效的问题，当数据表存在改动时，基于该数据表的任何查询缓存都会失效

2、DDL执行

表结构更改会导致全表被锁定，此时表处于维护状态，不能做其他操作。5.6之后采用online ddl操作

因此维护DDL的策略（例如alter table 之类的操作时）是copy策略：

copy一个新的表，满足新的结构，将就的表读到新的表上，旧的表同样可以进行读写操作，copy数据的过程中，通过日志的形式记录旧表上的更新操作，等新表copy完数据，然后再将更新的数据在新表上重做。最后用新表替换旧表。

DDL：（data definition language）数据库定义语言； CREATE、ALTER、DROP等 操作

DML：（data manipulation language）数据操纵语言 ； SELECT、UPDATE、INSERT、DELETE 等

DCL：（Data Control Language）数据库控制语言 ； 是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句

3、大页码，limit offset， limit

尽量通过条件过滤数据而不是通过offset跳过已经查询到的数据

4、不用select * 尽量选自己需要的字段

问题不大，主要是网络传输多数据的延时。

5、 单表和多表查询

尽量使用单表代替多表进行联合查询，单表查询计算的压力在应用层面，多表查询压力在数据库层面。多表查询可能会增加表的被锁定时间，降低了程序的并发性能。

单表查询可能就增加了编码层面程序的复杂度，使用ORM模型。

6、count(*)

对于myisam有记录总量没问题，innodb需要自己完成常规操作

count(id) 统计id不为null的记录数，count(1)与count(*)类似

7、慢查询日志

执行时间超过指定时间的sql记录

开启日志->设置临界时间->读取日志内容->优化sql

show variables like 'slow_query_log';
set gloable slow_query_log=on
set long_query_time=1

8、profile信息统计

统计sql执行时间，详细记录sql的执行时间

show variables like 'profiling';
set profiling=on;
show profiles;
show profile for query query_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VjRlJHgz-1595171095153)(asserts/1586604866702.png)]

9、典型服务器配置

max_connections 最大连接数

table_open_cache 表句柄缓存

key_buffer_size 索引缓冲大小–>从硬盘中读取的索引缓存到内存的大小

innodb_buffer_pool_size innodb存储引擎的缓存池大小，可以设到内存的80%，尽量设大

innodb_file_per_table=on 一个表对应一个ibd文件

配置取决于服务器配置

10、mysql 压测工具

mysqlslap，mysql自带的

模拟并发测试、多轮测试、存储引擎测试

P等 操作

DML：（data manipulation language）数据操纵语言 ； SELECT、UPDATE、INSERT、DELETE 等

DCL：（Data Control Language）数据库控制语言 ； 是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句

3、大页码，limit offset， limit

尽量通过条件过滤数据而不是通过offset跳过已经查询到的数据

4、不用select * 尽量选自己需要的字段

问题不大，主要是网络传输多数据的延时。

5、 单表和多表查询

尽量使用单表代替多表进行联合查询，单表查询计算的压力在应用层面，多表查询压力在数据库层面。多表查询可能会增加表的被锁定时间，降低了程序的并发性能。

单表查询可能就增加了编码层面程序的复杂度，使用ORM模型。

6、count(*)

对于myisam有记录总量没问题，innodb需要自己完成常规操作

count(id) 统计id不为null的记录数，count(1)与count(*)类似

7、慢查询日志

执行时间超过指定时间的sql记录

开启日志->设置临界时间->读取日志内容->优化sql

show variables like 'slow_query_log';
set gloable slow_query_log=on
set long_query_time=1

8、profile信息统计

统计sql执行时间，详细记录sql的执行时间

show variables like 'profiling';
set profiling=on;
show profiles;
show profile for query query_id;

[外链图片转存中…(img-VjRlJHgz-1595171095153)]

9、典型服务器配置

max_connections 最大连接数

table_open_cache 表句柄缓存

key_buffer_size 索引缓冲大小–>从硬盘中读取的索引缓存到内存的大小

innodb_buffer_pool_size innodb存储引擎的缓存池大小，可以设到内存的80%，尽量设大

innodb_file_per_table=on 一个表对应一个ibd文件

配置取决于服务器配置

10、mysql 压测工具

mysqlslap，mysql自带的

模拟并发测试、多轮测试、存储引擎测试