MySQL数据库优化

数据库优化原理？数据库优化是优化什么？
数据库优化是对数据库索引的优化，索引相当于书的目录，对于实际数据查询中常常由于各种原因导致索引失效或者索引的使用的级别不高从而导致数据查询较慢。
索引：index是帮助MySQL高效获取数据的数据结构。（实际结构为B+数）
索引并不是所有情况都使用，索引本身也占用一点空间，对于数据量较少时候使用索引并不能起到优化的效果。
索引的分类：
单值索引：数据库库表中某个单列如Person表中的age列，一个表可以有多个单值索引
唯一索引：数据不能重复的某列 如id
复合索引：类似于书的多级目录
创建索引：
方式一：create 索引类型 索引名 on 表名（字段）
单值：create index dept_index on tb(dept)
唯一索引：create unique index name_index on tb(name)
复合索引：create index dept_name_index on tb(dept,name)
方式二：alter table 表名 add 索引名（字段）
单值：alter table tb add index dept_index(dept);
唯一：alter table tb add unique index name_index(name)
复合索引：alter table tb add index dept_name_index(dept,name)
注意：如果一个字段是primary key,则该字段默认是 主键索引（不能为null的唯一索引）
删除索引：
drop index 索引名 on 表名；
drop index name_index on tb;
查询索引
show index from 表名;
SQL性能问题
explain + SQL语句 会得到一段数据可以查看SQL的执行情况

id：编号 ，多表相连且ID相同时候，表示从上到下依次执行，ID不同时则按照大到小的顺序执行
select_type：查询类型
primary表示主查询 subquery子查询 simple简单查询 （不包含子查询、union的查询） derived：衍生查询（使用到了临时表）
table：表
tyoe:类型 表示索引的效率，system>const>eq_ref>ref>range>index>all
possible_keys：预测用到的索引
key：实际用到 的索引
ken_len：实际使用索引的长度
ref：表之间的引用
rows:通过索引查询到的数据量
Extra：额外的信息 using filesort: 性能消耗大；需要“额外”的一次排序（查询）
using filesort出现的情况
1.单索引 select * from test 02 where al = ’ ’ order by a2
如果排序和查找不是同一个字段，则不会出现using filesory,
2.复合索引：不能跨列（最佳左前缀）
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3)
select * from test02 where a1=’ ’ order by a3 —using filesort
避免：where 和order by 按照复合索引的顺序使用，不要跨列或无序使用，索引在数据库底层实现是如下B+树，如果跨列或者无线使用，相当于其中35和55和在一起使用，而二者不相连，故会导致性能边查出现using filesort，单表查询也是一样的情况

using temporary::已经有表了但不适用需要额外多使用一张表，性能损耗大，用到了临时表。一般出现在 ground by 语句
出现：SELECT * FROM test02 WHERE a1 IN (‘1’,‘2’,‘3’) GROUP BY a2 — using temporary
从SQL语句解析过程理解
解析过程：from…on…join…where…group by…having… select dinstinct…order by limit …
explain select * from test03 where a2 = 2 and a4=4 group by a3
先执行where用到了一个表 然后group by又用到了一个表
单表优化

 多表优化：
 	情况一：如：SELECT  t.tid FROM teacher2 t LEFT OUTER JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'Java'
 	1.要给字段加上索引 t.tid t.cid c.cid
 	2.左外联时要尽量让左表是数量比较小的表

索引添加 原则之一：小表驱动大表，类似双层for循环 小的在外层大的在内层
#将表数量较小的写在左边，大的写在右边 t.cid = c.cid (尽量在表t数量小于表c)
#索引如何简历：建立在经常使用的字段上，（双层for循环外面一层使用的次数肯定远大于里面一层）应当建立在左边的字段 t.cid
对于左外连接给左表加索引 右外连接给右边加索引

