数据库优化

一、性能下降SQL慢

1. 执行时间长
   1. 查询语句烂
   2. 索引失效：单值索引，复合索引
   3. 关联查询太多join
   4. 服务器调优及各个参数设置（缓冲、线程数）
2. 等待时间长

二、常见通用的join查询

SELECT DISTINCT
    <select_list>
FROM
    <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT <limit number>

 查询顺序

• 1    FROM <left_table>
• 2    ON <join_condition>
• 3    <join_type> JOIN <right_table>
• 4    WHERE <where_condition>
• 5    GROUP BY <group_by_list>
• 6    HAVING <having_condition>
• 7    SELECT
• 8    DISTINCT <select_list>
• 9    ORDER BY <order_by_condition>
• 10  LIMIT <limit_number>

 

//左连接
SELECT <select_list> FROM Table A LEFT JOIN Table B ON A.Key = B.Key;
//右连接
SELECT <select_list> FROM Table A RIGHT JOIN Table B ON A.Key = B.Key;
//内连接
SELECT <select_list> FROM Table A INNER JOIN Table B ON A.Key = B.Key;
//全连接
SELECT <select_list> FROM Table A FULL JOIN Table B ON A.Key = B.Key;
//mysql没有全连接 等价于下面的语句
SELECT <select_list> FROM Table A LEFT JOIN Table B ON A.Key = B.Key;
UNION
SELECT <select_list> FROM Table A RIGHT JOIN Table B ON A.Key = B.Key;

SELECT <select_list> FROM Table A LEFT JOIN Table B ON A.Key = B.Key WHERE B.Key = NULL;
SELECT <select_list> FROM Table A RIGHT JOIN Table B ON A.Key = B.Key WHERE A.Key = NULL;

三、索引简介

本质是一种数据结构（排好序的数据结构），目的是提高查找效率，可类比字典；

索引两大功能，查找和排序，mysql中使用B+树构建索引；

一般索引本身也很大，往往以索引文件的形式存储在磁盘中

优势：提高数据检索效率，降低数据库的IO成本；降低数据排序成本，降低CPU的消耗

劣势：占用空间大；降低表更新速度（INSERT、UPDATE和DELETE）；

索引类别

1. 单值索引
2. 唯一索引
3. 复合索引
4. 基本语法

