xp中mysql优化_MySQL优化

最新推荐文章于 2021-05-22 11:01:49 发布
最新推荐文章于 2021-05-22 11:01:49 发布
阅读量127 收藏
点赞数
文章标签： xp中mysql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_32641973/article/details/113901264
版权

51.索引的优缺点：可以增加查询效率，但降低update,insert,delete的效率，需要维护索引的结构。

2.5叉B树：key的数量[ceil(m/2)-1]<=n<=m-1，n>4时，中间节点向上分裂，两边节点分裂。

3.m叉BTree: 1.树中每个节点最多有m个孩子

2.除了根节点与叶子节点，每个节点至少有[ceil(m/2)]个节点

3.若根节点不是叶子节点，则至少有两个孩子

4.所有的叶子节点都在同一层

5.每个非叶子节点有n个key与n+1个指针组成，其中[ceil(m/2)-1]<=n<=m-1

4.叉B+Tree：1.最多有n个key，BTree最多有n-1个 key

2.B+Tree的叶子节点保存所有的key,依key的大小顺序排列

3.所有的非叶子节点都可以看做key的索引部分，所有的数据页保存在叶子节点，

4.叶子节点之间有指针，便于范围查询

5.create index idx_city_name on city (city_name); 创建索引

6.show index from city \G查看索引 create view view_city as select语句 创建视图 一张虚拟的表 简单 安全 数据独立

7.drop index idx_city_name on city 删除索引

8.alter table city add unique/primary idx_city_name(city_name); 创建唯一/主键索引

9.查询频度高，在where条件后选取最常用，过滤效果好的列，使用唯一索引，短索引，提升I/O效率，复合索引用最左前缀原则

10.存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。简化开发人员的工作，减少数据在数据库与服务器之间的传输。函数有返回值，过程没有 delimiter 声明SQL语句分隔符

11.create proceduce proceduce_name(in/out/inout 传入/输出/ num int，@decription/@@decription用户/系统会话变量)参数传递

