数据库（2）MySQL基础

2016-07-03

一、MySQL架构

MySql安装访问

1、安装后忘记root密码，可使用脚本重置

1 2	wget http://soft.vpser.net/lnmp/ext/reset_mysql_root_password.sh sh reset_mysql_root_password.sh

2、如无法远程访问，添加权限，检查防火墙

1
2
3

mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3、修改密码

1	update user set password=password("123456") where user="root";

MySql逻辑架构

MySQL逻辑架构图

1、最上层处理连接、授权认证、安全等。

连接管理，每个连接都会在服务器进程中拥有一个线程，连接的查询只会在这个线程中执行，该线程只能轮流在某个CPU中运行。
安全性，会进行用户名和密码认证，还会验证是否有操作权限，可进行主机信息校验（如ip等）。

2、第二层，MySQL核心服务功能都在这，包括查询解析、分析、优化、缓存以及所有内置喊出，所有跨存储引擎的功能都在这实现：存储过程、触发器、视图等。
MySQL会根据解析查询，创建内部数据结构（解析树），对其进行优化，如重写查询、表的读取数据、选择合适的索引等。用户可请求优化器解释（explain）知晓服务器的优化决策。
针对SELECT语句，在解析查询前会检查查询缓存并直接返回查询缓存中的结果集。

3、第三层包含了存储引擎，负责MySQL中数据的存储和提取。上层通过API于存储引擎进行通信。

二、并发控制（锁机制）

锁的类型

在处理并发请求时，可以通过实现锁系统来解决。锁系统包含共享锁（读锁）和排他锁（写锁），读锁指查询，写锁指增删改。

锁的粒度

在给定的资源上，锁定的数据量越少，则系统的并发程序越高，只要相互之间不发生冲突，另外加锁会消耗资源，因此，针对锁粒度，就是在锁的开销和数据的安全性之间寻求平衡。每种存储引擎都可以实现自己的锁策略和锁粒度。常用的锁粒度有：表锁、行级锁和页锁。

表锁是锁定整张表，基本的锁策略，开销也最小。表级锁是不会产生死锁问题的。InnoDB、MyISAM、BDB存储引擎都支持表锁。
行级锁可最大程度地支持并发，开销最大，且只在InnoDB和XtraDB等一些存储引擎中得到实现。行级锁定也最容易发生死锁。InnoDB存储引擎支持行级锁。
页级锁定是MySQL中比较独特的一种锁定级别，锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销以及所能提供的并发处理能力也同样是介于二者之间。也会产生死锁问题。BDB存储引擎支持页级锁定。

一次性封锁与两段锁

一次性封锁：在sql语句的开始执行的时候，已经预先知道要涉及到那些数据，然后全部锁住，在执行完毕之后，再全部解锁！（myisam就是采用这样的锁协议）
两段锁：是指每个事务的执行可以分为两个阶段：生长阶段（加锁阶段）和衰退阶段（解锁阶段）。
加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行；
解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。
两段封锁法：事务开始后就处于加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块中DBMS释放所有封锁。（innodb就是采取这样的锁协议）
死锁：为两个或多个事务在同一资源上的相互占用，请求锁定对方占用的资源导致的恶性循环现象。InnoDB目前处理的方法是，将持有最少行级排他锁的事务进行回滚。

隐式锁和显式锁

隐式锁：当多个客户端并发访问同一个数据的时候，为了保证数据的一致性，数据库管理系统会自动的为该数据加锁、解锁，这种被称为隐式锁。隐式锁无需开发人员维护（包括锁粒度、加锁时机、解锁时机等）
显式锁：当时在某些特殊的情况下需要开发人员手动的进行加锁、解锁，这种锁方式被称为显式锁。对于显式锁而言，开发人员不仅要确定锁的粒度，还需要确定加锁的时机（何时加锁）、解锁的时机（何时解锁）以及所的类型。例如SELECT … FOR UPDATE

三、事务

事务是一组原子性的SQL查询，要么全部执行成功，要么全部执行失败。需要通过ACID测试。

原子性（atomicity）：不可分割的最小工作单元。
一致性（consistency）：数据库总是从一个一致性的状态到另外一个一致性的状态。
隔离性（isolation）：一个事务所做的修改在最终提交以前，对其他事务是不见的。
持久性（durability）：事务提交，所做的修改会永久保存到数据库中。

