mysql高性能sql语句_高性能MySQL

一、MySql逻辑架构图

Client-----> 连接/线程处理器 ----> {[解析器]---->[查询缓存]}{解析器}----->优化器------>存储引擎

information_schema(这张数据表保存了MySQL服务器所有数据库的信息)

select * from INFORMATION_SCHEMA.[TABLES]

SCHEMATA表：提供了关于数据库的信息。

TABLES表：给出了关于数据库中的表的信息。

COLUMNS表：给出了表中的列信息。

STATISTICS表：给出了关于表索引的信息。

USER_PRIVILEGES表：给出了关于全程权限的信息。该信息源自mysql.user授权表。

SCHEMA_PRIVILEGES表：给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。

TABLE_PRIVILEGES表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。

COLUMN_PRIVILEGES表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。

CHARACTER_SETS表：提供了关于可用字符集的信息。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。

TABLE_CONSTRAINTS表：描述了存在约束的表。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序(存储程序和函数)的信息。此时，ROUTINES表不包含自定义函数(UDF)。

VIEWS表：给出了关于数据库中的视图的信息。

TRIGGERS表：提供了关于触发程序的信息。

二、事务

MySql中支持事务型的引擎：InnoDB、XtraDB

MySql默认采用自动提交模式

SHOW VARIABLES LIKE 'AUTOCOMMIT'                    SET  AUTOCOMMIT = 1

显示加锁

1、SELECT ... LOCK IN SHARE MODE

2、SELECT ... FOR UPDATE

MVCC(多版本并发控制)以乐观锁、悲观锁方式实现

InnoDB  MySql默认的事务引擎，适合大量短期事务，自动崩溃恢复

MyISAM 用于只读数据查询

三、单条查询语句的性能剖析

SHOW VARIABLES LIKE 'PROFILING';

SET PROFILING = TRUE

SHOW PROFILES

SHOW PROFILE FOR QUERY 1

四、计数器统计查询

FLUSH STATUS

SHOW STATUS

五、线程连接状态查询

SHOW PROCESSLIST

六、数据类型

1、更小的数据类型

2、简单的数据类型更好(整形比字符串好)

3、避免NULL值

七、索引(特指B-Tree)

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引(用于单个列条件查询)

组合索引：即一个索包含多个列(用于多个列条件查询，有顺序关系，{全值匹配、匹配最左列[全值]、[前缀]、[范围]}索引有效，如果联合索引的第一个列不在where条件语句中，并且所查询的列其中有的是没有建立索引的，那么这个联合索引就是无效的)

索引的优点

1、减少数据库扫描啊的数据量

2、减少排序操作消耗、避免临时表

3、可以将随机I/O变为顺序I/O

避免索引失效操作

1、索引列不能是表达式的一部分，也不能是函数的参数

2、条件中有or，并且条件中不全是索引列

3、单独引用复合索引里非第一位置的索引列.

4、like查询是以%开头匹配方式

5、隐式转换导致索引失效(字符串和数字之间的隐式转换)

6、<>   !=  不等于

7、B-tree索引 is null不会走,is not null会走

8、not in ,not exist

索引覆盖(一个索引包含了查询所有需要的字段，查询就不会去表中查询数据行，提升查询效率)

表损坏修复

1、CHECK TABLE 表名；

2、REPAIR TABLE 表名；

3、对于不支持REPAIR操作，可以设置表的引擎为当前引擎，例：InnoDB(ALTER TABLE 表名 ENGINE = InnoDB)

减少索引和数据的碎片

1、OPTIMIZE TABLE 表名；

2、对于不支持OPTIMIZE操作，可以设置表的引擎为当前引擎，例：InnoDB(ALTER TABLE 表名 ENGINE = InnoDB)

八、查询性能优化

1、查询了不需要的数据

2、分析了大量超过需要的数据

EXPLAIN 中的type反应了查询访问类型(全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用    慢----->快)

如果查询没有找到合适的访问类型，解决的方法通常是建立一个合适的索引

分解查询 (有事候可以将一个复杂查询拆分成几个简单查询)

MySQL客户端和服务器端之间的通信协议是“半双工”的

在必要的时候一定要在查询中加上LIMIT限制

优化器提示语

SQL_SMALL_RESULT和SQL_BIG_RESULT只对SELECT语句有效，告诉优化器对GROUP BY 或者 DISTINCT查询如何使用临时表及排序

SQL_CACHE和SQL_NO_CACHE告诉优化器是否需要缓存查询结果集

