【mysql数据库优化】原因:性能不够,通过一些措施提高性能。
不同的阶段采用不同的措施,不同的措施借助不同的工具。
--【措施1:选取合适的存储引擎】--数据库设计阶段
>5.5 innodb 默认
<5.5 myisam 默认
myisam 和 innodb的区别?
【数据文件的存储格式】
① innodb由2个文件组成:*.frm表结构文件、*.ibd数据索引文件。
② myisam由3个文件组成:*.frm表结构文件、*.MYD数据文件、*.MYI 索引文件。
【数据的存储顺序】
① innodb如果有主键的话,数据会根据主键顺序进行存储。
② myisam,数据按照插入顺序进行存储。
【并发性】
① innodb并发处理能力更好一些,因为innodb支持行级锁定。
② myisam逊色一些,因为myisam只支持表锁。
【压缩性】--myisam独有的
注意:更新不频繁的数据适合进行压缩,例如:邮政编码。
【1】压缩
压缩后的影响:
① 需要重建索引。
② 数据表变成只读表。
【2】解压
【对事务的支持】
【对外键的支持】
--【措施2:优化sql语句,尽可能的使用上索引】--编码 和 项目上线运营阶段
查询的SQL语句有没有使用上索引。
【1】找出需要优化的sql语句(执行时间比较长的sql语句)
借助工具找。
① 慢查询日志(把执行时间超过一定阀值的sql语句给记录下来)
第1步,开启慢查询日志
① 临时开启,set slow_query_log =1;
② 修改配置文件,永久开启
第2步,设置时间阀值
set long_query_time = 0.5;
② profile机制(精确的记录sql语句执行的时间)
第1步,开启,set profiling = 1;
第2步,执行要分析的sql语句
第3步,查看时间:show profiles;
【2】对sql语句进行优化。提高sql语句的执行效率,尽可能让sql语句使用上索引。
【索引】一种特殊的数据。
① 作用:快速的定位要查询的数据。
② 类型:
普通索引:
唯一索引:要求关键字不能重复。
主键索引:要求关键字不能重复 且 不能为null
全文索引,字段内容中的一些单词提取出来,作为索引的关键字。
--创建语法:alter table articles add fulltext index(title);
--使用语法:select * from articles where match(字段) against(模糊内容);
注意:只支持英文,不支持中文。
复合索引(多列索引):多个字段作为索引的关键字。
前缀索引:把字段内容的前N位作为索引的关键字。
创建语法:alter table 表名 add index (字段(N))
前n位,需要计算出来!
第1步,计算前n位的位数,前几位可以唯一标识字段的内容。
1)计算不重复记录的总条数(需要去除内容重复的记录):select count(distinct 字段) from 表名;
2)计算前n位不重复记录的总条数: select count( distinct left(字段 , n) ) from 表名;
第2步,使用第1步计算出的位数,创建前缀索引。
alter table 表名 add key ( left( 字段名, n ) )。
③ 创建索引:
普通索引:index
唯一索引:unique key
主键索引:primary key
全文索引:fulltext index
④ 删除索引:
1)删除主键索引:alter table 表名 drop primary key;(注意auto_increment属性)
2)删除非主键索引:alter table 表名 drop index 索引名称;
【哪些字段适合创建索引?】
① 经常作为查询条件的字段
② 唯一性差的字段不适合。例如,性别。
③ 频繁更新的字段不适合。
【第2.1步,分析sql语句是否使用了索引】
借助工具:explain执行计划
使用:explain sql语句\G
【2.2步】sql语句是否符合索引使用的一些原则?
【① 索引覆盖(黄金索引)】
查询字段恰好是索引字段 或者 是索引字段的一部分,此时在索引区就获取到了数据,而不用去数据区。
采取的措施:一些经常查询的字段,建一个复合索引。
【② 列独立原则:】
索引字段要独立存在于 运算表达式的一侧,这时才有可能用上索引。
【③ like模糊查询:】
查询内容左边是固定的(like "张%"),这时才有可能用上索引。
【④ OR原则:】
参与or运算的字段都有索引,才有可能用上索引。
【⑤ 复合索引最左原则:】
条件字段是复合索引最左边的列,才有可能用上复合索引。
【⑥ mysql自身的智能选择。】
一般当取出的数据量超过表中数据的20%
【2.3 常见语句的优化】:
① group by语句优化:默认会进行排序。如果不需要排序,可以禁止排序,order by null;
--【措施3:使用mysql数据库自带的查询缓存功能】 select语句的结果给缓存起来。--上线运营阶段
1)开启缓存的配置:(修改配置文件my.ini)
a)开启缓存:query_cache_type=1
b)设置大小:query_cache_size=134217728(单位是字节Byte,设置128M的话就是128*1024*1024)bit位、B字节、KB千字节、M、G
2)无缓存的情况:
--情况1:【缓存失效】数据 或 表结构发生改变
update emp set job='123456' where empno=123456;
--情况2:【不缓存】sql语句中有变化表达式(随机数)
select ename,job,now() from emp where empno = 123456;
--情况3:【生成多份缓存】相同结果的sql语句,由于空格、大小写问题会生成多个缓存
select * from emp where empno=123456;
select * from emp WHERE empno=123456;
--情况4:【禁用缓存】
select sql_no_cache * from emp where empno=123456;
--【措施4:分区技术(逻辑分表)】--把数据分成多个区域是存储,对于客户端来说操作的还是一张表。
应用场景:单表的数据量比较大时,例如:1亿条记录。
分区字段:根据哪个字段的内容进行分区,哪个字段就是分区字段。
【注意】:① 表中有主键时,分区字段必须是主键或者主键的一部分。
② 表中有唯一索引时,分区字段必须是唯一索引或者一部分。
分区的依据(分区算法):list、range、hash、key
创建分区语法:
create table 表名{
字段信息,
索引信息,
}engine=myisam 表选项
partition by 分区算法 (分区字段){
分区信息
}
分区管理:
① 删除分区
1)删除list或者range分区
注意:会造成数据的丢失。
2)删除hash或者key分区
实际上进行分区的合并操作。
② 增加分区
1)alter table 表名 add partition partitions 数量;
2)alter table 表名 add partition(
partition 名称 values less than (常量)
);
--【措施5:分表技术(物理分表)】
解决问题:单表数据量过大。
1)水平分表
手动创建多张结构相同的表。
2)垂直分表
把一个表的全部字段分别存储到不同的表里边。
把经常使用的字段存到 主表,不经常使用的字段存到 辅表中
--【措施6:修复数据碎片】--上线运营阶段
在长期的数据更改(更新、删除)过程中,索引文件和数据文件,都将产生空洞,形成碎片。
通常是按照周、月,在凌晨进行碎片修复。
修复的语法:optimize table 表名;
--【措施7:逆范式/反三范式】--项目设计阶段
范式:创建数据表的时候遵循的规范。
1)故意违反三范式,在数据表中添加冗余字段,来 提高查询效率。
2)① 好处:可以提高了查询效率(单表查询优于连表查询)
② 弊端:增加了业务功能的复杂性,在插入、删除操作时要维护增加的字段。
不同的阶段采用不同的措施,不同的措施借助不同的工具。
--【措施1:选取合适的存储引擎】--数据库设计阶段
>5.5 innodb 默认
<5.5 myisam 默认
myisam 和 innodb的区别?
【数据文件的存储格式】
① innodb由2个文件组成:*.frm表结构文件、*.ibd数据索引文件。
② myisam由3个文件组成:*.frm表结构文件、*.MYD数据文件、*.MYI 索引文件。
【数据的存储顺序】
① innodb如果有主键的话,数据会根据主键顺序进行存储。
② myisam,数据按照插入顺序进行存储。
【并发性】
① innodb并发处理能力更好一些,因为innodb支持行级锁定。
② myisam逊色一些,因为myisam只支持表锁。
【压缩性】--myisam独有的
注意:更新不频繁的数据适合进行压缩,例如:邮政编码。
【1】压缩
压缩后的影响:
① 需要重建索引。
② 数据表变成只读表。
【2】解压
【对事务的支持】
【对外键的支持】
--【措施2:优化sql语句,尽可能的使用上索引】--编码 和 项目上线运营阶段
查询的SQL语句有没有使用上索引。
【1】找出需要优化的sql语句(执行时间比较长的sql语句)
借助工具找。
① 慢查询日志(把执行时间超过一定阀值的sql语句给记录下来)
第1步,开启慢查询日志
① 临时开启,set slow_query_log =1;
② 修改配置文件,永久开启
第2步,设置时间阀值
set long_query_time = 0.5;
② profile机制(精确的记录sql语句执行的时间)
第1步,开启,set profiling = 1;
第2步,执行要分析的sql语句
第3步,查看时间:show profiles;
【2】对sql语句进行优化。提高sql语句的执行效率,尽可能让sql语句使用上索引。
【索引】一种特殊的数据。
① 作用:快速的定位要查询的数据。
② 类型:
普通索引:
唯一索引:要求关键字不能重复。
主键索引:要求关键字不能重复 且 不能为null
全文索引,字段内容中的一些单词提取出来,作为索引的关键字。
--创建语法:alter table articles add fulltext index(title);
--使用语法:select * from articles where match(字段) against(模糊内容);
注意:只支持英文,不支持中文。
复合索引(多列索引):多个字段作为索引的关键字。
前缀索引:把字段内容的前N位作为索引的关键字。
创建语法:alter table 表名 add index (字段(N))
前n位,需要计算出来!
第1步,计算前n位的位数,前几位可以唯一标识字段的内容。
1)计算不重复记录的总条数(需要去除内容重复的记录):select count(distinct 字段) from 表名;
2)计算前n位不重复记录的总条数: select count( distinct left(字段 , n) ) from 表名;
第2步,使用第1步计算出的位数,创建前缀索引。
alter table 表名 add key ( left( 字段名, n ) )。
③ 创建索引:
普通索引:index
唯一索引:unique key
主键索引:primary key
全文索引:fulltext index
④ 删除索引:
1)删除主键索引:alter table 表名 drop primary key;(注意auto_increment属性)
2)删除非主键索引:alter table 表名 drop index 索引名称;
【哪些字段适合创建索引?】
① 经常作为查询条件的字段
② 唯一性差的字段不适合。例如,性别。
③ 频繁更新的字段不适合。
【第2.1步,分析sql语句是否使用了索引】
借助工具:explain执行计划
使用:explain sql语句\G
【2.2步】sql语句是否符合索引使用的一些原则?
【① 索引覆盖(黄金索引)】
查询字段恰好是索引字段 或者 是索引字段的一部分,此时在索引区就获取到了数据,而不用去数据区。
采取的措施:一些经常查询的字段,建一个复合索引。
【② 列独立原则:】
索引字段要独立存在于 运算表达式的一侧,这时才有可能用上索引。
【③ like模糊查询:】
查询内容左边是固定的(like "张%"),这时才有可能用上索引。
【④ OR原则:】
参与or运算的字段都有索引,才有可能用上索引。
【⑤ 复合索引最左原则:】
条件字段是复合索引最左边的列,才有可能用上复合索引。
【⑥ mysql自身的智能选择。】
一般当取出的数据量超过表中数据的20%
【2.3 常见语句的优化】:
① group by语句优化:默认会进行排序。如果不需要排序,可以禁止排序,order by null;
--【措施3:使用mysql数据库自带的查询缓存功能】 select语句的结果给缓存起来。--上线运营阶段
1)开启缓存的配置:(修改配置文件my.ini)
a)开启缓存:query_cache_type=1
b)设置大小:query_cache_size=134217728(单位是字节Byte,设置128M的话就是128*1024*1024)bit位、B字节、KB千字节、M、G
2)无缓存的情况:
--情况1:【缓存失效】数据 或 表结构发生改变
update emp set job='123456' where empno=123456;
--情况2:【不缓存】sql语句中有变化表达式(随机数)
select ename,job,now() from emp where empno = 123456;
--情况3:【生成多份缓存】相同结果的sql语句,由于空格、大小写问题会生成多个缓存
select * from emp where empno=123456;
select * from emp WHERE empno=123456;
--情况4:【禁用缓存】
select sql_no_cache * from emp where empno=123456;
--【措施4:分区技术(逻辑分表)】--把数据分成多个区域是存储,对于客户端来说操作的还是一张表。
应用场景:单表的数据量比较大时,例如:1亿条记录。
分区字段:根据哪个字段的内容进行分区,哪个字段就是分区字段。
【注意】:① 表中有主键时,分区字段必须是主键或者主键的一部分。
② 表中有唯一索引时,分区字段必须是唯一索引或者一部分。
分区的依据(分区算法):list、range、hash、key
创建分区语法:
create table 表名{
字段信息,
索引信息,
}engine=myisam 表选项
partition by 分区算法 (分区字段){
分区信息
}
分区管理:
① 删除分区
1)删除list或者range分区
注意:会造成数据的丢失。
2)删除hash或者key分区
实际上进行分区的合并操作。
② 增加分区
1)alter table 表名 add partition partitions 数量;
2)alter table 表名 add partition(
partition 名称 values less than (常量)
);
--【措施5:分表技术(物理分表)】
解决问题:单表数据量过大。
1)水平分表
手动创建多张结构相同的表。
2)垂直分表
把一个表的全部字段分别存储到不同的表里边。
把经常使用的字段存到 主表,不经常使用的字段存到 辅表中
--【措施6:修复数据碎片】--上线运营阶段
在长期的数据更改(更新、删除)过程中,索引文件和数据文件,都将产生空洞,形成碎片。
通常是按照周、月,在凌晨进行碎片修复。
修复的语法:optimize table 表名;
--【措施7:逆范式/反三范式】--项目设计阶段
范式:创建数据表的时候遵循的规范。
1)故意违反三范式,在数据表中添加冗余字段,来 提高查询效率。
2)① 好处:可以提高了查询效率(单表查询优于连表查询)
② 弊端:增加了业务功能的复杂性,在插入、删除操作时要维护增加的字段。