Mysql数据优化--DBA梳理珍藏篇
1. 优化SQL
1) 通过show status了解各种sql的执行频率
show status like 'Com_%'
了解 Com_select,Com_insert 的执行次数
2) 通过Explain分析低效的sql语句
3) 建立合适的索引
4) 通过show status like 'Handler_%'查看索引的使用情况
a) handler_read_key：根据索引读取行的请求数。如果该值很大，说明你的查询和表都建立了很好的索引，表明索引效率的很高
b) Handler_read_rnd_key：根据固定位置读取行的请求数。如果你执行很多需要排序的查询，该值会很高。你可能有很多需要完整表扫描的查询，或者你使用了不正确的索引用来多表查询。
c) Handler read rnd next：从数据文件中读取行的请求数。如果你在扫描很多表，该值会很大。通常情况下这意味着你的表没有做好索引，或者你的查询语句没有使用好索引字段。
5) 定期分析表和检查表
analyze table test_table和check table test_table，然后查看Msg_text字段的值是否是ok
6) 定期优化表 optimize table test_table
如果对表的可变字段varchar blob，text等进行了很多更改，则应用optimize优化。在多数的设置中，您根本不需要运行optimize table。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。optimize table只对MyISAM, BDB和InnoDB表起作用。
对于MyISAM表，optimize table按如下方式操作：
(1)如果表已经删除或分解了行，则修复表。
(2)如果未对索引页进行分类，则进行分类。
(3)如果表的统计数据没有更新(并且通过对索引进行分类不能实现修复)，则进行更新。
7) 优化order by，or，group by等
2. 优化数据库对象
2.1 选择表合适存储引擎
MyISAM: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的.
InnoDB： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（InnoDB有效地降低删除和更新导致的锁定）。对于支持事务的InnoDB类型的表来说，影响速度的主要原因是autocommit默认设置是打开的，而且程序没有显式调用begin 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。
Memory：数据保存在RAM,快速访问数据。要求表不能太大或者对mysql异常终止后不用恢复数据的
Merge:
2.2 优化表的数据类型,选择合适的数据类型
原则：更小通常更好，简单就好，所有字段都得有默认值,尽量避免null：
例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型.(mediumint就比int更合适)；比如时间字段：datetime和timestamp, datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970—2037适合做更新时间。
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为char(255),显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的，因为char(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用mediumint而不是bigint来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为not null，这样在将来执行查询的时候，数据库不用去比较null值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
2.3 字符串数据类型：char，varchar，text选择区别
1、长度的区别，char范围是0～255，varchar最长是64k，但是注意这里的64k是整个row的长度，要考虑到其它的column，还有如果存在not null的时候也会占用一位，对不同的字符集，有效长度还不一样，比如utf8的，最多21845，还要除去别的column，但是varchar在一般情况下存储都够用了。如果遇到了大文本，考虑使用text，最大能到4G。
2、效率来说基本是char > varchar > text，但是如果使用的是InnoDB引擎的话，推荐使用varchar代替
3、默认值char和varchar可以有默认值，text不能指定默认值
2.4 MySQL中float数据类型的问题
(1) .Float或Double列与具有数值类型的数值进行比较，不能使用等式(=)比较.这个是因为浮点数精度的问题,会产生误差。
(2)对货币等对精度敏感的数据，应该用定点数表示或存储
数据库选择合适的数据类型存储还是很有必要的，对性能有一定影响。这里在零碎记录两笔，对于int类型的，如果不需要存取负值，最好加上unsigned；对于经常出现在where语句中的字段，考虑加索引，整型的尤其适合加索引。
2.5 InnoDB数据表设计中需要的注意点
1、显式的定义一个int类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途；
2、如果不显式定义主键的话，可能会导致InnoDB每次都需要对新数据行进行排序，严重损害性能；
3、尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能；
4、如果需要对主键字段进行更新，请将该字段转变成一个唯一索引约束字段，另外创建一个没有其他业务意义的自增字段做主键；
5、主键字段类型尽可能小，能用smallint就不用int，能用int就不用bigint；
6、主键字段放在数据表的第一顺序；
3. 优化索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和order by这些命令的时候，性能提高更为明显.
那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于Join, Where判断和order by排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用Alter table或Create index在以后创建索引
3.1 普通索引
普通索引(由关键字Key或index定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(Where column = …)或排序条件(Order by column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
3.2 唯一索引
普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个”员工个人资料”数据表里可能出现两次或更多次。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字Unique把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
3.3 主索引
在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是Primary而不是Unique。
3.4 外键索引
如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
3.5 复合索引
索引可以覆盖多个数据列，如像Index(colA, colB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到colA数据列上的一个索引，就可以使用复合索引Index(colA, colB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，Index(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。
4. 表锁的问题
跟性能相关的最重要的区别就是 MyISAM 和 InnoDB 实现的锁机制不一样！ MyISAM 使用的是表锁， 而 InnoDB实现的是行锁。
4.1 MyISAM为表级锁
由于MyISAM写进程优先获得锁，使得读锁请求靠后等待队列。不仅如此，即使读请求先到锁等待队列，写请求后 到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。如果在大量更新操作的情况下，使得很难获得读锁。从而造成阻塞。所以MyISAM不适合做大量更新操作的原因
4.2 INNODB的行锁是基于索引实现
如果不通过索引访问数据，Innodb会使用表锁，表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又并发查询。因为只锁定要操作的行， 所以可以多个线程同时操作不同的行（只要不操作其他线程已经锁定的行）。
5. MySQL server服务器配置优化
1）使用show variables了解服务器参数
2）show status了解服务器运行状态，如锁等待情况，当前连接数等
3）影响mysql性能的重要参数：
l key_buffer_size设置索引块的缓存大小，key_buffer_size是对MyISAM表性能影响最大的一个参数，通过：
|
mysql> show global status like 'key_read%'; |
l Key_read_requests：从缓存读取索引的请求次数。
l Key_reads：从磁盘读取索引的请求次数。
一般Key_read_requests/Key_reads越大越好，需要适当加大key_buffer_size
l table_cache数据库打开表的缓存数量，每个连接进来，都会至少打开一个表缓存。因此table_cache和max_connections有关， 例如 对于200个并行运行的连接，应该让表的缓存至少是200 *N；N是可以执行查询的一个连接中的表的最大数
l 还有innodb_buffer_pool_size等innodb参数的设置
6. 磁盘IO优化
对于我们数据库调优来说，磁盘I/O优化是首屈一指的调优重点，我们都知道木桶原理，短板决定整体的好坏，而数据库系统中这个短板正是由于我们使用的硬件设备里最弱的磁盘所导致。很多时候，我们会发现系统中I/O累得要死，而CPU却在那里空闲等待，主要是由于I/O执行响应时间太长，处理读写的速度远远落后于CPU的处理速度，这时我们会尽可能的让操作放到内存中进行，由磁盘与CPU的关系，转变成内存与CPU的关系。但是，我们始终不能回 避磁盘I/O的弱点，优化是必须的。
磁盘搜索是巨大的性能瓶颈。当数据量变得非常大以至于缓存性能变得不可能有效时，该问题变得更加明显。对于大数据库，其中你或多或少地随机访问数据，你可以确信对读取操作需要至少一次硬盘搜索，写操作需要多次硬盘搜索。要想使该问题最小化，应使用搜索次数较少的磁盘。
6.1使用磁盘阵列RAID
RAID(Redundant Arrays of Independent Disks，RAID)就是按照一定的策略将数据分布到若干物理磁盘上，这样不仅增强了数据存储的可靠性，而且提高数据读写的性能（RAID有不同的级别）
1) 数据读写都很频繁，可靠性要求也很高的，最好RAID10
2) 数据读很频繁，但写相对较少的，对可靠性一定要求的，选择RAID5
3) 数据读写都很频繁，但是可靠性要求不高的可以选择RAID0
6.2使用符号链接分布I/O
MySQL在默认的情况下，数据库和数据表都存放在参数datadir定义的目录下，这样如果不使用RAID或者逻辑卷，所有的数据都存放在一个磁盘设备上，无法发挥多磁盘并行读写的优势。
可以将表和数据库从数据库目录移动到其它的位置并且用指向新位置的符号链接进行替换。推荐的方法只需要将数据库通过符号链接指到不同的磁盘。符号链接表仅作为是最后的办法。
符号链接一个数据库的方法是，首先在一些有空闲空间的硬盘上创建一个目录，然后从 MySQL数据目录中创建它的一个符号链接。
例如：
|
1 2 |
$ mkdir /dr1/databases/test $ ln -s /dr1/databases/test /path/to/datadir |
注意：只有 MyISAM 表完全支持符号链接。对于其它表类型，如果试图在操作系统 中的文件上用前面的任何语句使用符号链接，可能会出现奇怪的问题。对于 MyISAM 表的符号链接的处理如下：
1. 在数据目录指，一定会有表定义文件、数据文件和索引文件。数据文件和索引文件可 以移到别处和在数据目录中符号链接替代。表定义文件不能进行符号链接替换。
2. 可以分别通过符号链接将数据文件和索引文件指到不同的目录。
3. 如果 mysqld 没有运行，符号链接可以从服务器命令行使用 ln -s 手动完成。同样，通过使用 data directory和 index directory选项创建表，你可以指示运行的 MySQL 服务器执行符号链接。
4. myisamchk 不用数据文件或索引文件替换符号链接。它直接工作在符号链接指向的文件。任何临时文件创建在数据文件或索引文件所处的目录中。
5. 注释：当你删掉一个表时，如果该表使用了符号链接，符号链接和该符号链接指向的 文件都被删除掉。这就是你不应以系统 root 用户运行 mysqld 或允许系统用户对 MySQL数据库目录有写访问权限的原因。
6. 如果你用Alter table …rename重命名一个表并且不将表移到另一个数据库，数据库目录中的符号链接被重新命名为一个新名字并且数据文件和索引文件也相应地重新命名。
7. 如果你用Alter table …rename移动一个表到另一个数据库，表移动到另一个数据库目录。旧的符号链接和其所指向的文件被删除。换句话说，新表不再被链接。
8. 如果不使用符号链接，你应对 mysqld 使用 –skip-symbolic-links 选项以确保没有人能够使用 mysqld 来删除或重新命名数据目录之外的文件。
表符号链接还不支持以下操作：
1. Alter table忽略data directory和index Directory表选项。
2. Backup table和Restore table不考虑符号链接。
3. .frm文件必须绝不能是一个符号链接（如前面所述，只有数据和索引文件可以是符链接）。如果试图这样做（例如，生成符号链接）会产生不正确的结果。
6.3禁止操作系统更新文件的atime属性
7. 应用优化
1 )使用连接池
对于访问数据库来说，建立连接的代价比较昂贵，因此，我们有必要建立 " 连接池 " 以提高访问的性能。我们可以把连接当作对象或者设备，池中又有许多已经建立的连接，访 问本来需要与数据库的连接的地方，都改为和池相连，池临时分配连接供访问使用，结果返 回后，访问将连接交还。
2)减少对mysql的访问，使用mem缓存等
3)负载均衡，复制分流查询操作
|
利用mysql的主从复制，分流更新操作和查询操作 Grant replication slave on *.* to 'rel'@'10.0.1.2' identified by '123456'; 6) show processlist 查看。 |
4) 分布式cluster 数据库架构
8. 对表进行分拆
1）水平划分
如果某个表的数据太多，预期有上千条甚至上亿以上，我们可以化整为0：拆表。这里就涉及到拆表的算法：
记录日志的表，也可以按周或者按月来拆。
记录用户信息的表，按用户id的hash算法来拆。
2）垂直拆分
如果表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。
9. 附录1
9.1 优化SQL步骤
9.1.1 通过show status和应用特点了解SQL的执行频率
通过Show status可以提供服务器状态信息，也可以使用mysqladmin extended status命令获得。Show status可以根据需要显示session级别的统计结果和global级别的统计结果。
如显示当前session：Show status like "Com_%"; 全局级别：show global status;
以下几个参数对MyISAM和Innodb存储引擎都计数：
1. Com_select 执行select操作的次数，一次查询只累加 1 ；
2. Com_insert 执行insert操作的次数，对于批量插入的 insert 操作，只累加一次 ；
3. Com_update 执行update操作的次数；
4. Com_delete 执行delete操作的次数；
以下几个参数是针对Innodb存储引擎计数的，累加的算法也略有不同：
1. Innodb_rows_read 执行select查询返回的行数；
2. Innodb_rows_inserted 执行insert操作插入的行数；
3. Innodb_rows_updated 执行update操作更新的行数；
4. Innodb_rows_deleted 执行delete操作删除的行数；
通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。
对于事务型的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。此外，以下几个参数便于我们了解数据库的基本情况：
1. Connections 试图连接 MySQL 服务器的次数
2. Uptime 服务器工作时间
3. Slow_queries 慢查询的次数
9.1.2 定位执行效率较低的SQL语句
可以通过以下两种方式定位执行效率较低的SQL语句：
1. 可以通过慢查询日志定位那些执行效率较低的 sql 语句，用 --log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。可以链接到管理维护中的相关章节。
2. 使用 show processlist查看当前MySQL的线程， 命令慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查 询慢查询日志并不能定位问题，可以使用 show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看SQL执行情况，同时对一些锁表操作进行优化。
9.1.3 通过EXPLAIN 分析低效 SQL的执行计划
通过以上步骤查询到效率低的SQL后，我们可以通过explain或者desc获取MySQL 如何执行select语句的信息，包括select语句执行过程表如何连接和连接的次序。
9.2 MySQL索引
9.2.1 MySQL如何使用索引
索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高select操作性能的最佳途径。
查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列 索引，那么只有查询条件使用了多列关键字最左边的前缀时（前缀索引），才可以使用索引，否则 将不能使用索引。
下列情况下，MySQL不会使用已有的索引：
1、如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如：如果key_part1均匀分布在1和100之间，下列查询中使用索引就不是很好：
select * from table_name where key_part1 > 1 and key_part1 < 90；
2、如果使用 heap 表并且 where 条件中不用＝索引列，其他 > 、 < 、 >= 、 <= 均不使 用索引（MyISAM和Innodb表使用索引）；
3、使用or分割的条件，如果or前的条件中的列有索引，后面的列中没有索引，那么涉及到的索引都不会使用。
4、如果创建复合索引，如果条件中使用的列不是索引列的第一部分；（不是前缀索引）
5、如果 like 是以％开始。
6、对 where 后边条件为字符串的一定要加引号，字符串如果为数字 mysql 会自动转 为字符串，但是不使用索引。
9.2.2 查看索引使用情况
如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。
语法：
mysql> show status like 'Handler_read%';
9.3具体优化查询语句
9.3.1 查询进行优化，应尽量避免全表扫描
对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引，尝试下面的技巧以避免优化器错选了表扫描：
使用analyze table [table_name]为扫描的表更新关键字分布;
对扫描的表使用force index告知MySQL，相对于使用给定的索引表扫描将非常耗时。
Select * from t1, t2 force index (index_for_column) where t1.col_01=t2.col_01；
用--max-seeks-for-key=1000选项启动mysqld或使用set max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。
1). 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t1 where num is null
NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时null是默认值，但大多数时候应该使用not null，或者使用一个特殊的值，如0，-1作为默认值。
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
此例可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t1 where num=0
2). 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，between，in，以及某些时候的like。 可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如:
select id from t1 where col1 LIKE 'Mich%'; # 这个查询将使用索引，
select id from t1 where col1 LIKE '%ike'; # 这个查询不会使用索引。
3). 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t1 where num=10 or num=20
可以使用union合并查询：
Select id from t1 where num=10 union all select id from t where num=20;
在某些情况下，or条件可以避免全表扫描
1、where语句里面如果带有or条件, MyISAM表能用到索引，Innodb不行。
2、必须所有的or条件都必须是独立索引
4、in 和 not in 也要慎用，否则会导致全表扫描
如：
select id from t1 where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
Select id from t1 where num between 1 and 3
5、下面的查询也将导致全表扫描：
select id from t1 where name like '%abc%' 或者
select id from t1 where name like '%abc' 或者
若要提高效率，可以考虑全文检索。
而select id from t1 where name like 'abc%' 才用到索引
6、如果在where子句中使用参数，也会导致全表扫描，因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推 迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。 如下面语句将进行全表扫描：select id from t1 where num=@num
可以改为强制查询使用索引：select id from t1 with(index(索引名)) where num=@num
7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t1 where num/2=100应改为: Select id from t1 where num=100*2
8、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t1 where substring(name,1,3)='abc' --name
select id from t1 where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’
生成的id 应改为:
select id from t1 where name like 'abc%'
select id from t1 where createdate>='2005-11-30' and createdate<'2005-12-1'
9、不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10、索引字段不是复合索引的前缀索引，例如在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
9.3.2其他一些注意优化
11、不要写一些没有意义的查询，如需要生成一个空表结构：
Select col1, col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： Create table #t(...)
12、很多时候用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)
13、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
14、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
15、应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
16、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
17、尽可能的使用 varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
18、最好不要使用"*"返回所有：select * from t，用具体的字段列表代替“*”，不要返回用不到的任何字段。
9.3.3 临时表的问题
19、尽量使用表变量来代替临时表，如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
20、避免频繁创建和删除临时表，以减少系统表资源的消耗
21、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
22、 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免造成大log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
23、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
9.3.4 游标的问题
24、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
25、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
26、与临时表一样，游标并不是不可使用。对小型数据集使用 Fast_Forward游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
27、在所有存储过程和触发器的开始处设置 set nocount on，在结束时设置set nocount off，无需在执行存储过程和触发器的每个语句后向客户端发送done_in_proc消息。
9.3.5 事务的问题
28、尽量避免大事务操作，提高系统并发能力。
9.3.6 数据量的问题
29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
9.3.7 Count优化
30、count(*) 优于count(1)和count(primary_key)
很多人为了统计记录条数，就使用 count(1) 和 count(primary_key) 而不是 count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对count(*)计数操作做了一些特别的优化。
31、count(column)和count(*)是不一样的
这个误区甚至在很多的资深工程师或者是DBA中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column)和count(*)是一个完全不一样的操作，所代表的意义也完全不一样。
count(column)是表示结果集中有多少个column字段不为空的记录
count(*)是表示整个结果集有多少条记
[1] Innodb引擎在统计方面和MyISAM是不同的，MyISAM内置了一个计数器，Count(*)在没有查询条件的情况下使用 select count(*) from t1 的时候，MyISAM直接可以从计数器中取出数据。而Innodb必须全表扫描一次方能得到总的数量
[2] 但是当有查询条件的时候，两者的查询效率一致。
[3] 主键索引count(*)的时候之所以慢
InnoDB引擎
[1] 数据文件和索引文件存储在一个文件中，主键索引默认直接指向数据存储位置。
[2] 二级索引存储指定字段的索引，实际的指向位置是主键索引。当我们通过二级索引统计数据的时候，无需扫描数据文件；而通过主键索引统计数据时，由于主键索引与数据文件存放在一起，所以每次都会扫描数据文件，所以主键索引统计没有二级索引效率高。
[3] 由于主键索引直接指向实际数据，所以当我们通过主键id查询数据时要比通过二级索引查询数据要快。
MyiASM引擎
[1] 该引擎把每个表都分为几部分存储，比如用户表，包含user.frm，user.myd和user.myi。
[2] User.frm负责存储表结构
[3] User.myd负责存储实际的数据记录，所有的用户记录都存储在这个文件中
[4] User.myi负责存储用户表的所有索引，这里也包括主键索引。
9.3.8 优化Order by
基于索引的排序
MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录，但由于MySQL在查询时最多只能使用一个索引。因此，如果where条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度。我们可以看看如下的SQL语句:
select * from sales where name = “name” order by sale_date desc;
在以上的SQL的where子句中已经使用了name字段上的索引，因此，在对sale_date进行排序时将不再使用索引。为了解决这个问题，我们可以对sales表建立复合索引:
alter table sales drop index name, add index(name,sale_date);
这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意，在使用这个方法时，要确保where子句中没有排序字段，在上例中就是不能用sale_date进行查询，否则虽然排序快了，但是sale_date字段上没有单独的索引，因此查询又会慢下来。
在某些情况中，MySQL可以使用一个索引来满足order by子句，而不需要额外的排序。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。例如：下列sql可以使用索引。
select * from t1 order by key_part1, key_part2, ... ;
select * from t1 where key_part1=1 order by key_part1 desc, key_part2 desc;
select * from t1 order by key_part1 desc, key_part2 desc;
但是以下情况不使用索引：
select * from t1 order by key_part1 desc, key_part2 asc；
--order by 的字段混合asc和desc
select * from t1 where key2=constant order by key1；
-- 用于查询行的关键字与order by中所使用的不相同
select * from t1 order by key1, key2；
-- 对不同的关键字使用order by
9.3.9 优化Group by
默认情况下，MySQL排序所有group by col1，col2，.... 。查询的方法如同在查询中指定 order by col1，col2， ... 。如果显式包括一个包含相同的列的order by子句， MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。如果查询包括group by但你想要避免排序结果的消耗，你可以指定order by null禁止排序。
例如 ：
Insert into t1 select a, count(*) from bar group by a order by null;
9.3.10 优化 OR
具体详解看：mysql or条件可以使用索引而避免全表
9.4 Explain解释说明
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了，如：
- explain select surname,first_name form a,b where a.id=b.id;
分析结果形式如下：
table | type | possible_keys | key | key_len | Ref | Rows | Extra
EXPLAIN列的解释：
1 table
显示这一行的数据是关于哪张表的
2 type
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：system、const、eg_reg、ref、ref_or_null、range、indexhe、All。
system:表仅有一行(=系统表)。这是const联接类型的一个特例
const:(Primary key或Unique)
表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！ const用于用常数值比较primary key或unique索引的所有部分时。
在下面的查询中，tbl_name可以用于const表：
- SELECT * from tbl_name WHERE primary_key=1；
eq_reg:key
对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是Unique或Primary key。 eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：
- SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
- SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1= other_table.column AND ref_table.key_column_part2=1;
ref:key
对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是Unique或Primary key(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
在下面的例子中，MySQL可以使用ref联接来处理ref_tables：
SELECT * FROM ref_table WHERE key_column=expr;
- SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
- SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1= other_table.column AND ref_table.key_column_part2=1;
Ref_or_null:Or Is null
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：
- SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
range:=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN
只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、between或者in操作符，用常量比较关键字列时，可以使用range：
- SELECT * FROM tbl_name WHERE key_column = 10;
- SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;
- SELECT * FROM tbl_name WHERE key_column IN (10,20,30);
- SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);
indexhe:
该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。
ALL：
对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，
这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用All，使得行能基于前面的表中的常数值或列值被检索出。
3 possible_keys
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从Where语句中选择一个合适的语句
4 key
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在select语句中使用force index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制MySQL忽略索引
强制索引 force index
Select * from T1 force index (Field1) …
以上的SQL语句只使用建立在Field1上的索引，而不使用其它字段上的索引。
忽略索引 ignore index
Select * from T1 ignore index (Field1, Field2) …
在上面的SQL语句中，T1表中Field1和Field2上的索引不被使用。
5 key_len
使用的索引的长度。在不损失精确性的情况下，长度越短越好
6 ref
显示索引的哪一列被使用了，如果可能的话，是一个常数
7 Rows
MySQL认为必须检查的用来返回请求数据的行数 (扫描行的数量)
8 Extra
该列包含MySQL解决查询的详细信息，关于MySQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，
意思MySQL根本不能使用索引，结果是检索会很慢 ，extra列返回的描述的意义
Distinct:
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists :
MySQL优化了left join，一旦它找到了匹配left join标准的行，就不再搜索了，下面是一个可以这样优化的查询类型的例子：
select * from t1 left join t2 on t1.id=t2.id where t2.id is null；
假定t2.id定义为not null。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为null，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。
Range checked for each Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MySQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一，MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
关于适用性标准的描述参见7.2.5节，“范围优化”和7.2.6节，“索引合并优化”，不同的是前面表的所有列值已知并且认为是常量。这并不很快，但比执行没有索引的联接要快得多。
Using filesort
看到这个的时候，查询就需要优化了。MySQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary
看到这个的时候，查询需要优化了。这里，MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上
Using where
使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型all或index，这就会发生，或者是查询有问题
Impossible where noticed after reading const table...
9.5 SQL核心语句(非常实用的几个技巧)
1) 插入数据
批量插入:
- INSERT mytable (first_column,second_column,third_column)
- VALUES ('some data','some more data','yet more data') ,
- VALUES ('some data','some more data','yet more data') ,
- VALUES ('some data','some more data','yet more data')
2）清空数据表
- TRUNCATE TABLE `mytable`
注意：删除表中的所有记录，应使用TRUNCATE TABLE语句。注意这里为什么要用TRUNCATE TABLE语句代替DELETE语句:当你使用TRUNCATE TABLE语句时，记录的删除是不作记录的。也就是说，这意味着TRUNCATE TABLE要比DELETE快得多。
3）用Select创建记录和表
Insert语句与Delete语句和Update语句有一点不同，它一次只操作一个记录。然而，有一个方法可以使Insert语句一次添加多个记录。要作到这一点，你需要把Insert语句与Select语句结合起来，像这样:
- INSERT T1 (col1, col2) SELECT col1,col2 FROM T2 WHERE col1 ='Copy Me!';
这个语句从t2拷贝记录到t1.只有表t2中字段col1的值为'Copy Me!'的记录才被拷贝。当为一个表中的记录建立备份时，这种形式的Insert语句是非常有用的。在删除一个表中的记录之前，你可以先用这种方法把它们拷贝到另一个表中。
如果你需要拷贝整个表，你可以使用Select into语句。例如，下面的语句创建了一个名为t1_new的新表，该表包含表t1_old的所有数据:
- SELECT * INTO t1_new FROM t1_old;
你也可以指定只有特定的字段被用来创建这个新表。要做到这一点，只需在字段列表中指定你想要拷贝的字段。另外，你可以使用WHERE子句来限制拷贝到新表中的记录。下面的例子只拷贝字段col1的值等于'Copy Me!'的记录的col1字段。
- SELECT col1 INTO newtable FROM mytable WHERE col1 ='Copy Me!';
使用SQL修改已经建立的表是很困难的。例如，如果你向一个表中添加了一个字段，没有容易的办法来去除它。另外，如果你不小心把一个字段的数据类型给错了，你将没有办法改变它。但是，使用本节中讲述的SQL语句，你可以绕过这两个问题。
例如，假设你想从一个表中删除一个字段。使用select into语句，你可以创建该表的一个拷贝，但不包含要删除的字段。这使你既删除了该字段，又保留了不想删除的数据。
如果你想改变一个字段的数据类型，你可以创建一个包含正确数据类型字段的新表。创建好该表后，你就可以结合使用Update语句和Select语句，把原来表中的所有数据拷贝到新表中。通过这种方法，你既可以修改表的结构，又能保存原有的数据。
10. 附录2
在某些情况下，or条件可以避免全表扫描的。
10.1 where语句带"or"
MyISAM引擎表可以使用索引，InnoDB引擎表不行
1)MyiSAM表
|
CREATE TABLE IF NOT EXISTS `a` ( | | | | | | | | | | Using where | |
2)innodb表：
|
CREATE TABLE IF NOT EXISTS `a` ( |
10.2 必须所有的or条件都必须是独立索引
|
+-------+---------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) |
全表扫描了。
10.3 用UNION替换OR (适用于索引列)
通常情况下, 用Union替换Where子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.
在下面的例子中, LOC_ID 和Region上都建有索引.
高效:
- select loc_id,loc_desc,region from location where loc_id=10;
- union
- select loc_id,loc_desc,region from location where region="melbourne";
低效:
- select loc_id,loc_desc,region from location where loc_id=10 or region="melbourne";
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面。
10.4 用in来替换or
这是一条简单易记的规则，但是实际的执行效果还须检验，在oracle8i下，两者的执行路径似乎是相同的．
低效:
select…. from location where loc_id = 10 or loc_id = 20 or loc_id = 30
高效
select… from location where loc_in in (10,20,30);
