MySQL优化方式

表的设计合理化（符合3NF），实际设计中不可能百分百遵守3NF
	
添加适当索引（index）普通索引，主键索引，唯一索引，全文索引
	
SQL语句优化
	
分表技术（水平分割，垂直分割）
	
读写分离
	
存储过程
	
对mysql配置优化（配置最大并发数my.ini,调整缓存大小）
	
mysql服务器硬件升级
	
定时去清除不需要的数据，定时进行碎片整理
数据库三大范式

第一范式：1NF是对属性的原子性约束，要求属性列具有原子性，不可再分解。

第二范式：2NF是对记录的唯一性约束，表中的记录是唯一性的。

第三范式：3NF是对字段冗余性的约束，要求字段没有冗余。

分表分库

垂直拆分：垂直拆分就是要把表按模块划分到不同数据库中。

水平拆分：把一个表按照某种规则把数据划分到不同表或数据库里。有的按照注册时间，取模，账号规则，年份来拆分。

SQL优化

使用show status查看mysql服务器状态信息

show status like 'uptime'           mysql数据库启动了多少时间

show status like 'com_select'(update,delete,insert)           显示数据库的查询，更新，添加，删除次数

show status like 'connections'            显示到mysql数据库的连接数

show status like 'slow_queries'             显示慢查询次数

慢查询

mysql默认10秒内没有响应sql结果，则为慢查询，可以修改慢查询默认时间

show variables like 'long_query_time'          查看慢查询时间

set long_query_time = 1          修改慢查询时间，但是重启mysql  long_query_time 还是10秒

如何将慢查询定位到日志中

mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中，long_query_time的默认值为10，运行10s以上的语句，会被记录下来，mysql数据库默认不开启慢查询日志，需要我们手动来设置这个·参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定性能影响。

show variables like 'long_query_time'    查看慢查询设置的时间

set global slow_query_log=1;         开启慢查询日志

set global long_query_time=1      设置慢查询日志时间

show variables like '%slow_query_log%'      查看慢查询一些信息

show global status like '%slow_queries%'       查看有多少条慢查询记录

索引

索引用来快速地寻找那些具有特定值的记录，所有mysql索引都以b-树的形式保存。

查询索引：desc 表名     show index from 表名     show keys from 表名

主键索引：primary key   列如：alter table tablename add primary key 列名   alter table articles drop primary key删除索引

全文索引：fulltext 列如：alter table tablename add fulltext (列名)    select * from 表名 where match(列名) against('关键字')

唯一索引：unique  列如：alter table tablename add unique 索引名字 列名    可以为多个null，不可以为多个空字符串

普通索引：index  列如：create index 索引名 on 表名 （列名）

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库表中数据，索引的实现通常使用B树或变种B+树。为表设置索引要付出代价的，意识增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间。

mysql explain执行计划

语法：explain select * from 表名

explain列解释

table：显示这一行的数据时关于那张表的

type：显示连接使用了何种类型，好到差的连接类型为const,eq_reg,ref,range,indexhe 和 all

          system：表只有一行，system表，这是const连接类型的特殊情况

          const：表中的一个记录的最大值能够匹配这个查询

          eq_ref：在连接中，mysql在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，他在查询使用了索引为主键或唯一键的全部时使用。

          ref：这个俩呢及类型只有在查询使用了不是唯一或主键的键或者是这些类型的部分时发生。

          range：使用索引返回一个范围中的行。

          index：对前面的表中的每一个记录联合进行完全扫描。

          all：对于前面的每一记录联合进行完全扫描，比较糟糕，应该尽量避免。

possible_keys：显示可能应用在这张表中的索引，如果为空，没有可能的索引。

key：实际使用的索引，如果为null，则没有使用索引。

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

ref：显示索引的那一列被使用了。

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

extra：mysql解析查询的额外信息。

索引的代价

占用磁盘空间

对dml（update,delete,insert）语句的效率影响

增删改会对索引影响，因为索引要重新整理。

存储引擎
			
索引类型
		
