MySQL笔记

配置说明

重要的事说3遍!字符集用utf8mb4!字符集用utf8mb4!字符集用utf8mb4!否则你会后悔的~~
Mysql的utf8不是真正的UTF-8,它的utf8每个字符最多3个字节,而真正的UTF-8最多4个字节,因此它能编码的Unicode字符不多;
目前Mysql对这个问题的处理是2010年发布的"utf8mb4"字符集(真正的utf-8)

常用操作

windows下启动服务：
管理员输入net start mysql ；
登录：格式： mysql -h主机地址 -u用户名 －p用户密码
例如：mysql -hlocalhost -uroot -pword
主机地址是数据库主机ip，不输入默认localhost
修改密码：
mysqladmin -u用户名 -p旧密码 password 新密码
给用户加个密码去除 -p旧密码 就行
创建表：

Create table table1 (
Id int not null auto_increment,
Sex varchar(10) not null,
Primary key(id)
)engine=InnoDB default charset=utf8mb4;

创建table1表，包含Id，sex字段，id字段设为主键，采用InnoDB储存引擎,默认utf8编码
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1
给表添加一个字段:
表test添加otherinfo字段：alter table test add otherinfo varchar(40) not null;
修改表某个字段类型或者名称：
alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
删除某一字段：
ALTER TABLE mytable DROP 字段名;
插入数据：
Insert into table1 (id ,sex) Values (2,”男”);
可以一次性插入多条数据
查询示例：
SELECT * from table1 WHERE perinfo LIKE ‘%COM’;
查询table1表perinfo字段以com结尾的所有数据
更改数据:
Update table1 set sex=”女” where id =1;
把table1表id为1的数据sex字段数据设为“女”
修改表名
ALTER TABLE table_name RENAME TO new_table_name
修改字段类型
ALTER TABLE 表名 MODIFY COLUMN 字段名 新数据类型 新类型长度 新默认值 新注释; – COLUMN可以省略
alter table table1 modify column name varchar(100) DEFAULT NULL COMMENT ‘用户名’;
修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
alter table table1 change col1 col2 varchar(100) DEFAULT 1.2 COMMENT ‘注释’;
查看记录总数
select count(name) from songs; (查看表songs里name共有多少条记录)
批量插入
INSERT INTO example
(example_id, name, value, other_value) (与表字段一致这句可省略)
VALUES
(100, ‘Name 1’, ‘Value 1’, ‘Other 1’),
(101, ‘Name 2’, ‘Value 2’, ‘Other 2’),
(102, ‘Name 3’, ‘Value 3’, ‘Other 3’),
(103, ‘Name 4’, ‘Value 4’, ‘Other 4’);
此方法可以实现批量插入(逐条插入巨慢!)

删除
DELETE和TRUNCATE TABLE的最大区别是DELETE可以通过WHERE语句选择要删除的记录。但执行得速度不快。而且还可以返回被删除的记录数。而TRUNCATE TABLE无法删除指定的记录，而且不能返回被删除的记录。但它执行得非常快。
查看所有字段
SHOW FULL COLUMNS FROM table_name
创建和表字段完全一样的新表
song_hash_test
create table song_hash_test like song_hash;
从m开始，取n条
select * from table_1 limit m,n;
performance_schema表常用查询

• 查询没有主键的表

mysql> select distinct t.table_schema,t.table_name from information_schema.tables as t
    -> left join information_schema.columns as c on t.table_schema=c.table_schema
    -> and t.table_name = c.table_name and c.column_key="PRI"
    -> where t.table_schema not in ('information_schema','mysql','performance_schema')
    -> and c.table_name is null and t.table_type!='view';

• 查看是谁创建的临时表

mysql> SELECT user, host, event_name, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables,
    -> sum_created_tmp_tables AS tmp_tables
    -> FROM performance_schema.events_statements_summary_by_account_by_event_name
    -> WHERE sum_created_tmp_disk_tables > 0
    -> or sum_created_tmp_tables>0;

• 没有正确关闭数据库连接的用户

mysql> SELECT ess.user, ess.host
    -> ,(a.total_connections - a.current_connections) - ess.count_star as not_closed
    -> ,((a.total_connections - a.current_connections)-ess.count_star)*100/
    -> (a.total_connections - a.current_connections) as pct_not_closed
    -> FROM performance_schema.events_statements_summary_by_account_by_event_name ess
    -> JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)
    ->  WHERE ess.event_name = 'statement/com/quit'
    -> AND (a.total_connections - a.current_connections) > ess.count_star;

sys表常用查询

• 查看表访问量

mysql> select table_schema,table_name,sum(io_read_requests+io_write_requests) io from sys.schema_table_statistics
    -> group by table_schema,table_name order by io desc limit 10;

• 查看数据库连接情况

select * from sys.processlist \G
select * from sys.session limit 10 \G
select * from sys.x$processlist \G
select * from sys.x$session \G

• 查看冗余索引

mysql> select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,
    -> dominant_index_columns  from sys.schema_redundant_indexes;

• 查看未使用索引
  mysql>select * from sys.schema_unused_indexes;
• 表自增ID监控
  mysql>select * from sys.schema_auto_increment_columns limit 10;
• 查看实际消耗磁盘IO的文件
  mysql>select file,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_io desc limit 10;

提高效率
1.比较运算符能用 “=”就不用“<>”
2.明知只有一条查询结果，那请使用 “LIMIT 1”
3.为列选择合适的数据类型(能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT)
4.将大的DELETE，UPDATE or INSERT 查询变成多个小查询
5. 使用UNION ALL 代替 UNION，如果结果集允许重复的话(UNION ALL 不去重，效率高于 UNION)
6. 为获得相同结果集的多次执行，请保持SQL语句前后一致
7. 尽量避免使用 SELECT *(SELECT *会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销)
8. WHERE 子句里面的列尽量被索引(“尽量”，并不是说所有)
9. JOIN 子句里面的列尽量被索引
10. ORDER BY 的列尽量被索引
11. 使用 LIMIT 实现分页逻辑
12. 使用 EXPLAIN 关键字去查看执行计划