MySQL性能优化研究

 

一

当发现程序运行比较慢的时候，首先排除物力资源问题之后，就将注意力转向mysq数据库：

1、首先确定运行慢的sql语句：

mysql> show full processlist;

2、确认低效的查询：
多次执行第一步发现time耗费大的sql语句。查看耗费的时间。

 

3、为sql生成一个执行计划query Execution plan（QEP）

mysql> explain select * from tbal_name where ...;

 

4、查看创建表的语句：

show create table table_name \G;

 

5、查看表的状态：

show table status like 'table_name' \G;

 

---------------------------------------------------

二

总结一些分析的命令：

1、explain：解释sql的执行计划，后边的sql不执行

2、explain partitions ：用于查看存在分区的表的执行计划

3、explain extended：--书上说存在filtered列，但是检查后没有发现这一列，待验证

4、show warnings:

5、show create table:查看表的详细的创建语句，便于用户对表进行优化

6、show indexes :产看表的所有索引，show indexes from table_name，同样也可以从information_schema.statistics表中获得同样的信息。cardinality列很重要，表示数据量。

7、show tables status: 查看数据库表的底层大小以及表结构，同样可以从information_schema.tables表中获得底层表的信息。

8、show [global|session]status:可以查看mysql服务器当前内部状态信息。可以帮助却行mysql服务器的负载的各种指标。默认是session。同information_schema.global_status和information_schema.session_status

9、show [global|session] variables ：查看当前mysql系统变量的值，其中一些值能影响到sql语句的执行方式。同information_schema.global_variables和information_schema.session_variables;

10、information_schema:包含的表的数量和mysql的版本有关系。

 

-------------------------

三、索引

 1、数据完整性：通过主键和唯一键来确保数据唯一性

主键（primary key）：每个表只能有一个，主键不为null，定义了auto_increment列，那么此列就必须是主键的一部分。

唯一键（unique key）：表中可以存在多个唯一键，每个key可以为null，即null！=null

2、索引术语：

索引技术：

索引实现：

索引类型：

3、创建单列索引：这里要注意的是可以在一列上创建多个索引，但是这样会产生性能开销，

alter table table_name add primary key|index index_name (coumn_name);

4、当存在多个索引的时候，如何确定选择使用哪个索引更高效？

根据索引中唯一值和索引中总行数做比较，唯一值越多，使用这个索引时以更少读得到查询结果。

禁用优化器设置：set @@session.optimizer_switch='index_merge_intersection=off';

5、在like查询中（%oo）不会走索引

6、不支持函数索引。另外在索引上使用函数，执行计划也不会走索引。

7、唯一索引：提供数据完整性，保证在列中任何值都出现一次，告知优化器，给定的记录最多只有一条结果返回，避免了额外的索引扫描，是否进行了额外的索引扫描，可以使用下边的语句查看：

flush status;

show session status like 'handler_read_next';

select name from student where name='Randy';

show session status like 'handler_read_next';

8、对于结果的排序：如果不是根据索引字段进行排序，mysql会使用内部文件排序算法对返回额行按照指定顺序进行排序。使用索引字段进行排序的话将免去分类的过程。

9、组合索引：索引的执行顺序是按照组合索引中每个字段的cardinality来确定使用哪个组合索引。有的时候交换一下组合索引列的顺序，执行效率会更好。主要可能组合索引的最左列。

组合索引列不要太宽，ref表示使用了那几个组合索引字段，key_len表示索引字段的长度

10、查询提示：

通过查询提示来修改查询的执行计划。

straight_join: 强制按照from后边的关联顺序去执行链接操作，不管执行计划是否是最优的。

11、索引提示：

索引提示都会被链接中的表来使用，为每张表定义use、ignore、force类表

use [index|key] [for( join|order_by|group_by)] [index_list]  --让优化器倾向于使用指定的索引扫描

ignore [index|key] [for( join|order_by|group_by)] [index_list]

force [index|key] [for( join|order_by|group_by)] [index_list] --让优化器倾向于索引扫描而不是全表扫描

12、索引DML操作的影响：影响写操作的性能。重复索引、

13、通过 show index_staistics来查看索引的使用情况，来确定哪些索引没有被使用。没有被使用的索引占用空间，影响写入性能，

14、DDL：在修改表索引的时候会锁表。会影响存储空间的大小。

 

15、覆盖索引：

16、局部索引：

 

 

 

-----------------

 

四、搜索引擎

MySIAM：一种非事物行的存储引擎

InnoDB：事务性存储引擎

Memory：基于内存的、非事物性的以及非持久性的存储引擎

