mysql优化_mysql5.6.44 mysql优化 my.ini - CSDN
  • 一: 分区简介 分区和水平分表功能类似,将一个大表的数据分割到多张小表中去,由于查询不需要全表扫描了,只需要扫描某些分区,所以分区能提高查询速度。 水平分表需要用户预先手动显式创建出多张分表(如tbl_user...
    分享一个朋友的人工智能教程(请以“右键”->"在新标签页中打开连接”的方式访问)。比较通俗易懂,风趣幽默,感兴趣的朋友可以去看看。

    一: 分区简介

    分区是根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言,逻辑上就只有一个表或者一个索引,但实际上这个表可能有N个物理分区对象组成,每个分区都是一个独立的对象,可以独立处理,可以作为表的一部分进行处理。分区对应用来说是完全透明的,不影响应用的业务逻辑。

    分区有利于管理非常大的表,它采用分而治之的逻辑,分区引入了分区键的概念,分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集,让数据根据规则分布在不同的分区中,让一个大对象碧昂城一些小对象。

    MySQL分区即可以对数据进行分区也可以对索引进行分区。

    分区类型

    • range分区:基于一个给定的连续区间范围(区间要求连续并且不能重叠),把数据分配到不同的分区
    • list分区:类似于range分区,区别在于list分区是居于枚举出的值列表分区,range是基于给定的连续区间范围分区
    • hash分区:基于给定的分区个数,把数据分配到不同的分区
    • key分区:类似于hash分区

    注意:无论哪种分区,要么你分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包含分区键,也就是说不能使用主键/唯一键字段之外的其它字段分区。

    ####MySQL分区的有限主要包括以下4个方面:

    1. 和单个磁盘或者文件系统分区相比,可以存储更多数据
    2. 优化查询。在where子句中包含分区条件时,可以只扫描必要的一个或者多个分区来提高查询效率;同时在涉及sum()和count()这类聚合函数的查询时,可以容易的在每个分区上并行处理,最终只需要汇总所有分区得到的结果
    3. 对于已经过期或者不需要保存的数据,可以通过删除与这些数据有关的分区来快速删除数据
    4. 跨多个磁盘来分散数据查询,以获得更大的查询吞吐量

    分区和水平分表功能类似,将一个大表的数据分割到多张小表中去,由于查询不需要全表扫描了,只需要扫描某些分区,所以分区能提高查询速度。

    • 水平分表需要用户预先手动显式创建出多张分表(如tbl_user0, tbl_user1, tbl_user2),在物理上实实在在的创建多张表,通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)来实现分表逻辑。

    • 分区是MySQL的一个插件Plugin功能,将一张大表的数据在数据库底层分成多个分区文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd),和水平分表不同的是分区不需要显式的创建“分表”,数据库会自动创建分区文件的,用户看到的只是一张普通的表,其实是对应的是多个分区,这个是对用户是屏蔽的、透明的,在使用上和使用一张表完全一样,不需要借助任何功能来实现。分区是一种逻辑上的水平分表,在物理层面还是一张表。

    二:数据库文件

    CREATE TABLE `tbl_user_innodb` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `username` varchar(255) DEFAULT NULL,
       `email` varchar(20) DEFAULT NULL,
       `age` tinyint(4) DEFAULT NULL,
       `type` int(11) DEFAULT NULL,
       `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;
     
     CREATE TABLE `tbl_user_myisam` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `username` varchar(255) DEFAULT NULL,
       `email` varchar(20) DEFAULT NULL,
       `age` tinyint(4) DEFAULT NULL,
       `type` int(11) DEFAULT NULL,
       `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`)
     ) ENGINE=myisam AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;
    

    通过show variables like ‘%datadir%’;命令查看mysql的data存放目录,进入所在的数据库目录(如test),不同的引擎数据库文件格式不同

    • myisam
      • .frm : 存储表结构
      • .MYD : 存储表数据
      • .MYI : 存储索引文件
    • innodb: 只有设置成独立表空间才能做成功表分区
      • .frm : 表结构
      • .ibd : 数据 + 索引

    这里写图片描述

    三:插入500W条数据

    CREATE TABLE `tbl_user_no_part` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `username` varchar(255) DEFAULT NULL,
       `email` varchar(20) DEFAULT NULL,
       `age` tinyint(4) DEFAULT NULL,
       `type` int(11) DEFAULT NULL,
       `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    -- 修改mysql默认的结束符号,默认是分号;但是在函数和存储过程中会使用到分号导致解析不正确
    delimiter $$
    
    -- 随机生成一个指定长度的字符串
    create function rand_string(n int) returns varchar(255) 
    begin 
     # 定义三个变量
     declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
     declare return_str varchar(255) default '';
     declare i int default 0;
    
     while i < n do 
       set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
       set i = i + 1;
     end while;
     return return_str;
    end $$
    
    -- 创建插入的存储过程
    create procedure insert_user(in start int(10), in max_num int(10))
    begin
        declare i int default 0; 
        set autocommit = 0;  
        repeat
            set i = i + 1;
            insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
            until i = max_num
        end repeat;
       commit;
    end $$
    
    -- 将命令结束符修改回来
    delimiter ;
    
    -- 调用存储过程,插入500万数据,需要等待一会时间,等待执行完成
    call insert_user(100001,5000000);
    -- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G内存用了8分钟才执行完
    
    select count(*) from tbl_user_no_part;
    

    四:range分区

    MySQL有五种分区类型 range、list、hash、key、子分区,其中最常用的是range和list分区

    -- 查看mysql版本
    select version();
    
    -- 查看分区插件是否激活 partition active
    show plugins;
    
    对于低版本的MySQL,如果InnoDB引擎要想分区成功,需要在my.conf中设置innodb_file_per_table=1 设置成独立表空间
    独立表空间:每张表都有对应的.ibd文件
    innodb_file_per_table=1
    

    range分区:给定一个连续区间的范围值进行分区,某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段,如 日期范围, 连续的数字

    -- 语法
    create table <table> (
    	// 字段
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
    partition by range (分区字段) (
      partition <分区名称> values less than (Value),
      partition <分区名称> values less than (Value),
      ...
      partition <分区名称> values less than maxvalue
    );
    

    range:表示按范围分区
    分区字段:表示要按照哪个字段进行分区,可以是一个字段名,也可以是对某个字段进行表达式运算如year(create_time),使用range最终的值必须是数字
    分区名称: 要保证不同,也可以采用 p0、p1、p2 这样的分区名称,
    less than : 表示小于
    Value : 表示要小于某个具体的值,如 less than (10) 那么分区字段的值小于10的都会被分到这个分区
    maxvalue: 表示一个最大的值

    注意:range 对应的分区键值必须是数字值,可以使用range columns(分区字段) 对非int型做分区,如字符串,对于日期类型的可以使用year()、to_days()、to_seconds()等函数

    create table emp_date(
    	id int not null,
    	separated date not null default '9999-12-31'
    )
    partition by range columns(separated) (
    	partiontion p0 values less than ('1990-01-01'),
    	partiontion p0 values less than ('2001-01-01'),
    	partiontion p0 values less than ('2018-01-01')
    );
    

    分区可以在创建表的时候进行分区,也可以在创建表之后进行分区

    alter table <table> partition by RANGE(id) (
    	PARTITION p0 VALUES LESS THAN (1000000),
        PARTITION p1 VALUES LESS THAN (2000000),
        PARTITION p2 VALUES LESS THAN (3000000),
        PARTITION p3 VALUES LESS THAN (4000000),
        PARTITION p4 VALUES LESS THAN MAXVALUE 
    );
    
    -- 创建分区表
    CREATE TABLE `tbl_user_part` (
       `id` int(11) NOT NULL ,
       `username` varchar(255) DEFAULT NULL,
       `email` varchar(20)     DEFAULT NULL,
       `age` tinyint(4)        DEFAULT NULL,
       `type` int(11)          DEFAULT NULL,
       `create_time` datetime  DEFAULT CURRENT_TIMESTAMP
       -- PRIMARY KEY (`id`,`age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
    PARTITION BY RANGE (age) (
        PARTITION p0 VALUES LESS THAN (20),
        PARTITION p1 VALUES LESS THAN (40),
        PARTITION p2 VALUES LESS THAN (60),
        PARTITION p3 VALUES LESS THAN (80),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );
    
    

    这里写图片描述
    在创建分区的时候经常会遇到这个错误:A PRIMARY KEY must include all columns in the table’s partitioning function。意思是说分区的字段必须是要包含在主键当中。 可以使用PRIMARY KEY (id,xxx)来将多个字段作为主键。在做分区表时,选择分区的依据字段时要谨慎,需要仔细斟酌这个字段拿来做为分区依据是否合适,这个字段加入到主键中做为复合主键是否适合。

    使用range分区时表结构要么没有主键,要么分区字段必须是主键。

    -- 将tbl_user_no_part表中的数据复制到tbl_user_part表中(数据量比较多,可能要等几分钟)
    INSERT INTO tbl_user_part SELECT * FROM tbl_user_no_part;
    
    SELECT count(*) FROM tbl_user_no_part WHERE age > 25 AND age < 30;
    SELECT count(*) FROM tbl_user_part WHERE age > 25 AND age < 30;
    

    这里写图片描述
    从查询结果看,当查询条件中包括分区字段时,分区确实能提高查询效率

    五:list 分区

    设置若干个固定值进行分区,如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的,或者值是有限的,特别是像枚举这样特点的列。list分区使用in表示一些固定的值的列表

    -- 语法
    create table <table> (
    	// 字段
    ) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
    partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (
      partition <分区名称> values IN (Value1,Value2, Value3),
      ...
      partition <分区名称> values IN (Value4, Value5),
    );
    

    columns分区

    在mysql5.5之前range分区和list分区只支持整数分区,可以通过额外的函数运算或者额外的转换从而得到一个整数。columns分区分为 range columns 和 list columns 两种,支持整数(tinyint到bigint, 不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三大数据类型。

    columns分区支持一个或者多个字段作为分区键,不支持表达式作为分区键,这点区别于range 和 list 分区。需要注意的是range columns 分区键的比较是基于元组的比较,也就是基于字段组的比较,这和range分区有差异。

    create talbe rc3 (
    	a int,
    	b int
    )
    partition by range columns(a, b) (
    	partition p01 values less than (0, 10),
    	partition p02 values less than (10, 10),
    	partition p03 values less than (10, 20),
    	partition p04 values less than (10, 35),
    	partition p05 values less than (10, maxvalue),
    	partition p06 values less than (maxvalue, maxvalue),
    );
    
    insert into rc3(a, b) values(1, 10);
    
    select (1, 10) < (10, 10) from dual;
    
    -- 根据结果存放到p02分区上了
    select
    	partition_name,
    	partition_expression,
    	partition_description,
    	table_rows
    from information_schema.partitions
    where table_schema = schema() and table_name = 'rc3';	
    
    

    range columns分区键的比较(元组的比较)其实就是多列排序,先根据a字段排序再根据b字段排序,根据排序结果来分区存放数据,和range单字段的分区排序的规则实际上是一样的

    六:hash分区

    Hash分区主要用来分散热点读,确保数据在预先确定个数的分区中可能的平均分布。对一个表执行Hash分区时,mysql会对分区键应用一个散列函数,以此确定数据应当放在N个分区中的哪个分区。

    mysql支持两种hash分区,

    • 常规hash分区和线性hash分区(linear hash分区),常规hash分区使用的是取模算法,对应一个表达式expr是可以计算出它被保存到哪个分区中,N = MOD(expr, num)
    • 线性hash分区使用的是一个线性的2的幂运算法则。

    对指定的字段(整型字段)进行哈希,将记录平均的分配到分区中,使得所有分区的数据比较平均。 hash分区只需要指定要分区的字段和要分成几个分区,
    expr是一个字段值或者基于某列值云散返回的一个整数,expr可以是mysql中有效的任何函数或者其它表达式,只要它们返回一个即非常熟也非随机数的整数。
    num 表示分区数量

    -- HASH
    create table <table> (
    	// 字段
    ) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
    PARTITION BY HASH(expr)
    PARTITIONS <num>;
    

    常规hash分区方式看上去挺不错的,通过取模的方式来数据尽可能平均分布在每个分区,让每个分区管理的数据都减少,提高查询效率,可是当我们要增加分区时或者合并分区,问题就来了,假设原来是5个常规hash分区,现在需要增加一个常规分区,原来的取模算法是MOD(expr, 5), 根据余数0~4分布在5个分区中,现在新增一个分区后,取模算法变成MOD(expr, 6),根据余数0~6分区在6个分区中,原来5个分区的数据大部分都需要通过重新计算进行重新分区。

    常规hash分区在管理上带来了的代价太大,不适合需要灵活变动分区的需求。为了降低分区管理上的代价,mysql提供了线性hash分区,分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时,mysql能够处理的更加迅速,缺点是:对比常规hash分区,线性hash各个分区之间数据的分布不太均衡。

    -- LINEAR HASH
    create table <table> (
    	// 字段
    ) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
    PARTITION BY LINEAR HASH(expr)
    PARTITIONS <num>;
    

    七:key分区

    按照key进行分区非常类似于按照hash进行分区,只不过hash分区允许使用用户自定义的表达式,而key分区不允许使用用于自定义的表达式,需要使用mysql服务器提供的hash函数,同时hash分区只支持整数分区,而key分区支持使用出blob or text类型外的其他类型的列作为分区键。

    和hash功能一样,不同的是分区的字段可以是非int类型,如字符串、日期等类型。

    可以使用partition by key(expr)子句来创建一个key分区表,expr是零个或者多个字段名的列表。key分区也支持线性分区linear key

    
    partition by key(expr) partitions num;
    
    -- 不指定默认首选主键作为分区键,在没有主键的情况下会选择非空唯一键作为分区键
    partition by key() partitions num;
    
    -- linear key
    partition by linear key(expr)
    
    create table <table> (
    	// 字段
    ) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
    PARTITION BY HASH(分区字段名)
    PARTITIONS <count>;
    

    八:子分区

    子分区(subpartition):是分区表中对每个分区的再次分割,又被称为复合分区,支持对range和list进行子分区,子分区即可以使用hash分区也可以使用key分区。复合分区适用于保存非常大量的数据记录。

    -- 根据年进行分区
    -- 再根据天数分区
    -- 3个range分区(p0,p1,p2)又被进一步分成2个子分区,实际上整个分区被分成了 3 x 2 = 6个分区
    create table ts (
    	id int, 
    	purchased date
    ) 
    partition by range(year(purchased))
    subpartition by hash(to_days(purchased)) subpartitions 2 
    (
    	partition p0 values less than (1990),
    	partition p0 values less than (2000),
    	partition p0 values less than maxvalue
    );
    
    CREATE TABLE IF NOT EXISTS `sub_part` (
      `news_id` int(11) NOT NULL  COMMENT '新闻ID',
      `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',
      `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',
      `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'
    ) ENGINE=INNODB  DEFAULT CHARSET=utf8
    PARTITION BY RANGE(YEAR(create_time))
    SUBPARTITION BY HASH(TO_DAYS(create_time))
    (
    PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1, SUBPARTITION s2),
    PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3, SUBPARTITION s4, SUBPARTITION good),
    PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION tank0, SUBPARTITION tank1, SUBPARTITION tank3)
    );
    

    九:管理分区

    mysql不禁止在分区键值上使用null,分区键可能是一个字段或者一个用户定义的表达式,一般情况下,mysql的分区把null值当做零值或者一个最小值进行处理。range分区中,null值会被当做最小值来处理;list分区中null值必须出现在枚举列表中,否则不被接受;hash/key分区中,null值会被当做领值来处理。

    mysql提供了添加、删除、重定义、合并、拆分分区的命令,这些操作都可以通过alter table 命令来实现

    -- 删除list或者range分区(同时删除分区对应的数据)
    alter table <table> drop partition <分区名称>;
    
    -- 新增分区
    -- range添加新分区
    alter table <table> add partition(partition p4 values less than MAXVALUE);
    
    -- list添加新分区
    alter table <table> add partition(partition p4 values in (25,26,28));
    
    -- hash重新分区
    alter table <table> add partition partitions 4;
    
    -- key重新分区
    alter table <table> add partition partitions 4;
    
    -- 子分区添加新分区,虽然我没有指定子分区,但是系统会给子分区命名的
    alter table <table> add partition(partition p3 values less than MAXVALUE);
    
    -- range重新分区
    ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
    
    -- list重新分区
    ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
    

    分区优点

    1,分区可以分在多个磁盘,存储更大一点

    2,根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了

    3,进行大数据搜索时可以进行并行处理。

    4,跨多个磁盘来分散数据查询,来获得更大的查询吞吐量

    分享一个朋友的人工智能教程(请以“右键”->"在新标签页中打开连接”的方式访问)。比较通俗易懂,风趣幽默,感兴趣的朋友可以去看看。
    展开全文
  • 真·mysql中的SQL优化

    2018-08-01 18:15:42
    1. 优化SQL语句中的一般步骤 通过show status命令了解各种SQL的执行频率 定位执行效率较低的SQL语句 可以通过以下两种方式定位执行效率较低的SQL语句。 通过慢查询日志定位那些执行效率较低的SQL...

    1. 优化SQL语句中的一般步骤


    通过show status命令了解各种SQL的执行频率

    这里写图片描述
    这里写图片描述
    这里写图片描述

    定位执行效率较低的SQL语句

    • 可以通过以下两种方式定位执行效率较低的SQL语句。 通过慢查询日志定位那些执行效率较低的SQL语句,用-log-slow-queries[=file_name]选 项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志 文件。

    • 慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢 询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程, 包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操 作进行优化。

    通过EXPLAIN分析低效的SQL执行计划

    这里写图片描述

    这里写图片描述
    每个列的简单解释如下:

    • select_type:表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接
      或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或
      者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等。

    • table:输出结果集的表。

    • type:表示表的连接类型,性能由好到差的连接类型为 system(表中仅有一行,即
      常量表)、const(单表中最多有一个匹配行,例如 primary key 或者 unique index)、 eq_ref(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接 中使用primarykey或者uniqueindex)、re(f 与eq_ref类似,区别在于不是使用primary key 或者 unique index,而是使用普通的索引)、ref_or_null(与 ref 类似,区别在于 条件中包含对 NULL 的查询)、index_merge(索引合并优化)、unique_subquery(in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似, 区别在于 in 的后面是查询非唯一索引字段的子查询)、range(单表中的范围查询)、 index(对于前面的每一行,都通过查询索引来得到数据)、all(对于前面的每一行,都通过全表扫描来得到数据)。

    • possible_keys:表示查询时,可能使用的索引。

    • key:表示实际使用的索引。

    • key_len:索引字段的长度。

    • rows:扫描行的数量。

    • Extra:执行情况的说明和描述。

    确定问题并且采取相应的优化措施

    这里写图片描述
    这里写图片描述
    这里写图片描述

    *2. 索引问题


    • 索引的存储分类

            MyISAM 存储引擎的表的数据和索引是自动分开存储的,各自是独立的一个文件;InnoDB 存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成。
            MySQL 中索引的存储类型目前只有两种(BTREE 和 HASH),具体和表的存储引擎相关: MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引;MEMORY/HEAP 存储引擎可以支持 HASH 和 BTREE 索引。
            MySQL 目前不支持函数索引,但是能对列的前面某一部分进索引,例如 name 字段,可 以只取 name 的前 4 个字符进行索引,这个特性可以大大缩小索引文件的大小,用户在设计 表结构的时候也可以对文本列根据此特性进行灵活设计。

    • MySQL如何使用索引
      这里写图片描述

            索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作 性能的最佳途径。
            查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那 么只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。

    使用索引

    在 MySQL 中,下列几种情况下有可能使用到索引。
    (1)对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用, 举例说明如下。
    首先按 company_id,moneys 的顺序创建一个复合索引,具体如下:
    然后按 company_id 进行表查询,具体如下:
    这里写图片描述
    这里写图片描述
    可以发现即便 where 条件中不是用的 company_id 与 moneys 的组合条件,索引仍然能 用到,这就是索引的前缀特性。但是如果只按 moneys 条件查询表,那么索引就不会 被用到,具体如下:
    这里写图片描述

    (2)对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会 被使用,来看下面两个执行计划:
    这里写图片描述
    这里写图片描述
    可以发现第一个例子没有使用索引,而第二例子就能够使用索引,区别就在于“%”的位置 不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。 另外,如果如果 like 后面跟的是一个列的名字,那么索引也不会被使用。

    (3)如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%…%’。

    (4)如果列名是索引,使用column_name is null将使用索引。如下例中查询name为null 的记录就用到了索引:
    这里写图片描述

    存在索引但不使用索引

    在下列情况下,虽然存在索引,但是 MySQL 并不会使用相应的索引。
    (1) 如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。例如如果列
    key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:

    SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

    (2) 如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。
    (3) 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引, 那么涉及到的索引都不会被用到,例如:
    这里写图片描述
    这里写图片描述
    从上面可以发现只有 year 列上面有索引,来看如下的执行计划:
    这里写图片描述
    可见虽然在 year 这个列上存在索引 ind_sales_year,但是这个 SQL 语句并没有用到这个索引, 原因就是 or 中有一个条件中的列没有索引。
    (4) 如果不是索引列的第一部分,如下例子:
    这里写图片描述
    可见虽然在 money 上面建有复合索引,但是由于 money 不是索引的第一列,那么在查询中 这个索引也不会被 MySQL 采用。
    5) 如果 like 是以%开始,例如:
    这里写图片描述
    这里写图片描述
    可见虽然在 name 上建有索引,但是由于 where 条件中 like 的值的“%”在第一位了,那么 MySQL 也不会采用这个索引。

    (6) 如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引 起来,否则的话即便这个列上有索引,MySQL 也不会用到的,因为,MySQL 默认把输入的 常量值进行转换以后才进行检索。如下面的例子中 company2 表中的 name 字段是字符型的, 但是 SQL 语句中的条件值 294 是一个数值型值,因此即便在 name 上有索引,MySQL 也不能 正确地用上索引,而是继续进行全表扫描。
    这里写图片描述
    这里写图片描述
            从上面的例子中可以看到,第一个 SQL 语句中把一个数值型常量赋值给了一个字符型的列 name,那么虽然在 name 列上有索引,但是也没有用到;而第二个 SQL 语句就可以正确使 用索引。

    查看索引使用情况

            如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索引值读的 次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。
            Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值 的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描, Handler_read_rnd_next 的值较高,则通常说明表索引不正确或写入的查询没有利用索引,具体如下。
    这里写图片描述
    从上面的例子中可以看出,目前使用的 MySQL 数据库的索引情况并不理想。

    3. 两个简单实用的优化方法


            对于大多数开发人员来说,可能只希望掌握一些简单实用的优化方法,对于更多更复杂的优 化,更倾向于交给专业 DBA 来做。

    定期分析表和检查表

    分析表的语法如下:

    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

            本语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信 息,使得 SQL 能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计 划,执行一次分析表可能会解决问题。在分析期间,使用一个读取锁定对表进行锁定。这对 于 MyISAM, BDB 和 InnoDB 表有作用。对于 MyISAM 表,本语句与使用 myisamchk -a 相当,下例中对表 msgs 做了表分析:
    这里写图片描述
    检查表的语法如下:

    CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

    检查表的作用是检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。 对于 MyISAM 表,关键字统计数据被更新,例如:
    这里写图片描述
    CHECK TABLE 也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在,举例如 下。
    (1)首先我们创建一个视图。
    这里写图片描述
    (2)然后 CHECK 一下该视图,发现没有问题。
    这里写图片描述
    (3)现在删除掉视图依赖的表。
    这里写图片描述
    (4)再来 CHECK 一下刚才的视图,发现报错了。
    这里写图片描述

    定期优化表

    优化表的语法如下:

    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

    如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表(含有 VARCHAR、 BLOB 或 TEXT 列的表)进行了很多更改,则应使用 OPTIMIZE TABLE 命令来进行表优化。这个 命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费,但 OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。
    以下例子显示了优化表 sales 的过程:
    这里写图片描述

    4. 常用的SQL优化


    前面我们介绍了 MySQL 中怎么样通过索引来优化查询。日常开发中,除了使用查询外,我 们还会使用一些其他的常用 SQL,比如 INSERT、GROUP BY 等。

    大批量插入数据

    当用 load 命令导入数据的时候,适当的设置可以提高导入的速度。
    对于 MyISAM 存储引擎的表,可以通过以下方式快速的导入大量的数据。
    这里写图片描述
    DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入 大量的数据到一个非空的 MyISAM 表时,通过设置这两个命令,可以提高导入的效率。对于 导入大量数据到一个空的 MyISAM 表,默认就是先导入数据然后才创建索引的,所以不用进 行设置。
    下面例子中,用 LOAD 语句导入数据耗时 115.12 秒:
    这里写图片描述
    而用 alter table tbl_name disable keys 方式总耗时 6.34 + 12.25 = 18.59 秒,提高了 6 倍多。
    这里写图片描述
    这里写图片描述
    上面是对MyISAM表进行数据导入时的优化措施,对于InnoDB类型的表,这种方式并不 能提高导入数据的效率,可以有以下几种方式提高InnoDB表的导入效率。
    (1)因为 InnoDB 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺 序排列,可以有效地提高导入数据的效率。
    例如,下面文本film_test3.txt是按表film_test4的主键存储的,那么导入的时候共耗时 27.92秒。
    这里写图片描述
    而下面的 film_test4.txt 是没有任何顺序的文本,那么导入的时候共耗时 31.16 秒。
    这里写图片描述
    从上面例子可以看出当被导入的文件按表主键顺序存储的时候比不按主键顺序存储的时候 快 1.12 倍。
    (2)在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行 SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。
    例如,当 UNIQUE_CHECKS=1 时:
    这里写图片描述
    当 SET UNIQUE_CHECKS=0 时:
    这里写图片描述
    可见比 UNIQUE_CHECKS=0 的时候比 SET UNIQUE_CHECKS=1 的时候要快一些。
    (3)如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自
    动提交,导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。 例如,当 AUTOCOMMIT=1 时:
    这里写图片描述
    当 AUTOCOMMIT=0 时:
    这里写图片描述
    对比一下可以知道,当 AUTOCOMMIT=0 时比 AUTOCOMMIT=1 时导入数据要快一些。

    优化 INSERT 语句

    当进行数据 INSERT 的时候,可以考虑采用以下几种优化方式。

    • 如果同时从同一客户插入很多行,尽量使用多个值表的 INSERT 语句,这种方式将大大
      缩减客户端与数据库之间的连接、关闭等消耗,使得效率比分开执行的单个 INSERT 语 句快(在一些情况中几倍)。下面是一次插入多值的一个例子:
    insert into test values(1,2),(1,3),(1,4)...
    • 如果从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。 DELAYED 的含义是让 INSERT 语句马上执行,其实数据都被放在内存的队列中,并没有 真正写入磁盘,这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反,在所有其 他用户对表的读写完后才进行插入;

    • 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);

    • 如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是,这只能对 MyISAM 表使用;

    • 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

    优化 GROUP BY 语句

            默认情况下,MySQL 对所有 GROUP BY col1,col2….的字段进行排序。这与在查询中指定 ORDER BY col1,col2…类似。因此,如果显式包括一个包含相同的列的 ORDER BY 子句,则 对 MySQL 的实际执行性能没有什么影响。
            如果查询包括 GROUP BY 但用户想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序,如下面的例子:
    这里写图片描述
    这里写图片描述
    这里写图片描述
    从上面的例子可以看出第一个 SQL 语句需要进行“filesort”,而第二个 SQL 由于 ORDER BY NULL 不需要进行“filesort”,而 filesort 往往非常耗费时间。

    优化 ORDER BY 语句

    在某些情况中,MySQL 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排序。 WHERE 条件和 ORDER BY 使用相同的索引,并且 ORDER BY 的顺序和索引顺序相同,并且 ORDER BY 的字段都是升序或者都是降序。
    这里写图片描述
    但是在以下几种情况下则不使用索引:
    这里写图片描述

    优化嵌套查询

            MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查 询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很 多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起 来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代。
            在下面的例子中,要从 sales2 表中找到那些在 company2 表中不存在的所有公司的信息:

    这里写图片描述
    这里写图片描述
    果使用连接(JOIN)来完成这个查询工作,速度将会快很多。尤其是当 company2 表 中对 id 建有索引的话,性能将会更好,具体查询如下:
    这里写图片描述
    从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。
    连接(JOIN)之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这 个逻辑上的需要两个步骤的查询工作。

    MySQL 如何优化 OR 条件

            对于含有 OR 的查询子句,如果要利用索引,则 OR 之间的每个条件列都必须用到索引; 如果没有索引,则应该考虑增加索引。
            例如,首先使用 show index 命令查看表 sales2 的索引,可知它有 3 个索引,在 id、year 两个字段上分别有 1 个独立的索引,在 company_id 和 year 字段上有 1 个复合索引。
    这里写图片描述
    这里写图片描述
    这里写图片描述
    然后在两个独立索引上面做 OR 操作,具体如下:
    这里写图片描述
    可以发现查询正确的用到了索引,并且从执行计划的描述中,发现 MySQL 在处理含有 OR 字句的查询时,实际是对 OR 的各个字段分别查询后的结果进行了 UNION。 但是当在建有复合索引的列 company_id 和 moneys 上面做 OR 操作的时候,却不能用到索引, 具体结果如下:
    这里写图片描述这里写图片描述

    使用 SQL 提示

    SQL 提示(SQL HINT)是优化数据库的一个重要手段,简单来说就是在 SQL 语句中加入一些 人为的提示来达到优化操作的目的。
    下面是一个使用 SQL 提示的例子:

       SELECT SQL_BUFFER_RESULTS * FROM...

    这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后,所有表上的锁 定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助,因为 可以尽快释放锁资源。
    下面是一些在 MySQL 中常用的 SQL 提示。

    1.USE INDEX

    在查询语句中表名的后面,添加 USE INDEX 来提供希望 MySQL 去参考的索引列表,就可 以让 MySQL 不再考虑其他可用的索引。
    这里写图片描述

    2.IGNORE INDEX

    如果用户只是单纯地想让 MySQL 忽略一个或者多个索引,则可以使用 IGNORE INDEX 作
    为 HINT。同样是上面的例子,这次来看一下查询过程忽略索引 ind_sales2_id 的情况:
    这里写图片描述
    这里写图片描述
    从执行计划可以看出,系统忽略了指定的索引,而使用了全表扫描。

    3.FORCE INDEX

    为强制 MySQL 使用一个特定的索引,可在查询中使用 FORCE INDEX 作为 HINT。例如, 当不强制使用索引的时候,因为 id 的值都是大于 0 的,因此 MySQL 会默认进行全表扫描, 而不使用索引,如下所示:

    这里写图片描述
    但是,当使用 FORCE INDEX 进行提示时,即便使用索引的效率不是最高,MySQL 还是选择使 用了索引,这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示 后再次执行上面的 SQL:
    这里写图片描述
    这里写图片描述
    果然,执行计划中使用了 FORCE INDEX 后的索引。

    SQL优化调试在日常的开发工作中很重要,快速定位到问题不仅需要一定的技巧,更需要的是经验,熟练使用explain关键字和慢查询日志。

    展开全文
  • MySQL优化技巧

    2017-09-10 14:14:02
    MySQL优化三大方向① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。② 对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整。③ 对SQL语句以及表优化。MySQL参数优化1:MySQL 默认的最大...

    MySQL优化三大方向

    ① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。
    ② 对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整。
    ③ 对SQL语句以及表优化。

    MySQL参数优化

    1:MySQL 默认的最大连接数为 100,可以在 mysql 客户端使用以下命令查看
    mysql> show variables like 'max_connections';
    2:查看当前访问Mysql的线程
    mysql> show processlist;
    3:设置最大连接数
    mysql>set globle max_connections = 5000;
    最大可设置16384,超过没用
    4:查看当前被使用的connections
    mysql>show globle status like 'max_user_connections'

    对MySQL语句性能优化的16条经验

    ① 为查询缓存优化查询
    ② EXPLAIN 我们的SELECT查询(可以查看执行的行数)
    ③ 当只要一行数据时使用LIMIT 1
    ④ 为搜索字段建立索引
    ⑤ 在Join表的时候使用相当类型的列,并将其索引
    ⑥ 千万不要 ORDER BY RAND  ()
    ⑦ 避免SELECT *
    ⑧ 永远为每张表设置一个ID
    ⑨ 可以使用ENUM 而不要VARCHAR
    ⑩ 尽可能的使用NOT NULL
    ⑪ 固定长度的表会更快
    ⑫ 垂直分割
    ⑬ 拆分打的DELETE或INSERT语句
    ⑭ 越小的列会越快
    ⑮ 选择正确的存储引擎
    ⑯ 小心 "永久链接"
    具体描述如下:
    (一) 使用查询缓存优化查询
    大多数的MySQL服务器都开启了查询缓存。这是提高性能最有效的方法之一,而且这是被MySQL引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放入一个缓存中,这样后续的相同查询就不用操作而直接访问缓存结果了。
    这里最主要的问题是,对于我们程序员来说,这个事情是很容易被忽略的。因为我们某些查询语句会让MySQL不使用缓存,示例如下:
    1:SELECT username FROM user WHERE    signup_date >= CURDATE()
    2:SELECT username FROM user WHERE    signup_date >= '2014-06-24‘
    上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。所以,像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存。
    (二) 使用EXPLAIN关键字检测查询
    使用EXPLAIN关键字可以使我们知道MySQL是如何处理SQL语句的,这样可以帮助我们分析我们的查询语句或是表结构的性能瓶颈;EXPLAIN的查询结果还会告诉我们索引主键是如何被利用的,数据表是如何被被搜索或排序的....等等。语法格式是:EXPLAIN +SELECT语句;


    我们可以看到,前一个结果显示搜索了 7883 行,而后一个只是搜索了两个表的 9 和 16 行。查看rows列可以让我们找到潜在的性能问题。 
    (三)当只要一行数据时使用LIMIT 1
    加上LIMIT 1可以增加性能。MySQL数据库引擎会在查找到一条数据后停止搜索,而不是继续往后查询下一条符合条件的数据记录。
    (四)为搜索字段建立索引
    索引不一定就是给主键或者是唯一的字段,如果在表中,有某个字段经常用来做搜索,需要将其建立索引。
    索引的有关操作如下:
    1.创建索引
    在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
    1.1> ALTER TABLE
    ALTER TABLE 用来创建普通索引、唯一索引、主键索引和全文索引
    ALTER TABLE table_name ADD INDEX index_name (column_list);
    ALTER TABLE table_name ADD UNIQUE (column_list);
    ALTER TABLE table_name ADD PRIMARY KEY (column_list);
    ALTER TABLE table_name ADD FULLTEXT (column_list);
    其中table_name是要增加索引名的表名,column_list指出对哪些列列进行索引,多列时各列之间使用半角逗号隔开。索引名index_name是可选的,如果不指定索引名称,MySQL将根据第一个索引列自动指定索引名称,另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。
    1.2> CREATE INDEX
    CREATE INDEX可对表增加普通索引或UNIQUE索引以及全文索引,但是不可以对表增加主键索引
    CREATE INDEX index_name ON table_name (column_list);
    CREATE UNIQUE index_name ON table_name (column_list);
    CREATE FULLTEXT index_name ON table_name (column_list);
    table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名必须指定。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。
    2.索引类型
    普通索引INDEX:适用于name、email等一般属性
    唯一索引UNIQUE:与普通索引类似,不同的是唯一索引要求索引字段值在表中是唯一的,这一点和主键索引类似,但是不同的是,唯一索引允许有空值。唯一索引一般适用于身份证号码、用户账号等不允许有重复的属性字段上。
    主键索引:其实就是主键,一般在建表时就指定了,不需要额外添加。
    全文检索:只适用于VARCHAR和Text类型的字段。
    注意:全文索引和普通索引是有很大区别的,如果建立的是普通索引,一般会使用like进行模糊查询,只会对查询内容前一部分有效,即只对前面不使用通配符的查询有效,如果前后都有通配符,普通索引将不会起作用。对于全文索引而言在查询时有自己独特的匹配方式,例如我们在对一篇文章的标题和内容进行全文索引时:
    ALTER TABLE article ADD FULLTEXT ('title', 'content'); 在进行检索时就需要使用如下的语法进行检索:
    SELECT * FROM article WHERE MATCH('title', 'content') AGAINST ('查询字符串');
    在使用全文检索时的注意事项:
    MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表,如果是其他数据引擎,则全文索引不会生效。此外,MySql自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。另外使用MySql自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。
    3.组合索引
    组合索引又称多列索引,就是建立索引时指定多个字段属性。有点类似于字典目录,比如查询 'guo' 这个拼音的字时,首先查找g字母,然后在g的检索范围内查询第二个字母为u的列表,最后在u的范围内查找最后一个字母为o的字。比如组合索引(a,b,c),abc都是排好序的,在任意一段a的下面b都是排好序的,任何一段b下面c都是排好序的
    组合索引的生效原则是  从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用;
    造成断点的原因:
    前边的任意一个索引没有参与查询,后边的全部不生效。
    前边的任意一个索引字段参与的是范围查询,后面的不会生效。
    断点跟索引字字段在SQL语句中的位置前后无关,只与是否存在有关。在网上找到了很好的示例:
    比如:
    where a=3 and b=45 and c=5 .... #这种三个索引顺序使用中间没有断点,全部发挥作用;
    where a=3 and c=5... #这种情况下b就是断点,a发挥了效果,c没有效果
    where b=3 and c=4... #这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何效果;
    where b=45 and a=3 and c=5 .... #这个跟第一个一样,全部发挥作用,abc只要用上了就行,跟写的顺序无关
    (a,b,c) 三个列上加了联合索引(是联合索引 不是在每个列上单独加索引)而是建立了a,(a,b),(a,b,c)三个索引,另外(a,b,c)多列索引和 (a,c,b)是不一样的。
    具体实例可以说明:
    (0) select * from mytable where a=3 and b=5 and c=4;
    #abc三个索引都在where条件里面用到了,而且都发挥了作用
    (1) select * from mytable where  c=4 and b=6 and a=3;
    #这条语句为了说明 组合索引与在SQL中的位置先后无关,where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样
    (2) select * from mytable where a=3 and c=7;
    #a用到索引,b没有用,所以c是没有用到索引效果的
    (3) select * from mytable where a=3 and b>7 and c=3;
    #a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引
    (4) select * from mytable where b=3 and c=4;
    #因为a索引没有使用,所以这里 bc都没有用上索引效果
    (5) select * from mytable where a>4 and b=7 and c=9;
    #a用到了  b没有使用,c没有使用
    (6) select * from mytable where a=3 order by b;
    #a用到了索引,b在结果排序中也用到了索引的效果,前面说了,a下面任意一段的b是排好序的
    (7) select * from mytable where a=3 order by c;
    #a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort
    (8) select * from mytable where b=3 order by a;
    #b没有用到索引,排序中a也没有发挥索引效果
    注意:在查询时,MYSQL只能使用一个索引,如果建立的是多个单列的普通索引,在查询时会根据查询的索引字段,从中选择一个限制最严格的单例索引进行查询。别的索引都不会生效。
    4.查看索引
    mysql> show index from tblname;
    mysql> show keys from tblname;
    5.删除索引
    删除索引的mysql格式 :DORP INDEX IndexName ON tab_name;
    注意:不能使用索引的情况 
    对于普通索引而言 在使用like进行通配符模糊查询时,如果首尾之间都使用了通配符,索引时无效的。
    假设查询内容的关键词为'abc'
    SELECT * FROM tab_name WHERE index_column LIKE  'abc%';  #索引是有效的
    SELECT * FROM tab_name WHERE index_column LIKE  '%abc';  #索引是无效的
    SELECT * FROM tab_name WHERE index_column LIKE  '%cba';  #索引是有效的
    SELECT * FROM tab_name WHERE index_column LIKE  '%abc%';  #索引是无效的
    当检索的字段内容比较大而且检索内容前后部分都不确定的情况下,可以改为全文索引,并使用特定的检索方式。
    (五)在join表的时候使用相当类型的列,并将其索引
    如果在程序中有很多JOIN查询,应该保证两个表中join的字段时被建立过索引的。这样MySQL颞部会启动优化JOIN的SQL语句的机制。注意:这些被用来JOIN的字段,应该是相同类型的。例如:如果要把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无法使用它们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样)  
    例如:
    SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = “user_id”
    两个 state 字段应该是被建过索引的,而且应该是相当的类型,相同的字符集。
    (六)切记不要使用ORDER BY RAND()
    如果你真的想把返回的数据行打乱了,你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是:MySQL会不得不去执行RAND()函数(很耗CPU时间),而且这是为了每一行记录去记行,然后再对其排序。就算是你用了Limit 1也无济于事(因为要排序) 
    (七)避免使用SELECT *
    从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果我们的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。 所以,我们应该养成一个需要什么就取什么的好的习惯。
    Hibernate性能方面就会差,它不用*,但它将整个表的所有字段全查出来 
    优点:开发速度快
    (八)永远为每张表设置一个ID主键
    我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的 AUTO_INCREMENT标志。 就算是我们 users 表有一个主键叫 “email”的字段,我们也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外,在我们的程序中,我们应该使用表的ID来构造我们的数据结构。 而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区…… 在这里,只有一个情况是例外,那就是“关联表”的“外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。 
    (九)使用ENUM而不是VARCHAR
    ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。 如果我们有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,我们知道这些字段的取值是有限而且固定的,那么,我们应该使用 ENUM 而不是 VARCHAR。
    (十)尽可能的不要赋值为NULL
    如果不是特殊情况,尽可能的不要使用NULL。在MYSQL中对于INT类型而言,EMPTY是0,而NULL是空值。而在Oracle中 NULL和EMPTY的字符串是一样的。NULL也需要占用存储空间,并且会使我们的程序判断时更加复杂。现实情况是很复杂的,依然会有些情况下,我们需要使用NULL值。 下面摘自MySQL自己的文档: “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.” 
    (十一) 固定长度的表会更快
    如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要我们包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。 固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。 并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论我们用不用,他都是要分配那么多的空间。另外在取出值的时候要使用trim去除空格 
    (十二)垂直分割
    “垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。
    (十三)拆分大的DELETE或INSERT
    如果我们需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,我们需要非常小心,要避免我们的操作让我们的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。如果我们把我们的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让我们的WEB服务Crash,还可能会让我们的整台服务器马上掛了。所以在使用时使用LIMIT 控制数量操作记录的数量。
    (十四)越小的列会越快  
    对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把我们的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。 参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。 如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果我们不需要记录时间,使用 DATE 要比 DATETIME 好得多。 
    (十五)选择正确的存储引擎
    在MYSQL中有两个存储引擎MyISAM和InnoDB,每个引擎都有利有弊。
    MyISAM适合于一些需要大量查询的应用,但是对于大量写操作的支持不是很好。甚至一个update语句就会进行锁表操作,这时读取这张表的所有进程都无法进行操作直至写操作完成。另外MyISAM对于SELECT  COUNT(*)这类的计算是超快无比的。InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。
    MyISAM是MYSQL5.5版本以前默认的存储引擎,基于传统的ISAM类型,支持B-Tree,全文检索,但是不是事务安全的,而且不支持外键。不具有原子性。支持锁表。
    InnoDB是事务型引擎,支持ACID事务(实现4种事务隔离机制)、回滚、崩溃恢复能力、行锁。以及提供与Oracle一致的不加锁的读取方式。InnoDB存储它的表和索引在一个表空间中,表空间可以包含多个文件。
    MyISAM和InnoDB比较,如下图所示:

    对于Linux版本的MYSQL  配置文件在 /etc/my.cnf中

    在5.5之后默认的存储引擎是INNODB
    可以单独进行修改也可以在创建表时修改:
    ALTER TABLE tab_name ENGINE INNODB;
    (十六)小心永久链接
    “永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了,它会永远处在连接的状态,就算是数据库操作已经结束了。而且,自从我们的Apache开始重用它的子进程后——也就是说,下一次的HTTP请求会重用Apache的子进程,并重用相同的 MySQL 链接。 
    而且,Apache 运行在极端并行的环境中,会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在我们决定要使用“永久链接”之前,我们需要好好地考虑一下我们的整个系统的架构。


    展开全文
  • MySQL数据库优化的八种方式(经典必看) 引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿...

    MySQL数据库优化的八种方式(经典必看)

    引言:

     
    1. 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂

    2. 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿到自己的总结文集中,积累优质文章,提升个人能力,希望对大家今后开发中也有帮助

    1、选取最适用的字段属性

    MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。

    例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。同样的,如果可以的话,我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

    另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOTNULL,这样在将来执行查询的时候,数据库不用去比较NULL值。
    对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。

    2、使用连接(JOIN)来代替子查询(Sub-Queries)

    MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查询,如下所示:

    DELETEFROMcustomerinfo

    WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

    使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

    SELECT*FROMcustomerinfo

    WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

    如果使用连接(JOIN)..来完成这个查询工作,速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下:

    SELECT*FROMcustomerinfo

    LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID

    WHEREsalesinfo.CustomerIDISNULL

    连接(JOIN)..之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

    3、使用联合(UNION)来代替手动创建的临时表

    MySQL从4.0的版本开始支持union查询,它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用union来创建查询的时候,我们只需要用UNION作为关键字把多个select语句连接起来就可以了,要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

    SELECTName,PhoneFROMclientUNION

    SELECTName,BirthDateFROMauthorUNION

    SELECTName,SupplierFROMproduct

    4、事务

    尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询,但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操作就会变得不确定起来。设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用是:要么语句块中每条语句都操作成功,要么都失败。换句话说,就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始,COMMIT关键字结束。在这之间的一条SQL操作失败,那么,ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

    BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem='book';COMMIT;

    事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其它的用户所干扰。

    5、锁定表

    尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中。由于在事务执行的过程中,数据库将会被锁定,因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大的问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟。

    其实,有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

    LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='book';

    ...

    UPDATEinventorySETQuantity=11WHEREItem='book';UNLOCKTABLES

    这里,我们用一个select语句取出初始数据,通过一些计算,用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前,不会有其它的访问来对inventory进行插入、更新或者删除的操作。

    6、使用外键

    锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

    例如,外键可以保证每一条销售记录都指向某一个存在的客户。在这里,外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID,任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

     
    1. CREATETABLEcustomerinfo( CustomerIDINTNOTNULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

    2.  
    3. CREATETABLEsalesinfo( SalesIDINTNOTNULL,CustomerIDINTNOTNULL,

    4.  
    5. PRIMARYKEY(CustomerID,SalesID),

    6.  
    7. FOREIGNKEY(CustomerID)REFERENCEScustomerinfo(CustomerID)ONDELETECASCADE)TYPE=INNODB;

    注意例子中的参数“ONDELETECASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候,salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。

    7、使用索引

    索引是提高数据库性能的常用方法,它可以令数据库服务器以比没有索引快得多的速度检索特定的行,尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候,性能提高更为明显。

    那该对哪些字段建立索引呢?

    一般说来,索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况

    例如customerinfo中的“province”..字段,在这样的字段上建立索引将不会有什么帮助;相反,还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引,也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外,MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引,但仅能用于MyISAM类型的表。对于一个大的数据库,将数据装载到一个没有FULLTEXT索引的表中,然后再使用ALTERTABLE或CREATEINDEX创建索引,将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中,执行过程将会非常慢。

    8、优化的查询语句

    绝大多数情况下,使用索引可以提高查询的速度,但如果SQL语句使用不恰当的话,索引将无法发挥它应有的作用。

    下面是应该注意的几个方面。

    • 首先,最好是在相同类型的字段间进行比较的操作。

      在MySQL3.23版之前,这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较;但是作为特殊的情况,在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候,可以将它们进行比较。

    • 其次,在建有索引的字段上尽量不要使用函数进行操作。

    例如,在一个DATE类型的字段上使用YEAE()函数时,将会使索引不能发挥应有的作用。所以,下面的两个查询虽然返回的结果一样,但后者要比前者快得多。

    • 第三,在搜索字符型字段时,我们有时会使用LIKE关键字和通配符,这种做法虽然简单,但却也是以牺牲系统性能为代价的。

    例如下面的查询将会比较表中的每一条记录。

     
    1.  
    2. SELECT*FROMbooks

    3.  
    4. WHEREnamelike"MySQL%"

    但是如果换用下面的查询,返回的结果一样,但速度就要快上很多:

     
    1.  
    2. SELECT*FROMbooks

    3.  
    4. WHEREname>="MySQL"andname<"MySQM"

    最后,应该注意避免在查询中让MySQL进行自动类型转换,因为转换过程也会使索引变得不起作用。

     

    优化Mysql数据库的8个方法

    本文通过8个方法优化Mysql数据库:创建索引、复合索引、索引不会包含有NULL值的列、使用短索引、排序的索引问题、like语句操作、不要在列上进行运算、不使用NOT IN和<>操作

    1、创建索引
    对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。
    2、复合索引
    比如有一条语句是这样的:select * from users where area='beijing' and age=22;
    如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。
    3、索引不会包含有NULL值的列
    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
    4、使用短索引
    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
    5、排序的索引问题
    mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
    6、like语句操作
    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
    7、不要在列上进行运算
    select * from users where YEAR(adddate)<2007;
    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成
    select * from users where adddate<‘2007-01-01';
    8、不使用NOT IN和<>操作
    NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id<>3则可使用id>3 or id<3来代替。

     

     

     

     

    数据库SQL优化大总结之 百万级数据库优化方案

     

     

     

    网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。

    这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的话推荐给更多的人,让更多的人看到、纠正以及补充。

     

    1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。


    2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

    select id from t where num is null

    最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.

    备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

    不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。


    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

    select id from t where num = 0


    3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。

    4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

    select id from t where num=10 or Name = 'admin'

    可以这样查询:

    select id from t where num = 10
    union all
    select id from t where Name = 'admin'


    5.in 和 not in 也要慎用,否则会导致全表扫描,如:

    select id from t where num in(1,2,3)

    对于连续的数值,能用 between 就不要用 in 了:

    select id from t where num between 1 and 3

    很多时候用 exists 代替 in 是一个好的选择:

    select num from a where num in(select num from b)

    用下面的语句替换:

    select num from a where exists(select 1 from b where num=a.num)

     

    6.下面的查询也将导致全表扫描:

    select id from t where name like ‘%abc%’

    若要提高效率,可以考虑全文检索。

    7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

    select id from t where num = @num

    可以改为强制查询使用索引:

    select id from t with(index(索引名)) where num = @num

    .应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where num/2 = 100

    应改为:

    select id from t where num = 100*2


    9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
    select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

    应改为:

    select id from t where name like 'abc%'
    select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'


    10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

    11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    12.不要写一些没有意义的查询,如需要生成一个空表结构:

    select col1,col2 into #t from t where 1=0

    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
    create table #t(…)

    13.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

    14.对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

    15.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。


    16.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

    17.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

    18.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

    19.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    20.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

    21.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

    22. 避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件, 最好使用导出表。

    23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

    24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

    25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

    26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

    27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

    28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

    29.尽量避免大事务操作,提高系统并发能力。

    30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

     

    实际案例分析:拆分大的 DELETE 或INSERT 语句,批量提交SQL语句
      如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。
      Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。
      如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。
      所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:

    while(1){
    
       //每次只做1000条
    
       mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”);
    
       if(mysql_affected_rows() == 0){
    
         //删除完成,退出!
         break;
      }
    
    //每次暂停一段时间,释放表让其他进程/线程访问。
    usleep(50000)
    
    }

     

    好了,到这里就写完了。我知道还有很多没有写到的,还请大家补充。后面有空会介绍一些SQL优化工具给大家。让我们一起学习,一起进步吧!

     

     

    运维角度浅谈MySQL数据库优化

     

     

     一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的,它是随着用户量的增加,基础架构才逐渐完善。这篇博文主要谈MySQL数据库发展周期中所面临的问题及优化方案,暂且抛开前端应用不说,大致分为以下五个阶段:

    1、数据库表设计

      项目立项后,开发部根据产品部需求开发项目,开发工程师工作其中一部分就是对表结构设计。对于数据库来说,这点很重要,如果设计不当,会直接影响访问速度和用户体验。影响的因素很多,比如慢查询、低效的查询语句、没有适当建立索引、数据库堵塞(死锁)等。当然,有测试工程师的团队,会做压力测试,找bug。对于没有测试工程师的团队来说,大多数开发工程师初期不会太多考虑数据库设计是否合理,而是尽快完成功能实现和交付,等项目有一定访问量后,隐藏的问题就会暴露,这时再去修改就不是这么容易的事了。

    2、数据库部署

      该运维工程师出场了,项目初期访问量不会很大,所以单台部署足以应对在1500左右的QPS(每秒查询率)。考虑到高可用性,可采用MySQL主从复制+Keepalived做双击热备,常见集群软件有Keepalived、Heartbeat。

    双机热备博文:http://lizhenliang.blog.51cto.com/7876557/1362313

    3、数据库性能优化

      如果将MySQL部署到普通的X86服务器上,在不经过任何优化情况下,MySQL理论值正常可以处理2000左右QPS,经过优化后,有可能会提升到2500左右QPS,否则,访问量当达到1500左右并发连接时,数据库处理性能就会变慢,而且硬件资源还很富裕,这时就该考虑软件问题了。那么怎样让数据库最大化发挥性能呢?一方面可以单台运行多个MySQL实例让服务器性能发挥到最大化,另一方面是对数据库进行优化,往往操作系统和数据库默认配置都比较保守,会对数据库发挥有一定限制,可对这些配置进行适当的调整,尽可能的处理更多连接数。

    具体优化有以下三个层面:

      3.1 数据库配置优化

      MySQL常用有两种存储引擎,一个是MyISAM,不支持事务处理,读性能处理快,表级别锁。另一个是InnoDB,支持事务处理(ACID),设计目标是为处理大容量数据发挥最大化性能,行级别锁。

      表锁:开销小,锁定粒度大,发生死锁概率高,相对并发也低。

      行锁:开销大,锁定粒度小,发生死锁概率低,相对并发也高。

      为什么会出现表锁和行锁呢?主要是为了保证数据的完整性,举个例子,一个用户在操作一张表,其他用户也想操作这张表,那么就要等第一个用户操作完,其他用户才能操作,表锁和行锁就是这个作用。否则多个用户同时操作一张表,肯定会数据产生冲突或者异常。

      根据以上看来,使用InnoDB存储引擎是最好的选择,也是MySQL5.5以后版本中默认存储引擎。每个存储引擎相关联参数比较多,以下列出主要影响数据库性能的参数。

      公共参数默认值:

    1

    2

    3

    4

    5

    6

    max_connections = 151

    #同时处理最大连接数,推荐设置最大连接数是上限连接数的80%左右   

    sort_buffer_size = 2M

    #查询排序时缓冲区大小,只对order by和group by起作用,可增大此值为16M

    open_files_limit = 1024 

    #打开文件数限制,如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时,程序会无法连接数据库或卡死

      MyISAM参数默认值:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    key_buffer_size = 16M

    #索引缓存区大小,一般设置物理内存的30-40%

    read_buffer_size = 128K  

    #读操作缓冲区大小,推荐设置16M或32M

    query_cache_type = ON

    #打开查询缓存功能

    query_cache_limit = 1M  

    #查询缓存限制,只有1M以下查询结果才会被缓存,以免结果数据较大把缓存池覆盖

    query_cache_size = 16M  

    #查看缓冲区大小,用于缓存SELECT查询结果,下一次有同样SELECT查询将直接从缓存池返回结果,可适当成倍增加此值

      InnoDB参数默认值:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    innodb_buffer_pool_size = 128M

    #索引和数据缓冲区大小,一般设置物理内存的60%-70%

    innodb_buffer_pool_instances = 1    

    #缓冲池实例个数,推荐设置4个或8个

    innodb_flush_log_at_trx_commit = 1  

    #关键参数,0代表大约每秒写入到日志并同步到磁盘,数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘,I/O开销大,执行完SQL要等待日志读写,效率低。2代表只把日志写入到系统缓存区,再每秒同步到磁盘,效率很高,如果服务器故障,才会丢失事务数据。对数据安全性要求不是很高的推荐设置2,性能高,修改后效果明显。

    innodb_file_per_table = OFF  

    #默认是共享表空间,共享表空间idbdata文件不断增大,影响一定的I/O性能。推荐开启独立表空间模式,每个表的索引和数据都存在自己独立的表空间中,可以实现单表在不同数据库中移动。

    innodb_log_buffer_size = 8M  

    #日志缓冲区大小,由于日志最长每秒钟刷新一次,所以一般不用超过16M

      3.2 系统内核优化

      大多数MySQL都部署在linux系统上,所以操作系统的一些参数也会影响到MySQL性能,以下对linux内核进行适当优化。

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    net.ipv4.tcp_fin_timeout = 30

    #TIME_WAIT超时时间,默认是60s

    net.ipv4.tcp_tw_reuse = 1    

    #1表示开启复用,允许TIME_WAIT socket重新用于新的TCP连接,0表示关闭

    net.ipv4.tcp_tw_recycle = 1  

    #1表示开启TIME_WAIT socket快速回收,0表示关闭

    net.ipv4.tcp_max_tw_buckets = 4096   

    #系统保持TIME_WAIT socket最大数量,如果超出这个数,系统将随机清除一些TIME_WAIT并打印警告信息

    net.ipv4.tcp_max_syn_backlog = 4096

    #进入SYN队列最大长度,加大队列长度可容纳更多的等待连接

      在linux系统中,如果进程打开的文件句柄数量超过系统默认值1024,就会提示“too many files open”信息,所以要调整打开文件句柄限制。

    1

    2

    3

    4

    # vi /etc/security/limits.conf  #加入以下配置,*代表所有用户,也可以指定用户,重启系统生效

    * soft nofile 65535

    * hard nofile 65535

    # ulimit -SHn 65535   #立刻生效

      3.3 硬件配置

      加大物理内存,提高文件系统性能。linux内核会从内存中分配出缓存区(系统缓存和数据缓存)来存放热数据,通过文件系统延迟写入机制,等满足条件时(如缓存区大小到达一定百分比或者执行sync命令)才会同步到磁盘。也就是说物理内存越大,分配缓存区越大,缓存数据越多。当然,服务器故障会丢失一定的缓存数据。

      SSD硬盘代替SAS硬盘,将RAID级别调整为RAID1+0,相对于RAID1和RAID5有更好的读写性能(IOPS),毕竟数据库的压力主要来自磁盘I/O方面。

    4、数据库架构扩展

      随着业务量越来越大,单台数据库服务器性能已无法满足业务需求,该考虑加机器了,该做集群了~~~。主要思想是分解单台数据库负载,突破磁盘I/O性能,热数据存放缓存中,降低磁盘I/O访问频率。

      4.1 主从复制与读写分离

      因为生产环境中,数据库大多都是读操作,所以部署一主多从架构,主数据库负责写操作,并做双击热备,多台从数据库做负载均衡,负责读操作,主流的负载均衡器有LVS、HAProxy、Nginx。

      怎么来实现读写分离呢?大多数企业是在代码层面实现读写分离,效率比较高。另一个种方式通过代理程序实现读写分离,企业中应用较少,常见代理程序有MySQL Proxy、Amoeba。在这样数据库集群架构中,大大增加数据库高并发能力,解决单台性能瓶颈问题。如果从数据库一台从库能处理2000 QPS,那么5台就能处理1w QPS,数据库横向扩展性也很容易。

      有时,面对大量写操作的应用时,单台写性能达不到业务需求。如果做双主,就会遇到数据库数据不一致现象,产生这个原因是在应用程序不同的用户会有可能操作两台数据库,同时的更新操作造成两台数据库数据库数据发生冲突或者不一致。在单库时MySQL利用存储引擎机制表锁和行锁来保证数据完整性,怎样在多台主库时解决这个问题呢?有一套基于perl语言开发的主从复制管理工具,叫MySQL-MMM(Master-Master replication managerfor Mysql,Mysql主主复制管理器),这个工具最大的优点是在同一时间只提供一台数据库写操作,有效保证数据一致性。

      主从复制博文:http://lizhenliang.blog.51cto.com/7876557/1290431

      读写分离博文:http://lizhenliang.blog.51cto.com/7876557/1305083

     MySQL-MMM博文:http://lizhenliang.blog.51cto.com/7876557/1354576

      4.2 增加缓存

      给数据库增加缓存系统,把热数据缓存到内存中,如果缓存中有要请求的数据就不再去数据库中返回结果,提高读性能。缓存实现有本地缓存和分布式缓存,本地缓存是将数据缓存到本地服务器内存中或者文件中。分布式缓存可以缓存海量数据,扩展性好,主流的分布式缓存系统有memcached、redis,memcached性能稳定,数据缓存在内存中,速度很快,QPS可达8w左右。如果想数据持久化就选择用redis,性能不低于memcached。

      工作过程:

      wKiom1VukrqyM-JcAABPhCy-LOM409.jpg

      4.3 分库

      分库是根据业务不同把相关的表切分到不同的数据库中,比如web、bbs、blog等库。如果业务量很大,还可将切分后的库做主从架构,进一步避免单个库压力过大。

      4.4 分表

      数据量的日剧增加,数据库中某个表有几百万条数据,导致查询和插入耗时太长,怎么能解决单表压力呢?你就该考虑是否把这个表拆分成多个小表,来减轻单个表的压力,提高处理效率,此方式称为分表。

      分表技术比较麻烦,要修改程序代码里的SQL语句,还要手动去创建其他表,也可以用merge存储引擎实现分表,相对简单许多。分表后,程序是对一个总表进行操作,这个总表不存放数据,只有一些分表的关系,以及更新数据的方式,总表会根据不同的查询,将压力分到不同的小表上,因此提高并发能力和磁盘I/O性能。

      分表分为垂直拆分和水平拆分:

      垂直拆分:把原来的一个很多字段的表拆分多个表,解决表的宽度问题。你可以把不常用的字段单独放到一个表中,也可以把大字段独立放一个表中,或者把关联密切的字段放一个表中。

      水平拆分:把原来一个表拆分成多个表,每个表的结构都一样,解决单表数据量大的问题。

      4.5 分区

      分区就是把一张表的数据根据表结构中的字段(如range、list、hash等)分成多个区块,这些区块可以在一个磁盘上,也可以在不同的磁盘上,分区后,表面上还是一张表,但数据散列在多个位置,这样一来,多块硬盘同时处理不同的请求,从而提高磁盘I/O读写性能,实现比较简单。

    注:增加缓存、分库、分表和分区主要由程序猿来实现。

    5、数据库维护

      数据库维护是运维工程师或者DBA主要工作,包括性能监控、性能分析、性能调优、数据库备份和恢复等。

      5.1 性能状态关键指标

      QPS,Queries Per Second:每秒查询数,一台数据库每秒能够处理的查询次数

      TPS,Transactions Per Second:每秒处理事务数

      通过show status查看运行状态,会有300多条状态信息记录,其中有几个值帮可以我们计算出QPS和TPS,如下:

      Uptime:服务器已经运行的实际,单位秒

      Questions:已经发送给数据库查询数

      Com_select:查询次数,实际操作数据库的

      Com_insert:插入次数

      Com_delete:删除次数

      Com_update:更新次数

      Com_commit:事务次数

      Com_rollback:回滚次数

      那么,计算方法来了,基于Questions计算出QPS:

    1

    2

      mysql> show global status like 'Questions';

      mysql> show global status like 'Uptime';

      QPS = Questions / Uptime

      基于Com_commit和Com_rollback计算出TPS:

    1

    2

    3

      mysql> show global status like 'Com_commit';

      mysql> show global status like 'Com_rollback';

      mysql> show global status like 'Uptime';

      TPS = (Com_commit + Com_rollback) / Uptime

      另一计算方式:基于Com_select、Com_insert、Com_delete、Com_update计算出QPS

    1

      mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update');

      等待1秒再执行,获取间隔差值,第二次每个变量值减去第一次对应的变量值,就是QPS

      TPS计算方法:

    1

      mysql> show global status where Variable_name in('com_insert','com_delete','com_update');

      计算TPS,就不算查询操作了,计算出插入、删除、更新四个值即可。

      经网友对这两个计算方式的测试得出,当数据库中myisam表比较多时,使用Questions计算比较准确。当数据库中innodb表比较多时,则以Com_*计算比较准确。

      5.2 开启慢查询日志

      MySQL开启慢查询日志,分析出哪条SQL语句比较慢,使用set设置变量,重启服务失效,可以在my.cnf添加参数永久生效。

    1

    2

    3

    4

    mysql> set global slow-query-log=on  #开启慢查询功能

    mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log';  #指定慢查询日志文件位置

    mysql> set global log_queries_not_using_indexes=on;   #记录没有使用索引的查询

    mysql> set global long_query_time=1;   #只记录处理时间1s以上的慢查询

      分析慢查询日志,可以使用MySQL自带的mysqldumpslow工具,分析的日志较为简单。

      # mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log    #查看最慢的前三个查询

      也可以使用percona公司的pt-query-digest工具,日志分析功能全面,可分析slow log、binlog、general log。

      分析慢查询日志:pt-query-digest /var/log/mysql/mysql-slow.log

      分析binlog日志:mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql 

      pt-query-digest --type=binlog mysql-bin.000001.sql 

      分析普通日志:pt-query-digest --type=genlog localhost.log

      5.3 数据库备份

      备份数据库是最基本的工作,也是最重要的,否则后果很严重,你懂得!但由于数据库比较大,上百G,往往备份都很耗费时间,所以就该选择一个效率高的备份策略,对于数据量大的数据库,一般都采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup等,mysqldump比较适用于小的数据库,因为是逻辑备份,所以备份和恢复耗时都比较长。mysqlhotcopy和xtrabackup是物理备份,备份和恢复速度快,不影响数据库服务情况下进行热拷贝,建议使用xtrabackup,支持增量备份。

      Xtrabackup备份工具使用博文:http://lizhenliang.blog.51cto.com/7876557/1612800

      5.4 数据库修复

      有时候MySQL服务器突然断电、异常关闭,会导致表损坏,无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复,myisamchk和mysqlcheck。

      myisamchk:只能修复myisam表,需要停止数据库

      常用参数:

      -f --force    强制修复,覆盖老的临时文件,一般不使用

      -r --recover  恢复模式

      -q --quik     快速恢复

      -a --analyze  分析表

      -o --safe-recover 老的恢复模式,如果-r无法修复,可以使用此参数试试

      -F --fast     只检查没有正常关闭的表

      快速修复weibo数据库:

      # cd /var/lib/mysql/weibo 

      # myisamchk -r -q *.MYI

      mysqlcheck:myisam和innodb表都可以用,不需要停止数据库,如修复单个表,可在数据库后面添加表名,以空格分割

      常用参数:

      -a  --all-databases  检查所有的库

      -r  --repair   修复表

      -c  --check    检查表,默认选项

      -a  --analyze  分析表

      -o  --optimize 优化表

      -q  --quik   最快检查或修复表

      -F  --fast   只检查没有正常关闭的表

      快速修复weibo数据库:

      mysqlcheck -r -q -uroot -p123 weibo 

      5.5 另外,查看CPU和I/O性能方法

      #查看CPU性能

    wKiom1VtPFmCEtY9AADbdiZbn9A400.jpg

      #参数-P是显示CPU数,ALL为所有,也可以只显示第几颗CPUwKioL1VtPpayB7WeAALQHX41buc367.jpg

      #查看I/O性能

    wKiom1VtPSXTsI4zAAMkfVf2r-I743.jpg

     

      #参数-m是以M单位显示,默认K

      #%util:当达到100%时,说明I/O很忙。

      #await:请求在队列中等待时间,直接影响read时间。

      I/O极限:IOPS(r/s+w/s),一般RAID0/10在1200左右。(IOPS,每秒进行读写(I/O)操作次数)

      I/O带宽:在顺序读写模式下SAS硬盘理论值在300M/s左右,SSD硬盘理论值在600M/s左右。

    展开全文
  • ① SQL语句及索引的优化 SQL语句的优化: 1、尽量避免使用子查询 ​2、避免函数索引 3、用IN来替换OR 4、LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引,如果查询的是索引列则可以 5、读取...
  • mysql优化

    2018-08-13 18:42:41
    Mysql优化综合性的问题: A、表的是设计合理化(符合 3范式) B、添加适当的索引(index)[四种:普通索引,主键索引,唯一索引,unique,全文索引] C、分表技术(水平分割,垂直分割) D、读写[写:update/delete/add...
  • 说起MySQL的查询优化,相信大家收藏了一堆奇技淫巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优...
  • MySQL优化面试准备

    2018-11-28 21:42:18
    想了很久要不要发一篇关于MySql优化的文章最后还是决定把它写出来。以输出倒逼自己复习与输入。以下内容大都参考了《高性能MySQL》一书也好把一些的章节做一个总结。全文的聊到的三个主要能容是: MySql的特点与...
  • MySQL优化   数据库操作往往是当今大多数Web应用程序的主要瓶颈。不仅是DBA(数据库管理员)不得不担心这些性能问题。作为程序员,我们需要通过正确地构造表、编写优化的查询和更好的代码来完成我们的工作。在本文...
  • mysql 优化面试题

    2020-03-04 12:22:18
    mysql优化,不用怕面试题 第一方面:30种mysql优化sql语句查询的方法1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by涉及的列上建立索引。 2.应尽量避免在 where 子句中使用 !=或&lt...
  • 最近项目不太忙,所以有时间静心来研究下mysql的优化,对于MySQL的设置是否合理优化,直接影响... 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.c...
  • 对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,...下面我们了解一下MySQL优化的一些基础,MySQL的优
  • 当面试官问你mysql优化 首先要知道mysql分为两个层次。你要逐一从各个层次讲解mysql优化,让面试官知道,哇,原来你对mysql这么了解。 面试官:你知道哪些mysql优化? 我: …巴拉巴拉巴拉。 mysql结构层次 首先,...
  • MySQL已经成为时下关系型数据库产品的中坚力量,备受互联网大厂的青睐,出门面试想进BAT,想拿高工资,不会点MySQL优化知识,拿offer的成功率会大大下降。 为什么要优化 系统的吞吐量瓶颈往往出现在数据库的访问...
  • 应用程序慢如牛,原因多多,可能是网络的原因、可能是系统...MYSQL优化技巧 程序中嵌入的一行行的SQL语句,如果使用了一些优化小技巧,定能达到事半功倍的效果。下面是微课帮小编整理的一些技巧。  技巧1 :比较...
  • 第一方面:30种mysql优化sql语句查询的方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by涉及的列上建立索引。  2.应尽量避免在 where 子句中使用 !=或&lt;&gt; 操作符,...
  • 一、mysql优化,主要从哪些方面去考虑 1.存储层 数据表的存储引擎选取, 字段类型的选取, 逆范式 2.设计层 索引的使用, 分区/分表, sql语句的优化, 存储过程优化 3.架构层 分布式部署(读写分离) 4.sql语句...
  • MySQL优化工具之profiling 使用慢查询日志分析出慢查询语句后,用profiling分析该语句的优化后执行效果。 查看慢查询设置 mysql> show variables like "%slow%"; +---------------------+------...
  • 第一方面:30种mysql优化sql语句查询的方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。  2.应尽量避免在 where 子句中使用!=或&lt;&gt;操作符,否则将...
  • 本课程为PHP高性能架构班系列课程中的mysql优化部分教程,由燕十八主讲。课程通过16课时(共32课)来详解mysql的优化技术,帮助大家在实际使用过程中来提升mysql的性能,从而节约成本、提高效率。 课程讲师:燕十八...
1 2 3 4 5 ... 20
收藏数 285,306
精华内容 114,122
关键字:

mysql优化