MySQL索引类型一览 让MySQL高效运行起来

转自:http://www.php100.com/html/webkaifa/database/Mysql/2010/0409/4279.html

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );   我们随机向里面插入了10000条记录,其中有一条:5555, admin。

在查找username=”admin”的记录 SELECT * FROM mytable WHERE username=’admin’;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

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 (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=”郑州”

(5)建立索引的时机

到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city=’郑州’ 此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

SELECT * FROM mytable WHERE username like’admin%’ 而下句就不会使用:

SELECT * FROM mytable WHEREt Name like’%admin’ 因此,在使用LIKE时应注意以上的区别。

(6)索引的不足之处

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

◆虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

(7)使用索引的注意事项

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

◆索引不会包含有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索引类型进行了介绍。

vi和vim编辑器的一些常规操作

vi有3个模式:插入模式、命令模式、低行模式。

插入模式:在此模式下可以输入字符,按ESC将回到命令模式。

命令模式:可以移动光标、删除字符等。

低行模式:可以保存文件、退出vi、设置vi、查找等功能(低行模式也可以看作是命令模式里的)。

①:打开文件、保存、关闭文件(vi命令模式下使用)

vi filename     //打开filename文件

:w              //保存文件

:w cszhi.com    //保存至cszhi.com文件

:q              //退出编辑器,如果文件已修改请使用下面的命令

:q!            //退出编辑器,且不保存

:wq          //退出编辑器,且保存文件

②:插入文本或行(vi命令模式下使用,执行下面命令后将进入插入模式,按ESC键可退出插入模式)

a       //在当前光标位置的右边添加文本

i       //在当前光标位置的左边添加文本

A       //在当前行的末尾位置添加文本

I       //在当前行的开始处添加文本(非空字符的行首)

O       //在当前行的上面新建一行

o       //在当前行的下面新建一行

R       //替换(覆盖)当前光标位置及后面的若干文本

J       //合并光标所在行及下一行为一行(依然在命令模式)

③:移动光标(vi命令模式下使用)

1、使用上下左右方向键

2、命令模式下:

h   向左、j   向下、k   向上、l  向右。

空格键向右、Backspace向左、Enter移动到下一行首、-移动到上一行首。

④:删除、恢复字符或行(vi命令模式下使用)

x         //删除当前字符

nx        //删除从光标开始的n个字符

dd        //删除当前行

ndd       //向下删除当前行在内的n行

u         //撤销上一步操作

U         //撤销对当前行的所有操作

⑤:搜索(vi命令模式下使用)

/cszhi    //向光标下搜索cszhi字符串

?cszhi    //向光标上搜索cszhi字符串

n         //向下搜索前一个搜素动作

N         //向上搜索前一个搜索动作

⑥:跳至指定行(vi命令模式下使用)

n+//向下跳n行

n-//向上跳n行

nG        //跳到行号为n的行

G         //跳至文件的底部

⑦:设置行号(vi命令模式下使用)

:set  nu     //显示行号

:set nonu    //取消显示行号

⑧:复制、粘贴(vi命令模式下使用)

yy    //将当前行复制到缓存区,也可以用 “ayy 复制,”a 为缓冲区,a也可以替换为a到z         的任意字母,可以完成多个复制任务

nyy   //将当前行向下n行复制到缓冲区,也可以用 “anyy 复制,”a 为缓冲区,a也可以替         换为a到z的任意字母,可以完成多个复制任务

yw    //复制从光标开始到词尾的字符

nyw   //复制从光标开始的n个单词

y^    //复制从光标到行首的内容

y$    //复制从光标到行尾的内容

p     //粘贴剪切板里的内容在光标后,如果使用了前面的自定义缓冲区,建议使用”ap 进         行粘贴

P     //粘贴剪切板里的内容在光标前,如果使用了前面的自定义缓冲区,建议使用”aP 进         行粘贴

⑨:替换(vi命令模式下使用)

:s/old/new//用new替换行中首次出现的old

:s/old/new/g        //用new替换行中所有的old

:n,m s/old/new/g    //用new替换从n到m行里所有的old

:%s/old/new/g       //用new替换当前文件里所有的old

⑩:编辑其他文件

:e otherfilename    //编辑文件名为otherfilename的文件

⑪:修改文件格式

:set fileformat=unix   //将文件修改为unix格式,如win下面的文本文件在linux下会出现^M

 

解决ERROR 1146 (42S02): Table ‘xxx.xxx’ doesn’t exist问题的

由于网站需要搬家到另一个数据库上,导入数据库的时候因为没有安装phpmyadmin这个软件,所以就直接把数据库的data文件复制过来了。

出现的问题:

show databases; 没问题有我刚刚导入的数据库,但是有的当你对某个表执行sql语句就会报1146错误 ,在网上查询了很长时间,比较多的说法就是“存储引擎”有问题。

查找问题:

将原服务器上的数据库用phpmyadmin导下来,到本地用navicat for mysql 导入的时候到最后也出错了。

回头看了下原服务器上数据表的“存储引擎”,的确有几个是innoDB的,于是就手动全部都改成了myISAM,结果再次导入就没有问题了,完全成功。

注:这里说的只是我这里出现的问题^_^,仅供参考

 

运用MySQLI扩展库的预处理功能

想想看。假如我们要插入很多1000个用户.怎么做?for循环,还是mysqli处理多条sql, no,这些处理很慢的,php里面有很多操作mysql数据库的函数,无非是把sql语句传递给mysql数据库,真正处理sql语句的是mysql,mysql数据库是要编译sql语句迚行执行的,上面这两种操作会对相同的sql语句迚行多次编译,有这必要吗?程序员总是很聪明的,于是有了mysqli预处理技术mysqli还能防止sql注入.

预处理的意思是先提交sql语句到mysql服务端,执行预编译,客户端执行sql语句时,只需上传输入参数即可,这点和存储过程有点相似.摘抄网上滴.^_^

DML语句的预处理:

DQL语句的预处理:

对于预处理的多次执行语句,可以使用for等循环语句代替.DQL语句的预处理在DML语句的预处理的基础上增加了输出和释放.

我自己觉得,预处理就是给数据库一个模版,对数据库说,你就按照这样给我处理,等下我传个参数给你,你把它放在?的位置,进行处理.处理完,模版我就不用了,你就销毁吧.

 

mysqli扩展应用

mysqli扩展库中有三个类库

mysqli()连接库

mysqli_result();结果集库

还有预处理库

mysqli使用构造方法连接

$mysqli=new mysqli(“localhost”,”root”,””,”数据库”);

var_dump($mysqli);

无论返回正确错误,都会返回一个对象

那么使用mysqli_connect_errno()函数去判断连接是否成功(连接的时候用)

使用mysqli_connect_error() 给出出错后的信息(连接的时候用)

这两个函数库都是mysqli扩展库里的

当执行sql语句时,如果出错可以使用

errno属性去得到错误号

error属性去得到错误信息

$mysqli->errno

$mysqli->error

如果 执行 插入insert 删除delete 更新update

就可以 使用 affected_rows属性去获取 改变的条数

返回数字
====================================================

<?php

$mysqli=@new mysqli(“localhost”,”root”,””,”brophp”);
 if(mysqli_connect_errno())
 {
  echo “数据库连接错误”.mysqli_connect_error();
    $mysqi=null;
    exit;
 }

 $sql=”insert into bro_user (id1,name,age,sex,email) values (”,’2222222′,’52’,’jdj’,’2222222′)”;
 $result=$mysqli->query($sql);
 if(!$result)
 {
  echo $mysqli->errno.”|”.$mysqli->error.”<br>”;//这是判断sql语句是否有误
  exit;
 }

if($mysqli->affected_rows > 0)//这是判断 增/删/改 影响的行数 ,可以知道sql语句是否执行成功

//例如 delete from bro_user where id <10 sql语句执行成功,但是数据表中id<10的数据不存在,那么

//$mysql->affecter_rows 依然返回 0 ,因为没对数据表有任何改变,自然行数就没发生变化  

{

echo “有行数被影响”;

}

echo “最好自动增长的ID:”.$mysqli->insert_id;//使用insert语句才有效,而且查询的字段设置了自动增长的属性

echo $mysql->affected_rows; 
 echo “数据库连接成功”;

?>

=======================================================

开启mysqli扩展

从PHP 5.0开始,不仅可以使用早期的mysql数据库扩展函数,而且还可以使用新的扩展mysqli技术实现与MySQL数据库的信息交流。PHP的 mysqli扩展被封装到一个类中,它是一种面向对象的技术,只能在PHP 5和MySQL 4.1(或更高的版本)环境中使用,(i)表示改进,其执行速度更快。使用mysqli扩展和传统的过程化方法相比更方便也更高效。利用mysqli扩展 技术不仅可以调用MySQL的存储过程、处理MySQL事务,而且还可以使访问数据库工作变得更加稳定。

喜欢过程化编程的用户也不用担心,mysqli也有过程式的方式,提供了一个传统的函数式接口,只不过开始贯以mysqli的前缀,其他都差不多。 如果mysqli以过程式的方式操作的话,有些函数必须指定资源,比如说 mysqli_query(资源标识,SQL语句)。并且资源标识的参数是放在前面的,而mysql_query(SQL语句,’可选’)的资源标识是放 在后面的,并且可以不指定,它默认是上一个打开的连接或资源。本书将重点介绍他的面向对象的用法,如果更喜欢以过程化方式编写程序,使用前面介绍的 mysql功能扩展模块就可以了。这里,希望读者使用面向对象的方式编程,这样可以编写出更容易阅读和理解的代码。

启用mysqli扩展模块

与mysql功能扩展模块类似,mysqli接口也不是PHP的一个集成组件,如果想使用这个功能扩展模块,需要显示配置PHP才能使用此扩展。在 不同平台下的配置有所不同,如果在Linux平台中启用mysqli扩展,必须在编译PHP时加上–with-mysqli选项。如果在Windows 平台中启用mysqli扩展,需要通过一个DLL文件提供相应的扩展。不管使用的是哪一个操作系统平台,都必须在php.ini文件里启用这个扩展,以确 保PHP能够找到所有必要的DLL。可以在php.ini文件中找到下面一行,取消前面的注释,如果没有找到就添加这样一行:

  1. extension=php_mysqli.dll              //在php.ini文件中启用这一行

关于配置PHP的更多信息,请参见本书第2章的环境安装。另外,可以在PHP脚本文件中,调用phpinfo()函数检查PHP版本是否支持mysqli接口。如果找到如图13-1所示的结果,则所用的PHP版本中支持mysqli接口。

图13-1  使用phpinfo()函数检查mysqli扩展模块的支持

mysqli 扩展不仅提供了mysql模块的所有功能,也相应地增加了一些新特性。mysqli扩展模块包括mysqli、mysqli_result和 mysqli_stmt三个类,通过这三个类的搭配使用,就可以连接MySQL数据库服务器和选择数据库、查询和获取数据,以及使用预处理语句简化了重复 执行的查询语句。

四大算法

EXPLAIN

使用explain语句去查看分析结果,如
explain select * from test1 where id=1;
会出现:
id  selecttype  table  type possible_keys  key key_len  ref rows  extra各列

其中,

type=const表示通过索引一次就找到了,
key=primary的话,表示使用了主键 
type=all,表示为全表扫描,
key=null表示没用到索引;
type=ref,因为这时认为是多个匹配行,在联合查询中,一般为REF

2 MYSQL中的组合索引
假设表有id,key1,key2,key3,把三者形成一个组合索引,则
如:
  where key1=….
  where key1=1 and key2=2
  where key1=3 and key3=3 and key2=2
根据最左原则,这些都是可以使用索引的哦

  from test where key1=1 order by key3
用explain分析的话,只用到了normal_key索引,但只对where子句起作用,而后面的order by需要排序

3 使用慢查询分析:
在my.ini中:
long_query_time=1
log-slow-queries=d:\mysql5\logs\mysqlslow.log

把超过1秒的记录在慢查询日志中
可以用mysqlsla来分析之。也可以在mysqlreport中,有如
DMS 分别分析了select ,update,insert,delete,replace等所占的百份比

4 MYISAM和INNODB的锁定
myisam中,注意是表锁来的,比如在多个UPDATE操作后,再SELECT时,会发现SELECT操作被锁定了,必须等所有UPDATE操作完毕后,再能SELECT 

innodb的话则不同了,用的是行锁,不存在上面问题。
 
5 MYSQL的事务配置项
innodb_flush_log_at_trx_commit=1
表示事务提交时立即把事务日志写入磁盘,同时数据和索引也更新

innodb_flush_log_at_trx_commit=0
事务提交时,不立即把事务日志写入磁盘,每隔1秒写一次
innodb_flush_log_at_trx_commit=2
事务提交时,立即写入磁盘文件(这里只是写入到内核缓冲区,但不立即刷新到磁盘,而是每隔1秒刷新到盘,同时更新数据和索引 

explain用法
EXPLAIN tbl_name
或:
EXPLAIN [EXTENDED] SELECT select_options

前者可以得出一个表的字段结构等等,后者主要是给出相关的一些索引信息,而今天要讲述的重点是后者。

举例
mysql> explain select * from event;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)

各个属性的含义
id
select查询的序列号

select_type
select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。

table
输出的行所引用的表。

type
联合查询所使用的类型。
type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys
指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。

key
显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。

key_len
显示MySQL决定使用的键长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

ref
显示哪个字段或常数与key一起被使用。

rows
这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。

Extra
如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
如果是where used,就是使用上了where限制。
如果是impossible where 表示用不着where,一般就是没查出来啥。
如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

常见的一些名词解释
Using filesort
MySQL需要额外的一次传递,以找出如何按排序顺序检索行。

Using index
从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

Using temporary
为了解决查询,MySQL需要创建一个临时表来容纳结果。

ref
对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取

ALL
完全没有索引的情况,性能非常地差劲。

index
与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。

SIMPLE
简单SELECT(不使用UNION或子查询)

对于SQL优化开发这需要牢记的原则

比如下边的sql语句:

mysql>SELECT * FROM table WHERE a>’0′ AND b<‘1’ ORDER BY c LIMIT 10;

事实上无论a>’0’还是b<‘1’在前,得到的结果都是一样的,但查询速度大不相同,尤其是对大表进行操作时。

开发者需要牢记的原则是:最先出现的条件,一定是过滤和排除掉最多结果的条件;第二出现的次之;以此类推。因而,表中不同字段的值的分布,对查询速度有很大影响。而ORDER BY中的条件,只与索引有关,与条件顺序无关。

除了条件顺序优化以外,针对固定或者相对固定的SQL查询语句,还可以通过针对索引结构进行优化,进而实现相对高的查询速度。原则是:在大多数情况下,根据WHERE条件的先后顺序和ORDER BY 的排序字段的先后顺序而建立的联合索引,就是与这条SQL语句匹配的最有索引结构。尽管事实的茶品中不能只考虑一条SQL语句,也不能不考虑空间占用太多的索引。

摘自细说php