查看表的存储引擎：

show create table \show table status \ information_schema.tables可以查看表的存储引擎

 

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接

怎么进入MySQL命令行呢？ mysql的安装目录下面有个bin目录，先用命令行进入该目录，然后用 mysql -uroot -p123456 来登录（注意：用户名和密码不用包含“”） 
命令： show processlist; 
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。 
show processlist;只列出前100条，如果想全列出请使用show full processlist; 
mysql> show processlist; 
命令： show status; 
命令：show status like '%下面变量%'; 
Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。 
Connections 试图连接MySQL服务器的次数。 
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。 
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。 
Delayed_writes 用INSERT DELAYED写入的行数。 
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 
Flush_commands 执行FLUSH命令的次数。 
Handler_delete 请求从一张表中删除行的次数。 
Handler_read_first 请求读入表中第一行的次数。 
Handler_read_key 请求数字基于键读行。 
Handler_read_next 请求读入基于一个键的一行的次数。 
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。 
Handler_update 请求更新表中一行的次数。 
Handler_write 请求向表中插入一行的次数。 
Key_blocks_used 用于关键字缓存的块的数量。 
Key_read_requests 请求从缓存读入一个键值的次数。 
Key_reads 从磁盘物理读入一个键值的次数。 
Key_write_requests 请求将一个关键字块写入缓存次数。 
Key_writes 将一个键值块物理写入磁盘的次数。 
Max_used_connections 同时使用的连接的最大数目。 
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。 
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。 
Open_tables 打开表的数量。 
Open_files 打开文件的数量。 
Open_streams 打开流的数量(主要用于日志记载） 
Opened_tables 已经打开的表的数量。 
Questions 发往服务器的查询的数量。 
Slow_queries 要花超过long_query_time时间的查询数量。 
Threads_connected 当前打开的连接的数量。 
Threads_running 不在睡眠的线程数量。 
Uptime 服务器工作了多少秒。

关于上面的一些注释：

如果Opened_tables太大，那么你的table_cache变量可能太小。 
如果key_reads太大，那么你的key_cache可能太小。缓存命中率可以用key_reads/key_read_requests计算。 
如果Handler_read_rnd太大，那么你很可能有大量的查询需要MySQL扫描整个表或你有没正确使用键值的联结(join)。 
可以根据“show status”命令返回的状态进行微调。我主要注意以下变量的数值，越小越好，最好为零：）
        Created_tmp_disk_tables
        Created_tmp_tables
        Created_tmp_files
        Slow_queries

 

 

1. Show status命令了解各种SQL的执行频率

查看当前会话执行的各项命令统计 com_XXX

show session status like 'Com_%' 其中session可省

show status like 'Com_%'

显示全局统计使用

SHOW GLOBAL STATUS LIKE 'COM_%';

2. 查看针对InnoDB存储引擎状态的统计

SHOW GLOBAL STATUS LIKE 'Innodb_%'

红色部分可以看到插入 删除 读取 更新的行的汇总数量。无论事务提交还是回滚，都进行累加。

3. 查看试图连接mySQL服务器的次数

show global status like 'connections'

4. 查看服务器工作时间

show global status like 'uptime'

练习2：通过explain 分析低效SQL的执行计划

1. 使用explain 分析SQL语句

explain select * from TStudent where studentID='00034'

explain select * from TStudent where cardID like '%45%'

2. 确定问题并采取相应措施

如果出现对大表的全表扫描，应该在该列创建索引，并且尽量避免使用like这样的条件进行查找。

explain select * from TStudent where studentID like '%3%'

注意以下这个查询使用索引进行的，大家想想为什么？

explain select * from TStudent where studentID like '0003%'

练习3：通过索引优化查询

3. 索引的存储分类

MySQL中的索引的存储类型目前只有BTREE和HASH，具体和表的存储引擎有关。myISAM和InnoDB存储引擎都支持BTREE。Memory/Heap存储引擎可以支持HASH和BTREE

4. MySQL如何使用索引-组合索引的使用

索引用户快速找出某一列中有一特定值的行，查询使用索引的主要条件是查询条件中使用索引关键字，多列索引只有在查询条件中使用最左面的前缀，才使用索引。

在TSCore表上创建了组合主键。

查询

explain select * from TScore where studentID='00023'

explain select * from TScore where subjectID='00001'

可以看到是全表扫描

5. 条件语句中使用Like

explain select * from TStudent where studentID like '%3%'

注意以下这个查询使用索引进行的，大家想想为什么？

explain select * from TStudent where studentID like '0003%'

如果列是索引，查找空值使用索引，以下是给sname列创建索引

alter table `TStudent` add index indexName(sname)

查看查询计划

explain select * from TStudent where sname is null

6. 不适用索引的情况

如果查询的结果站记录总数的大多数，就不适用索引。

如果条件语句中有or or前面的列有索引，后面列没索引，两个索引都不用。

如果列类型是字符串，一定要用' ' 否则不使用索引查询数据。

explain select * from TStudent where studentid=00051

explain select * from TStudent where studentid='00051'

7. 查看索引使用情况

show status like 'Handler_read%'

8. 两个简单的实用的优化方法

定期分析和检查表

analyze table TStudent

本语句用于分析和存储表的关键字分布，分析的结果将可以使的系统得到准确的统计信息，使得SQL能够生成正确的执行计划。

check table TStudent

本语句的作用是检查一个或多个表是否有错误，check table对MyISAM和InnoDB表有作用。

常用的SQL的优化

9. 优化Insert语句

如果从同一个客户端一次插入很多行，尽量使用下面的语句，能大大缩减客户端与数据库之间的连接、关闭等消耗。

insert student values

(45,'李双江','212121212121212121121','北京海淀区','213322323232'),

(46,'冯玲娥','212121212121212121121','北京海淀区','213322323232'),

(47,'李双江','212121212121212121121','北京海淀区','213322323232')

不要使用以下语句

insert student values (45,'李双江','212121212121212121121','北京海淀区','213322323232');

insert student values (46,'冯玲娥','212121212121212121121','北京海淀区','213322323232');

insert student values (47,'李双江','212121212121212121121','北京海淀区','213322323232');

10. 使用delayed选项

如果从多个客户端插入很多行，能通过使用INSERT delayed语句得到更高的速度。其含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘。这比每条语句分别插入要快的多。LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入。

该参数支持MyISAM数据引擎。

insert delayed student values (46,'冯玲娥','212121212121212121121','北京海淀区','213322323232');

11. 将索引文件和数据文件分别放到不同的盘

12. RAID磁盘

13. 从文件装载一个表时，使用load data infile 比insert语句块20倍

14. 可以使用一个索引来满足Order by，不需要额外的排序。Where条件和Order by 使用相同的索引，并且order by的顺序和索引顺序相同。

15. 优化嵌套子查询 子查询可以被更有效率的join替代，使用join不需要在内存中使用临时表。

16. MySQL如何优化or条件 两个条件必须有索引，否则不是用索引

优化数据库对象

练习4：优化表的数据类型

使用PROCEDURE ANALYSE()来对表进行分心，该函数可以对数据表中列的数据类型提出优化建议。

select * from TStudent PROCEDURE ANALYSE();

通过拆分提高表的访问效率

17. 垂直拆分

将一个有很多列的表，分解成多个表，使用主键进行关联，可以使数据行变小，一页就能存放更多的数据，使用jion进行连接。如果一个表，有的列常用，有些列不常用，就可以垂直拆分。

18. 水平拆分

根据一列或多列数据的值把数据行放到两个队表中。以下情况使用水平拆分

1. 表很大 分割后可以降低查询时需要读的数据和索引的页数。同时也降低了索引的层数，提高查询速度。

2. 表中的数据有很强的独立性 比如表中数据，记录不同地区或不同时间的数据。特别是有些数据常用，有些不常用。

3. 需要把数据放到多个介质上。放到不同的硬盘。

这种查询需要使用多个表名，使用UNION将结果连接起来，增加了操作的复杂性。

逆规范化

规范化，降低了数据冗余，以及数据修改引起的数据不一致。但是需要多个表进行jion，降低了查询的性能，为了性能考虑，不严格使用规范化设计数据库。

反规范化的好处，降低连接操作的需求，降低外键和索引的数目，还能减少表的数量。

常用的反规范化技术有：

1. 增加数据冗余

2. 增加派生列

3. 重新组表

4. 分割表

反规范化技术需要维护数据的完整性。常用的维护完整性方法有：

5. 批处理 是指定期运行批处理作业或存储过程对派生列进行修改，这在实时性要求不高的情况使用

6. 由应用逻辑来实现 使用事务，在一个事务中对所有涉及到的表进行增删改操作。因为同一逻辑必须在所有应用中使用和维护，容易遗漏，特别是在需求变化大时，不易维护。

7. 使用触发器实现，易于维护。

使用中间表提高统计查询速度

比如一个表记录了客户每天的消费记录，你需要统计用户本周客户消费总金额和近一周不同时间段用户的消费总金额。你就应该将本周的数据插入到一个新表，然后使用新表进行统计。

优点：

8. 中间表与源表隔离

9. 中间表可以灵活添加索引或增加临时用字段。从而达到提高统计查询效率和辅助统计查询的作用。

锁问题

MySQL不同的存储引擎支持不同的锁机制。myISAM和MEMORY存储引擎采用表级锁；BDB存储引擎采用的是页面锁，也支持表级锁；InnoDB及支持行级锁、也支持表级锁，默认情况是采用行级锁。

MySQL有三个级别的锁。

三种锁地特性：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销稍大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

练习5：查看锁

这是myISAM存储引擎支持表锁，这也是早起mySQL版本中唯一支持的锁类型。

19. 查询表级锁争用情况

show status like 'table%'

20. 查看锁

当上面出现锁等待的情况下，使用putty连接输入以下命令

show global status like 'innodb_row_lock%'

Innodb_row_lock_current_waits：当前正在等待锁定的数量；

Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

Innodb_row_lock_time_avg：每次等待所花平均时间；

Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

输入以下命令，查看全局的表锁

show global status like 'table%'

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

Table_locks_immediate表示立即释放表锁数

等待的锁不多，就不需要使用InnoDb

优化mySQL

练习6：查看mysql参数

和大多数数据库一样，MySQL也提供了很多参数来进行服务器的设置。

21. 查看mySQL Server参数

show variables

查看当前会话状态

show status

查看全局状态

show global status

22. 影响mySQL性能的重要参数

将索引加到缓存，能够提高查找速度。

绝大多数参数不需要用户调整，key_buffer_size参数，用来设置索引块缓存的大小，被所有线程共享，此参数适用于MyISAM存储引擎。

使用putty连接mySQL设置这个参数

set global hot_cache2.key_buffer_size=128*1024;

global标识对每一个新的连接，此参数都将生效，hot_cache2是新的key_buffer名称。

然后可以把相关表的索引放到指定的索引缓存中。

将相关表索引放到指定的索引缓存中。

可以看到有两个索引

将TStudent表的索引加载到缓存。

磁盘I/O问题

使用磁盘阵列

23. RAID技术

RAID-0 读写快 无冗余

RAID-1 读快 写一般 有冗余 有一半的磁盘浪费

RAID-10 先做RAID-1 在做RAID -0 读写性能好

RAID-4 使用一个单独的磁盘存放校验数据，当一个磁盘坏掉，

RAID-5 将校验数据分布在多个磁盘 写性能不如RAID 0、RAID 1和RAID 10，出现坏盘，读性能下降。

RAID技术有硬件实现的也有软件实现的，即操作系统实现的。

应用优化

减少对mySQL的访问

要想提高访问速度，能够一次连接就能取的所有结果，就不要分两步。这样能够大大减少对数据库无谓的重复访问。

例如

第一个查询得到用户学号和姓名

Select studentID,sname from TStudent where studentID='00005'

第二个查询得到用户的班级

Select class from TStudent where studentID='00005'

这样就需要想数据库提交两次查询。

使用一个SQL语句实现，将class放到变量以备后用。

Select studentID,sname, class from TStudent where studentID='00005'

练习7：使用查询缓存

MySQL的查询缓存，就是将select语句查询的结果放到缓存，再遇到个相同的查询，服务器就会从查询缓存中重新得到查询结果，不再需要解析和执行查询。

适合更新不频繁的表。表结构和数据更改后，查询缓存值的相关条目被清空。

show variables like '%query_cache%'

按顺序执行以下命令查看缓存的数量

show status like 'Qcache%'

Select class from TStudent where studentID='00046';

Select class from TStudent where studentID='00056'

show status like 'Qcache%'

增加cache层

在应用端增加cache层来减轻数据库负担的目的，cache层有很多种，也有很多证实现方式。

比如，把部分数据从数据库抽取出来放到应用端以文本形式存储，如果有查询需求就直接重文本（cache）中查询。由于cache中数据量小，能够达到较高的访问效率。但是也涉及到数据更新，需要及时刷新cache。

负载均衡

负载均衡是一种在实际应用中非常普遍的方法。从Web服务器到数据库服务器都可以使用负载均衡。现在介绍mySQL负载均衡方法。

利用mySQL复制分流查询操作

主服务器负载数据更改，从服务器负载查询。数据复制的延迟需要考虑。

采用分布式数据库架构

MySQL cluster适用于大数据量，负载高的情况，有良好的扩展性和高可用性。

 

转自：http://www.cnblogs.com/flish/p/4897176.html