SQL标准定义了四种隔离级别

未提交读，代表事务的修改，即使没有提交，对其他事务也都是可见的。所以事务可以读取未提交的数据，即为脏读。
提交读，为事务开始时，只能看见自己已经提交的事务所做的修改，对其他事务是不可见的。但会出现不可重复读，即两次同样查询，可能会得到不一样的结果。（大多数数据库的默认隔离级别），但能保证每次都读到最新的数据。
可重复读，还是无法解决幻读。幻读，是指某个事务在读取记录时，另一个事务又在该范围内插入新的记录，当之前的事务再次读取时会产生幻行。（MySQL的默认事务隔离级别）。为解决幻行，InnoDB和XtraDB采用了多版本并发控制（MVCC）来解决。每次都读取指定的版本，这样保证不会产生幻读，但可能读不到最新的数据。
可串行化，通过强制事务串化执行，即每次读取时会加锁。只有在非常需要确保数据一致性且可接受没有并发的情况下使用。

隔离级别	名称	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITTED	未提交读	Yes	Yes	Yes	No
READ COMMITTED	提交读	No	Yes	Yes	No
REPEATABLE READ	可重复读	No	No	Yes	No
SERIALIZABLE	可串行化	No	No	No	Yes

MySQL提供事务型的存储引擎

事务型的存储引擎有InnoDB和NDB Cluster，加第三方XtraDB和PBXT。事务是由存储引擎实现的，因此同一个事务中，使用多种存储引擎是不可靠的。事务中混合使用事务型表(InnoDB)和非事务型表（MyISAM），正常提交可以，如果回滚会出现状态不一致。

默认采用AUTOCOMMIT，即如果不是显式的开始一个事务，则每个查询都被当做一个事务执行提交操作。mysql的autocommit（自动提交）默认是开启，其对mysql的性能有一定影响，举个例子来说，如果你插入了1000条数据，mysql会自动commit 1000次的。如果我们把autocommit关闭掉，通过程序来控制，需手动进行一次commit就可以了。

但是即便开启AUTOCOMMIT，仍可以显式的开始一个事务：

start transaction（开启事务）
sql语句（这里要注意一些sql语句会造成事务的隐式提交）
commit/rollback（提交或回滚）

多版本并发控制MVCC

MVCC只在 REPEATABLE READ和 READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和 MVCC不兼容，因为 READ UNCOMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE则会对所有读取的行都加锁。