`begin

//declare num int default 10;声明变量

set num=num+10;给变量赋值

select语句

end;创建存储过程call proceduce_name()调用存储过程

12.show/drop procudure status \G查看/删除存储过程 b

13.repeat 语句 util 满足条件 退出循环 end repeat;

c:loopset n=n-1;

if n<=0 then   loop循环。leave 退出

leave c;

end if;

end loop c;

14 .游标是用来存储查询结果集的数据类型

create procedure pro_test11()

begin

declare e_id int(10);

declare emp_result cursor for select查询语句 //声明游标

open emp_result;             //打开游标

fetch emp_result into e_id;//fetch只能一行一行获取   用循环获取

select concat('id=',e_id);

close emp_result;   //关闭

end;

15.触发器是与表有关的数据库对象，在增删改之前或之后，触发并执行触发器中定义的SQL集合

create trigger emp_insert_trigger

after insert

on emp

for each row

begin

insert into emp_logs(id,....) values(new.id) 修改前old 修改后 new

end;

16.存储引擎是存储数据，建立索引，更新查询数据等技术的实现方式。

Innodb 支持事务，外键，表锁,行锁(默认,适合高并发) MyISAM不支持事务，外键，表锁 访问速度高

开启事务 增加数据 不提交 查询不到 on delete restrict/on update cascade

MyISAM .frm存储表结构 .MYD存储数据 .MYI存储索引

Innodb frm存储表结构 .ibd存储数据索引

MEMORY .frm存储表结构 数据存储在内存 效率高

MERGE 是一组MyISAM表的组合

17 .查看SQL 执行效率 show (global)status like 'Com_____'/InnOdb_rows_%;查看增删改查的次数

18 .定位低效率执行SQL

1.慢查询日志 2.show processlist time(时间) state(状态) info(语句)

19.explain 分析执行计划

id: 相同是加载顺序从上往下 不同 值越大 优先级越高 越先被执行

select_type: simple 简单查询 不包含子查询 union

primary 包含子查询 最外层位次标志

subquery 在select where中包含子查询

derived 在from中包含子查询 递归执行 把结果放在临时表中

union 在第二个select 出现union

union result 从union 表获取结果   从上往下 ，效率越低

table: 数据来源于哪张表

type:     null 不查询任何表 select now();

SYSTEM 表中只有一条数据

const 通过一次索引就找到，只返回一条数据

eq_ref 多表关联查询 主键 唯一索引返回一条记录

ref 非唯一索引 返回多条数据

range 范围查询 between > < in

index 遍历索引树

ALL 遍历数据文件从上往下 ，效率越低

possible_key: 可能用到的索引

key: 实际用到的索引

key_len: 索引长度 越短越好

ref:

rows: 扫描的行

Extra: using filesort/temporary/index

20.show profile分析SQL

select @@having_profiling YES 支持

select @@prifiling  0未开启

show  profiles query_ID Duration(消耗时间) query(语句)

SHOW profile for query query_ID 具体

21.trace分析优化器执行计划 MYSQL5.6以后

set optimizer_trace="enable=on" end_markers_in_json=on;

set optimizer_trace_max_mem_size=100000

select *from information_achema.optimizer_trace\G;

22.(1) 复合索引的最左前缀原则是不跳过索引列，跟先后顺序无关 。

(2)范围查询后面的，索引会失效。

(3)在索引列上计算，索引会失效。

(4)varchar类型的要加单引号，否则索引失效。

(5)尽量用覆盖索引，不要select * using index condition 会回表查询整行数据。

(6)用or分割开的条件，or前用到索引，or后没用到，整体都不会用索引。

(7)like模糊查询 避免'%xsacs' 索引会失效，优化 select 索引列 from emp like '%xsacs'会走索引。

(8)全表扫描比索引快，则全表扫描，不走索引。例如address 是索引列，但99%是北京，1%是西安。

(9)is NULL 和is not NULL都不定会走索引，is NULL大部分是空会走全表扫描，is not NULL也一样。

(10)in 走索引(主键索引)，not in 不走索引。

(11)尽量使用复合索引(创建一个复合索引相当于创建多个单列索引)，少使用单列索引(会选择最优的一个索引)。

23.导入大批量数据时：主键有序的快，关闭唯一性校验。set unique_check=0;手动提交事务。

24.insert 优化insert into tb_test values (1,'Tom'),(2,'Jerry'),(3,'Cat');手动提交事务.

25.order by 优化(覆盖索引)using index比filesort效率高。多个字段时，字段顺序要与索引顺序一样；要么全升序，要么全降序。filesort max_length_for_sort_data>Query语句取出的大小(sort_buffer_size)使用一次性扫描算法，反之有二次扫描。

26.group by 优化排序后进行分组 不进行排序 order by null; 创建索引。

27.子查询优化用多表联合查询代替子查询。

28.or 优化or前后都用索引。or 不会使用复合索引， 用union 代替or.

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

29.分页查询优化1.在索引列上完成排序分页 2.主键自增 select * from tb_item where id>20000 limit 10;主键被删除会出现断层。

30.使用SQL提示

use/ignore index(index_seller_name)

force index(index_seller_name)强制使用该索引`

31.应用层面的优化

1.使用数据库连接池

2.减少对MYSQL的访问(避免对数据的重复访问)

３.增加cache层 (使用mybatis/Hibernate的一二级缓存,以及使用redis)

4.MYSQL集群　　读(从节点)写(主节点)分离

32. *Mysql 中查询缓存优化*

当执行相同的SQL语句时,服务器会从缓存中读取结果,当数据被修改时,之前的缓存会失效,修改频繁的表不是做查询缓存.

show variables like 'have_query_cache'; //是否支持查询缓存  ON /OFF

show variables like 'query_cache_type'; //是否开启  0/1 DEMAND(select SQL_(NO)_CACHE title fom emp where id=1;)

show variables like 'query_cache_size'; //查看缓存大小 size/1024/1024

show variables like 'Qcache%';           //查看查询缓存的状态信息(命中/增加次数,走与未走缓存数量)

33. *查询缓存失效的情况*

s/Select * from EMP;         //查询数据不一致

select now();                 //查询语句中有一些时不确定的

select 'A';                   //不使用任何表查询

select * from mysql/infomation_schame ;                     //查询系统数据库时

5.在存储函数,触发器的主体内查询

6.当表更改时,使用该表的所有高速查询缓存都将变为无效并被删除   insert/update/delete/drop

34.Mysql内存管理及优化

34.1MyISAM 存储引擎使用 key_buffer缓存索引块,加速myisam索引的读写速度

key_buffer_size=512M   //在/usr/my.cnf配置

read_buffer_size       //每个session独占 ,不能太大

