MYSQL问题答疑

MYSQL问题答疑

1.在项目中数据库性能下降，想找到哪些sql耗时较长，应该如何操作? my.cnf里如何配置?

1、show processlist;

2、select * from information_schema.processlist ;

3、可以在[mysqld]中添加如下：

log =/var/log/mysql.log
　　　　 如果需要监控慢查询可以添加如下内容：
　　　　 log-slow-queries = /var/log/slowquery.log
　　　　 long_query_time = 1

2.主从同步报错：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

原因在于，拷贝整个data目录，把auto.cnf文件也拷贝过来了，里面记录了数据库的uuid，每个库的uuid应该是不一样的。

[root@bogon ~]# find / -name auto.cnf

/var/lib/mysql/auto.cnf

[root@bogon ~]# vi /var/lib/mysql/auto.cnf

[auto]
server-uuid=6dcee5be-8cdb-11e2-9408-90e2ba2e2ea6

解决办法，按照这个16进制格式，随便改下，重启mysql即可[两台机器时复制的]

3.MYSQL问题解决方案：Access denied for user ‘root’@‘localhost’ (using password:YES)

1. 打开MySQL目录下的my.ini文件，在文件的最后添加一行“skip-grant-tables”，保存并关闭文件。（WIN7默认安装，my.ini在C:\ProgramData\MySQL\MySQL Server 5.6）

2. 重启MySQL服务。

3. 通过命令行进入MySQL的BIN目录，输入“mysql -u root -p”(不输入密码)，回车即可进入数据库。（WIN7默认安装，BIN目录为：C:\Program Files\MySQL\MySQL Server 5.6\bin）

4. 执行“use mysql;”，使用mysql数据库。

5. 执行“update user set password=PASSWORD(“123456”) where user=‘root’;”（修改root的密码）

6. 打开MySQL目录下的my.ini文件，删除最后一行的“skip-grant-tables”，保存并关闭文件。

7. 重启MySQL服务。

8. 在命令行中输入“mysql -u root -p 123456”，即可成功连接数据库。

4.在项目中如何进行MySQL调优?

1 硬件配置优化

1.1 CPU选择：多核的CPU，主频高的CPU
　　　　1.2 内存：更大的内存
　　　　1.3 磁盘选择：更快的转速、RAID、阵列卡，
　　　　1.4 网络环境选择：尽量部署在局域网、SCI、光缆、千兆网、双网线提供冗余、0.0.0.0多端口绑定监听

2 操作系统级优化

2.1 使用64位的操作系统，更好的使用大内存。
　　　　2.2 设置noatime,nodiratime
　　　　2.3 优化内核参数
　　　　2.4 加大文件描述符限制
　　　　2.5 文件系统选择 xfs

3 Mysql设计优化

3.1 存储引擎的选择
　　　　　　3.1.1 Myisam：数据库并发不大，读多写少,而且都能很好的用到索引，sql语句比较简单的应用，TB数据仓库
　　　　　　3.1.2 Innodb：并发访问大，写操作比较多，有外键、事务等需求的应用，系统内存较大。
　　　　3.2 命名规则
　　　　　　3.2.1 多数开发语言命名规则：比如MyAdress
　　　　　　3.2.2 多数开源思想命名规则：my_address
　　　　　　3.2.3 避免随便命名
　　　　3.3 字段类型选择
　　　　字段类型的选择的一般原则：
　　　　　　3.3.1 根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。
　　　　　　3.3.2 只分配满足需求的最小字符数，不要太慷慨。 原因：更小的字段类型更小的字符数占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。
　　　　对于varchar和char的选择要根据引擎和具体情况的不同来选择，主要依据如下原则:
　　　　　　1.如果列数据项的大小一致或者相差不大，则使用char。
　　　　　　2.如果列数据项的大小差异相当大，则使用varchar。
　　　　　　3.对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间。
　　　　　　4.对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针），所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利。
　　　　　　5.表中只要存在一个varchar类型的字段，那么所有的char字段都会自动变成varchar类型，因此建议定长和变长的数据分开。
　　　　3.4 编码选择
　　　　　　单字节 latin1
　　　　　　多字节 utf8(汉字占3个字节，英文字母占用一个字节)如果含有中文字符的话最好都统一采用utf8类型，避免乱码的情况发生。
　　　　3.5 主键选择原则
　　　　注：这里说的主键设计主要是针对INNODB引擎
　　　　　　1.能唯一的表示行。
　　　　　　2.显式的定义一个数值类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途。
　　　　　　3.MySQL主键应该是单列的，以便提高连接和筛选操作的效率。
　　　　　　4.主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。
　　　　　　5.尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。
　　　　　　6.MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
　　　　　　7.MySQL主键应当有计算机自动生成。
　　　　　　8.主键字段放在数据表的第一顺序。
　　　　推荐采用数值类型做主键并采用auto_increment属性让其自动增长。
　　　　3.6 其他需要注意的地方
　　　　　　3.6.1 NULL OR NOT NULL
　　　　　　尽可能设置每个字段为NOT NULL，除非有特殊的需求，原因如下：
　　　　　　　　1.使用含有NULL列做索引的话会占用更多的磁盘空间，因为索引NULL列需要而外的空间来保存。
　　　　　　　　2.进行比较的时候，程序会更复杂。
　　　　　　　　3.含有NULL的列比较特殊，SQL难优化，如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。
　　　　　　3.6.2 索引
　　　　　　　　索引的优点：极大地加速了查询，减少扫描和锁定的数据行数。
　　　　　　　　索引的缺点：占用磁盘空间，减慢了数据更新速度，增加了磁盘IO。
　　　　　　添加索引有如下原则：
　　　　　　　　1 选择唯一性索引。
　　　　　　　　2.为经常需要排序、分组和联合操作的字段建立索引。
　　　　　　　　3.为常作为查询条件的字段建立索引。
　　　　　　　　4.限制索引的数据，索引不是越多越好。
　　　　　　　　5.尽量使用数据量少的索引，对于大字段可以考虑前缀索引。
　　　　　　　　6.删除不再使用或者很少使用的索引。
　　　　　　　　7.结合核心SQL优先考虑覆盖索引。
　　　　　　　　8.忌用字符串做主键。
　　　　　　3.6.3 反范式设计
　　　　　　适当的使用冗余的反范式设计，以空间换时间有的时候会很高效。

