九、Mysql优化

引擎：
查看引擎：
mysql> show engines;
mysql> SHOW VARIABLES LIKE '%storage_engine%';
mysql> show create table t1;
mysql> show table status like 't1'; 

临时指定引擎：
mysql> create table innodb1(id int)engine=innodb;
修改默认引擎： 
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB  ----引擎
修改已经存在的表的引擎：
mysql> alter table t2 engine=myisam;

优化：

调优思路：
    1.数据库设计与规划--以后再修该很麻烦，估计数据量，使用什么存储引擎
    2.数据的应用--怎样取数据，sql语句的优化
    3.mysql服务优化--内存的使用，磁盘的使用
    4.操作系统的优化--内核、tcp连接数量
    5.升级硬件设备
    
只对innodb引擎
    物理分区:
    #mkdir /data
    #chown mysql.mysql /data
    mysql> create table t1(id int,name char(20)) data directory='/data/';----指定存储表的内容目录
    表结构存储的文件是.frm结尾的文件。不能指定。
如果创建不成功给data目录增加权限。chown mysql.mysql  data    

mysql服务优化
    mysql> show status         看系统的资源     ---各种状态参数。
    mysql> show variables    看变量，在my.cnf配置文件里定义的
    mysql> show warnings    查看最近一个sql语句产生的错误警告，看其他的需要看.err日志
    mysql> show processlist 显示系统中正在运行的所有进程。
    mysql> show errors

字符集设置
    临时：
        mysql> create database db1 CHARACTER SET = utf8;
        mysql> create table t1(id int(10)) CHARACTER SET = utf8;      
        
          5.7/ 5.5版本设置：
            [mysqld]
            character_set_server = utf8
===========================================================================================
慢查询：  
  查看是否设置成功：
    mysql> show variables like '%query%'; 

当连接数的数值过小会经常出现ERROR 1040: Too many connections错误。
这是是查询数据库当前设置的最大连接数
mysql> show variables like '%max_connections%';

这是查看超时时间
mysql> show global variables like '%timeout%';

强制限制mysql资源设置:
vim /etc/my.cnf
max_connections = 1024  并发连接数，根据实际情况设置连接数。
connect_timeout= 5   单位秒 ----超时时间，默认30秒

innodb引擎：       
   innodb-buffer-pool-size   //缓存 InnoDB 数据和索引的内存缓冲区的大小
innodb-buffer-pool-size=#   ----值     
        这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的 80%。
vim /etc/my.cnf
innodb-buffer-pool-size=2G
wait_timeout=10 终止空闲时间超过10秒的链接，避免长连接
max_connect_errors=10 //10次连接失败就锁定，使用flush hosts 解锁，

mysql> flush hosts;     #解锁 

#锁表
锁定数据表，避免在备份过程中，表被更新
mysql>LOCK TABLES tbl_name READ;
#为表增加一个写锁定：
mysql>LOCK TABLES tbl_name WRITE;

#查询是否锁表
mysql> show open tables where in_use >0;
#解锁
mysql> UNLOCK TABLES;