myisam
			
btree
		
innodb
			
btree
		
memory
			
hash,btree
		
那些列上适合添加索引

查询作为查询条件字段应该创建索引

唯一性太差的字段不合适单独创建索引，即使频繁

频繁更新字段，也不要定义索引

不会出现在where语句的字段不要创建索引

满足以下一条件的字段，才应该创建索引

1.肯定在where条件经常使用

2.该字段的内容不是唯一的几个值

3.字段内容不是频繁变化

SQL优化技巧

使用group by分组查询是，默认分组后，还会排序，可能会降低速度，在group by后面增加order by null防止排序
	
有些情况下，可以使用连接来代替子查询，因为使用join，mysql不需要在内存中创建临时表。
	
对查询进行优化，要尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引，避免在where字句中对字段进行null值判断。
	
不要使用大于=
MySQL数据引擎

myisam存储：如果表对事物要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎。

innodb存储：对事物要求高，保存的数据都是重要数据。

MyISAM和INNODB的区别

事务安全（myisam不支持事务，innodb支持事务）

查询和添加速度（myisam批量插入速度快）

支持全文索引（myisam支持全文索引，innodb不支持全文索引）

锁机制（myisam表锁，innodb行锁）

外键（myisam不支持外键，innodb支持外键）

memory存储：数据变化频繁，不需要入库，同时又频繁的查询和修改，速度极快（mysql重启，数据就不存在了）

myisam注意事项

如果数据库的存储引擎是myisam，一定要定时进行碎片整理

optimize table 表名   删除碎片

 

主从复制

mysql主从复制作用：读写分离，数据备份，高可用，集群。

mysql主从复制原理：二进制sql执行文件

修改主服务器：my.ini或my.cof文件
server_id=100   ###服务器id
log-bin=mysql-bin      ###开启日志文件



###主服务器给从服务器账号授权
GRANT REPLICATION SLAVE ON *.* TO 'wenlong'@'%' IDENTIFIED BY 'wenlong';


###查询master的状态,如果结果为null，则主服务器my.ini没有配置好
show master status;


###修改从（slave）服务器
server_id=200
log-bin=mysql-bin
binlog_do_db=test
change master to master_host='ip地址'，master_user='用户名'，master_password='密码'，master_log_file='mysql-bin.000001',master_log_pos=343;


###启动同步
start slave


###检查从服务器复制功能状态  slave_io_running:yes  slave_sql_running:yes
show slave status

读写分离

在数据库集群架构中，让主库负责处理事务性查询，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。

mycat：一个开源的分布式数据库系统，但是因为数据库一般都有自己的数据库引擎，而mycat并没有属于自己的独特数据引擎，所以严格意义上说并不能算是一个完整的数据库系统，只能说是一个在应用和数据库之间起桥梁作用的中间件。

配置server.xml

<!-- 添加user -->
<user name="mycat">
    <property name="password">mycat</property>
    <property name="schemas">mycat</property>
</user>
	
<!-- 添加user -->
<user name="mycat_red">
    <property name="password">mycat_red</property>
    <property name="schemas">mycat</property>
    <property name="readOnly">true</property>
</user>

配置schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
    <!-- 与server.xml中user的schemas名一致 -->
    <schema name="mycat" checkSQLschema="true" sqlMaxLimit="100">
        <table name="t_users" primaryKey="user_id" dataNode="dn1" rule="rule1"/>
        <table name="t_message" type="global" primaryKey="messages_id" dataNode="dn1" />
    </schema>
<dataNode name="dn1" dataHost="jdbchost" database="weibo_simple" />
    <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
         <heartbeat>select user()</heartbeat>  
        <writeHost host="hostMaster" url="127.0.0.1:3306" user="root" password="root">
        </writeHost>
        <writeHost host="hostSlave" url="127.0.0.1:3306" user="root" password="root"/>
    </dataHost>
    
</mycat:schema>