InnoDB的 MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号(system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。下面看一下在 REPEATABLE READ隔离级别下，具体是如何操作的。

1、SELECT
InnoDB会根据以下两个条件检查每行记录：

InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插人或者修改过的。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果。

2、INSERT：为新插人的每一行保存当前系统版本号作为行版本号。
3、DELETE：为删除的每一行保存当前系统版本号作为行删除标识。
4、UPDATE：为插人一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

四、存储引擎

InnoDB（MySQL的默认事务型引擎）

2008年oracle发布InnoDB plugin，适用于5.1版本。后来oracle收购Sun后发布的MySQL 5.5中彻底使用InnoDB plugin替换了旧版本的InnoDB。
InnoDB采用MVCC支持高并发、自动崩溃恢复、和聚簇索引等特性，实现了4个标准的隔离级别。

MyISAM（MySQL5.1及之前的版本的默认引擎）

不支持事务和行级锁。
崩溃后无法安全恢复。

MySQL内建的其他存储引擎

Archive引擎，只支持INSERT和SELECT操作，适合日志和数据采集类应用，实现高并发的插入。
CSV引擎，将CSV文件作为MySQL的表来处理。
Memory引擎，数据保存在内存中，重启后会丢失。表级锁，并发写入性能较低。支持Hash索引，用于快速查找访问。MySQL自己使用的临时表就可以看做是Memory表。
NDB集群引擎，MySQL集群使用。

第三方存储引擎

OLTP类引擎，基于InnoDB的改进，MariaDB包含了该引擎。
面向列的数据引擎，如Infobright，用于数据分析和数据仓库。
社区存储引擎，有很多，如SphinxSE，为Sphinx全文索引搜索服务器提供SQL接口。

选择合适的引擎.

考虑因素：事务、在线热备份、崩溃恢复、特有的特性。

大多数情况下InnoDB是正确的选择。
对于日志型应用，可使用MyISAM或Archive。
对于只读或读多写少的应用，MyISAM并不比InnoDB快，建议使用InnoDB。尤其是MyISAM压力上升时，无法与InnoDB相提并论。
订单处理，必须支持事务，因此是InnoDB。
大数据量，如10TB以上，可使用Infobright等引擎。

转换表的引擎

1	mysql> ALTER TABLE mytable ENGINE= InnoDB

在繁忙的表上执行可能需要消耗很长时间。可创建新的存储引擎的表，然后INSERT……SELECT语法进行导入。

版本选择

2008年发布了5.1，2010年发布了5.5，到现在5.7，理想情况是越新越好，当然也可以等到第一个bug修复版本以后再采用新的大版本。

五、数据类型

原则

更小的通常更好。
简单就好。内建类型datetime来存储日期，而不是字符串。整型存储IP等。
尽量避免NULL。NULL的列会使用更多的存储空间。

整数类型

数字有两种：整数和实数。如果存储整数，可使用：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,分别对应8、16、24、32、64位存储空间，存储的值得范围从-2^(N-1)到2^(N-1) -1。可选UNSIGNED属性，表示不允许负值。MySQL可以为整数类型指定宽度，例如INT(11)，但对于存储和计算来讲，INT(1)和INT(11)是相同的。

实数类型

如果不只是为了存储实数的小数部分，可以使用DECIMAL存储币BIGINT还大的整数。

MySQL既支持精确类型，也支持不精确类型。DECIMAL用于存储精确的小数。CPU支持原生浮点计算，所以浮点运算更快，使用更少的空间。但CPU不支持DECIMAL的直接计算，需要MySQL服务器自身实现。FLOAT使用4个字节存储，DOUBLE占用8个，而DECIMAL(18,9)，由于每4个字节存储9个数字，因此需要4+4+1（小数点本身）=9个。因此针对DECIMAL，只有当需要对小数进行精确计算时才使用。但一般，可以将金额乘以一百万，可以达到存储数据精确到万分之一分，便可满足需要。

字符串类型

VARCHAR，用于存储可边长字符串，比定长节省空间，但需要使用1个（列长度在255个字节以内）或2个额外字节记录字符串的长度，例如VARCHAR(10)需要11个字节，VARCHAR(1000)需要1002个字节。VARCHAR使用情形：字符串列的最大长度，比平均长度大很多，且列的更新很少。
CHAR，用于存储定长，会根据定义的字符串长度分配足够的空间。CHAR适合存储很短的字符串。需要注意，当字符串末尾有空格时，插入会被截断。
使用VARCHAR(5)和VARCHAR(200)存储’hello’的空间开销是一样的，但是更长的列会消耗更多的内存，所以尽量只分配真正需要的空间。
BLOB和TEXT类型用于存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。BLOB没有排序规则或字符集，而TEXT类型有。
枚举类型ENUM，有时可代替字符串类型。但需要小心使用。

日期和时间类型

DATETIME可保存大范围的值，从1001年到9999年，精度为秒，与时区无关，使用8个字节的存储空间。TIMESTAMP保存了从1970年以来的秒数，使用4个字节存储，只能从1970年到2038年。MySQL提供FROM_UNIXTIME()函数把时间戳转换为日期，提供UNIX_TIMESTAMP()将日期转换为时间戳。应当尽量使用TIMESTAMP。有时将时间戳存储为整型值，但不会带来任何收益，且不方便处理。如果需要存储比秒更小粒度的日期，可以使用BIGINT存储微秒级别的时间戳。

位数据类型

1、BIT，BIT的行为因存储引擎而异，MyISAM会打包存储所有的BIT列，例如17个单独的BIT列需要3个字节。而InnoDB引擎，每个BIT列都会使用一个足够存储的最小整数类型来存放。MySQL将BIT当做字符串类型，例如将值’00111001’存入bit(8)，直接检索为得到字符吗为57的字符串，即9。但是在数字上下文场景中（例如select a+0 from …），得到的是数字57，因此应谨慎使用BIT类型。

2、SET，可保存打包的位的集合。整数可替代SET，来包装一系列的位。

选择标识符（identifier）

在可满足值的范围内，预留未来增长空间的前提下，选择最小的数据类型。整数通常是标识列最好的选择，并且可以使用AUTO_INCREMENT。对于字符串，很消耗空间，查询也缓慢，针对完全随机的字符串，如MD5()、SHAR1()、UUID()，由于新值会任意分布，会导致INSERT（插入值会随机写入索引的不同位置）以及SELECT语句变得更慢（分布在存盘和内存的不同位置，且缓存失效）。

特殊数据类型

例如ip，可使用32位无符号整数，提供INET_ATON()和INET_NTOA()来转换。

MySQL schema涉及中的陷阱

1、太多的列
存储引擎API需要通过行缓冲格式拷贝数据，才能在服务器层将缓冲内容解码成各个列。转换的代价依赖于列的数量。

2、太多的关联
所谓的“实体-属性-值”EAV设计模式，是个常见的糟糕设计模式。MySQL限制了每个关联操作最多只能有61张表，一个粗略的经验法则，如果希望查询执行的快速且并发性好，最好在12个表内做关联。

3、全能的枚举
注意防止过度使用枚举，在列表的末尾增加枚举值需要ALTER TABLE。

4、NULL值，尽量考虑空值和0等特殊值，但也别走极端。

范式和反范式

1、范式优点：更新操作更快。很少有重复数据。表更小，更好的放到内存里。更少使用DISTINCT或GROUP BY语句。范式缺点：需要更多关联。

2、反范式优点：很好的避免关联。可能会比关联要快的多，可使用触发器更新缓存值。因此需要混用范式和反范式。

缓存表和汇总表

缓存表，为简单的从schema其他表（每次获取的速度比较慢）获取数据的表。汇总表，则保存的是使用group by语句聚合数据的表。

1、物化视图，ORACLE和SQL SERVER自带，MySQL原生不支持，可采用开源工具Flexviews来实现。

2、计数器表，如果计数器为单行，会导致并发更新的性能问题。可增加100行数据，随机选择一个槽进行更新，统计结果时使用sum汇总。
为了提升读查询速度，经常需要建额外索引、增加冗余列、甚至创建缓存表和汇总表，这些都会增加写查询的负担，但设计高性能数据库时，需要更快的读、更慢的写。

3、加快ALTER TABLE操作的速度。修改表结构操作的方式是：创建空表-旧表数据插入新表-删除旧表。有时可以只修改.frm文件。

六、索引

索引的定义

索引（键key），是存储引擎用于快速找到记录的一种数据结构。不恰当的索引当数据量逐渐增大时，性能会急剧下降（传统非固态硬盘）。因此索引优化甚至优先于查询优化，是查询性能优化最有效的手段。
索引可包含一个或多个列的值，如果包含多个列，列的顺序也十分重要，MySQL只能高效地使用索引的最左前缀列。即便使用对象关系映射（ORM）工具，除非是基本的查询，否则很难生成适合索引的查询。
索引是存储引擎层实现的，而不是服务器层实现的。

索引的优点

1、大大减少了服务器需要扫描的数据量
2、可以帮助服务器避免排序和临时表
3、可以将随机I/O变为顺序I/O
索引并不总是最好的工具，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，简单的全表扫描更高效。

索引的类型有，B-Tree索引、哈希索引、空间数据索引、全文索引、其他等。

B-Tree索引

为默认索引，大多数引擎都支持，但可能使用不同的存储结构，例如InnoDB使用的是B+Tree。
B-Tree索引
B-Tree所有的值都是按顺序存储，并且每一个叶子页到根的距离相同。索引从根节点开始搜索，根节点的槽中存放了指向子节点的指针，通过比较节点页的值和要查找的值，可以找到合适的指针进入下层子节点。叶子节点比较特别，它们的指针指向的不是其他的节点页，而是被索引的数据。树的深度和表的大小直接相关。

1、B-Tree索引适用于全键值、键值范围或键前缀查找（最左前缀）。

全值匹配：和索引的所有列进行匹配。
匹配最左前缀：只使用索引的第一列。
匹配列前缀：索引的第一列的开头部分。
匹配范围值：查找索引第一列的某个范围。
精确匹配某一列并范围匹配另外一列：即索引第一列全匹配，第二列范围匹配。
只访问索引的查询：不访问数据行，只需要访问索引。
查询中的ORDER BY操作：索引树中的节点是有序的。

2、B-Tree的限制：

如果不是从最左列查找，则无法使用索引。
不能跳过索引中的列。
如果查询中有某个列的范围查询，则其右侧所有列都无法使用索引优化查找。
以上的限制并非是B-Tree本身导致，而是MySQL优化器和存储引擎使用索引的方式导致。

哈希索引
基于哈希表实现，只有精确匹配索引所有列的查询才有效。Memory存储引擎会对所有的索引列计算一个哈希码，支持非唯一哈希索引。
1、哈希索引的限制：

只包含哈希值和行指针，而不存储字段值，因此不能使用索引中的值来避免读取行。
不是按照索引值顺序存储的，无法用于排序。
不支持部分索引列匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
只支持等值比较查询。
可能会产生哈希冲突，即不同的索引列值却有相同的哈希值。有冲突时，需要遍历链表中所有的行指针，逐行比较。
如果哈希冲突很多，一些索引维护操作的代价也会很高，比如删除表中一行时需要遍历冲突的每一行。

2、InnoDB中有一个“自适应哈希索引”，会在内存中基于B-Tree索引上再创建一个哈希索引，比如快速的哈希查找。该功能无法控制或配置，可以关闭。

3、创建自定义哈希索引。
例如需要存储大量的URL，并且根据URL进行查找。可以删除URL列上的索引，然后新增一个被索引的url-crc列，使用CRC32做哈希。不要使用SHA1或MD5，因为强加密函数的哈希值很长，使用简单哈希函数，保证冲突控制在一个可以接受的范围即可。要避免冲突，需要WHERE条件中带入哈希值和对应列值，便可以使用

1 2	SELECT * FROM url WHERE url="http://www.baidu.com" AND url_crc=CRC32("http://www.baidu.com");

另外，需要手动维护哈希值，可以使用触发器，在插入和更新时维护url_crc列。

如果数据表很大，会出现大量哈希冲突（如32位，当索引有93000条记录时出现冲突的概率是1%），可自定义64位哈希函数，返回整数。如

1	SELECT CONV(RIGHT(MD5('http://www.baidu.com'),16),16,10) AS HASH64;

空间数据索引（R-Tree）

MyISAM表支持空间索引，用来存储地理数据。查询时无须前缀，会从所有维度来索引数据，使用GIS相关函数如MBRCONTAINS()来维护。但MySQL对GIS支持不完善，大部分人不会使用这个特性。可以使用PostgreSQL的PostGIS来替代。

全文索引

是一种特殊类型的索引，用来查找文本中的关键词。全文索引适用于MATCH AGAINST操作，而不是WHERE条件操作。

其他索引类别

第三方存储引擎使用不同类型的数据结构来存储索引。例如TokuDB的分形树索引等。

七、聚簇索引

定义

二级索引(secondary index)，也叫辅助索引，为非聚簇索引，像多列索引、前缀索引、唯一索引。
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

聚簇索引

当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页中，所以一个表只能有一个聚簇索引。叶子页包含了行的全部数据，但是节点页只包含了索引列，在图中，索引列包含的是整数值。目前，还没有数据库引擎支持选择哪个索引作为聚簇索引，而InnoDB默认通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

优点

可以把相关数据保存在一起，比如根据用户ID来聚集数据，只需要从磁盘读取少量数据页就能获取某个用户的全部邮件。
数据访问更快。
使用覆盖索引扫描的查询，可以直接使用页节点中的主键值。

缺点

提高了I/O密集型应用的性能，但如果数据全部放在内存中，则没有什么优势了。
插入速度严重依赖于插入顺序。
更新聚簇索引列的代价很高。
维护索引很昂贵，特别是插入新行或者主键被更新面临“页分页(page split)”问题。当行的主键值要求必须将这一行插入到某个已满的页中，存储引擎会将该页分裂成两个页面来容纳，会导致表占用更多的磁盘空间。使用独享表空间可以弱化碎片。
表如果使用UUID等作为主键，使数据存储稀疏，或者由于页分裂导致数据存储不连续，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键。
如果主键比较大的话，那二级索引（非聚簇索引）将会变的更大，因为二级索引的叶子节点存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间。
二级索引访问需要两次索引查找，而不是一次。因为二级索引的叶子节点存储的是主键值（以主键值作为指向行的“指针”），而不是指向行的物理位置的指针，然后根据这个值去聚簇索引中查找到对应的行，因此做了重复的工作。

InnoDB和MyISAM的数据分布对比

有以下表，

CREATE TABLE layout_test(
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1),KEY(col2)
);

