Mysql优化篇

11. January 2017 DataBase 0
show processlist 查看当前在进行的线程 show status 了解各SQL执行频率 使用explain分析低效SQL执行计划 type=ALL 全表扫描,效率最低 type=index 索引全扫描 type=range 索引范围扫描 type=ref 非唯一索引扫描或唯一索引前缀扫描 type=eq_ref 使用唯一索引,相当于使用多表链接中primary_key 或则unique index type=const/system 单表中最多有一个匹配行,查询速度很迅速 type=null 不用访问表或索引,直接查出结果,适合常量或变量的select 通过show profile; 分析SQL执行效率 select @@have_profiling 查看当前版本数据库是否支持profile select @@profiling 查看是否开启了profiling set profiling=1 设置profiling 开启 show profiles; 查看当前SQL运行事件和Query ID show profile for query ID 查看指定语句具体查询时间 load data infile 路径 into  table 表明   //大批量插入数据 ...

Mysql char,varchar的区别

10. January 2017 DataBase 0
char是一种固定长度的类型,varchar则是一种可变长度的类型 char(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足.(在检索操作中那些填补出来的空格字符将被去掉) 在varchar(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节. 对于MyISAM表,尽量使用Char,对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此,它的缺点就是占用磁盘空间; 对于InnoDB表,因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分(所有数据行共用一个表头部分,这个标头部分存放着指向各有关数据列的指针),所以使用char类型不见得会比使用varchar类型好。事实上,因为char类型通常要比varchar类型占用更多的空间,所以从减少空间占用量和减少磁盘i/o的角度,使用varchar类型反而更有利.。 如此看来varchar比char聪明多了,那char有用武之地吗?还是很不少优势的。 一,存储很短的信息,比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个byte用于存储信息长度,本来打算节约存储的现在得不偿失。 二,固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根据长度的特性就消失了,而且还要占个长度信息。 三,十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。 创建表的时候并不是varchar的值越大越好,系统会把varchar的定长读入内存,然后。。。 来源于 http://www.jb51.net/article/23575.htm

Mysql触发器

10. January 2017 DataBase 0
delimiter // 定义触发器 create trigger Yn_trigger after insert on user for each row begin update data set name=’赛亚人’ where id=1; end // show triggers查看触发器 insert into user(id,name,age) values(101,’ss’,100); 插入数据引发触发器 select * from user; 查看结果 drop trigger in_trigger删除触发器 before insert, after insert before update after update 执行更新操作,会执行before insert,before update,after update 插入新纪录只会执行 before insert,after insert

Mysql特有语法

10. January 2017 DataBase 0
insert into lijian.user(id,name,age) values(2,’lijian’,99) on duplicate key update age=101 若此记录不存在,则插入数据,如果存在,则更新age。

Mysql 事件调度器

10. January 2017 DataBase 0
第一步查看调度器状态 show variables like \'%scheduler%\' 若为开启,执行set global event_scheduler=1开启 第二步 创建事件调度器 create event myevent on schedule every 10 second do insert into lijian.user(name,age) values(‘111’,222); 最后 show events 查看事件调度器 select * from user; 查看执行结果 drop event lijian.test1 删除事件调度器 alter event myevent disable; 禁用事件调度器

Mysql 存储过程 mysql workbench

10. January 2017 DataBase 0
show procedure status; 查看存储过程 DELIMITER // 定义存储过程 create procedure film4(id2 int) begin select * from user where id=id2; end // call film4(2); //调用存储过程 drop film4 删除存储过程 show create procedure film4  查看存储过程的创建信息 delimiter // 存储过程使用变量 create procedure actor_insert() begin set @x=1; insert into user(id,name,age) value (11,’lijian’,99); set @x=2; insert into user(id,name,age) value (12,’lisa’,99); set @x=3; end // ...

Mysql数据库select结果集的横向和纵向拼接

06. January 2017 DataBase 0
第一种:纵向拼接 纵向拼接使用 union, union all 全部拼接,不去重。只使用union会过滤重复数据,例如一下测试语句 select name from phpcmsv9.data union all select username from phpcmsv9.v9_admin 效果图如下 第二种:横向拼接 如果想把多列毫无相关的数据横向凭借起来确实有点困难,换种思维:不同表中的数据也许没有任何关系,一般情况下每个表都有自增ID这列吧,那么这就可以了,其实就是一个外链接,硬找出关系来,如果数据没有自增ID,其他字段完全不相同,只能造一个自增列了(这个问题曾困扰了我许久)。测试语句如下: select phpcmsv9.data.name,phpcmsv9.v9_admin.username from phpcmsv9.data left join phpcmsv9.v9_admin  on phpcmsv9.data.id=phpcmsv9.v9_admin.userid

使用Navicat从Excel导大量数据到Mysql数据库

07. December 2016 DataBase 0
使用navicat向mysql导大量数据时候,容易出现 ERROR 2006 (HY000): MySQL server has gone away 打开数据库数据,看到数据库不完整,只导入了一部分,解决方法如下: 第一:打开excel,在列的上部单击右键,选择设置单元格格式,然后选择文本,如下图,保存好以后再看一下是否修改好了。 第二步 打开数据库配置文件my.ini,把max_allowed_packet 这一项的数据修改大点,修改成了max_allowed_packet = 1024M,然后保存,打开mysql控制台,使用 show VARIABLES like \'%max_allowed_packet%\'; 查看配置是否生效 第三步 保证存储大量数据的字段类型,大数据最好使用 MEDIUMTEXT,或者LONGTEXT。

Mysql数据库索引重要性

04. November 2016 DataBase 0
第一 排他锁方面 InnoDB存储引擎在不使用索引时对全部记录加锁。 如果在没有索引的情况下,对一行数据加上排他锁,另一session对其它行请求派他锁时候会出现锁等待,原因就在于InnoDB对所有记录都加了锁。

PHP高效更新数据库记录

31. October 2016 DataBase 0
大批量插入或更新数据库记录,使用循环费时费力,数据库压力会更大,插入可以在values后拼接字符串,大批量数据更新同样也可以拼接字符串,只是方式稍微有点不同,直接看代码。 <?php include_once ‘../admin/include.php’; $info=$_SESSION[‘info’]; $chk22=$_POST[‘chk22’]; $chk11=$_POST[‘chk11’]; // echo json_encode($chk11); $ids = implode(‘,’, array_keys($chk22));//获取要修改的条件列值 $sql = “UPDATE state SET result = CASE flag “;//根据flag条件列更新result值 foreach ($chk22 as $id => $ordinal) { $sql .= sprintf(“WHEN %d THEN %d “, $id, $ordinal);//根据条件列循环拼接字符串 } $sql .= “END WHERE flag IN ($ids) and info=’$info\'”;//设置条件列取值范围和更新条件 if(mysql_query($sql,$conn)){ echo “11”;//传回自定义状态数据 }else{ echo ...