--创建
CREATE [UNIQUE] INDEX indexName ON mytable(coiumnName(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
--删除
DROP INDEX [indexName] ON mytable;
--显示
SHOW INDEX FROM table_name\G
--添加索引
ALTER TABLE tableName ADD PRIMARY KEY (column_list);--主键索引，索引值必须唯一，不能为NULL
ALTER TABLE tableName ADD UNIQUE index_name (column_list);--添加唯一索引
ALTER TABLE tableName ADD INDEX index_name (column_list);--添加普通索引
ALTER TABLE tableName ADD FULLTEXT index_name (column_list);--指定索引为FULLTEXT,用于全文索引

哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应当创建索引
3. 查询中与其他表不关联的字段，外键关系建立索引
4. 高并发倾向创建复合索引
5. 查询中排序的字段建立索引
6. 查询中统计或分组的字段建立索引
7. 经常曾删改的表不建立索引
8. 重复且平均分配的字段不建立索引

四、性能分析

1. MySQL常见瓶颈
   1. CPU：CPU饱和状态一般是数据读取或写磁盘的时候
   2. IO：磁盘I/O瓶颈在装入数据源大于内存容量的时候
   3. 服务器硬件的性能瓶颈：top，free，iostat和vmstat查看系统的性能状态
2. Explain：模拟优化器执行SQL语句，可分析查询语句和表结构的性能瓶颈
   1. 使用：explain + 查询语句
   2. 可查询表的读取顺序，数据读取操的作类型，哪些索引可以使用，哪些索引被使用，表之间的引用，每张表有多少行被优化查询
   3. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
      1. id   sql执行顺序，id越大，越先执行
         1. id都相同，从上到下依次执行；
         2. id都不同，id越大，越先执行；
      2. select_type  数据读取操作的类型
         1. SIMPLE：简单select查询，查询中不包含子查询或者UNION；
         2. PRIMARY：查询中若包含任何复杂的子查询，最外层的查询；
         3. SUBQUERY：在select或者where中包含的子查询；
         4. DERIVED：在from列表中包含是子查询被标记为DERIVED，MySQL递归执行这些子查询，放到临时表中;
         5. UNION：若第二个select出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的查询中，外层SELECT被标记为DERIVED;
         6. UNION RESULT：从UNION表获取结果的SELECT;
      3. type 数据访问类型 system > const > eq_ref > ref > range > index > ALL 
         1. ALL：全表扫描；
         2. index：与ALL的区别为index类型只扫描索引树；
         3. range：范围查询，使用一个索引来选择行；where中出现between,<,>,in等查询，从某个索引点开始，不用扫描全部索引；
         4. ref：唯一性所以扫描，返回匹配某个单值是所有行，属于查找和扫描的混合体；
         5. eq_ref：唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描；
         6. const：通过索引一次就可以查到，一般是用在主键索引或者唯一索引上；
         7. system：表中只有一行记录（等于系统表）这是const类型的特例，一般不会出现；
         8. 一般达到range级别，最好达到ref；
      4. possible_keys 可能会用到的索引;
      5. key 使用到的索引;
         1. 若为NULL，表示没有用到索引;
         2. 若查询中使用了覆盖索引（查询的列与索引一致），则该索引仅出现在key类表中；
      6. key_len  表示索引中使用的字节数，值为索引字段最大长度可能值，并非实际使用长度;
      7. ref  显示索引的哪一列被使用，最好是一个常数;
      8. rows  根据表统计信息及索引选取的情况，大致估算出找到所需记录需要读取的行数;
      9. Extra 重要信息
         1. Using filesort 文件内排序 ，MySQL中无法利用索引完成的排序;
         2. Using temporary 使用临时表保存中间结果，常见与order by 和分组查询group by;
         3. Using index 表示相应的select使用了覆盖索引，出现using where表示索引用来执行索引键值的查找，若没有，表明所用用来读取数据而非执行查找动作;
         4. Using where 表明使用了where过滤;
         5. using join buffer 使用了链接缓存，出现时，链接缓存可以适当的调大;
         6. impossible where where句子是false，不能用来获取任何元组;

 覆盖索引（Covering Index）

select数据列只用从索引中获取，无需从数据库中查找，查询的列被索引覆盖;

五、优化索引

1. 索引分析
   1. 单表
   2. 两表  左连接索引加右表，右链接索引加左表;
   3. 三表   使用小表驱动大表;
2. 索引失效（范围查询后的索引会失效）
   1. 最佳左前缀原则：查询从索引的最左前列开始，且不跳过索引是中间列;
   2. 不在索引上左任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效二转向全表扫描;
   3. 存储引擎不能使用索引中范围条件右边的列（<、>）;
   4. 尽量使用覆盖索引（索引列和查询类一致），减少使用select *；使用范围查询时，可以避免索引失效，使用using index;
   5. 在使用不等于（!=、<>）时无法使用索引而导致全表扫描;
   6. is null，is not null也无法使用索引；
   7. like以通配符开头索引失效会变成全表扫描（like%在右边索引不会失效），可使用覆盖索引解决以%开头的问题；
   8. 字符串不加单引号索引会失效；
   9. 少用or，or会造成索引失效；
   10. order by时给范围，group by基本都需要进行排序，会产生临时表
3. 一般建议
   1. 单值索引，尽量选择针对当前query过滤行好的索引
   2. 复合索引，当前query中过滤性好的字段在索引中的顺序，位置越靠前越好
   3. 复合索引，尽量选择能够包含当前query中where中字段多的索引

六、查询截取分析

1. 查询优化
   1. 永远小表驱动大表   in 和exists性能区分
   2. order by关键字优化
      1. order by子句尽量使用index方式排序，避免使用filesort方式排序：filesort和index两种排序方式index效率高；
      2. 尽可能在索引列上完成排序操作，遵循最佳左前缀原则；
      3. 若不在索引列上，filesort有两种算法
      4. order by时，尽量不要使用select *；尝试提高sort_buffer_size的大小；尝试提高max_length_for_sort_data的大小
   3. group by关键字优化
      1. 先排序，后分组，遵循最佳左前缀原则
      2. 无法使用索引列时，增大max_length_for_sort_data和sort_buffer_sized设置
      3. 能在where中搞定的不用用having
2. 慢查询日志
   1. 默认MySQL数据库没有开启慢查询日志，一般不建议开启（会影响性能），除非调优需要
   2. 查看慢查询配置 show variables like '%slow_query_log%'；set global slow_query_log=1;若要永久生效，需修改my.cnf文件
   3. 查看慢查询控制时间 show variables like ‘long_query_time%’;修改同上一直，修改后需重新开启会话；
   4. 查询当前系统有多少慢查询语句 select global statuts like '%Slow_queries%';
   5. 日志分析工具mysqldumpslow；
      1. 记录集最多的10 个SQL：mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log
      2. 访问次数最多的10个SQL：mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log
      3. 按时间排序，前10条含左连接的SQL：mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xxx-slow.log
      4. 查新记录较多时，可结合more使用：mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log|more
      5. 
3. 批量数据脚本
   1. 设置log_bin_trust_function_creators
   2. 创建函数
   3. 创建存储过程
   4. 调用   DELIMTER;   CALL 函数名\存储过程
4. show profile：MySQL提供用来分析当前会话中语句执行的资源消耗情况
   1. 查看是否支持：show variables like 'profiling';一般默认关闭，需要开启
   2. 查看结果 show profiles；
   3. 诊断SQL，show profile cpu ,block io for query Query_ID;
   4. 需注意结论
      1. converting HEAP to MyISAM 查询结果太大，内存不够用往磁盘上般
      2. Creating tmp table 创建临时表
      3. Copying to tmp table on disk 把内存中临时表复制到磁盘
      4. locked 死锁
5. 全局查询日志
   1. 配置 set global general_log=1;set global log_output='TABLE';或者在My.cnf中配置
   2. 使用 select * from mysql.general_log;
   3. 永远不要在生产环境使用
6. 行锁
   查看行锁状态：show status like 'Innodb_row_lock%';
   Innodb_row_lock_corrent_waits：当前正在等待锁定的数量
   Innodb_row_lock_time：从系统启动到现在锁定的总时间
   Innodb_row_lock_time_avg：平均每次等待需要的时间
   Innodb_row_lock_time_max：等待最长一次的时间
   Innodb_row_lock_waits：等待的总共次数

七、主从复制

slave会从master读取binlog进行数据同步

1. master将改变记录到二进制日志中，记录过程叫二进制日志事件
2. slave将master的二进制日志事件拷贝到中继日志（relay log）中
3. slave重做中继日中的事件，将改变应用到自己的数据库中，MySQL复制时异步且串行化的

主机（master）192.168.150.130；从机（slave）192.168.150.134；

主机配置

my.cnf（或者my.ini），一般位于/etc/my.cnf，在[mysqld]部分修改

#开启二进制日志
log-bin=mysql-bin
server-id=1
#设置保留binlog天数
expire_logs_days=7
#binlog日志记录指定更新的数据库
#binlog-do-db=db_name
#binlog日志不更新的数据库
#binlog-ignore-db=db_name

 修改配置项后要重启MySQL服务：systemctl restart mysqld；

 创建用户-->分配权限-->刷新数据库-->查看master状态

#删除之前的用户
mysql> drop user 'root'@'192.168.150.134';
Query OK, 0 rows affected (0.00 sec)

#创建用户，用于主从复制
mysql> CREATE USER 'root'@'192.168.150.134' IDENTIFIED WITH mysql_native_password BY 'MySQL@123';
Query OK, 0 rows affected (0.00 sec)

#分配权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.150.134';
Query OK, 0 rows affected (0.00 sec)

#刷新数据库
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

#查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     2059 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录二进制文件名（mysql-bin.000001）和位置（2059）；

从机配置

同样要修改my.cnf配置文件，server-id不能和主机的一样，要唯一，这里设置为：server-id=2

重启MySQL服务：systemctl restart mysqld；

建立链接

mysql> CHANGE MASTER TO
    -> MASTER_HOST='182.92.150.130',
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='MySQL@123',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=2059;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

#刷新数据库
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

#启动slave同步进程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

 查看slave状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.150.130
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2059
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
              ......

 Slave_IO_Running和Slave_SQL_Running必须同时为Yes；主机和从机的防火墙要关闭或者开启相应的通道