有10000行数据如下，主键取值1-10000，随机顺序插入并使用OPTIMIZE TABLE命令做了优化，col2的值从1-100随机赋值：

行号	col1	col2
0	99	8
1	12	56
2	3000	62
…	…	…
9997	18	8
9998	4700	13
9999	3	93
MyISAM引擎下表的主键分布和col2列索引的分布，如下图，由于MyISAM没有聚簇索引，因此主键索引和其他索引在结构上没有区别，叶子节点保存的是行指针。

InnoDB引擎下表的主键分布和col2列索引的分布，如下图，聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。另外InnoDB的二级索引，存储的不是行指针，而是主键值，并以此作为指向行的“指针”，来减少出现行移动或数据页分裂时二级索引的维护工作。
聚簇索引和非聚簇索引的表分布
下图可清楚看出InnoDB和MyISAM保存数据和索引的区别，有些复杂，慢慢理解。

在InnoDB表中按主键按顺序插入行

如果正在使用InnoDB表并且没有什么数据需要聚集，可以使用AUTO_INCREMENT自增列，保证数据行按顺序写入。当达到页的最大填充因子时（默认为页大小的15/16），下一条记录就会写入新的页中。
最好避免随机的簇聚索引，例如UUID，它是的聚簇索引的插入变得完全随机，没有任何聚集特性。插入行不仅花费时间更长，且索引占用的空间更大，原因有：字段长、写入是乱序导致频道的页分裂，一次插入修改三个页，稀疏产生的碎片等）。
对于高并发工作负载，按主键插入会造成明显的争用，且AUTO_INCREMENT锁机制需要更改innodb_autoinc_lock_mode配置。

