Mysql优化和小技巧等

Mysql分区:

创建用户:

CREATEUSER 'username'@'host' IDENTIFIED BY 'password';

 

用户授权:

命令:GRANT privileges ON databasename.tablename TO 'username'@'host'

 

说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*.

 

例子: GRANT SELECT, INSERT ON test.user TO 'pig'@'%';

GRANTALL ON *.* TO 'pig'@'%';

 

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:

GRANTprivileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

 

 

建库:

createdatabase study_mysql ;

 

 

 

分区:

范围分区range

CREATETABLE employees (

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT'9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

    PARTITIONp0 VALUES LESS THAN (6),

    PARTITION p1 VALUES LESS THAN (11),

    PARTITION p2 VALUES LESS THAN (16),

    PARTITION p3 VALUES LESS THAN MAXVALUE

);

 

 

集合分区list

CREATETABLE employees_list(

    id INT NOT NULL,

    fname VARCHAR(30),

    lname VARCHAR(30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT'9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)

PARTITION BY LIST (store_id)(

PARTITION pNorth    VALUESIN (2,4,6,8,10),

PARTITIONpEast     VALUES IN (1,3,5,7,9),

PARTITIONpWest     VALUES IN (11,13,15,17,19),

PARTITIONpControl  VALUES IN (12,14,16,18)

);

 

 

 

 

哈希分区hash

 

CREATETABLE t2 (

    id INT NOT NULL 

)

PARTITION BY HASH(id)

PARTITIONS4；

 

 

 

createindex in_id on t2(id);

insertinto t2(id) values(1);

insertinto t2 values(2) ,(3),(4);

 

 

 

分区测试

--创建新表

createtable t3(id int);

--存储过程

 

createprocedure p3()

     begin

      set @i=1;

 while @i<10000 do

  insert into t3 values(@i);

  set @i=@i+1;

 end while;

 end

 

 

 

 

--测试分区

 

 

CREATETABLE part_tab(

c1int default NULL,

c2varchar(30) default NULL,

c3date default NULL)engine=myisam

 

 

PARTITIONBY RANGE(year(c3))(

   PARTITION p0 VALUES LESS THAN(1995),

   PARTITION P1 VALUES LESS THAN(1996),

   PARTITION P2 VALUES LESS THAN(1997),

   PARTITION P3 VALUES LESS THAN(1998),

   PARTITION P4 VALUES LESS THAN(1999),

   PARTITION P5 VALUES LESS THAN(2000),

   PARTITION P6 VALUES LESS THAN(2001),

   PARTITION P7 VALUES LESS THAN(2002),

   PARTITION P8 VALUES LESS THAN(2003),

   PARTITION P9 VALUES LESS THAN(2004),

   PARTITION P10 VALUES LESS THAN(2010),

   PARTITION p11 VALUES LESS THAN MAXVALUE);

 

 

  

  

  

CREATETABLE no_part_tab(

c1int default NULL,

c2varchar(30) default NULL,

c3date default NULL)engine=myisam;

  

--测试分区 测试数据

delimiter//

 

 

CREATEPROCEDURE load_part_tab()

   begin

   declare v int default 0;

   while v<8000000

   do

     insert into part_tab

values(v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));

setv=v+1;

endwhile;

end

 

 

delimiter;

 

 

 

 

insertinto no_part_tab select * from part_tab

 

 

 

 

 

 

--分区表

selectcount(*) from part_tab where c3>date'1995-01-01' and c3<date'1996-12-31'

 

 

--未分区

 

selectcount(*) from no_part_tab where c3>date'1995-01-01' andc3<date'1996-12-31'

 

 

 

 

解析分区表

descselect count(*) from part_tab where c3>date'1995-01-01' andc3<date'1996-12-31'

mysql>desc select count(*) from part_tab where c3>date'1995-01-01' and c3<date'

1996-12-31'\G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: NULL

         type: NULL

possible_keys:NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

        Extra: Impossible WHERE noticed afterreading const tables

1row in set (0.00 sec)

 

 

解析未分区表

descselect count(*) from no_part_tab where c3>date'1995-01-01' and c3<date'1996-12-31'

mysql>desc select count(*) from no_part_tab where c3>date'1995-01-01' and c3<da

te'1996-12-31'\G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: no_part_tab

         type: ALL

possible_keys:NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 8000000

        Extra: Using where

1row in set (0.00 sec)

 

 

 

给未分区表创建索引

createINDEX idx_of_c3 on no_part_tab(c3)

 

 

mysql>create INDEX idx_of_c3 on no_part_tab(c3);

QueryOK, 8000000 rows affected (56.75 sec)

Records:8000000  Duplicates: 0  Warnings: 0

 

 

给分区表创建索引

createINDEX idx_of_c3 on part_tab(c3)

 

 

mysql>create INDEX idx_of_c3 on part_tab(c3);

QueryOK, 8000000 rows affected (57.39 sec)

Records:8000000  Duplicates: 0  Warnings: 0

 

 

 

 

 

 

索引分区总结:

分区表和未分区表建立索引以后速度差不多，但是未分区表更占用CPU等资源

 

 

 

 

加入未索引的列  \G 格式化显示 desc结果

--分区表

descselect count(*) from part_tab where c3>date'1995-01-01' andc3<date'1996-12-31' and c2='hello'

--未分区

desc  select count(*) from no_part_tab wherec3>date'1995-01-01' and c3<date'1996-12-31' and c2='hello'

 

 

 

 

总结:加入未索引的列以后，分区表优势明显

 

 

 

 

 

 

 

 

 

MySql 存储引擎

 

 

 

 

InnoDB:  .fim

 

 

MyISAM:

 

 

 

 

 

 

 

 

 

 

 

 

 

InnoDB

 

 

my.ini加入

--代表数据库存放的目录

innodb_data_home_dir="C:\ProgramFiles\MySQL\MySQL Server 5.6\data\InnoDB"

--日志存放目录

innodb_log_group_home_dir="C:\ProgramFiles\MySQL\MySQL Server 5.6\data\InnoDB"

 

 

innodb_data_file_path=ibdata1:10M:autoextend

--是否使用共享以及独立表空间

innodb_file_per_table=1  

 

 

重启mysql

 

 

 

 

 

 

myisam有三个文件.frm 表结构   .MYD存表数据  .MYI  存索引

innodb有两个文件

 

 

 

 

createtable t3(in int)ENGINE=InnoDB

 

 

createtable t4(in int)ENGINE=InnoDB

 

 

createtable t5(in int)ENGINE=InnoDB

 

 

createtable t6(in int)ENGINE=InnoDB

 

 

createtable t7(id int)engine=innodb

partitionby hash(id)

partitions5;

 

 

优化技巧:

 

 

1.正则表达式regexp

 

 

selectname,email from t1 where email regexp"@163[.,]com$"

 

 

 

 

 

2.rand()随机数

 select * from stu order by rand();

 --随机拿出钱三条

 select * from stu order by rand() limit 3;

 

 

 

 

3.group by 的with rollup

 

 

  create table table_group (cnamevarchar(30),pname varchar(30))

 

 

 insert into table_group (cname,pname)values("bj","hd"), ("bj","hd"),("bj","xc"), ("bj","xc"),("bj","hd"), ("sh","dh"),("sh","dh"), ("sh","rg"),("sh","dh")

 

selectcname,pname,count(pname)from table_group group by cname,pname with rollup

 

 

 

4.bit group functions

 

 bit_and ,bit_or

 

createtable demo2(

  id int

);

 insert into demo2 values(10);

 insert into demo2 values(20);

 

  select * from demo2 group by id;

 

  select bit_or(id) from demo2 group by id;

 alter table demo2 add name varchar(30)

 

 create table table_bit (name varchar(30),

 score int

 )

 

 insert intotable_bit(name,score)values("user1",10),("user1",20),("user1",30),("user2",1),("user2",2),("user2",3)

 

 select * from table_bit group by name

 

 select bit_and(score)from table_bit group byname

 

 

  select bit_or(score)from table_bit group byname

 

 

 

 

5.外键 myisam不支持外键  innodb支持外键

  create table temp(id int ,namechar(20),foreign key(id) references outTable(id) on delete cascade on updatecascade)

 

 

 

 

 

6.help

--查询记不太清楚的命令

?pro% ;

--查看存储过程procedure用法

?procedure;

--查看所有命令

?contents;

--行列互换

\G

showcreate table t7

SQL语句优化

 

 

show[session|global] status;

session(default)表示当前连接

global表示自数据库启动至今

 

 

这些针对所有表引擎

showsession status like"com_insert%"

showglobal status like"com_insert%"

 

 

showsession status like "com_delete%"

showglobal status like "com_delete%"

 

 

showsession status like"com_select%"

showglobal status like"com_select%"

 

 

 

 

只针对innodb引擎

--select

Innodb_rows_read 

--

innodb_rows_updated

--插入

innodb_rows_inserted

--删除

innodb_rows_deleted

 

 

--连接数mysql数量

connections

--服务器工作的秒数

uptime

--慢查询的次数

slow_queries;

--查看慢查询是否开启

showvariables like "slow_query_log";

--慢查询相关

showvariables like"%slow%";

--慢查询时间设定

 show variables like '%long%';

 show variables like'long_query_time';

 

 

--查看表结构和引擎类型

showcreate table table_name

 

 

--解析查询语句1

descselect * from demo2

--解析查询语句2

explainselect * from demo2

 

 

 

 

mysql>explain select * from demo2 \G

***************************1. row ***************

           id: 1

  select_type: SIMPLE    --简单查询(不使用表连接或子查询)primary（住查询，外层查询）、union（union第二个或者后面的查询）、subquery(子查询第一个select等)

        table: demo2      --表名数据集

         type: ALL        --all全表扫描;性能好到差system(表就一行)const(只一行匹配)eq_ref(每一行使用主键和唯一)ref(同eq_ref无主键和唯一)  ref_or_null(同前面对null查询)、index_merge(索引合并优化)、 unique_subquery(主键子查询)、index_subquery（非主键子查询）、range(表单中的范围查询)、index(都通过查询索引来得到数据)、all(全表扫描)

possible_keys:NULL       --可能用到的索引名

          key: NULL       --实际用到的索引

      key_len: NULL       --索引字段长度

          ref: NULL

         rows: 17             --影响行数

        Extra: NULL           --where index 执行情况的说明和描述

 

 

 

 

 

 

 explain select * from demo2 where name='test2'\G

mysql>explain select * from demo2 where name='test2' \G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: demo2

         type: ALL

possible_keys:NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 18

        Extra: Using where

1row in set (0.00 sec)

 

 

altertable demo2 add index in_name(name)

 

 

mysql>explain select * from demo2 where name='test2' \G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: demo2

         type: ref

possible_keys:in_name

          key: in_name

      key_len: 93

          ref: const

         rows: 1

        Extra: Using index condition

1row in set (0.00 sec)

 

 

 

 

优化步骤:

1查看慢查询日志

 2.desc 、explain 查看语句

 3.增加索引

 

 

 

小技巧

 

 

数据库优化:group by 加入order by null 不排序,提高速度

 

 

mysql>desc select * from t2 group by name \G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: ALL

possible_keys:NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 14

        Extra: Using temporary; Using filesort

1row in set (0.00 sec)

 

 

 

 

mysql>desc select * from t2 group by name order by null \G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: ALL

possible_keys:NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 14

        Extra: Using temporary

1row in set (0.00 sec)

 

 

 

 

 

 

 

 

 CREATE TABLE `t1` (

  `id` int(10) unsigned NOT NULLAUTO_INCREMENT,

  `name` varchar(30) DEFAULT NULL,

  PRIMARY KEY (`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8

 alter table t1 add index index_name (name);

 

 

CREATETABLE `t2` (

  `id` int(10) unsigned NOT NULLAUTO_INCREMENT,

  `uid` int(11) DEFAULT NULL,

  `class` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8 |

altertable t2 add index index_class on class;

createindex index_uid on t2(uid);

 

 

 

 

insertintot1(name)values("user1"),("user2"),("user3"),("user4"),("user6

"),("user5"),("user7"),("user8"),("user9");

 

 

 

 

 insert into t2(uid,class)values(1,111),(2,222),(3,333),(4,111),(5,111);

 

 

嵌套查询--外边的表没使用索引

 

 

mysql>  explain select * from t1 where id in(selectuid from t2) \G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: index

possible_keys:index_uid

          key: index_uid

      key_len: 5

          ref: NULL

         rows: 5

        Extra: Using where; Using index;LooseScan

***************************2. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: eq_ref

possible_keys:PRIMARY

          key: PRIMARY

      key_len: 4

          ref: study_mysql.t2.uid

         rows: 1

        Extra: Using where

2rows in set (0.00 sec)

 

 

--多表查询

 mysql> explain select t1.* from t1,t2 where t1.id=t2.uid \G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: index

possible_keys:index_uid

          key: index_uid

      key_len: 5

          ref: NULL

         rows: 5

        Extra: Using where; Using index

***************************2. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: eq_ref

possible_keys:PRIMARY

          key: PRIMARY

      key_len: 4

          ref: study_mysql.t2.uid

         rows: 1

        Extra: Using where

2rows in set (0.00 sec)

 

 

 

--左连接

 mysql> explain select t1.* from t1 leftjoin t2 on t1.id=t2.uid  where t2.uid is

 not null \G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t2

         type: index

possible_keys:index_uid

          key: index_uid

      key_len: 5

          ref: NULL

         rows: 5

        Extra: Using where; Using index

***************************2. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: eq_ref

possible_keys:PRIMARY

          key: PRIMARY

      key_len: 4

          ref: study_mysql.t2.uid

         rows: 1

        Extra: Using where

2rows in set (0.00 sec)

数据库优化

优化表的类型

 

 

--删除自增

altertable t1 modify id int ;

--删除主键

altertable t1 drop primary key ;

 

 

 

mysql>explain select * from t1 where id <4 \G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: ALL

possible_keys:NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 9

        Extra: Using where

1row in set (0.00 sec)

 

 

 

 

优化

createtable t1_temp like t1;

删除数据 truncate table t1_temp;

 

 

insertinto t1_temp select * from t1 where id<4;

 

 

视图

createview v_t1 as select * from t1 where id<4;

 

 

通过拆分表提高表的访问效率

主从数据库

 

 

 

 

 

使用中间表提高统计查询查询速度

 

 

 

Mysql 锁

备份数据库用

1.myisam  读锁定 所有人能读，但是不能增删改

2.myisam  写锁定  不能读不能写

 

 

--读锁  大家能读 不能增删改

locktable t1 read;

--解锁

unlocktables;

 

 

 

 

--写锁   write 别人不能增删改查--自己可以

locktable t1 write;

 

 

 

服务器优化

 

6.1字符集:

\s 查看字符集 status

          Server characterset

 Db    characterset

 Client characterset

 Conn  characterset

修改my.ini文件 

 

 

character-set-server=utf8

--校验字符集,order by 排序a -z这种排序

collation-server=utf8_general_ci

 

 

 

 

查看字符集的校验字符集

showcharacter set

 

 

 

 

6.2打开bin log 日志 binary 二进制

 

 

--查看bin log日志

 

showvariables like "%bin%";

 

 

|log_bin                                 |OFF

|log_bin_basename                        |

|log_bin_index                           |

|log_bin_trust_function_creators         |OFF

 

 

在my.ini里修改

log-bin=mysql-bin

 

 

重启mysql

exit;

netstop mysql56   --没有;

netstart mysql56

|log_bin                                 |ON

|log_bin_basename                        |C:\mysql-bin      

|log_bin_index                           |C:\mysql-bin.index

| log_bin_trust_function_creators         | OFF

|log_bin_use_v1_row_events               |OFF

 

 

 

 

 

 

6.3慢查询日志  时间

--慢查询日志是否开启

mysql>show variables like '%slow%';

+---------------------------+--------------------------+

|Variable_name             | Value                    |

+---------------------------+--------------------------+

|log_slow_admin_statements | OFF                      |

|log_slow_slave_statements | OFF                      |

|slow_launch_time          | 2                        |

|slow_query_log            | ON                       |

|slow_query_log_file       |DELL-670024CA3F-slow.log |

+---------------------------+--------------------------+

 

 

--慢查询时间是否开启

mysql>show variables like '%long%';

+--------------------------------------------------------+-----------+

|Variable_name                                          |Value     |

+--------------------------------------------------------+-----------+

|long_query_time                                       | 10.000000 |

|performance_schema_events_stages_history_long_size     | 10000    |

|performance_schema_events_statements_history_long_size | 10000     |

|performance_schema_events_waits_history_long_size      | 10000     |

+--------------------------------------------------------+-----------+

 

 

修改my.ini

slow-query-log=1

slow_query_log_file="DELL-670024CA3F-slow.log"

long_query_time=10

 

 

 

 

6.4 socket 

socket被删不能登录  通过tcpip协议和端口连接

 

 

--通过tcp ip登录

mysql-uUser -pPass --protocol tcp -hlocalhost

 

 

重启一次服务 会自动创建mysqlsocket

 

 

 

 

 

 

6.5 root密码丢失

重启mysql服务 越过 授权表

1.关闭服务   linux(pkill mysqld)       windows(net stop mysql56)

 

 

2.启动服务  linux(mysqld_safe --skip-grant-table--user=mysql &)

 

 

 

 

3.登录root    mysql -uroot 不需要密码

 

 

4.selectuser,password from mysql.user;

 

 

5.updatemsql.user set password=password("123") where user="root"and host="localhost";