10分钟掌握 MySQL 优化技巧
本文的内容是总结一些MySQL的常见使用技巧，以供没有DBA的团队参考。以下内容以MySQL5.5为准，如无特殊说明，存储引擎以InnoDB为准。
MySQL的特点
了解MySQL的特点有助于更好的使用MySQL，MySQL和其它常见数据库最大的不同在于存在存储引擎这个概念，存储引擎负责存储和读取数据。不同的存储引擎具有不同的特点，用户可以根据业务的特点选择适合的存储引擎，甚至是开发一个新的引擎。
支持事务处理
支持行级锁
数据存储在表空间中，表空间由一些列数据文件组成
采用MVVC(多版本并发控制)机制实现高并发
表基于主键的聚簇索引建立
-
支持热备份
-
MyISAM：老版本MySQL的默认引擎，不支持事务和行级锁，开发者可以手动控制表锁；支持全文索引；崩溃后无法安全恢复；支持压缩表，压缩表数据不可修改，但占用空间较少，可以提高查询性能 -
Archive：只支持Insert和Select，批量插入很快，通过全表扫描查询数据 -
SCV：把一个SCV文件当做一个表处理 -
Memory：数据存储在内存中
数据类型优化
选择占用空间小的数据类型
选择简单的类型
-
避免不必要的可空列
整型类型
整型类型包括：
-
tinyint -
smallint -
mediumint -
int -
bigint
小数类型
float
double
-
decimal
字符串类型
-
varchar -
char -
varbinary -
binary -
blob -
text -
枚举
时间类型
-
year -
date -
time -
datetime -
timestamp
主键类型的选择
特殊类型的数据
索引优化
InnoDB使用B+树实现索引，举个例子，假设有个People，建表语句如下
CREATE TABLE `people` (`Id` int(11) NOT NULL AUTO_INCREMENT,`Name` varchar(5) NOT NULL,`Age` tinyint(4) NOT NULL,`Number` char(5) NOT NULL COMMENT '编号',PRIMARY KEY (`Id`),KEY `i_name_age_number` (`Name`,`Age`,`Number`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
也就是说，索引列的顺序很重要，如果两行数据的Name列相同，则用Age列比较大小，如果Age列相同，则用Number列比较大小。先用第一列排序，然后是第二列，最后是第三列。
如以下几个SQL是正面范例：
SELECT * from people where Name =’Abel’ and Age = 2 AND Number = 12312
SELECT * from people where Name =’Abel’
SELECT * from people where Name like ‘Abel%’
SELECT * from people where Name = ‘Andy’ and Age BETWEEN 11 and 20
SELECT * from people ORDER BY NAME
SELECT * from people ORDER BY NAME, Age
-
SELECT * from people GROUP BY Name
SELECT * from people where Age = 2
SELECT * from people where NAME like ‘%B’
SELECT * from people where age = 2
SELECT * from people where NAME = ‘ABC’ AND number = 3
-
SELECT * from people where NAME like ‘B%’ and age = 22
一个使用Hash值创建索引的技巧
再新建一个字段，比如叫做URL_CRC，专门放置URL的哈希值，然后给这个字段创建索引，查询时这样写：
select * from t where URL_CRC = 387695885 and URL = 'www.baidu.com'如果数据量比较多，为防止哈希冲突，可自定义哈希函数，或用MD5函数返回值的一部分作为哈希值：
SELECT CONV(RIGHT(MD5('www.baidu.com'),16), 16, 10)前缀索引
多列索引
上面提到的“People”上创建的索引即为多列索引，多列索引往往比多个单列索引更好。
对多个索引进行and查询时，应该创建多列索引，而不是多个单列索引
可以试试这样写的效果：
select * from t where f1 = 'v1' and f2 <> 'v2' union all select * from t where f2 = 'v2' and f1 <> 'v1'但也有例外，如果能确认某些查询是频繁执行的，则应该优先照顾这些查询的选择性，比如，如果上面的People表中Name的选择性大于Age，查询语句应该这样写:
select * from people where name = 'xxx' and age = xxName列放了索引中的左侧比较合适，但是如果某个SQL执行的频率最高，比如
select * from people where name = 'xxx' and age = 20聚簇索引
覆盖索引
重复索引和冗余索引
不使用的索引
索引使用总结
索引的三星原则：
索引将查询相关的记录按顺序放在一起则得一星
索引中的数据顺序和查询结果的排序一致则得一星
-
索引中包含了查询所需要的全部列则得一星
查询优化
查询慢的原因
是否向数据库请求了多余的行
是否向数据库请求了多余的列
是否重复多次执行了相同的查询
MySQL是否在扫描额外的记录
重构查询的方式
将一个复杂的查询分解成多个简单的查询
将大的查询切分成小的查询，每次查询功能一样，只完成一小部分
-
分解关联查询。可以将一个大的关联查询改成分别查询若干个表，然后在应用程序代码中处理
杂七杂八
优化count()
SELECT count(name like 'B%') from people可以使用近似值优化来代替count()，如执行计划中的行数。
索引覆盖扫描
增加汇总表
-
增加内存缓存系统记录数据条数
关联查询的优化
MySQL优化器关联表查询是这样进行的，比如有两个表A和B通过c列关联，MySQL会遍历A表，然后根据遍历到的c列的值去B表中查找数据。综上所述，通常，如无只需要给B表的c列加上索引即可
-
确保order by和group by涉及到的列只属于一个表，这样才有可能发挥索引的作用
优化子查询
优化group by、distinct
优化limit
SELECT * from sa_stockinfo ORDER BY StockAcc LIMIT 400, 5
SELECT * FROM sa_stockinfo i JOIN (SELECT StockInfoID FROM sa_stockinfo ORDER BY StockAcc LIMIT 400,5)t ON i.StockInfoID = t.StockInfoID
优化union
判断某条记录是否存在
select count(*) from t where condition
最好这样写：
SELECT IFNULL((SELECT 1 from tableName where condition LIMIT 1),0)
参考书
cnblogs.com/zzy0471/p/OptimizeMySQL.html