覆盖索引

如果可以使用索引来直接获取列的数据，这样就不再需要读取数据行。
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。
覆盖索引必须要存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。
当发起索引覆盖查询时，在EXPLAIN的Extra列可以看到“Using index”（区别于type列的“index”）的信息。
如果使用InnoDB存储，虽然二级索引的列不包含主键，但二级索引的叶子节点都包含了主键的值，也能够对主键进行覆盖查询。

使用索引扫描来做排序

MySQL有两种方式生成有序的结果：通过排序操作；或者按索引顺序扫描。如果EXPLAIN出来的type列的值为index，则说明MySQL使用了索引扫描来做排序。但是有当索引的列顺序和ORDER BY子句的顺序完全一致且列的排序方向都一致时才能使用索引来对结果做排序。ORDER BY子句如果需要利用索引排序，也需要满足索引的最左前缀要求。（有一种特殊情况，前导列为常量时可不满足）。

前缀压缩索引

MyISAM使用前缀压缩来减少索引的大小。默认只压缩字符串。例如索引块的第一个值为perform，第二个值是performance，那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式。
如果是CPU密集型应用，因为扫描需要随机查找，压缩索引会使得在索引查找上慢好几倍。如果是I/O密集型应用，则带来的好处会多于成本。

冗余和重复索引