配置rule.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
	 <tableRule name="rule1">
        <rule>
            <columns>user_id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>
    <function name="func1" class="org.opencloudb.route.function.AutoPartitionByLong">
  	<property name="mapFile">autopartition-long.txt</property>
    </function>
</mycat:rule>

为了更好定位错误，修改log4j.xml

<level value="debug"/>

双击startup_nowrap.bat开始启动

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

前言:
        回想当初刚投入工作时，想的是努力工作，早日成为一个老司机，如今也已经工作一年有余，现在的工作做的倒是顺风顺水，然而想想自己的成长，却觉得自己的技术成长极少，甚至之前熟悉的技术，也因为工作中极少使用而被遗忘，细思极恐。。。所以痛下决心，不能再如此安逸下去了。。。于是决定在工作之余，学习知识，提高自己。
       本篇博客是我看了《MySQL5.6从零开始学》这本书之后，总结了该书中所提到的一些关于MySQL优化相关的知识。
概要：
          MySQL优化，大的层面分为三个：优化查询，优化数据库结构，优化MySQL服务器。本文只讲述三个大层面下的各个优化方法，至于每种优化方法的细节不做具体描述。主要帮助自己整理思路，也可作为一个学习MySQL优化的提纲。
一，优化查询
         （1）分析查询语句
           要想优化一条查询语句，我们首先要分析这个查询语句，MySQL使用 EXPLAIN 和 DESCRIBE关键字分析查询语句。其具体使用语法，和分析参数解析在此不再详述。
       （2）使用索引查询
             使用索引可以增加查询速度，这一点毋庸置疑，单单对于查询来说增益非常明显。但是创建了索引相应的会减慢插入，删除的速度。大量的索引文件也会增加数据库的体积。所以有利有弊。如何创建索引，以及应该何时创建索引，使用索引的利弊，这些都是非常有必要研究的，在此不再详述。
        （3）优化子查询

             这里只有一句话：使用连接查询代替子查询。至于为什么建议使用连接查询，而不建议使用子查询，以及如何将连接查询替换为子查询，这些具体细节值得深究，在此不再详述了，我会在下一篇博客中总结一下。
二，优化数据库结构
          （1）将字段很多的表分解成多个表。
              这个嘛一看就懂了啊。如果一个表中的字段超过了50个，那真的是要吐的，可以通过创建父子表，关联表来细化拆分。
        （2）增加中间表
             这个好处也是很明显的，使用中间表进行关联，主要用于解决多对多的关系，建议在中间表中也应该创建一个自增的id作为主键。
         （3）增加冗余字段
             适度的增加冗余字段能够提升查询速度，简化操作，因为不用每次都使用联接查询，获取属性。但是使用冗余字段违反了数据库第三范式的设计原则，需要根据业务设计冗余字段。
          （4）优化插入记录的速度
               正如上面所说，创建索引提升了查询速度，却减慢插入的速度，所以增加插入速度一条重要的操作就是，禁用索引（插入前禁用，插入后在开启）;另外还有禁用唯一性检查，禁用外键检查，禁止自动提交，使用批量插入，尽量使用LOAD DATA INFILE 语句做批量插入。至于这些操作的具体实现在此不再详述。
          （5）分析表，检查表，优化表
                MySQL提供了分析表，检查表，优化表的语句，分析表主要是分析关键字的分布；检查表主要是检查表是否存在错误；优化表主要是消除删除和更新造成的空间浪费。
三，优化MySQL服务器
                
优化MySQL服务器，属于专业操作，在此只做了解，如需深入研究，还请自己百度。

             （1）优化服务器硬件
         （2）优化MySQL参数

Mysql优化
Mysql的逻辑结构

组件：客户端、核心服务、存储引擎



Mysql查询的5个过程过程：


客户端向MySQL服务器发送一条查询请求


服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段


服务器进行SQL解析、预处理、再由优化器生成对应的执行计划


MySQL根据执行计划，调用存储引擎的API来执行查询


将结果返回给客户端，同时缓存查询结果


Mysql 优化的三个方向：
a.设计合理Schemea

1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。

2. 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。

