mysql常见的系统瓶颈

 

  1. 磁盘搜索。需要花时间从磁盘上找到一个数据,用在现代磁盘的平均时间通常小于10ms,因此理论上我们能够每秒大约搜索1000次。这个时间在新磁盘上提高不大并且很难为一个表进行优化。优化它的方法是将数据分布在多个磁盘上。
  2. 磁盘读/写。当磁盘放入正确位置后,我们需要从中读取数据。对于现代的磁盘,一个磁盘至少传输10-20Mb/s的吞吐。这比搜索要容易优化,因为你能从多个磁盘并行地读。
  3. CPU周期。我们将数据读入内存后,需要对它进行处理以获得我们需要的结果。表相对于内存较小是最常见的限制因素。但是对于小表,速度通常不成问题。
  4. 内存带宽。当CPU需要的数据超出CPU缓存时,主缓存带宽就成为内存的一个瓶颈

MySQL中如何获取下一条插入的自增ID 或 刚刚插入的id

MySQL中如何获取下一条插入的自增ID 或 刚刚插入的id

要立刻获得刚插入记录的id,该表必须有一个为一个AUTO_INCREMENT列生成的ID

$sql_addinfo ” insert into contact_info_group set contact_group_id=”,contact_id=” ” ;
mysql_query ( $sql_addinfo ) ;
echo mysql_insert_id () ;

mysql_insert_id – 取得上一步 INSERT 操作产生的 ID

int mysql_insert_id ( [resource link_identifier])
mysql_insert_id() 返回给定的 link_identifier 中上一步 INSERT 查询中产生的 AUTO_INCREMENT 的 ID 号。如果没有指定 link_identifier,则使用上一个打开的连接。

如果上一查询没有产生 AUTO_INCREMENT 的值,则 mysql_insert_id() 返回 0。如果需要保存该值以后使用,要确保在产生了值的查询之后立即调用 mysql_insert_id()。

注: MySQL 中的 SQL 函数 LAST_INSERT_ID() 总是保存着最新产生的 AUTO_INCREMENT 值,并且不会在查询语句之间被重置。
警告

mysql_insert_id() 将 MySQL 内部的 C API 函数 mysql_insert_id() 的返回值转换成 long(PHP 中命名为 int)。如果 AUTO_INCREMENT 的列的类型是 BIGINT,则 mysql_insert_id() 返回的值将不正确。可以在 SQL 查询中用 MySQL 内部的 SQL 函数 LAST_INSERT_ID() 来替代。

具体使用如下语句:mysql_query(”SELECT LAST_INSERT_ID()”);

PS: 在插入数据时候可以这样写insert into xxx (col_a, col_b) values (11,22);select LAST_INSERT_ID()这样不就可以取到最近生成的ID了吗.

常常需要在编程的时候获取当前表的下一个自增的ID值,因为表可能被删除之后造成最大的Max(ID) + 1并不是下一条ID的值,所以需要使用其他办法。通过查询Mysql的文档,会发现Mysql里面有一个函数叫做getLastInsertID,用来获取最后插入的表的ID值,但是对获取下一条插入的自增ID并没有太多帮助。

1. 方法1
插入一条测试记录,然后删除掉,获取ID。不再赘述。

2. 方法2
利用Mysql自带的一个函数语句 “SHOW TABLE STATUS” ,可以获取当前数据库里的所有表的属性等信息,通常用来做数据库的优化和维护。如果需要指定某一张表的话,可以通过两种方法指定。
一种是网上给出来的:
show table status like ‘TableName
另外就是常见的where语句
show table status where Name =’ TableName

得到的结果集里面有一列AUTO_INCREMENT,是一个Int值变量,获取之后就是下一个要插入的ID了。

//查看表“测试”的状态

