Mysql-SQL优化

一、概述：
Mysql数据库的优化技术，对mysql优化是一个综合性的技术，主要包括：
(1)表的设计合理化(符合3NF)
(2)添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
(3)分表技术(水平分割、垂直分割) ，参考 《【已实践】Mysql优化：数据划分.txt 》
(4)读写分离
(5)对mysql-DBMS配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
    1.    作者任务最大并发数调至到1000左右，多了内存无法承受
    2.    缓存大小设置
(6)mysql服务器硬件升级
(7)其他：1.定时的去清除不需要的数据,定时进行碎片整理(MyISAM)  2.存储过程 [模块化编程，可以提高速度] 优点：不需要再编译 缺点：移植性不好


二、使用场景及综合分析：
读写分离: 数据库并发大时选择，将读和写的操作分别放在多台机器上；由于读写的特点，可以有多台读的机器；
分表/分区/分库：一般在单库上，并发量没有成为瓶颈时，可以进行分表或纵向分割查询；只有当单表或多表数据量巨大时，进行分库；使用分布式数据库进行管理；

数据库优化有很多可以讲，按照支撑的数据量来分可以分为两个阶段：单机数据库和分库分表，前者一般可以支撑500W或者10G以内的数据，超过这个值则需要考虑分库分表。
另外，一般大企业面试往往会从单机数据库问起，一步一步问到分库分表，中间会穿插很多数据库优化的问题。本文试图描述单机数据库优化的一些实践，数据库基于mysql，如有不合理的地方，欢迎指正。
流程：单机数据库 --- 数据库优化 --(单机数据库一般可以支撑500W或者10G以内的数据)--分库分表


4、历史数据归档
当数据量到了一年增加500W条的时候，索引也无能为力
[1]这时候一般的思路都是考虑分库分表。
[2]如果业务没有爆发式增长，但是数据的确在缓慢增加，则可以不考虑分库分表这种复杂的技术手段，而是进行历史数据归档。我们针对生命周期已经完结的历史数据，比如6个月之前的数据，进行归档。我们可以使用quartz的调度任务在凌晨定时将6个月之前的数据查出来，然后存入远程的hbase服务器。当然，我们也需要提供历史数据的查询接口，以备不时之需。


分离意味着更多的服务器加入网站，提高网站处理能力，在网站发展的任何阶段，都可以在物理上分离不同的网站功能，实现网站伸缩性；
具体又可以分为如下两种情况：
(1)纵向分离(分层后分离)：将  "业务处理流程上的不同部分"  分离部署，实现系统伸缩性比如，网站具体产品-可复用业务服务-基础技术服务-数据库
(2)横向分离(业务分割后分离)：将不同的 "业务模块" 分离部署,实现系统伸缩性 比如，网站前台，卖家后台，买家后台，交易论坛


三、SQL优化的一般步骤：
1.通过show status命令了解各种SQL的执行频率。
2.定位执行效率较低的SQL语句-（重点select）
3.通过explain分析低效率的SQL语句的执行情况
4.确定问题并采取相应的优化措施

1.通过show status命令了解各种SQL的执行频率。
常用的：
-- SQL的运行时间
show  status like ‘uptime’ ; 
-- 了解各种SQL的执行频率。
show STATUS LIKE 'com_insert';
show STATUS LIKE 'com_update';
show STATUS LIKE 'com_delete';
show STATUS LIKE 'com_select';
show [session|global] status like .... 如果你不写  [session|global] 默认是session 会话，指取出当前窗口的执行，
如果你想看所有(从mysql 启动到现在，则应该 global)
-- 显示当前的连接数
show global status like 'connections';
-- 显示慢查询次数
show status like 'slow_queries';
-- 清空缓存


