MySQL性能优化之骨灰级高阶神技( 二 ) _小知识

有极端的情况，有些 ALTERTABLE 操作需要花费数个小时甚至数天才能完成。
这里推荐两种小技巧：

先把数据库拷贝到一台非生产服务器上，在上面做修改表操作，此时的修改不会影响生产库。

修改完毕以后在做数据库的切换，把非生产数据库切换成生产库。不过需要注意的时候，在做表结构修改的时候，生产库会生成一些数据。这里需要通过脚本根据时间区间导入这部分数据。

“影子拷贝”，即生成一张表结构相同的不同名新数据表(更改数据结构以后的表) 。

然后导入原表的数据到新表，导入成功以后停止数据库，修改原表和新表的名字，最终将数据访问指向新表。
在运行正常以后，将原表删除。这里有现成的工具可以协助完成上述操作，“online schema change”,”openark toolkit”
如果只是删除或者更改某一列的默认值，那么直接可以使用 Alert table modify column 和 Alert table alert column 来实现。
索引优化
说了字段和表再来聊聊索引。对于索引的优化网上有很多的说法，都是在实际工作中总结出来的，这里没有一定的标准。
针对我们使用比较多的 InnoDB 的存储引擎(使用的 B-Tree 索引)，推荐几个方法给大家。
索引独立
“索引独立”是指索引列不能是表达式的一部分，也不能是函数的参数。例如：假设 User 表中分别把 create_date 和 userId 设置为索引。
select *from user where date(create_date)=curdate() selectuserId from user where userId+1=5 类似上面的语句就是将索引作为了函数中的参数和表达式的一部分，是不推荐这样使用的。
前缀索引
有时候索引字段长度较大，例如：VarChar，Blob，Text 。当搜索的时候，这会让索引变得大且慢。
通常的做法是，可以索引开始的部分字符，这样可以节约索引空间，提高索引效率。
既然索引全部字符行不通，那么索引多少字符就是我们要讨论的问题了。
这里需要引入一个概念，索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。
索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。
例如：有一张 user 表，其中有一个字段是 FirstName，如何计算这个字段的选择性，如下：
Select1.0*count(distinct FirstName)/count(*) from user 假设这个结果是 0.75 再用 left 函数对该字段取部分字符，例如取从左开始的 3，4，5 个字段。
分别查看其选择性，目的是看当选择多少字符的时候，选择性最接近 0.75 。
从左取3个字段的时候，Select 1.0*count(distinct left(FirstName,3))/count(*) from user 结果为0.58从左取4个字段的时候，Select 1.0*count(distinct left(FirstName,4))/count(*) from user 结果为0.67从左取5个字段的时候，Select 1.0*count(distinct left(FirstName,5))/count(*) from user 结果为0.74 从上面尝试发现，字段 FirstName 取左边字符，从 3-5 的获取可以看出，当从左边取第 5 个字符的时候，选择性 0.74 最接近 0.75 。
因此，可以将 FirstName 的前面 5 个字符作为前缀索引，这样建立索引的效果基本和 FirstName 全部字符建立索引的效果一致。而又不用将 FirstName 整个字段都当成索引。
于是可以用下面语句修改索引信息：
Alter tableuser add key(FirstName(5)) 多列索引及其顺序
多列索引，顾名思义就是将多列字段作为索引。假设在 user 表中通过搜索 LastName 和 FirstName 条件来查找数据。
可能出现以下语句：
Select *from user where LastName = ‘Green’ Select *from user where LastName = ‘Green’ and FirstName = ‘Jack’ Select *from user where LastName = ‘Green’ and (FirstName = ‘Jack’ or FirstName =‘Michael’ Select *from user where LastName = ‘Green’ and FirstName >=‘M’ and FirstName<‘N’ 如果分别在 LastName 和 FirstName 上面建立索引：
Select *from user where LastName = ‘Green’ and FirstName = ‘Jack’ 当运行上面这段代码的时候，系统会让选择性高的 SQL 的索引生效，另外一个索引是用不上的。因此我们就需要建立多列索引(合并索引) 。
语句如下：
Alter table user add key(LastName, FirstName) 既然定义了多列索引，那么其中的索引顺序是否也需要考虑呢?在一个多列 B-Tree 索引中，索引列的顺序意味着，索引首先按照最左列进行排序，其次是第二列。
索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 ORDERBY、GROUPBY 和 DISTINCT 等子句的查询需求。