1、重复索引，是指的在相同的列上按照相同的顺序创建的相同类型的索引。应当避免，发现以后需立即移除。例如

CREATE TABLE test(
	ID INT NOT NULL PRIMARY KEY,
	A INT NOT NULL,
	B INT NOT NULL,
	UNIQUE(ID),
	INDEX(ID)
) ENGINE=InnoDB

该sql会导致创建一个主键，加上唯一限制和索引（MySQL的唯一限制和主键限制都是通过索引实现的），即在ID列上创建了三个重复的索引。

2、冗余索引，和重复索引有些不同。如果创建了索引(A,B)，再创建索引(A)就是冗余索引。因为索引(A,B)可以当做索引(A)来使用。

还有一种特殊的冗余索引是(A,ID)，其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。

但是如果创建索引(B,A)或者索引(B)，就不办事B-Tree索引的冗余索引。大多数情况下都不需要冗余索引，应该尽量拓展已有的索引，而不是创建新索引。特殊情况出于性能方面的考虑，如果拓展已有的索引导致太大影响使用索引的查询的性能。有冗余索引的缺点是索引成本更高，导致操作的速度变慢。
建议使用Percona工具箱的pt-upgrade工具来检查计划中的索引变更。

未使用的索引

可以先打开userstates服务器变量（默认关闭），然后让服务器运行一段时间，查询INFORMATION_SCHEMA.INDEX_STATISTICS查到每个索引的使用频率，来决定删除。或者使用Percona工具箱的pt-index-usage工具来读取查询日志，进行EXPLAIN操作，打印出关于索引的报告。最后删除。

