MySQL笔记之优化篇

常用SQL技巧

匹配a开头的字符:

Select‘asfdas’PEGEXP “^a”; 返回1.

匹配不包含a 的字符串.

Select‘da’ PEGEXP “[^a]”;

匹配fhk任意字符:

Select‘abcdefg’ PEGEXP “[fhk]”; 因为含有f所以返回1.

随机排序:

Select *from tab_name order by rand();

用BIT GROUP FUNC做统计.可以用二进制的位代表客观事物.从而用简洁的数据表示丰富的信息:

Slectcustomer_id bit_or(kind) from order_rab group by customer_id;

 

数据库中的每个表至少对应数据库目录中的一个文件,MySQL数据库名和表名的大小写敏感性跟操作系统关联,在UNIX中是敏感的,在Windows中是不敏感的.

而列,索引,存储子程序,和触发器名在任何平台上是大小写不敏感的.表别名在UNIX中是敏感的,在WIN中不敏感.

 

SQL优化

通过show status 命令了解各种SQL的执行效率.

Show [session | global]status 中的session代表当前连接, global代表自数据库上次启动至今.

我们通常比较关心一下几个统计参数:

    Com_select: 执行select操作的次数.

    Com_insert: 执行insert操作的次数.

    Com_update: 执行update操作的次数.

    Com_delete: 执行delete操作的次数.

    Com_commit: 事物提交的次数.

    Com_rollback: 事物回滚的次数. 对于回滚操作非常频繁的数据库,可能意味这应用编写存在问题.

    Connections: 试图连接mysql服务器的次数.

    Uptime: 服务器工作时间.

    Slow_queries: 慢查询的次数.

可以通过两种方法定位执行效率低的SQL语句. 1,查看慢查询日志.2, show processlist 命令查看当前线程.

 

通过desc 或 explain都可以获取SELECT语句的执行信息.比如:

Explain select * fromkevin;

mysql> explainselect * from kevin \G

得到如下信息:

***************************1. row ***           id: 1  select_type: SIMPLE        table: kevin         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 7        Extra:1 row in set (0.00 sec)

每个列的简单解释如下:

Select type:表示select的类型,

Table : 输出结果集的表.

Type : 表示表的链接类型,性能由好到差的类型:syetem,const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery,range, index, all

Possible_keys : 可能使用的索引.

Key: 实际使用的索引.

Key_len: 索引字段的长度.

Rows : 扫描行的数量.

Extra: 执行情况的说明和描述.

 

查询表上的索引状态: show index from tab_name \G;

对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用.

对于使用like的查询,后面如果是常量并且只有”%”号不在第一个字符的时候,索引才可能被使用.

如果对大的文本进行索引,使用全文索引而不应使用like’%...%’.

Heap表只用在where中有”=”的条件下才会使用索引.

用or分割开的条件,如果有一个条件中的列没有用到索引,那么索引不会被使用.

如果列类型是字符串, 在where中要用把此字符常量用引号引起来,否则即使这个列上有索引,也不会被用到.

查看索引使用情况: show status like ‘Handler_read%’; 返回的数据其中:

    Handler_read_key 代表了一个行被索引值读的次数,说明索引性能比较高.

    Handler_read_rnd_next 是数据文件中读下一行的请求数,意味着查询运行低效,说明索引不正确或查询没有利用索引.

 

两个简单实用的优化方法:

1, 定期分析表和检查表.

    Analyze table tab_name;

    Check tabletab_name;

2, 定期优化表.

    Optimize table tab_name;

上面三个命令会对表进行锁定.使用时要注意.

 

导入

对于MyISAM表,大批量插入数据时,如果原表非空,那么设置关闭索引会明显加快导入速度:

    Alter table tbl_name DISABLE KEYS;

    Loading the data

    Alter table tlb_name ENABLE KEYS;

