关闭

mysql优化2

339人阅读 评论(0) 收藏 举报
锁:
为了提高数据库的并发:大多数商业数据库系统没有提供更多的选择,一般都是在表上施加行级锁。
2种锁:
表锁:写锁比读锁优先级更高。
行级锁:最大程度提高并发处理。带来最大锁开销。

事务:事务内的语句要么全部成功,要么全部失败。
单条语句:每条单独的语句都是事务,其后隐含COMMIT。(开关autocommit默认为ON(1),如果为0一直到commit时才会提交,影响其他事务操作)
事务基本语法:以BEGIN TRANSACTION显式开始,以COMMIT或ROLLBACK显式结束。
事务ACID特性:atomicity,consistency,isolation,durability.
atomicity:事务是最小原子,操作要么全做,要么全不做。
Consistency:数据库操作完成前一致,操作完成后一致。
isolation:操作完成前,对其他事务不可见。
durability:完成后,数据永存。

隔离级别:read uncommitted,read committed,repeatable read,serializable
隔离级别    脏读    不可重复读    虚拟读取(幻读)    加锁读
未提交读    是    是        是        否
已提交读    否    是        是        否
可重复读    否    否        是        否
可序列化    否    否        否        是
mysql默认策略:并且通过间隙锁防止幻读。
mysql> show variables like "%isolation%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

InnoDB是使用最广泛的引擎。
InnoDB在出现锁死时会返回错误,在mysql该引擎才支持事务(NDB cluster也支持)。
InnoDB行级锁工作的很好,很多情况避免加锁,提高效率。根据隔离级别自动加锁。
InnoDB支持热备份,崩溃损坏概率比MyISAM低。
MyISAM不支持事务和行级锁,支持全文索引(InnoDB+Sphinx来支持)。
---注:不得混合使用多引擎。
在autocommit禁用情况下才使用lock tables,否则不使用。不管什么引擎。

sysbench是一款开源的多线程性能测试工具,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL。

选择优化的数据类型。
1.更小的通常更好。2.简单就好:INT比byte操作代价更低;用内建类型而不用字符串保存日期,用INT储存IP。3.尽量避免NULL:null增加储存空间,索引更复杂,建立索引避免设计到有NULL列。

陷阱:太多的列,太多的关联(一个查询最好在12个表以内作关联),全能的枚举(防止过度使用枚举),变相枚举

第一范式:表无重复字段,字段不再分;第二范式:减少字段列的沉余(耦合)。反范式。
employee    department    head(部门领导)
Jones        Accounting    Jones
Smith        Engigeering    Smith
Brown        Accounting    Jones
Green        Engineering    Smith
如果修改head,开销比较大,沉余严重。
可以分为2个表:
1表:
employee    department
Jones        Accounting
Smith        Engigeering
Brown        Accounting
Green        Engineering
2表:
department    head(部门领导)
Accounting    Jones
Engineering    Smith

缓存表和汇总表:有点像中间表的应用。几个表和一(缓存),GROUP BY形成一表(汇总),视图,便于查找。
计数器表:放一个int unsigned型进行计数。表小且快。
更快的读,更慢的写:使用额外索引,添加沉余列,创建缓存表,汇总表,试图。但是写变慢。

ALTER TABLE对大表是个大问题,MYSQL先创建一个新表,将旧表所有数据插入新表,然后删除旧表。因为MODIFY修改字段类型,针对数据每一行数据。
ALTER TABLE 允许使用ALTER COLUMN,MODIFY COLUMN,CHANGE COLUMN修改字段。但是字段内容不一样。
ALTER TABLE mytable ALTER COLUMN tableColumn SET DEFAULT 5;该命令只修改.frm文件,而不涉及表数据。
ALTER TABLE mytalbe MODIFY tableColumn INT DEFAULT 5;重新创建表,复制,删除。
类型修改,只修改.frm文件快但是有风险。
步骤:
1.创建一个结构相同的空表,并进行所需要的修改(如:enum('G','PG','PG-13','R','NC-17','PG-14'),添加'PG-14',但是添加需要在尾部添加,放在'PG-13'后面造成'PG-14'替代'R'.)
2.执行FLUSH TABLES WITH READ LOCK;关闭正在使用的表,并禁止任何表被打开。
3.交换.frm文件。
4.执行UNLOCK TABLES;释放第二步的读锁。
CREATE TABLE film_new LIKE film;
ALTER TABLE film_new MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17','PG-14') DEFAULT 'G';
FLUSH TABLE WITH READ LOCK;
系统操作:mv film.frm film-tmp.frm && mv film_new.frm film.frm &&  mv film_tmp.frm film_new.frm;
UNLOCK TABLES;
DROP TABLE film_new;


索引InnoDB中是根据B+TREE数据结构,之用Memory引擎支持hash索引,创建多列索引,索引根据定义索引顺序的第一个字段进行索引,其他字段在第一个字段重复时再索引。
索引就是快速定位数据在表的地址,加快ORDER BY 和GROUP BY操作(不用排序了)。小表可以直接全表扫描更高效,大表索引非常有用。
describe select * from suremployer where num-1=201500007 \G   索引不能用与函数,表达式,这样失去了索引意义。num是索引。
前缀索引长度应当在7个字节内。
如果表多个字段建立一个索引,只有第一个索引有效。and与or:
select * from film_actor where actor_id=1 and film_id=1;两个字段分别建立索引,查询很快。
select * from film_actor where actor_id=1 or film_id=1;5.0以后,因为都是索引没问题,有归并动作,稍慢。
select * from film_actor where actor_id=1 UNION ALL select * from film_actor where actor_id<>1 AND film_id=1;5.0之前。
索引可以减少锁定行。
SELECT * FROM table where sex='m' order by rating limit 10;如果没有索引将会很慢。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:44044次
    • 积分:1180
    • 等级:
    • 排名:千里之外
    • 原创:73篇
    • 转载:25篇
    • 译文:0篇
    • 评论:1条
    最新评论