数据库MySQL优化和索引创建

一、选取合适的字段属性

综从的原则：在建表的时候，为了获取最高的性能，设置合理的字段属性，当然可以将表中的字段的宽度设定尽可能小

• 能使用char字段不必要使用varchar
• 根据业务需求能使用mediumin属性不使用bigin来定义整数型字段
• 对于某些字段：比如“省份”和“性别”，我们尽量定义成ENUM类型，因为，ENUM类型被当做数值型数据来处理，而数值型处理起来比文本类型要快的多

二、在可能的情况下，尽量把字段设置成NOTNULL

这样将来在执行查询的时候，数据库不会去比较NULL值

三、SQL语句的编写技巧

1、使用连接（JION）来代替子查询（Sub-Queries）

虽然子查询一次性完车很多逻辑上需要多个步骤才能完成的SQL操作，同时也避免事务和表锁死，并且写法简单。但是，使用JION合适的情况下，会更有效率，因为MySQL不需要在内存中建立临时表完成这个逻辑上需要俩个步骤完成的查询工作。

2、使用联合（UNION）来代替手动创建的临时表

MySQL在4.0开始可以支持使用多个select查询合并到一个查询中。在客户端查询回话时，临时表会自动删除。从而保持数据库的整齐高效。那么使用union创建查询时候，我们使用UNION连接起来，注意的是所有select语句中字段的数目相同。

四、事务

为了保持多数据库查询的一直和整体性，SQL上可以使用事务保持数据一致性：以BEGIN开始，以COMMIT关键字结束；这样ROLLBACK命令可以将数据库恢复到BEGIN开始之前的状态；
例如：
BIGIN；INSERT salesinfo SET CustomerID=14;UPDATE inventory SET Quantity=11 WHERE item=“book”;COMMIT;
事务的另一个重要的作用是：当有多个用户使用相同的数据源时，它可以锁定数据库方法为用户提供一种安全的访问方式，这样可以保证用户的操作不会被其他用户的干扰

五、锁定表

由于事务的存在，多个用户访问时会造成其他用户请求暂停或者等待直到该事务结束。如果是少量用户，不会是太大的问题，但成千上万的话，就会影响性能。
所以有些情况下就需要使用锁定表的方式获取更高的性能
例如：
LOCK TABLE inventory WRITE SELECT Quatity FORM inventory WHERE Item=‘book’
…
UPDATE inventory SET Quantity=11 WHERE Item=‘book’；UNLOCKTABLES
利用select语句取出初始化数据，通过一些计算更新到表中。使用WHERE结合LOCkTABLE语句，可以保证UNLOCKTABLE执行命令之前不会有用户对inventory进行插入更新删除操作

六、使用外键

锁定表的方式可以维护数据的完整性，但不能保证数据的关联性。这时候可以使用外键
具有有待更新了解：。。。。

七、使用索引

它可以令数据库检索特定和行不会全局查找而影响性能

1、普通索引建立

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

删除索引的语法：

DROP INDEX [indexName] ON mytable;

2、唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

3、主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

4、组合索引

• 为了形象地对比单列索引和组合索引，为表添加多个字段
  CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
  为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

• ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

• 如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

• usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引==“最左前缀”==的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username=“admin” AND city=“郑州”

SELECT * FROM mytable WHREE username=“admin”

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city=“郑州”

SELECT * FROM mytable WHREE city=“郑州”

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。

如果我们创建了 (area, age,salary)的复合索引，那么其实相当于创建了 (area,age,salary)、(area,age)、(area)三个索引，这被称为“最佳左前缀”特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

5、使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

Mysql查询每次只能使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：

select * from users where adddate<‘2007-01-01’;

◆不使用NOT IN和<>操作

以上，就对其中MySQL索引类型进行了介绍。