Mysql 进阶&细节

数据库引擎

MyISAM与InnoDB选择问题

MyISAM 很多很多的select/insert操作 一丁点的update/delete操作 不需要事务 OLAP(联机分析处理) 经常查询数量select count(*)  需要本地全文搜索

INNODB 混合的查询操作( selects/updates/deletes/inserts ) OLTP(联机事务处理) 需要事务 通用/多用 极好的数据恢复性

原文： http://www.bigdbahead.com/?p=130

总结下：

当大部分的数据表操作(90%+)是select、insert时，选MyISAM

当数据表CURD四种类型操作都有时，且并发较多时，选InnoDB

• MyISAM
  • 特性
    1. 不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用
    2. 表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
    3. 读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读
    4. 只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据
  • 适用场景
    1. 不需要事务支持（不支持）
    2. 并发相对较低（锁定机制问题）
    3. 数据修改相对较少（阻塞问题）
    4. 以读为主
    5. 数据一致性要求不是非常高
  • 最佳实践
    1. 尽量索引（缓存机制）
    2. 调整读写优先级，根据实际需求确保重要操作更优先
    3. 启用延迟插入改善大批量写入性能
    4. 尽量顺序操作让insert数据都写入到尾部，减少阻塞
    5. 分解大的操作，降低单个操作的阻塞时间
    6. 降低并发数，某些高并发场景通过应用来进行排队机制
    7. 对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
    8. MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问
• InnoDB
  • 特性
    1. 具有较好的事务支持：支持4个事务隔离级别，支持多版本读
    2. 行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
    3. 读写阻塞与事务隔离级别相关
    4. 具有非常高效的缓存特性：能缓存索引，也能缓存数据
    5. 整个表和主键以Cluster方式存储，组成一颗平衡树
    6. 所有Secondary Index都会保存主键信息
  • 适用场景
    1. 需要事务支持（具有较好的事务特性）
    2. 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
    3. 数据更新较为频繁的场景
    4. 数据一致性要求较高
    5. 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO
  • 最佳实践
    1. 主键尽可能小，避免给Secondary index带来过大的空间负担
    2. 避免全表扫描，因为会使用表锁
    3. 尽可能缓存所有的索引和数据，提高响应速度
    4. 在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
    5. 合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
    6. 避免主键更新，因为这会带来大量的数据移动 

MyISAM引擎

缺省情况下，写操作的优先级要高于读操作的优先级，即便是先发送的读请求，后发送的写请求，此时也会优先处理写请求，然后再处理读请求。这就造成一个问题：一旦我发出若干个写请求，就会堵塞所有的读请求，直到写请求全都处理完，才有机会处理读请求。此时可以考虑使用

max_write_lock_count=1
有了这样的设置，当系统处理一个写操作后，就会暂停写操作，给读操作执行的机会。

我们还可以更干脆点，直接降低写操作的优先级，给读操作更高的优先级。
low-priority-updates=1

innodb引擎

l  共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

l  排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

l  意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

l  意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁(S)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

 

优化插入操作：

alter table table_name disable keys;#先关闭表的索引检查,注意是非唯一索引!

load data infile ‘/path/file’ into table table_name;

alter table table_name anable keys;#再打开索引

1)使用LOAD DATA INFILE从文本下载数据这将比使用插入语句快20倍。

2)使用带有多个VALUES列表的INSERT语句一次插入几行这将比使用一个单行插入语句快几倍。调整bulk_insert_buffer_size变量也能提高(向包含行的表格中)插入的速度。

3)可以对myisam表并行插入Concurrent_insert系统变量可以被设置用于修改concurrent-insert处理。该变量默认设置为1。如果concurrent_insert被设置为0，并行插入就被禁用。如果该变量被设置为2，在表的末端可以并行插入，即便该表的某些行已经被删除。

4)使用插入延迟

　　如果你的客户不能或无需等待插入完成的时候，这招很有用。当你使用MySQL存储，并定期运行需要很长时间才能完成的SELECT和UPDATE语句的时候，你会发现这种情况很常见。当客户使用插入延迟，服务器立刻返回，如果表没有被其他线程调用，则行会列队等待被插入。使用插入延迟的另一个好处就是从多个客户插入的情况会被绑定并记录在同一个block中。这将比处理多个独立的插入要快得多。