read_rnd_buffer_size   //用于做排序的MYISAM表,如 order by 每个session独占 ,不能太大

34.2 InnoDB 用一块内存块做IO缓存池,会缓存数据块 ,索引块

innodb_buffer_pool_size=512M //在操作系统,内存足够可用的时候,设置的值越大,缓存命中越高,访问Innodb表需要的磁盘IO越少,性能越高

innodb_log_buffer_size=10M //增加值的大小,避免Innodb在提交事务时不必要把日志写入磁盘操作

35.Mysql并发参数调整

max_connections   //允许连接到mysql的最大连接数 默认值时151 linux平台支持500-1000不是难事

back_log           //请求数量大于max_connections,其余请求将被存在对栈中 50+(max_connections/5)

table_open_cache   //用来控制所有sql语句执行线程可打开表缓存的数量

thread_cache_size //控制mysql缓存客户服务线程的数量

innodb_lock_wait_timeout //用来设置Innodb事务等待行锁的时间,默认值是50ms

36.表级锁 :偏向ＭyISAM,开销小,加锁快,不会出现死锁,锁粒度大,发生所冲突的概率最高,并发度低;写优先

读锁(共享锁):  lock table EMP read; //同一客户端加锁后,只有释放才查询其他表

(读锁只阻塞其他线程的写操作,不会阻塞读操作)

写锁(排它锁):  lock table EMP write; //(写锁会阻塞其他线程的写操作,读操作)

show open tables; in use列 查看被锁定的表

show status like 'Table_lock%'; table_locks_waited 越高,锁争抢越严重

行级锁 :偏向Innodb,开销大,加锁慢,会出现死锁,锁粒度小,发生所冲突的概率低,并发度高;

索引失效,行锁会升级成表锁

间隙锁 ID自增出现断层 insert/update/delete时会加排他锁,对断层加间隙锁, 断层是添加不进来的

读锁(共享锁): select * from EMP where ...lock in share mode

​

写锁(排它锁): select * from EMP where ...for update  insert/update/delete会自动加写锁

37.并发事务处理带来的问题

1. 丢失更新: 当多个事务选择同一行,最初的事务修改的值,会被后面的事务修改的值覆盖.

脏读:一个事务对数据访问并修改但未提交,另一个事务访问数据并使用.

不可重复读: 一个事务在读取某些数据的某个时间,再次读取以前的数据,发现和以前读取的不一样(修改)

幻读:一个事务按照相同的查询的条件重新读以前查询过的数据,发现其他事务插入了满足条件的新数据(插入)

38.事务隔离级别

事务隔离级别越高,性能越低

隔离级别丢失更新脏读不可重复读幻读

Read uncommited

能解决

不能解决

不能解决

不能解决

Read commited

能解决

能解决

不能解决

不能解决

Repeatable read (默认)

能解决

能解决

能解决

不能解决

Serializable

能解决

能解决

能解决

能解决

39.SQL执行顺序 FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT >ORDER BY > LIMIT

SELELCT * FROM EMP WHERE NAME REGRXP '^s' / 'S$' / [unc]正则表达式的使用

40. *Mysql 常用工具*

mysql -u / -p / -h (主机)/ -p(端口) demo_03 -e 'select * from EMp; -e 执行语句

mysqladmin -uroot -proot create/drop 'demo01' version ;创建/删除数据库 查看版本

mysqlbinlog -vv mysqlbin.000001 查看二进制日志文件

mysqldump -uroot -proot demo_03 tb_book > tb_book.sql备份数据到tb_book.sql

mysqldump -uroot -proot demo_03 ----add -drop-table > tb_book.sql

mysqldump -uroot -proot -T /tep demo_03 tb_book在tem目录下生成.sql .txt 文件分别生成表结构和数据

mysqlimport -uroot -proot demo_03 /tem/tb_book.txt导入txt文本数据

resource /root/t'b_book.sql导入sql文件

mysqlshow -uroot -proot demo_03 tb_book --count(表的统计)/-i(表的详细状态信息)

41.Mysql日志

show variables like 'log_error%'; 错误日志 默认开启 查看日志地址

​

log_bin=mysqlbin   在/usr/my.cnf 二进制日志 记录DDL　DML(增删改无查询) 用于mysql复制生成

mysqlbin.000001以及mysqlbin.index

