mysql 加索引_MySql调优

MySql的架构介绍

安装

在linux下安装

设置开启自启动

chkconfig mysql on

ntsysv 查看自启动的服务有哪些 【*】代表开机自启动

安装位置

路径	解释	备注
/var/lib/mysql/	mysql数据库文件的存放路径
/usr/share/mysql	配置文件目录	mysql.server命令及配置文件
/etc/init.d/mysql	启停相关的脚本

修改配置文件

5.5版本  /usr/share/mysql/my-huge/cnf  

拷贝 cp  /usr/share/mysql/my-huge/cnf   /etc/my.cnf

高版本  /usr/share/mysql/my-defalt.cnf   /etc/my.cnf

修改默认字符集编码

 1.查看字符集show variables like ‘%char%’2.在 /etc/my.cnf中[client]设置   default-charater-set=utf8   [mysqld]中添加   charater_set_server=utf8   charater_set_client=utf8   charater_server=utf8_general_ci     [mysql]     default-charater-set=utf8

MySql配置文件

二进制日志文件：log-bin(主从复制)  window在my.ini  linux在 /etc/my.cnf

错误日志log-error:默认是关闭的(mysqlerr.err文件)

查询日志：默认关闭

数据文件：1.frm文件 -> 存放表结构

 2.myd文件 -> 存放表数据

     3.myi文件  -> 存放索引

MySql逻辑校验

连接层

服务层:执行SQL服务

存储引擎:MyISAM(不支持主外键,不支持事务、表锁、只缓存索引)、innoDB(支持行锁和事务)

储存层

存储引擎的介绍

 查看存储引擎:

 方式一：show engines;方式二：show variables like '%storage_engine%';

SQL查询速率慢的原因

 1.性能下降sql慢2.执行时间长3.等待时间长原因：1.查询语句写的烂2.索引失效->{单值，复合}     例子：创建单值索引 create index idx_index_name from user(name);         创建复合索引 create index idx_index_name_email from user(name,eamil);          单值索引: select * from user where name =''  查询一个条件   复合索引: select * from user where name ='' and email=''  3.查询关联太多join(设计缺陷或者不得已的需求)  4.服务器调优及各个参数设置(缓冲、线程等)

SQL的执行顺序

  FROM table>            //先是一个笛卡尔积 ON  <join condition>   <join type> JOIN table> WHERE <where condition> SELECT DISTINCT  ORDER BY  LIMIT 

 案例：A表,B表1.A表和B表的交集 select * from A inner join B on A.key =B.key2.A表和B表的共有加A表独有 select * from A left join B on A.key=B.key3.A表和B表的共有加B表的独有加 select * from A right join B on A.key =B.key4.A表独有 select * from A left join B on A.key = B.key where B.key is null5.B表独有 select * from A right join B on A.key = B.key where A.key is null6.A表和B表的全部 select * from A full outer join B on A.key = B.key7.A表独有和B表独有的 select * from A full outer join B on A.key =B.key where A.key is null and B.key is null  //注意 mysql不支持 6 7的写法,使用union

索引(重要)

• 索引是帮助MySQL高效获取数据的数据结构

• 索引：排好序的快速查找的数据结构

• 影响的范围：查找和排序(where 和 order by)

优势：

  1.提高了索引的小效率,降低了数据库的IO成本

  2.通过索引对数据进行排序,降低数据排序的成本,降低cpu的消耗

劣势：

  1.索引实际也是一张表,需要占用空间的。

  2.索引提高了查找速度,但是会降低更新表的速度。因为更新表的时候,不仅要保存数据还要添加索引的字段

索引的分类

• 单值索引：一个索引值包含单个列,一个表可以有多个单列索引

• 唯一索引：索引列的值必须唯一,但允许有空值