#避免索引失效的原则
#a.复合索引，不要跨列或无效使用（最佳左前缀）
#b.复合索引，尽量使用全索引匹配
#c.不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
# select … where A.x = …; A.x是索引 不要 出现A.x*3之类的
#d.复合索引不能使用不等于（！= <>）或 is null （is not null）否则失效
#e.like尽量以“常量”开头，不要以“%”开头，否则可能会索引失败
#select * from xx where name like ‘%x%’; ()
#f.尽量不要使用类型转换，否则使得索引失效
EXPLAIN SELECT * FROM teacher WHERE tname = ‘123’
EXPLAIN SELECT * FROM teacher WHERE tname = 123 #底层会将123转化为字符串
#尽量不要使用or，否则索引失效
#其他优化 exist和in select …from table where exist/in(子查询)
#如果主查询的数据集大，则使用in
#如果子查询的数据集大，则使用exist
#exist语法：将主查询的结果，放到子查询中进行校验，成功就保留

#SQL排查，—慢查询日志 查看是否开启 show variables like ‘%low_query_log%’;
#临时开启：set global slow_query_log = 1; —在内存中开启
SHOW VARIABLES LIKE ‘%low_query_log%’;
#慢查询阈值：
SHOW VARIABLES LIKE ‘%long_query_time%’
#临时设置阈值
SET GLOBAL long_query_time=5 # 不会立即生效
#慢查询SQL语句数量
SHOW GLOBAL STATUS LIKE ‘%slow_query%’;

查看在文件里 cat /var/lib/mysql/localhost-slow.log

#锁 读锁（共享锁），多个线程可同时读,但只允许一个进行修改

写锁（互斥锁），如果当前写操作没完成，则无法进行其他的读/写操作

myisam 表锁 innerDB 行锁（开销大）

#加锁语法
#locak table 表名 read/write
#查看加锁的表 show open tables

#若某个会话对表 table1（读锁）进行加了读锁，整个会话只能对table1进行读操作，不能进行写，而且不能对其他表进行操作（读/写都不行）。
#释放锁 unlock tables

#MySQL表级锁的模式
#MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动加写锁

#行锁主义事项
#a.如果没有索引，则行锁会转为表锁

#主从同步运来
#1.远程连接数据库，远程授权访问
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
#刷新访问权限
FLUSH PRIVILEGES

#原理：主数据库里面有一个二进制日志文件 记录了所有的增删改操作，从数据库通过一个io线程，读取中这个文件到
#从数据库的Relay log 再通过一个SQL线程实现

#配置 window(mysql:my.ini) linux(mysql:my.cnf)
#主计算机配置在[mysqld]下配置
#主机id
SERVER-id=1
#二进制日志文件（记录操作的）
LOG-BIN = “D:/mysql/data/mysql-bin”
#错误记录文件
LOG-error=“D:/mysql/data/mysql-error”
#主从同步时候忽略的数据
BINLOG-IGNORE-db=mysql
#(可选)指定主从同步时，同步那些数据库
BINLOG-DO-db=test

#主计算机授权那台计算机是自己的从计算机
GRANT REPLICATION SLAVE,RELOAD,SUPER ON . TO ‘root’@‘192.168.2.%’ IDENTIFIED BY ‘root’
#刷新访问权限
FLUSH PRIVILEGES
#查看主计算机状态
SHOW MASTER STATUS

#从计算机配置在[mysqld]下配置
#主机id
SERVER-id=2
#二进制日志文件（记录操作的）
LOG-BIN = “D:/mysql/data/mysql-bin”
#错误记录文件
LOG-error=“D:/mysql/data/mysql-error”
#从数据库配置要同步那些数据库
replicate-DO-db=test
#从数据库也需要授权谁是主计算机
CHANGE MASTER TO
MASTER_HOST = ‘192.168.2.2’,
MASTER_USER = ‘root’,
MASTER_PASSWORD = ‘root’,
MASTER_PORT = 3306,
MASTER_LOG_FILE = ‘mysql-bin.000001’,
MASTER_LOG_POS = 107;#107是主计算使用 show master status查出来的pos数据

#开启主从同步 从机linux;
START SLAVE;
#检验show slave status \G 看关键数据
#Slave_IO_Running 和Slave_SQL_Running确保两者都是yes 若不是yes 则看Last_IO_Error数据发现错误原因