《高性能MySQL》

• 基本概念和性能剖析
• schema和数据类型优化
• 索引
• 查询性能优化

转载注明出处：http://blog.csdn.net/qq_20906499/article/details/51435241

基本概念和性能剖析

 

1、设置隔离级别

SET  SESSION  TRANSACTION  ISOLATION  LEVEL  READ  COMMITTED

2、显示表的相关信息

SHOW  TABLE  STATUS  LIKE  'user'  \G

3、分段复制表

INSERT  INTO  table1  SELECT  *  FROM  table2  WHERE  ID  BETWEEN  x  AND  y

4、幻读和不可重复读参考

• 幻读
  在事务A操作的过程中，事务B插入或删除了数据，导致事务A读取的数据不一致
• 不可重复读
  在事务A操作的过程中，事务B修改了数据，导致事务A读取的数据不一致

5、可用new relic做性能剖析，将来可用到rails中，分析渲染时间，数据库响应时间等。参考

6、性能剖析

最全面的是使用pt-query-digest分析慢查询日志

--show profile显示每条查询的响应时间 set  profiling=1;    --会话级别的改变 show profiles; --show status show status;      --显示超长

6、读锁是共享的，或者说是相互不阻塞的。多个客户端同时读取互不干扰。写锁是排他的，一个写锁会阻塞其他的写锁和读锁。 --4

schema和数据类型优化

1、使用正确存储数据的最小数据类型，使用内建类型（date，datetime，time）存储时间，使用整型存储ip --p112 

2、尽量避免使用NULL，如果要为某列建立索引该列要为NOT NULL --p112

3、小数计算时尽量使用float和double不要使用decimal（因为有额外的空间和计算开销），除非要精确存储财务信息时才使用，但也可以考虑使用bigint*缩放因子代替decimal --p114 

4、varchar在update行时容易产生内存碎片 --p115

5、可以使用FROM_UNIXTIME()把timestamp格式转换为datetime格式，用UNIX_TIMESTAMP()做相反的操作 --p122

6、尽量使用数字类型做比较和做标示符，因为比较快 --p125

7、Mysql执行大部分修改表结构的方法是用新的结构建立一个新表，然后从旧表中查出所有数据复制到新表，所以很慢。如果只改变某个列的默认值，可以使用:

ALTER  TABLE  tablename  ALTER  COLUMN  cname  SET  DEFAULT  5 这比使用 ALTER  TABLE  tablename  MODIFY  COLUMN  cname TINYINT  NOT  NULL  DEFAULT  5; 快得多，因为 alter  column 会直接修改.frm文件不涉及表数据，而 modify  column 将导致表重建  --p137

索引

1、索引可以包含多个列或一个列，如果包含多个列，那么列的顺序也很重要，因为MySQL只能高效的使用索引的最左前缀。--p142

2、如果需要存储大量的url，并根据需要根据url进行搜索查找，如果用B-Tree来存储url，索引文件就会很大，因为url都比较长。好的方法是删除原来url上的索引，新增一个被索引的url_crc列，使用crc32做哈希。例子：

SELECT  id  FROM  url  WHERE  url= "<a href="http://www.baidu.com/" "="" style="color: rgb(0, 51, 102) !important; text-decoration: none; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; border: 0px; bottom: auto; float: none; height: auto; left: auto; margin: 0px; outline: 0px; overflow: visible; padding: 0px; position: static; right: auto; top: auto; vertical-align: baseline; width: auto; box-sizing: content-box; min-height: inherit; background-position: 0px 50%; background-repeat: initial initial; ">http://www.baidu.com" AND  url_crc=CRC32( "<a href="http://www.baidu.com/" "="" style="color: rgb(0, 51, 102) !important; text-decoration: none; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; border: 0px; bottom: auto; float: none; height: auto; left: auto; margin: 0px; outline: 0px; overflow: visible; padding: 0px; position: static; right: auto; top: auto; vertical-align: baseline; width: auto; box-sizing: content-box; min-height: inherit; background-position: 0px 50%; background-repeat: initial initial; ">http://www.baidu.com" )

