对于数据库的优化可以从以下几点切入：
- 表的设计合理，符合3NF
- 添加适当的索引（index）【索引：普通索引，主键索引，唯一索引，全文 索引】
- 分表技术（垂直分割、水平分割）
- 读写分离
- 存储过程（模块化编程，可以提高速度，但是移植性比较差）
- 对mysql的配置优化（配置最大并发数my.ini, 调整缓存大小 max_connections）
- mysql服务器硬件升级
- 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)
关于MyISAM
MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。（注意MySQL 5.1不支持ISAM）。
每个MyISAM在磁盘上存储成三个文件，每一个文件的名字均以表的名字开始，扩展名指出文件类型。
- .frm文件存储表定义；
- .MYD (MYData)文件存储表的数据；
- .MYI (MYIndex)文件存储表的索引。
- .opt为后缀的：用来记录该库的默认字符集编码和字符集排序规则用的
1、数据库分类
- 关系型数据库 ：Mysql/Oracle/db2/Informix/Sysbase/SqlServer
- 非关系型数据库(特点：面向对象或者集合)：NoSql、MongoDB
2、表的设计（符合3NF）
1NF：即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要数据
库是关系型数据库，就自动的满足1NF
2NF：表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现（设置
主键）
3NF：即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就
不应该单独的设计一个字段来存放.
2.1 Sql语句本身的优化
如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)
首先我们了解mysql数据库的一些运行状态如何查询
比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete.. / 当前连接是多少
常用的1
2
3
4
5
6
7
8
9
10
11
12
13show status like ‘uptime’ ; （mysql启动了多长时间（秒））
show stauts like ‘com_select’ showstauts like ‘com_insert’ ...类推 update delete（查询执行了多少次查询、添加..）
show[session|global] status like .... 如果你不写 [session|global] 默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global)
show global status like ‘com_select’;
show status like ‘connections’; （查询出现在连接mysql的数量）
//显示慢查询次数
show status like ‘slow_queries’;
如何去定位慢查询
默认情况下，mysql认为10秒才是一个慢查询.
修改mysql的慢查询1
2
3show variables like ‘long_query_time’ ; //可以显示当前慢查询时间
set long_query_time=1 ;//可以修改慢查询时间
这时我们如果出现一条语句执行时间超过1秒种，就会统计到。
如何把慢查询的sql记录到我们的一个日志中
在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以
Cmd指令1
2
3bin\mysqld.exe - -safe-mode - -slow-query-log [mysql5.5 可以在my.ini指定]
bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
Safe：安全模式，这样可以记录日志，记录下sql语句，如果有特殊情况，数据可以回滚
先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在my.ini 文件中记录的位置
1 | ### Path to the database root |
注意：修改此文件的位置，有可能会改变索引。索引是跟磁盘地址有关联的，所以不要轻易的修改此文件的位置
测试,可以看到在日志中就记录下我们的mysql慢sql语句.
番外篇：
通过 explain 语句可以分析mysql如何执行你的sql语句（可以看出语句有没有用到索引、扫描方式是什么样子的、有没有额外的开销）
查询海量数据的时候，查询已经查询出的数据的时候，速度会变快，因为mysql会把原先查出的数据缓存下，再次查询的时候，会直接把数据返回回来
2、添加索引
2.1 为什么添加索引后，查询速度会变快？
(1). 不添加索引
不添加索引的时候，查询会通过dbms的游标从数据表的开始位置一直查询到数据表的结束，即使在中间位置已经查询到该条数据，游标依然会继续往下执行，因为它不确定下面有没有同样的数据，所以查询速度就会变慢。
(2). 添加索引
添加主键索引后，会按照二叉树（BTREE）的算法进行查询，比如这里是十一条数据。
二叉树算法
先是找中间数据，之后分成左右两个区间再分别找中间数，以此类推；如果剩两个数的话，可以向上取整也可以向下取整，但在同一个算法中必须同步，向上取整只能向上取整，不能混淆。
使用二叉树算法后，查询的时候就会拿索引跟这二叉树进行比较。
例如：现在我们想得到4的位置，那么先拿4跟6进行比较，4<6,进入左边的树杈位置进行查询，这样就减少了1/2的数据。接下来继续比较，4>3,进入右边一个树杈，又减去一半的数据，以此类推，获取到索引为4的数据，我们只查询了三次。但是如果没有加索引的话，它会从头查到尾，会查11次，这样很浪费性能。
二叉树算法查询的数量是log2N（log以2为底的N）
二叉树查询10次的话，那么没有索引就会查询2的十次方=1024.以此类推，可以看出二叉树算法很牛，可以解决庞大数据的查询。
注意：每一个索引对应的数据在磁盘中都有磁道来进行存储。
2.2 主键索引
当一张表，把某个列设为主键，那个列就是主键索引1
Create table aa(id intunsigned primary key auto_increment,namevarchar(32) not null defaul ‘’); )
这里的id就是主键索引
番外篇：
- Primarykey ：设置主键
- auto_increment:设置自增
- defaul：设置默认值
Unsigned：
整型的每一种都分为：无符号（unsigned）和有符号（signed）两种类型（float和double总是带符号的），在默认情况下声明的整型变量都是有符号的类型（char有点特别），如果需声明无符号类型的话就需要在类型前加上。unsigned。无符号版本和有符号版本的区别就是无符号类型能保存2倍于有符号类型的正整数数据，比如16位系统中一个short能存储的数据的范围为-32768~32767，而unsigned能存储的数据范围则是0~65535。由于在计算机中,整数是以补码形式存放的。根据最高位的不同，如果是1,有符号数的话就是负数；如果是无符号数,则都解释为正数。另外，unsigned若省略后一个关键字，大多数编译器都会认为是unsigned int。
如果你在创建表的时候，没有设置主键，可以后续添加
alter table 表名 addprimary key（列名）;
举例1
2
3create table bbb (id int , name varchar(32)not null default ‘’);
alter table bbb add primary key (id);
2.3 创建普通索引
一般来说，普通索引的创建，是先创建表，然后在创建普通索引
比如1
2
3
4
5create table ccc(
id int unsigned,
name varchar(32)
)
create index 索引名 on 表 (列1,列名2);
2.4 创建全文索引
文索引，主要是针对文件、文本的检索, 比如文章, 全文索引针对MyISAM有用.（非关系型数据库就不能使用）
创建1
2
3
4
5
6
7
8
9
10
11CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=myisam charset utf8;
添加数据1
2
3
4
5
6
7
8
9
10
11
12
13INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
如何使用全文索引
错误用法1
select * from articles where body like ‘%mysql%’;【不会使用到全文索引】
证明1
explain select * from articles where body like ‘%mysql%’
explain的作用：用于分析sql 语句的执行及数据库索引的使用
正确的用法1
select * from articles wherematch(title,body) against(‘database’); 【可以】
上面是把title和body创建成全文索引的，against里面是需要查询的关键字。
☞ 说明:
在mysql中fulltext 索引只针对 myisam生效
mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中 文
使用方法是 match(字段名..) against(‘关键字’)
全文索引一个叫 停止词, 因为在一个文本中，创建索引是一个无穷大
的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.
举例：
查询第一列中与database匹配度为65%左右，第二列0%，以此类推
全文索引不可能把每个词都建成索引，加入全文有上个个字符，那么组合起来的索引量就特别大，所以全文索引只会针对一些生僻的词
像a这样的，在很多地方都会出现，所以就不会建立索引，因此叫做停止词。
##2.5 创建唯一索引
当表的某列被指定为unique约束时，这列就是一个唯一索引1
create table ddd(id int primary keyauto_increment , name varchar(32) unique);
这时, name 列就是一个唯一索引.
unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.
主键字段，不能为NULL,也不能重复.
在创建表后，再去创建唯一索引1
2
3create table eee(id int primary keyauto_increment, name varchar(32));
create unique index 索引名 on 表名 (列名);
2.6 查询索引
desc 表名 ——查看表的详情
Show index(es) from 表名 ——查看表的详情（一般用于查询索引使用，后面加\G可以显示的更清晰）
Show keys from 表名\G
2.7 删除索引
1 | alter table 表名 drop index 索引名; |
如果是删除主键索引1
alter table 表名 drop primary key
2.8 修改索引
先删除，再重新创建
如何查看索引使用的情况1
show status like‘Handler_read%’;
大家注意1
2
3handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。
总结
- 较频繁的作为查询字段的应该创建索引。
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。
- 更新非常频繁的字段不适合创建索引。
- 不会出现在Where子句中字段不该创建索引。
使用索引注意事项
- 创建索引，索引文件就会变大，占用磁盘空间
- 使用索引，会降低dml（增删改）操作效率，因为每次改变数据，那么二叉树就重新排列索引位置。
- 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。 explain select * from dept where
dname like ‘%aaa’\G 不能使用索引， 即，在like查询时，关键的‘关键字’ , 最前面，不能使用 % 或者
_这样的字符.， 如果一定要前面有变化的值，则考虑使用 全文索引->sphinx. - 如果条件中有or，即使其中有条件带索引也不会使用。 换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字
1 | select * from dept where dname=’xxx’ or loc=’xx’ ordeptno=45 |
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’),
也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来如果mysql估计使用全表扫描要比使用索引快，则不使用索引。
explain 可以帮助我们在不真正执行某个sql语句时，就执行mysql怎样执行，这样利用我们去分析sql指令.