使用延迟插入操作

DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候，

服务器把数据行放入一个队列中，并立即给客户端返回一个状态信息，这样客户

端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据

表中读取数据，队列中的数据就会被保持着，直到没有读取者为止。接着服务器

开始插入延迟数据行（delayed-row）队列中的数据行。在插入操作的同时，服务器

还要检查是否有新的读取请求到达和等待。如果有，延迟数据行队列就被挂起，

允许读取者继续操作。当没有读取者的时候，服务器再次开始插入延迟的数据行。

这个过程一直进行，直到队列空了为止。

几点要注意事项：

· INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED...SELECT语句的DELAYED。

· 服务器忽略用于INSERT DELAYED...ON DUPLICATE UPDATE语句的DELAYED。

· 因为在行被插入前，语句立刻返回，所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。

· 对于SELECT语句，DELAYED行不可见，直到这些行确实被插入了为止。

· DELAYED在从属复制服务器中被忽略了，因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。

注意，目前在队列中的各行只保存在存储器中，直到它们被插入到表中为止。这意味着，如果您强行中止了mysqld（例如，使用kill -9）

或者如果mysqld意外停止，则所有没有被写入磁盘的行都会丢失。

优化插入记录的速度

插入记录时，索引，唯一性校验等都会影响到插入记录的速度。而且，一次插入多条记录和多次插入记录所耗费的时间也不同。

  1.禁用索引

插入数据时，mysql会根据表的索引对插入的记录进行排序，降低插入速度。解决这个问题可以在插入记录之前禁用索引，等到插入完毕后在开启。

alter table 表名 disable keys;    //禁用索引

alter table 表名 enable keys;    //开启索引

2.禁用唯一性检查

插入数据时，mysql会对插入的记录进行唯一性检查，会降低插入速度。

set unique_checks=0;     //禁用唯一性

set unique_checks=1;    //开启唯一性

3.优化insert语句

insert into 表名 values 

(......),

(......);

insert into 表名 values (......);

insert into 表名 values (......);

上面两种插入方法，第一种与数据库的连接等操作，明显比第二种快。

5)插入之前将表锁定(只针对非事务处理型的表)

　　这将提高数据库性能，因为索引缓冲区只是在所有的插入语句完成后才对磁盘进行一次刷新。通常情况下，有多少个插入语句就会有多少次索引缓冲区刷新。如果你可以用一个插入语句实现所有行的插入，则无需使用显式锁定语句。

　　要想更快地对事务型表插入，你应该使用START TRANSACTION和COMMIT语句，而不是LOCK TABLES语句。

 

innodb插入优化

innodb表,上面方法不怎么凑效 

set unique_checks=0; //关闭唯一性校验

set autocommit=0; //关闭自动提交

默认设置下，MyISAM表的读和写是串行的。

MyISAM中读写是相互阻塞的锁

MyISAM存储引擎有一个 系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

当concurrent_insert设置为0时，不允许并发插入。

当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

从以上的赋值说明可以得出，在删除操作不是很多的情况下，可以选用 concurrent_insert =2  ，这样可以增大并发速度，但是会浪费一部分数据文件的空间，因为中间删除的空隙得不到数据填充。这个浪费在删除操作较多的情况下是非常可观的，所以在删除操作较多的情况下应该选用concurrent_insert =1,当然，假如你的对空间不是特别在乎，而且能很好的处理备份问题，选用concurrent_insert =2显然能提高你的MyISAM的并发读写能力。

查看当前concurrent_insert：SHOW GLOBAL VARIABLES LIKE '%concurrent_insert%';

设置concurrent_insert，配置文件(my.cnf)设置concurrent_insert = 2

 

在没有索引的情况下 myisam的select速度比innodb快30-50%。

索引

对于CHAR和VARCHAR列，建议索引列的前缀(前N个字符)。这更快并且比索引整个列需要较少的磁盘空间。

对于BLOB和TEXT列，必须索引列的前缀，不能索引列的全部。

多列索引(联合索引)

一个联合列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。所以在where条件中只有顺序利用索引字段的查询才能利用联合索引。

前缀的索引可以使索引更小并且更快，但是它也有明显的缺点。MySQL不能在ORDER BY或GROUP BY语句中使用前缀索引，也不能把它们作为覆盖索引进行使用。