2.如何去定位慢查询
2.1 首先要创建这么多的数据
方法见：sql.txt
2.2 默认情况下，mysql认为10秒才是一个慢查询.
show variables like 'long_query_time'; -- 可以显示当前慢查询时间
set long_query_time=1 ;-- 可以修改慢查询时间
这时我们如果出现一条语句执行时间超过1秒中，就会统计到.
2.3 如果把慢查询的sql记录到我们的一个日志中
在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以

开启慢查询非常简单, 操作如下:
[1] 在linux系统下：
vi /etc/my.cnf, 在[mysqld]下方加入慢查询的配置语句
注意:一定要在[mysqld]下的下方加入, 如果是在[mysqld_safe]下面加入配置语句是不会生效的

[mysqld]
...
long_query_time= 2
slow-query-log = on
slow-query-log-file = /home/mysql/logs/mysql-slow.log

slow-query-log-file: 代表MYSQL慢查询的日志存储目录, 此目录文件一定要有写权限.
long_query_time: 最长执行时间.
注：MSYQL将记录下所有执行时间超过2条的SQL语句, 此处为测试时间, 时间不应太小最好在5-10秒之内, 当然可以根据自己的标准而定);
重启
service mysqld restart

[2]windows下：
bin\mysqld.exe - -safe-mode  - -slow-query-log [mysql5.5 可以在my.ini指定]
bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在
my.ini 文件中记录的位置
#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

2.4 测试,可以看到在日志中就记录下我们的mysql慢sql语句.
select * from emp

-- 清除查询缓存
reset query cache;

tail -f /home/mysql/logs/mysql-slow.log
3.通过explain分析低效率的SQL语句的执行情况
参考<<优化：Explain.ppt>>
SQL执行计划解读：http://blog.csdn.net/c_enhui/article/details/9021271

4.确定问题并采取相应的优化措施


四、具体分析：

【表的设计合理化】
数据库的表结构设计往往会影响应用后期的性能，特别是用户量上来了以后的性能：

1.符合3NF
表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF
1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF
2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键一般不含业务逻辑，设置为自增长
3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF:
反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

在表的1对N的情况下，为提高效率，可能会在1这表中设计字段提速

2.字符集
一般来说尽量选择UTF-8
虽然在存储的时候GBK比UTF-8使用的存储空间少，但是UTF-8兼容各国语言，其实我们不必为了这点存储空间而牺牲了扩展性。
事实上，后期如果要从GBK转为UTF-8所要付出的代价是很高的，需要进行数据迁移，而存储空间完全可以用花钱扩充硬盘来解决。

3.主键
在使用mysql的innodb的时候,设计表的时候需要： 增加一个主键，而且最好要自增。
对于插入：
因为自增主键可以让插入的数据按主键顺序插入到底层的B+树的叶子节点中，由于是按序的，这种插入几乎不需要去移动已有的其它数据，所以插入效率很高。
如果主键不是自增的，那么每次主键的值近似随机，这时候就有可能需要移动大量数据来保证B+树的特性，增加了不必要的开销。
对于查询：
原因是innodb的底层存储模型是B+树，它使用主键作为聚簇索引，使用插入的数据作为叶子节点，通过主键可以很快找到叶子节点，从而快速获取记录

4.字段
(1)只含数值信息的字段只使用数字型字段
若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
(2) 存储小数，建议使用decimal,
不建议使用float、double来存小数，会损失精度
(3)尽量使用varchar/nvarchar 代替 char/nchar 首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。其次，varchar类型长度,建议不要超过8K。
在设计长度时，括号里的数字尽可能少，除了需要进行填充
(4)时间类型建议使用Datetime，
不要使用timestamp，虽然Datetime占用8个字节，而timestamp只占用4个字节，但是后者要保证非空，而且后者是对时区敏感的。
(5)保存大量数据,建议将大文本数据保存在专门的文件存储系统中，mysql中只保存这个文件的访问地址，比如博客文章可以保存在文件中，mysql中只保存文件的相对地址,不建议使用Text/blob来保存大量数据，因为对大文本的读写会造成比较大的I/O开销，同时占用mysql的缓存，高并发下会极大的降低数据库的吞吐量
(6) 在设计字段时，建议字段必须加上not null约束，并且设置default值，尤其是索引字段和查询条件必须设置
(7) 建议表中增加gmt_create和gmt_modified两个字段，用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。