• 复合索引：一个索引包含多个列

 --创建索引create [unique] index indexName on mytable(columnName(length))alter mytable ADD [unique] index [indexName] on(columnName(length))--删除索引drop index[indexName] on mytable--查看索引show index from table_name--使用Alter来创建索引的四种方式ALTER TABLE tab_name ADD PRIMARY KEY (column_list);添加一个主键,意味着索引值是唯一,不为NUlLALTER TABLE ADD UNIQUE INDEX_NAME(column_list);这条语句创建的索引值唯一(除NUll外)ALTER TABLE tab_name ADD INDEX index_name(column_list);添加普通索引,索引值可出现多次ALTER TABLE tab_name ADD FULLTEXT index_name(column_list);添加全文索引

MySql索引结构

• Btree索引

哪些情况创建索引

• 主键自动建立唯一索引

• 频繁查找的条件

• 查询中与其他边建立关联的字段,外键关系建立索引

• Where条件用不到的字段不要创建索引

• 单值/组合索引的选择问题？(在高并发下建议创建组合索引)

• 查询字段中排序字段,排序字段若通过索引去访问将大大提高排序效率

• 查询中统计或则分组字段(疑问)

哪些情况不适合创建索引

• 表记录太少

• 频繁更新的字段不适合创建索引

• 数据重复并且分布平均的字段(假设一个字段只有True和false,分布概率大概都为50%)

性能分析

1.Mysql Query Optimizer SQL的服务

2.MySQL常见的瓶颈：

  2-1：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候

  2-2：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

  2-3：服务器硬件的性能瓶颈

Explain

执行计划：使用Explain关键字可以模拟优化器执行SQL查询语句,从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

Explain能做什么？

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

使用Explain包含的信息

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
		DERIVED衍生

• id

 id select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序三种情况1.id相同,执行顺序由上至下2.如果是子查询,id的序号会递增,id值越大优先越高3.id如果相同,可以认为是一组,从上往下顺序执行；在所有组中,id值越大,优先级越高,越先执行

• select_type

 1.simple  简单的select查询,查询中不包含子查询或者union2.primary  查找中若包含任何复杂的子部分,最外层被标记为primary3.subquery  在select或者where中包含了子查询4.derived  在from列表中包含的子查询被标记为DERIVED(衍生)Mysql会递归执行这些子查询,把结果放在临时表里5.union  若第二个select出现在union之后,则被标记为union。若union包含在from子句的子查询中，外层的select被标   记为derived  6.union result  从union获得的select(两个union合并的结果)

• type

 1.All2.index  index、和all都是读全表,但是index是从索引中读取的,all是从硬盘读取的  (select id from A id为主键索引)3.range  检索给定范围的行,使用一个索引来选择行(比如between、in)4.ref  非唯一性的所有扫描，返回匹配某个单独值的所有行。  (一个公司的所有程序员)5.eq_ref  唯一性索引扫描，对于每个索引键，表中只有一条记录来与之匹配。常见与主键或者唯一索引扫描  (一个公司的CEO)6.const  表示通过索引一次就找到了,const用于比较primary或者unique索引。7.system  表只有一条记录(等于系统表),特例一般不会出现8.null访问类型的排序,从最好到最差(一般达到range或者ref就可以)system>const>eq_ref>ref>range>index>all

• possible_keys 和 key

 possible_keys  显示可能应用到这张表的索引,可能一个或者多个               查询涉及到的字段上若存在索引,则该索引将被列出，但不一定被实际使用key:实际使用的索引,如果为null，则没有使用索引    查询中若使用了覆盖索引,则该索引仅出现在key列表中    覆盖索引：select查询的字段和我创建的复合索引的个数和顺序一一一致

• key_len

 标识索引中使用的字节数，可通过该列计算查询中使用的索引的长度。越短越好

• ref

 显示一个索引的哪一列被使用了,如果可能得话是一个常数

• rows

 根据表统计的信息及索引选用情况,大致估算出找到所需的记录需要读取的行数(越少越好)