查询优化

显示查询详细时间

set profiling=1;

show profiles;

show profile;

强制使用索引和禁止使用索引

1、mysql强制使用索引:force index(索引名或者主键)

select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引"PRI和ziduan1_index")

2、mysql禁止某个索引：ignore index(索引名或者主键)

select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引"PRI,ziduan1_index")

当只显示一个表的数据，关系条件只一个时，使用IN更快

当只显示一个表的数据如A，关系条件不只一个如ID，col1时，使用IN就不方便了，可以使用EXISTS：  

尽量使用Join 而不是Where来列出关联条件，特别是多个表联合的时候

用where进行多表连接的比较时，先全部进行迪卡尔积后，最后再统一过滤。

而inner join是一边Descartes迪卡尔积，同时这滤，所以临时记录会少一些。

limit

select * from mytable where index_col = xxx limit offset, limit;

在偏移量offset较大时，变为：

select * from mytable where id > offset and id < offset + limit and index_col = xxx

速度杠杠滴(id 不连续会出现问题

 

配置文件优化

Innodb类型的my.cnf：http://www.bigdbahead.com/?p=115

Myisam类型的my.cnf：http://www.bigdbahead.com/?p=643

单个服务的连接数

让一台机器多跑几个MYSQL服务分担。将服务均衡的规划到多个MYSQL服务端口上：比如app_1 ==> 3301 app_2 ==> 3302...app_9 ==> 3309。不要害怕机器是否可以支撑，一般情况下，一个1G内存的机器跑上10个MYSQL是很正常的。让10个MYSQLD承担1000个并发连接效率要比让2个MYSQLD承担1000个效率高的多。当然，这样也会带来一些应用编程上的复杂度；

*随机选取一条，保证效率

1.SELECT t1.* FROM `myisam` as t1
WHERE t1.id >= (floor( RAND() * ((SELECT MAX(id) FROM `myisam`)-(SELECT MIN(id) FROM `myisam`)) + (SELECT MIN(id) FROM `myisam`))) order by t1.id LIMIT 1;(有问题)

2.SELECT t1.* FROM `myisam` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `myisam`)-(SELECT MIN(id) FROM `myisam`))+(SELECT MIN(id) FROM `myisam`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1;

*去除重复行

*删除无法连接的脏数据(常用于删除没有保持数据一致性的记录)

delete from wl_askill where inborn = 1 and not exists(select * from wl_pet where wl_pet.pet_id = wl_askill.pet_id)
delete wl_askill from wl_askill left join wl_pet on wl_askill.pet_id = wl_pet.pet_id where wl_askill.inborn = 1 and wl_pet.pet_id is null

//选取不重复记录
select * from code where id in(select id from code group by item having count(*)=1)

//选取所有重复的记录 =_,=
select * from code where id not in(select id from code group by item having count(*)=1)

//删除重复的记录
1.删除 指定字段全部重复 的记录
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
2.保留最小值
delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId)>1)  and  rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3.实践

1.建个临时表，避免alias别名问题。

create table tmp as select um_id from wl_user_material where um_id in
(SELECT um_id FROM `wl_user_material` b1 where b1.um_id not in(select b2.um_id from wl_user_material b2 group by b2.user_id,b2.prototype_id having count(*) = 1))
and um_id not in(select c1.um_id from (SELECT * FROM `wl_user_material` c2 where c2.um_id not in(select um_id from wl_user_material c3 group by c3.user_id,c3.prototype_id having count(*) = 1) order by c2.user_id asc,c2.num desc) c1 group by c1.user_id,c1.prototype_id);

2.删除
delete from wl_user_material where um_id in (select um_id from tmp);

3.收尾
drop table tmp;

insert not exists防止插入重复记录

INSERT INTO clients 
(client_id, client_name, client_type) 
SELECT 10345, 'IBM', 'advertising' 
FROM dual 
WHERE not exists (select * from clients 
where clients.client_id = 10345); 

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值，即使这些值还不存在当前表中。

数据库复制

主从同步、备份

在配置文件里加replicate-do-db and replicate-do-table这两个参数
复制多个表就写多行replicate-do-table

 

存储过程

存储过程