mysql优化
目录
优化视频
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的最后一位