• Extra

 包含不适合在其他列中显示但十分重要的额外信息Using_filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取.MySql无法利用索引完成排序操作称为"文件排序"(最好不要出现)Using_temporary(新建了一个临时表):使用了临时表保存中间结果,Mysql在对查询结果排序时使用临时表。常见于order by和分组查询group by  (不能有)Using_index:表示相应的select操作中使用了覆盖索引(Covering index)，避免访问表的数据行,效率不错            如果同时出现using where,标识索引被用来执行索引键值的查找            如果没有同时出现using where，表明索引引用来读取数据而非执行查找动作Using where:使用到了whereUsing join buffer:使用了连接缓存;imposible where:where子句中的值总是false，不能用来执行任何元素例子：select * from a where a.name='A' and a.name ='B' name不可能同时是'A'和'B'select tables optimized away:(了解)dinstinct:(了解)

 例子：select * from tbl_emp;使用执行计划explain select * from tbl_emp;

索引优化

• 优化规则

• 尽可能减少Join语句中的NestedLoop的循环总次数 “永远用小结果驱动大的结果集”

• 优先优化NestedLoop的内层循环

• 保证join语句中被驱动表上join条件字段已经被索引

• 当无法保证被驱动表的join字段被索引且内存资源充足的情况下,不要太联系JoinBuffer的设置

索引失效

 最佳左前缀法则：查询从索引的最左列开始并且不跳过索引的列例子：假设我建立了一个复合索引p_index_nameAgePosselect * from A where a.age = '1' and a.pos ='2' //索引失效,带头大哥没了select * from A where a.name ='1' and a.pos ='2' //索引部分失效,因为age没有使用直接跳到pos

 不在索引列上做任何操作(计算,函数,自(动or手动)类型转换 比如是字符串'2000' 你写成2000),会导致索引失效而转向全表扫描例如：select * from A where name ='A' 正常select * from A where left(name,1) = 'A' //索引失效

 存储引擎不能使用范围条件右边的列select * from A where name ='A' and age>25 and post='manager'; //age后面的索引失效

 在mysql中使用(!=或者<>)的时候会导致索引失效select * from A where name ='a' //索引正常select * from A where name <> 'a' //索引失效

 is null 或者 is not nulll;会导致索引失效

 like以通配符开头('%abc..')mysql索引会失效select * from A where name like '%a%' //索引失效select * from A where name like 'a%'  //索引不失效如果不想索引失效,使用覆盖索引

 字符串不加引号会导致索引失效

 少用or，用它来连接会导致索引失效

查询的截取分析

查询的截取分析

• 观察,至少跑一天,查看生产的慢查询Sql情况(慢查询的开启并捕获)

• explain (开启慢查询日志,设置阈值，比如超过5s就是慢查询,并江它抓取出来) explain+慢查询分析

• show profile

查询优化

• in 和 exists的区别

 // 当B表的数据集小于A表的数据集时,用in优于exists//等价于 select id from B//      select * from A where A.id=B.idselect * from A where id in (select id from B)//当A表的数据集小于B表的时候,使用exists优于in//等价于 select * from A//      select * from B where B.id = A.idselect * from A where exists (select 1 from B where B.id =A.id)

• order by

 尽量使用Index方式排序,避免使用FileSort排序双路排序：4.1之前读取磁盘两次;单路排序：小总结：

• group by

 group by 实际是先排序后分组当无法使用到索引列的时候,增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置where高于having,能在where写的就不要在having中写

慢查询日志

默认mysql数据没有开启慢查询日志,需要我们手动来设置这个参数

 默认:show variables like %show_query_log%;开启：set global show_query_log =1;//只针对本次生效,如果重启那么恢复默认永久生效(不建议)：修改my.cnf文件,[mysql]下增加或修改参数show_query_log=1show_query_log_file =/var/lib/mysql/atguigu-slow.loglong_query_time =3 设置几秒算慢查询long_output=file

