MySQL5.0中索引的设计和使用

下面讨论下MySQL5.0中的索引的设计和使用。任何东西设计的好，那么使用起来就顺手。不过很多时候给出设计什么规则这些都是相对的。做任何的事情，最重要的是能否根据当时情况就合理的调整你的设计。如果你只会看着书本或者权威来死套什么设计理念来进行实际的开发和设计的话，那么我请你还是少读书为妙。

索引是数据库中用来提高性能的常用工具。（注意如果要优化数据库的性能，这是一个点）。

所有MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎(MyISAM、InnoDB、BDB、MEMORY等)对每个表至少支持16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。MySQL目前还不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引。前缀索引的长度跟存储引擎相关，对于MyISAM存储引擎的表，索引的前缀长度可以达到1000字节长，而对于Inn0DB存储引擎的表，索引的前缀长度最长是767字节。请注意前缀的限制应以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。在为使用多字节字符集的列指定前缀长度时一定要加以考虑。

MySQL中还支持全文本索引（FULLTEXT），该索引可以用于全文搜索。但是在Mysql5.0中只有MyISAM存储引擎支持全文本索引，并且仅仅局限于CHAR、VARCHAR和TEXT列。索引总是对整个列进行的，不支持局部索引。也可以为空间类型创建索引，但是只要MyISAM存储引擎支持空间类型索引，而且索引的字段必须是非空。

创建索引的语法如下：

引用

CREATE [UNIQUE | FULLTEXT | SPATIAL] IDEX index_name

…… …… 余下全文

MySQL执行计划

mysql的执行计划：

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了：

如：explain select * from test1

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra列返回的描述的意义

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一 Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

…… …… 余下全文

//**MYSQL定时执行存储过程

查看event是否开启: show variables like '%sche%';

将事件计划开启: set global event_scheduler=1;

关闭事件任务: alter event e_test ON COMPLETION PRESERVE DISABLE;

开户事件任务: alter event e_test ON COMPLETION PRESERVE ENABLE;

简单实例.

创建表 CREATE TABLE test(endtime DATETIME);

创建存储过程test

CREATE PROCEDURE test ()

BEGIN

update examinfo SET endtime = now() WHERE id = 14;

END;

创建event e_test

CREATE EVENT if not exists e_test

on schedule every 30 second

on completion preserve

do call test();

每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去.

//** MYSQL取一个月前一个月后的时间

date_add() 增加

date_sub()减少

month 月份

minute 分钟

second 秒

例如：select DATE_ADD(NOW(),INTERVAL 1 MONTH); //一个月后的时间

CREATE EVENT IF NOT EXISTS e_test

ON SCHEDULE EVERY 1 DAY

STARTS '2015-09-30 00:00:00'

ON COMPLETION PRESERVE

…… …… 余下全文

MySQL体系结构

了解MySql必须牢牢记住其体系结构图，Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的

1 Connectors指的是不同语言中与SQL的交互

2 Management Serveices& Utilities：系统管理和控制工具

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：查询缓存。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 8 Engine ：存储引擎。

…… …… 余下全文

MySQL生产环境突发故障处理手册

1. LOAD飙高

一般导致MySQL服务器LOAD突然飙高，可能的五种情况：

1>.全表扫描的SQL语句；

2>.SELECT操作语句的执行计划走错；

3>.存在UPDATE/DELETE 语句没有索引可选择，而导致堵塞其他SQL语句的执行； 4>.存在修改表结构或OPTIMIZE 语句执行；

5>.大数据量的导入 或 导出，尤其数据库的逻辑备份操作；

6>.业务量大到超过服务器处理能力（我们大家都高度关注业务发展，以及公司业务特点， 还有与开发和运营保持良好联系，很难出现未知的业务突然爆发性增长）；

要解决LOAD飙高，必须先找到造成飙高的真实原因，请登陆数据库服务器后，执行命令： SHOW PROCESSLIST;（适合MySQL各种版本）

或

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND <> ‘sleep’ AND TIME>100;（5.1.x系列及以上版本）

若一直处在执行状态，且执行时间比较久，可以分析下SQL语句执行计划： EXPLAIN SQL-statement;