【SQL优化】
一般来说sql就那么几种:基本的增删改查，分页查询，范围查询，模糊搜索，多表连接

1.查询基本说明
1.1 一般情况下，查询需要走索引，若没有索引建议修改查询，把有索引的那个字段加上；
1.2 如果由于业务场景没法使用这个字段，那么需要看这个查询调用量大不大：
如果大，比如每天调用10W+，这就需要新增索引，
如果不大，比如每天调用100+，则可以考虑保持原样。
注：
[1]任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段，用到什么字段就在sql语句中加什么，不必要的字段就别查了，浪费I/O和内存空间。
[2]不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0    这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)

2.条件查询
在Mysql中:
2.1 避免在where子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null
做法：可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

2.2 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
做法：若不得已尽量放在走索引的查询条件之后

2.3 应尽量避免在 where 子句中使用 or 来连接条件，or需要后面条件都在索引内，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or no=20
做法1
select id from t where num=10
union all
select id from t where no=20
做法2：
都做索引

2.4 in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)
in查询的条件有数量的限制，若数量较小可以走索引查询，若数量较大，就成了全表扫描了。
做法：对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

2.5 between、大于、小于等，这些查询不会走索引，所以尽量放在走索引的查询条件之后。

2.6 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)
用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

2.7 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

2.8 模糊查询like
使用 like %name%这样的语句是不会走索引的，相当于全表扫描，数据量小的时候不会有太大的问题，数据量大了以后性能会下降的很厉害，
建议
[1]数据量大了以后使用搜索引擎来代替这种模糊搜索，实在不行也要在模糊查询前加个能走索引的条件。
[2]若要提高效率，可以考虑全文检索。

2.9 在 where 子句中使用参数，也会导致全表扫描
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。
然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
[1]如下面语句将进行全表扫描：select id from t where num=@num
建议： 改为强制查询使用索引：select id from t with(index(索引名)) where num=@num
[2]避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:select id from t where num=100*2
[3]避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=’abc’     –name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0   –‘2005-11-30’生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′


3.子查询和多表连接
3.1 有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表
select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]
select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接，更ok!]
3.2 对于mysql的join，它用的是Nested Loop Join算法，也就是通过前一个表查询的结果集去后一个表中查询，比如前一个表的结果集是100条数据，后一个表有10W数据，那么就需要在100*10W的数据集合中去过滤得到最终的结果集。
因此，尽量用小结果集的表去和大表做join，
3.3 同时在join的字段上建立索引，如果建不了索引，就需要设置足够大的join buffer size。
3.4 如果以上的技巧都无法解决join所带来的性能下降的问题，那干脆就别用join了，将一次join查询拆分成两次简单查询。
3.5 多表连接尽量不要超过三张表，超过三张表一般来说性能会很差，建议拆分sql。

4.在使用group by 分组查询是，默认分组后，还会排序，可能会降低速度.
做法：select * from temp group by dname order by null;


【高效分页】
limit m,n实质就是先执行limit m+n，然后从第m行取n行，这样当limit翻页越往后翻m越大，性能越低。比如
select * from A limit 100000,10
这种sql语句的性能是很差的，建议改成下面的版本:
select id,name,age from A where id >=(select id from A limit 100000,1) limit 10
注，若ID没有自增生成，使用可能有问题
以下方法已测试，参考 http://www.111cn.net/database/mysql/50921.htm
分页查询优化：
http://www.111cn.net/database/mysql/50921.htm
总结：
最近一个网站的服务器评论被人刷死，导致mysql数据库异常发生too many open connections
引发的SQL语句：
SELECT a.uid, a.veil, a.content, a.datetimes, a.audit, b.user_name, b.uc_id
FROM news_talkabout a
LEFT JOIN users_info b ON a.uid = b.id
WHERE infoid =11087
ORDER BY a.id DESC
LIMIT 451350 , 30
丢在phpmyadmin里执行一下，是很慢。