• 怎么样的sql会记录到慢查询日志呢？

 这个是由long_query_time控制的,默认情况下long_query_time的时间为10秒命令:SHOW VARIABLES LIKE 'long_query_time' 可以使用命令修改,也可以在my.cnf参数里面修改手动设置时间set global long_query_time =3设置完成之后,重新断开连接或者使用SHOW global VARIABLES LIKE 'long_query_time'  查看设置的时间手动设置睡眠时间select sleep(4);查询当前数据库中有多少条慢查询记录select global status like %slow_queries%;日志分析工具 mysqldumpslow例如：mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log # 查找这个日志下出现最多的十条数据

批量数据脚本

 创建函数,假如报错:This function has none of deterministic...# 由于开启过慢查询日志,因为我们开启了bin-log,我们就必须为我们的function指定一个参数show variables like 'log_bin_trust_function_creators';set global log_bin_trust_function_creators=1;# 这样我们添加了参数以后,如果重启mysql,上述参数又会消失，永久方法windows下的my.cnf[mysql]加上log_bin_trust_function_creators=1;linux下 /etc/my.cnf下的[mysql]加上log_bin_trust_function_creators=1;

• 创建函数和存储过程

Show Profile

默认处于关闭状态，并且保存最近15次的记录

 show variables like 'profiling'设置开启set profile=on;运行sqlshow profiles;诊断cpushow profiles cpu,block io for query 3(指定查询几号id);如果出现以下这些说明sql有问题converting heap to myisam 查询结果太大,内存都不够用 开始往磁盘上搬createing to temp table 创建临时表copying to tmp table on disk 把内存中的临时复制到磁盘,危险！！！locked

全局查询日志

 配置启动：# 开启general_log=1#记录日志的文件路径general_log_file = /path/logfile#输出格式log_output=file编码启动set global general_log = 1 ;set global log_output='TABLE' //以表的形式输出//查验select * from mysql.general_log;

Mysql锁机制

InnoDB和myIsam的区别：InnoDB支持事务,并且支持行级锁

查看当前事务的隔离级别：show variables like ‘tx_islocation’

表锁

• 读锁:共享锁 多个读操作可以同时进行而不会相互影响

• 写锁:排他锁。当前写操作没有完成前,它会阻断其他写锁和读锁

• 表锁(偏读)：发生锁冲突的概率高,并发度最低(myisam引擎)

 手动锁表lock table 表名称 read(write),表名称2 read(wirte)，其他lock table A read,book write 给A表读锁,book表写锁释放表unlock tables;【查看表有没被锁】show open tables

 lock table mylock write;//其他session读取这张表的时候 阻塞。

 [如何分析表锁定]show status like '%tables%' 会产生两个变量table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1；table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,没等待一次锁值加1)，此值越高则说明存在较严重的表级锁争用的情况。总结：读锁会阻塞写但是不会阻塞读,而写锁会把读和写阻塞。

行锁

• 行锁(写锁)InnoDB：开销大,加锁慢；会出现死锁，锁定粒度小，发生锁冲突的概率小，并发度也最高。

 [如何分析行锁]show status like '%innodb_row_lock%' 查看指定变量innodb_row_lock_current_wait:当前正在等待锁定的数量innodb_row_lock_time_avg:等待的平均时长innodb_row_lock_waits:系统启动后到现在总共等待的次数

 测试流程：开启两个session session1 和 session2设置手动提交事务 set autocommit = 0 当session1和session2更新同一条数据时候,如果session1没有提交那么session2等待当session1和session2更新不同的数据时候,互不影响

问题

• 无索引行锁升级为表锁

 例子：假设我给A表一个单值索引b,且b的类型为varchar。此时我开启两个session,事务设置为手动提交session1:  update A set name='1' where b=123 //b的类型是varchar但是我给它写成了number,导致索引失效,那么session2更新其他数据时,无法更新。此时升级为表锁

• 间隙锁的危害

 什么叫做间隙锁？当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时，innoDB会给复合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做"间隙锁"间隙锁的危害？因为Query执行的过程中通过范围查找的话,他会锁定整个范围内所有的索引键值，即使这个键值不存在。

面试题

• 如何锁定一行

 三步第一步：begin第二步: select xxx for update第三步：commit