binlog_format=STATEMENT/ROW/MIXED 记录语句/变更信息/

​

Reset Master   删除并重新记录日志

purge master logs to 'mysqlbin.00006' 删除mysqlbin.00006编号之前的日志

purge master logs before 'yyyy-mm-dd hh24:mi:ss' 删除在这时间之前的日志

--expire_logs_days=#   设置日志过期天气

​

general_log =1       查询日志 记录所有的 查询 语句

general_log_file=query_log.log

​

slow_query_log = 1     慢查询日志 记录了所有执行时间超过参数long_query_time并且记录数不小于min_examined_row_limit的SQL语句的日志 效率低的语句

slow_query_log_file=slow_query.log

long_query_time=10   默认10秒   mysqldumpslow slow_query.log 查看日志

42.Mysql主从复制原理

Mater主库在事务提交时,会把数据变更作为时间jiluEvent记录在二进制Binlog中

主库推送二进制日志文件Binlog中的日志事件到从库的中继日志Relay Log

slave重做中继日志中的事件,将改变反应它自己的数据

秋冬将至
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
pl0语言的扩充之repeat-until语句
qq_46813387的博客
05-25 1628
pl0语言的扩充之repeat-until语句 repeat语句系列的扩充可以仿照while-do语句，但要注意repeat语句系列没有无条件跳转，while-do语句有。程序按顺序执行，在条件语句后存在一个条件跳转指令，跳转地址未定，当条件判断为假时回到repeat后面的循环开始位置，所以循环体语句开始位置要保存记录，当条件为真时，语句结束。 具体代码更改如下 头文件： #define norw 16 /* 关键字个数 */ 符号： /* 符号 */ enum symbol { nul
mysql 优化
qq_25952241的博客
11-25 1102
Show status; Threads_connected:当前的客户端已经连接的数量，这个值会少于预设值， Threads_running:记录了并发执行stmt/command的数量。正在运行。 Awk: 1.读取下一行，并把下一行赋给$0,各列赋给$1,$2, 2.用指定命令处理改行。 3.awk'/l/{printf("%s\n",$1)}' test.txt  mysql
RegExp_Like
菜鸟逆袭之路
08-04 1万+
--基本用法，是否包含某字符串 =like %36% select * from table where regexp_like(字段名,'条件'); if regexp_like(str,'^[0-9\.]+$') --只包含数字0-9，,小数点. --oracle判断字段是否是纯数字 (四种写法结果一样） select * from table where regexp_like(loginid,'^[0-9]+[0-9]$'); select * from table where reg.
regexp_like
cuigaijiao2361的博客
11-21 92
Oracle中的支持正则表达式的函数，主要有下面四个：（这里主要介绍REGGEXP_LIKE） 1，REGEXP_LIKE ：与LIKE的功能相似。 2，REGEXP_INSTR ：与INSTR的功能相似。 3，REGEX...
【oracle】正则表达式函数REGEXP_LIKE(x,pattern)
michiko98的博客
05-26 1万+
1.REGEXP_LIKE(x,pattern)函数 REGEXP_LIKE(x,pattern)函数的功能类似于like运算符， 用于判断源字符串是否匹配或包含指定模式的子串。 x指定源字符串， pattern是正则表达式字符串。该函数只可用在where子句中。 2.正则表达式规则 (1) 正则表达式通常是由普通字符和元字符组成。 (2) 元字符. 匹配任意单个字符，包括空格、 Tab字符甚至换行符。 (3)元字符[]描述值的集合。 --查找用户，要求last_name中含有’B’开头，第二.
oracle正则表达式regexp_like的用法详解
极客神殿
09-10 5万+
ORACLE中的支持正则表达式的函数主要有下面四个： 1，REGEXP_LIKE ：与LIKE的功能相似 2，REGEXP_INSTR ：与INSTR的功能相似 3，REGEXP_SUBSTR ：与SUBSTR的功能相似 4，REGEXP_REPLACE ：与REPLACE的功能相似 它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，...
mysql优化
liushangzaibeijing的博客
12-29 5万+
mysql的索引机制、索引类型、explain语法的说明
Mysql运行环境优化(Linux系统)
09-10
MySQL运行在Linux系统中，其性能受到操作系统环境的极大影响。为了提升MySQL的服务质量和响应速度，我们可以对Linux环境进行一系列的优化。以下是一些关键的优化步骤： 1. **修改IO调度算法**： Linux系统默认的IO...
MySQL_数据仓库指南.docx
05-21
- **操作系统环境**：书中示例已在Windows XP Professional SP2操作系统上使用MySQL 5.0.21版本进行测试。 - **适用对象**：面向数据仓库开发者、IT经理、MIS和DSS领域从业者、数据仓库初学者等。 #### 四、读者所...
asp中使用mysql数据库的注意实现
01-20
首先，环境配置为Windows XP Service Pack 2，MySQL版本5.0.18，使用MySQL ODBC 3.51 Driver，表引擎为MyISAM。与Access 2003相比，两者在数据插入和查询方面的性能有所不同。 1. 插入数据： - 在MySQL中，推荐...
mysql 5.1 for windows xp 经典版二合一
10-28
MySQL 5.1 for Windows XP 是一款专为微软操作系统设计的经典数据库管理系统，它结合了MySQL服务器和MySQL Workbench，提供了一体化的数据管理和开发环境。MySQL是一个开源的关系型数据库管理系统，以其高效、可靠和...
mysql.data.dll xp可用（版本6.3.7.0）
04-25
总的来说，"mysql.data.dll xp可用（版本6.3.7.0）"是一个专门为Windows XP系统优化的.NET Framework 4版MySQL数据库连接器，它为开发者提供了在老版本操作系统上构建和运行MySQL数据库应用的能力。通过熟悉和熟练...
mysql 在线修改参数_动态修改MYSQL配置参数
weixin_42138376的博客
01-27 759
mysql数据库也像ORACLE数据库一样，可以动态的修改参数，可以修改会话级变量只对当前会话产生影响；也可以修改全局变量，对所有新连接的会话都产生影响。修改会话级变量用show variables 命令查看当前参数的值,like 'pattern'用于模式匹配，查找指定的参数mysql> show variables like '%sort_buffer_size%';+---------...
c语言repeat语句的用法,关于Lua中的repeat...until循环语句的使用教程
weixin_34653945的博客
05-22 3253
关于Lua中的repeat...until循环语句的使用教程这篇文章主要介绍了Lua中的repeat...until循环语句使用教程,是Lua入门学习中的基础教程,需要的朋友可以参考下与for和while循环不同，在循环的顶部测试循环条件，Lua编程语言的repeat...until 循环检查循环底部的状态。repeat...until 循环类似于while循环，不同的是do ... while循...
正则表达式regexp_like的用法详解
weixin_43792309的博客
10-03 4万+
正则表达式regexp_like的用法详解ORACLE中的支持正则表达式的函数主要有下面四个： 1，REGEXP_LIKE ：与LIKE的功能相似 2，REGEXP_INSTR ：与INSTR的功能相似 3，REGEXP_SUBSTR ：与SUBSTR的功能相似 4，REGEXP_REPLACE ：与REPLACE的功能相似 它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同， 但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。
mysql循环（while..do,loop..leave,repeat..util)
龟的小号的博客
01-23 4783
文章目录一、while..do二、loop..leave三、repeat..until 求和：计算从1到n的和 一、while…do while…do相当于java中的whlie循环 delimiter// create function testsum(num int) returns int begin declare i int default 0; declare sum int defa...
Oracle中RegExp_Like 正则表达式基本用法
热门推荐
wh445306
11-06 7万+
--基本用法，是否包含某字符串 =like %36% select * from k_micfo where regexp_like(loginid,'36'); if regexp_like(str,'^[0-9\.]+$') --只包含数字0-9，,小数点. --oracle判断字段是否是纯数字 (四种写法结果一样） select * from k_micfo where regex...
[毕业设计]VB Access就业指导与失业保险发放系统(源代码+论文撰写+答辩演示文稿).zip
最新发布
08-07
[毕业设计]VB Access就业指导与失业保险发放系统(源代码+论文撰写+答辩演示文稿)
微信开发资源ddddddd
08-07
微信开发资源ddddddd
Windows XP下IIS5.1配置PHP5.2.1与MySQL5.0.37环境指南
"本教程主要介绍如何在Windows XP上的IIS5.1环境中配置PHP，使用的软件版本包括PHP5.2.1、MySQL5.0.37、phpMyAdmin2.10.0.2和ZendOptimizer-3.2.6。" 在Windows XP系统中配置PHP环境，首先需要安装IIS（Internet ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值