史上最详细的网站优化系列（一）mysql优化1
一、mysql优化概述 方针：
① 存储层：数据表”存储引擎”选取、字段类型选取、逆范式(3范式)二、存储引擎的选择 1、存储引擎介绍 熟悉的存储引擎：Myisam、innodb memory
② 设计层：索引、分区/分表、存储过程，sql语句的优化
③ 架构层：分布式部署(集群)(读写分离)，需要增加硬件
④ sql语句层：结果一样的情况下，要选择效率高、速度快、节省资源的sql语句执行
（1）什么是存储引擎？
数据表存储数据的一种格式。
数据存储在不同的格式里边，该格式体现的特性也是不一样的。例如innodb存储引擎的特性有支持事务、支持行级锁，mysiam支持的特性有压缩机制等。
MySQL中的数据是通过各种不同的技术(格式)存储在文件（或者内存）中的。技术和本身的特性就称为"存储引擎"。
（2）存储引擎的理解：
现实生活中，楼房、平房就是具体存储人的存储引擎，楼房、平房有自己独特的技术特性
例如楼房有楼梯、电梯、平房可以自己打井喝水等。
文章图片
（3）存储引擎所处的位置：
存储引擎，处于MySql服务器的最底层，直接存储数据，导致上层的操作，依赖于存储引擎的选择。
客户端-》网络连接层-》业务逻辑层（编译，优化，执行SQL）-》存储引擎层
文章图片
查看当前mysql支持的存储引擎列表：
show engines
打开cmd,输入mysql -uroot -p123456（u、p后面是你的mysql的用户名和密码，如不能进入mysql则配置下环境变量，如下
右击 计算机——属性——高级系统设置——高级->环境变量->系统变量
选择PATH，在其后面添加：你的mysql文件夹路径/bin (如:D:\phpStudy\MySQL\bin)
PATH=…; D:\phpStudy\MySQL\binn (注意为追加,不是覆盖所以放到最后前面以; 作为隔离)
）进入mysql控制台，输入上面命令
文章图片
（4）常用存储引擎：
① Myisam：表锁，全文索引
② Innodb：行(记录)锁，事务（回滚），外键
③ Memory：内存存储引擎，速度快、数据容易丢失（实用性太差几乎用不到，特别现在有了nosql的出现）
2、innodb存储引擎 在>=5.5 版本中默认的存储引擎，MySql推荐使用的存储引擎。提供事务，行级锁定，存储引擎。
事务安全型存储引擎，更加注重数据的完整性和安全性。
（1）存储格式：
innodb存储引擎 每个数据表有单独的“结构文件” *.frm
数据，索引集中存储，存储于同一个表空间文件中ibdata1。
ibdata1就是InnoDB表的共享存储空间，默认innodb所有表的数据都在一个ibdata1里。
创建innodb表后，存在文件如下：
create table t1(id int,name varchar(32)) engine innodb charset utf8;
先查看数据库
文章图片
打开数据库并创建表t1
文章图片
.frm表结构文件。
文章图片
innodb表空间文件：存储innodb的数据和索引。
ibdata1
文章图片
默认，所有的 innodb表的数据和索引在同一个表空间文件中，
通过配置可以达到每个innodb的表对应一个表空间文件。
showvariableslike 'innodb_file_per_table%';
文章图片
开启该配置：
setglobalinnodb_file_per_table=1;
文章图片
创建一个innodbd的表进行测试使用。
文章图片
查看表对应的文件自己独立的“数据/索引”文件
文章图片
系统配置参数innodb_file_per_table后期无论发生任何变化，t2都有自己独立的“数据/索引”文件。
注意：相比较之下，使用独占表空间的效率以及性能会更高一点。
注意：innodb数据表不能直接进行文件的复制/粘贴进行备份还原，可以使用如下指令：
备份数据库的指令
mysqldump-uroot -p密码数据库名字 > f:/文件名称.sql[备份]
需要到有mysqldump文件的目录操作
文章图片
备份数据库
文章图片
成功后备份文件php.sql会出现在e盘
文章图片
还原数据库
mysql -uroot-p密码 数据库
文章图片
备份文件php.sql还原到demo数据库
文章图片
（2）数据是按照主键顺序存储。
该innodb数据表，数据的写入顺序 与 存储的顺序不一致，需要按照主键的顺序把记录摆放到对应的位置上去，速度比Myisam的要稍慢。
create table t3(
id int primary key auto_increment,
name varchar(32) not null
)engine innodb charset utf8;
insert into t3 values(223,'刘备'),(12,'张飞'),(162,'张聊'),(1892,'网飞');
给innodb数据表写入4条记录信息(主键id值顺序不同)
插入时做排序工作，效率低。
文章图片
遇到中文字符串无法写入到数据表中问题，改变原有数据库的编码方式
set character_set_database=utf8;
set character_set_server=utf8;
set character_set_client=gbk;
set character_set_connection=gbk;
set character_set_results=gbk;
查看编码状态
show variables like'%char%';
文章图片
删除原数据表重新测试：
文章图片
（3）并发处理：
擅长处理并发的。
行级锁定(row-level locking)，实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性，也支持表级锁定，innodb根据操作选择。
锁机制：
当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能相互影响），通过加锁来处理。
操作方面：
读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是所有人都只可以读，只有释放锁之后才可以写。
写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征，只有锁表的客户可以操作（读写）这个表，其他客户读都不能读。
办公室开会锁上门。
锁定粒度（范围）
表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam和innodb都支持。
行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb支持
3、MyISAM存储引擎 <=5.5mysql默认的存储引擎。
（ISAM——索引顺序访问方法）是Indexed Sequential Access Method(索引顺序存取方法)的缩写
它是一种索引机制，用于高效访问文件中的数据行，擅长与处理高速读与写。
（1）存储方式：
数据，索引，结构分别存储于不同的文件中。
create table t4(id int,name varchar(32)) engine myisam charset utf8;
文章图片
mysiam存储引擎数据表，每个数据表都有三个文件*.frm（结构文件） *.MYD(数据文件) *.MYI(索引文件)
这三个文件支持物理复制、粘贴操作(直接备份还原)。
（2）数据的存储顺序为插入顺序。
create table t5(
id int primary key auto_increment,
name varchar(32) not null
)engine myisamcharset utf8;
insert into t5 values(2223,'刘备'),(12,'张飞'),(162,'张聊'),(1892,'网飞');
文章图片
（3）并发性
mysiam的并发性较比innodb要稍逊色（mysiam不支持事务）
因为数据表是“表锁”
myisam和innodb的取舍
如果表对事务的要求不高，同时是以查询和添加为主，我们考虑使用MyISAM存储引擎，比如bbs中的发帖表，回复表。
INNODB存储引擎：
对事务要求高，保存的数据都是重要数据，我们建议使用INNODB，比如订单表，库存表，商品表，账号表等等。
购买成功了库存 -1，
产生订单，操作表
4、memory 内存存储引擎，
特点：内部数据运行速度非常快，临时存储一些信息
缺点：服务器如果断电，重启，就会清空该存储引擎的全部数据
create table t6(id int,name varchar(32)) engine memory charset utf8;
insert into t5 values(2223,'刘备'),(12,'张飞'),(162,'张聊'),(1892,'网飞');
mysql服务，重启后，数据丢失（很不安全，在nosql出现后这个存储引擎就没有使用价值）。
文章图片
关闭数据库服务再重启，数据丢失
文章图片
三、查找需要优化语句 1、慢查询日志 是一种mysql提供的日志，记录所有执行时间超过某个时间界限的sql的语句。这个时间界限，我们可以指定。在mysql中默认没有开启慢查询，即使开启了，只会记录执行的sql语句超过10秒的语句。
方式一、临时启动慢查询记录日志
（1）{mysql程序所在的目录}>bin>mysqld.exe --safe-mode --slow-query-log
注意：先把mysql关闭后，再执行以上指令启动。
文章图片
进入cmd开始启动；
>bin>mysqld.exe--safe-mode--slow-query-log
文章图片
通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。
（2）在默认情况下，慢查询日志是存储到data目录下面的。根据配置文件里面的配置，找到data的存储路径。
文章图片
（3）可以通过命令查看慢查询日志的时间
show variables like 'long_query_time';
修改慢查询日志时间：
setlong_query_time=0.001;
文章图片
（4）测试查询：
查看慢查询日志
benchmark(count,expr)函数可以测试执行count次expr操作需要的时间。
select benchmark(10000,90000000*4);
文章图片
文章图片
慢查询日志可以记录查询数据表较慢的sql语句
（5）一般情况下，一个sql语句执行比较慢，原因是没有索引
添加索引之前,索引文件大小如下；
没有添加索引之前查询时间如下：
文章图片
添加索引之后：
altertableap_count add index(ip);
文章图片
另外在MyISAM存储引擎表中，添加索引后，*.MYI(索引文件)变大，但添加索引会明显的提高查询速度。。
方式二：通过修改配置文件，添加如下语句 在配置文件中指定：（1）开启（2）时间界限
log-slow-queries=‘e:/slow.log’通过修改配置文件是永远的开启慢查询日志
慢查询日志文件存储的路径，当前是把慢查询日志存储到d:盘下面，文件名为slow-log long_query_time=1
指定慢查询的时间，默认是10秒，我们自定义成1或0.05秒，也就是说当一个sql语句的执行速度超过1秒时，会把该语句添加到慢查询日志里面，
文章图片
2、精确记录查询时间 使用mysql提供profile机制完成。
profile记录每次执行的sql语句的具体时间，精确时间到小数点8位
（1）开启profile机制:
set profiling = 1;
文章图片
执行需要分析的sql语句（自动记录该sql的时间）
（2）查看记录sql语句的执行时间：
show profiles;
文章图片
注意：不需要分析时，最好将其关闭。
set profiling=0;
四、索引讲解 1、索引的基本介绍 利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。
索引的作用：是用于快速定位实际数据位置的一种机制。
例如：索引在mysql中，是独立于数据的一种特殊的数据结构。
字典的 检索
写字楼 导航
索引一定有顺序（排好序的快速查找结构），记录则不一定。
画图说明
文章图片
测试添加索引前后，对比执行时间。
2、索引的类型：
4种类型：无论任何类型，都是通过建立关键字与位置的对应的关系来实现的。
主键索引，唯一索引，普通索引，全文索引。
以上类型的差异，是对关键字的要求不同。
关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）
普通索引：对关键字没有要求。关键词的来源：可以是某个字段，也可以是某些字段（复合索引）。如果一个索引通过在多个字段上提取的关键字，称之为复合索引。
唯一索引：要求关键字不能重复，同时增加唯一约束。
主键索引：要求关键字不能重复，也不能为NULL。同时增加主键约束。
全文索引：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。
比如(复合索引)：
alter table emp addindex (field1,field2)
3、索引管理语法 （1）创建索引：
建表时：
注意：索引可以起名字，但是主索引不能起名字，因为一个表仅仅可以有一个主键索引，其他索引可以出现多个。名字可以省略，mysql会默认生成，通常使用字段名来充当。
show create table index1;
文章图片
更新表结构
alter table index2 add unique key (name),add index(age),add fulltext index(intro),add index(name,age);
文章图片
注意：
第一点：如果表中存在数据，数据符合唯一或主键约束才可能创建成功。
第二点：auto_increment属性，依赖于一个KEY（主键或唯一）。
（2）删除索引
修改表结构时完成：
删除主键索引：
alter tabletable_name drop primary key;
主键索引的删除，如果没有auto_increment 属性则使用 alter table 表名 drop primary key
如果在删除主键索引时，该字段中有auto_increment则先去掉该属性再删除。
去除主键的auto_inrement属性：
alter table 表名 modify id int unsigned not null comment '主键'
如有主键中有auto_incrments属性时，删除主键索引，则报如图提示。
文章图片
去除主键的auto_inrement属性：
alter table index1 modify id int unsigned;
文章图片
删除普通索引，唯一索引，全文索引，复合索引；
语法：
alter table 表名drop index 索引的名称；
文章图片
如果没有指定索引名，则可以通过查看索引的方法得到索引名（一般依赖于索引字段的名字）
（3）查看索引
show indexes from table_name;
show index from table_name\G;
show create table table_name;
show keys from table_name;
desc table_name;
文章图片
（4）创建索引注意事项(重点)：
第一：较频繁的作为查询条件字段应该创建索引五、执行计划 主要用于分析sql语句的执行情况（并不执行sql语句）得到sql语句是否使用了索引，使用了哪些索引。
select * from emp where empno = 1
第二：唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex = '男‘
第三：更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1
第四：不会出现在WHERE子句中字段不该创建索
语法：
explainsql语句\G或 desc sql语句\G
文章图片
文章图片
添加索引进行查看
文章图片
删除索引时，在看执行计划
文章图片
六、索引的数据结构 查看索引的类型：
show keys from 表名;
文章图片
文章图片
1、myisam的存储引擎索引结构：
索引的节点中存储的是数据的物理地址（磁道和扇区）
在查找数据时，查找到索引后，根据索引节点中记录的物理地址，查找到具体的数据内容。
文章图片
2、innodb的存储引擎的索引结构
innodb的主键索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用（非主键索引的节点存储是主键的id）
文章图片
比如要通过nam创建的索引，查询name=’采臣’的,先根据name建立的索引，找出该条记录的主键id，再根据主键的id通过主键索引找出该条记录。
【史上最详细的网站优化系列（一）mysql优化1】innodb的主索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用
myisam中, 主索引和非主索引,都指向物理行(磁盘位置).
注意: innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据聚簇索引
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)七、索引覆盖 索引覆盖是指：如果查询的列恰好是索引的一部分，那么查询只需要在索引区上进行，不需要到数据区再找数据，这种查询速度非常快，称为“索引覆盖”
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂（索引的节点移动）.
索引覆盖就是，我要在书里 查找一个内容，由于目录写的很详细，我在目录中就获取到了，不需要再翻到该页查看。
先分析一下使用场景，为什么使用索引覆盖，有什么好处？
准备两张表来测试使用；
文章图片
案例1，比如给id建立了主键索引，使用id查询数据。
文章图片
在nav_middle_level表里面，给middle_name字段添加索引，查询middle_name,就用到了索引覆盖。
文章图片
索引使用的场合如下，在没有条件时，直接查询建立索引的字段时，
案例2：比如给id和name 建立了复合索引，使用middle_name作为条件查询。
没有创建索引时，
给id和middle_name创建复合索引。
文章图片
典型情况如下：
学生表：
共30个字段。
Select id, name, height,gender from student where name=’XXX’;
建立索引：
Alter table student add index (name);
Alter table student add index (name, id, height, gender, class_id);
select name, id, height, gender, class_id from student
负面影响，增加了索引的尺寸。
保证该索引的使用率尽可能高，索引覆盖才有意义。
八、索引的使用原则 1、列独立 只有参与条件表达式的字段独立在关系运算符的一侧，该字段才可能使用到索引。
“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
文章图片
文章图片
文章图片
2、like查询 在使用like(模糊匹配)的时候，在左边没有通配符的情况下，才可以使用索引。
在mysql里，以%开头的like查询，用不到索引。
文章图片
注意，如果select的字段正好就是索引，那么会用到索引即索引覆盖。
文章图片
如果该表改为innodb引擎，因为非主键索引中存储的是id,select的字段是id因此用到了索引覆盖。
比如如下把表改成了 innodb的引擎，对name建立了索引，如下查询，就用到了索引覆盖。
改存储引擎语法：
alert table 数据表名称 engine=存储引擎类型（innodb/myisam）
文章图片
如果是innodb的表，可以如上使用。
文章图片
注意以下查询会用到索引；
文章图片
3、OR运算都具有索引 如果出现OR(或者)运算，要求所有参与运算的字段都存在索引，才会使用到索引。
如下：middle_name有索引，high_id没有索引
文章图片
如下：id有索引，name有索引
文章图片
4、复合索引使用 当前查询环境已有索引；
先删除和添加索引
文章图片
文章图片
**最左原则：
对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。
**
文章图片
注意：在多列索引里面，如果有多个查询条件，要想查询效率比较高，比如如下建立的索引，
index(a,b,c,d) 要保证最左边的列用到索引。
4、mysql 智能选择 如果mysql认为，全表扫描不会慢于使用索引，则mysql会选择放弃索引，直接使用全表扫描。一般当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。
文章图片
5、优化group by语句。 默认情况下， mysql对所有的group by col1,col2进行排序。这与在查询中指定order by col1,col2类型，如果查询中包括group by 但用户想要避免排序结果的消耗，则可以使用order by null禁止排序。
输出班级的id,high_id
文章图片
根据high_id分组，自动根据high_id进行 了排序，
select high_id,sum(id) from nav_middle_level group by high_id;
文章图片
如果不想根据high_id排序，则可以在后面使用order by nulll
select high_id,sum(id) from nav_middle_level group by high_id order by null;
通过分析语句发现：
文章图片
九、mysql中锁机制 1、应用场合： 比如有如下操作：
（1）从数据库中取出id的值(比如id=100)假如id=1
（2）把这个值-1(id=100-1)
（3）再把该值存回到数据库(id=99)
有两个进程（用户）同时操作，
使用锁机制来完成，
同时操作时，只有一个进程获得锁，其他进程就等待，
进程1
添加锁
id =100
id=100-1
id=99
释放锁
进程2
wating等待
wating等待
wating等待
id =100
id=100-1
id=99
2、mysql里面的锁的几种形式 锁机制：
当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能相互影响），通过加锁来处理。
操作方面：
读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是所有人都只可以读，只有释放锁之后才可以写。
写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征，只有锁表的客户可以操作（读写）这个表，其他客户读都不能读。
办公室开会锁上门。
锁定粒度（范围）
表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。
myisam引擎的表支持表锁，
行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。
innodb引擎的表支持行锁与表锁。
3、表锁的演示， 建立测试表，并添加测试数据：
create table user(
id int primary key auto_increment,
name varchar(32) not null default ‘’,
age tinyint unsigned not null default 0,
email varchar(32) not null default ‘’,
classid int not null default 1
)engine myisam charset utf8;
insert into user values(null,‘xiaogang’,12,‘gang@sohu.com’,4),
(null,‘xiaohong’,13,‘hong@sohu.com’,2),
(null,‘xiaolong’,31,‘long@sohu.com’,2),
(null,‘xiaofeng’,22,‘feng@sohu.com’,3),
(null,‘xiaogui’,42,‘gui@sohu.com’,3);
添加锁的语法：
lock table table_name1 read|write,table_name2 read|write;
释放锁的语法：
unlock tables;
（1）添加读锁
语法：
locktable_nameread|write;
文章图片
另外一个用户登录后，不能执行修改操作，可以执行查询操作。
文章图片
注意：添加读锁后，自己和其他的进程（用户）只能对该表查询操作，自己也不能执行修改操作。
释放锁之后，另外的一个进程，可以执行修改的操作了。
文章图片
查看另外的一个用户，是否可以操作该表，其他的用户，读都不能读，处于阻塞状态
文章图片
4、行锁的演示 innodb存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件(主键)检索数据，innodb才会使用行级锁，否则，innodb使用表锁。
先改变存储引擎
alter table user engine innodb;
文章图片
语法：
begin;
执行语句；
commit;
当前用户添加行锁，
另外的一个用户登录，进行操作。
文章图片
5、通过php代码来实现锁机制。 在apache里面有一个bin目录 下面有一个ab.exe工具，该工具可以模拟多个并发测试。
文章图片
语法：
ab.exe–n 总的请求数-c 并发数url地址；
具体的一个步骤：
（1）创建一个表，表里面就只有一个字段，便于我们演示。
create table t7(id int)engine myisam charset utf8;
insert into t7 values(100);
select * from t7;
文章图片
（2）通过代码来完成，
从数据库中取出id的值(比如id=100)
把这个值+1(id=100+1)
再把该值存回到数据库(id=101)
D:\phpStudy\WWW建立个php文件
demo.php
exec('set names utf8');
//取出id的值
$res = $pdo->query('select id from t7');
$info = $res->fetch(PDO::FETCH_ASSOC);
$id = $info['id'];
//给id的值加1操作
$id = $id+1;
//把该值再写入；
$pdo->exec("update t7 set id=$id");
echo 'ok';
?>
（3）使用ab工具模拟并发操作。
文章图片
查看表里面的id的值，我们执行了代码50次，应该id的值是150才对（性能差则低于150），则说明我们请求的50个并发，有几个并发是同时执行的。
文章图片
demo.php把注释去掉，添加锁机制。
exec('set names utf8');
//添加锁
$pdo->exec('lock table t7 write');
//取出id的值
$res = $pdo->query('select id from t7');
$info = $res->fetch(PDO::FETCH_ASSOC);
$id = $info['id'];
//给id的值加1操作
$id = $id+1;
//把该值再写入；
$pdo->exec("update t7 set id=$id");
$pdo->exec('unlock tables');
//是否锁
echo 'ok';
?>
加锁后会有一定的阻塞
文章图片
使用mysql里面锁机制缺点：就是阻塞，假如有一张goods表，goods表里面有一个库存的字段，当前下订单时，如果锁定了goods表，还能执行查询goods表吗？
会阻塞拖慢整个网站的速度，一但锁定goods表（添加写锁，要更改库存），则其他进程就无法查询goods表。
可以使用文件锁，
demo.php
exec('set names utf8');
//添加文件锁
//$pdo->exec('lock table t7 write');
$fh = fopen('./lock.txt','w');
flock($fh,LOCK_EX);
//取出id的值
$res = $pdo->query('select id from t7');
$info = $res->fetch(PDO::FETCH_ASSOC);
$id = $info['id'];
//给id的值加1操作
$id = $id+1;
//把该值再写入；
$pdo->exec("update t7 set id=$id");
//$pdo->exec('unlock tables');
//是否锁
flock($fh,LOCK_UN);
echo 'ok';
?>
文章图片
开始执行50个并发,测试效果如下；
文章图片
推荐阅读
- 这份史上最经典的3大学习方法，清华北大学霸都在用！
- “三家分晋”最终三国还是归晋，历史上可怕的惊人巧合
- 历史上的今天|【历史上的今天】2 月 16 日（世界上第一个 BBS 诞生；中国计算机教育开端；IBM 机器人赢得智能竞赛）
- 中国古代的“网红”（盘点历史上各个朝代的第一名人）
- 幸耶（不幸耶？）
- 适用于小白（VSCode搭建Vue项目，最详细的搭建步骤哦）
- 史上最有“演技”的演员（刘备）
- 历史上的娱乐与今天直播的必然联系。以及智能和互联网
- 刘禅不呆傻，呆傻的是我
- “神医”