这样性能会很高，但要增加一个触发器，每插入一行数据都会自动生成url_crc列 --p149

3、应该养成简化where的习惯，始终将索引列单独放在比较符号的一侧，否则MySQL无法使用索引 --153

4、选择合适的前缀长度，首先计算完整列的选择性 --156

SELECT  COUNT ( DISTINCT  city)/ COUNT (*)  from  citydemo

然后计算不同长度的前缀的选择性

SELECT  COUNT ( DISTINCT  LEFT (city,x))/ COUNT (*)  from  citydemo

取不同的x使得第二个SELECT的值接近于第一个即可

5、以city字段的前七个字母创建前缀索引 --156

ALTER  TABLE  citydemo  ADD  KEY (city(7))

6、当使用多个AND进行where条件连接时，最好建立多列索引 --158

7、MyISAM索引和INNODB索引的区别

• MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持
• InnoDB支持事务，MyISAM不支持
• MyISAM顺序储存数据，索引叶子节点保存对应数据行地址，辅助索引跟主键索引相差无几；InnoDB主键节点同时保存数据行，其他辅助索引保存的是主键索引的值 --166
• MyISAM键值分离，索引载入内存（key_buffer_size）,数据缓存依赖操作系统，因为索引和数据是两个文件MYI和MYD；InnoDB键值一起保存，索引与数据一起载入InnoDB缓冲池，因为索引叶节点是数据
• MyISAM索引的基数值（Cardinality，show index 命令可以看见）是精确的，InnoDB则是估计值。这里涉及到信息统计的知识，MyISAM统计信息是保存磁盘中，在alter表或Analyze table操作更新此信息，而InnoDB则是在表第一次打开的时候估计值保存在缓存区内

8、建立多列索引时，最好将选择性好(COUNT(DISTINCT column)/count(*)越大越好)的列放在前面

9、总应该使用单调递增的id作为主键（聚簇）索引，这样可以使得数据顺序插入，减少页分裂，插入时间更短，索引大小更小 --169

10、覆盖索引，就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列和where条件都要被所使用的索引覆盖。 使用explain时如果extra为using index就是使用了覆盖索引。--171

11、使用explain时如果type为index，则说明使用了索引扫描进行了排序使用索引扫描来排序要满足的条件：--175

• 索引的列顺序和order by子句的顺序完全一致
• 并且所有列的排序方向都一样。
• order中的列要是使用的索引的最左前缀，且不能有其他列

12、如果创建了索引(A,B)再创建索引(A)就是冗余索引 --179

13、当索引越多时执行INSERT,UPDATE,DELETE速度越慢 --180

14、InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁 --182

15、使用索引的一个小窍门： 如果我们有索引(sex,contry,age),但只需要根据contry和age查询则理论上是使用不了这个索引的，因为不是最左前缀，如果要想使用该索引可以这样写

SELECT  *  FROM  table  WHERE  sex  IN  ( "male" , "female" )  AND  contry= "china"  AND  age = 25

使用sex的全集骗过MySQL --183

16、对于范围查询BETWEEN 和 < > MySQL无法再使用范围列后边的其他索引列了，使用BETWEEN的字段根据情况由系统判断是否使用索引，但对于多个等值查询IN则没有这个规则。 --185

17、查看表的索引信息（cardinality基数显示了存储引擎估算索引列有多少个不同的取值）

show  index  from  table

18、如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用 OPTIMIZE TABLE tablename进行表的优化。 被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。 对于不支持optimize的存储引擎，可以使用通过一个不做任何操作的alter table操作来重建表，只需要将存储引擎改为当前引擎即可

ALTER  TABLE  tablename ENGINE=innodb;

19、进行分表时，一般按行分100个表，根据id后两位判断存在哪个表中。 可以建立一个表单独存储这100个表中的自增id判断存到了多少。 可以再建立一个表存储表结构（其结构与分表结构相同），该表相当于一个视口，只存储分表中可用字段，当对100个分表进行结构调整时可以通过该表限制用户只能读写有效字段，分表结构改完后再修改该表结构与分表相同

查询性能优化

