一、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