分析：
SELECT a.uid, a.veil, a.content, a.datetimes, a.audit, b.user_name, b.uc_id
FROM news_talkabout a
LEFT JOIN users_info b ON a.uid = b.id
WHERE infoid =11087
ORDER BY a.id DESC
LIMIT 0 , 30
第一页会很快
来自雅虎的几位工程师带来了一篇"EfficientPagination Using MySQL"的报告：
limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。
LIMIT 451350 , 30 扫描了45万多行，怪不得慢的数据库都堵死了。
但是，limit 30 这样的语句仅仅扫描30行。
如下：
select id,name,content from users order by id asc limit 100000,20扫描100020行 如果记录了上次的最大ID
select id,name,content from users where id>100073 order by id asc limit 20扫描20行。
总数据有500万左右
优化前： select * from wl_tagindex where byname='f' order by id limit 300000,10 执行时间是 3.21s
优化后：select * from (
            select id from wl_tagindex
            where byname='f' order by id limit 300000,10
) a
left join wl_tagindex b on a.id=b.id 执行时间为 0.11s 速度明显提升
这里需要说明的是 我这里用到的字段是 byname ,id 需要把这两个字段做复合索引，否则的话效果提升不明显

总结
当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。
如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。
如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。
SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);
如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并且auto_increment
SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;
这条语句，大意如下:
SELECT * FROM users WHERE uid >=  (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10;
如果limit的offset值过大，用户也会翻页疲劳，你可以设置一个offset最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户。

继续见： http://www.111cn.net/database/mysql/50921.htm


【选择mysql的存储引擎】
在开发中，经常使用的存储引擎 myisam / innodb/ memory
myisam 存储: MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持,如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.
INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。


问 MyISAM 和 INNODB的区别
1. 事务安全
2. 查询和添加速度
3. 支持全文索引
4. 锁机制
5. 外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)
Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.
图：比较

注：如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理；
因为使用这个引擎，删除数据时，数据文件的大小不会发生改变；
optimize table test100;


以下是一些细节和具体实现的差别：

1. InnoDB不支持 FULLTEXT 类型的索引 ( 目前只有MyISAM表支持，且只能用在 CHAR , VARCHAR , TEXT 类型的字段上 )
2. InnoDB中不保存表的具体行数，也就是说，执行 select count(*) from table 时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3. 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4. DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5. LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如 update table set num=1 where name like '%wfc%'
任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势.




















【索引】
索引是解决查询效率的利器，当数据量增加到一定程度后，靠sql优化已经无法提升性能了，这时候就需要祭出大招：索引。

1.为什么创建索引后，速度就会变快
原理图
解析：没有索引会进行全表扫描，即便已经找到，还要判断后面是否还要符合条件的。
.MYI：二叉树，即索引文件，假如11条数据，找到中间值数据6，并记录6磁道(磁盘物理地址)，然后在6左边(比6小的数)找到中间数值3，记录树，假设向上取整则2(记录磁道)，右边；例如：若取4，4<6,则取左边，和3比,大于3则，则和4比较，等于4，直接根据磁道获得记录，扫描了3次，11次扫描变成了。
Log2N,N是检索次数，例如2次，则为1024；30次则为2的30次方，理论可扫描2的30次方数据
注，移植时，因为磁道地址变化，需要重建索引

2.索引使用的注意事项
索引的代价:
1.    占用磁盘空间
2.    对dml操作有影响，变慢：需要对二叉树进行维护，但是查询和DML操作相比，网上统计几乎是9：1


3.如何查看索引使用的情况:
show status like 'Handler_read%';
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。