若执行计划不合理，则可以根据SQL类型选择是否与应用负责人联系。首先，查找造成服务器LOAD飙高的PID，特别是DELETE 或UPDATE等会堵住其他SQL语句的PID，然后进入MySQL命令行工具中，对一些SQL先记录下来，再适情考虑执行：kill sql_pid;

1.1 统计信息更新

单表索引统计信息查看命令：SHOW INDEXES FROM tablename;

若发现其统计信息存在偏差，则可以执行：ANALYZE LOCAL TABLE tablename; 备注：请加上LOCAL 参数，从而使此语句执行时不需要登记到二进制文件中。

…… …… 余下全文

对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。一般来说，要保证数据库的效率，要做好以下四个方面的工作：数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统，这个顺序也表现了这四个工作对性能影响的大小。下面我们逐个阐明：

一、数据库设计

适度的反范式,注意是适度的。我们都知道三范式，基于三范式建立的模型是最有效保存数 据的方式，也是最容易扩展的模式。我们在开发应用程序时，设计的数据库要最大程度的遵守三范式，特别是对于OLTP型的系统，三范式是必须遵守的规则。当 然，三范式最大的问题在于查询时通常需要join很多表，导致查询效率很低。所以有时候基于性能考虑，我们需要有意的违反三范式，适度的做冗余，以达到提 高查询效率的目的。注意这里的反范式是适度的，必须为这种做法提供充分的理由。下面就是一个糟糕的实例：

在这里，为了提高学生活动记录的检索效率，把单位名称冗余到学生活动记录表里。单位信息有500条记录，而学生活动记录在一年内大概有200万数据量。 如果学生活动记录表不冗余这个单位名称字段，只包含三个int字段和一个timestamp字段，只占用了16字节，是一个很小的表。而冗余了一个 varchar(32)的字段后则是原来的3倍，检索起来相应也多了这么多的I/O

。而

–1–

且记录数相差悬殊，500 VS 2000000 ，导致更新一个单位名称还要更新4000条冗余记录。由此可见，这个冗余根本就是适得其反。

下面这个冗余就很好

可以看到，[学生考试总分]是冗余的，这个分数完全可以通过[得分情况]汇总得到。在【学生考试总分】里，一次考试一个学生只有一条记录，而在【得分情 况】里，一个学生针对试卷里一个小题的一个小问一条记录，粗略的算一下比例大概是1：100。而且判卷子得分是不会轻易变的，更新的频率不高，所以说这个冗余是比较好的。

…… …… 余下全文

千万别在论坛、群里问，我的机器好慢怎么回事？我的机器内存泄露了怎么回事？

这类大而空的问题一点意义都没有，其实谁都不知道。你要做的是用下面的思路、方法、工具去定位它

解决问题思路

Java程序问题(运行慢)

先通过top查看整个CPU资源使用情况;

通过top-Hppid查看java进程的每一个线程占用CPU的情况;

如果有一个线程占用CPU过高，有两种可能：

没有内存了，Java垃圾回收线程不停地运行尝试回收内存，但是每次无法收回，确认：http://lxylc.com

jstat-gcutilpid1s观察10多秒钟就能发现了，看是不是内存使用率接近100%了

类似于死循环(hash冲突攻击)，就是一个线程一直占用一个核的所有CPU资源(其实一个线程总是暂用一个核超过50%的资源都是不太正常的)，解决：

用我课堂的checkPerf脚本，定位这个线程具体执行的任务(能具体到某一行)，对应看代码解决。

如果有很多线程，每个线程占用的CPU都不多，那基本是正常的。

如果死锁：

jstack-lpid多执行几次，统计一下stack中总是在等待哪些锁，可以对锁id进行排序统计(sortuniqgrep)

上面列出来的都是明显的瓶颈，最可怕的是哪里都没有明显的瓶颈，哪里都要偷一点点资源走，这是可以试试JProfiler这样更专业一点的工具，同时要配合自己对业务的了解来解决。http://g60j.com

Java内存的问题，如果有内存泄露(就是执行完fgc/oldgc后不能回收的内存不断地增加)：

快速解决：jmap-histo:livepid来统计所有对象的个数

(String/char/Integer/HashEntry这样的对象很多很正常，主要是盯着你们公司的包名下的那些对象)

每隔一分钟执行一次上面的命令，执行5次以上，看看你们公司报名下的对象数量哪个在一直增加，那基本上就是这个对象引起了泄露;

…… …… 余下全文