3.UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。

4. 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

5. TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

6. 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

7. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

8. 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇技淫巧可以解决这个问题，有兴趣可自行查阅。
b.创建高性能索引

索引的类别

PRIMARY KEY： 主键，这意味着索引值必须是唯一的，且不能为NULL。
UNIQUE:创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
INDEX: 普通索引，索引值可出现多次。
FULLTEXT: 用于全文索引.

使用索引的注意事项：

表的主键和外键必须有索引
经常出现where字句的字段
经常要查询的列
选择性高的字段
经常用户排序的字段
数据超过300以上
表的索引最好不要超过5个

c.根据业务需求选择合理的数据类型和存储引擎
引擎(engine)说明及区别
ISAM引擎：读取数据速度很快，而且不占用大量的内存和存储资源；但是ISAM不支持事务处理、不支持外来键、不能够容错、也不支持索引。

如果硬盘崩溃，数据文件也无法恢复，因此若把ISAM用在关键任务上，就必须经常备份实时数据。
MyISAM引擎：MyISAM强调了快速读取操作。(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

静态MyISAM：如果数据库中的各个数据列的长度都是预先固定好的，服务器将自动选择这种表类型。（1）表中每一条记录所占用的空间都是一样的，所以存取和更新的效率非常高。（2）当数据受损时，回复工作也比较容易做。

动态MyISAM：如果数据表中出现varchar、xxxtex或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小。

压缩MyISAM：这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。

memory（heap）适合：HEAP允许只驻留在内存里的临时表格。因为数据仅存储在内存中，因此数据存取速度比ISAM和MYISAM都快。利用HASH进行索引（记住，用完表格之后就删除表格）
InnoDB引擎：具有提交、回滚和崩溃恢复能力的失误安全存储引擎。(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

唯一一个支持外键的引擎。
archive引擎：日志记录和聚合分析方面。archive不支持索引，仅支持insert和select语句。

Mysql优化综合性的问题：

A、表的是设计合理化(符合 3范式)

B、添加适当的索引(index)[四种：普通索引，主键索引，唯一索引，unique，全文索引]

C、分表技术(水平分割，垂直分割)

D、读写[写：update/delete/add]分离

E、存储过程[模块化编程，可以提高速度]

F、对mysql配置优化[配置最大并发数，my.ini调整缓存大小]

G、Mysql服务器引荐升级

H、定时的去清楚不需要的数据，定时进行碎片整理

.1.  数据库的表设计

第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。没有冗余的数据库设计可以做到。

.2.  Sql优化的一般步骤：

操作步骤:

1、通过show status命令了解各种SQL的执行频率。

2、 定位执行效率较低的SQL语句-（重点select）

3、 通过explain分析低效率的SQL语句的执行情况

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

 

MySQL通过使用show [session|global] status 命令可以提供服务器状态信息。



 

session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。

show status like ‘Com_%’;

其中Com_XXX表示XXX语句所执行的次数。Eg:Com_insert,Com_Select…

重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

Connections：试图连接MySQL服务器的次数

Uptime：服务器工作的时间（单位秒）

Slow_queries：慢查询的次数 (默认是慢查询时间10s)

Show status like‘Handler_read%’使用查询的次数



 

♥定位慢查询：

         在默认的情况下mysql是不记录满查询日志的，需要在启动的时候指定

         \bin\mysqld.exe- -safe-mode – slow-query-log[mysql5.5可以在my.ini中指定]

\bin\mysqld.exe- -log-slow-queries=d:bac.log

具体操作如下：

     如果启用了慢查询，默认存储在mysql.ini文件的此处

      

 

         1、重启mysql,找到datadir的路劲，使用cmd进入到data的上级目录

         2、运行命令\bin\mysqld.exe –safe-mode  –slow-query-log(注意执行前先关闭mysql服务)

         3、生成的日志文件记录着所有的记录信息

显示慢查询的时间：Show variables like ‘long_query_time’;

重新设置满查询的时间：Set long_query_time=2;

 

修改命令结束符：(为了存储过程能够正常执行，我们需要把命令结束符号进行修修改)

Delimiter $$

如何把慢查询的sql语句记录到我们的日志中(默认情况下mysql是不会记录的，需要在启动mysql的时候，指定慢查询的)。

 

 

.3.  索引

♥索引的类型：

★四种索引①主键索引②唯一索引③普通索引④全文索引

       一、添加

      1.1主键索引添加

                   当把一张表的某列设置为主键的时候，则该列就是主键索引。

                  Createtable aaa(id int unsigned primary key auto_increment,

                  name varchar(32) not null default);

       1.2普通索引

                  一般来说，普通索引是先创建表，然后创建普通索引。

                  比如：

                  Createindex索引名 from表名

       1.3创建全文索引

         全文索引，主要是针对文件，比如文章的索引全文索引针对MyISAM有用，针对innodb没有用

                          Create table articles(

                          Id int unsignedauto_increment not null primary key,

                          Title varchar(20),

                          Body text,

                          Fulltext (title,body)

                          )engine=myisam charsetutf8;

                  错误用法：

                          Select * from articles where body like ‘%mysql%’[不会使用到全文索引]

                  证明：

                          Explain select * from articles body like ‘%mysql%’;

                  正确的用法：

                          Select * from article wherematch(title,body)against(‘database’);[可以]

                  说明：

                          1、在mysql中fulltest索引值针对myisam生效

                         2、针对英文生效，àsphinx(coreseek)技术处理中文

                          3、使用的方法，match(字段名，…)against(‘关键词’)

                         4、全文索引一个叫停止词。因为在一个文本中，创建索引的是一个无穷大的书，因此，对一些常用词和字符就不会创建，这些词，称之为停止词

           1.4创建唯一索引

                       当表的某列被指定为unique约束时，这列就是唯一索引

                        第一种、Create table ddd(id int primary keyauto_increment,name varchar(32) unique);

                                    这时，name默认就是唯一索引

                        第二种、create table eee(id int primary keyauto_increment,name varchar(32));

                              Createunique index索引名 on表名(列名)

                  简单的说：PRIMARY KEY=UNIQUE+NOT NULL

                  Unique字段可以为null，并可以有多个null,但是如果是具体内容，则不能重复

                  主键字段，不能为null,也不能重复

      二、查询

         1．Desc表名[该方法的缺点，不能够现实索引名]

         2．Show index from表名;

                  select index from表名\G

        3．show keys from表名

      三、删除

         Altertable 表名 drop  index 索引名，

         Altertable 表名 drop primary key。(删除主键索引名)

       四、修改

             先删除，在全部

二、针对SQL编写导致的慢 SQL，优化起来还是相对比较方便的。正如上一节提到的正确的使用索引能加快查询速度，那么我们在编写 SQL 时就需要注意与索引相关的规则：

1.字段类型转换导致不用索引，如字符串类型的不用引号，数字类型的用引号等，这有可能会用不到索引导致全表扫描；

2.mysql 不支持函数转换，所以字段前面不能加函数，否则这将用不到索引；

3.不要在字段前面加减运算；

4.字符串比较长的可以考虑索引一部份减少索引文件大小，提高写入效率；

5.like % 在前面用不到索引；

6.根据联合索引的第二个及以后的字段单独查询用不到索引；

7.不要使用 select *；

8.排序请尽量使用升序 ;

9.or 的查询尽量用 union 代替（Innodb）；

10.复合索引高选择性的字段排在前面；

11.order by / groupby 字段包括在索引当中减少排序，效率会更高。

除了上述索引使用规则外，SQL 编写时还需要特别注意一下几点：

1.尽量规避大事务的 SQL，大事务的 SQL 会影响数据库的并发性能及主从同步；

2.分页语句 limit 的问题；

3.删除表所有记录请用 truncate，不要用 delete；

4.不让 mysql 干多余的事情，如计算；

5.输写 SQL 带字段，以防止后面表变更带来的问题，性能也是比较优的 ( 涉及到数据字典解析，请自行查询资料)；

6.在 Innodb上用 select count(*)，因为 Innodb 会存储统计信息；

7.慎用 Oder by rand()。

三、显示慢查询的次数：show status like 'slow_queries';

                    

存储类型


			
存储引擎
			
			

			
允许的索引类型
			
		

			
MyISAM
			
			

			
BTREE
			
		

			
InnoDB
			
			

			
BTREE
			
		

			
MEMORY/HEAP
			
			

			
BTREE
			
		
HEAP是较早的mysql版本

四、Explain分析低效率的SQL语句：

             

会产生如下信息：

             select_type:表示查询的类型。

            table:输出结果集的表

            type:表示表的连接类型

            possible_keys:表示查询时，可能使用的索引

            key:表示实际使用的索引

            key_len:索引字段的长度

            rows:扫描出的行数(估算的行数)

            Extra:执行情况的描述和说明

Select_type类型：

            primary : 子查询中最外层查询

            subquery : 子查询内层第一个select，结果不依赖于外部查询

            dependent subquery : 子查询内层第一个select，依赖于外部查询

            union:union语句中第二个select开始后面所有select

            simple: 简单模式

            union result: union中合并结果

type 类型:

            all: 完整的表扫描 通常不好

            system : 表仅有一行(=系统表) 这是const联接类型的一个特例

            const : 表最多有一个匹配行

extra 类型:

            no table: query语句中使用 from dual 或不含任何from子句

            Using filesort : 当query中包含 order by 操作，而且无法利用索引完成排序

             impossible WHERE noticed after readingconst tables:Mysql query optimizer

通过收集统计信息不可能存在结果

            Using temporary : 某些操作必须使用临时表，常见 group by ,order by

            Using where: 不用读取表中所有信息，仅通过索引就可以获取所需数据

 

.4.  ☆★为什么使用了索引后查询速度会变快

 

       普通的查询如果没有索引，他会一直去执行，及时匹配到了还要继续查询，不能保证后面有没有要查询的。要全文索引。





 

 

■索引使用的注意事项

索引的代价：

         1、占用磁盘空间

2、对DML(insert,update,create)操作有影响，变慢

■总结：满足以下条件，才应该创建索引

A、肯定在where经常使用

B、该字段的内容不是唯一的几个值(sex)

C、字段内容不是频繁变化

■使用索引的注意事项：

alter table dept add index myind (dname,loc); // dname就是左边的列，loc是右边的列

下列情况有可能使用到索引

a.对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用 explain select * from dept where dname='aaa';

b.对于使用like的查询，查询条件如果是'%aaa'则不会使用到索引，'aaa%'会使用到索引

下列情况不会使用索引 ：

a.如果条件中有or，即使其中有条件带索引也不会使用换言之，就是要求使用的所有字段都创建索引，建议：尽量避免使用or关键字

b.对于多列索引，不是使用的第一部分，则不会使用索引

explain select * from dept where loc='aaa';// 多列索引时，loc为右边列，索引不会使用到

c.like查询是以%开头如果一定要使用，则使用全文索引去查询

d.如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不使用索引

e.如果MySQL估计使用全表扫描要比使用索引块，则不使用索引

如何选择mysql的存储引擎

         1：myISAM

                   如果表对事务的要求不高，同事一查询和添加为主的，

                   比如BBS中的发帖，回帖。

2：InnoDB

         对事务的要求高，保存的数据都是重要数据，

         比如订单，账户表

3：Memory:

         数据变化频繁，不需要入库同时又进场查询和修改。

myISAM和InnoDB的区别：

1、myISAM批量插入快，InnoDB插入慢，myISAM插入时候不排序。

2、InnoDB支持事务，myISAM不支持事务。

3、MyISAM支持全文索引，

4、锁机制，myISAM是表锁，InnoDB是行锁

5、myISAM不支持外键，InnoDB支持外健

① 在进度要求高的应用中，建议使用定点数据来存储数值，组U一保证数据的准确性，deciaml进度比float高，尽量使用

②  对于存储引擎的myISAM的数据库，如果进场要走删除和修改的操作，要定时执行optimize_table_name功能对表进行碎片整理。

③  日期类型要根据实际需要选择引用的最小存储的早期类型，

手动备份数据库：

1、进入cmd

2、Mysqldump –uroot –proot数据库【表名1,表名2…】 > 文件路径

Eg: mysqldump -uroot -proot temp > d:/temp.bak

恢复备份文件数据:

         Source d:/temp.bak（在mysql控制台）

合理的硬件资源和操作系统

         Master

Slave1

Slave2

Slave3

         主库master用来写入，slave1—slave3都用来做select，每个数据库

分担的压力小了很多。

要实现这种方式，需要程序特别设计，写都操作master，读都操作

slave，给程序开发带来了额外负担。当然目前已经有中间件来实现这个

代理，对程序来读写哪些数据库是透明的。官方有个mysql-proxy，但是

还是alpha版本的。新浪有个amobe for mysql，也可达到这个目的，结构

如下　



.5.  表的分割

水平分割：

         大数据量的表,我们在提供检索的时候，应该根据业务的需求，找到表的标准，并在检索页面约束用户的检索方式，而且要配合分页，

         案例：大数据量的用户表

三张表：qqlogin0,qqlogin1,qqlogin2

将用户id%3,按结果放入不同的表当中

create tableqqlogin0(

       id int unsigned not null primary key,/* 这个id不能设置自增长 */

       name varchar（32）not null default''，

       pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

 

创建表qqlogin1（

        id int unsigned not null主键，/ *这个id不能设置自增长* /

        name varchar（32）not null default''，

        pwd varchar（32）not null default''

    ）engine = myisam default charset = utf8;

 

创建表qqlogin2（

        id int unsigned not null主键，/ *这个id不能设置自增长* /

        name varchar（32）not null default''，

        pwd varchar（32）not null default''

    ）engine = myisam default charset = utf8;

垂直分割：

                   把某个表的某些字段，这些字段，在查询时候并不关系，但是数据量很大，我们建议将这些字段放到一个表中，从而提高效率，

0.6。优化的Mysql的配置

MY.INI

port = 3306默认端口是3306，

如果想修改端口port = 3309，在mysql_connect（'localhost：3309'，'root'，'root'）;要注意

query_cache_size = 15M这个是查询缓存的大小

InnoDB的参数也可以调大以下两个参数

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size = 1G

myisam需要调整key_buffer_size

调整参数还要看状态，用show status可以看到当前状态，以决定该调整哪些参数

0.7。增量备份

实际案例：

         如何进行增量备份，和恢复

步骤：

如图1所示，配置的my.ini文件或者是my.cof，启用二进制备份



 

2，重新启动的MySQL

启动之后会发现mylog目录下生成了一下文件



 

其中：E：\二进制日志\ mylog.index索引文件，有哪些备份文件

E：\二进制日志\ mylog.000001存放用户对象数据库操作的文件

3，当我们进行操作的时候（选择）

查看需要进入到MySQL的的安装目录下的bin中，然后执行mysqlbinlog可以文件，后面追加文件路径



 

 

 

如图4所示，恢复到某个语句的时间点

4,1按照时间点回复

Mysqlbinlog -stop-datetime =“2013-01-17 12:00:23”d：/binlog/mylog.000001 | mysq -uroot -p

（恢复到停止时间之前的所有数据）

Mysqlbinlog-start-datetime =“2013-01-17 12:00:23”d：/binlog/mylog.000001 | mysq -uroot -p

（恢复开始时间到之后的所有数据）

4,2按照位置恢复

  Mysqlbinlog-stop-position =“234”d：/binlog/mylog.000001 | mysq -uroot -p

（恢复到停止时间之前的所有数据）

Mysqlbinlog-start-position =“234”d：/binlog/mylog.000001 | mysq -uroot -p

（恢复开始时间到之后的所有数据）