MySQL 高级

环境构建

Linux 下 Docker 初步配置

1. 卸载旧版本
sudo yum remove docker \
                  docker-client \
                  docker-client-latest \
                  docker-common \
                  docker-latest \
                  docker-latest-logrotate \
                  docker-logrotate \
                  docker-engine
2. 使用Docker仓库进行安装
sudo yum install -y yum-utils \
  device-mapper-persistent-data \
  lvm2
3. 配置阿里云镜像地址
sudo yum-config-manager \
    --add-repo \
    http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
// 另附官方地址
sudo yum-config-manager \
    --add-repo \
    https://download.docker.com/linux/centos/docker-ce.repo
4. 下载安装
sudo yum install docker-ce docker-ce-cli containerd.io
5. 启动服务 + 开机自启
sudo systemctl start docker
sudo systemctl enable docker
6. 配置docker镜像源
阿里云 -> 容器镜像服务 -> 执行CentOs命令

7. mysql 配置 // 注意这是mysql80
！！一定要本机先创建配置文件！！
vim /mydata/mysql/my.cnf
--------------------------
[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8
--------------------------
docker run --restart=always --privileged=true -p 3306:3306 --name mysql -v /mydata/mysql/log:/var/log/mysql -v /mydata/mysql/data:/var/lib/mysql -v /mydata/mysql/my.cnf:/etc/mysql/my.cnf -v /mydata/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=666666 -d mysql
  
    
8. mysql 密码设置、远程连接设置和配置文件（字符编码）(上面设置的密码可能不生效，密码或为空)
// 允许外部访问
use mysql; update user set host = '%' where user = 'root'; FLUSH PRIVILEGES;
// 先进入mysql
docker exec -it mysql /bin/bash
// 登录
mysql -uroot -p666666 // 
// 更改密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '666666';

9. docker 配置 redis
// 3步
mkdir -p /mydata/redis/conf
touch /mydata/redis/conf/redis.conf
docker run -p 6379:6379 --name redis -v /mydata/redis/data:/data -v /mydata/redis/conf/redis.conf:/etc/redis/redis.conf -d redis redis-server /etc/redis/redis.conf
// 测试
docker exec -it redis redis-cli
// 持久化配置
vi /mydata/redis/conf/redis.conf
appendonly yes
docker rest
10. 设置卡机自启 
docker update --restart=always mysql
docker update --restart=always redis

目录对应关系

路径	解释
/var/lib/mysql/	mysql 数据库文件的存放路径
/usr/bin/	mysql 相关命令存放路径
/etc/mysql/	mysql 配置文件路径

MySQL体系结构

存储引擎

概述

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可以被称之为表类型

Oracle，SQL Server等数据库只有一种存储引擎，MySQL提供了插件式的存储引擎架构，所以MySQL可以自由选择。查看支持的数据库引擎：show engines，建表时在末尾添加engine = 引擎名指定使用的引擎

各种存储引擎特性

存储引擎简述

InnoDB：如果对事务完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包含很多的更新、删除操作，那么InnoDB是比较合适的选择。InnoDB除了有效的降低由于删除和更新导致的锁定，还可以确保事物的完整提交和回滚，对于类似于计费系统这些要求数据准确性较高的，InnoDB是最合适的选择。
MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事物的完整性、并发性要求不是很高，那么MyISAM非常合适。
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY通常用于更新不太频繁的小表，用以快速得到访问结果。
MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率，这对于存储诸如数据仓储等VLDB环境十分合适。

InnoDB

InnoDb 逻辑存储结构图

Innodb页结构单体图

InnoDB数据页由以下7部分组成，如图所示：

SQL优化步骤

查看SQL执行频率

# 查看当前增删改查次数
show [global] status like 'Com_______' 
# 查看当前增删改查次数『针对InnoDB』
show global status like 'Innodb_rows_%'

定位低效SQL

慢查询日志

# 注意开启慢查询日志会影响性能
slow_query_log=1
slow_query_log_file="LENVON-slow.log"
long_query_time=10

mysqldumpslow -s r -t 10 (日志文件位置)  -- 得到返回记录集最多的10个SQL
mysqldumpslow -s c -t 10 (日志文件位置)  -- 得到访问次数最多的10个SQL
mysqldumpslow -s t -t 10 -g "left join" (日志文件位置)  -- 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s r -t 10 (日志文件位置) | more  -- 另外建议在使用这些命令时结合 | 和 more 使用，否则条数太多

show processlist：慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用这个命令查看当前MySQL在进行的线程、是否锁表等，可以实时查看SQL执行情况，同时对一些锁表操作进行优化
explain：分析执行计划，详细见下索引章节
show profile：mysql 提供的可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

show profiles  -- 查看历史执行SQL的时间
show profile [cpu,block io] for query n   -- 诊断SQL，n对应上一步的编号
-- 出现以下语句代表有问题
converting HEAP to MyISAM  -- 查询结果太大，内存不够用了往磁盘上搬
creating tmp table  -- 创建临时表
copying to tmp table on disk -- 把内存中临时表复制到磁盘
locked

全局查询日志

开启会影响性能！

# 开启
general_log=1
# 记录日志文件的路径 /path 不代表实际路径
general_log_file=/path 
# 输出格式
log_output=FILE

-- SQL设置开启
set global general_log = 1;
set global log_output = 'TABLE';
-- 开启后，编写的SQL语句，将会记录到MySQL库里的general_log表中，可以使用以下命令查看
select * from mysql.general_log;

索引 Index

索引是什么

官方定义：帮助MySQl高效获取数据的数据结构
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是儿叉的）结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引等

优缺点

提高数据检索效率，降低数据库IO成本；
降低数据排序的成本，降低了CPU的消耗。
索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，但会降低更新表的速度
索引知识提高效率需要和实际情况反复磨合

索引结构

BTREE 索引：最常见的索引类型，大部分索引都支持B树索引
HASH 索引：只有Memory引擎支持，使用场景简单
R-tree 索引（空间索引）：MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，使用较少
Full-text（全文检索）：MyISAM的一个特殊索引类型，主要用于全文检索，InnoDB从MySQL5.6版本开始支持全文索引

我们平常所说的索引，如没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引，统称为索引

B-Tree

B-Tree又叫做多路平衡搜索树，一颗m叉的BTree特性如下：

树中每一节点最多包含m个孩子
除根节点与叶子节点外，每个节点至少有[cell(m/2)]个孩子
若根节点不是叶子结点，则至少有两个孩子
所有叶子结点都在同一层
每个非叶子节点都由n个key与n+1个指针组成，其中[cell(m/2)-1] <= n <= m-1

以5叉BTree为例，key的数量：公式推导[cell(m/2)-1]<=n<=m-1。所以 2<=n<=4，当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 CNGAHEKQMFWLTZDPRXYS 数据为例：

B+Tree

n 叉的B+Tree最多含有n个key，而BTree最多含有n-1个key
B+Tree的叶子节点保存所有的key信息，依key大小顺序排列
所有的非叶子节点都可以看做是key的索引部分

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子，所以B+Tree查询效率比较稳定

MySQL中的B+Tree

MySQL对B+Tree进行了优化，在原有的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree

索引分类

单列索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列

基本语法

-- 方式之一
-- 创建
CREATE  [UNIQUE|FULLTEXT|SPATIAL|PRIMARY] INDEX indexName [USING index_type]
ON tableName(columnName(length),...);
-- 删除
DROP INDEX indexName ON mytable;
-- 查看
SHOW INDEX FROM tableName

-- 方式之二
-- 创建
Alter tableName ADD [PRIMARY|UNIQUE|FULLTEXT|SPATIAL] INDEX indexName ON (columnName(length))

Explain

id

表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高

select_type

SIMPLE：简单的select查询，查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何的子部分，最外层查询则被标记为
SUBQUERY：在SELECT或WHERE列表中包含了子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT：从UNION表获取结果的SELECT

table

查询的表名

type

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

null：不访问任何表，直接取值
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，将遍历全表以找到匹配的行

possible_keys

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

key

实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引（查询已建立索引的字段），则该索引仅出现在key列表中

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好 key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

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

rows

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

extra

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
- 一般出现在 where 与 group by 后面字段不一致的情况下，如：explain select status from tb_item where title in('3','2') group by status;
Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率很高。如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明通过索引来读取数据而非执行查找动作
- using index condition：会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；
Using where：使用了 where 过滤
Using join buffer：使用了连接缓存
Impossible where：where 子句的值总是false，不能用来获取任何元组
Selsect tables optimized away：在没有GROUPBY子句的情况下使用，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值得动作

避免索引失效

计算、函数、类型转换、左模糊、OR没有被索引全包、违反最左前缀法则、is not null、不等于

1.最左前缀法则

如果索引了多列，where 查询条件（ = ）应该按照索引的顺序查询，可以缺少后面的列或添加没有索引的列，不能跳过前面的列。

order by 配合 where 时尽量不要跨过中间的列，会导致 filesort

2.范围后面的索引失效（自己是生效的）

范围查询：>、<、>=、<=、!=、<>

3.索引列上使用运算操作索引失效

4.字符串类型必须加单引号

MySQL会类型为varchar的没加引号的数字进行隐式类型转换，导致索引失效

5.尽量使用覆盖索引，避免 select *

这种情况下，using index ; using where 比 using index condition 更快

6.对于 or 两边必须使用两个不同的索引，或是其中一边的索引对另一边有覆盖，否则索引失效

UNION替换 or 也是一种解决方案，可以解决 or 两边使用的索引是同一个复合索引且没有覆盖关系的情况

7.like 百分号写右边，不得不的情况应使用覆盖索引避免全表扫描

针对 where 后面只有一个 like 的情况准确，这个字段应被索引

8.如果MySQL评估使用索引比全表扫描更慢，则不会使用索引

这个跟存储的数据有关！查询条件对应数据较多不走索引，查询条件对应数据较少走索引

9.is [not] null 可能会导致索引失效

仍然跟存储的数据有关，null值多 is not null 走索引，null值少 is null 走索引

10.in 索引可能失效，not in 索引失效

11.单列索引 vs 复合索引

优化案例

单表案例

select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;   -- 需求SQL 

create index idx_article_ccv on article(category_id,comments,views);   -- 错误建法
drop index idx_article_ccv on article;
 
create index idx_article_cv on article(category_id,views);   -- 正确建法

双表案例

select * from class left join book on class.card = book.card;  -- 左连接SQL
alter table book add index Y (card);  -- 左连接索引建右表

select * from class right join book on class.card = book.card;  -- 右连接SQL
alter table book add index Y (card);  -- 右连接索引建左表

三表

接以上两表

SELECT * FROM class
	LEFT JOIN book ON class.card = book.card
	LEFT JOIN phone ON book.card = phone.card
	
alter table book add index Y (card);  
alter table phone add index Z (card);  

-- 索引最好建立在经常查询的字段上
-- 尽量减少Join语句中的NestedLoop的循环次数 “永远用小节果集驱动大的结果集”
-- 优先优化NestedLoop的内层循环
-- 保证Join语句中被驱动表上Join条件字段已经被索引
-- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer

全值匹配我最爱，最左前缀要遵守； 带头大哥不能死，中间兄弟不能断； 索引列上少计算，范围之后全失效； Like百分写最右，覆盖索引不写星； 不等空值还有or，索引失效要少用； VAR引号不可丢，SQL高级也不难！

几种优化案例

Order By 索引优化

尽量减少排序字段，多字段排序字段尽量都是升序或是降序
order by 配合 where 组成最左前缀法则，单独 order 则会失效！
- where -> order by / select

排序字段使用索引的情况下，select 字段尽量都使用覆盖索引，否则导致filesort

Filesort 的优化

show variables like 'max_length_for_sort_data'
show variables like 'sort_buffer_size'

Group By 索引优化

group by 实质是先排序后分组，group by 字段涉及索引就会生效！(sum、avg字段是索引的情况下)
不想排序只分组：order by null
where 高于 having ，能写在 where 限定的条件就不要去 having 限定

嵌套查询优化

很多情况下，子查询效率不如JOIN

条件OR优化

对于 or 两边必须使用两个不同的索引，或是其中一边的索引对另一边有覆盖，否则索引失效

UNION替换 or 也是一种解决方案，可以解决 or 两边使用的索引是同一个复合索引且没有覆盖关系的情况

分页查询优化

这样一条sql：limit 200000,10 对MySQL性能浪费过高

优化思路一

在索引上完成排序操作，最后根据主键关联，例如

select * from tb_item t,
(select id from tb_item order by id limit 200000,10) a 
where t.id = a.id

优化思路二

该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询，这种方案最快

select * form tb_item where id > 2000000 limit 10

使用索引提示

SQL提示，是优化数据库的一个重要手段，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

# 指定使用的索引，非强制性
select * from 表名 use index(索引名)
# 忽略一个或多个索引
select * from 表名 ignore index(索引1,索引2...)
# 强制使用某个索引
select * from 表名 force index(索引名)

索引条件下推

ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率。

如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估where后面的条件是否保留行；
启用ICP后，如果部分where条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分where条件放到存储引擎筛选，然后，存储引擎通过使用索引条目来筛选数据，并且只有满足这一条件时才从表中读取行。

一文读懂什么是MySQL索引下推（ICP） - 简书 (jianshu.com)

五分钟搞懂MySQL索引下推 - 掘金 (juejin.cn)

参数优化

基本思路

1.使用连接池

对于数据库来说，建立连接的代价是比较昂贵的，因此有必要使用数据库连接池

2.减少对MySQL的访问

避免对数据进行重复检索

在编写应用代码时需要能够清理对数据库的访问逻辑。能够一次性连接获取到结果的，就不用两次连接，这样可以大大减少重复请求

增加cache层

缓存有很多实现方式，如MyBatis框架所提供的一级/二级缓存、redis作为缓存等等

3.负载均衡

通过MySQL的主从复制，实现读写分离

增删改操作走主节点，查询操作走从节点，可以降低单台服务器的读写压力

采用分布式数据库架构

分布式数据库架构适合大数据量、负载高的情况，它有良好的扩展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率。

查询缓存

开启MySQL的查询缓存，当执行完相同的SQL语句时，服务器就会从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。注意MySQL8.0已被废弃

参数查看

查看当前数据库是否支持缓存

show variables like 'have_query_cache'

查看当前MySQL是否开启了查询缓存

show variables like 'query_cache_type'

查看查询缓存大小（单位字节）

show variables like 'query_cache_size'

查看查询缓存占用大小

show status like 'Qcache%'

查看数据库查询、插入情况

show global status like "Com_______";

开启查询缓存

MySQL的查询缓存默认是关闭的，需要手动配置 query cache type 来开启查询缓存。query_cache_type该参数的值可能有三个：

在 /usr/my.cnf (linux)中配置以下参数，配置完毕重启即可生效

# 开启mysql的查询缓存
query_cache_type = 1

SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项：

SQL_CACHE：如果查询结果是可缓存的，并且query_cache_type 系统变量的值为ON或DEMAND，则缓存查询结果

SQL_NO_CACHE：服务器不使用查询缓存。它既不检查缓存，也不检查结果是否缓存，也不缓存查询结果

select SQL_CACHE id, name from customer;
select SQL_NO_CACHE id , name from customer;

查询缓存失效的情况

SQL语句不一致
查询语句中有一些不确定，如now(),current_date()...
不使用任何查询语句
查询MySQL, information_schema 或 performance_schema 数据库中的表
在存储的函数，触发器或事件的主体内执行的查询
如果表更改，则使用该表的所有高速缓存查询都将被删除

内存管理及优化

优化原则

将尽量多的内存分配给MySQL做缓存
MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留给更多的内存给操作系统做IO缓存
排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽

MyISAM内存优化

MyISAM存储引擎使用 key_buffer 缓存索引块，加速MyISAM索引的读写速度。对于MyISAM表的数据块，MySQL没有特别的缓存机制，完全依赖与操作系统的IO缓存

key_buffer_size

决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率，可以在MySQL参数文件中设置key_buffer_size的值

对于一般的MyISAM，建议至少将1/4的可用内存分配给它

key_buffer_size = 512M

read_buffer_size

如果需要经常顺序扫描MyISAM表，可以通过增大read_buffer_size的值来改善性能，但需要注意的是read_buffer_size是每个session独占的，如果设置过大，会导致内存浪费

read_rnd_buffer_size

对于需要做排序的MyISAM表的查询，如带有order by子句的SQL，适当增加read_rnd_buffer_size的值，可以改善此类SQL性能，但需要注意的是read_rnd_size仍然是每个session独占的

InnoDB内存优化

InnodB用一块内存区做IO缓存池，该缓存池不仅用来缓存索引块，也用来缓存数据块

innodb_buffer_pool_size

该变量决定了InnoDB存储引擎表数据或索引数据的最大缓存区大小。在保证操作系统及其他应用程序有足够的内存可用的情况下，innodb_buffer_pool_size值越大，缓存命中率越高，访问InnoDB表需要的磁盘IO就越少，性能也就越高

innodb_buffer_pool_size = 512M

innodb_log_buffer_size

决定了重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作

innodb_log_buffer_size = 10M

并发参数调整

从实现上来说，MySQL Server是多线程结构的，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。

max_connections

允许按连接到MySQL数据库的最大数量，默认151。如果状态变量 connectino_errors_max_connections 不为零，且一直增长，则说明并发连接数过多，可以考虑增大 max_conncetions 值

MySQL最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux平台下，性能好的服务器，支持500-1000个连接是应该的。

back_log

控制MySQL监听TCP端口时设置的挤压请求栈大小。如果MySQL的连接数达到max_connections时，新来的请求将会被堆在栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接，会报错。5.6.6之前的默认值是50，之后默认为50+（max_conncetions / 5），最大不超过900

table_open_cache

控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开1个表缓存。该参数的值应该根据设置的最大连接数以及每个连接执行关联查询中涉及的表的最大数量来决定：max_connections * N

thread_cache_size

为了加快数据库的速度，MySQL会缓存一定数量的客户服务器线程以备重用

innodb_lock_wait_timeout

该参数是用来设置InnoDB事务等待行锁的时间，默认50ms。对于需要快速反馈的业务系统来说，可以将该参数设置小，避免事务长时间挂起；对于后台运行的批处理程序来说，可以将该参数调大，避免发生大的回滚操作。

实践优化

1.查看性能参数

一些常用的性能参数如下：

• Connections：连接MySQL服务器的次数。

• Uptime：MySQL服务器的上线时间。

• Slow_queries：慢查询的次数。

• Innodb_rows_read：Select查询返回的行数

• Innodb_rows_inserted：执行INSERT操作插入的行数

• Innodb_rows_updated：执行UPDATE操作更新的行数

• Innodb_rows_deleted：执行DELETE操作删除的行数 • Com_select：查询操作的次数。

• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。

• Com_update：更新操作的次数。

• Com_delete：删除操作的次数。

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

2.explain

SHOW STATUS LIKE 'last_query_cost';  # explain上一条sql
explain ...

3.慢查询日志

# 临时开启（重启失效）
set global slow_query_log='ON';
set global long_query_time=1;   # 1s为慢查询sql
show variables like 'slow_query_log_file';    # 查看日志文件位置

# 永久开启
[mysqld]
slow_query_log = ON
long_query_time = 1
# slow_query_log_file = /var/lib/mysql/tmp_slow.log     //可选


SHOW GLOBAL STATUS LIKE '%Slow_queries%';  # 查看有多少条慢sql

# 个人习惯  cat /opt/mysql/data/2397e8f16638-slow.log

4.查看 SQL 执行成本：SHOW PROFILE

# 临时开启（重启失效） 注意这个优化手段需要在会话窗口手动执行一遍sql，才会有记录
set profiling = 'ON';
show variables like 'profiling';  # 校验
show profiles; 
show profile cpu,block io for query 2;  # 2是query_id

4.SHOW WARNINGS

# 仍然需要在会话中重现sql
show warnings

5.trace

SET optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;
# 这个工具暂时没有使用过
# 仍然需要在会话中重现sql
select * from information_schema.optimizer_trace;

6.MySQL监控分析视图-sys schema

# 索引相关
#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema='mall_pms'; # 自定义dbname

# 表相关
# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='mall_pms';

# 语句相关
#1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL 
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

# IO相关
#1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_read limit 10;

# Innodb 相关
#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

MySQL 锁机制

查看锁

# 查看所有表的锁定情况
show open tables 
# 查看表的锁定和等待次数
show status like 'Table_locks%';

锁分类

从对数据操作的粒度分类：

表级锁：操作时，会锁定整个表
- 读锁（共享锁）
- 写锁（独占锁）
- 元数据锁（MDL）
- 意向锁
行级锁：操作时，会锁定当前操作行
- 行锁（Record Lock）
- 间隙锁（Gap Lock）
- 临键锁（Next-Key Lock）
- 插入意向锁
页锁

从对数据操作的类型分：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响
写锁（排它锁）：当前操作没有完成，会阻塞其它锁

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

表级锁

-- 查看当前锁
show open tables [where in_use>0]; 
-- 加锁示例
lock table tableName read|write;  
-- 解锁
unlock tables;

读锁（共享锁）

loca tables tableName read

一张表加了读锁则任何客户端都不能有修改操作，自己修改会报错，别的修改会阻塞
不影响读操作，可以同时被别的客户端加锁
加锁会话不能再去访问别的表

写锁（独占锁）

loca tables tableName write

一张表加了读锁则自己可以读写，阻塞别的会话的读写
只有拿到这张表的锁才能对其进行操作
加锁会话不能再去访问别的表

简而言之，读锁会阻塞读写，但不会阻塞读，而写锁会阻塞读和写

此外，MyISAM的读写调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。

元数据锁（MDL）

主要是为了防止表结构修改与增删改查的冲突（DDL语句与DML语句的冲突）

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向锁

如果我们给某一行加上了锁，数据库会自动给更大一级的空间加锁，主要是为了解决行锁与表锁的冲突问题（不必一行一行扫描是否有锁）

意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排它锁（write）互斥
意向排它锁（IX）：与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不互斥

行级锁

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

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

行锁（Record Lock、记录锁）

select ... lock in share mode;
select ... for share  # 读锁  8.0新增  nowait/skip locked
select ... for update  # 写锁

锁定单个行记录的锁，防止其他事物对此进行update和delete。在RC、RR隔离级别下都支持。

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事物获得相同数据集的共享锁和排它锁。

注意：InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么将升级为表锁。

间隙锁（Gap Lock）

show variables like 'innodb_locks_unsafe_for_binlog';   # 默认OFF，启用

锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。

索引上的等值查询（唯一索引），给不存在的记录加锁时。优化为间隙锁；
索引上的等值查询（普通索引），向右遍历第一个值不满足查询需求时，next-key lock退化为间隙锁；
索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止。

插入意向锁

InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB就把这种类型的锁命名为 Insert Intention Locks。插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

页锁

	页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。**页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。**每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。当某个层级的锁数量超过了这个层级的阈值时，就会进行 锁升级 。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

事务

事务是由一组SQL组成的逻辑处理单元，事务具有以下四个属性，通常称为事务的ACID属性

并发事务处理所带来的问题

事务隔离级别

读数据—致性及允许的并发副作用（隔离级别）	读数据一致性	脏读	不可重复读	幻读
未提交读（read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读（read committed）	语句级	否	是	是
可重复读（repeatable read）	事务级	否	否	是
可序列化（serializable）	最高级别，事务级	否	否	否

查看当前数据库的事务隔离级别：select @@transaction_isolation; ;

设置事务隔离级别：set global transaction isolation level REPEATABLE READ;

RedoLog

刷盘策略 innodb_flush_log_at_trx_commit

设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步）
设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（默认值）
设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

UndoLog

两个作用：

提供回滚(保证事务的原子性)
MVCC(多版本并发控制)

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment。

MVCC ※※

MVCC 全称 Multi-Version Concurrency Control，多版本并发控制。用以无锁解决读写冲突问题。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode、select ... for update、update、insert、delete 都是一种当前读

快照读

简单的select（不加锁）就是快照读，读取的是记录的可见版本，有可能是历史数据，不加锁，是非阻塞读

Read Committed：每次select，都生成一个快照读；
Repeatable Read：开启事务后第一个select语句才是快照读的地方；
Serializable：快照读退化为当前读。

隐藏字段

undo log

回滚日志，在insert、update、delete时产生的便于数据回滚的日志。当insert时，产生的undolog日志只在回滚时需要，在事务提交后，即可删除，而update、delete时产生的回滚日志，不仅在回滚时需要，在快照读时也需要，不会立即被删除。

undo log 版本链

readview

ReadView（读视图）是快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id

不同的隔离级别，生成ReadView的时机不同：

READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

常用原生命令及日志

常用工具

1.mysql

mysql [options]  [database]

options

参数：
	-u	# 账号
	-p  # 密码
	-h	# 服务器IP或域名
	-p	# 端口号

执行选项

-e  # 执行SQL语句而不必进入其命令行
# 例
mysql -uroot -p123 db01 -e "select * from emp"

这样的方式一般用于批处理脚本之中

2.mysqladmin

mysqladmin 是一个执行管理操作的客户端程序，它可以用来检查服务器的配置和当前状态、创建并删除数据库等等。使用 --help 查看支持的命令

例如：mysqladmin -uxxx -pxxx drop 'db01'

3.mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以要想查看这些日志，就会用到该命令

语法：

mysqlbinlog [options] log-files1 log-files2 ...
options:
		-d   --database=name # 指定数据库
		-o  --offset=n  # 忽略掉日志中的前 n 行命令
		-r  --result-file=name # 将输出的文本格式日志输出到指定文件
		-s  --short-form # 显示简单格式
		--start-datetime=date1 --stop-datetime=date2 # 指定日期间隔内的所有日志
		--start-opsition=pos1 --stop-position=pos2 #指定位置间隔内的所有日志

4.mysqldump

用于备份数据库或在不同数据库之间进行数据迁移，备份内容包含创建表，插入表等SQL语句

语法：

mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1[db2 db3 ...]
mysqldump [options] --all-database/-A
options:
	-u  -p  -h  -p ...
	--add-drop-database  # 在每个数据库创建语句前加上 drop if exists 语句
	--add-drop-table  # 在每个表的创建语句上加上 drop if exists（默认开启）；想要不开启：（--skip-add-drop-table）
	-n  --no-create-db  # 不包含库的创建语句
	-t  --no-create-info   # 不包含表的创建语句
	-d  --no-data 	# 不包含数据
	-T  --tab=name  # 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件。数据文件，相当于select into outfile

示例：
mysqldump -uroot -pxxx db1 tb_book > tb_book.sql # 导出表
mysqldump -uroot -pxxx db1  > tb_book.sql # 导出库
mysqldump -uroot -pxxx -T /tmp db1 tb_book # 分别导出结构和数据

5.mysqlimport/source

用来导入mysqldump -T后生成的文件

语法：

mysqlimport [options] db_name textfile1 [textfile2...]
# 示例：
mysqlimport -uroot -p123 db1 /tmp/tb_book.txt   导入数据

# 如果需要导入.sql文件(不一定有数据)
source /root/tb_book.sql

6.mysqlshow

客户端对象查找工具，用来查找存在哪些数据库、数据库中的表、表中的列或者索引

语法：

mysqlshow [options] [db_name [col_name]]
options:
	--count   # 显示数据库及表的统计信息（数据库、表均可不指定）
	-i    		# 显示指定数据库或者指定表的状态信息

# 示例
mysqlshow -uroot -p123 --count     # 
mysqlshow -uroot -p123 test --count
mysqlshow -uroot -p123 test book --count

日志

在MySQL中，有四种不同的日志，分别是错误日志、二进制日志（BINLOG日志）、查询日志和慢查询日志

错误日志

记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生了任何严重错误时的相关信息。当数据库出现严重错误时，可以优先查看此日志。

该日志默认开启，默认存放目录为（var/lib/mysql），默认日志文件名为 hostname.err（hostname-主机名）

查看日志位置指令：

show variables like 'log_error%'
# mac 结果
# /usr/local/mysql/data/mysqld.local.err

二进制日志

记录了所有的DDL（数据定义语言）和DML（数据操纵语言），但不包括查询语句，此日志对于故障时数据恢复起着极其重要的作用，MySQL主从复制，就是通过该binlog实现。

默认没有开启，需要在配置文件中开启：

# 开启并设置日志文件名为 mysqlbin.xxx
log_bin=mysqlbin	
# 日志格式
binlog_format=STATEMENT

日志格式

STATEMENT

该日志格式在日志文件中记录的都是SQL语句（statement），对每一条数据进行修改的SQL都会记录在日志文件中，通过MySQL提供的mysqlbinlog工具，可以清晰地看到每条语句的文本，主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。

MIXED

默认格式，即以上两者的混合。默认

查看日志内容

sudo mysqlbinlog mysqlbin.000001

删除

# 全部删除
reset master;
# 删除 xxxxxx 编号之前的所有日志 
purge master logs to 'mysqlbin.xxxxxx'
# 删除以下具体时间之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mm:ss'
# 设置参数 expire_logs_days=# 过期时间
expire_logs_days=3

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询语句。

默认关闭，如需开启：

# 开启，可选值：0 or 1
general_log=1
# 设置日志文件名，默认 host_name.log
general_log_file=file_name

慢查询日志

记录了所有执行时间超过long_query_time设置值并且扫描记录不小于min_examined_row_limit的所有的SQL语句的日志。

默认关闭，如需开启：

# 1开启，0关闭
slow_query_log=1
# 指定慢查询日志文件名
slow_query_log_file=slow_query.log
# 时间阈值，默认10s
long_query_time=10
# 扫描行阈值，默认0
min_examined_row_limit=n

主从复制

架构图

环境搭建

一主一从常见配置，Windows主，linux从

1.主机修改my.ini配置文件

[mysqld]
# [必须]集群唯一ID
server-id=1
# [必须]启用二进制日志
log-bin=自己本地的路径/mysqlbin
# log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin

# [可选]启用错误日志
log-err=自己本地的路径/mysqlerr
# log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
# [可选]根目录
basedir=“自己本地路径”
# basedir=“D:/devSoft/MySQLServer5.5/”
# [可选]临时目录
tmpdir=“自己本地路径”
# tmpdir=“D:/devSoft/MySQLServer5.5/”
# [可选]数据目录
datadir=“自己本地路径/Data/”
# datadir=“D:/devSoft/MySQLServer5.5/Data/”

# [可选]主机读写方式
read-only=0
# [可选]设置不要复制的数据库
binlog-ignore-db=mysql
# [可选]设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字

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

GRANT REPLICATION SLAVE ON *.* TO ‘zhangsan’@‘从机器数据库IP’ IDENTIFIED BY ‘666666’;
flush privileges; 	-- 刷新
-- 查询master的状态,记录下File和Position的值
show master status;
-- 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

3.【如必要】配置文件，请主机+从机都重启后台mysql服务，关闭防火墙

# windows手动服务重启
service mysql restart
#关闭虚拟机linux防火墙 
service iptables stop

4.从机修改my.cnf配置文件

# server-id 避免重复
server-id=2
# 指定logbin日志
log-bin=/var/lib/mysql/data/mysqlbin

5.从机上配置需要复制的主机

CHANGE MASTER TO 
MASTER_HOST=’主机IP’, 
MASTER_USER=‘主机用户名’, 
MASTER_PASSWORD=’666666’, 
MASTER_LOG_FILE='File名字’,MASTER_LOG_POS=Position数字';

start slave;

show slave status\G

-- 下面两个参数都是Yes，则说明主从配置成功!
-- Slave_IO_Running：Yes
-- Slave_SQL_Running：Yes

停止从服务复制功能：stop slave ，停止之后注意主机Position数字会变化

其它问题

主从延迟： MySQL 主从同步延迟的原因及解决办法_BrickCarrier的博客-CSDN博客

项目优化案例

分页查询优化

优化count

创建一张表用来记录日志表的总数据量：

create table log_counter(
  longcount bigint not null
)engine = innodb default charset = utf8;

每次插入数据后，更新该表。在进行分页查询时，获取总记录数，从该表中查询即可。

这种方式的不足在于条件查询无法使用

优化limit

在进行分页时，一般通过建立索引，能够比较好的提高性能。但是在这种场景下“limit 100000,10”是一个问题，因此，可以采用以下方式解决：

select field1... from tableName a,
(select id from tableName order by id limit 300000,id) b 
where a.id = b.id;

索引优化

针对查询条件字段建立复合索引（阶梯式建立，防止索引失效）
如果有排序需求，还需要对排序字段建立适当的索引

读写分离

核心问题在于请求的分流。对于SSM项目，可以通过SpringAOP框架拦截请求实现该逻辑。

SpringAOP实现

properties 配置读写数据源
datasource.xml 配置读写数据源和 数据源选择bean（该bean[ChooseDataSource]需要继承AbstractRoutingDatasource）

实现DataSourceHandler类逻辑

实现ChooseDataSource类逻辑

实现DataSourceAspect类逻辑

原理：www.bilibili.com/video/BV1UQ…

应用优化

缓存

可以在业务系统中使用redis来做缓存，缓存一些基础性的数据，来降低关系型数据库的压力，提高访问效率

全文检索

如果业务系统中的数据量比较大（千万级别）。此时，如果再对数据库进行查询，特别是分页查询，速度将不可避免的变慢（首先需要count），为了提高访问效率，此时，可以考虑加入 Solr 或者 ElasticSearch 全文检索服务，来提高访问效率

非关系型数据库

也可以考虑将非核心（重要）数据，存在MongoDB中，这样可以提高插入及查询效率

附

大量数据SQL测试数据

优化导入（针对InnoDB）：

主键顺序插入
关闭唯一性校验：set unique_checkes=0，用完记得开
手动提交事务：set autocommit=0

-- 建表
create table tb_item
(
    id         int auto_increment
        primary key,
    title      varchar(100)   not null,
    price      decimal(20, 2) not null,
    num        int            not null,
    categoryid bigint         not null,
    status     varchar(1)     null,
    sellerid   varchar(50)    null,
    createtime datetime       null,
    updatetime datetime       null
);

-- 存储过程
create
    definer = root@localhost procedure insert_tb_item(IN num int)
begin
while num <= 10000000 do
insert into tb_item values(num,concat('货物',num,'号'),round(RAND() * 100000,2),FLOOR(RAND() * 100000),FLOOR(RAND() * 10),'1','5435343235','2019-04-20 22:37:15','2019-04-20 22:37:15');
set num = num + 1;
end while;
end;

MySQL高级篇学习笔记

MySQL 高级

环境构建

Linux 下 Docker 初步配置

目录对应关系

MySQL体系结构

存储引擎

概述

各种存储引擎特性

存储引擎简述

InnoDB

InnoDb 逻辑存储结构图

Innodb页结构单体图

SQL优化步骤

查看SQL执行频率

定位低效SQL

全局查询日志

索引 Index

索引是什么

优缺点

索引结构

B-Tree

B+Tree

MySQL中的B+Tree

索引分类

基本语法

Explain

id

select_type

table

type

possible_keys

key

key_len

ref

rows

extra

避免索引失效

1.最左前缀法则

2.范围后面的索引失效（自己是生效的）

3.索引列上使用运算操作索引失效

4.字符串类型必须加单引号

5.尽量使用覆盖索引，避免 select *

6.对于 or 两边必须使用两个不同的索引，或是其中一边的索引对另一边有覆盖，否则索引失效

7.like 百分号写右边，不得不的情况应使用覆盖索引避免全表扫描

8.如果MySQL评估使用索引比全表扫描更慢，则不会使用索引

9.is [not] null 可能会导致索引失效

10.in 索引可能失效，not in 索引失效

11.单列索引 vs 复合索引

优化案例

几种优化案例

Order By 索引优化

Filesort 的优化

Group By 索引优化

嵌套查询优化

条件OR优化

分页查询优化

使用索引提示

索引条件下推

参数优化

基本思路

1.使用连接池

2.减少对MySQL的访问

3.负载均衡

查询缓存

参数查看

开启查询缓存

SELECT选项

查询缓存失效的情况

内存管理及优化

优化原则

MyISAM内存优化

InnoDB内存优化

并发参数调整

实践优化

1.查看性能参数

2.explain

3.慢查询日志

4.查看 SQL **执行成本：**SHOW PROFILE

4.SHOW WARNINGS

4.查看 SQL 执行成本：SHOW PROFILE