第17章 常用SQL技巧和常见问题
17.1 正则表达式的使用
SELECT name,email FROM t WHERE email REGEXP "@163[.,]com$"
17.2 巧用RAND()提取随机行
SELECT * FROM sales2 ORDER BY RAND() LIMIT 5;
17.3 利用GROUP BY 的WITH ROLLUP子句做统计
SELECT year,country,product,sum(profit) FROM sales GROUP BY
year,country,product WITH ROLLUP;
WITH ROLLUP反映的是一种OLAP思想，也就是说这一个GROUP
BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序。换言之，ROLLUP和ORDER
BY是互相排斥的。此外，LIMIT用在ROLLUP后面。
第18章 SQL优化
18.1 优化SQL语句的一般步骤
1. 通过show status命令了解各种SQL的执行频率
show status like 'Com_%';
Com_XXX表示每个XXX语句执行的次数。
2. 定位执行效率较低的SQL语句
3. 通过EXPLAIN分析低效SQL的执行计划
4. 确定问题并采取相应的优化措施
18.2 索引问题
1. MySQL中索引类型目前只有BTREE和HASH两种，不支持函数索引。
MyISAM和InnoDB存储引擎都只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
2. 使用索引
1>对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用。
2>对于使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用。
3>如果对大的文本进行搜索，使用全文索引而不用使用like“%...%”.
4>如果列名是索引，使用column_name is null将使用索引。
18.3. 两个简单实用的的优化方法
1. 定期分析表和检查表
ANALYZE TABLE tbl_name
在分析期间，使用一个读取锁定对表进行锁定，这对于MyISAM,BDB和InnoDB有作用。
CHECK TABLE tbl_name
CHECK TABLE对于MyISAM和InnoDB有作用。也可以检查视图是否有错误。
2. 定期优化表
OPTIMIZE TABLE tbl_name
可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE
TABLE只对于MyISAM,BDB和InnoDB起作用。
18.4 常用SQL的优化
1. 大批量插入数据
对于MyISAM存储引擎的表：
ALTER TABLE tbl_name DISABLE KEYS;
LOAD DATA INFILE '/home/mysql/film_test.txt' INTO tbl_name;
ALTER TABLE tbl_name ENABLE KEYS;
DISABLE KEYS和ENABLE
KEYS用来打开或者关闭MyISAM表非唯一索引的更新。在倒入大量的数据到一个非空的MyISAM表时，用过设置这两个命令，可以提高导入的效率。对于倒入大量的数据到一个空的MyISAM表，默认就是先倒入数据然后创建索引。
对于InnoDB类型的表：
1>因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效提高导入数据的效率。
2>在导入数据前执行SET UNIQUE_CHECK=0,关闭唯一性校验，在导入结束后执行SET
UNIQUE_CHECK=1，恢复唯一性校验，可以提高导入的效率。
3>如果应用使用自动提交的方式，建议在导入前执行SET
AUTOCOMMIT=0,关闭自动提交，导入结束后再执行SET
AUTOCOMMIT=1,打开自动提交，也可以提高导入的效率。
2. 优化INSERT语句
1>如果从同一客户插入很多行，尽量使用多个值表的INSERT语句。
INSERT INTO test VALUES(1,2),(1,3),(1,4)...
2>如果从不同的客户插入很多行，能通过使用INSERT
DELAYED语句得到更高的速度。DELAYED的含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多。
3>将索引文件和数据文件分在不用的磁盘上存放。
4>如果进行批量插入，可以增加bulk_inset_buffer_size变量值的方法来提高速度，但是这只能对MyISAM表使用。
5>当从一个文本文件装载一个表时，使用LOAD DATA
INFILE.这通常比使用INSERT语句快20倍。
3.
使用SQL提示(SQL HINT)
1>在查询语句中表明的后面，添加USE
INDEX来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
EXPLAIN SELECT * FROM sales2 USE INDEX (ind_sales2_id) WHERE
id=3;
2>如果用户只是想让MySQL忽略一个或者多个索引，则可以使用IGNORE
INDEX作为HINT。
EXPLAIN SELECT * FROM sales2 IGNORE INDEX (ind_sales2_id) WHERE
id=3;
3>为强制MySQL使用一个特定的索引，可在查询中使用FORE INDEX作为HINT。
EXPLAIN SELECT * FROM sales2 FORE INDEX (ind_sales2_id) WHERE
id>0;
第19章 优化数据库对象
19.1
优化表的数据类型
在MYSQL中，可以使用函数PROCEDURE
ANALYSE()对当前应用的表进行分析，该函数可以对数据表中列的数据类型提出优化建议。
SELECT
* FROM tbl_name PROCEDURE ANALYSE();
SELECT
* FROM tbl_name PROCEDURE ANALYSE(16,256);
19.2
通过拆分提高表的访问效率
1.
垂直拆分，即把主码和一些列放放到一个表，然后把主码和另外的列放到另一个表中。
如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直拆分。垂直拆分可以是的数据航标小，一个数据页就能存放风多的数据，在查询时就会减少I/O次数。缺点是需要管理冗余列，查询所有数据需要联合(join)操作。
2.
水平拆分，即根据一类或多列数据的值把数据行放到两个独立的表中。
以下几种情况会用到水平拆分：
1>表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
2>表中的数据本来就有独立性，比如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些则不常用。
3>需要把数据存放到多个介质上。
19.3
逆规范化
常用的反规范技术有
增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。
增加派生列：指增加的列来自其他表中的数据，有其他表中的数据经过计算生成。增加的派生列其做实在查询时减少连接操作，避免使用集函数。
重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
分割表：参见19.2
逆规范技术需要维护数据的完整性。无论使用何种规范技术，都需要一定的管理来维护数据的完整性，常用的方法是批处理维护，应用逻辑和触发器。
1.批处理维护市支队复制烈火派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。
2.数据的完整性也可有应用逻辑来实现，这就要求必须在同一事物中对所有涉及的表进行增，删，改操作。用应用逻辑来丝线数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是在需求变化时，不易于维护。
3.另一种方式是使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题比较好的办法。
19.4
使用中间表提高统计查询速度
1.
中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不会对在线应用产生负面影响。
2.
中间表上可以灵活添加所引或增加临时用的新字段，从而达到提高统计查询效率和辅助系统查询作用。
第21章 优化MySQL Server
21.1
查看MySQL Server参数
SHOW
VARIABLES;
SHOW
STATUS;
mysqld
--verbose --help|more
21.2
影响MySQL性能的重要参数
1.
key_buffer_size的设置
这个参数是用来设置索引块(Index Blocks)缓存的大小，他被所有线程共享，此参数只适用于MyISAM存储引擎。
建立一个索引缓存：
SET
GLOBAL hot_cache2.key_buffer_size=128*1024;
把相关表的索引放到指定的索引缓存中：
cache
index sales,sales2 in hot_cache2;
要想将索引预装到默认key_buffer中：
LOAD
INDEX INTO CACHE sales;
需要删除索引缓存：
SET
GLOBAL hot_cache2.key_buffer_size=0;
2.table_cache的设置
这个参数表示数据库用户打开表的缓存数量。每个连接进来，都会至少打开一个表缓存。
因此table_cache与max_connections有关，例如，对于200个并行运行的链接，应该让表的缓存至少有200*N个，这里N是可以执行的查询的一个链接中表的最大数量。
可以通过检查mysqld的状态变量open_tables和opened_tables确定这个参数是否过小，这两个参数的区别是前者表示当前打开的表缓存数，如果执行FLUSH
TABLES操作，则此系统会关闭一些当前没有使用的表缓存而使得此状态值减少；后者表示曾经打开的表缓存数，会一直进行累加，如果执行FLUSH
TABLES操作，值不会减少。
3.innoda_buffer_pool_size的设置
这个参数定义了InnoDB存储引擎的表数据和索引数据的最大内存缓冲区大小。
4.innodb_flush_log_at_trx_commit的设置
这个参数是用来控制缓冲区中的数据写入到日志文件一记日志文件数据刷新到磁盘的操作时机。
默认值是1，也只最安全的设置。
1>.当这个参数是0的时候，日志缓冲每秒一次地被写到日志文件，并且对日志文件作向磁盘刷新的操作，但是在一个事务提交不做任何操作。
2>.当这个参数是1的时候，在每个事务提交时，日志缓冲被写到日志文件，并且对日志文件作向磁盘刷新的操作。
3>.当这个参数是2的时候，在每个事务提交时，日志缓冲被写到日志文件，但不对日志文件作向磁盘刷新的操作。日志文件每秒向磁盘做一次刷新操作。
5.innodb_additional_mem_pool_size的设置
这个参数是InnoDB存储引擎用来存储数据库结构和其他内部数据结构的内存池的大小，其默认值是1MB。
6.innodb_lock_wait_timeout的设置
MySQL可以自动地检测行所导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的检测。
7.innodb_support_xa的设置
是否支持分布式事务，默认是ON或者1.
8.innodb_log_buffer_size的设置
日子缓存的大小。通常设置为8-16MB。
9.innodb_log_file_size的设置
一个日志组(log group)中每个日志文件的大小。
第22章磁盘I/O问题
22.2
使用Symbolic Links分布I/O
MySQl的数据库名和表名是与文件系统的目录名和文件名对应的，默认情况下，创建的数据库和表都存放在参数datadir定义的目录下。我们可以利用操作系统的符号连接(Symbolic
Links)将不同的数据库或表,索引指向不同的物理磁盘，从而达到分布磁盘I/O的目的。
1>将一个数据库指向其它物理磁盘
方法是先在目标磁盘上创建目录，然后再创建从MySQL数据目录到目标目录的符号链接：
shell>mkdir /otherdisk/databases/test
shell>ln -s
/otherdisk/databases.test /path/to/datadir
2>将MyISAM(其他存储引擎的表不支持)表的数据文件或索引文件指向其它物理磁盘。
对于新建的表：
CREATE
TABLE test(id int primary key,
name
varchar(20))
TYPE =
myisam
DATA
DIRECTORY = '/disk2/data'
INDEX
DIRECTORY = '/disk2/data'
对于已有的表，可以先将其数据文件(.MYD)或索引文件(.MYI)转移到目标磁盘，然后再建立符号链接即可。需要说明的是表定义文件(.frm)必须位于MySQl数据文件目录下，不能用符号连接。
3>在Windows下，是通过在MySQL数据文件目录下创建包含目标路径并以“.sym”结尾的文本文件来实现的。例如，假设MySQL的数据文件目录是C:\mysql\data,要把数据库foo存放到D:\data\foo
创建目录D:\data\foo
创建文件C:\mysql\data\foo.sym,在其中输入D:\data\foo。
第23章 应用优化
1.
使用连接池
2.
减少对MySQL的访问
1>避免对同一数据做重复检索
2>使用查询缓存
3>增加cache层
3.
负载均衡
1>利用MySQL复制分流查询操作
2>采用分布式数据库架构
扫一扫
1103
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