4 Mysql软件优化

4.1 开启mysql复制，实现读写分离、负载均衡，将读的负载分摊到多个从服务器上，提高服务器的处理能力。
　　　　4.2 使用推荐的GA版本，提升性能
　　　　4.3 利用分区新功能进行大数据的数据拆分
　　V Mysql配置优化
　　注意：全局参数一经设置，随服务器启动预占用资源。
　　　　1 key_buffer_size参数
　　　　mysql索引缓冲，如果是采用myisam的话要重点设置这个参数，根据（key_reads/key_read_requests）判断
　　　　2 innodb_buffer_pool_size参数
　　　　INNODB 数据、索引、日志缓冲最重要的引擎参数，根据（hit riatos和FILE I/O）判断
　　　　3 wait_time_out参数
　　　　线程连接的超时时间，尽量不要设置很大，推荐10s
　　　　4 max_connections参数
　　　　服务器允许的最大连接数，尽量不要设置太大，因为设置太大的话容易导致内存溢出
　　　　5 thread_concurrency参数
　　　　线程并发利用数量，(cpu+disk)*2,根据(os中显示的请求队列和tickets)判断
　　　　6 sort_buffer_size参数
　　　　获得更快的–ORDER BY,GROUP BY,SELECT DISTINCT,UNION DISTINCT
　　　　7 read_rnd_buffer_size参数
　　　　当根据键进行分类操作时获得更快的–ORDER BY
　　　　8 join_buffer_size参数
　　　　join连接使用全表扫描连接的缓冲大小，根据select_full_join判断
　　　　9 read_buffer_size参数
　　　　全表扫描时为查询预留的缓冲大小，根据select_scan判断
　　　　10 tmp_table_size参数
　　　　临时内存表的设置，如果超过设置就会转化成磁盘表，根据参数(created_tmp_disk_tables)判断
　　　　11 innodb_log_file_size参数(默认5M)
　　　　记录INNODB引擎的redo log文件，设置较大的值意味着较长的恢复时间。
　　　　12 innodb_flush_method参数(默认fdatasync)
　　　　Linux系统可以使用O_DIRECT处理数据文件，避免OS级别的cache，O_DIRECT模式提高数据文件和日志文件的IO提交性能
　　　　13 innodb_flush_log_at_trx_commit(默认1)
　　　　　　1.0表示每秒进行一次log写入cache，并flush log到磁盘。
　　　　　　2.1表示在每次事务提交后执行log写入cache，并flush log到磁盘。
　　　　3.2表示在每次事务提交后，执行log数据写入到cache，每秒执行一次flush log到磁盘。

5. Mysql语句级优化

1.性能查的读语句，在innodb中统计行数,建议另外弄一张统计表，采用myisam，定期做统计.一般的对统计的数据不会要求太精准的情况下适用。
　　　　2.尽量不要在数据库中做运算。
　　　　3.避免负向查询和%前缀模糊查询。
　　　　4.不在索引列做运算或者使用函数。
　　　　5.不要在生产环境程序中使用select * from 的形式查询数据。只查询需要使用的列。
　　　　6.查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。
　　　　7.where子句尽可能对查询列使用函数，因为对查询列使用函数用不到索引。
　　　　8.避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。
　　　　9.所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。
　　　　10.联表查询的时候，记得把小结果集放在前面，遵循小结果集驱动大结果集的原则。
　　　　11.开启慢查询，定期用explain优化慢查询中的SQL语句。

5. MySQL怎么修复损坏的表?

有两种方法，一种方法使用mysql的check table和repair table 的sql语句，另一种方法是使用MySQL提供的多个myisamchk, isamchk数据检测恢复工具。