数据库的优化（index）

最新推荐文章于 2023-07-05 20:00:00 发布

米二

最新推荐文章于 2023-07-05 20:00:00 发布

阅读量1k

点赞数

分类专栏：数据库文章标签：数据库 index

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

index的使用：

1.最好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

2.在建有索引的字段上尽量不要使用函数进行操作，尽量不要在数据库中做运算。
3.避免负向查询和%前缀模糊查询，like 'xxx%'百分号后缀查询可以在此字段上使用索引。
4.不要在生产环境程序中使用select * from 的形式查询数据。只查询需要使用的列。
5.查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。
6.对查询列使用函数用不到索引。
7.避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。
8.所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。
9.联表查询的时候，记得把小结果集放在前面，遵循小结构及驱动大结果集的原则,这条很重要！
10.开启慢查询，定期用explain优化慢查询中的SQL语句。
11.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
12.IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。
13.索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

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

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

什么情况下应不建或少建索引?

1.表记录太少如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如： select * from zl_sybm where sydw_bh=’5401’（对sydw_bh建立索引不会产生性能优化）经常插入、删除、修改的表对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。 2.数据重复且分布平均的表字段假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。经常和主字段一块查询但主字段索引值比较多的表字段如 gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。对千万级MySQL数据库建立索引的事项及提高性能的手段.

MySQL索引类型包括：

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 (username(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