mysql数据库实例在系统上的表现就是一个进程。
mysql组成部分：
1）Connectors：指的是不同语言中与SQL的交互
2）Management Serveices & Utilities： 系统管理和控制工具，例如备份恢复、Mysql复制、集群等
3）Connection Pool（连接池） ：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求
4）SQL Interface:（SQL接口）：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
5）Parser（解析器）：SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本， 主要功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
6）Optimizer（查询优化器）：SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果
7）Cache和Buffer（高速缓存区）： 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
8）Engine ：存储引擎：存储引擎其实就是如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。
注：插件式存储引擎：mysql不同于其它数据库，用的是插件式存储引擎。插件式表存储引擎是底层物理结构的实现，负责为数据库执行实际额数据I/O操作，它是基于表而不是数据库的，可以根据实际应用需求为每个表设定不同的选择。
插件式存储引擎的核心是文件访问层的一个抽象接口，任何人都可以利用这个API接口去建立新的文件访问机制。
其中在新版本中默认使用的是InnoDB存储引擎
Mysql数据库InnoDB存储引擎特性：
InnoDB索引的方式：
primary index：data域保存了完整的数据记录，
secondary index：data域存储相应记录主键的值而不是地址。但是辅助索引搜索需要检索两遍索引：即1）检索辅助索引获得主键，2）通过主键到主索引中检索
InnoDB的缓存机制与MyISAM不相同，该缓存池不仅用来缓存InnoDB的索引块，而且也用来缓存InnoDB的数据块。
InnoDB使用日志来减少提交事务的开销，不是每次在事务提交时就把缓冲池刷写的磁盘上，而是记录了事务日志。
（1）将数据写入到InnoDB buffer pool，并对相关记录加独占锁；
（2）将UNDO信息写入到undo表空间的回滚当中；
（3）更改缓存页中的数据，并将更新记录写入redo buffer中；
（4）提交时，根据innoDB_redo_log_file_at trx_commit的设置，用不同的方式将redo buffer中的更新刷新到innoDB redo log file中，然后释放独占锁；
（5）最后，后台IO携程根据需要将缓存中更新过的数据刷新到磁盘文件中。
InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，有可能发生死锁，对于有外键的列，会自动加上索引。
- select * from actor where last_name like '%NI%';
- select * from actor where last_name=1;//不使用索引
- select * from actor where last_name='1';//使用索引
（3）复合索引下，假如查询条件不包含索引列最左边部分，即不满足最左原则；
事务：
事务的概念
先看一个经典银行转账案例，A向B的银行卡转账1000元，这里分两个主要事件，一个是A向B转账1000，那么A的银行卡转账成功后必须在原来的数额上扣掉1000元，另一个是B收到了A的转款，B的银行卡上数额必须增加1000元，这两个步骤是必须都成功才算转账成功，总不能A转账B后，A的数额没有变化而B增加了1000元吧？这样银行不得亏死了？因此两个步骤只要有一个失败，此次转账的结果就是失败。但我们在执行sql语句时，两个动作是分两个语句执行的，万一执行完一个突然没电了另外一个没有执行，那岂不出问题了？此时就需要事务来解决这个问题了，所谓的事物就是保证以上的两个步骤在同一个环境中执行，只要其中一个失败，事务就会撤销之前的操作，回滚的没转账前的状态，如果两个都执行成功，那么事务就认为转成成功了。这就是事务的作用。
对事务有了初步理解后，进一步了解事务的官方概念，事务是DBMS的执行单位。它由有限个数据库操作语句组成。但不是任意的数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件（ACID）
原子性（Autmic）：一个原子事务要么完整执行，要么干脆不执行。也就是说，工作单元中的每项任务都必须正确执行，如果有任一任务执行失败，则整个事务就会被终止并且此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，那么对数据所作的修改将会是永久性的
一致性（Consistency）：一致性代表了底层数据存储的完整性。 它是由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(数据预期所表达的现实业务情况不相一致)。例如，在刚才的AB转账过程中，从A账户中扣除的金额必须与B账户中存入的金额相等。
隔离性（Isolation）：隔离性是指事务必须在不干扰其他事务的前提下独立执行，也就是说，在事务执行完毕之前，其所访问的数据不能受系统其他部分的影响。
持久性（Durability）：持久性指明当系统或介质发生故障时，确保已提交事务的更新数据不能丢失，也就意味着一旦事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，耐得住任何系统故障，持久性可以通过数据库备份和恢复来保证。
四种分离水平(隔离级别)
READ_UNCOMMITTED：这是事务最低的分离水平(隔离级别)，它充许别外一个事务可以看到这个事务未提交的数据，会出现脏读、不可重复读、幻读 （分离水平最低，并发性能高）；
READ_COMMITTED：保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。可以避免脏读，但会出现不可重复读、幻读问题（锁定正在读取的行）；
REPEATABLE_READ：在InnoDB中，这是默认隔离水平，可以防止脏读、不可重复读，但会出幻读（锁定所读取的所有行）
InnoDB是行锁（默认隔离级别为可重复读，在这个隔离级别上使用了间隙锁尽量防止“幻读”）
SERIALIZABLE：这是花费最高代价但是最可靠的事务分离水平(隔离级别)，事务被处理为顺序执行。保证所有的情况不会发生（锁表,并发性及其低），可以避免幻读。
脏读、不可重复读，幻读
读未提交，也称脏读，脏读发生在一个事务读取了另一个事务改写但尚未提交的数据时。如果改写在稍后被回滚了，那么第一个事务获取的数据就是无效的。
不可重复读：不可重复读发生在一个事务执行相同的查询两次或两次以上，但是每次都得到不同的数据时。这通常是因为另一个并发事务在两次查询期间进行了更新。请注意，不可重复读重点是修改数据导致的(修改数据时排他读);，
幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录。请注意，幻读重点是插入或者删除数据导致的（对满足条件的数据行集进行锁定），同样的道理，在事务1中，客户管理查询所有用户生日在1990-06-05的人只有20个，操作并没有完成，此时事务2中，刚好有一个新注册的用户，其生日也1990-06-05，在事务2中插入新用户并提交了事务，此时在事务1中再次查询时，所有用户生日在1990-06-05的人变为21个了，从也就导致了幻读。
事务、分离水平、锁之间的关系
锁和事务以及分离水平关系如何呢？实际上，事务是解决多条sql执行执行过程的原子性、一致性、隔离性、持久性的整体解决方案，而事务分离水平则是并发控制的整体解决方案，其实际是综合利用各种类型的锁来解决并发问题。锁是数据库并发控制的内部基础机制。对应用开发人员来说，只有当事务分离水平无法解决并发问题和需求时，才有必要在语句中手动设置锁。关于锁的锁定，对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。请注意InnoDB行锁是通过给索引上的索引项加锁来实现的，也就是说，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
- select * from tablename where ... lock in share mode;
- select * from tablename where ... for update
即要查找的版本必须开始先于当前事务，过期慢于当前事务。
事务原理概要
最后我们来简单了解一下事务内部实现的原理概要，事实上事务的处理机制是通过记录更新日志而实现的，其中与事务处理相关的日志是UNDO日志和REDO日志。
UNDO日志亦称为回滚端，在进行数据插入、更新、删除的情景下，保存变更前的数据，原理图如下：
在表中保存了指向UNDO日志的指针，rollback执行时根据这个指针来获取旧数据并覆盖到表中，rollback执行完成后或者commit后UNDO日志将被删除。UNDO还有另外一种作用，当A用户正在更新数据时，还没提交，而B用户也需要使用该数据，这时不可能让B读取未提交的数据，因此会将存在UNDO表中的数据提供给B用户。这就是事务回滚的简单模型。
REDO日志主要是事务提交后由于错误或者断电停机等原因使数据无法更新到数据库中时，REDO日志将提供数据恢复作用。其原理是通过数据库中的一段缓冲的数据先实时更新到REDO日志再更新到数据库，也就是说平常的更新操作并非一步执行到位的，而是首选更新到REDO日志中，再更新到数据库文件的。所以REDO日志才能用户故障数据的恢复。
-
-
-
索引
优点：
可以提高检索数据的速度；
对有依赖关系的子表和父表之间的联合查询时，可以提高查询速度
使用分组和排序子句进行数据查询时，同样可以显著节省分组和排序的时间
缺点：
创建和维护索引需要耗费时间，耗费的数量随着数据量的增加而增加；
索引需要占据物理空间；
一般来说，应该在这些列上创建索引：
（1）在经常需要搜索的列上，可以加快搜索的速度；
（2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
（3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
（4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
（5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
（6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
（7）索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
（8）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可同样，对于有些列不应该创建索引：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少即CardInality值远远小于1，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大时增加索引，并不能明显加快检索速度。注：在InnoDB中的B+树索引中，CardInality是通过对随机获取8个叶子节点，统计每个页的记录个数再预估求得的。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
在InnoDB存储引擎中，若没有显示定义主键，则会指定非空的唯一索引为主键，若不存在，则自动创建一个6字节大小的指针。
InnoDB中支持一下几种常见的索引：
B+树索引；全文索引；哈希索引
其中哈希索引是自适应的，存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预。
B+树索引并不能找到一个给的键值的具体行，只能查询到数据所在的页，然后通过把页读入内存再从内存中查找到想要的数据。且B+树索引可以通过索引字段的前缀进行查找，例如 where name like 'xxxx%' ，该索引是有有效的，若为‘%xxx%’，则是无效的。
全文索引是将存储于整篇文章或整本书的任意内容信息查找出来的技术。它根据章、节、段、句、词等进行分析。在InnoDB中，使用倒排索引来实现，它在辅助表中存储了单词与单词自身在一个或多个问的中所在位置之间的映射，辅助表通常利用关联数组实现。在全文检索的表中有两个字段，一个是word字段，一个是ilst字段（组成为DoumenId,Position），并在word上设索引。然后为了提高全文检索的性能，设有6个辅助表和一个根据（word,ilist）排序的红黑树结构的FTS Index Cache（全文索引存储缓存）。
MYSQL数据库5.6版本新增的两种优化
1）MRR优化：为了减少磁盘IO操作，并将随机访问转化为较为顺序的数据访问。原理：在查询辅助索引时，根据得到的查询结果，按照主键排序，并按照主键排序的顺序进行书签查找，这样可以批量处理对键值的查询操作，也因为不是离散读操作减少了缓冲池中页被替换的次数。
2）ICP优化：在进行索引查询时，首先根据索引查询记录，在取出索引的同时判断是否可以进行WHERE条件过滤，也就是将WHERE条件过滤操作放在了存储引擎层。当然WHERE可以过滤的条件是该索引能覆盖到的范围。
Mysql引起索引失效的原因总结
1、对单字段建了索引，where条件多字段。
2、建立联合索引，where条件单字段。与上面情况正好相反。
3、对索引列运算，运算包括（+、-、*、/、！、!=、<>、%like'%_'（%放在前面）、or、in、exist等），导致索引失效。
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。如果操作符的选择率过高即取出的数据量大，则会放弃使用索引，而使用全表扫描，这种情况使用索引反而减慢了速度。
4、类型错误（隐式类型转换），如字段类型为varchar，where条件用number。
5、对索引应用内部函数，这种情况下应该建立基于函数的索引。
6、查询表的效率要比应用索引查询快的时候。
7、is null 索引失效；is not null则Betree索引生效。导致的原因，个人认为应该是，mysql没有在null写进索引。还要看应用的数据库而定。
8、在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用
9、如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。
10、B-tree索引 is null不会走,is not null会走11、在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。
12. 索引字段有多个，只要有一个字段为null，则对应唯一索引失效。
13select id from t where num in(1,2,3);
应尽量避免在 where 子句中使用 or 来连接条件、否则将导致引擎放弃使用索引而进行全表扫描、如：
select id from t where num=10 or num=20
--可以这样查询：
select id from t where num=10
union all
select id from t where num=20;
14、in 和 not in 也要慎用、否则会导致全表扫描、如：
select id from t where num in(1,2,3);
对于连续的数值、能用 between 就不要用 in 了：
select id from t where num between 1 and 3;
15、如果在 where子句中使用参数、也会导致全表扫描。因为SQL只有在运行时才会解析局部变量、但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而、如果在编译时建立访问计划、变量的值还是未知的、因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num= @num ;
--可以改为强制查询使用索引：
select id from t with(index(索引名)) where num= @num ;
16、很多时候用 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);
EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
MYSQL优化建议：
一、创建索引
创建原则
1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) =’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
二、配置缓存
在这里主要分析query cache，它是主要用来缓存查询数据。当你想使用该cache，必须把query_cache_size大小设置为非0。当设置大小为非0的时候，server会就会缓存每次查询返回的结果，到下次相同查询server就直接从缓存获取数据，而不是再执行查询。能缓存的数据量就和你的size大小设置有关，所以当你设置的足够大，数据可以完全缓存到内存，速度就会非常之快。但是，query cache也有它的弊端。当你对数据表做任何的更新操作(update/insert/delete)等操作，server为了保证缓存与数据库的一致性，会强制刷新缓存数据，导致缓存数据全部失效。所以，当一个表格的更新数据表操作非常多的话，query cache是不会起到查询提升的性能，还会影响其他操作的性能。
三、slow_query_log分析（慢查询分析）。
其实对于查询性能提升，最重要也是最根本的手段也是slow_query的设置。当你设置slow_query_log为on的时候，server端会对每次的查询进行记录，当超过你设置的慢查询时间(long_query_time)的时候就把该条查询记录到日志，以rows的速度为核心指标。而你对性能进行优化的时候，就可以分析慢查询日志，对慢查询的查询语句进行有目的的优化。可以通过创建各种索引，可以通过分表等操作。
步骤：
0. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析
四、分库分表
分库分表应该算是查询优化的杀手锏了。上述各种措施在数据量达到一定等级之后，能起到优化的作用已经不明显了。这个时候就必须对数据量进行分流。分流一般有分库与分表两种措施。而分表又有垂直切分与水平切分两种方式。下面我们就针对每一种方式简单介绍。
对于mysql，其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大的时候，操作系统对大文件的操作就会比较麻烦与耗时，而且有的操作系统就不支持大文件，所以这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb，它的底层数据结构是B+树。当其数据文件过大的时候，B+树就会从层次和节点上比较多，当查询一个节点的时候可能会查询很多层次，而这必定会导致多次IO操作进行装载进内存，肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁，那么当更改表结构的时候，这时候就会树进行加锁，当表文件大的时候，这可以认为是不可实现的。
所以综上我们就必须进行分表与分库的操作。
五、子查询优化
在查询中经常会用到子查询，在子查询的时候一般使用in或者exist关键词。针对in和exist在查询的时候当数据量大到一定程度以后，查询执行时间就差别比较大。但是，为了避免此类情况出现，最好的方式是使用join查询。因为在绝大多数情况下，服务器对join的查询优化要远远高于子查询优化。在比较高的版本5.6，mysql查询会自动把in查询优化成joint查询，就不会出现子查询比较慢的问题。有时候也可以采用distinct关键词来限制子查询的数量，但是需要注意的是distinct很多时候会转化为group by，这个时候就会出现一个 临时表，就会出现copy数据到临时表的时延。
六、杂项
1. 为查询缓存优化你的查询
大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多 次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。
这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例：
1 2 3 4 5 6 | // 查询缓存不开启 $r = mysql_query( "SELECT username FROM user WHERE signup_date >= CURDATE()" );
// 开启查询缓存 $today = date ( "Y-m-d" ); $r = mysql_query( "SELECT username FROM user WHERE signup_date >= '$today'" ); |
上面两条SQL语句的差别就是CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像NOW() 和RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而 开启缓存。
2. EXPLAIN 你的 SELECT 查询
使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。
挑一个你的SELECT语句（推荐挑选那个最复杂的，有多表联接的），把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个 事。然后，你会看到一张表格。下面的这个示例中，我们忘记加上了group_id索引，并且有表联接：
当我们为 group_id字段加上索引后：
我们可以看到，前一个结果显示搜索了 7883行，而后一个只是搜索了两个表的 9和 16行。查看rows列可以让我们找到潜在的性能问题。
3. 当只要一行数据时使用LIMIT 1
当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
在这种情况下，加上 LIMIT 1可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
4. 为搜索字段建索引
索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。
5. 在Join表的时候使用相同类型的列，并将其索引
如果你的应用程序有很多 JOIN查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把DECIMAL 字段和一个INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
6. 千万不要ORDER BY RAND()
想打乱返回的数据行？随机挑一个数据？真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。
如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得 不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）
7. 避免SELECT *
从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。
8. 永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
9. 使用ENUM 而不是VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。
如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用ENUM 而不是 VARCHAR。
10. 从PROCEDURE ANALYSE() 取得建议
PROCEDURE ANALYSE() 会让MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。
15. 固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length” 。 例如，表中没有如下类型的字段：VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL引擎会用另一种方法来处理。
固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果 字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，
因为定长的字段无论你用不用，他都是要分配那么多的空间。
16. 垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过 一张表有100多个字段，很恐怖）
示例一 ：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有 好的性能。
示例二 ： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。
另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。
17. 选择正确的存储引擎
在 MySQL中有两个存储引擎 MyISAM和 InnoDB，每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB还是 MyISAM? 》讨论和这个事情。
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都 无法操作直到读操作完成。另外，MyISAM对于 SELECT COUNT(*)这类的计算是超快无比的。
InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。
Mysql-InnoDB引擎为什么用B+树做索引？
先从数据结构的角度来答。
B-树和B+树最重要的一个区别就是B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。
这就决定了B+树更适合用来存储外部数据，也就是所谓的磁盘数据。
从Mysql（Inoodb）的角度来看，B+树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上。
那么Mysql如何衡量查询效率呢？磁盘IO次数，B-树（B类树）的特定就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，当查询数据的时候，最好的情况就是很快找到目标索引，然后读取数据，使用B+树就能很好的完成这个目的，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时啊！），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。
另一个优点是什么，B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。
###详解b+树
如上图，是一颗b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
###b+树的查找过程
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
###b+树性质
1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
999
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
