12067-mysql优化一1206

网站优化-MySQL优化（一）目录网站优化-MySQL优化（一）1一、mysql优化概述1二、存储引擎的选择11、存储引擎介绍1（1）什么是存储引擎？1（2）存储引擎的理解：1（3）存储引擎所处的位置：2（4）常用存储引擎：22、innodb存储引擎3（1）存储格式：3（2）数据是按照主键顺序存储。6（3）并发处理：63、MyISAM存储引擎7（1）存储方式：7（2）数据的存储顺序为插入顺序。8（3）并发性84、memory8三、查找需要优化语句91、慢查询日志9方式一、临时启动慢查询记录日志9方式二：通过修改配置文件，添加如下语句132、精确记录查询时间14四、索引讲解151、索引的基本介绍152、索引的类型：153、索引管理语法16（1）创建索引：16（2）删除索引17（3）查看索引18（4）创建索引注意事项：19五、执行计划19六、索引的数据结构211、myisam的存储引擎索引结构：212、innodb的存储引擎的索引结构22七、索引覆盖23八、索引的使用原则251、列独立252、like查询263、OR运算都具有索引274、复合索引使用284、mysql 智能选择295、优化group by语句。29九、mysql中锁机制311、应用场合：312、mysql里面的锁的几种形式313、表锁的演示，324、行锁的演示345、通过php代码来实现锁机制。35一、mysql优化概述前面我们讲页面静态化，memcache是通过减少对mysql 操作来提升访问速度，但是一个网站总是要操作数据库，我们如何提升对mysql的操作速度。方针： 存储层：数据表”存储引擎”选取、字段类型选取、逆范式(3范式) 设计层：索引、分区/分表、存储过程，sql语句的优化 架构层：分布式部署(集群)(读写分离)，需要增加硬件 sql语句层：结果一样的情况下，要选择效率高、速度快、节省资源的sql语句执行二、存储引擎的选择1、存储引擎介绍熟悉的存储引擎：Myisam、innodb memory（1）什么是存储引擎？数据表存储数据的一种格式。数据存储在不同的格式里边，该格式体现的特性也是不一样的。例如innodb存储引擎的特性有支持事务、支持行级锁，mysiam支持的特性有压缩机制等。MySQL中的数据是通过各种不同的技术(格式)存储在文件（或者内存）中的。技术和本身的特性就称为存储引擎。（2）存储引擎的理解：现实生活中，楼房、平房就是具体存储人的存储引擎，楼房、平房有自己独特的技术特性例如楼房有楼梯、电梯、平房可以自己打井喝水等。（3）存储引擎所处的位置：存储引擎，处于MySql服务器的最底层，直接存储数据，导致上层的操作，依赖于存储引擎的选择。客户端-网络连接层-业务逻辑层（编译，优化，执行SQL）-存储引擎层查看当前mysql支持的存储引擎列表：show engines（4）常用存储引擎： Myisam：表锁，全文索引 Innodb：行(记录)锁，事务（回滚），外键 Memory：内存存储引擎，速度快、数据容易丢失2、innodb存储引擎=5.5 版本中默认的存储引擎，MySql推荐使用的存储引擎。提供事务，行级锁定，存储引擎。事务安全型存储引擎，更加注重数据的完整性和安全性。（1）存储格式：innodb存储引擎 每个数据表有单独的“结构文件” *.frm数据，索引集中存储，存储于同一个表空间文件中ibdata1。ibdata1就是InnoDB表的共享存储空间，默认innodb所有表的数据都在一个ibdata1里。创建innodb表后，存在文件如下：create table t1(id int,name varchar(32) engine innodb charset utf8;.frm表结构文件。innodb表空间文件：存储innodb的数据和索引。ibdata1默认，所有的 innodb表的数据和索引在同一个表空间文件中，通过配置可以达到每个innodb的表对应一个表空间文件。show variables like innodb_file_per_table%开启该配置：set global innodb_file_per_table=1;创建一个innodbd的表进行测试使用。查看表对应的文件自己独立的“数据/索引”文件系统配置参数innodb_file_per_table后期无论发生任何变化，t2都有自己独立的“数据/索引”文件。注意：相比较之下，使用独占表空间的效率以及性能会更高一点。注意：innodb数据表不能直接进行文件的复制/粘贴进行备份还原，可以使用如下指令：备份数据库的指令 mysqldump -uroot -p密码 数据库名字 f:/文件名称.sql 备份 mysql -uroot -p密码 数据库 f:/文件名称.sql 还原（2）数据是按照主键顺序存储。该innodb数据表，数据的写入顺序 与 存储的顺序不一致，需要按照主键的顺序把记录摆放到对应的位置上去，速度比Myisam的要稍慢。create table t3(id int primary key auto_increment,name varchar(32) not null)engine innodb charset utf8;insert into t3 values(223,刘备),(12,张飞),(162,张聊),(1892,网飞);给innodb数据表写入4条记录信息(主键id值顺序不同)插入时做排序工作，效率低。（3）并发处理：擅长处理并发的。行级锁定(row-level locking)，实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性，也支持表级锁定，innodb根据操作选择。锁机制：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能相互影响），通过加锁来处理。操作方面：读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是所有人都只可以读，只有释放锁之后才可以写。写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征，只有锁表的客户可以操作（读写）这个表，其他客户读都不能读。办公室开会锁上门。锁定粒度（范围）表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam和innodb都支持。行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb支持 3、MyISAM存储引擎bin/mysqld.exe -safe-mode -slow-query-log 注意：先把mysql关闭后，再执行以上指令启动。进入cmd开始启动；bin/mysqld.exe -safe-mode -slow-query-log通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。（2）在默认情况下，慢查询日志是存储到data目录下面的。根据配置文件里面的配置，找到data的存储路径。（3）可以通过命令查看慢查询日志的时间show variables like long_query_time;修改慢查询日志时间：set long_query_time=0.5;（4）测试查询：查看慢查询日志 benchmark(count,expr)函数可以测试执行count次expr操作需要的时间。select benchmark(10000,90000000*4)（5）一般情况下，一个sql语句执行比较慢，原因是没有索引添加索引之前,索引文件大小如下；没有添加索引之前查询时间如下：添加索引之后：alter table emp add index(empno)添加索引后，索引文件变大。添加索引之后需要的时间；结论：创建完索引后，索引文件会变大，添加索引会明显的提高查询速度。方式二：通过修改配置文件，添加如下语句在配置文件中指定：（1）开启（2）时间界限log-slow-queries=d:/slow-log慢查询日志文件存储的路径，当前是把慢查询日志存储到d:盘下面，文件名为slow-loglong_query_time=1指定慢查询的时间，默认是10秒，我们自定义成1或0.05秒，也就是说当一个sql语句的执行速度超过1秒时，会把该语句添加到慢查询日志里面，通过配置文件是永远的开启慢查询日志2、精确记录查询时间使用mysql提供profile机制完成。profile记录每次执行的sql语句的具体时间，精确时间到小数点8位（1）开启profile机制:set profiling = 1;执行需要分析的sql语句（自动记录该sql的时间）（2）查看记录sql语句的执行时间：show profiles;注意：不需要分析时，最好将其关闭。set profiling=0;四、索引讲解1、索引的基本介绍利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的作用：是用于快速定位实际数据位置的一种机制。例如：字典的 检索写字楼 导航索引在mysql中，是独立于数据的一种特殊的数据结构。索引一定有顺序（排好序的快速查找结构），记录则不一定。画图说明测试添加索引前后，对比执行时间。2、索引的类型：4种类型：主键索引，唯一索引，普通索引，全文索引。无论任何类型，都是通过建立关键字与位置的对应的关系来实现的。以上类型的差异，是对关键字的要求不同。关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）普通索引：对关键字没有要求。唯一索引：要求关键字不能重复，同时增加唯一约束。主键索引：要求关键字不能重复，也不能为NULL。同时增加主键约束。全文索引：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。关键词的来源：可以是某个字段，也可以是某些字段（复合索引）。如果一个索引通过在多个字段上提取的关键字，称之为复合索引。比如：alter table emp add index (field1,field2)3、索引管理语法（1）创建索引：建表时：注意：索引可以起名字，但是主索引不能起名字，因为一个表仅仅可以有一个主键索引，其他索引可以出现多个。名字可以省略，mysql会默认生成，通常使用字段名来充当。show create table index1;更新表结构alter table index2 add unique key (name),add index(age),add fulltext index(intro),add index(name,age);注意：第一点：如果表中存在数据，数据符合唯一或主键约束才可能创建成功。第二点：auto_increment属性，依赖于一个KEY（主键或唯一）。（2）删除索引修改表结构时完成：删除主键索引：alter table table_name drop primary key 主键索引的删除，如果没有auto_increment 属性则使用 alter table 表名 drop primary key如果在删除主键索引时，该字段中有auto_increment则先去掉该属性再删除。去除主键的auto_inrement属性：alter table 表名 modify id int unsigned not null comment 主键如有主键中有auto_incrments属性时，删除主键索引，则报如图提示。去除主键的auto_inrement属性：alter table index1 modify id int unsigned;删除普通索引，唯一索引，全文索引，复合索引；语法：alter table 表名 drop index 索引的名称；如果没有指定索引名，则可以通过查看索引的方法得到索引名（一般依赖于索引字段的名字）（3）查看索引show indexes from table_name;show index from table_nameGshow create table table_nameshow keys from table_namedesc table_name（4）创建索引注意事项：第一：较频繁的作为查询条件字段应该创建索引select * from emp where empno = 1第二：唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件select * from emp where sex = 男第三：更新非常频繁的字段不适合创建索引select * from emp where logincount = 1第四：不会出现在WHERE子句中字段不该创建索 五、执行计划主要用于分析sql语句的执行情况（并不执行sql语句）得到sql语句是否使用了索引，使用了哪些索引。 语法：explain sql语句G 或 desc sql语句G 添加索引进行查看删除索引时，在看执行计划六、索引的数据结构查看索引的类型：show keys from 表名;1、myisam的存储引擎索引结构：索引的节点中存储的是数据的物理地址（磁道和扇区）在查找数据时，查找到索引后，根据索引节点中记录的物理地址，查找到具体的数据内容。2、innodb的存储引擎的索引结构innodb的主键索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用（非主键索引的节点存储是主键的id）比如要通过nam创建的索引，查询name=采臣的,先根据name建立的索引，找出该条记录的主键id，再根据主键的id通过主键索引找出该条记录。innodb的主索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用myisam中, 主索引和非主索引,都指向物理行(磁盘位置).注意: innodb来说, 1: 主键索引 既存储索引值,又在叶子中存储行的数据2: 如果没有主键, 则会Unique key做主键 3: 如果没有unique,则系统生成一个内部的rowid做主键.4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”聚簇索引 优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)劣势: 如果碰到不规则数据插入时,造成频繁的页分裂（索引的节点移动）.七、索引覆盖索引覆盖是指：如果查询的列恰好是索引的一部分，那么查询只需要在索引区上进行，不需要到数据区再找数据，这种查询速度非常快，称为“索引覆盖”索引覆盖就是，我要在书里 查找一个内容，由于目录写的很详细，我在目录中就获取到了，不需要再翻到该页查看。先分析一下使用场景，为什么使用索引覆盖，有什么好处？准备两张表来测试使用；案例1，比如给id建立了主键索引，使用id查询数据。在user表里面，给name字段添加索引，查询name,就用到了索引覆盖。索引使用的场合如下，在没有条件时，直接查询建立索引的字段时，案例2：比如给id和name 建立了复合索引，使用name作为条件查询。没有创建索引时，给id和name创建复合索引。典型情况如下：学生表：共30个字段。Select id, name, height,gender from student where name=XXX;建立索引：Alter table student add index (name);Alter table student add index (name, id, height, gender, class_id);select name, id, height, gender, class_id from student负面影响，增加了索引的尺寸。保证该索引的使用率尽可能高，索引覆盖才有意义。八、索引的使用原则1、列独立只有参与条件表达式的字段独立在关系运算符的一侧，该字段才可能使用到索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。2、like查询在使用like(模糊匹配)的时候，在左边没有通配符的情况下，才可以使用索引。在mysql里，以%开头的like查询，用不到索引。注意，如果select的字段正好就是索引，那么会用到索引即索引覆盖。如果该表改为innodb引擎，因为非主键索引中存储的是id,select的字段是id因此用到了索引覆盖。比如如下把表改成了 innodb的引擎，对name建立了索引，如下查询，就用到了索引覆盖。如果是innodb的表，可以如上使用。注意以下查询会用到索引；3、OR运算都具有索引如果出现OR(或者)运算，要求所有参与运算的字段都存在索引，才会使用到索引。如下：name有索引，classid没有索引如下：id有索引，name有索引4、复合索引使用当前查询环境；最左原则：对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。 注意：在多列索引里面，如果有多个查询条件，要想查询效率比较高，比如如下建立的索引，index(a,b,c,d) 要保证最左边的列用到索引。4、mysql 智能选择如果mysql认为，全表扫描不会慢于使用索引，则mysql会选择放弃索引，直接使用全表扫描。一般当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。5、优化group by语句。 默认情况下， mysql对所有的group by col1,col2进行排序。这与在查询中指定order by col1,col2类型，如果查询中包括group by 但用户想要避免排序结果的消耗，则可以使用order by null禁止排序。 输出班级的id,classid根据classid分组，自动根据classid进行 了排序，select classid,sum(age) from user group by classid;如果不想根据classid排序，则可以在后面使用order by nulllselect classid,sum(age) from user group by classid order by null;通过分析语句发现：九、mysql中锁机制1、应用场合： 比如有如下操作：（1）从数据库中取出id的值(比如id=100) （2）把这个值-1(id=100-1)（3）再把该值存回到数据库(id=99)假如id=1有两个进程（用户）同时操作，使用锁机制来完成，同时操作时，只有一个进程获得锁，其他进程就等待，进程1添加锁id =100id=100-1id=99释放锁进程2wating等待 wating等待wating等待id =100id=100-1id=992、mysql里面的锁的几种形式锁机制： 当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能相互影响），通过加锁来处理。操作方面： 读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是所有人都只可以读，只有释放锁之后才可以写。写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征，只有锁表的客户可以操作（读写）这个表，其他客户读都不能读。办公室开会锁上门。锁定粒度（范围） 表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam引擎的表支持表锁，行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb引擎的表支持行锁与表锁。3、表锁的演示，建立测试表，并添加测试数据：create table user( id int primary key auto_increment, name varchar(32) not null default , age tinyint unsigned not null default 0, email varchar(32) not null default , classid int not null default 1)engine myisam charset utf8;insert into user values(null,xiaogang,12,,4),(null,xiaohong,13,,2),(null,xiaolong,31,,2),(null,xiaofeng,22,,3),(null,xiaogui,42,,3);添加锁的语法： lock table table_name1 read|write,table_name2 read|write释放锁的语法：unlock tables（1）添加读锁语法：lock table_name read|write;另外一个用户登录后，不能执行修改操作，可以执行查询操作。注意：添加读锁后，自己和其他的进程（用户）只能对该表查询操作，自己也不能执行修改操作。注意：添加表的锁定后，针对锁表的用户，只能操作锁定的表，不能操作没有锁定的表。执行释放锁，释放锁之后，另外的一个进程，可以执行修改的操作了。（2）添加写锁，只有锁表的客户可以操作（读写）这个表，其他客户读都不能读。查看另外的一个用户，是否可以操作该表，其他的用户，读都不能读，4、行锁的演示innodb存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件(主键)检索数据，innodb才会使用行级锁，否则，innodb使用表锁。