MySQL数据库优化技巧（二）

最新推荐文章于 2024-11-15 18:38:47 发布

Lnho

最新推荐文章于 2024-11-15 18:38:47 发布

阅读量876

点赞数

分类专栏：数据库文章标签：数据库优化技巧 mysql

本文链接：https://blog.csdn.net/Lnho2015/article/details/45747257

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

在上一篇MySQL数据库优化技巧中，主要是从表设计的角度来优化，这篇将探讨一下其他的优化技巧。

1. 使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。
那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引。
索引有4种：
1）普通索引：最基本的索引类型，没有唯一性限制。
2）唯一性索引
3）主键：也是一种唯一性索引，必须指定为primary key。
4）全文索引
MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL 中是一个FULLTEXT类型索引，可在varchar或者text类型的列上创建，但仅能用于MyISAM 类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

2. 利用LIMIT 1取得唯一行

有时，当你要查询一张表是，你知道自己只需要看一行。你可能会去的一条十分独特的记录，或者只是刚好检查了任何存在的记录数，他们都满足了你的WHERE子句。
在这种情况下，增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。‘

3. 选取合适的存储引擎

1)MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子表)
2)InnoDB：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间(如果对安全要求高，则使用innodb)。[账户，积分]
3)Memory/heap [一些访问频繁，变化频繁，又没有必要入库的数据：比如用户在线状态]
说明: memory表的数据都在内存中，因此操作速度快，但是缺少是当mysql重启后，数据丢失，但表的结构在.
注：从mysql5.5.x开始，默认的存储引擎变更为innodb，innodb是为处理巨大数据量时拥有最大性能而设计的。它的 cpu效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

4. myisam表的定时维护

对于myisam 存储引擎而言，需要定时执行optimize table 表名，通过optimize table语句可以消除删除和更新造成的磁盘碎片，从而减少空间的浪费。
语法格式：optimizetable 表名
清理完成碎片之后会减少空间的浪费！

5. 分表优化

把一个大表分成几个小表：
1)垂直分割：采用内容主表+附加表的方式
内容主表：存储各种数据的一些公共信息，比如数据的名称，添加时间等，
可以使用多个附加表，附加表存储一些数据的独特的信息。
主要原因：是内容主表里面的数据访问比较频繁。
2)水平分割：采用对id取模的方式

6. 并发处理的锁机制

比如执行如下操作：
（1）从数据库中取出id值，
（2）进行加1操作。
（3）修改完成后，再保存到数据库中。
比如原来 id的值为100,==》101
以上步骤执行100次，最后变成200
有两个用户同时执行的话。
a用户：100->101
b用户：100->101
通过锁机制来进行解决
锁机制：在执行时，只有一个用户获得锁，其他用户处于阻塞状态，需要等待解锁。
mysql 的锁有以下几种形式：
表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam引擎属于这种类型。
行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb属于这种类型。
1、表锁的演示；
对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的操作。
表添加读锁后，其他进程对该表只能查询操作，修改时会被阻塞。
当前进程，能够执行查询操作，不能执行修改操作。不能对没有锁定的表进行操作。
锁表的语法：
lock table 表名 read|write
也可以锁定多个表，语法是：locktable 表1 read|wirte,表2 read|wirte
对myisam表的写操作（加写锁），会阻塞其他进程对锁定表的任何操作，不能读写，
表加写锁后，则只有当前进程对锁定的表，可以执行任何操作。其他进程的操作会被阻塞。
2、行锁的演示
innodb存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，innodb才会使用行级锁，否则，innodb使用表锁。
行锁的语法：
begin
sql语句
commit
开启行锁后，当前进程在针对某条记录执行操作时，其他进程不能操作和当前进程相同id的记录。

7. 大批量插入数据

1）对于MyISAM：
先禁用索引：
alter table table_name disable keys;
loading data//insert语句; 执行插入语句
执行完成插入语句后，开启索引，统一添加索引。
alter table table_name enable keys;
2）对于Innodb：
1，将要导入的数据按照主键排序
2，setunique_checks=0,关闭唯一性校验。
3，setautocommit=0,关闭自动提交。