MySQL优化

最新推荐文章于 2022-07-01 12:06:38 发布

a大魔王

最新推荐文章于 2022-07-01 12:06:38 发布

阅读量3.7w

点赞数 3

分类专栏：小白到架构师路线文章标签： mysql mysql优化

本文链接：https://blog.csdn.net/qq_38065439/article/details/88387921

版权

小白到架构师路线专栏收录该内容

20 篇文章 0 订阅

订阅专栏

MySQL优化方式

表的设计合理化（符合3NF），实际设计中不可能百分百遵守3NF
添加适当索引（index）普通索引，主键索引，唯一索引，全文索引
SQL语句优化
分表技术（水平分割，垂直分割）
读写分离
存储过程
对mysql配置优化（配置最大并发数my.ini,调整缓存大小）
mysql服务器硬件升级
定时去清除不需要的数据，定时进行碎片整理

数据库三大范式

第一范式：1NF是对属性的原子性约束，要求属性列具有原子性，不可再分解。

第二范式：2NF是对记录的唯一性约束，表中的记录是唯一性的。

第三范式：3NF是对字段冗余性的约束，要求字段没有冗余。

分表分库

垂直拆分：垂直拆分就是要把表按模块划分到不同数据库中。

水平拆分：把一个表按照某种规则把数据划分到不同表或数据库里。有的按照注册时间，取模，账号规则，年份来拆分。

SQL优化

使用show status查看mysql服务器状态信息

show status like 'uptime' mysql数据库启动了多少时间

show status like 'com_select'(update,delete,insert) 显示数据库的查询，更新，添加，删除次数

show status like 'connections' 显示到mysql数据库的连接数

show status like 'slow_queries' 显示慢查询次数

慢查询

mysql默认10秒内没有响应sql结果，则为慢查询，可以修改慢查询默认时间

show variables like 'long_query_time' 查看慢查询时间

set long_query_time = 1 修改慢查询时间，但是重启mysql long_query_time 还是10秒

如何将慢查询定位到日志中

mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中，long_query_time的默认值为10，运行10s以上的语句，会被记录下来，mysql数据库默认不开启慢查询日志，需要我们手动来设置这个·参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定性能影响。

show variables like 'long_query_time' 查看慢查询设置的时间

set global slow_query_log=1; 开启慢查询日志

set global long_query_time=1 设置慢查询日志时间

show variables like '%slow_query_log%' 查看慢查询一些信息

show global status like '%slow_queries%' 查看有多少条慢查询记录

索引

索引用来快速地寻找那些具有特定值的记录，所有mysql索引都以b-树的形式保存。

查询索引：desc 表名 show index from 表名 show keys from 表名

主键索引：primary key 列如：alter table tablename add primary key 列名 alter table articles drop primary key删除索引

全文索引：fulltext 列如：alter table tablename add fulltext (列名) select * from 表名 where match(列名) against('关键字')

唯一索引：unique 列如：alter table tablename add unique 索引名字列名可以为多个null，不可以为多个空字符串

普通索引：index 列如：create index 索引名 on 表名（列名）

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库表中数据，索引的实现通常使用B树或变种B+树。为表设置索引要付出代价的，意识增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间。

mysql explain执行计划

语法：explain select * from 表名

explain列解释

table：显示这一行的数据时关于那张表的

type：显示连接使用了何种类型，好到差的连接类型为const,eq_reg,ref,range,indexhe 和 all

system：表只有一行，system表，这是const连接类型的特殊情况

const：表中的一个记录的最大值能够匹配这个查询

eq_ref：在连接中，mysql在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，他在查询使用了索引为主键或唯一键的全部时使用。

ref：这个俩呢及类型只有在查询使用了不是唯一或主键的键或者是这些类型的部分时发生。

range：使用索引返回一个范围中的行。

index：对前面的表中的每一个记录联合进行完全扫描。

all：对于前面的每一记录联合进行完全扫描，比较糟糕，应该尽量避免。

possible_keys：显示可能应用在这张表中的索引，如果为空，没有可能的索引。

key：实际使用的索引，如果为null，则没有使用索引。

key_len：使用的索引的长度，在不损失精确性的情况下，长度越短越好。

ref：显示索引的那一列被使用了。

rows：mysql认为必须检查的用来返回请求数据的行数。

extra：mysql解析查询的额外信息。

索引的代价

占用磁盘空间

对dml（update,delete,insert）语句的效率影响

增删改会对索引影响，因为索引要重新整理。

存储引擎	索引类型
myisam	btree
innodb	btree
memory	hash,btree

那些列上适合添加索引

查询作为查询条件字段应该创建索引