索引和锁

索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少访问行数，从而减少锁的数量。InnoDB在二级索引上使用共享锁，在访问主键索引需要排他锁。

八、高性能的索引策略

正确的创建和使用索引，是实现高性能查询的基础。

独立的列

是指索引列不能是表达式的一部分，也不能是函数的参数。

前缀索引和索引选择性

对于BLOB、TEXT或很长的VARCHAR类型，必须使用前缀索引，可以索引开始的部分字符，节约索引空间，提高索引效率。但这样会降低索引的选择性，索引的选择性是指，不重复的索引值和数据表的记录总数（#T）的比值，介于1/#T和1之间。唯一索引的选择性为1，这是最好的索引选择性，性能也是最好的。缺点是，前缀索引无法做ORDER BY和GROUP BY。
可以使用COUNT统计，从1个前缀开始慢慢增加，直到这个前缀的选择性接近完整列的选择性。
完整列的选择性计算方法：

1	SELECT COUNT(DISTINCT column)/COUNT(*) FROM table;

那么不同前缀长度的选择性：

1	SELECT COUNT(DISTINCT LEFT(column,n))/COUNT(*) FROM table;

那么创建前缀索引：

1	ALTER TABLE table ADD KEY (column(7));

有时候后缀索引页有用途，比如查找某个域名的所有电子邮件地址，可以将字符串反转后存储，然后使用前缀索引。

多列索引

多列索引，并非为每个列创建独立的索引。
索引合并策略（可以通过参数optimizer_switch来关闭）：虽然MySQL5.0以上的版本，查询能够同时使用单列索引进行扫描，并将结果进行合并。这个算法有三个变种：OR条件的union，AND条件的intersection，组合前两种情况的联合和相交。
索引合并策略有时候是一种优化的结果，但实际上更多时候说明表的索引建的很糟糕。如果EXPLAIN中的Extra列，有看到Using union等索引合并，应作检查优化：

当服务器对多个索引做相交操作时，意味着需要一个多列索引。
当服务器对多个索引做联合操作时，需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。
优化器不会把这些计算到查询成本中，导致查询成本被低估，影响查询并发性。

选择合适的索引列顺序

所列索引的列顺序至关重要，“三星索引”，当不需要考虑排序和分组时，将选择性最高的列放到前面通常是很好的选择。
以如下查询为例

1	SELECT * FROM payment WHERE staff_id=2 AND customer_id=584;

那么应该创建一个(staff_id,customer_id)的索引还是应该颠倒一下顺序，需要一些查询来确定表中值得分布情况，并确定哪个列的选择性更高。

1 2	SELECT COUNT(DISTINCT staff_id)/COUNT() AS staff_id_selectivity,COUNT(DISTINCT customer_id)/COUNT() AS customer_id_selectivity, COUNT(*) FROM payment;

哪个值越高，代表哪个列的选择性更高，因此将其作为索引列的第一列。

1	ALTER TABLE payment ADD KEY (column(7));

另外，需要注意的是，经验法则考虑的是全局基数和选择性，多数情况下是有效的，但个别极端的特殊情况的性能无法保证。

找到并修复损坏的表

InnoDB会由于硬件、MySQL自身缺陷或操作系统问题导致索引损坏。MyISAM可能由于系统崩溃导致表损坏。损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等。如果遇到了古怪的问题（不该发生的错误），可以运行CHECK TABLE检查是否发生了损坏。可以使用REPAIR TABLE来修复损坏的表。如果有些存储引擎不支持该命令，可以通过一个不做任何操作（no-op）的ALTER操作来重建表，例如修改表的存储引擎为当前的引擎。
InnoDB一般不会出现损坏，由设计保证。如果发生损坏，则一定是发生了严重的错误，需要立刻调查下，并且进行数据库恢复。