USE INDEX 和 IGNORE INDEX 和 FORCE INDEX建议优化器索引的使用

EXPLAIN中返回的行数可以近似COUNT(*)返回的结果

SET @ONE := 1  自定义变量

九、MySQL高级特性

PARTITION(分区表，用于超大量数据的表)

SHOW CREATE TABLE 表名 (展示创建的初始语句)

内部存储代码(触发器、存储过程、函数、定时任务)

十、优化服务器配置

强烈要求是使用默认的配置，如果某些特定的问题由配置项导致，可以修改相应的配置项解决问题

查找MySQL配置文件

方式一、[which mysqld 结果] --verbose --help | grep -A 1 'Default options'

方式二、whereis my.cnf

十一、MySQL慢查询

1、SHOW VARIABLES LIKE 'slow%'

slow_launch_time：表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

slow_query_log：慢查询日志开关ON开启，OFF关闭

slow_query_log_file：日志保存路径

2、SHOW VARIABLES LIKE 'long%'

long_query_time：达到多少秒的sql就记录日志

3、在my.conf下配置慢查询日志

slow_query_log=ON

slow_query_log_file=/var/lib/mysql/localhost-centos-slow.log

long_query_time=3

十二、EXPLAIN

EXPLAIN (模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈)

explain SQL查询语句;

id：代表优先级  id值越大，越先执行，id值相同，从上往下执行

select_type：表示select类型 取值如下

simple 简单表 即不使用表连接或者子查询

primary 包含union或者子查询的主查询 即外层的查询

union UNION中的第二个或者后面的查询语句

subquery 一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询

derived 派生表 该临时表是从子查询派生出来的

type：表示MySQL在表中查找数据的方式，或者叫访问类型，以下对于type取值的说明 从上往下性能由最差到最好

all:全表扫描，MySQL遍历全表来找到匹配的行

index：索引全扫描，MySQL遍历挣个索引来查询匹配的行

range：索引范围扫描，常见于、>=、between等操作符

ref：使用非唯一索引或唯一索引的前缀扫描，返回匹配的单行数据

eq_ref：类似ref，区别就在于使用的索引是唯一索引，简单来说，就是多表连接中使用primary key或者unique index作为关联条件。

const/system：单表中最多有一个匹配行，查询起来非常迅速，常见于根据primary key或者唯一索引unique index进行的单表查询

null：mysql不用访问表或者索引，直接就能够得到查询的结果，例如select 1+2 as result。

possible_keys：表示查询时可能使用的索引

key：表示实际使用的索引

key_len：使用到索引字段的长度

rows：扫描数量

Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息，常用取值如下：

Using index：直接访问索引就取到了数据，高性能的表现。

Using where：直接在主键索引上过滤数据，必带where子句，而且用不上索引

Using index condition：先条件过滤索引，再查数据，

Using filesort：使用了外部文件排序 只要见到这个 就要优化掉

Using temporary：创建了临时表来处理查询 只要见到这个 也要尽量优化掉

十三、MySQL主备复制

1、在主库把数据更改记录到二进制日志文件中

2、备库将主库日志文件复制到自己的中继日志中

3、备库读取中继日志中的事件，将其重放到备库数据上

(1)创建复制账号

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword';

(2)配置主库和备库

1、主库my.cnf文件新增配置，重启主库，SHOW MASTER STATUS;查看配置结果

log_bin = mysql-bin  --二进制文件名

server_id = 10  --服务器ID

2、备库my.cnf文件新增配置，重启服务器，SHOW LAVE STATUS;查看配置结果

log_bin = mysql-bin  --二进制文件名

server_id = 2   --服务器ID

relay_log = /var/lib/mysql/mysql-relay-bin  --中继日志

log_slave_updates = 1 --允许备库将重放事件记录到自己的二进制日志文件中

read_only = 1  --只读

(3)备库连接主库

CHANGE MASTER TO MASTER_HOST='server1',   --主库地址

MASTER_USER='rep1',    --用户名

MASTER_PASSWORD='p4ssword',    --密码

MASTER_LOG_FILE='mysql-bin.000001',   --二进制文件

MASTER_LOG_POS=0;        --从日志开头读起

(4)启动复制

START SLAVE;   --启动

SHOW SLAVE STATUS;  --查看状态

十四、MySQL数据备份和恢复

1、备份

mysqldump -hhost  -Pport -uusername -ppwd dbname [table1] [table2] > backup.sql

2、恢复

mysql -hhost  -Pport -uusername -ppwd dbname < backup.sql