1、每次看到SELECT * 的时候都要多思考一下，是否真的需要返回全部列？取出全部列会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO、内存和CPU消耗。因此一些DBA是严格禁止SELECT * 的写法的。 --197

2、当需要重复查询相同的数据的地方最好把它缓存起来，比如用户头像的url，第一次查询的时候缓存起来，之后读取缓存。--198

3、较短的行访问速度更快，因为IO更少，内存中的行也比磁盘中的快 --199

4、服务器层没有统计信息，存储引擎存储了如每个表或者索引有多少个页面、每个表每个索引基数是多少、数据行和索引长度、索引的分部信息等统计信息，优化器根据这些信息选择最优执行计划。 --214

5、在使用join做关联查询时，优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树，基本原则就是小结果集驱动大结果集 -- 220

6、关联查询原理

SELECT  tbl1.col1, tbl2.col2 FROM  tbl1  JOIN  tbl2 USING(col3) WHERE  tbl1.col1  in (5,6)

会先查询tbl1中col1为5和6的行，然后利用这些行的col3字段去tbl2中找col3相等的行，最后取出这些数据 --216

7、使用IN加子查询性能经常会很差，详细原理请看书 --224

8、exists(subquery)关键字，对每行数据判断是否满足subquery，如果该行有数据返回则exists结果为true，否则为false（自己瞎编的，存疑）

9、使用UNION进行两个表合并时，如果只在最外层加入limit 20则会将两个表中数据全部取出来合并后取前20个，效率很低。一种优化的方式是在select两个表时就加入limit 20这样总共只会取出40条数据。 --228

10、如果对一个列建立了索引，则很快能读出min和max的值，也就是第一条和最后一条数据，若没建立索引只能进行全表扫描 --231

11、MySQL中不允许对同一张表进行同时查询和更新，即不能进行如下操作： --232

DELETE  FROM  Person WHERE  Id  IN ( SELECT  P1.Id  FROM  Person  AS  P1, Person  AS  P2 WHERE  P1.Id > P2.Id  AND  P1.Email = P2.Email);

一个变通方法是在内存中建立一个临时表，从临时表中进行select，而update真实的表

delete  from  Person where  Id  in ( select  p1.Id  from  ( select  *  from  Person) p1, ( select  *  from  Person) p2  where  p1.Email = p2.Email  and  p1.Id > p2.Id )

12、只有在MyISAM中执行没有where条件的count(*)时才会使用存储引擎的特性直接获得这个值。其他情况下MyISAM与其他引擎没区别 --237

13、通常来说count需要扫描大量的行才能获得精确的结果，因此较难优化，在MySQL层面能做的优化就是索引覆盖扫描了，如果这还不够就要增加汇总表。如果想获得近似的结果，使用explain看row字段就能满足要求，或使用show indexes看主键的cardinality（基数）字段。

14、优化limit，原始语句为 --241

SELECT  film_id, description  FROM  sakila.film  ORDER  BY  title LIMIT 50,5

如果这个表非常大，最好改为如下延迟关联方式（何谓"延迟关联" ：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。）

SELECT  film.film_id, film.description FROM  sakila.film    INNER  JOIN (      SELECT  film_id  FROM  sakila.film      ORDER  BY  title LIMIT 50,5    ) AS  lim USING(film_id)

在原语句中由于取出的字段较多要对一个较大的临时表进行排序然后丢弃不需要的数据行，但优化后使用覆盖索引对一个小数据表进行排序

15、变量的生命周期是在一个mysql连接过程中，定义： SET @var := 0  --244

16、MySQL排序有两种方式

• 第一种排序先扫描表取出行指针row_pointer和需要排序的字段key，然后根据(key,row_pointer)排序，排序完毕后根据row_pointer取出行数据。这种方法对内存占用较小，但需要对表进行两次随机IO，所以时间较长。如果select语句中又text或blob类型字段则一定会使用这种排序。
• 第二种排序根据where语句直接取出所有要查询的字段，然后根据orderby中的字段进行排序，这种方式占用内存较大，但避免了两次随机io，效率较高。如果要让系统尽可能的使用第二种排序，则应该将max_length_for_sort_data参数设置大一些参考1 参考2