、我们可以且应该优化什么？硬件
操作系统/软件Software库
SQL服务器(设置和查询)
应用编程接口(API)
应用
--------------------------------------------------------------------------------
2、优化硬件
如果你需要庞大
安装运行
--------------------------------------------------------------------------------
十 4、重要
MySQL启动选项back_log 如果需要大量新连接
修改它thread_cache_size 如果需要大量新连接
修改它key_buffer_size 索引页池
可以设成很大bdb_cache_size BDB表使用
记录和键吗高速缓存Cachetable_cache 如果有很多
表和并发连接修改它delay_key_write 如果需要缓存Cache所有键码写入
设置它log_slow_queries 找出需花大量时间
查询max_heap_table_size 用于GROUP BY
sort_buffer 用于ORDER BY和GROUP BY
myisam_sort_buffer_size 用于REPAIR TABLE
join_buffer_size 在进行无键吗
联结时使用--------------------------------------------------------------------------------
十 5、优化表
MySQL拥有
套丰富类型你应该对每列尝试使用最有效类型ANALYSE过程可以帮助你找到表
最优类型:SELECT * FROM table_name PROCEDURE ANALYSE
对于不保存NULL值
列使用NOT NULL这对你想索引列尤其重要将ISAM类型
表改为MyISAM如果可能
用固定表格式创建表不要索引你不想用
东西利用MySQL能按
个索引前缀进行查询事实如果你有索引INDEX(a,b)你不需要在a上索引不在长CHAR/VARCHAR列上创建索引
而只索引列个前缀以节省存储空间CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))对每个表使用最有效
表格式在区别表中保存相同信息
列应该有同样定义并具有相同列名--------------------------------------------------------------------------------
十 6、MySQL如何次存储数据
数据库以目录存储
表以文件存储
列以变长或定长格式存储在文件中
对BDB表数据以页面形式存储支持基于内存
表数据库和表可在区别
磁盘上用符号连接起来在Windows上
MySQL支持用.sym文件内部符号连接数据库--------------------------------------------------------------------------------
十 7、MySQL表类型
HEAP表:固定行长
表只存储在内存中并用HASH索引进行索引ISAM表:MySQL 3.22中
早期B-tree表格式MyIASM:IASM表
新版本有如下扩展:2进制层次
可移植性NULL列索引
对变长行比ISAM表有更少
碎片支持大文件
更好
索引压缩更好
键吗统计分布更好和更快
auto_increment处理来自Sleepcat
Berkeley DB(BDB)表:事务安全(有BEGIN WORK/COMMIT|ROLLBACK)--------------------------------------------------------------------------------
十 8、MySQL行类型(专指IASM/MyIASM表)
如果所有列是定长格式(没有VARCHAR、BLOB或TEXT)
MySQL将以定长表格式创建表否则表以动态长度格式创建定长格式比动态长度格式快很多并更安全
动态长度行格式
般占用较少存储空间但如果表频繁更新会产生碎片在某些情况下
不值得将所有VARCHAR、BLOB和TEXT列转移到另个表中只是获得主表上更快速度利用myiasmchk(对ISAM
pack_iasm)可以创建只读压缩表这使磁盘使用率最小但使用慢速磁盘时这非常不错压缩表充分地利用将不再更新日志表--------------------------------------------------------------------------------
十 9、MySQL高速缓存Cache(所有线程共享
次性分配)键码缓存Cache:key_buffer_size
默认8M表缓存Cache:table_cache
默认64线程缓存Cache:thread_cache_size
默认0主机名缓存Cache:可在编译时修改
默认128内存映射表:目前仅用于压缩表
注意:MySQL没有行高速缓存Cache
而让操作系统处理--------------------------------------------------------------------------------
2十、MySQL缓存Cache区变量(非共享
按需分配)sort_buffer:ORDER BY/GROUP BY
record_buffer:扫描表
join_buffer_size:无键联结
myisam_sort_buffer_size:REPAIR TABLE
net_buffer_length:对于读SQL语句并缓存Cache结果
tmp_table_size:临时结果
HEAP表大小--------------------------------------------------------------------------------
2十
、MySQL表高速缓存Cache工作原理每个MyISAM表
打开例子(instance)使用个索引文件和个数据文件如果表被两个线程使用或在同条查询中使用两次MyIASM将共享索引文件而是打开数据文件另个例子如果所有在高速缓存Cache中
表都在使用缓存Cache将临时增加到比表缓存Cache尺寸大些如果是这样下个被释放表将被关闭你可以通过检查mysqld
Opened_tables变量以检查表缓存Cache是否太小如果该值太高你应该增大表高速缓存Cache--------------------------------------------------------------------------------
2十 2、MySQL扩展/优化-提供更快
速度使用优化
表类型(HEAP、MyIASM或BDB表)对数据使用优化
列如果可能使用定长行
使用区别
锁定类型(SELECT HIGH_PRIORITYINSERT LOW_PRIORITY)Auto_increment
REPLACE (REPLACE INTO table_name VALUES (...))
INSERT DELAYED
LOAD DATA INFILE / LOAD_FILE
使用多行INSERT
次插入多行SELECT INTO OUTFILE
LEFT JOIN, STRAIGHT JOIN
LEFT JOIN
结合IS NULLORDER BY可在某些情况下使用键码
如果只查询在
个索引中列将只使用索引树解决查询联结
般比子查询快(对大多数SQL服务器亦如此)LIMIT
SELECT * from table1 WHERE a > 10 LIMIT 10,20
DELETE * from table1 WHERE a > 10 LIMIT 10
foo IN (常数列表) 高度优化
GET_LOCK
/RELEASE_LOCKLOCK TABLES
INSERT和SELECT可同时运行
UDF
可装载进个正在运行服务器压缩只读表
CREATE TEMPORARY TABLE
CREATE TABLE .. SELECT
带RAID选项
MyIASM表将文件分割成很多文件以突破某些文件系统2G限制Delay_keys
复制功能
--------------------------------------------------------------------------------
2十 2、MySQL何时使用索引
对
个键码使用>, >=, =, 1 and key_part1 explain select t3.DateOfAction, t1.TransactionID-> from t1 join t2 join t3
-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
-> order by t3.DateOfAction, t1.TransactionID;
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort |
| t2 | ref | ID | ID | 4 | t1.TransactionID | 13 | |
| t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 | |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
ALL和范围类型提示
个潜在问题--------------------------------------------------------------------------------
2十 5、学会使用SHOW PROCESSLIST
使用SHOW processlist来发现正在做什么:
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| 6 | monty | localhost | bp | Query | 15 | Sending data | select * from station,station as s1 |
| 8 | monty | localhost | | Query | 0 | | show processlist |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
在mysql或mysqladmin中用KILL来杀死溜掉
线程--------------------------------------------------------------------------------
2十 6、如何知晓MySQL解决
条查询运行项列命令并试图弄明白其输出:
SHOW VARIABLES;
SHOW COLUMNS FROM ...G
EXPLAIN SELECT ...G
FLUSH STATUS;
SELECT ...;
SHOW STATUS;
--------------------------------------------------------------------------------
2十 7、MySQL非常不错
日志
在进行很多连接时
连接非常快同时使用SELECT和INSERT
场合在不把更新和耗时太长
选择结合时在大多数选择/更新使用唯
键码时在使用没有长时间冲突锁定
多个表时在用大表时(MySQL使用
个非常紧凑表格式)--------------------------------------------------------------------------------
2十 8、MySQL应避免
事情用删掉
行更新或插入表结合要耗时长SELECT在能放在WHERE子句中
列上用HAVING不使用键码或键码不够唯
而进行JOIN在区别列类型
列上JOIN在不使用=匹配整个键码时使用HEAP表
在MySQL监控
中忘记在UPDATE或DELETE中使用条WHERE子句如果想这样做使用mysql客户--i-am-a-dummy选项--------------------------------------------------------------------------------
2十 9、MySQL各种锁定
内部表锁定
LOCK TABLES(所有表类型适用)
GET LOCK
/RELEASE LOCK页面锁定(对BDB表)
ALTER TABLE也在BDB表上进行表锁定
LOCK TABLES允许
个表有多个读者和个写者般WHERE锁定具有比READ锁定高优先级以避免让写入方干等对于不重要写入方可以使用LOW_PRIORITY关键字让锁定处理器优选读取方UPDATE LOW_PRIORITY SET value=10 WHERE id=10;
--------------------------------------------------------------------------------
3十、给MySQL更多信息以更好地解决问题
窍门技巧 注意你总能去掉(加注释)MySQL功能以使查询可移植:SELECT /*! SQL_BUFFER_RESULTS */ ...
SELECT SQL_BUFFER_RESULTS ...
将强制MySQL生成
个临时结果集只要所有临时结果集生成后所有表上锁定均被释放这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助SELECT SQL_SMALL_RESULT ... GROUP BY ...
告诉优化器结果集将只包含很少
行SELECT SQL_BIG_RESULT ... GROUP BY ...
告诉优化器结果集将包含很多行
SELECT STRAIGHT_JOIN ...
强制优化器以出现在FROM子句中
次序联结表SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
强制MySQL使用/忽略列出
索引--------------------------------------------------------------------------------
3十
、事务例子MyIASM表如何进行事务处理:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer
total_value=sum_from_previous_statementwhere customer_id=some_id;
mysql> UNLOCK TABLES;
BDB表如何进行事务:
mysql> BEGIN WORK;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer
total_value=sum_from_previous_statementwhere customer_id=some_id;
mysql> COMMIT;
注意你可以通过下列语句回避事务:
UPDATE customer SET value=value+
_value WHERE customer_id=some_id;--------------------------------------------------------------------------------
3十 2、使用REPLACE
例子REPLACE
功能极像INSERT除了如果条老记录在个唯索引上具有和新纪录相同值那么老记录在新纪录插入前则被删除不使用 SELECT 1 FROM t1 WHERE key=#IF found-row
LOCK TABLES t1
DELETE FROM t1 WHERE key1=#
INSERT INTO t1 VALUES (...)
UNLOCK TABLES t1;
ENDIF
而用
REPLACE INTO t1 VALUES (...)
--------------------------------------------------------------------------------
3十 3、
般窍门技巧使用短主键
联结表时使用数字而非
串当使用多部分键码时
第部分应该时最常用部分有疑问时
首先使用更多重复列以获得更好地键码压缩如果在同
台机器上运行MySQL客户和服务器那么在连接MySQL时则使用套接字而不是TCP/IP(这可以提高性能7.5%)可在连接MySQL服务器时不指定主机名或主机名为localhost来做到如果可能
使用--skip-locking(在某些OS上为默认)这将关闭外部锁定并将提高性能使用应用层哈希值而非长键码:
SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
col_1='constant' AND col_2='constant'
在文件中保存需要以文件形式访问
BLOB在数据库中只保存文件名删除所有行比删除
大部分行要快如果SQL不够快
研究下访问数据较底层接口--------------------------------------------------------------------------------
3十 4、使用MySQL 3.23
好处MyISAM:可移植
大表格式HEAP:内存中
表Berkeley DB:支持事务
表众多提高
限制动态
集更多
STATUS变量CHECK和REPAIR表
更快
GROUP BY和DISTINCTLEFT JOIN ... IF NULL
优化CREATE TABLE ... SELECT
CREATE TEMPORARY table_name (...)
临时HEAP表到MyISAM表
自动转换复制
mysqlhotcopy脚本
--------------------------------------------------------------------------------
3十 5、正在积极开发
重要功能改进事务处理
失败安全
复制正文搜索
多个表
删除(的后完成多个表更新)更好
键码缓存Cache原子RENAME (RENAME TABLE foo as foo_old, foo_
as foo)查询高速缓存Cache
MERGE TABLES
个更好GUI客户
最新评论