$r = mysql_query(“show table status where name=’ceshi'”);
$r_h = mysql_fetch_assoc($r);
//获得下一条信息的id号
var_dump($r_h[‘Auto_increment’]);

windows下安装mysql5.7,没有配置向导问题

安装.msi文件到最后一步并没有像以前一样在finish界面的上方有个勾选的地方(是否进行配置向导),而是finish后就没有然后了,什么提示都没有!!!

解决方法如下:

  1. mysql的目录下也没有出现my.ini文件而是出现了一个,default.ini文件,这是默认的配置文件,这里将default.ini复制一份到改名为my.ini并将如下代码写入
    basedir=D:/wamp/mysql
    datadir=D:/wamp/mysql/data
    port=3306
  2. 建立data空文件夹
  3. 执行mysqld –initialize-insecure –user=mysql,官方文档说明,使用-initialize生成随机密码,使用-initialize-insecure是生成空密码。默认帐号root,后面的-user=mysql不更改
  4. 执行完mysqld –initialize-insecure –user=mysql命令后没有任何提示,直接执行net start mysql,成功开启

 

解决windows下的mysql匿名登陆无法使用mysql数据库的问题

转自:http://www.it165.net/database/html/201402/5328.html

我在windows下安装了mysql,但是不用密码就能登进去,而root明明是有密码的,我用select user()命令查看当前登录用户是root。

查看数据库,只能看到自带的两个数据库(information_schema和test),mysql这个数据库都看不到。如果运行use mysql,就会出现 Access denied for user ”@’localhost’ to database ‘mysql’, 这说明这是匿名登录的,几乎什么权限都没有。即使用mysql -u root登录也是提示这个。

网上搜了很多资料,大多数是教的怎么在linux下解决这个问题。

我综合了大多数的方法,自以为找到了解决的办法:

1.首先,网上的资料都提到了需要关闭mysqld这个服务来屏蔽权限检查,我查看了任务管理器,发现的确有一个叫mysqld.exe的程序在运行:

直接关闭这个进程

我查阅了帮助手册,”MySQL服务器也就是mysqld,是在MySQL安装中负责大部分工作的主程序。服务器随附了几个相关脚本,当你安装MySQL时它们可以执行设置操作,或者是帮助你启动和停止服务器的帮助程序。“ 我估计它相当于mysql的守护进程,不停止它就无法设置启动脚本。

2.网上都提到了

mysqld-nt –skip-grant-tables

mysqld_safe –skip-grant-tables

safe_mysqld –skip-grant-tables 等关闭权限检查的方法,但是我在CMD命令提示符下输入都提示:“mysqld_safe”不是内部或外部命令,也不是可运行的程序或批处理文件。

于是我查看mysql安装目录下的bin文件夹里有哪些相关的文件:

(1)mysqld.exe (2)mysqld_multi.pl

我查看了帮助手册,“在Unix和NetWare中推荐使用mysqld_safe来启动mysqld服务器。mysqld_safe增加了一些安全特性,例如当出现错误时重启服务器并向错误日志文件写入运行时间信息。为了保持同旧版本MySQL的向后兼容性,MySQL二进制分发版仍然包括safe_mysqld作为mysqld_safe的符号链接。默认情况下,mysqld_safe尝试启动可执行mysqld-max(如果存在),否则启动mysqld。”

我估计mysqld_safe是mysqld.exe文件里的一个启动选项。所以我尝试了这样输入:

先跳转到mysql的bin目录下,再输入mysqld –skip-grant-tables。这时候有可能一直没有反应的样子,不会生成新的一行输入。

实际上它已经停止了权限检查,我们可以进行其他操作了。

3.重新打开一个cmd命令提示符窗口,进入到mysql里面,然后输入use mysql,就会发现成功了!:

看到没?上面最后一行提示的是Database changed!!

如果你输入show tables就会看到这个数据库下有个表叫user,记录了登陆的账户名和密码。

所以你可以这样修改root账户的密码:

UPDATE user SET Password=PASSWORD(‘123′) WHERE user=’root’;

这时你使用MySql Command Line Client登陆的话,就会要求输入密码123了!你已经不是匿名登陆,所以可以进行各种操作了!!!

远程连接mysql错误1130

错误提示:1130,ERROR 1130: Host xxx.xxx.xxx.xxx  is not allowed to connect to this MySQL server

解决办法:

登录线上数据库:mysql -u ***  -h localhost -p

找到mysql数据库的user表:

 

 

 

将”localhost”修改问”%”

还没完,还需要重新刷新下MySQL的系统权相关表

mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表,否则会出现拒绝访问

(还有一种方法,就是重新启动mysql服务器,来使新设置生效。­)

 

inux命令行下导出导入.sql文件

一、导出数据库用mysqldump命令(注意mysql的安装路径,即此命令的路径):
1、导出数据和表结构:
mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/   mysqldump -uroot -p abc > abc.sql
敲回车后会提示输入密码

2、只导出表结构
mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/   mysqldump -uroot -p -d abc > abc.sql

注:/usr/local/mysql/bin/  —>  mysql的data目录
二、导入数据库
1、首先建空数据库
mysql>create database abc;

2、导入数据库
方法一:
(1)选择数据库
mysql>use abc;
(2)设置数据库编码
mysql>set names utf8;
(3)导入数据(注意sql文件的路径)
mysql>source /home/abc/abc.sql;
方法二:
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
#mysql -uabc_f -p abc < abc.sql

建议使用第二种方法导入。

注意:有命令行模式,有sql命令

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索引类型进行了介绍。

解决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 “数据库连接成功”;

?>

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