查询索引
desc 表名 【该方法的缺点是：　不能够显示索引名.】
show index(es) from 表名
show keys from 表名
修改索引： 先删除，再重新创建.

4.索引分类
主键索引/唯一索引/全文索引/普通索引
全文索引，主要是针对对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用

 
5.索引有三级，一般来说掌握这三级就足够了，另外，对于建立索引的字段，需要考虑其选择性。
5.1 一级索引:条件上建立索引
在where后面的条件上建立索引，单列可以建立普通索引，多列则建立组合索引。组合索引需要注意最左前缀原则。
详细：参考  <<索引建立的原则>>

5.2 二级索引: 有被order by或者group by用到的字段
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
如果有被order by或者group by用到的字段，则可以考虑在这个字段上建索引，这样一来，由于索引天然有序，可以避免order by以及group by所带来的排序，从而提高性能。

5.3 三级索引: 所查询的字段也加上索引
如果上面两招还不行，那么就把所查询的字段也加上索引，这时候就形成了所谓的索引覆盖，这样做可以减少一次I/O操作，因为mysql在查询数据的时候，是先查主键索引，然后根据主键索引去查普通索引，然后根据普通索引去查相对应的记录。如果我们所需要的记录在普通索引里都有，那就不需要第三步了。
注，当然，这种建索引的方式比较极端，不适合一般场景。

5.4 索引的选择性: 选择性高的字段,索引并不是越多越好(6)

[1]并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
在建立索引的时候，尽量在选择性高的字段上建立。什么是选择性高呢？
所谓选择性高就是通过这个字段查出来的数据量少，比如按照名字查一个人的信息，查出来的数据量一般会很少，而按照性别查则可能会把数据库一半的数据都查出来，所以，名字是一个选择性高的字段，而性别是个选择性低的字段。
该字段的内容不是唯一的几个值(sex)

[2]索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

[3]应尽可能的避免更新 clustered 索引数据列，因为 clustered (字段内容不是频繁变化) 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

注：
1在设计阶段，由于对业务并不了解，所以尽量不要盲目加索引，只为一些一定会用到索引的字段加普通索引。
2创建innodb单列索引的长度不要超过767bytes，如果超过会用前255bytes作为前缀索引
3创建innodb组合索引的各列索引长度不要超过767bytes，一共加起来不要超过3072bytes


5.索引建立的原则
5.1 用于索引的最好的备选数据列:  那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。
仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列
SELECT
col_a <- 不是备选列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 备选列
WHERE
col_d = expr; <- 备选列
当然，显示的数据列与WHERE子句中使用的数据列也可能相同。我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。

5.2 复合索引的建立以及最左前缀原则
(1)如果有CHAR(200)数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

(2)假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使[1]你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：
state, city, zip
state, city
state
[2]MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围
[3]考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了(例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引)。

(3)在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引

(4) 在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用
5.3 对于使用like的查询，查询如果是  ‘%aaa’ 不会使用到索引,‘aaa%’可以
5.4 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.
5.5 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。



【MySQL插入语句insert性能优化】
对于一些数据量较大的系统，面临的问题除了是查询效率低下，还有一个很重要的问题就是插入时间长。当导入的数据量较大时，插入操作耗费的时间相当可观。因此，提高大数据量系统的MySQL insert效率是很有必要的。

(1) 一条SQL语句插入多条数据
执行效率高的主要原因有两个，一是减少SQL语句解析的操作， 只需要解析一次就能进行数据的插入操作，二是SQL语句较短，可以减少网络传输的IO
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES  ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1); 
mybatis :http://chenzhou123520.iteye.com/blog/1583407/

性能：这里提供一些测试对比数据，分别是进行单条数据的导入与转化成一条SQL语句进行导入，分别测试1百、1千、1万条数据记录。
记录数     单条数据插入     多条数据插入
1百     0.149s               0.011s
1千     1.231s               0.047s
1万     11.678s           0.218s