维护准确的索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值得分布信息，以决定如果使用索引。第一个是records_in_range()，传入两个边界值获取这个范围大概有多少条记录。针对MyISAM返回的是精确值，对于InnoDB返回的是估算值。第二个是info()，返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。MySQL优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表的统计信息不准确，可能会使优化器做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息来解决。

MyISAM引擎将索引统计信息存储在磁盘中，需要一次全索引扫描来计算索引基数，整个过程需要锁表。
InnoDB存储在内存中，通过随机的索引访问进行评估。可通过参数innodb_stats_sample_pages来设置样本页数量，更大的值可以帮助生成更准确的索引信息。
可以使用SHOW INDEX FROM命令来查看索引的基数（Cardinality）。

减少碎片

B-Tree索引可能会碎片化，降低查询的效率。
表的数据存储也可能碎片化。有行碎片、行间碎片和剩余空间碎片。
对于MyISAM表三种碎片都可能发生，InnoDB不会出现短小的行碎片。
通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。
InnoDB可以通过先删除再重新创建索引的方式来消除索引的碎片化。
对于不支持的存储引擎，同样可以通过不做操作的ALTER TABLE操作来重建表。

选择索引的三个原则

单行访问是很慢的。特别是在机械硬盘中，如果服务器从存储中读取一个数据块只是为了获取其中一行，就浪费了很多工作。最好读取的快中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
按顺序访问范围数据时很快的，顺序I/O不需要多次磁盘寻道，不需要额外排序操作。GROUP BY 查询也无须排序和聚合运算。
索引覆盖查询时很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行，这避免了大量的单行访问。

九、慢查询

为什么查询速度会慢

1、查询看作一个任务，由一系列子任务组成。优化查询，需要优化其子任务，消除或减少执行次数，或者运行更快。

2、从查询的生命周期来讲，从客户端，到服务器，然后服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”最为重要，包含大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序和分组等。

3、查询需要在不同的地方花费时间，包括网络，cpu计算，统计信息生成，执行计划，锁等待等操作，尤其是向底层存储引擎检索数据的调用操作，需要在内存操作、cpu操作和内存不足时导致的I/O操作上消耗时间。

是否向数据库请求了不需要的数据

1、查询不需要的数据，MySQL会查询出全部的结果集再进行计算，客户端应用程序会接收全部结果集数据，然后抛弃其中大部分数据，这样会产生问题。解决方法是查询后面加上LIMIT。

2、多表关联时返回全部列、总是取出全部列
禁止使用SELECT *的写法，会让优化器无法完成索引覆盖扫描这类优化，带来额外的I/O、内存和cpu的消耗。
3、重复查询相同的数据，针对相同的查询，可将数据缓存来使用。

MySQL是否在扫描额外的记录

衡量查询开销的三个指标：

1、响应时间：是一个表面上的值，但是仍然是最重要的指标。由服务时间和排队时间组成，服务时间是指数据库处理这个查询真正花了多长时间，排队时间是指服务器因为等待某些资源而没有真正执行查询的时间（I/O操作等待、行锁等待等），一般需要判断当前响应时间是不是一个合理的值，来区分是单个查询问题还是服务器问题。

2、扫描的行数和返回的行数
分析查询时，查看该查询扫描的行数，可以判断查询的效率高不高。理想情况下扫描的行数和返回的行数应该是相同的（不常见）。除非特殊情况，一般比例在1：1和10：1之间。

3、扫描的行数和访问类型
在EXPLAIN语句中的type列反应了访问类型，可能会是权标扫描、索引扫描、范围扫描、唯一索引查询、常数引用等，其中，扫描表（type：ALL）、扫描索引（type：ref）、范围访问和单值访问是速度从慢到快，扫描的行数也是从多到少。如果未使用索引进行where条件，Extra会体现“Using where”，表示MySQL将通过WHERE条件来筛选存储引擎返回的记录。
一般Mys能使用以下三种方式应用WHERE条件，从好到坏依次是：

在索引中使用WHERE条件来过滤不匹配的记录，在存储引擎层完成。
使用索引覆盖扫描（在Extra列中出现Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，是在MySQL服务器层完成，无须再回表查询记录。
从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where），在服务器层完成，并需要先从数据表读出记录然后过滤。

如果发现查询需要扫描大量的数据，但只返回少数的行，可以这样来优化

使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
改变库表结构，例如使用单独的汇总表