唯一性太差的字段不合适单独创建索引，即使频繁

频繁更新字段，也不要定义索引

不会出现在where语句的字段不要创建索引

满足以下一条件的字段，才应该创建索引

1.肯定在where条件经常使用

2.该字段的内容不是唯一的几个值

3.字段内容不是频繁变化

SQL优化技巧

使用group by分组查询是，默认分组后，还会排序，可能会降低速度，在group by后面增加order by null防止排序
有些情况下，可以使用连接来代替子查询，因为使用join，mysql不需要在内存中创建临时表。
对查询进行优化，要尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引，避免在where字句中对字段进行null值判断。
不要使用大于=

MySQL数据引擎

myisam存储：如果表对事物要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎。

innodb存储：对事物要求高，保存的数据都是重要数据。

MyISAM和INNODB的区别

事务安全（myisam不支持事务，innodb支持事务）

查询和添加速度（myisam批量插入速度快）

支持全文索引（myisam支持全文索引，innodb不支持全文索引）

锁机制（myisam表锁，innodb行锁）

外键（myisam不支持外键，innodb支持外键）

memory存储：数据变化频繁，不需要入库，同时又频繁的查询和修改，速度极快（mysql重启，数据就不存在了）

myisam注意事项

如果数据库的存储引擎是myisam，一定要定时进行碎片整理

optimize table 表名删除碎片

主从复制

mysql主从复制作用：读写分离，数据备份，高可用，集群。

mysql主从复制原理：二进制sql执行文件

修改主服务器：my.ini或my.cof文件

server_id=100   ###服务器id
log-bin=mysql-bin      ###开启日志文件



###主服务器给从服务器账号授权
GRANT REPLICATION SLAVE ON *.* TO 'wenlong'@'%' IDENTIFIED BY 'wenlong';


###查询master的状态,如果结果为null，则主服务器my.ini没有配置好
show master status;


###修改从（slave）服务器
server_id=200
log-bin=mysql-bin
binlog_do_db=test
change master to master_host='ip地址'，master_user='用户名'，master_password='密码'，master_log_file='mysql-bin.000001',master_log_pos=343;


###启动同步
start slave


###检查从服务器复制功能状态  slave_io_running:yes  slave_sql_running:yes
show slave status

读写分离

在数据库集群架构中，让主库负责处理事务性查询，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。

mycat：一个开源的分布式数据库系统，但是因为数据库一般都有自己的数据库引擎，而mycat并没有属于自己的独特数据引擎，所以严格意义上说并不能算是一个完整的数据库系统，只能说是一个在应用和数据库之间起桥梁作用的中间件。

配置server.xml

<!-- 添加user -->
<user name="mycat">
    <property name="password">mycat</property>
    <property name="schemas">mycat</property>
</user>
	
<!-- 添加user -->
<user name="mycat_red">
    <property name="password">mycat_red</property>
    <property name="schemas">mycat</property>
    <property name="readOnly">true</property>
</user>

配置schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
    <!-- 与server.xml中user的schemas名一致 -->
    <schema name="mycat" checkSQLschema="true" sqlMaxLimit="100">
        <table name="t_users" primaryKey="user_id" dataNode="dn1" rule="rule1"/>
        <table name="t_message" type="global" primaryKey="messages_id" dataNode="dn1" />
    </schema>
<dataNode name="dn1" dataHost="jdbchost" database="weibo_simple" />
    <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
         <heartbeat>select user()</heartbeat>  
        <writeHost host="hostMaster" url="127.0.0.1:3306" user="root" password="root">
        </writeHost>
        <writeHost host="hostSlave" url="127.0.0.1:3306" user="root" password="root"/>
    </dataHost>
    
</mycat:schema>

配置rule.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
	 <tableRule name="rule1">
        <rule>
            <columns>user_id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>
    <function name="func1" class="org.opencloudb.route.function.AutoPartitionByLong">
  	<property name="mapFile">autopartition-long.txt</property>
    </function>
</mycat:rule>

为了更好定位错误，修改log4j.xml

双击startup_nowrap.bat开始启动

a大魔王

关注

3
点赞
踩
25

收藏

觉得还不错? 一键收藏
0
评论
MySQL优化

MySQL优化方式表的设计合理化（符合3NF），实际设计中不可能百分百遵守3NF 添加适当索引（index）普通索引，主键索引，唯一索引，全文索引 SQL语句优化分表技术（水平分割，垂直分割）读写分离存储过程对mysql配置优化（配置最大并发数my.ini,调整缓存大小） mysql服务器硬件升级定时去清除不需要的数据，定时进行碎片整理数据库三大范式第一范式：1...
复制链接

扫一扫