(2)使用事务可以提高数据的插入效率，
原因在于进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内进行真正插入处理。
通过使用事务可以减少数据库执行插入语句时多次“创建事务，提交事务”的消耗，所有插入都在执行后才进行提交操作。

性能：这里也提供了测试对比，分别是不使用事务与使用事务在记录数为1百、1千、1万的情况。
记录数     不使用事务     使用事务
1百     0.149s          0.033s
1千     1.231s          0.115s
1万     11.678s      1.050s

性能测试：这里提供了同时使用上面两种方法进行INSERT效率优化的测试。即多条数据合并为同一个SQL，并且在事务中进行插入。
记录数     单条数据插入     合并数据+事务插入
1万     0m15.977s           0m0.309s
10万     1m52.204s           0m2.271s
100万     18m31.317s           0m23.332s
从测试结果可以看到，insert的效率大概有50倍的提高，这个一个很客观的数字。

【谨慎使用临时表，游标，存储过程】
(1) 临时表
[1]尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
[2]临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。
[3]在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
[4]如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
[5]避免频繁创建和删除临时表，以减少系统表资源的消耗

(2) 游标
[1] 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
[2] 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
[3] 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

(3) 存储过程和触发器
在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

8.避免大事务的操作
(1) 尽量避免大事务操作，提高系统并发能力。
(2) 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。


【数据库连接池优化】
数据库连接池本质上是一种缓存，它是一种抗高并发的手段。数据库连接池优化主要是对参数进行优化，一般我们使用DBCP连接池，它的具体参数如下：
(1) initialSize
初始连接数，这里的初始指的是第一次getConnection的时候，而不是应用启动的时候。初始值可以设置为并发量的历史平均值
(2) minIdle
最小保留的空闲连接数。DBCP会在后台开启一个回收空闲连接的线程，当该线程进行空闲连接回收的时候，会保留minIdle个连接数。一般设置为5，并发量实在很小可以设置为1.
(3) maxIdle
最大保留的空闲连接数，按照业务并发高峰设置。比如并发高峰为20，那么当高峰过去后，这些连接不会马上被回收，如果过一小段时间又来一个高峰，那么连接池就可以复用这些空闲连接而不需要频繁创建和关闭连接。
(4) maxActive
最大活跃连接数，按照可以接受的并发极值设置。比如单机并发量可接受的极值是100，那么这个maxActive设置成100后，就只能同时为100个请求服务，多余的请求会在最大等待时间之后被抛弃。这个值必须设置，可以防止恶意的并发攻击，保护数据库。
(5) maxWait
获取连接的最大等待时间，建议设置的短一点，比如3s，这样可以让请求快速失败，因为一个请求在等待获取连接的时候，线程是不可以被释放的，而单机的线程并发量是有限的，如果这个时间设置的过长，比如网上建议的60s，那么这个线程在这60s内是无法被释放的，只要这种请求一多，应用的可用线程就少了，服务就变得不可用了。
(6) minEvictableIdleTimeMillis
连接保持空闲而不被回收的时间，默认30分钟。
(7) validationQuery
用于检测连接是否有效的sql语句，一般是一条简单的sql，建议设置
(8) testOnBorrow
申请连接的时候对连接进行检测，不建议开启，严重影响性能
(9) testOnReturn
归还连接的时候对连接进行检测，不建议开启，严重影响性能
(10) testWhileIdle
开启了以后，后台清理连接的线程会没隔一段时间对空闲连接进行validateObject，如果连接失效则会进行清除，不影响性能，建议开启
(11) numTestsPerEvictionRun
代表每次检查链接的数量，建议设置和maxActive一样大，这样每次可以有效检查所有的链接。
(12) 预热连接池
对于连接池，建议在启动应用的时候进行预热，在还未对外提供访问之前进行简单的sql查询，让连接池充满必要的连接数。