对于InnoDB表的导入优化,有三种措施:1,被导入文件要按主键顺序存储. 2,在导入前执行SET UNIQUE_CHECKS = 0 关闭唯一性校检,导入后再打开. 3,在导入前执行 SETAUTOCOMMIT=0 关闭自动提交,导入后再打开.

 

优化其他语句

1, 批量插入比循环单次插入快很多.

2, 如果从不同客户插入很多行,使用 insert delayed 语句会得到更快的速度,它的含义是立即执行.而low_priority刚好相反.

3, 将索引文件和数据文件分在不同的磁盘上.(利用建表中的选项)

4, 增加bulk_insert_buffer_size变量值的方法可以提高MyISAM表的插入速度.

5, 当从一个文本文件装载一个表时,使用LOAD DATAINFILE.

 

如果查询包括GROUP BY但用户想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序.

 

子查询往往可以用JOIN替代.而JOIN的效率要远远高于子查询.

 

OR之间每个条件都必须用到索引,如果没有索引,则应该考虑增加索引.

 

USE INDEX : 提供希望MYSQL去参考的索引列表.

    Select * from tbl_name use index(ind_id) where id = 2 ;

IGNORE INDEX : 让MYSQL忽略一个或者多个索引.

    Select * from tbl_name ignore index(ind_id) where id = 2;

FORCE INDEX : 强制MYSQL使用一个特定的索引.

    Select * from tbl_name force index(ind_id) where id = 2;

 

优化表的数据类型

Select * from tbl_name PROCEDURE ANALYSE();

 

锁问题

表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如WEB应用.

行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如OLTP系统.

 

MyISAM表锁

可以通过检查table_locks_waited,和table_locks_immediate状态变量来分析系统上的表锁争定夺.

    Showstatus like ‘table%’;

MYISAM表的读操作,不会堵塞其他用户对同一表的读请求,但会堵塞写请求.

MYISAM表的写操作,则会堵塞其他用户对同一表的读和写操作.

 

MYISAM表有一个系统变量:concurrent_insert. 其值有0,1, 2.    

0表示读锁时不允许并发插入. 1表示在表中没有空洞的时候允许并发插入,2表示总是允许并发插入.

 

MySQL认为写请求要比读请求重要.同时有读和写的请求,会先执行写锁.可以通过指定insert, update, delete语句的LOW_PRIORITY属性,降低这些语句的优先级.例如: SET LOW_PRIORITY_UPDATES=1;

 

InnoDB表锁

事务的ACID属性: 原子性,一致性,隔离性,持久性.

并发事务会带来四个问题:更新丢失, 脏读, 不可重复读, 幻读. 防止更新丢失应该是应用的责任,其他的必须由数据库提供一定的事务隔离机制来解决.基本可以分为两种:1,加锁.2,生成一个请求时间点的数据快照.

 

获取InnoDB行锁争用情况: show status like ‘innodb_row_lock%’;

 

共享锁:

Ø      set autocommit=0;Ø      select id, name from abl_namewhere id =188 lock in share mode;

共享锁下本session可以查询,更新要等待.其他session可以查询也可以加共享锁,但是不能更新会形成死锁而退出.

 

排他锁:

Ø      set autocommit=0;Ø      select id, name from tbl_namewhere id =188 for update;Ø      update tbl_name set name = “Tom”where id = 188;Ø      commit;

排他锁下,本session可以查询和更新,其他session可以查询,但不能加共享锁.

 

由于InnoDB行锁机制是通过索引实现的,所以在不通过索引条件查询的时候,InnoDB使用是表锁,而不是行锁.

访问不同的记录,但是如果是使用相同的索引键,是会出现锁冲突的.

当表有多个索引,不同的事务可以使用不同的索引锁定不同的行.

 

由于间隙锁的机制,并发插入比较多的应用,会造成严重的锁等待.因此我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件.

InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁.

 

关于避免死锁:

1, 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表.

2, 在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按照固定的顺序来处理记录,也可以大大降低出现死锁的可能.

3, 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁.

 

