目录
优化视频
https://www.imooc.com/learn/194
2、对于使用like的查询，查询如果是”%aaa”,不会使用到索引，‘aaa%’会使用到索引。 10
3、如果条件中有or，则要求or的索引字段都必须有索引，否则不能用到索引。 11
4、如果列类型是字符串，一定要在条件中将数据使用引号引用起来，否则不使用索引。 12
6、当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。 13
一、mysql优化概述
页面静态化的内容，memcache，减少数据库的访问，提高网站的访问速度，无论如何优化，还是要操作数据库的，要从数据库的角度来优化，提高访问速度。
1、设计角度：存储引擎的选择，字段类型选择，范式
存储引擎： innerDB、mysiam
1.MyISAM查询性能比InnoDB更快，但不支持事务处理，InnoDB支持事务处理和外键等高级功能
2.在mysql5.5版本之前InnoDB不支持全文检索，5.5以后支持了
3、每张MyISAM表被存放在三个文件 ：frm文件存放表格定义。 数据文件是MYD (MYData)。 索引文件是MYI (MYIndex)引伸。
因为MyISAM相对简单所以在效率上要优于InnoDB，小型应用使用MyISAM是不错的选择。
MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦
InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。InnoDB表的大小只受限于操作系统的文件大小，一般为 2 GB。InnoDB所有的表都保存在同一个数据文件ibdata1 中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份
1、数据库存储引擎
① 查看MySQL支持的数据库引擎（MySQL5.5以后默认的存储引擎是innerDB）
基本语法：
show engines; 查看MySQL支持的所有引擎
mysql 5.6之后innoDB才支持全文索引
② 支持的功能不同
Myisam引擎：擅长数据查询和新增操作，另外其还支持全文索引（英文分词技术）
InnoDB引擎：擅长数据的更新和删除操作，还支持事务处理，另外MySQL5.6之后其也支持全文索引了
③ 存储机制也不同
2种引擎对应的数据库的结构不一致，文件数目不一致，且数据保存的文件也不一致
innerDB的数据文件在
InnoDB和myisam区别
1. 文件的存储形式不一样
2. myisam的批量插入和查询效率高，而InnoDB的修改和删除效率高
3. 事务安全只有InnoDB支持，myisam不支持
4. InnoDB支持外键，myisam不支持
2、功能：
利用mysql自身的特性：索引，查询缓存，分区、分表，存储过程
3、部署大负载架构体系：主从复制，读写分离。
4、SQL语句的优化
5、硬件升级
二、分析需要优化的语句
要 查找执行速度比较慢的sql语句，
1、使用mysql里面的 慢查询日志，
慢查询日志，是由mysql提供的，用于记录sql执行时间超过了某个时间界限，该时间界限我们可以自己设定，比如我们设定的时间界限为0.5秒，开启慢查询日志后，会自动记录执行时间超过0.5秒的sql语句。慢查询日志默认没有开启，默认的时间界限是10秒。
（1）如何开启慢查询日志，
方式一：打开mysql的配置文件,my.ini,添加如下语句：修改完成后，要重启mysql服务。
（2）开始测试，是否记录执行时间超过0.5秒 的sql语句。
select benchmark(执行次数,表达式);
在慢查询日志文件里面查看是否记录：
（3）相关的一些命令，
查看慢查询日志的时间界限
可以在当前会话中进行修改慢查询的时间界限：
语法：set long_query_time=时间界限
2、使用profiles机制
该机制开启后，会记录每个sql语句的执行时间，精确到小数点后8位。
如果开启：set profiling=1|0 值为1则是开启，为0则是关闭。
查看sql语句的执行时间:show profiles
一般sql语句执行比较慢，原因是没有添加索引，
没有添加索引之前：
添加索引之后使用的时间：
三、索引
索引：利用字段的某些属性，快速 的定位数据（磁盘，柱面，磁道，扇区）
1. 什么是索引？
索引就是类似书的目录，提高检索数据的效率。
索引是系统按照某个具体的算法（哈希，散列，二叉树），将数据从全部数据里进行提取，维护成一个索引文件，然后系统在进行数据查询的时候，发现如果查询条件刚好满足索引条件，就可以从索引文件中快速的定位的数据所在位置。
mysql中有哪些索引？
主键索引（primary key效率最高的索引）
唯一索引（unique key）：不为空的情况下效率最高
普通索引（index）对数据没有要求，文件很大，效率比较低
全文索引（fulltext），对整个文章内部进行关键字索引（mysql5.5以后InnoDB支持全文索引）
英文的全文索引很简单：英文单词默认是用空格分离的
中文的全文索引很难：中文的词组成很麻烦，需要利用分词工具（sphinx）
1、索引 的类型
唯一索引（unique key）:字段数据是唯一的，数据内容里面能否为null,在一张表里面，是可以添加多个唯一索引。
主键索引(primary key )：数据记录里面不能有null,数据内容不能重复，在一张表里面不能有多个主键索引。
普通索引(index )：使用字段关键字建立的索引，主要是提高查询速度。
全文索引(fulltext index)：在比较老的版本中，只有myisam引擎支持全文索引，在最新的版本中（可能是mysql.5.6）innodb引擎也支持全文索引，在mysql中全文索引不支持中文。
2、如何创建索引
（1）在创建表的时候，同时创建索引，
create table stu(
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null,
email varchar(32) not null,
intro text,
unique key (name),
index (email),
fulltext index (intro)
)engine myisam charset utf8;
（2）在修改表的时候，添加索引
create table stu1(
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null,
email varchar(32) not null,
intro text
)engine myisam charset utf8;
alter table stu1 add unique key (name), add index (email), add fulltext index (intro);
3、删除索引
主键索引的删除，在删除主键 索引时，如果有自增长，先修改掉自增长，再删除主键
alter table 表名 drop primary key
普通索引的删除：
alter table 表名 drop index 索引名字（如果索引名称没有指定则是索引的字段名称）
删除唯一索引：
alter table 表名 drop index 索引名字
4、查询索引：
1）show index form表名\G
2）show indexes from 表名
3）desc 表名
4）show create table表名
5、创建索引的注意事项
（1）较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex = '男‘
更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1
（2）不会出现在WHERE子句中字段不该创建索引
四、索引结构
查看索引的类型，是BTREE结构。
myisam引擎的索引的结构
该引擎的表是有三个文件组成的。一个是frm(存储结构)myd(数据) myi（索引）
innodb索引的索引结构，innodb的索引叫聚簇索引。
innodb的主索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用
注意: innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”
在插入大量的数据的时候，造成频繁的页分裂.
五、explain(执行计划)工具的使用
主要用于分析sql语句的执行情况（并不执行sql语句）得到sql语句是否使用了索引，使用了哪些索引。
语法：explain sql语句\G 或 desc sql语句\G
在mysql之前的版本中，explain只支持select语句，但是在最新的5.6版本中，它支持 explain update/delete了。
建表完成测试：
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);
创建一个班级表：
create table class5(
id int not null default 0,
classname varchar(32) not null default ''
)engine myisam charset utf8;
insert into class values(1,'java'),(2,'.net'),(3,'php'),(4,'c++'),(5,'ios');
总结：即使一张表中创建了索引，但是没有在where条件后使用的话，该执行语句不会使用索引
六、使用执行计划，完成sql语句索引执行情况查询；
复合索引，有多列组合成一个索引。比如如下，建立一个name和age的一个复合索引。
mysql> alter table user add index (name,age);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
1、多列索引：
（1）对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。 （最左原则）
因为组合索引是需要按顺序执行的，比如c1234组合索引，要想在c2上使用索引，必须先在c1上使用索引，要想在c3上使用索引，必须先在c2上使用索引，依此。
注意:如果字段是纯数字,索引会全部命中(不遵循最左原则)
2、对于使用like的查询，查询如果是”%aaa”,不会使用到索引，‘aaa%’会使用到索引。
比如：根据电影的剧情查找电影的名称，根据歌词查找歌名。like ‘%爱请%’
使用全文索引来解决，可以使用第三方查询工具，sphinx
3、如果条件中有or，则要求or的索引字段都必须有索引，否则不能用到索引。
4、如果列类型是字符串，一定要在条件中将数据使用引号引用起来，否则不使用索引。
5、优化group by语句。
默认情况下， mysql对所有的group by col1,col2进行排序。这与在查询中指定order by col1,col2类型，如果查询中包括group by 但用户想要避免排序结果的消耗，则可以使用order by null禁止排序。
6、用like查询的时候当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。
七、索引覆盖
索引覆盖是指：如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据，这种查询速度非常快，称为“索引覆盖”
案例1，如下对name字段添加了普通索引，要查询name字段信息。
案例2：比如对id和name字段建立符合索引，我们取出的数据是复合索引的一部分，因此用到了索引覆盖。
注意: 如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以是SELECT *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降，不能为了利用覆盖索引而这么做
八、前缀索引
利用字段数据的前部分作为索引，称为前缀索引。减少索引长度，提高索引效率。
比如：统计密码的前7个字符，作为不相同匹配条件，几乎可以做到1:1
此时，就可以利用前7个字符做索引关键字即可（离散程度高）
语法：
alter table 表名 add index (passwd(7)) 指定前7位作为索引关键字。
不使用索引前缀，索引的长度。
使用索引前缀后，索引的长度。
全文索引
匹配热度
九、翻页优化
翻页的sql语句：
select * from table_name limit offset，length
使用如上语句，在翻页时，翻到最后，越来越慢，
原因：并不是跨过offset行，取出n条，
是取出offset+N条数据，舍弃前面的offset行，只取出n条数据。
如何解决？
（1）从业务上去解决：
办法：不允许翻过100页，
以百度为例，一般翻页到70页左右，谷歌40页左右
（2）不用offset，用条件查询，条件中使用id查询，使用到了索引，
select * from user limit 10000,10;
select * from user where id>10000 limit 10;
下一页：select * from user where id>10000+10 limit 10
该种方式要注意：如果有数据被删除，会导致select * from user limit 10000,10;
和select * from user where id>10000 limit 10;语句取出的结果不一样，。
因为数据在磁盘中被删除掉了（物理删除），结构发生改变，两者的查询的结果不一致，
要想使结果一致，不能进行物理删除，给表增加一个字段来区分是否删除。
（3）假如不能使用限制翻页到100页，数据有删除，还要求翻页，速度不能受影响。
思路：通过翻页，先取出id（主键），在根据id取出数据。
select name,age,email from user inner join (select id from user limit 10000,10) as tmp on tmp.id=user.id
非要物理删除，还要用offset精确查询，还不限制用户分页，怎么办
我们现在必须要查，则只查索引，不查数据，得到id
再用id去查具体条目，这种技巧就是延迟索引。
十、碎片整理
比如建表测试：
数据表文件原来的容量：
当delete from ceshi where id=1，应该容量减去三分之一，但是并没有被删除。
需要把里面的一些碎片给释放掉。
使用optimize table 表名；或alter table 表名 engine myisam(innodb)
执行optimize table 表名，命令后，把原来的碎片空间给释放掉
注意：修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐，这个过程，如果表的行数比较大，也是比较耗费资源的操作，所以，不能频繁的修复。
如果表的update操作很频繁，可以按周月来修复
十一、查询缓存（了解）
开启查询缓存：
show variables like ‘query_cache%’;
set global query_cache_type=1; //开启缓存
set global query_cache_size=1024*1024*32; //设置缓存的大小（单位是字节）
前提条件
1、保证SQL语句完全一致
2、动态的数据是无法被缓存的
3、一旦开启了查询缓存，MySQL会将所有可以被缓存的select语句都缓存，在业务上，如果存在不想使用缓存的sql执行，则可以在查询数据时加上SQL_NO_CACHE语法达到目的
十二、锁机制讲解
场景:
下订单：
库存 为100，买一件：
（1）取出库存的数量 100
（2）库存减去1 99
（3）把剩余的库存再写入到表里面。 99
如果是两个人同时操作：
刘备：
100-1=99
99
曹操：
100-1=99
99
锁机制
mysql 的锁有以下几种形式：
表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam引擎属于这种类型。
行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb属于这种类型。
1、表锁的演示：
对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的操作。
对myisam表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
read:所有人都只可以读，只有释放锁之后才可以写。
write:只有锁表的客户可以操作这个表，其他客户读都不能读。
语法：
lock table 表名 read|write,
解锁：
unlock table
读锁的演示：
要注意：对表添加锁定后，只能操作锁定的表，如果想要操作其他表，则可以在锁定表时，一次性锁定多张表。语法：lock table表1 read,表2 read;
写锁的演示
对表写锁锁定之后，自己可以进行修改和查询，另外的进程则无法查询，更不能修改。
2、行锁的演示：
是innodb支持的一种锁，在使用时，要添加条件限制是要操作哪行数据。
语法：
begin;
执行语句；
commit;
十三、分区技术
基本概念，把一个表，从逻辑上分成多个区域，便于存储数据。
采用分区的前提：数据量非常大
分区的语法，在创建表时，完成分区
create table 表名(
字段信息
)表选项
partition by 分区的类型（分区的条件）(
//分区信息。
)；
1、分区类型：
list :条件值为一个数据列表。
myisam直接可以查看
InnoDB set global innodb_file_per_table=1; 设置每张表一个文件
通过预定义的列表的值来对数据进行分割
例子：假如你创建一个如下的一个表，该表保存有全国20家分公司的职员记录，这20家分公司的编号从1到20.而这20家分公司分布在全国4个区域，如下表所示：
职员表：
北部 1,4,5,6,17,18
南部 2,7,9,10,11,13
东部 3,12,19,20
西部 8,14,15,16
id name store_id(分公司的id)
1 李小龙 3
2 大刀王五 8
北部 1,4,5,6,17,18
南部 2,7,9,10,11,13
东部 3,12,19,20
西部 8,14,15,16
create table emp(
id int,
name varchar(32),
store_id int,
primary key(id,store_id)
)engine myisam charset utf8
partition by list (store_id)(
partition p_north values in (1,4,5,6,17,18),
partition p_east values in(2,7,9,10,11,13),
partition p_south values in(3,12,19,20),
partition p_west values in(8,14,15,16)
);
添加两天条语句，测试是否使用了分区存储。
explain partitions select *from emp where store_id=3
测试是否用到了分区：
explain partitions select * from p_list where store_id=20\G
注意：在使用分区时，where后面的字段必须是分区字段，才能使用到分区。
Range（范围）
这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
create table p_range(
id int,
name varchar(32),
birthday date,
primary key(id,birthday)
)partition by range (month(birthday))(
partition p_1 values less than (3),
partition p_2 values less than(6),
partition p_3 values less than(9),
partition p_4 values less than MAXVALUE
);
less than 小于等于；
MAXVALUE可能的最大值
Hash(按照某个表达式的值进行取余)
create table p_hash(
id int,
name varchar(32),
birthday date,
primary key(id,birthday)
)partition by hash(month(birthday)) partitions 12; //作为分区的字段必须是主键字段的一部分
Key 按照某个值进行取余
create table p_key(
id int primary key,
name varchar(32)
)partition by key (id) partitions 5;
注意:建分区的字段不能为null,,如果list超过分区的字段,分区不存在range,hash
2、管理分区
Key,hash分区
增加分区：
alter table 表名 add partition partitions数目
减少分区：(合并)
alter table 表名 coalesce partition数目
list,range 分区
添加分区:
alter table 表名 add partion(
//分区列表
);
减少分区：（会导致数据丢失）
alter table 表名 drop partition分区名
3、选择分区
key 和list
注意：分区字段必须依赖于主键
平均分配： 就按照主键进行key(primary key)即可，也是最常见的分区算法
按照某种业务逻辑分区：选择整数型的字段进行分区、选择最常进行查询（筛选）的字段进行分区
十四、分表（做了解）
水平分表（MySQL5.1之前使用，已经不使用了，了解）：创建结构相同的多张表
垂直分表： 表中字段非常多，按照常用和不常用字段进行分表，两张表的字段为1对1的关系
垂直分表 (同时操作,字段多拆分)
水平分表 (字段都是一样,)
取模,取余,id的最后一位
127
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