如果出现死锁,可以用SHOW INNODB STATUS分析最后一个死锁.

 

优化MySQLServer

查看MySQL参数:

Show variables    查看数据库启动后的静态参数值.如缓冲区大小,字符集等.

Show status       查看运行期间的动态变化的信息.如锁等待,当前连接数等.

 

Key_buffer_size

这个参数是索引块的缓存大小. 此参数只适用于MyISAM表.

新建缓存大小: set globalhot_catch2.key_buffer_size = 200* 1024;

这个缓存名字就是hot_catch2,可以修改删除.. 默认的缓存不可以删除.

 

Table_catch

这个参数表示数据库用户打开表的缓存数量.此参数值适用于MyISAM表.

Show global status like ‘open_tables’; 表示当前打开的表缓存数.可以通过flush tables命令释放.

Show global status like ‘opened_tables’; 表示曾经打开的表缓存数.

Innodb_buffer_pool_size

此参数定义了InnoDB表数据和索引数据的最大内存缓冲区大小.前面的key_buffer_size只对MyISAM表的索引做缓存,而这个参数同时为数据块和索引块做缓存.这个值设的越高,访问表中数据需要的磁盘I/O就越少.专用数据库服务器上可以设置成机器物理内存的80%.

 

Innodb_flush_log_at_trx_commit

控制缓存区中的数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机.

0 . 缓冲每秒被写入到日志中,并且对日志文件做向磁盘书信的操作.

1 . (默认)每个事务提交时,写入日志.刷新磁盘.

2 . 每个事务提交时,写入日志而不刷新磁盘. 每秒刷新磁盘.

 

Innodb_additional_mem_pool_size

存放数据库结构和其他内部数据结构的内存池的大小.

 

Innodb_lock_wait_timeout

某些死锁出现后,需要等待的时间回滚.

 

Innodb_support_xa

是否支持分布式事务.默认值是NO或者1. 如果确认应用中不需要使用分布式事务,可以关闭这个参数,获取性能.

 

Innodb_log_buffer_size

日志缓存的大小.如果存在更新操作峰值或者负载较大.就应该加大它的值.而太高了则浪费内存.通常设置为8~16M就足够了.默认是1M

 

Innodb_log_file_size

一个日志组中每个日志文件的大小.此参数在高写入负载情况下很重要.这个值越大性能相对越高.默认是5M.

 

磁盘I/O问题

磁盘阵列的选择:

数据读写频繁,可靠性要求高,选择RAID10

读很频繁,写相对较少,可靠性有一定要求.选择RAID5

读写频繁,可靠性要求不高,选择RAID0

 

使用SYMBOLIC LINKS分布I/O

Linux下,将一个数据库指向其他物理磁盘:

Shell>mkdir/otherdisk/databases/test

Shell>ln –s/otherdisk/databases/test/path/to/datadir

对于新表可以在CREATETABLE 语句中增加选项:

Create table. . .

Type = myisam

Data directory = ‘/disk2/data’

Index directory = ‘/disk3/index’

对于已有的表,可以先将数据文件(.MYD)或索引文件(.MYI)转移到目标磁盘.

 

Windows下,

创建目录 D:\data\foo

创建文件C:\mysql\data\foo.sym, 在其中输入D:\data\foo

这样在数据库foo创建的表都会存储到D:\data\foo目录下.

 

应用优化

避免对同一数据做重复检索

负载均衡

负载均衡的机制就是利用某种均衡算法,将固定的负载量分布到不同的服务器上,以此减轻单台服务器的负载.

利用MySQL的主从复制可以有效的分流更新和查询操作,具体的是一个主服务器承担更新操作,多台从服务器承担查询操作,主从之间通过复制实现数据的同步.

 

分布式数据库架构适合大数据量,负载高的情况,可以使用MySQL的CLUSTERE功能或者通过自己编写程序来实现全局事务.当前分布式事务只支持InnoDB存储引擎.